Rewriting Intersect Queries Using In SELECT S sid

Rewriting Intersect Queries Using In SELECT S. sid FROM Sailors S, Boats B, Reserves R WHERE S. sid = R. sid and R. bid = B. bid and B. color = ‘red’ INTERSECT SELECT S. sid FROM Sailors S, Boats B, Reserves R WHERE S. sid = R. sid and R. bid = B. bid and B. color = ‘green’; 1

Rewriting Intersect Queries Using In SELECT 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 IN (SELECT S 2. sid FROM Sailors S 2, Boats B 2, Reserves R 2 WHERE S. sid = R. sid and R. bid = B. bid and B. color = ‘green’); 2

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. 3

Suppliers from A who supply All Parts from B sno pno S 1 S 1 S 2 S 3 S 4 P 1 P 2 P 3 P 4 P 1 P 2 P 2 P 4 A pno pno P 2 P 4 P 1 P 2 P 4 B 1 B 2 B 3 sno S 1 S 2 S 3 S 4 A/B 1 sno S 1 S 4 S 1 A/B 2 A/B 3 4

Sailors who Reserved all Boats • To find the Sailors who reserved all boats: ( sid, bid Reserves)/( bid Boats) • Division can be expressed using other relational algebra operators. 5

Division in SQL (1) 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 6

Division in SQL (2) SELECT S. sname FROM Sailors S WHERE NOT EXISTS((SELECT FROM EXCEPT (SELECT FROM WHERE B. bid Boats B) R. bid Reserves R R. sid = S. sid)); 7

Aggregation 8

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

Some Examples SELECT COUNT(*) FROM Sailors S SELECT AVG(S. age) FROM Sailors S WHERE S. rating=10 10

Find name and age of oldest Sailor SELECT S. sname, MAX(S. age) FROM Sailors S SELECT S. sname, S. age FROM Sailors S WHERE S. age = (SELECT MAX(S 2. age) FROM Sailors S 2) Wrong!! Right!! 11

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? 12

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 13

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 14

Find Average Age for each Rating SELECT S. rating, AVG(S. age) FROM Sailors S GROUP BY S. rating 15

Find the number of Reservations of Each Red Boat SELECT FROM WHERE GROUP BY B. bid, COUNT(*) as count Boats B, Reserves R R. bid=B. bid and B. color=‘red’ B. bid What would happen if we put the condition about the color in the HAVING clause? 16

Age of Youngest Sailor that is over 18, for each Rating with at least 2 such Sailors SELECT FROM WHERE GROUP BY HAVING S. rating, MIN(S. age) Sailors S S. age >= 18 S. rating COUNT(*) > 1 17

Age of Youngest Sailor that is over 18, for each Rating with at least 2 Sailors (of any Age) SELECT FROM WHERE GROUP BY HAVING S. rating, MIN(S. age) Sailors S S. age >= 18 S. rating 1 < (SELECT COUNT(*) FROM Sailors S 2 WHERE S. rating = S 2. rating) 18

More Options 19

Sorting Results • Results can be sorted using the ORDER BY clause • Here are sailors who ordered boat 103, ordered by their names: SELECT FROM WHERE ORDER BY S. sname Sailors S, Reserves R S. sid = R. sid and R. bid = 103 S. sname 20

Outer Join • How can we find sailor names with their reservation numbers if we don’t want to lose information about sailors who did not reserve any boats? ? SELECT S. sname, R. bid FROM Sailors S, Reserves R WHERE S. sid = R. sid(+) • The (+) must follow column that we allow to be null. 21

Views • A View is a query that looks like a table and can be used as a table. CREATE SELECT FROM WHERE OR REPLACE VIEW Sailors. Boats S. sname, B. bname, B. color Sailors S, Reserves R, Boats B S. sid = R. sid and R. bid = B. bid; SELECT sname FROM Sailor. Boats WHERE color = ‘red’; 22

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 * Pseudo-column Grades which is equal to Login = User; the user name. 23

Views for Complex Queries • Find those ratings for which the average is the minimum over all ratings CREATE SELECT FROM WHERE OR REPLACE VIEW Avg. Age. Ratings S. rating, AVG(S. age) as avgage Sailors S S. rating; SELECT rating, avgage FROM Ave. Age. Ratings WHERE avgage = (SELECT MIN(avgage) FROM Ave. Age. Ratings) 24

Sub-queries in FROM, instead of Views SELECT Temp. rating, Temp. avgage FROM (SELECT S. rating, AVG(S. age) as avgage FROM Sailors S GROUP BY S. rating) as Temp WHERE Temp. avgage = (SELECT MIN(Temp. avgage) FROM Temp) 25

Delete and Update 26

Deleting Tuples • The basic form of a delete statement is: DELETE FROM Table. Name WHERE Condition; 27

Examples Delete Sailors with rating less than 3: DELETE FROM Sailors WHERE rating < 3; Delete Sailors with the minimum rating: DELETE FROM Sailors S 1 WHERE S 1. rating = (SELECT MIN(S 2. rating) FROM Sailors S 2) 28

Updating Tuples • The basic form of an update statement is: UPDATE Table. Name SET Column 1 = Value 1, … Column. N = Value. N WHERE Condition; 29

Example Update boat 13: color to red and name to voyager UPDATE Boats SET color = ‘red’, bname = ‘Voyager’ WHERE bid = 13; 30

Another Example Update rating of Rusty to be the maximum rating of any sailor UPDATE Sailors SET rating = (SELECT MAX(rating) FROM Sailors) WHERE sname = ‘Rusty’; Note: When updating with a subquery, the subquery must return one value only! 31