relational algebra

Database Management Systems, R. Ramakrishnan and J. Gehrke 1 Relational Algebra and SQL Johannes Gehrke [email protected] Database Management Systems, R. Ramakrishnan and J. Gehrke 3 Formal Relational Query Languages v Two mathematical Query Languages form the basis for “real” languages (e.g. SQL), and for implementation: – Relational Algebra : More operational , very useful for representing execution plans. – Relational Calculus : Lets users describe what they want, rather than how to compute it. ( Non- operational, declarative .) Database Management Systems, R. Ramakrishnan and J. Gehrke 28 Find sid’s of sailors who’ve reserved a red or a green boat SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND (B.color=‘red’ OR B.color=‘green’) SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’ UNIONSELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘green’ Database Management Systems, R. Ramakrishnan and J. Gehrke 29 What does this query compute? SELECT S.sid FROM Sailors S, Boats B1, Reserves R1, Boats B2, Reserves R2 WHERE S.sid=R1.sid AND R1.bid=B1.bid AND S.sid=R2.sid AND R2.bid=B2.bid AND B1.color=‘red’ AND B2.color=‘green’ Database Management Systems, R. Ramakrishnan and J. Gehrke 32 Nested Queries (with Correlation) SELECT S.sname FROM Sailors S WHERE EXISTS ( SELECT FROM Reserves R WHERE R.bid=103 AND S.sid =R.sid) Find names of sailors who have reserved boat 103: Database Management Systems, R. Ramakrishnan and J. Gehrke 33 Nested Queries (with Correlation) SELECT S.sname FROM Sailors S WHERE NOT EXISTS ( SELECT FROM Reserves R WHERE R.bid=103 AND S.sid =R.sid) Find names of sailors who have not reserved boat 103: Database Management Systems, R. Ramakrishnan and J. Gehrke 34 Division in SQL 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)) Find sailors who’ve reserved all boats Database Management Systems, R. Ramakrishnan and J. Gehrke 40 GROUP BY SELECT [DISTINCT] target-list FROM relation-list [WHERE condition ] GROUP BY grouping-list Find the age of the youngest sailor for each rating level SELECT S.rating, MIN( S.Age) FROM Sailors S GROUP BY S.rating Database Management Systems, R. Ramakrishnan and J. Gehrke 42 Find the age of the youngest sailor with age 18, for each rating with at least one such sailor SELECT S.rating, MIN (S.age) FROM Sailors S WHERE S.age >= 18 GROUP BY S.rating sid Database Management Systems, R. Ramakrishnan and J. Gehrke 43 Are These Queries Correct? SELECT MIN( S.Age) FROM Sailors S GROUP BY S.rating SELECT S.name, S.rating, MIN( S.Age) FROM Sailors S GROUP BY S.rating Database Management Systems, R. Ramakrishnan and J. Gehrke 51 Find the average age for each rating, and order results in ascending order on avg. age SELECT S.rating, AVG (S.age) AS avgage FROM Sailors S GROUP BY S.rating ORDER BY avgage ™ ORDER BY can only appear in top-most query • Otherwise results are unordered! Database Management Systems, R. Ramakrishnan and J. Gehrke 55 General Constraints v Useful when more general ICs than keys are involved v Can use queries to express constraint v Constraints can be named CREATE TABLE Reserves ( sname CHAR(10), bid INTEGER, day DATE, PRIMARY KEY (bid,day), CONSTRAINT noInterlakeRes CHECK ( Interlake’ <> ( SELECT B.bname FROM Boats B WHERE B.bid=bid))) Database Management Systems, R. Ramakrishnan and J. Gehrke 57 Summaryv The relational model has rigorously defined query languages that are simple and powerful. v Relational algebra is more operational; useful as internal representation for query evaluation plans. v Several ways of expressing a given query; a query optimizer should choose the most efficient version. v SQL is the lingua franca for accessing database systems today.