SQL

1
SQL
?

• CS 186, Spring 2007, Lecture 7
• RG, Chapter 5
• Mary Roth

?
The important thing is not to stop
questioning. Albert Einstein
Life is just a bowl of queries. -Anon (not
Forrest Gump)
2
Administrivia

• Homework 1 due Thursday, Feb 8 10 p.m.
• Source code for diskmgr and global are available
  on class web site
• Coming up
• Homework 2 handed out Feb 13
• Midterm 1 in class February 22
• Questions?

3
Review

• Query languages provide 2 key advantages
• Less work for user asking query
• More opportunities for optimization
• Algebra and safe calculus are simple and powerful
  models for query languages for relational model
• Have same expressive power
• Algebra is more operational calculus is more
  declarative
• SQL can express every query that is expressible
  in relational algebra/calculus. (and more)

4
Review Where have we been?

• Practice

Theory
Relational Calculus
Lecture 6
Relational Algebra
Lecture 5
Relational Model
Lecture 2
5
Where are we going next?

• Practice

This week
SQL
After the midterm
Next week
6
Review Relational Calculus Example
Find names, ages and reservation dates of
sailors rated gt 7 whove reserved boat 103
S
S
S

• 3 quantifiers, but only 1 is free.
• The free quantifier defines the shape of the
  result.

R
R
S1 ? S?Sailors ? S.rating gt 7 ? ?
R(R?Reserves ? R.bid 103 ? R.sid
S.sid) ? (S1.sname S.name ?
S1.age S.age ? S1.day R.day)
sname age day

35.0
rusty
11/12/96
S1
7
Review The SQL Query Language

• The most widely used relational query language.
• Standardized
• (although most systems add their own special
  sauce -- including PostgreSQL)
• We will study SQL92 -- a basic subset

8
Review SQL

• Two sublanguages
• DDL Data Definition Language
• Define and modify schema (at all 3 levels)
• DML Data Manipulation Language
• Queries and IUD (insert update delete)
• DBMS is responsible for efficient evaluation.
• Relational completeness means we can define
  precise semantics for relational queries.
• Optimizer can re-order operations, without
  affecting query answer.
• Choices driven by cost model

9
Review DDL
Sailors
sid sname rating age
1 Frodo 7 22
2 Bilbo 2 39
3 Sam 8 27
CREATE TABLE Sailors (sid INTEGER, sname
CHAR(20), rating INTEGER, age REAL,
PRIMARY KEY sid) CREATE TABLE Boats (bid
INTEGER, bname CHAR (20), color
CHAR(10) PRIMARY KEY bid) CREATE TABLE
Reserves (sid INTEGER, bid INTEGER,
day DATE, PRIMARY KEY (sid, bid, day),
FOREIGN KEY sid REFERENCES Sailors, FOREIGN
KEY bid REFERENCES Boats)
NOT NULL,
Boats
bid bname color
101 Nina red
102 Pinta blue
103 Santa Maria red
NOT NULL,
Reserves
NOT NULL,
NOT NULL,
sid bid day
1 102 9/12
2 102 9/13
NOT NULL,
10
Integrity Constraints (ICs)

• A foreign key constraint is an Integrity
  Constraint
• a condition that must be true for any instance of
  the database
• Specified when schema is defined.
• Checked when relations are modified.
• Primary/foreign key constraints but databases
  support more general constraints as well.
• e.g. domain constraints like
• Rating must be between 1 and 10
• ALTER TABLE SAILORS
• ADD CONSTRAINT RATING
• CHECK (RATING gt 1 AND RATING lt 10)
• Or even more complex (and potentially
  nonsensical)
• ALTER TABLE SAILORS
• ADD CONSTRAINT RATING
• CHECK (RATINGAGE/4 lt SID)

11
DBMSs have fairly sophisticated support for
constraints!

• Specify them on CREATE or ALTER TABLE statements
• Column Constraints
• expressions for column constraint must produce
  boolean results and reference the related
  columns value only.
• NOT NULL NULL UNIQUE PRIMARY KEY CHECK
  (expression)
• FOREIGN KEY (column) referenced_table
• ON DELETE action ON UPDATE action
• action is one of
• NO ACTION, CASCADE, SET NULL, SET DEFAULT

12
DBMSs have fairly sophisticated support for
constraints!

• Table Constraints
• UNIQUE ( column_name , ... )
• PRIMARY KEY ( column_name , ... )
• CHECK ( expression )
• FOREIGN KEY ( column_name , ... ) REFERENCES
  reftable
• ON DELETE action ON UPDATE action
  
• Here, expressions, keys, etc can include multiple
  columns

13
Integrity Constraints can help prevent data
consistency errors

• but they have drawbacks
• Expensive
• Cant always return a meaningful error back to
  the application.
• e.g What if you saw this error when you
  enrolled in a course online?
• A violation of the constraint imposed by a
  unique index or a unique constraint occurred.
• Can be inconvenient
• e.g. What if the Sailing Class
  application wants to register new (unrated)
  sailors with rating 0?
