Chapter Eleven Data Manipulation Language (DML) Nested Queries Dr. Chitsaz

1. Chapter ElevenData Manipulation Language (DML)Nested QueriesDr. Chitsaz Objectives Nested queries Application of nested queries Conditions on nested queries Pair wise and non-pair wise comparison Union Minus intersection

2. Nested Queries: SELECT …… FROM ….. WHERE operator (SELECT …. FROM …. );

3. Rules: • Inner query is executed first • Enclosed sub-queries in parentheses. • Sub-queries must be placed on the right side of the comparison operator • Do not add an ORDER BY to a sub-query • Result is used by outer query • Sub-query can be placed in WHERE HAVING FROM

4. Rules: Operator: • Single row = ,<>, > ,< , >= ,<= • Multiple rows ANY IN ALL • Use single row operators with a single row sub-query • Use multiple row operators with multiple rows sub-query

5. Example Data SELECT * FROM Student; NAME ID MAJOR MINOR GPA ------------------------------- ---- ---- ---------------- James 243 COSC MATH 3.21 John 102234 COSC MATH 3.32 Sarah 201241 MATH COSC 3.20 Mary 341235 MATH ENGL 2.11 Walter 1111 ENGL MATH 1.41 Sally 2001 MATH ENGL 3.00 Isaac 201248 MATH COSC 4.00 7 rows selected.

6. Example Data SELECT * FROM Faculty; NAME ID SALARY DEPT AREA ---------- ---------- ----------------------------------------------------- Williams 191 34000 COSC DATA STRUCTURES Johnson 1234 30000 MATH CALCULUS Jackson 413 45000 COSC NETWORKING Hook 103 25000 COSC DATABASE Morgan 127 32000 ENGL LITERATURE Thomas 243 30000 MATH ALGEBRA Smith 849 45000 COSC NETWORKING Augustine 837 20000 COSC DATA STRUCTURES 8 rows selected.

7. Example Data SELECT * FROM Department; DEPARTMENT_NAME ID ---- ----------------------------------------------- ENGL 2003 MATH 4513 PSYC 1113 COSC 2001 MUSC 1233 HIST 42513 PHSC 4542 CHEM 4113 IDIS 3543 9 rows selected.

8. Example Data SELECT * FROM Student_course; ID C_NUM DEPT CREDITS NAME GRADE ----------------------------------- ---------- ---- ---------- -------------- ----------- 102234 360 MATH 3 MATH360 A 102234 100 MATH 3 MATH100 B 201241 310 COSC 3 COSC310 C 341235 250 COSC 3 COSC250 F 2001 120 ENGL 3 ENGL120 C 1111 360 ENGL 3 ENGL360 A 243 450 MATH 3 MATH450 B 2001 565 COSC 3 COSC565 C 8 rows selected.

9. Nested Queries: • Correlated sub-queries: SELECT Name FROM Student WHERE GPA > (SELECT GPA FROM Student WHERE ID= 243);

10. Results NAME --------- John Sarah Isaac

11. *Nested Queries*: • Scalar sub-queries: SELECT Name, ID FROM Student_course S ORDER BY (SELECT Department_Name FROM Department D WHERE S.id=D.id);

12. Results NAME ID -------------------------------------- ENGL120 2001 COSC565 2001 MATH360 102234 MATH100 102234 ENGL360 1111 MATH450 243 COSC310 201241 COSC250 341235

13. Using group function in a sub-query: • SELECT Name FROM Faculty WHERE Salary = (SELECT MIN(Salary ) FROM Faculty );

14. Results NAME ------------- Augustine

15. Using HAVING with sub-queries: SELECT Dept, MIN(salary) FROM Faculty GROUP BY Dept HAVING MIN(Salary) > (SELECT MIN(Salary ) FROM Faculty WHERE Dept='COSC');

16. Results DEPT MIN(SALARY) ------------------------------ ENGL 32000 MATH 30000

17. Incorrect Statements: SELECT Name FROM Faculty WHERE Salary = (SELECT MIN(Salary ) FROM Faculty GROUP BY Dept);

