1SQL: Part ICPS 116Introduction to Database Systems2Announcements (Thu. Sep. 11) Homework #1 due next Tuesday Do we need a help session tomorrow or Monday?• Tomorrow (Sep. 12): 3-4pm?• Monday (Sep. 15): 4:15-5:15pm?•Will il h•Will email the announcementTalk next Monday (Sep. 15), 4-5pm, North 130A http://www.cs.duke.edu/events/?id=00000000938 Flexible Recommendations in CourseRank• Hector Garcia-Molina (Stanford)– One of the book authors! Highly recommended!3SQL SQL: Structured Query Language Pronounced “S-Q-L” or “sequel” The standard query language supported by most commercial DBMSAbriefhistoryA brief history IBM System R ANSI SQL89 ANSI SQL92 (SQL2) ANSI SQL99 (SQL3) ANSI SQL 2003 (added OLAP, XML, etc.) ANSI SQL 2006 (added more XML)24Creating and dropping tables CREATE TABLE table_name(…, column_nameicolumn_typei, …); DROP TABLE table_name; Examplescreate table Student (SID integer,name varchar(30) email varchar(30)name varchar(30), email varchar(30),age integer, GPA float);create table Course (CID char(10), title varchar(100));create table Enroll (SID integer, CID char(10));drop table Student;drop table Course;drop table Enroll;-- everything from -- to the end of the line is ignored.-- SQL is insensitive to white space.-- SQL is insensitive to case (e.g., ...Course... is equivalent to-- ...COURSE...)5Basic queries: SFW statement SELECT A1, A2, …, AnFROM R1, R2, …, RmWHERE condition; Also called an SPJ (select-project-join) query Equivalent (not really!) to relational algebra queryπA1, A2, …, An( σcondition(R1× R2× … × Rm))6Example: reading a table SELECT * FROM Student; Single-table query, so no cross product here WHERE clause is optional * is a short hand for “all columns”37Example: selection and projection Name of students under 18 SELECT name FROM Student WHERE age < 18; When was Lisa born? SELECT 2008 – ageFROM StudentWHERE name = ’Lisa’; SELECT list can contain expressions• Can also use built-in functions such as SUBSTR, ABS, etc. String literals (case sensitive) are enclosed in singlequotes8Example: join SID’s and names of students taking courses with the word “Database” in their titles SELECT Student.SID, Student.nameFROM Student, Enroll, CourseWHERE Student SID = Enroll SIDWHERE Student.SID = Enroll.SIDAND Enroll.CID = Course.CIDAND title LIKE ’%Database%’; LIKE matches a string against a pattern• % matches any sequence of 0 or more characters Okay to omit table_name in table_name.column_name if column_name is unique9Example: rename SID’s of all pairs of classmates Relational algebra query:SQLSQL:SELECT e1.SID AS SID1, e2.SID AS SID2FROM Enroll AS e1, Enroll AS e2WHERE e1.CID = e2.CIDAND e1.SID > e2.SID; AS keyword is completely optional410A more complicated example Titles of all courses that Bart and Lisa are taking togetherFROM Student sb, Student sl, Enroll eb, Enroll el, Course cSELECT c.titleTip: Write the FROM clause first, then WHERE, and then SELECTWHERE sb.name = ’Bart’ AND sl.name = ’Lisa’AND eb.SID = sb.SID AND el.SID = sl.SIDAND eb.CID = c.CID AND el.CID = c.CID;11Why SFW statements? Out of many possible ways of structuring SQL statements, why did the designers choose SELECT-FROM-WHERE? A large number of queries can be written using only lddselection, projection, and cross product (or join) Any query that uses only these operators can be written in a canonical form: πL(σp(R1× … × Rm))•Example: πR.A, S.B(R !p1S) !p2(πT.Cσp3T) =πR.A, S.B, T.Cσp1 ∧ p2 ∧ p3( R × S × T ) SELECT-FROM-WHERE captures this canonical form12Set versus bag semantics Set No duplicates Relational model and algebra use set semantics Bag Duplicates allowed Number of duplicates is significant SQL uses bag semantics by default513Set versus bag exampleπSIDEnrollEnrollSID CID142 CPS116142CPS114SID142123857456…SELECT SIDFROM Enroll;142CPS114123 CPS116857 CPS116857 CPS130456 CPS114……SID142142123857857456…14A case for bag semantics Besides, SQL provides the option of set semantics with DISTINCT keyword15Forcing set semantics SID’s of all pairs of classmates SELECT e1.SID AS SID1, e2.SID AS SID2FROM Enroll AS e1, Enroll AS e2WHERE e1.CID = e2.CIDAND e1.SID > e2.SID;• Say Bart and Lisa both take CPS116 and CPS114 SELECT DISTINCT e1.SID AS SID1, e2.SID AS SID2...•With DISTINCT, all duplicate (SID1, SID2) pairs are removed from the output616Operational semantics of SFW SELECT [DISTINCT] E1, E2, …, EnFROM R1, R2, …, RmWHERE condition; For each t1in R1:For eacht2inR2:For each t2in R2: … …For each tmin Rm:If condition is true over t1, t2, …, tm:Compute and output E1, E2, …, Enas a rowIf DISTINCT is presentEliminate duplicate rows in output t1, t2, …, tmare often called tuple variables17SQL set and bag operations UNION, EXCEPT, INTERSECT Set semantics• Duplicates in input tables, if any, are first eliminated Exactly like set ∪, −, and ∩ in relational algebraUNION ALLEXCEPT ALLINTERSECT ALLUNION ALL, EXCEPT ALL, INTERSECT ALL Bag semantics Think of each row as having an implicit count (the number of times it appears in the table) Bag union: sum up the counts from two tables Bag difference: proper-subtract the two counts Bag intersection: take the minimum of the two counts18Examples of bag operationsBag1 Bag2fruitappleappleorangefruitappleorangeorangeBag1 UNION ALL Bag2Bag1 EXCEPT ALL Bag2Bag1 INTERSECT ALL Bag2fruitappleappleorangeappleorangeorangefruitapplefruitappleorange719Examples of set versus bag operations Enroll(SID, CID), ClubMember(club, SID) (SELECT SID FROM ClubMember)EXCEPT(SELECT SID FROM Enroll); (SELECT SID FROM ClubMember)EXCEPT ALL(SELECT SID FROM Enroll);20Summary of SQL features covered so far SELECT-FROM-WHERE statements (select-project-join queries) Set and bag operationsgp) Next: how to nest SQL queries21Table expression Use query result as a table In set and bag operations, FROM clauses, etc. A way to “nest” queries Example: names of students who are in more clubs than classes(SELECT SID FROM ClubMember)EXCEPT ALL(SELECT SID FROM Enroll)SELECT DISTINCT nameFROM Student,() AS SWHERE Student.SID = S.SID;822Scalar subqueries A query that returns a single row can be used as a value in WHERE, SELECT, etc. Example:
View Full Document