1SQL: Part ICPS 116Introduction to Database Systems2Announcements (September 15) Homework #1 due tonight Sample solution available next Tuesday Homework #2 out next Tuesday Project Milestone #1 due in 28 days Come to my office hours if you want to chat about project ideas TA out of town until September 263SQL SQL: Structured Query Language Pronounced “S-Q-L” or “sequel” The standard query language support by most commercial DBMS A brief history IBM System R ANSI SQL89 ANSI SQL92 (SQL2) ANSI SQL99 (SQL3) ANSI SQL 2003 (+OLAP, XML, etc.)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),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 case insensitive (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 2005 – 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 single quotes8Example: 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.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:πe1.SID, e2.SID( ρe1Enroll e1.CID = e2.CID∧ e1.SID > e2.SIDρe2Enroll ) SQL: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 togetherTip: Write the FROM clause first, then WHERE, and then SELECTFROM Student sb, Student sl, Enroll eb, Enroll el, Course cWHERE 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;SELECT c.title11Why 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 selection, 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 exampleSID CID142 CPS196142 CPS114123 CPS196857 CPS196857 CPS130456 CPS114... ...SID142123857456...πSIDEnrollEnrollSELECT SIDFROM Enroll;SID142142123857857456...14A case for bag semantics Efficiency Which one is more useful? πGPAStudent SELECT GPA FROM Student; Besides, SQL provides the option of set semantics with DISTINCT keyword15Operational semantics of SFW SELECT [DISTINCT] E1, E2, …, EnFROM R1, R2, …, RmWHERE condition; For each t1in R1: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 variables616Example: forcing 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; SELECT DISTINCT e1.SID AS SID1, e2.SID AS SID2...•With DISTINCT, all duplicate (SID1, SID2) pairs are removed from the output17SQL set and bag operations UNION, EXCEPT, INTERSECT Set semantics• Duplicates in input tables, if any, are first eliminated Exactly like set ∪, −, and ∩ in relational algebra UNION 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 operationsfruitappleappleorangefruitappleorangeorangeBag1 Bag2Bag1 UNION ALL Bag2fruitappleappleorangeappleorangeorangeBag1 EXCEPT ALL Bag2fruitappleBag1 INTERSECT ALL Bag2fruitappleorange719Examples 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 operations) 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: students at the same age as BartSELECT *FROM StudentWHERE age = ();SELECT ageFROM StudentWHERE name = ’Bart’What’s Bart’s age? Runtime error if subquery returns more than one row Under what condition will this
View Full Document
Unlocking...