18. No Value SELECT Name FROM Student WHERE GPA = (SELECT GPA FROM Student WHERE Name=‘NOHN');

19. Multiple Row Sub-query: • Name of students having Grade A: SELECT Name FROM Student WHERE ID IN (SELECT ID FROM Student_Course WHERE Grade='A' ); WHERE EXISTS WHERE NOT IN …. WHERE NOT EXISTS

20. Results NAME ------- Walter John

21. Multiple Row Sub-query: SELECT Name, ID FROM Student WHERE ID IN (SELECT ID FROM Faculty WHERE Dept IN (SELECT Department_Name FROM Department WHERE ID = 2001) );

22. Multiple Row Sub-query: SELECT Name FROM Student WHERE GPA < ANY (SELECT GPA FROM Student WHERE Major='COSC' );

23. Results NAME -------- James Sarah Mary Walter Sally

24. *Multiple Row Sub-query*: SELECT Name FROM Student WHERE ID > ALL (SELECT ID FROM Student_Course WHERE Major='COSC' );

25. Results NAME -------- Sarah Mary Walter Sally Isaac

26. Multiple Row Sub-query: SELECT Name FROM Faculty WHERE Salary < (SELECT Salary FROM Faculty WHERE Faculty.ID =1234) AND Dept = (SELECT Dept FROM Faculty WHERE Area='DATABASE');

27. Results NAME ------------ Hook Augustine

28. Multiple Row Sub-query: • Name of Faculty who took courses SELECT Name FROM Faculty WHERE EXISTS // IN (SELECT * FROM Student_Course WHERE Faculty.ID = Student_Course.ID);

29. Results NAME --------- Thomas

30. Multiple Row Sub-query: • Name of Faculty who did not take courses SELECT Name FROM Faculty WHERE NOT EXISTS (SELECT * FROM Student_Course WHERE Faculty.ID = Student_Course.ID);

31. Results NAME ------------ Williams Johnson Jackson Hook Morgan Smith Augustine

32. How to write this query? • Find the department name and faculty name of the department with more than one faculty member.

33. How to write this query? • Find Departments with more than one Faculty SELECT Dept, COUNT(*) FROM Faculty GROUP BY Dept HAVING COUNT(*) > 1; Result: DEPT COUNT(*) ---------------------------- COSC 5 MATH 2

34. How to write this query? • Find department and name of faculty that are in each department with more than one faculty: SELECT Dept, Name, COUNT(*) FROM Faculty GROUP BY Dept, Name HAVING COUNT(*) > 1;

35. How to write this query? • SELECT Dept, Name FROM Faculty a WHERE EXISTS (SELECT * FROM Faculty b WHERE a.Dept = b.Dept GROUP BY b.Dept HAVING COUNT(b.ID) > 1);

36. Results DEPT NAME -------------------------- COSC Williams MATH Johnson COSC Jackson COSC Hook MATH Thomas COSC Smith COSC Augustine

37. Multiple Column Sub-queries: SELECT col1,col2, col3,… FROM table WHERE (col1,col2, ….) IN (SELECT (col1,col2, ….) FROM table WHERE condition );

38. (Pair-wise Comparison) • Name of students who have the same major and minor as Mary: SELECT Name, ID, GPA FROM Student WHERE (major, minor) IN (SELECT Major, Minor FROM Student WHERE Name='MARY');

39. Results NAME ID GPA ---------- --------------------------- Mary 341235 2.11 Sally 2001 3.00

40. (Non-pair-wise Comparison) SELECT Name, ID, Salary FROM Faculty WHERE Salary IN (SELECT Salary FROM Faculty WHERE Dept='COSC') OR Area IN (SELECT Area FROM Faculty WHERE name =‘Williams');

41. Results NAME ID SALARY ------------------------------------------------- Williams 191 34000 Jackson 413 45000 Hook 103 25000 Smith 849 45000 Augustine 837 20000

42. Using a sub-query in the FROM clause SELECT a.Dept, avgsalary FROM Faculty a , (SELECT Dept , AVG(Salary) avgsalary FROM Faculty GROUP BY Dept) b WHERE a.Dept = b.Dept;

43. Results DEPT AVGSALARY ---------------------------- COSC 33800 COSC 33800 COSC 33800 COSC 33800 COSC 33800 ENGL 32000 MATH 30000 MATH 30000

44. Correlated Subquery SELECT * FROM Student WHERE EXISTS (SELECT NULL FROM Faculty WHERE Faculty.Dept = Student.Major);

45. Results NAME ID MAJOR MINOR GPA -------------------------------------------------------------------------- James 243 COSC MATH 3.21 John 102234 COSC MATH 3.32 Sarah 201241 MATH COSC 3.2 Mary 341235 MATH ENGL 2.11 Walter 1111 ENGL MATH 1.41 Sally 2001 MATH ENGL 3.0 Isaac 201248 MATH COSC 4.0

46. SELECT ID FROM Student WHERE ID NOT IN (SELECT ID FROM Faculty WHERE ID IS NOT NULL); Note: If Faculty ID is nullable

47. Results ID ---------- 102234 201241 341235 1111 2001 201248

48. Inline View SELECT S.ID FROM Student S, (SELECT ID FROM Faculty) F WHERE S.ID < > F.ID;

49. Manipulating Data • UNION: List of students and faculty. SELECT Name FROM Student UNION SELECT Name FROM Faculty;

50. Results NAME ------------ Augustine Hook Isaac Jackson James John Johnson Mary Morgan Sally Sarah Smith Thomas Walter Williams