Scribd
Upload
24 views34 pages

4.4-SQL DML Subquery

The document outlines the lecture content for CS220: Database Systems, focusing on subqueries, including single row and multiple row subqueries, as well as handling NULL values. It covers rules, types, and examples of subqueries, along with best practices for using operators like EXISTS and NOT EXISTS. The lecture also emphasizes the importance of revisiting concepts for better understanding.

Uploaded by

Thomas Cutler
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
24 views34 pages

4.4-SQL DML Subquery

The document outlines the lecture content for CS220: Database Systems, focusing on subqueries, including single row and multiple row subqueries, as well as handling NULL values. It covers rules, types, and examples of subqueries, along with best practices for using operators like EXISTS and NOT EXISTS. The lecture also emphasizes the importance of revisiting concepts for better understanding.

Uploaded by

Thomas Cutler
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

CS220: DATABASE SYSTEMS

Summer
LECTURE OUTLINE
 Subquery
 Single Row Subquery

 Multiple row subquery

 Null with Subquery

2
SUBQUERY
PROBLEM

4
SOLUTION- SUBQUERY

5
SUBQUERY

6
RULES FOR SUBQUERY

7
TYPES OF SUBQUERIES

8
SINGLE ROW SUBQUERY
SINGLE ROW SUBQUERY

10
EXECUTION OF SINGLE ROW QUERY

11
GROUP FUNCTION IN SUBQUERY

12
HAVING CLAUSE IN SUBQUERY

13
EXAMPLE: OUTPUT OF QUERY

14
SOLUTION: OUTPUT

15
MULTIPLE ROW SUBQUERY
MULTIPLE ROW SUBQUERY

17
ANY OPERATOR WITH SUBQUERY

18
ALL OPERATOR WITH SUBQUERY

19
EXISTS OPERATOR WITH SUBQUERY

20
NULL WITH SUBQUERY
NULL IN SUBQUERY

22
NOT EXISTS

Avoid using NOT IN


if there is a chance
of having NULL in
the column use
NOT EXISTS
instead.
23
SUMMARY

24
MORE ON SUBQUERY
Revisit!

26
Revisit!

27
Revisit!

28
Example-1
WHERE clause-IN
Operator

29
Example-2
WHERE clause-
NOT IN Operator

30
Example-3
WHERE clause
-Multiple Column

31
Example-4
HAVING clause

32
Example-5
FROM clause

33
THANK YOU

You might also like