1 / 13

SQL Review

SQL Review. Example Schema. We will use these table definitions in our examples. Sailors ( sid : integer , sname : string , rating : integer , age : real ) Boats ( bid : integer , bname : string , color : string ) Reserves ( sid : integer , bid : integer , day : date ).

arnold
Download Presentation

SQL Review

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Review

  2. Example Schema We will use these table definitions in our examples. Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date) Make reservations

  3. Basic SQL Query • relation-list A list of relation names • target-list A list of attributes of relations in relation-list • qualification Comparisons (“Attrop 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. SELECT [DISTINCT] target-list FROM relation-list WHERE qualification

  4. Semantics of SQL QUERY PROCESSOR SELECT [DISTINCT] target-list FROM relation-list WHERE qualification target-list Define search space R1 × R2 × R3 × · · · relation-list qualification This strategy is probably the least efficient way to compute a query! An optimizer will find more efficient strategies to compute the same answers. ˂, ˃, ≤, ≥, =, ≠ Select rows Select columns Query Result Projection

  5. Example of Conceptual Evaluation Range variable SELECTS.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND R.bid=103 Reservation Sailors

  6. Example of Conceptual Evaluation SELECTS.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND R.bid=103 S×R Remove Irrelevant columns Disqualified Answer

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

  8. Aggregate Operators Significant extension of relational algebra

  9. Aggregate Operators Count the number of sailors Find the name of sailors with the highest rating SELECT COUNT (*) FROM Sailors S Compute maximum rating SELECTS.sname FROM Sailors S WHERES.rating= (SELECT MAX(S2.rating) FROM Sailors S2) Find the average age of sailors with a rating of 10 Find the average of the distinct ages of sailors with a rating of 10 SELECT AVG (S.age) FROM Sailors S WHERES.rating=10 Count the number of distinct ratings of sailors called “Bob” SELECT AVG (DISTINCT S.age) FROM Sailors S WHERES.rating=10 SELECT COUNT (DISTINCTS.rating) FROM Sailors S WHERE S.sname=‘Bob’

  10. GROUP BY and HAVING (1) • So far, we’ve applied aggregate operators to all (qualifying) tuples. • Sometimes, we want to apply them to each of several groups of tuples. Relation Aggregator Qualifier 32 12 Aggregator Group 1 Relation 9 Group 2 Aggregator 11 Aggregator Group 3

  11. Queries With GROUP BYand HAVING SELECT [DISTINCT] target-list FROM relation-list WHERE qualification GROUP BY grouping-list HAVING group-qualification MIN(Attribute) GROUP BY Output a table HAVING 12 Qualifier selecting groups SELECT FROM WHERE Aggregator Group 1 9 Aggregator Group 2 Group 3

  12. Find the age of the youngest sailor with age ≥ 18, for each rating with at least 2 such sailors Input relation SELECT S.rating, MIN (S.age) FROM Sailors S WHERE S.age>= 18 GROUP BY S.rating HAVING COUNT (*) > 1 Sailors age Answer Disqualify Only one group satisfies HAVING 4rating groups Only S.rating and S.age are mentioned in SELECT

  13. Summary • SQL was an important factor in the early acceptance of the relational model; more natural than earlier, procedural query languages. • Relationally complete; in fact, significantly more expressive power than relational algebra. • Even queries that can be expressed in RA can often be expressed more naturally in SQL. • Many alternative ways to write a query; optimizer should look for most efficient evaluation plan. • In practice, users need to be aware of how queries are optimized and evaluated for best results.

More Related