SQL: Queries, Programming, Triggers

1. SQL: Queries, Programming, Triggers Chapter 5 Modified by Donghui Zhang

2. SELECT [DISTINCT] target-list FROMrelation-list WHERE qualification Basic SQL Query • relation-list A list of relation names (possibly with a range-variable after each name). • target-list A list of attributes of relations in relation-list • qualification Comparisons (Attr op const or Attr1 op Attr2, where op is one of ) combined using AND, OR and NOT. • DISTINCT is an optional keyword indicating that the answer should not contain duplicates. Default is that duplicates are not eliminated!

3. Conceptual Evaluation Strategy • Semantics of an SQL query defined in terms of the following conceptual evaluation strategy: • Compute the cross-product of relation-list. • Discard resulting tuples if they fail qualifications. • Delete attributes that are not in target-list. • If DISTINCT is specified, eliminate duplicate rows. • This strategy is probably the least efficient way to compute a query! An optimizer will find more efficient strategies to compute the same answers.

4. A Note on Range Variables • Really needed only if the same relation appears twice in the FROM clause. The previous query can also be written as: SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND bid=103 It is good style, however, to use range variables always! OR SELECT sname FROM Sailors, Reserves WHERE Sailors.sid=Reserves.sid AND bid=103

5. Expressions and Strings SELECT S.age, age1=S.age-5, 2*S.age AS age2 FROM Sailors S WHERE S.sname LIKE ‘B_%B’ • Illustrates use of arithmetic expressions and string pattern matching: Find triples (of ages of sailors and two fields defined by expressions) for sailors whose names begin and end with B and contain at least three characters. • ASand = are two ways to name fields in result. • LIKE is used for string matching. `_’ stands for any one character and `%’ stands for 0 or more arbitrary characters.

6. Set Operations in SQL • UNION: s1 UNION s2, result rows either in s1 or s2. • INTERSECT: s1 INTERSECT s2, result rows in s1 and s2. • EXCEPT: s1 EXCEPT s2, result rows in s1 but not in s2. (Some system recognize ‘MINUS’ for EXECPT) • Also, IN, ANY, ALL, EXISTS, to be covered in ‘Nested Queries’.

7. Nested Queries Find names of sailors who’ve reserved boat #103: SELECT S.sname FROM Sailors S WHERE S.sid IN (SELECT R.sid FROM Reserves R WHERE R.bid=103) • A very powerful feature of SQL: a WHERE clause can itself contain an SQL query! (Actually, so can FROM and HAVING clauses.) • To find sailors who’ve not reserved #103, use NOT IN. • To understand semantics of nested queries, think of a nested loops evaluation: For each Sailors tuple, check the qualification by computing the subquery.

8. Nested Queries with Correlation Find names of sailors who’ve reserved boat #103: SELECT S.sname FROM Sailors S WHERE EXISTS (SELECT * FROM Reserves R WHERE R.bid=103 ANDS.sid=R.sid) • EXISTSis another set comparison operator, like IN. • If UNIQUEis used, and * is replaced by R.bid, finds sailors with at most one reservation for boat #103. (UNIQUE checks for duplicate tuples; * denotes all attributes. Why do we have to replace * by R.bid?) • Illustrates why, in general, subquery must be re-computed for each Sailors tuple.

9. More on Set-Comparison Operators • We’ve already seen IN, EXISTS and UNIQUE. Can also use NOT IN, NOT EXISTS and NOT UNIQUE. • Also available: opANY, opALL, op IN • Find sailors whose rating is greater than that of some sailor called Horatio: SELECT * FROM Sailors S WHERE S.rating > ANY(SELECT S2.rating FROM Sailors S2 WHERE S2.sname=‘Horatio’)

