Database Management Systems, R. Ramakrishnan and J. Gehrke 1SQL: Queries, Programming, TriggersChapter 5Database Management Systems, R. Ramakrishnan and J. Gehrke 2Example Instancessid sname rating age22 dustin 7 45.031 lubber 8 55.558 rusty 10 35.0sid sname rating age28 yuppy 9 35.031 lubber 8 55.544guppy535.058rusty1035.0sidbidday2210110/10/965810311/12/96R1S1S2❖ We will use these instances of the Sailors and Reserves relations in our examples.❖ If the key for the Reserves relation contained only the attributes sid and bid, how would the semantics differ?Database Management Systems, R. Ramakrishnan and J. Gehrke 3Basic SQL Query❖ relation-list A list of relation names (possibly with a range-variable after each name).❖ target-listA list of attributes of relations in relation-list❖ qualification Comparisons (Attr op const or Attr1 opAttr2, 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! SELECT [DISTINCT] target-listFROM relation-listWHERE qualification<>=≤≥≠,,,,,Database Management Systems, R. Ramakrishnan and J. Gehrke 4Conceptual 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.Database Management Systems, R. Ramakrishnan and J. Gehrke 5Example of Conceptual EvaluationSELECT S.snameFROM Sailors S, Reserves RWHERE S.sid=R.sid AND R.bid=103(sid)snameratingage(sid)bidday22dustin745.02210110/10/9622dustin745.05810311/12/9631lubber855.52210110/10/9631lubber855.55810311/12/9658rusty1035.02210110/10/9658rusty1035.05810311/12/96Database Management Systems, R. Ramakrishnan and J. Gehrke 6A 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.snameFROM Sailors S, Reserves RWHERE S.sid=R.sid AND bid=103SELECT snameFROM Sailors, Reserves WHERE Sailors.sid=Reserves.sidAND bid=103It is good style,however, to userange variablesalways!ORDatabase Management Systems, R. Ramakrishnan and J. Gehrke 7Find sailors who’ve reserved at least one boat❖ Would adding DISTINCT to this query make a difference?❖ What is the effect of replacing S.sid by S.sname in the SELECT clause? Would adding DISTINCT to this variant of the query make a difference?SELECT S.sidFROM Sailors S, Reserves RWHERE S.sid=R.sidDatabase Management Systems, R. Ramakrishnan and J. Gehrke 8Expressions and Strings❖ 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.❖ AS and = 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. SELECT S.age, age1=S.age-5, 2*S.age AS age2FROM Sailors SWHERE S.sname LIKE ‘B_%B’Database Management Systems, R. Ramakrishnan and J. Gehrke 9Find sid’s of sailors who’ve reserved a red or a green boat❖ UNION: Can be used to compute the union of any two union-compatible sets oftuples (which are themselves the result of SQL queries).❖ If we replace OR by AND in the first version, what do we get?❖ Also available: EXCEPT(What do we get if we replace UNION by EXCEPT?)SELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bidAND (B.color=‘red’ OR B.color=‘green’)SELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid ANDR.bid=B.bidAND B.color=‘red’UNIONSELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid ANDR.bid=B.bidANDB.color=‘green’Database Management Systems, R. Ramakrishnan and J. Gehrke 10Find sid’s of sailors who’ve reserved a red and a green boat❖ INTERSECT: Can be used to compute the intersection of any two union-compatible sets of tuples. ❖ Included in the SQL/92 standard, but some systems don’t support it.❖ Contrast symmetry of the UNION and INTERSECT queries with how much the other versions differ.SELECT S.sidFROM Sailors S, Boats B1, Reserves R1,Boats B2, Reserves R2WHERE S.sid=R1.sid AND R1.bid=B1.bidAND S.sid=R2.sid AND R2.bid=B2.bidAND (B1.color=‘red’ AND B2.color=‘green’)SELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid ANDR.bid=B.bidAND B.color=‘red’INTERSECTSELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid ANDR.bid=B.bidANDB.color=‘green’Key field!Database Management Systems, R. Ramakrishnan and J. Gehrke 11Nested Queries❖ A very powerful feature of SQL: a WHERE clause can itself contain an SQL query! (Actually, so can FROMand HAVING clauses.)❖ To find sailors who’ve not reserved #103, use NOT IN.❖ To understand semantics of nested queries, think of a nested loopsevaluation: For each Sailors tuple, check the qualification by computing the subquery.SELECT S.snameFROM Sailors SWHERE S.sid IN (SELECT R.sidFROM Reserves RWHERE R.bid=103)Find names of sailors who’ve reserved boat #103:Database Management Systems, R. Ramakrishnan and J. Gehrke 12Nested Queries with Correlation❖ EXISTS is another set comparison operator, like IN. ❖ If UNIQUE is 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.SELECT S.snameFROM Sailors SWHERE EXISTS (SELECT *FROM Reserves RWHERE R.bid=103 AND S.sid=R.sid)Find names of sailors who’ve reserved boat #103:Database Management Systems, R. Ramakrishnan and J. Gehrke 13More on Set-Comparison Operators❖ We’ve already seen IN, EXISTS and UNIQUE. Can also use NOT IN, NOT EXISTS and NOT UNIQUE.❖ Also available: op ANY, op ALL, op IN❖ Find sailors whose rating is greater than that of some sailor called Horatio:><=≥≤≠,,,,,SELECT *FROM Sailors SWHERE