1 CS 480: Database Systems Lecture 11 February 8, 2013

2 SQL Query Example We will start by studying the Data Manipulation Language component of SQL. Query Example: Retrieve the names of parts that are red. SQL: SELECT partName FROM part WHERE color = ‘Red’ ΠpartName(σcolor=‘Red’ (part)) Relational Algebra:

3 SQL Basic Query Format SELECT A1,A2,…,An FROM r1,r2,…,rm WHERE PSuppose the ri’s have scheme ri(Ri) where Ri is a set of attributes. Then the Ai’s are attributes in R1  …  Rm. P is a boolean predicate in which an atom is a selection atom on r1  r2  …  rm or other types of SQL boolean predicates: string predicates (LIKE,CONTAINS) t IN ri t θ ALL(ri), t θ SOME(ri) others MAKE NOTE: SQL is case insensitive

4 SQL Basic Query Format SELECT A1,A2,…,An FROM r1,r2,…,rm WHERE PQueries are written in SELECT, FROM, WHERE order It’s important to understand the operational order: FROM: Cartesian product of the given relations WHERE: Selection based on the given predicate SELECT: Projection of the given attributes.

5 SQL Basic Query Format SELECT A1,A2,…,An FROM r1,r2,…,rm WHERE PIn relational algebra:

6 SQL Basic Query Format The WHERE clause could be omitted:Ex: Retrieve the colors of parts. Πcolor(part) SELECT color FROM part

7 SQL Basic Query Format The SELECT clause can have arithmeticoperations. Ex: Retrieve employee names with their monthly salaries. SELECT name,yearly_salary/12 FROM employee

8 SQL General Query Format( ) { } ( ) UNION INTERSECT MINUS Basic Query Format 1 Basic Query Format 2 { } … UNION INTERSECT MINUS

9 SQL Example Ex. Query: Retrieve names of CS students at UIUC or UIC.UIC_STUDENT(id,name,major) UIUC_STUDENT(id,name,major) Query: Retrieve names of CS students at UIUC or UIC. (SELECT name FROM uic_student WHERE major=‘CS’) UNION FROM uiuc_student

10 SQL Example Ex. Query: Retrieve names of CS students at UIUC or UIC.UIC_STUDENT(id,name,major) UIUC_STUDENT(id,name,major) Query: Retrieve names of CS students at UIUC or UIC. (SELECT name FROM uic_student WHERE major=‘CS’) UNION FROM uiuc_student

11 SQL Example Ex. Query: Retrieve names of CS students at UIUC or UIC.UIC_STUDENT(id,name,major) UIUC_STUDENT(id,name,major) Query: Retrieve names of CS students at UIUC or UIC. (SELECT name FROM uic_student WHERE major=‘CS’) UNION FROM uiuc_student

12 Completeness of SQL Projection (Π)

13 Completeness of SQL Projection (Π) SELECT A1,A2,…,An FROM r

14 Completeness of SQL Selection (σ) σP(r)

15 Completeness of SQL Selection (σ) σP(r) SELECT * FROM r WHERE P

16 σP(r) Completeness of SQL SELECT * FROM r WHERE P Selection (σ)* denotes “all attributes”

17 Completeness of SQL Union () r  s

18 Completeness of SQL Union () r  s (SELECT * FROM r) UNION FROM s)

19 Completeness of SQL Difference (–) r – s

20 r – s Completeness of SQL (SELECT * FROM r) MINUS FROM s)Difference (–) r – s (SELECT * FROM r) MINUS FROM s)

21 r – s Completeness of SQL (SELECT * FROM r) EXCEPT FROM s)Difference (–) r – s (SELECT * FROM r) EXCEPT FROM s) Book uses except. Both are accepted.

22 Completeness of SQL Cartesian Product () r  s

23 Completeness of SQL Cartesian Product () r  s SELECT * FROM r,s

24 ΠR(r  s) Completeness of SQL SELECT r.* FROM r,sCartesian Product () ΠR(r  s) SELECT r.* FROM r,s

25 Completeness of SQL Rename (ρ) ρd(r)

26 Completeness of SQL Rename (ρ) ρd(r) SELECT * FROM r AS d

27 Rename Example Πenrol.student-id(σenrol.grade=d.grade  enrol.course=d.course( enrol  σstudent-id=‘194’(ρd(enrol)))) SELECT enrol.student_id FROM enrol,enrol AS d WHERE enrol.grade=d.grade AND enrol.course=d.course AND d.student_id=194

28 Rename Example Πenrol.student-id(σenrol.grade=d.grade  enrol.course=d.course( enrol  σstudent-id=‘194’(ρd(enrol)))) SELECT enrol.student_id FROM enrol,enrol AS d WHERE enrol.grade=d.grade AND enrol.course=d.course AND d.student_id=194

