SQL: Queries, Constraints, Triggers
Structured Query Language
– Practice Queries –
Slides based on “Database Management Systems” 3rd ed, Ramakrishnan and Gehrke
CS430/630
Lecture 5
Example Schema
sid sname rating age
22 dustin 7 45.0
31 lubber 8 55.5
58 rusty 10 35.0
sid bid day
22 101 10/10/96
58 103 11/12/96
Reserves
Sailors
bid name color
101 interlake red
103 clipper green
Boats
Query Example 1
“Find names of sailors who’ve reserved boat #103”
SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid=R.sid AND R.bid=103
sid sname rating age
sid bid day
Reserves
Sailors
bid name color
Boats
Query Example 2
“Find names of sailors who’ve reserved a red boat”
SELECT S.sname
FROM Sailors S, Reserves R, Boats B
WHERE S.sid=R.sid AND R.bid=B.bid
AND B.color=‘red’;
sid sname rating age
sid bid day
Reserves
Sailors
bid name color
Boats
Query Example 3
“Find sailor ids who’ve reserved a red or a green boat; list each
matching sailor id once”
sid sname rating age
sid bid day
Reserves
Sailors
bid name color
Boats
SELECT DISTINCT S.sid
FROM Sailors S, Reserves R, Boats B
WHERE S.sid=R.sid AND R.bid=B.bid
AND (B.color=‘red’ OR B.color=‘green’);
Same query with set operations
SELECT S.sid
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid
AND B.color=‘red’
UNION
SELECT S.sid
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid
AND B.color=‘green’
Query Example 4
“Find sailor ids who’ve reserved a red and a green boat”
sid sname rating age
sid bid day
Reserves
Sailors
bid name color
Boats
SELECT S.sid
FROM Sailors S, Boats B1, Reserves R1,
Boats B2, Reserves R2
WHERE S.sid=R1.sid AND R1.bid=B1.bid
AND S.sid=R2.sid AND R2.bid=B2.bid
AND (B1.color=‘red’ AND B2.color=‘green’)
Same query with set operations
SELECT S.sid
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid
AND B.color=‘red’
INTERSECT
SELECT S.sid
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid
AND B.color=‘green’
LIKE with escape sequence
SELECT S.age, S.age-5 AS age1, 2*S.age AS age2
FROM Sailors S
WHERE S.sname LIKE ‘d_!%n’ ESCAPE ‘!’;