10. (1) SELECT S.sname FROM Sailors S WHERE NOT EXISTS ((SELECT B.bid FROM Boats B) EXCEPT (SELECT R.bid FROM Reserves R WHERE R.sid=S.sid)) Division in SQL Find sailors who’ve reserved all boats. • Let’s do it the hard way, without EXCEPT: (2) SELECT S.sname FROM Sailors S WHERE NOT EXISTS (SELECT B.bid FROM Boats B WHERE NOT EXISTS (SELECT R.bid FROM Reserves R WHERE R.bid=B.bid AND R.sid=S.sid)) Sailors S such that ... there is no boat B without ... a Reserves tuple showing S reserved B

11. COUNT (*) COUNT ( [DISTINCT] A) SUM ( [DISTINCT] A) AVG ( [DISTINCT] A) MAX (A) MIN (A) Aggregate Operators • Significant extension of relational algebra. single column SELECT COUNT (*) FROM Sailors S SELECT S.sname FROM Sailors S WHERE S.rating= (SELECT MAX(S2.rating) FROM Sailors S2) SELECT AVG (S.age) FROM Sailors S WHERE S.rating=10 SELECT COUNT (DISTINCT S.rating) FROM Sailors S WHERE S.sname=‘Bob’ SELECT AVG ( DISTINCT S.age) FROM Sailors S WHERE S.rating=10

12. Queries With GROUP BY and HAVING SELECT [DISTINCT] target-list FROMrelation-list WHERE qualification GROUP BYgrouping-list HAVING group-qualification • The target-list contains (i) attribute names(ii) terms with aggregate operations (e.g., MIN (S.age)). • The attribute list (i)must be a subset of grouping-list. Intuitively, each answer tuple corresponds to a group, andthese attributes must have a single value per group. (A group is a set of tuples that have the same value for all attributes in grouping-list.)

13. Conceptual Evaluation • The cross-product of relation-list is computed, tuples that fail qualification are discarded, `unnecessary’ fields are deleted, and the remaining tuples are partitioned into groups by the value of attributes in grouping-list. • The group-qualification is then applied to eliminate some groups. Expressions in group-qualification must have a single value per group! • In effect, an attribute in group-qualificationthat is not an argument of an aggregate op also appears in grouping-list. (SQL does not exploit primary key semantics here!) • One answer tuple is generated per qualifying group.

14. Find the age of the youngest sailor with age 18, for each rating with at least 2 such sailors SELECT S.rating, MIN (S.age) FROM Sailors S WHERE S.age >= 18 GROUP BY S.rating HAVINGCOUNT (*) >= 2 • Only S.rating and S.age are mentioned in the SELECT, GROUP BY or HAVING clauses; other attributes `unnecessary’. • 2nd column of result is unnamed. (Use AS to name it.) Answer relation

15. Find the age of the youngest sailor with age 18, for each rating with at least 2 such sailors SELECT S.rating, MIN (S.age) FROM Sailors S WHERE S.age >= 18 GROUP BY S.rating HAVINGCOUNT (*) >= 2 SELECT T.rating, T.minimum FROM (SELECT S.rating, MIN(S.age) AS minimum, COUNT (*) AS ratingcount FROM Sailors S WHERE S.age > 18 GROUP BY S.rating) AS T WHERE T.ratingcount >= 2

16. Null Values • Field values in a tuple are sometimes unknown(e.g., a rating has not been assigned) or inapplicable(e.g., no spouse’s name). • SQL provides a special value null for such situations. • The presence of null complicates many issues. E.g.: • Special operators needed to check if value is/is not null. • Is rating>8 true or false when rating is equal to null? What about AND, OR and NOT connectives? • We need a 3-valued logic(true, false and unknown). • Meaning of constructs must be defined carefully. (e.g., WHERE clause eliminates rows that don’t evaluate to true.) • New operators (in particular, outer joins) possible/needed.

17. Integrity Constraints (Review) • An IC describes conditions that every legal instance of a relation must satisfy. • Inserts/deletes/updates that violate IC’s are disallowed. • Can be used to ensure application semantics (e.g., sid is a key), or prevent inconsistencies (e.g., sname has to be a string, age must be < 200) • Types of IC’s: Domain constraints, primary key constraints, foreign key constraints, general constraints. • Domain constraints: Field values must be of right type. Always enforced.

