DOC PREVIEW
Duke CPS 116 - Part I

This preview shows page 1-2-3 out of 8 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 8 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 8 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 8 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 8 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

Announcements September 15 Homework Homework Project TA 3 4 create 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 IBM System R ANSI SQL89 ANSI SQL92 SQL2 ANSI SQL99 SQL3 ANSI SQL 2003 OLAP XML etc 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 Creating and dropping tables brief history Basic queries SFW statement out of town until September 26 CREATE TABLE table name column namei column typei DROP TABLE table name Examples Structured Query Language Pronounced S Q L or sequel The standard query language support by most commercial DBMS A 2 out next Tuesday Milestone 1 due in 28 days Come to my office hours if you want to chat about project ideas CPS 116 Introduction to Database Systems SQL 1 due tonight Sample solution available next Tuesday SQL Part I SQL 2 5 Example reading a table 6 SELECT SELECT FROM Student Single table query so no cross product here WHERE clause is optional is a short hand for all columns 1 Example selection and projection Name 7 of students under 18 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 Enroll SID AND Enroll CID Course CID AND title LIKE Database LIKE matches a string against a pattern was Lisa born SELECT 2005 age FROM Student WHERE name Lisa SELECT list can contain expressions Can also use built in functions such as SUBSTR ABS etc matches any sequence of 0 or more characters String literals case sensitive are enclosed in single quotes Example rename SID s Okay to omit table name in table name column name if column name is unique 9 of all pairs of classmates 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 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 p1 S p2 T C p3 T R A S B T C p1 p2 p3 R S T A more complicated example 10 Titles of all courses that Bart and Lisa are taking together Relational algebra query e1 SID e2 SID e1 Enroll e1 CID e2 CID e1 SID e2 SID e2 Enroll 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 Why SFW statements 8 SID s SELECT name FROM Student WHERE age 18 When Example join 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 11 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 SELECT FROM WHERE captures this canonical form 2 13 Set versus bag example SID Enroll Enroll SID 142 142 123 857 857 456 CID CPS196 CPS114 CPS196 CPS196 CPS130 CPS114 SELECT SID FROM Enroll Efficiency SID 142 123 857 456 Saves time of eliminating duplicates Which SID 142 142 123 857 857 456 Operational semantics of SFW SQL provides the option of set semantics with DISTINCT keyword 15 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 UNION EXCEPT INTERSECT Set semantics Duplicates in input tables if any are first eliminated Exactly like set and in relational algebra UNION one is more useful GPA Student SELECT GPA FROM Student The first query just returns all possible GPA s The second query returns the actual GPA distribution Besides Example forcing set semantics 16 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 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 output SQL set and bag operations 14 A case for bag semantics 17 18 Examples of bag operations Bag1 Bag2 fruit apple apple orange fruit apple orange orange 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 counts Bag1 INTERSECT ALL Bag2 Bag1 UNION ALL Bag2 fruit apple apple orange apple orange orange Bag1 EXCEPT ALL Bag2 fruit apple fruit apple orange 3 Examples of set versus bag operations Enroll SID 19 20 Summary of SQL features covered so far CID ClubMember club SID SELECT SID FROM ClubMember EXCEPT SELECT SID FROM Enroll SELECT FROM WHERE statements select project join queries Set and bag operations SID s of students who are in clubs but not taking any classes SELECT SID FROM ClubMember EXCEPT ALL SELECT SID FROM Enroll Next how to nest SQL queries SID s of students who are in more clubs than classes Table expression Use 21 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 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 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 name is a key of Student What if subquery returns no rows Can be used in SELECT to compute a value for an output column The value returned is NULL and the comparison fails IN subqueries x IN subquery checks if …


View Full Document

Duke CPS 116 - Part I

Documents in this Course
Part I

Part I

8 pages

XSLT

XSLT

4 pages

XSLT

XSLT

8 pages

XSLT

XSLT

8 pages

Load more
Download Part I
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Part I and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Part I 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?