{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

06-sql-notes - SQL Part I CPS 116 Introduction to Database...

Info iconThis preview shows pages 1–5. Sign up to view the full content.

View Full Document Right Arrow Icon
1 SQL: Part I CPS 116 Introduction to Database Systems 2 Announcements (Tue. Sep. 20) Homework #1 due today Deadline for the Gradiance portion has been extended Homework #2 will be assigned Thursday 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 (added OLAP, XML, etc.) ANSI SQL 2006 (added more XML) ANSI SQL 2008, …
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
2 4 Creating and dropping tables CREATE TABLE table_name ( , column_name i column_type i , ); DROP TABLE table_name ; Examples 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 insensitive to case (e.g., ...Course... is equivalent to -- ...COURSE...) 5 Basic queries: SFW statement SELECT A 1 , A 2 , , A n FROM R 1 , R 2 , , R m WHERE condition ; Also called an SPJ (select-project-join) query Equivalent (not really!) to relational algebra query π A 1 , A 2 , …, A n ( σ condition ( R 1 × R 2 × × R m )) 6 Example: 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”
Background image of page 2
3 7 Example: selection and projection Name of students under 18 SELECT name FROM Student WHERE age < 18; When was Lisa born? SELECT 2009 – 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 8 Example: join 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 = 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 9 Example: rename SID’s of all pairs of classmates Relational algebra query: 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
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
4 10 A more complicated example Titles of all courses that Bart and Lisa are taking together Tip: Write the FROM clause first, then WHERE , and then SELECT FROM WHERE SELECT 11 Why 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 ( R 1 × × R m )) • Example: π R . A , S . B ( R ± p 1 S ) ± p 2 ( π T . C σ p 3 T ) = π R . A , S . B , T . C σ p 1 Æ p 2 Æ p 3 ( R × S × T ) SELECT - FROM - WHERE captures this canonical form 12 Set versus bag semantics
Background image of page 4
Image of page 5
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}