CSC 453 Database Systems Lecture

1. CSC 453 Database SystemsLecture Tanu Malik College of CDM DePaul University

2. Today • SQL • Joins • Union/Difference/Intersect • Subqueries • Nested subqueries • Correlated subqueries • Update, Delete statements

3. Last time • SQL • Basic SQL on single table • Find all students who live in ‘Chicago’ • Basic SQL on two tables

4. SQL: Review Queries on two tables Student Enrolled

5. Inner Join Operator • Combines data distributed among linked tables into a single set of tuples using a join condition. • The tables are linked via foreign key references.

6. Find all students who are members of studentgroup ‘HerCTI’ SELECT * FROM Student S, StudentGroup SG, MemberOf M WHERE SG.gid = M.groupID AND M.studentid = S.SID AND SG.name = 'HerCTI' SELECT * FROM Student S, StudentGroup SG, MemberOf M WHERE SG.gid = M.groupID AND SG.name = 'HerCTI' SELECT * FROM Student S, StudentGroup SG WHERE SG.name = 'HerCTI'

7. Find all students whether or not they are members of studentgroup ‘HerCTI’

8. Inner Join vs. Outer Join • An inner join requires that tuples in the tables match in the specified attribute to create a tuple in the result. • An outer join does not: a tuple in the result may be either • the combination of two tuples that match in the specified attribute (matching tuple) • a tuple that does not match anything, combined with an all-NULL tuple (non-matching tuple)

9. Outer Join SELECT * FROM student S LEFT OUTER JOIN enrolled E ON S.sid = E.studentid Null

10. Outer Join Find all students whether or not they are enrolled. SELECT * FROM student LEFT OUTER JOIN enrolled ON sid = studentid

11. Left Outer Join

12. LEFT Vs RIGHT Vs FULL • LEFT/RIGHT refers to the position of the table which will be considered in entirety.

13. Left Outer Join • Includes all matching tuples, plus a tuple for each tuple in the first table that has no match … TABLE1 LEFT OUTER JOIN TABLE2ON TABLE1.Attribute = TABLE2.Attribute;

14. Right Outer Join • Includes all matching tuples, plus a tuple for each tuple in the second table that has no match … TABLE1 RIGHT OUTER JOIN TABLE2ON TABLE1.Attribute = TABLE2.Attribute;

15. Find all students whether or not they are members of studentgroup ‘HerCTI’

16. Let’s say non-students can be presidents of studentgroups. • Find all students that are presidents or not, and find all presidents whether students or not.

17. Full Outer Join • Includes all matching tuples, plus a tuple for each tuple in either table that has no match … TABLE1 FULL OUTER JOIN TABLE2ON TABLE1.Attribute = TABLE2.Attribute;

18. Inner, Outer, and Full Joins

19. SQL: Review Queries on two tables Student Enrolled

20. Find students with same last name but different first name.

21. Joins for comparison: Self Join • Joins the table to itself SELECT … TABLE as Alias1, TABLE as Alias2ON Alias1.AttributeA = Alias2.AttributeB; 21

22. Self-Join • List pair of students taking the same course. • Find students enrolled in two or more courses

23. Break

24. Today • SQL • Combine two tables • Joins • Combine two queries with exact display • Union/Difference/Intersect • Combine two different queries • Subqueries • Nested subqueries • Correlated subqueries

26. Find those students who are not enrolled in courses

27. DIFFERENCE Operation Subtract

28. SQL: EXCEPT/MINUS • The SQL EXCEPT clause/operator is used to combine two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement. • SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] EXCEPT SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]

29. SQL-1 SELECT sid FROM student left outer join course MINUS SELECT studentid FROM enrolled

30. SQL-2 SELECT sid FROM student left outer join course where courseid is null • But courseid must not be null in Enrolled

31. List student members of DeFrag and HerCTI.

