06-sql-notes

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 DocumentRight 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 DocumentRight 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 ?
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.

This document was uploaded on 01/17/2012.

Page1 / 15

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

This preview shows document pages 1 - 5. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online