CS 405 G Introduction to Database Systems Instructor
CS 405 G: Introduction to Database Systems Instructor: Jinze Liu Fall 2007
Review l l l l SFW statement: Sort the outputs: USE bag operation: Enforce set operation: Output average: Partition rows in a table: Select certain groups: Subqueries: 10/25/2021 ORDER BY Default choice DISTINCT AVG (aggregation function) GROUP BY HAVING =, in, exists Jinze Liu @ University of Kentucky 2
Operational semantics of subqueries l SELECT * FROM Student AS s WHERE EXISTS (SELECT * FROM Student WHERE name = ’Bart’ AND age = s. age); l For each row s in Student l l l Evaluate the subquery with the appropriate value of s. age If the result of the subquery is not empty, output s. * The DBMS query optimizer may choose to process the query in an equivalent, but more efficient way (example? ) 10/25/2021 Jinze Liu @ University of Kentucky 3
Exercise l l Goal: Get the Name of one’s supervisor EMPLOYEE (Eid, Mid, Name) SQL Statement: SELECT EID, Name FROM EMPLOYEE WHERE MId =Eid; SELECT E 1. EID, E 2. Name FROM EMPLOYEE E 1, EMPLOYEE E 2 WHERE E 1. MId = E 2. EId; Eid Mid Name Eid Name 1123 1234 John Smith 1123 Mary Carter 1234 1311 Mary Carter 1234 Bob Lee 1311 1611 Bob Lee 1311 Jack Snow 1455 1611 Lisa Wang 1455 Jack Snow 1611 Jack Snow 10/25/2021 Jinze Liu @ University of Kentucky 4
Exercise l l l F F Goal: group employees according to their department, for each department, list EIds of its employees and list the head count EMPLOYEE (Eid, Name), DEPARTMENT(Eid, Did) SQL Statement: SELECT Did, Eid, COUNT(Eid) FROM EMPLOYEE e, DEPARTMENT d WHERE e. Eid = d. Eid GROUP BY Did There is something wrong! Wishful thinking (list a group of Eid after grouping) won’t work Solution: produce (1) a summary table listing Did and head count, and (2) sort Department according to Did. 10/25/2021 Jinze Liu @ University of Kentucky 5
More on Nested Queries: Scoping l To find out which table a column belongs to l l l Start with the immediately surrounding query If not found, look in the one surrounding that; repeat if necessary Use table_name. column_name notation and AS (renaming) to avoid confusion 10/25/2021 Jinze Liu @ University of Kentucky 6
An Example SELECT * FROM Student s WHERE EXISTS (SELECT * FROM Enroll e WHERE SID = s. SID AND EXISTS (SELECT * FROM Enroll WHERE SID = s. SID AND CID <> e. CID)); Students who are taking at least two courses 10/25/2021 Jinze Liu @ University of Kentucky 7
Quantified subqueries l l A quantified subquery can be used as a value in a WHERE condition Universal quantification (for all): … WHERE x op ALL (subquery) … l l True iff for all t in the result of subquery, x op t Existential quantification (exists): … WHERE x op ANY (subquery) … l F True iff there exists some t in the result of subquery such that x op t Beware l l 10/25/2021 In common parlance, “any” and “all” seem to be synonyms In SQL, ANY really means “some” Jinze Liu @ University of Kentucky 8
Examples of quantified subqueries l Which employees have the highest salary? Employee (Sid, Name, Salary) l SELECT * FROM Employee WHERE Salary >= ALL (SELECT Salary FROM Employee); l How about the lowest salary? l SELECT * FROM Employee WHERE Salary <= ALL (SELECT salary FROM Employee); 10/25/2021 Jinze Liu @ University of Kentucky 9
More ways of getting the highest Salary l Who has the highest Salary? l l SELECT * FROM Employee e WHERE NOT EXISTS (SELECT * FROM Employee WHERE Salary > e. Salary); SELECT * FROM Employee WHERE Eid NOT IN (SELECT e 1. SID FROM Employee e 1, Employee e 2 WHERE e 1. Salary < e 2. Salary); 10/25/2021 Jinze Liu @ University of Kentucky 10
Nested Queries l Nested queries do not add expression power to SQL l l l For convenient Write intuitive SQL queries Can always use SQL queries without nesting to complete the same task (though sometime it is hard) 10/25/2021 Jinze Liu @ University of Kentucky 11
More Exercise Sailors (sid: INTEGER, sname: string, rating: INTEGER, age: REAL) Boats (bid: INTEGER, bname: string, color: string) Reserves (sid: INTEGER, bid: INTEGER) Sailors Boats sid sname rating age bid bname color 1 Fred 7 22 101 Nina red 2 Jim 2 39 102 Pinta blue 3 Nancy 8 27 103 Santa red Maria sid bid Reserves 1 102 2 10/25/2021 Jinze Liu @ University of Kentucky 12
Exercise I l l l Find sid’s of sailors who’ve reserved a red AND a green boat SELECT R 1. sid FROM Boats B 1, Reserves R 1, Boats B 2, Reserves R 2 WHERE B 1. color=‘red’ AND B 2. color=‘green’ AND R 1. bid=B 1. bid AND R 2. bid=B 2. bid AND R 1. sid=R 2. sid SELECT sid FROM Boats, Reserves Where B. color = ‘red’ INTERSECT (SELECT sid FROM Boats, Reserves Where B. color = ‘green’) 10/25/2021 Jinze Liu @ University of Kentucky 13
Exercise II l Find sid’s of sailors who have not reserved a boat SELECT sid Non-monotonic operation! FROM Sailors EXCEPT (SELECT S. sid FROM Sailors S, Reserves R WHERE S. sid=R. sid) 10/25/2021 Jinze Liu @ University of Kentucky 14
Exercise III (a tough one) l Find sailors who’ve reserved all boats SELECT Sid Non-monotonic operation! FROM Sailor #All sailors EXCEPT #Those who do not reserve all SELECT Sid boats FROM #All possible combinations (SELECT bid, sid between sid and bid FROM Boat, Sailor EXCEPT #Existing reservations Reserves) 10/25/2021 Jinze Liu @ University of Kentucky 15
- Slides: 15