18. CREATE TABLE Reserves ( sname CHAR(10), bid INTEGER, day DATE, PRIMARY KEY (bid,day), CONSTRAINTnoInterlakeRes CHECK (`Interlake’ <> ( SELECT B.bname FROM Boats B WHERE B.bid=bid))) CREATE TABLE Sailors ( sid INTEGER, sname CHAR(10), rating INTEGER, age REAL, PRIMARY KEY (sid), CHECK ( rating >= 1 AND rating <= 10 ) General Constraints • Useful when more general ICs than keys are involved. • Can use queries to express constraint. • Constraints can be named.

19. Constraints Over Multiple Relations CREATE TABLE Sailors ( sid INTEGER, sname CHAR(10), rating INTEGER, age REAL, PRIMARY KEY (sid), CHECK ( (SELECT COUNT (S.sid) FROM Sailors S) + (SELECT COUNT (B.bid) FROM Boats B) < 100 ) Number of boats plus number of sailors is < 100 • Awkward and wrong! • If Sailors is empty, the number of Boats tuples can be anything! • ASSERTION is the right solution; not associated with either table. CREATE ASSERTION smallClub CHECK ( (SELECT COUNT (S.sid) FROM Sailors S) + (SELECT COUNT (B.bid) FROM Boats B) < 100 )

20. Triggers • Trigger: procedure that starts automatically if specified changes occur to the DBMS • Three parts: • Event (activates the trigger) • Condition (tests whether the triggers should run) • Action (what happens if the trigger runs)

21. Triggers: Example (SQL:1999) CREATE TRIGGER youngSailorUpdate AFTER INSERT ON SAILORS REFERENCING NEW TABLE NewSailors FOR EACH STATEMENT INSERT INTO YoungSailors(sid, name, age, rating) SELECT sid, name, age, rating FROM NewSailors N WHERE N.age <= 18

22. Exercises Sailors ( sid, sname, rating, age) Boats (bid, bname, color ) Reserve( sid, bid, date)

23. Find snames whose rating>10 and who reserved some red boat. Solution 1: 3-way join. Solution 2: use IN: … WHERE S.rating>10 AND S.sid IN Solution 3: use EXISTS … WHERE S.rating>10 AND EXISTS … Solution 4: use INTERSECT … WHERE S.sid IN ( … INTERSECT … )

24. Find snames whose rating>10 or who reserved some red boat. Simple modifications from the above four cases. In particular: INTERSECT  UNION Solution 5: union on snames.

25. Find sids with the highest rating. Solution 1: use MAX … S.rating = (SELECT MAX(S2.rating) …) Solution 2: use ALL … S.rating >= ALL ( SELECT S2.rating … ) Solution 3: use EXCEPT … EXCEPT ( … S.sid < ANY (SELECT S2.rating) … )

26. Find bids not reserved by sid=5. Solution 1: use EXCEPT Solution 2: use NOT EXISTS Find sids who reserved all boats. Find sids who reserved all red boats.

27. Find sids who made exactly one reservation. Solution 1: use NOT EXISTS not exists another reserve record with same sid. Solution 2: use UNIQUE … WHERE UNIQUE (… WHERE R.sid=R2.sid) Solution 3: use GROUPBY & HAVING

28. More examples on groupby. - For each sid who reserved, find # reservations.- For each sid who reserved at least 5 times, find # different boats she reserved.- For each sid who reserved, find # different colors she reserved.- For each bid which was reserved by at least two different sailors, find avg rating of reservers.

29. More examples on nested query as source. • Find the ratings for which the average age of sailors is minimum over all ratings.- Find the sailor sids who made more than average number of reservations per sailor. * choice 1: counting only those in Reserves. * choice 2: including those who did not reserve.

30. Exercise 5.2 Suppliers (sid: integer, sname: string, address: string) Parts (pid: integer, pname: string, color: string) Catalog (sid: integer, pid: integer, cost: real)

31. 4. Find the pnames of parts supplied by Acme Widget Suppliers and no one else. SELECT P.pname FROM Parts P, Catalog C, Suppliers S WHERE P.pid = C.pid AND C.sid = S.sid AND S.sname = `Acme Widget Suppliers' AND NOT EXISTS ( SELECT * FROM Catalog C1, Suppliers S1 WHERE P.pid = C1.pid AND C1.sid = S1.sid AND S1.sname <> `Acme Widget Suppliers' )

32. 3. Find the snames of suppliers who supply every red part. SELECT S.sname FROM Suppiers S WHERE NOT EXISTS ( (SELECT P.pid FROM Parts P WHERE P.color = `red' ) EXCEPT (SELECT C.pid FROM Catalog C WHERE C.sid = S.sid) )

33. 6. For each part, find the sname of the supplier who charges the most for that part. SELECT P.pid, S.sname FROM Parts P, Suppliers S, Catalog C WHERE C.pid = P.pid AND C.sid = S.sid AND C.cost = (SELECT MAX (C1.cost) FROM Catalog C1 WHERE C1.pid = P.pid)

34. 7. Find the sids of suppliers who supply only red parts. SELECT DISTINCT C.sid FROM Catalog C WHERE NOT EXISTS ( SELECT * FROM Catalog C1, Parts P WHERE C1.pid = C.pid AND C1.pid = P.pid AND P.color <> `red' )

35. 10. For every supplier that only supplies green parts, print the name of the supplier and the total number of parts that she supplies. SELECT S.sname, COUNT(*) as PartCount FROM Suppliers S, Parts P, Catalog C WHERE P.pid = C.pid AND C.sid = S.sid GROUP BY S.sname, S.sid HAVING EVERY (P.color='green')

36. Exercise 5.7 Emp (eid: integer, ename: string, age: integer, salary: real) Works (eid: integer, did: integer, pct_time: integer) Dept (did: integer, budget: real, managerid: integer)

37. 1. Define a table constraint on Emp that will ensure that every employee makes at least $10,000. CREATE TABLE Emp ( eid INTEGER, ename CHAR(10), age INTEGER , salary REAL, PRIMARY KEY (eid), CHECK ( salary >= 10000 ))

38. 3. Define an assertion on Dept that will ensure that all managers have age > 30. Compare this assertion with the equivalent table constraint. Explain which is better. CREATE TABLE Dept ( did INTEGER, budget REAL, managerid INTEGER , PRIMARY KEY (did) ) CREATE ASSERTION managerAge CHECK ( (SELECT MIN(E.age) FROM Emp E, Dept D WHERE E.eid = D.managerid ) > 30 )

39. Exercise 5.10 Emp (eid: integer, ename: string, age: integer, salary: real) Works (eid: integer, did: integer, pct_time: integer) Dept (did: integer, budget: real, managerid: integer) • An employee can work in more than one department; the pct_time field of the Works relation shows the percentage of time that a given employee works in a given department. • Write integrity constraints or triggers.

40. 2. Every manager must also be an employee. CREATE ASSERTION ManagerIsEmployee CHECK ( ( SELECT COUNT (*) FROM Dept D WHERE D.managerid NOT IN (SELECT eid FROM Emp)) = 0)

41. 3. The total percentage of all appointments for an employee must be under 100%. CREATE TABLE Works ( eid INTEGER, did INTEGER, pct time INTEGER, PRIMARY KEY (eid, did), CHECK ( (SELECT COUNT (W.eid) FROM Works W GROUP BY W.eid HAVING Sum(pct time) > 100) = 0))

42. 5. Whenever an employee is given a raise, the manager’s salary must be increased to be at least as much. CREATE TRIGGER GiveRaise AFTER UPDATE ON Emp WHEN old.salary < new.salary FOR EACH ROW BEGIN UPDATE Emp M SET M.Salary = new.salary WHERE M.salary < new.salary AND M.eid IN (SELECT D.mangerid FROM Emp E, Works W, Dept D WHERE E.eid = new.eid AND E.eid = W.eid AND W.did = D.did); END