• So they arent widely used
• Software developers often prefer to keep the
  integrity logic in applications instead

14
Intermission
15
SQL DML

• DML includes 4 main statements
• SELECT (query), INSERT, UPDATE and DELETE
• e.g To find the names of all 19 year old
  students

Well spend a lot of time on this one
PROJECT
SELECT S.name FROM Students S WHERE S.age19
SELECT
16
Querying Multiple Relations

• Can specify a join over two tables as follows

SELECT S.name, E.cid FROM Students S, Enrolled
E WHERE S.sidE.sid AND E.gradeB'
PROJECT
SELECT
JOIN
S.name E.cid Jones History105
result
17
Basic SQL Query
target-list A list of attributes of tables in
relation-list
DISTINCT optional keyword indicating answer
should not contain duplicates. In SQL, default
is that duplicates are not eliminated! (Result is
called a multiset)
SELECT DISTINCT target-list FROM
relation-list WHERE qualification
qualification Comparisons combined using AND,
OR and NOT. Comparisons are Attr op const or
Attr1 op Attr2, where op is one of ?,?,?,?, etc.
relation-list A list of relation names,
possibly with a range-variable after each name
18
Query Semantics

• Semantics of an SQL query are defined in terms of
  the following conceptual evaluation strategy
• FROM clause compute cross-product of all tables
• WHERE clause Check conditions, discard tuples
  that fail. (called selection).
• 3. SELECT clause Delete unwanted fields. (called
  projection).
• 4. If DISTINCT specified, eliminate duplicate
  rows.
• Probably the least efficient way to compute a
  query!
• An optimizer will find more efficient strategies
  to get the same answer.

19
Query Semantics Example
SELECT sname FROM Sailors, Reserves WHERE
Sailors.sidReserves.sid AND bid103
Boats
bid bname color
101 Nina red
102 Pinta blue
103 Santa Maria red
X
Sailors
sid sname rating age
1 Frodo 7 22
2 Bilbo 2 39
3 Sam 8 27
Reserves
sid bid day
1 102 9/12
2 103 9/13
20
Step 1 Compute the cross product
Sailors
Reserves
sid sname rating age
1 Frodo 7 22
2 Bilbo 2 39
3 Sam 8 27
sid bid day
1 102 9/12
2 103 9/13
...
SailorsXReserves
sid sname rating age sid bid day
1 Frodo 7 22 1 102 9/12
1 Frodo 7 22 2 103 9/13
2 Bilbo 2 39 1 102 9/12
2 Bilbo 2 39 2 103 9/13
3 Sam 8 27 1 103 9/12
3 Sam 8 27 2 103 9/13
21
Step 1 How big?
Sailors
Reserves
sid sname rating age
1 Frodo 7 22
2 Bilbo 2 39
3 Sam 8 27
sid bid day
1 102 9/12
2 103 9/13
Question If S is cardinality of Sailors, and
R is cardinality of Reserves, What is the
cardinality of Sailors X Reserves?
Answer S R
Sailors X Reserves 3X2 6
22
Step 2 Check conditions in where clause
SELECT sname FROM Sailors, Reserves WHERE
Sailors.sidReserves.sid AND bid103
SailorsXReserves
sid sname rating age sid bid day
1 Frodo 7 22 1 102 9/12
1 Frodo 7 22 2 103 9/13
2 Bilbo 2 39 1 102 9/12
2 Bilbo 2 39 2 103 9/13
3 Sam 8 27 1 102 9/12
3 Sam 8 27 2 103 9/13
23
Step 3 Delete unwanted fields
SELECT sname FROM Sailors, Reserves WHERE
Sailors.sidReserves.sid AND bid103
SailorsXReserves
sid sname rating age sid bid day
1 Frodo 7 22 1 102 9/12
1 Frodo 7 22 2 103 9/13
2 Bilbo 2 39 1 102 9/12
2 Bilbo 2 39 2 103 9/13
3 Sam 8 27 1 102 9/12
3 Sam 8 27 2 103 9/13
24
Range Variables

• Used for short hand
• Needed when ambiguity could arise
• e.g two tables with the same column name
• SELECT sname
• FROM Sailors, Reserves
• WHERE Sailors.sidReserves.sid AND
  Reserves.bid103
• SELECT sname
• FROM Sailors S, Reserves R
• WHERE S.sidR.sid AND R.bid103

Question do range variables remind you of
anything?

• Variables in relational calculus

25
Sometimes you need a range variable
e.g a Self-join SELECT R1.bid, R1.date FROM
Reserves R1, Reserves R2 WHERE R1.bid R2.bid
and R1.date R2.date and R1.sid !
R2.sid
bid day
103 9/12
bid day

103 9/12
Reserves
Reserves
sid bid day
1 102 9/12
3 103 9/12
4 103 9/13
2 103 9/12
sid bid day
1 102 9/12
3 103 9/12
4 103 9/13
2 103 9/12
R1
R2
R2
R1
R2
R2
R1
26
Sometimes you need a range variable
SELECT R1.bid, R1.day FROM Reserves R1, Reserves
R2 WHERE R1.bid R2.bid and R1.day
R2.day and R1.sid ! R2.sid
bid day
103 9/12
bid day

