SQL: The Query Language Part 1ReviewQuery OptimizationRelational Query LanguagesThe SQL Query LanguageExample DatabaseReview: SQL DDLThe SQL DMLQuerying Multiple RelationsBasic SQL QueryQuery SemanticsFind sailors who’ve reserved at least one boatAbout Range VariablesArithmetic ExpressionsString ComparisonsFind sid’s of sailors who’ve reserved a red or a green boatFind sid’s of sailors who’ve reserved a red and a green boatSlide 19Slide 20Find sid’s of sailors who have not reserved a boatNested Queries: INNested Queries: NOT INNested Queries with CorrelationMore on Set-Comparison OperatorsA Tough OneSummarySQL: The Query Language Part 1R &G - Chapter 5 The important thing is not tostop questioning.Albert EinsteinReview•Relational Algebra (Operational Semantics)•Given a query, how to mix and match the relational algebra operators to answer it•Used for query optimization•Relational Calculus (Declarative Semantics)•Given a query, what do I want my answer set to include?•Algebra and safe calculus are simple and powerful models for query languages for relational model –Have same expressive power•SQL can express every query that is expressible in relational algebra/calculus. (and more)Query OptimizationSQL QueryRel. Algebra Query 1Rel. Algebra Query 2Rel. Algebra Query n...Pick the cheapest oneRelational Query Languages•Two sublanguages:–DDL – Data Definition Language•Define and modify schema (at all 3 levels)–DML – Data Manipulation Language•Queries can be written intuitively.•DBMS is responsible for efficient evaluation.–The key: precise semantics for relational queries.–Optimizer can re-order operations, without affecting query answer.–Choices driven by cost model: how many disk accesses; how much CPU?The SQL Query Language•The most widely used relational query language. •Standardized (although most systems add their own “special sauce” -- including PostgreSQL)•We will study SQL92 -- a basic subsetExample Databasesid snamerating age1 Fred 7 222 Jim 2 393 Nancy 8 27Sailorssid bid day1 102 9/122 102 9/13Reservesbid bname color101 Nina red102 Pinta blue103 Santa MariaredBoatsReview: SQL DDLCREATE TABLE Sailors ( sid INTEGER, sname CHAR(20), rating INTEGER, age REAL, PRIMARY KEY sid);CREATE TABLE Boats ( bid INTEGER, bname CHAR (20), color CHAR(10) PRIMARY KEY bid); CREATE TABLE Reserves ( sid INTEGER, bid INTEGER, day DATE, PRIMARY KEY (sid, bid, date), FOREIGN KEY sid REFERENCES Sailors, FOREIGN KEY bid REFERENCES Boats);sid sname rating age1 Fred 7 222 Jim 2 393 Nancy 8 27bid bname color101 Nina red102 Pinta blue103 Santa Maria redsid bid day1 102 9/122 102 9/13The SQL DML•Find all 18-year-old sailors:SELECT *FROM Sailors SWHERE S.age=18• To find just names and ratings, replace the first line:SELECT S.sname, S.ratingsid snamerating age1 Fred 7 222 Jim 2 393 Nancy 8 27SailorsSELECT *FROM SailorsWHERE age=18Querying Multiple RelationsSELECT S.snameFROM Sailors S, Reserves RWHERE S.sid=R.sid AND R.bid=102sid snamerating age1 Fred 7 222 Jim 2 393 Nancy 8 27Sailorssid bid day1 102 9/122 102 9/13ReservesBasic SQL QuerySELECT [DISTINCT] target-listFROM relation-listWHERE qualificationqualification : Comparisons combined using AND, OR and NOT. Comparisons are Attr op const or Attr1 op Attr2, where op is one of etc.relation-list : A list of relation names, possibly with a range-variable after each nametarget-list : A list of attributes of tables in relation-listDISTINCT: optional keyword indicating answer should not contain duplicates. In SQL, default is that duplicates are not eliminated! (Result is called a “multiset”)1. FROM : compute cross product of tables.2. WHERE : Check conditions, discard tuples that fail.3. SELECT : Delete unwanted fields.4. DISTINCT (optional) : eliminate duplicate rows.Note: Probably the least efficient way to compute a query! –Query optimizer will find more efficient ways to get the same answer.Query SemanticsSELECT [DISTINCT] target-listFROM relation-listWHERE qualificationFind 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?S.sidSailors S, Reserves RS.sid=R.sidSELECTFROMWHEREAbout Range Variables•Needed when ambiguity could arise. –e.g., same table used multiple times in FROM (“self-join”)SELECT x.sname, x.age, y.sname, y.ageFROM Sailors x, Sailors yWHERE x.age > y.agesid snamerating age1 Fred 7 222 Jim 2 393 Nancy 8 27SailorsArithmetic ExpressionsSELECT S.age, S.age-5 AS age1, 2*S.age AS age2FROM Sailors SWHERE S.sname = ‘dustin’SELECT S1.sname AS name1, S2.sname AS name2FROM Sailors S1, Sailors S2WHERE 2*S1.rating = S2.rating - 1String Comparisons `_’ stands for any one character and `%’ stands for 0 or more arbitrary characters. Yes, every other language in the world uses Perl-like regular expressions. In fact, PostgreSQL supports this with substring(), but this is not standard or portable.SELECT S.snameFROM Sailors SWHERE S.sname LIKE ‘B_%B’Find sid’s of sailors who’ve reserved a red or a green boatSELECT R.sidFROM Boats B, Reserves RWHERE R.bid=B.bid AND (B.color=‘red’ OR B.color=‘green’)SELECT R.sidFROM Boats B, Reserves RWHERE R.bid=B.bid AND B.color=‘red’ UNION SELECT R.sidFROM Boats B, Reserves RWHERE R.bid=B.bid AND B.color=‘green’... or:SELECT R.sidFROM Boats B,Reserves RWHERE R.bid=B.bid AND (B.color=‘red’ AND B.color=‘green’)Find sid’s of sailors who’ve reserved a red and a green boatFind sid’s of sailors who’ve reserved a red and a green boatSELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’INTERSECTSELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘green’•Could use a self-join:SELECT R1.sidFROM Boats B1, Reserves R1, Boats B2, Reserves R2WHERE R1.sid=R2.sid AND R1.bid=B1.bid AND R2.bid=B2.bid AND (B1.color=‘red’ AND B2.color=‘green’)Find sid’s of sailors who’ve reserved a red and a green boatFind sid’s of sailors who have not reserved a boatSELECT S.sidFROM Sailors SEXCEPTSELECT S.sidFROM Sailors S, Reserves RWHERE
View Full Document
Unlocking...