32. SQL: UNION • The UNION operator is used to combine the result-set of two or more SELECT statements. • Each SELECT statement within UNION must have the same number of columns • The columns must also have similar data types • The columns in each SELECT statement must also be in the same order • SELECT column_name(s) FROM table1UNIONSELECT column_name(s) FROM table2;

33. List students that are members of both DeFrag and HerCTI groups.

34. SQL: Intersect • Combine two SELECT statements, but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement.  • SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] INTERSECT SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]

35. SQL Queries • General form of a query: 1. SELECT list of attributes to report3. FROM list of tables [CROSS JOIN| [LEFT|REIGHT] OUTER JOIN]2. [WHERE tuple condition]5.[GROUP BY list of grouping attributes]6. [HAVING group condition]4. [ORDER BY list of ordering attributes] ; [UNION|INTERSECT|EXCEPT] • Result is an ordered set of ordered tuples

36. Subqueries

37. Today • SQL • Combine two tables • Joins • Combine two queries with exact display • Union/Difference/Intersect • Combine different queries • Subqueries • Nested subqueries • Correlated subqueries

38. Subqueries • The result of one query may be needed by another to compute its result. • There are two kinds of subqueries • Nested subqueries in which result of a inner subquery is used by outer subquery to compute result • Correlated query in which each tuple of outer query runs aa loop for the inner subqery.

39. Nested Subquery Example • Find out students who started the earliest. • This is not a robust way of querying SELECT min(started) FROM student SELECT student.* FROM student WHERE started = ‘2003’

40. Nested Subquery Example Outer query • Nest Q1 inside Q2 SELECT S1.* FROM student S1 WHERE started = (SELECT min(S2.started) FROM student S2) Subquery OR Inner query

41. Writing Nested Subqueries • A subquery is nested (using parentheses) within an outer query • Outer query uses the result of the subquery, which can be either single value or a table • Outer query checks if a tuple in the outer query is within the inner query single value or table

42. Subquery check • Different ways of checking: • Within the inner query set • Not within the inner query set • Against all members of the inner query set • Against any one member of the inner query set • Does the set exists

43. Set Membership • (6 in {2,4,6}) = ? • (5 in {2,4,6}) = ? • (5 not in {2,4,6}) = ?

44. Set Comparison • Attribute <comp> ANY/ALL (Subquery result) • (5 < ANY {0,4,6}) = ? • (5 < ALL {0,2,4}) = ? • (5 = ANY {0,5}) = ? • (5 != ANY {0,5}) = ? • (5 = ALL {4,5}) = ? • (5 != ALL {4,6}) = ?

45. SQL: IN • IN checks membership in a set • The set may be specified by a subquery or declared in the query • NOT IN check non-membership

46. The IN Operator Conditions can contain IN for “element of” • SELECT LastName, FirstName FROM student WHERE started IN (2010, 2013, 2014); • SELECT LastName, FirstName FROM student WHERE started NOT IN (2010, 2013, 2014); • SELECT Department, CourseName FROM Course WHERE Department IN ('CSC' , 'IT', 'IS');

47. Nesting Queries with IN SELECT LastName, FirstName, SID FROM student WHERE SID IN (SELECT PresidentID FROM studentgroup) Examples List all students who are members of HerCTI. Presidents who are members of their groups.

48. NOT IN: Set Complement Example: Students who did not enroll in 2013. SELECT LastName, FirstName, SID FROM student WHERE sid NOT IN (SELECT studentID FROM enrolled WHERE year = 2013);

49. Example ALL/ANY List student who started in the current year. SELECT LastName, FirstName, SID FROM student WHERE started >= ALL (SELECT started FROM student); List students who started before the current year SELECT LastName, FirstName, SID FROM student WHERE started ? ANY (SELECT started FROM student);

50. Pop Quiz SELECT LastName, FirstName, SID FROM student WHERE started >= ALL (SELECT started FROM student); SELECT LastName, FirstName, SID FROM student WHERE started = (SELECT max(started) FROM student);