103 9/12
What are we computing? Boats reserved on the
same day by different sailors
27
SELECT Clause Expressions

• Can use if you want all columns

SELECT FROM Sailors x WHERE x.age gt 20

• Can use arithmetic expressions (add other
  operations well discuss later)

SELECT S.age, S.age-5 AS age1, 2S.age AS age2
FROM Sailors S WHERE S.sname Dustin

• Can use AS to provide column names

SELECT S1.sname AS name1, S2.sname AS name2
FROM Sailors S1, Sailors S2 WHERE 2S1.rating
S2.rating - 1
28
WHERE Clause Expressions

• Can also have expressions in WHERE clause

SELECT S1.sname AS name1, S2.sname AS name2
FROM Sailors S1, Sailors S2 WHERE 2S1.rating
S2.rating - 1

• LIKE is used for string matching.

SELECT S.age, S.age-5 AS age1, 2S.age AS age2
FROM Sailors S WHERE S.sname LIKE B_lo

• _ stands for any one character and stands
  for 0 or more arbitrary characters.

29
SELECT DISTINCT
Sailors
Reserves
sid bid day
1 102 9/12
2 103 9/12
2 102 9/13
sid sname rating age
1 Frodo 7 22
2 Bilbo 2 39
3 Sam 8 27
SELECT DISTINCT S.sid FROM Sailors S,
Reserves R WHERE S.sidR.sid
sid
1
2

• Find sailors that have reserved at least one boat

30
SELECT DISTINCT

• How about

SELECT S.sid FROM Sailors S, Reserves R WHERE
S.sidR.sid
sid
1
2
2
31
SELECT DISTINCT
Sailors
sid sname rating age
1 Frodo 7 22
2 Bilbo 2 39
3 Sam 8 27
4 Bilbo 5 32

• How about

sname
Frodo
Bilbo
Bilbo
SELECT S.sname FROM Sailors S, Reserves
R WHERE S.sidR.sid
Reserves
sid bid day
1 102 9/12
2 103 9/13
4 105 9/13
vs
SELECT DISTINCT S.sname FROM Sailors S,
Reserves R WHERE S.sidR.sid
sname
Frodo
Bilbo
Do we find all sailors that reserved at least one
boat?
32
Find sids of sailors whove reserved a red or a
green boat
ANDs, ORs, UNIONs and INTERSECTs
Boats
SELECT R.sid FROM Boats B,Reserves
R WHERE(B.colorred OR
B.colorgreen) AND R.bidB.bid
bid bname color
101 Nina red
102 Pinta blue
103 Santa Maria red
105 Titanic green
sid
2
4
Sailors
Reserves
sid sname rating age
1 Frodo 7 22
2 Bilbo 2 39
3 Sam 8 27
X
sid bid day
1 102 9/12
2 103 9/13
4 105 9/13
33
Find sids of sailors whove reserved a red and a
green boat
ANDs and ORs
Boats
SELECT R.sid FROM Boats B,Reserves
R WHERE(B.colorred AND
B.colorgreen) AND R.bidB.bid
X
bid bname color
101 Nina red
102 Pinta blue
103 Santa Maria red
105 Titanic green
Sailors
Reserves
sid sname rating age
1 Frodo 7 22
2 Bilbo 2 39
3 Sam 8 27
sid bid day
1 101 9/12
2 103 9/13
1 105 9/13
34
Use INTERSECT instead of AND
Exercise try to rewrite this query using a self
join instead of INTERSECT!
SELECT R.sid FROM Boats B,Reserves R WHERE
B.color red AND R.bidB.bid INTERSECT SELE
CT R.sid FROM Boats B,Reserves R WHERE B.color
green AND R.bidB.bid
Boats
bid bname color
101 Nina red
102 Pinta blue
103 Santa Maria red
105 Titanic green
Reserves
sid bid day
1 101 9/12
2 103 9/13
1 105 9/13
sid
1
2
sid
1
sid
1
?

35
Could also use UNION for the OR query
SELECT R.sid FROM Boats B, Reserves R WHERE
B.color red AND R.bidB.bid UNION
SELECT R.sidFROM Boats B, Reserves R WHERE
B.color green AND R.bidB.bid
Boats
bid bname color
101 Nina red
102 Pinta blue
103 Santa Maria red
105 Titanic green
Reserves
sid bid day
1 102 9/12
2 103 9/13
4 105 9/13
sid
2
4
sid
2
sid
4

?
36
EXCEPT Set Difference
Sailors
sid sname rating age
1 Frodo 7 22
2 Bilbo 2 39
3 Sam 8 27
Find sids of sailors who have not reserved a boat
SELECT S.sid FROM Sailors S EXCEPT SELECT
S.sid FROM Sailors S, Reserves R WHERE
S.sidR.sid
Reserves
sid bid day
1 102 9/12
2 103 9/13
1 105 9/13
First find the set of sailors who have reserved a
boat
sid
3
and then compare it with the rest of the sailors