29 Rename Example Πenrol.student-id(σenrol.grade=d.grade  enrol.course=d.course( enrol  σstudent-id=‘194’(ρd(enrol)))) SELECT enrol.student_id FROM enrol,enrol AS d WHERE enrol.grade=d.grade AND enrol.course=d.course AND d.student_id=194

30 Rename Example Πenrol.student-id(σenrol.grade=d.grade  enrol.course=d.course( enrol  σstudent-id=‘194’(ρd(enrol)))) SELECT enrol.student_id FROM enrol,enrol AS d WHERE enrol.grade=d.grade AND enrol.course=d.course AND d.student_id=194

31 Rename Example Πenrol.student-id(σenrol.grade=d.grade  enrol.course=d.course( enrol  σstudent-id=‘194’(ρd(enrol)))) SELECT enrol.student_id FROM enrol,enrol AS d WHERE enrol.grade=d.grade AND enrol.course=d.course AND d.student_id=194

32 Rename Example Πenrol.student-id(σenrol.grade=d.grade  enrol.course=d.course( enrol  σstudent-id=‘194’(ρd(enrol)))) SELECT enrol.student_id FROM enrol,enrol AS d WHERE enrol.grade=d.grade AND enrol.course=d.course AND d.student_id=194

33 Rename Example Πenrol.student-id(σenrol.grade=d.grade  enrol.course=d.course( enrol  σstudent-id=‘194’(ρd(enrol)))) SELECT enrol.student_id FROM enrol,enrol AS d WHERE enrol.grade=d.grade AND enrol.course=d.course AND d.student_id=194

34 Rename Example Πenrol.student-id(σenrol.grade=d.grade  enrol.course=d.course( enrol  σstudent-id=‘194’(ρd(enrol)))) SELECT enrol.student_id FROM enrol,enrol AS d WHERE enrol.grade=d.grade AND enrol.course=d.course AND d.student_id=194

35 Rename Example Πenrol.student-id(σenrol.grade=d.grade  enrol.course=d.course( enrol  σstudent-id=‘194’(ρd(enrol)))) SELECT enrol.student_id FROM enrol,enrol AS d WHERE enrol.grade=d.grade AND enrol.course=d.course AND d.student_id=194

36 Rename Example Πenrol.student-id(σenrol.grade=d.grade  enrol.course=d.course( enrol  σstudent-id=‘194’(ρd(enrol)))) SELECT enrol.student_id FROM enrol,enrol AS d WHERE enrol.grade=d.grade AND enrol.course=d.course AND d.student_id=194

37 Rename Example Πenrol.student-id(σenrol.grade=d.grade  enrol.course=d.course( enrol  σstudent-id=‘194’(ρd(enrol)))) SELECT enrol.student_id FROM enrol,enrol AS d WHERE enrol.grade=d.grade AND enrol.course=d.course AND d.student_id=194

38 More set operations and Nested SubqueriesSQL provides a mechanism for nested subqueries. A subquery is a SELECT-FROM-WHERE expression that is nested within another query. Common use of subqueries is to perform tests for set membership and set comparisons as part of the WHERE clause predicate.

39 Set Membership () SELECT student_id FROM enrolRetrieve the student_id’s of students that took both CS480 and CS580 and got an A in both. ENROL(student_id,course,grade) SELECT student_id FROM enrol WHERE course=‘CS480’ AND grade=‘A’ AND student_id IN (SELECT student_id FROM enrol WHERE course=‘CS580’ AND grade=‘A’)

40 Set Membership () SELECT student_id FROM enrolRetrieve the student_id’s of students that took both CS480 and CS580 and got an A in both. ENROL(student_id,course,grade) SELECT student_id FROM enrol WHERE course=‘CS480’ AND grade=‘A’ AND student_id IN (SELECT student_id FROM enrol WHERE course=‘CS580’ AND grade=‘A’)

41 Set Membership () SELECT student_id FROM enrolRetrieve the student_id’s of students that took both CS480 and CS580 and got an A in both. ENROL(student_id,course,grade) SELECT student_id FROM enrol WHERE course=‘CS480’ AND grade=‘A’ AND student_id IN (SELECT student_id FROM enrol WHERE course=‘CS580’ AND grade=‘A’) WHAT WOULD BE AN ALTERNATIVE TO THIS? Cartesian product of enrol with itself with renaming. OR use the Intersection operator.

42 Set Membership () Retrieve the student_id’s of students that took CS480 but have not taken CS580. ENROL(student_id,course,grade) SELECT student_id FROM enrol WHERE course=‘CS480’ AND student_id NOT IN (SELECT student_id FROM enrol WHERE course=‘CS580’)

