SQL Part I CPS 116 Introduction to Database Systems Announcements September 13 Homework 2 1 due next Tuesday Do we need a help session on Monday Course project assigned today Choice of standard or open One to three person teams Two milestones demo report Milestone 1 due in 4 weeks right after fall break 3 SQL SQL Structured Query Language Pronounced S Q L or sequel The standard query language supported by most commercial DBMS A brief history IBM System R ANSI SQL89 ANSI SQL92 SQL2 ANSI SQL99 SQL3 ANSI SQL 2003 OLAP XML etc 1 Creating and dropping tables 4 CREATE TABLE table name column namei column typei DROP TABLE table name Examples create table Student SID integer name varchar 30 varchar 30 email varchar 30 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 Basic queries SFW statement 5 A1 A2 An FROM R1 R2 Rm WHERE condition Also called an SPJ select project join query Equivalent not really to relational algebra query A1 A2 An condition R1 R2 Rm SELECT Example reading a table SELECT 6 FROM Student Single table query so no cross product here WHERE clause is optional is a short hand for all columns 2 Example selection and projection Name 7 of students under 18 SELECT name FROM Student WHERE age 18 When was Lisa born SELECT 2007 age FROM Student WHERE 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 quotes Example join 8 SID s and names of students taking courses with the word Database in their titles SELECT Student SID Student name FROM Student Enroll Course WHERE Student SID Student SID Enroll SID Enroll SID AND Enroll CID Course CID AND 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 unique Example rename SID s 9 of all pairs of classmates Relational algebra query e1 SID e2 SID e1 Enroll e1 CID e2 CID e1 SID e2 SID e2 Enroll SQL SQL SELECT e1 SID AS SID1 e2 SID AS SID2 FROM Enroll AS e1 Enroll AS e2 WHERE e1 CID e2 CID AND e1 SID e2 SID AS keyword is completely optional 3 A more complicated example 10 Titles of all courses that Bart and Lisa are taking together SELECT c title FROM Student sb Student sl Enroll eb Enroll el Course c WHERE sb name Bart AND sl name Lisa AND eb SID sb SID AND el SID sl SID AND eb CID c CID AND el CID c CID Tip Write the FROM clause first then WHERE and then SELECT Why SFW statements 11 Out of many possible ways of structuring SQL statements why did the designers choose SELECTFROM WHERE A large number of queries can be written using only selection l projection andd cross product d 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 p1 S p2 T C p3 T R A S B T C p1 p2 p3 R S T SELECT FROM WHERE captures this canonical form Set versus bag semantics 12 Set No duplicates Relational model and algebra use set semantics Bag Duplicates allowed Number of duplicates is significant SQL uses bag semantics by default 4 13 Set versus bag example SID Enroll SID 142 Enroll 123 SID CID 142 CPS116 142 CPS114 123 CPS116 857 CPS116 857 CPS130 456 CPS114 857 456 SELECT SID FROM Enroll SID 142 142 123 857 857 456 A case for bag semantics Forcing set semantics 14 15 SID s of all pairs of classmates SELECT e1 SID AS SID1 e2 SID AS SID2 FROM Enroll AS e1 Enroll AS e2 WHERE e1 CID e2 CID AND 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 output 5 Operational semantics of SFW 16 SELECT DISTINCT E1 E2 En FROM R1 R2 Rm WHERE condition For each t1 in R1 For each t2 in R2 For each tm in Rm If condition is true over t1 t2 tm Compute and output E1 E2 En as a row If DISTINCT is present Eliminate duplicate rows in output t1 t2 tm are often called tuple variables 17 SQL 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 ALL EXCEPT ALL 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 counts 18 Examples of bag operations Bag1 Bag2 fruit fruit apple apple apple orange orange orange Bag1 UNION ALL Bag2 Bag1 INTERSECT ALL Bag2 fruit fruit apple apple orange apple Bag1 EXCEPT ALL Bag2 fruit apple orange apple orange orange 6 Examples of set versus bag operations Enroll SID 19 CID ClubMember club SID SELECT SID FROM ClubMember EXCEPT SELECT SID FROM Enroll SELECT SID FROM ClubMember EXCEPT ALL SELECT SID FROM Enroll 20 Summary of SQL features covered so far SELECT FROM WHERE statements select project join queries Set and bag g operations p Next how to nest SQL queries Table expression Use 21 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 DISTINCT name FROM Student SELECT SID FROM ClubMember EXCEPT ALL SELECT SID FROM Enroll AS S WHERE Student SID S SID 7 Scalar subqueries 22 A query that returns a single row can be used as a value in WHERE SELECT etc Example students at the same age as Bart SELECT What s Bart s age FROM Student WHERE age SELECT age FROM Student WHERE name Bart Runtime error if subquery returns more than one row Under what condition will this runtime error never occur What if subquery returns no rows Can be used in SELECT to compute a value for an output column The value returned is a special NULL value and the comparison fails IN subqueries 23 x IN subquery checks if x is in the result of subquery Example students at the same age as some Bart SELECT What s Bart s age g FROM Student WHERE age IN SELECT age FROM Student WHERE name Bart EXISTS subqueries 24 subquery checks if the result of subquery is non empty Example students at the same age as some Bart EXISTS SELECT FROM Student …
View Full Document
Unlocking...