Advanced SQL Queries 1 Example Tables Used Boats

Advanced SQL Queries 1

Example Tables Used Boats Sailors sid sname rating age 22 Dustin 7 45. 0 31 Lubber 8 55. 5 58 Rusty 10 35. 0 bid bname color 101 Nancy red 103 Gloria green Reserves sid bid day 22 101 10/10/04 58 103 11/12/04 2

Rewriting Minus Queries Name and id of sailors that has reserved at least one red boat and has never reserved green boat SELECT S. sname, S. sid FROM Sailors S, Boats B, Reserves R WHERE S. sid = R. sid and R. bid = B. bid and B. color = ‘red’ MINUS SELECT S. sname, S. sid FROM Sailors S, Boats B, Reserves R WHERE S. sid = R. sid and R. bid = B. bid and B. color = ‘green’; 3

Rewriting Minus Queries Using Not In SELECT S. sname, S. sid FROM Sailors S, Boats B, Reserves R WHERE S. sid = R. sid and R. bid = B. bid and B. color = ‘red’ and S. sid NOT IN ( SELECT R. sid FROM Boats B, Reserves R WHERE R. bid = B. bid and B. color = ‘green’); 4

Rewriting Minus Queries Using Not Exists SELECT S. sname, S. sid FROM Sailors S, Boats B, Reserves R WHERE S. sid = R. sid and R. bid = B. bid and B. color = ‘red’ and NOT EXISTS ( SELECT * FROM Boats B, Reserves R WHERE R. sid = S. sid and R. bid = B. bid and B. color = ‘green’); 5

Division • Consider: A(X, Y) and B(Y). Then A B = • In general, we require that the set of fields in B be contained in those of A. 6

Suppliers from A who supply All Parts from B (1) sno pno S 1 P 1 S 1 P 2 S 1 P 3 S 1 P 4 S 2 P 1 S 2 P 2 S 3 P 2 S 4 P 4 A sno pno P 2 = B 1 7

Suppliers from A who supply All Parts from B (1) sno pno S 1 P 1 S 1 P 2 S 1 P 3 S 1 P 4 S 2 P 1 S 2 P 2 S 3 P 2 S 4 P 4 A sno pno P 2 B 1 = S 1 S 2 S 3 S 4 8

Suppliers from A who supply All Parts from B (2) sno pno S 1 P 1 S 1 P 2 S 1 P 3 S 1 P 4 S 2 P 1 S 2 P 2 S 3 P 2 S 4 P 4 A sno pno P 2 P 4 = B 2 9

Suppliers from A who supply All Parts from B (2) sno pno S 1 P 1 S 1 P 2 S 1 P 3 S 1 P 4 S 2 P 1 S 2 P 2 S 3 P 2 S 4 P 4 A sno pno P 2 P 4 = S 1 S 4 B 2 10

Suppliers from A who supply All Parts from B (3) sno pno S 1 P 1 S 1 P 2 S 1 P 3 S 1 P 4 S 2 P 1 S 2 P 2 S 3 P 2 S 4 P 4 A sno pno P 1 P 2 P 4 = B 3 11

Suppliers from A who supply All Parts from B (3) sno pno S 1 P 1 S 1 P 2 S 1 P 3 S 1 P 4 S 2 P 1 S 2 P 2 S 3 P 2 S 4 P 4 A sno pno P 1 P 2 P 4 = S 1 B 3 12

Sailors who Reserved all Boats ( sid, bid Reserves) ( bid Boats) Sailor S whose "set of boats reserved" contains the "set of all boats" 13

Division in SQL (1) Sailor S for which there does not exist a boat B in Boats that he did not reserve SELECT sid FROM Sailors S WHERE NOT EXISTS (SELECT B. bid FROM Boats B WHERE B. bid NOT IN (SELECT R. bid FROM Reserves R WHERE R. sid = S. sid)); 14

Division in SQL (2) Sailor S for which there does not exist a boat B in Boats that he did not reserve SELECT S. sid 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)) 15

Division in SQL (3) Sailor S for which there does not exist a boat B in Boats for which there is no reservation in Reserves SELECT S. sid FROM Sailors S WHERE NOT EXISTS((SELECT FROM MINUS (SELECT FROM WHERE B. bid Boats B) R. bid Reserves R R. sid = S. sid)); 16

Aggregation 17

Aggregate Operators • The aggregate operators available in SQL are: – COUNT(*) – COUNT([DISTINCT] A) – SUM([DISTINCT] A) – AVG([DISTINCT] A) – MAX(A) – MIN(A) • NULL values are ignored 18

Some Examples SELECT COUNT(*) FROM Sailors S SELECT COUNT(sid) FROM Sailors S SELECT AVG(S. age) FROM Sailors S WHERE S. rating=10 SELECT COUNT(distinct color) FROM Boats 19

Find Average Age for each Rating • So far, aggregation has been applied to all tuples that passed the WHERE clause test. • How can we apply aggregation to groups of tuples? 20

Basic SQL Query SELECT FROM WHERE GROUP BY HAVING [Distinct] target-list relation-list condition grouping-list group-condition; • target-list: Fields appearing in grouping-list and aggregation operators • group-condition: Can only constrain attributes appearing in grouping-list and aggregation operators 21

Evaluation 1. Compute cross product of relations in FROM 2. Tuples failing WHERE are thrown away 3. Tuples are partitioned into groups by values of grouping-list attributes 4. The group-condition is applied to eliminate groups 5. One answer in generated for each group 22

Find Average Age for each Rating SELECT AVG(age) FROM Sailors GROUP BY rating; 23