43 Set Membership () Retrieve the student_id’s of students that took CS480 but have not taken CS580. ENROL(student_id,course,grade) SELECT student_id FROM enrol WHERE course=‘CS480’ AND student_id NOT IN (SELECT student_id FROM enrol WHERE course=‘CS580’)

44 Set Membership () Retrieve the student_id’s of students that took CS480 but have not taken CS580. ENROL(student_id,course,grade) SELECT student_id FROM enrol WHERE course=‘CS480’ AND student_id NOT IN (SELECT student_id FROM enrol WHERE course=‘CS580’) An alternative to this one would be to do it with the EXCEPT one… ADD NOTE THAT YOU COULD HAVE MORE THAN ONE ATTRIBUTE before the NOT IN… or before the IN for that matter.

45 Set Comparison Retrieve the names of instructors that have a salary higher than at least one instructor in the Biology department. INSTRUCTOR(name,dept,salary) SELECT name FROM instructor as i WHERE i.salary > SOME(SELECT salary FROM instructor as j WHERE j.dept=‘Biology’)

46 Set Comparison Retrieve the names of instructors that have a salary higher than at least one instructor in the Biology department. INSTRUCTOR(name,dept,salary) SELECT name FROM instructor as i WHERE i.salary > SOME(SELECT salary FROM instructor as j WHERE j.dept=‘Biology’) An alternative to this one would be to do it with the EXCEPT one…

47 Set Comparison Retrieve the names of instructors that have a salary higher than at least one instructor in the Biology department. INSTRUCTOR(name,dept,salary) SELECT name FROM instructor as i WHERE i.salary > SOME(SELECT salary FROM instructor as j WHERE j.dept=‘Biology’)

48 Set Comparison Retrieve the names of instructors that have a salary higher than all of the instructors in the Biology department. INSTRUCTOR(name,dept,salary) SELECT name FROM instructor as i WHERE i.salary > ALL(SELECT salary FROM instructor as j WHERE j.dept=‘Biology’)

49 Set Comparison Retrieve the names of instructors that have a salary higher than all of the instructors in the Biology department. INSTRUCTOR(name,dept,salary) SELECT name FROM instructor as i WHERE i.salary > ALL(SELECT salary FROM instructor as j WHERE j.dept=‘Biology’)

50 Set Containment The CONTAINS clause is a mechanism by which SQL implements the division operator. ENROL(id,course,grade) STUDENT(id,name,major) Names and majors of students that took all the courses that John Doe took. SELECT name,major FROM student as d WHERE (SELECT course FROM enrol enrol.id = d.id) CONTAINS (SELECT course FROM enrol as e,student as s WHERE e.id=s.id AND s.name=‘John Doe’)

51 Set Containment The CONTAINS clause is a mechanism by which SQL implements the division operator. ENROL(id,course,grade) STUDENT(id,name,major) Names and majors of students that took all the courses that John Doe took. SELECT name,major FROM student as d WHERE (SELECT course FROM enrol enrol.id = d.id) CONTAINS (SELECT course FROM enrol as e,student as s WHERE e.id=s.id AND s.name=‘John Doe’)

52 Set Containment The CONTAINS clause is a mechanism by which SQL implements the division operator. ENROL(id,course,grade) STUDENT(id,name,major) Names and majors of students that took all the courses that John Doe took. SELECT name,major FROM student as d WHERE (SELECT course FROM enrol enrol.id = d.id) CONTAINS (SELECT course FROM enrol as e,student as s WHERE e.id=s.id AND s.name=‘John Doe’)

53 Set Containment The CONTAINS clause is a mechanism by which SQL implements the division operator. ENROL(id,course,grade) STUDENT(id,name,major) Names and majors of students that took all the courses that John Doe took. SELECT name,major FROM student as d WHERE (SELECT course FROM enrol enrol.id = d.id) CONTAINS (SELECT course FROM enrol as e,student as s WHERE e.id=s.id AND s.name=‘John Doe’)

54 Set Containment The CONTAINS clause is a mechanism by which SQL implements the division operator. ENROL(id,course,grade) STUDENT(id,name,major) Names and majors of students that took all the courses that John Doe took. SELECT name,major FROM student as d WHERE (SELECT course FROM enrol enrol.id = d.id) CONTAINS (SELECT course FROM enrol as e,student as s WHERE e.id=s.id AND s.name=‘John Doe’)

55 Set Containment The CONTAINS clause is a mechanism by which SQL implements the division operator. ENROL(id,course,grade) STUDENT(id,name,major) Names and majors of students that took all the courses that John Doe took. Πname,major(student (Πid,course(enrol)  Πcourse(σname=‘John Doe’(student enrol))) SELECT name,major FROM student as d WHERE (SELECT course FROM enrol enrol.id = d.id) CONTAINS (SELECT course FROM enrol as e,student as s WHERE e.id=s.id AND s.name=‘John Doe’)