Sailors sid sname rating age 22 Dustin 7 45. 0 31 Lubber 8 55. 5 63 Fluffy 7 44. 0 58 Rusty 10 35. 0 78 Morley 7 31. 0 63 Fluffy 7 44. 0 31 Lubber 8 55. 5 78 Morley 7 31. 0 58 Rusty 10 35. 0 84 Popeye 10 33. 0 40 55. 5 34 24

Find name and age of oldest Sailor SELECT S. sname, MAX(S. age) FROM Sailors S GROUP BY S. sname Wrong! Wrong: we don’t obtain what we want 25

Find name and age of oldest Sailor SELECT S. sname, S. age FROM Sailors S WHERE S. age = (SELECT MAX(S 2. age) FROM Sailors S 2) Right!! How else can this be done? Hint: >= ALL SELECT S. sname, S. age FROM Sailors S WHERE S. age >= ALL (SELECT S 2. age FROM Sailors S 2) 26

What does this return? SELECT FROM WHERE GROUP BY B. bid, COUNT(*) Boats B, Reserves R R. bid=B. bid and B. color=‘red’ B. bid Tuples: (id of reserved red boat, number of reservations of the red boat) What would happen if we put the condition about the color in the HAVING clause? 27

What would happen if we put the condition about the color in the HAVING clause? We have also to put the color in the grouping list! SELECT B. bid, COUNT(*) FROM Boats B, Reserves R WHERE R. bid=B. bid GROUP BY B. bid, B. color HAVING B. color=‘red’ 28

Names of Boats that were not Reserved on more than 5 days SELECT bname FROM Boats B, Reserves R WHERE R. bid=B. bid GROUP BY bid, bname HAVING count(DISTINCT day) <= 5 Can we move the condition in the HAVING to the WHERE? Aggregate functions are not allowed in WHERE 29

The Color for which there are the most boats SELECT FROM GROUP BY HAVING color Boats B color max(count(bid)) What is wrong with this? How would you fix it? 30

The Color for which there are the most boats SELECT FROM GROUP BY HAVING color Boats B color count(bid) >= ALL (SELECT count(bid) FROM Boats GROUP BY Color) 31

Aggregation Instead of Exists • Aggregation can take the place of exists. • Example: SELECT color FROM Boats B 1 WHERE NOT EXISTS( SELECT * FROM Boats B 2 WHERE B 1. bid <> B 2. bid AND B 1. color=B 2. color) The color of the boat there is no other boat of this color 32

Aggregation Instead of Exists SELECT FROM GROUP BY HAVING color Boats B 1 color count(bid) = 1 33

Sub-queries and Views 34

A Complex Query • We would like to create a table containing 3 columns: – Sailor id – Sailor age – Age of the oldest Sailor How can this be done? 35

Attempt 1 SELECT S. sid, S. age, MAX(S. age) FROM Sailors S; Why is this wrong? 36

Attempt 2 SELECT S. sid, S. age, MAX(S. age) FROM Sailors S GROUP BY S. sid, S. age; Why is this wrong? Each group contains only one tuple 37

Solution 1: Sub-query in FROM SELECT S. sid, S. age, M. mxage FROM Sailors S, (SELECT MAX(S 2. age) as mxage FROM Sailors S 2) M; • We can put a query in the FROM clause instead of a table • The sub-query in the FROM clause must be renamed with a range variable (M in this case). 38

Solution 2: Sub-query in SELECT S. sid, S. age, (SELECT MAX(S 2. age) FROM Sailors S 2) FROM Sailors S; • A sub-query in the SELECT clause must return at most one value for each row returned by the outer query. 39

Another Example of a Sub-query in SELECT S. sid, S. age, (SELECT MAX(S 2. age) FROM Sailors S 2 WHERE S 2. age<S. age) FROM Sailors S; • What does this query return? For each sailor S, the age of the oldest sailor among the sailors younger than S • Note the use of S (defined in the outer query) in the sub-query. 40

Another Example of a Sub-query in FROM? ? SELECT S. sid, S. age, M. mxage FROM Sailors S, (SELECT MAX(S 2. age) as mxage FROM Sailors S 2 WHERE S 2. age<S. age) M; Why is this wrong? We cannot refer to S. age in a sub-query in FROM 41

Solution 3: Create a Table CREATE TABLE Max. Age as SELECT MAX(S. age) as mxage FROM Sailors S; MUST Rename! SELECT S. sid, S. age, M. mxage FROM Sailors S, Max. Age M; Problem: how to update Max. Age table? 42

Views • A view is a "virtual table" defined using a query • You can use a view as if it were a table, even though it doesn't contain data • The view is computed every time that it is referenced 43

Advantages and Disadvantages • Advantages: – no memory used for views – update of table does not require updating views – gives query processor more choices for optimizing • Disadvantages: – must be recomputed every time used – if tables that view uses are dropped, view data is lost 44

Solution 4: Views • A View is a query that looks like a table and can be used as a table. CREATE OR REPLACE VIEW Max. Age as SELECT MAX(S. age) as mxage FROM Sailors S; SELECT S. sid, S. age, M. mxage FROM Sailors S, Max. Age M; MUST Rename! 45

Another Example of Views CREATE OR REPLACE VIEW Max. Ages AS SELECT S 1. sid, S 2. age AS mxage FROM Sailors S 1, Sailors S 2 WHERE S 2. age = (SELECT MAX(S 3. age) FROM Sailors S 3 WHERE S 3. age < S 1. age); SELECT S. sid, S. age, M. mxage FROM Sailors S, Max. Ages M WHERE S. sid = M. sid; 46

Views For Restricting Access • Suppose that we have a table: Grades(Login, Exercise, Grade) • We would like a user to only be able to see his own grades. We create the following view and grant privileges to query the view (not the underlying table) CREATE SELECT FROM WHERE OR REPLACE VIEW User. Grades as * The system Grades defines the user Login = User; name. 47