SQL: Queries, Programming, Triggers
Chapter 5
Database Management Systems, R. Ramakrishnan and J. Gehrke
1
SQL
Database Management Systems, R. Ramakrishnan and J. Gehrke
2
SQL
Database Management Systems, R. Ramakrishnan and J. Gehrke
3
Example tables
Sailors(sid: integer, sname: string, rating: integer, age: real)
Boats(bid: integer, bname: string, color: string)
Reserves(sid: integer, bid: integer, day: date)
Database Management Systems, R. Ramakrishnan and J. Gehrke
4
Example Instances
R1
S1
S2
Database Management Systems, R. Ramakrishnan and J. Gehrke
5
Basic SQL Query
SELECT [DISTINCT] target-list
FROM relation-list
WHERE qualification
Database Management Systems, R. Ramakrishnan and J. Gehrke
6
Conceptual Evaluation Strategy
Database Management Systems, R. Ramakrishnan and J. Gehrke
7
Example of Conceptual Evaluation
SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid=R.sid AND R.bid=103
Database Management Systems, R. Ramakrishnan and J. Gehrke
8
A Note on Range Variables
SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid=R.sid AND bid=103
SELECT sname
FROM Sailors, Reserves
WHERE Sailors.sid=Reserves.sid
AND bid=103
It is good style,
however, to use
range variables
always!
OR
Database Management Systems, R. Ramakrishnan and J. Gehrke
9
Example queries
(Q15) Find the names and ages of all sailors. (see text pp. 134-135)
(Q11) Find all sailors with a rating above 7. (see text pp. 135)
Database Management Systems, R. Ramakrishnan and J. Gehrke
10
(Q4) Find sailors who’ve reserved at least one boat
SELECT S.sid
FROM Sailors S, Reserves R
WHERE S.sid=R.sid
Database Management Systems, R. Ramakrishnan and J. Gehrke
11
Other example queries
Database Management Systems, R. Ramakrishnan and J. Gehrke
12
Expressions and Strings
Q17: pp. 140
Read 1st paragraph of Section 5.2.2 in pp. 139-140
Each item in a qualification can be as general as
expression1 = expressions2.
SELECT S1.sname AS name1, S2.sname AS name2
FROM Sailors S1, Sailors S2
WHERE 2*S1.rating=S2.rating-1
Database Management Systems, R. Ramakrishnan and J. Gehrke
13
Expressions and Strings
SQL provides support for pattern matching through
the LIKE operator, along with the use of the wild-card
symbols % (which stands for zero or more arbitrary
characters) and _ (which stands for exactly one, arbitrary,
character)
‘_AB%’ → a pattern that will match every string that contains
at least three characters, with the second and third
characters being A and B respectively.
Q18: pp. 140
Database Management Systems, R. Ramakrishnan and J. Gehrke
14
Find sid’s of sailors who’ve reserved a red or a green boat
SELECT R.sid
FROM Boats B, Reserves R
WHERE R.bid=B.bid
AND (B.color=‘red’ OR B.color=‘green’)
SELECT R.sid
FROM Boats B, Reserves R
WHERE R.bid=B.bid
AND B.color=‘red’
UNION
SELECT R.sid
FROM Boats B, Reserves R
WHERE R.bid=B.bid
AND B.color=‘green’
Database Management Systems, R. Ramakrishnan and J. Gehrke
15
Find sid’s of sailors who’ve reserved a red and a green boat
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’)
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’
Key field!
Database Management Systems, R. Ramakrishnan and J. Gehrke
16
Nested Queries
SELECT S.sname
FROM Sailors S
WHERE S.sid IN (SELECT R.sid
FROM Reserves R
WHERE R.bid=103)
Find names of sailors who’ve reserved boat #103:
Database Management Systems, R. Ramakrishnan and J. Gehrke
17
More Examples
Database Management Systems, R. Ramakrishnan and J. Gehrke
18
Nested Queries with Correlation
SELECT S.sname
FROM Sailors S
WHERE EXISTS (SELECT *
FROM Reserves R
WHERE R.bid=103 AND S.sid=R.sid)
Find names of sailors who’ve reserved boat #103:
Database Management Systems, R. Ramakrishnan and J. Gehrke
19
Another Example using correlated queries
How do we find out which departments do not have any employee working in it?
Database Management Systems, R. Ramakrishnan and J. Gehrke
20
Another Example using correlated queries
from Departments D
where NOT EXISTS (select *
from Works_IN W
where W.did = D.did)
Database Management Systems, R. Ramakrishnan and J. Gehrke
21
More on Set-Comparison Operators
SELECT *
FROM Sailors S
WHERE S.rating > ANY (SELECT S2.rating
FROM Sailors S2
WHERE S2.sname=‘Horatio’)
Database Management Systems, R. Ramakrishnan and J. Gehrke
22
More Examples
IN is equivalent to = ANY
NOT IN is equivalent to <> ALL
Database Management Systems, R. Ramakrishnan and J. Gehrke
23
Rewriting INTERSECT Queries Using IN
Find sid’s of sailors who’ve reserved both a red and a green boat:
SELECT S.sid
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’
AND S.sid IN (SELECT S2.sid
FROM Sailors S2, Boats B2, Reserves R2
WHERE S2.sid=R2.sid AND R2.bid=B2.bid
AND B2.color=‘green’)
Database Management Systems, R. Ramakrishnan and J. Gehrke
24
Division in SQL
SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS
((SELECT B.bid
FROM Boats B)
EXCEPT
(SELECT R.bid
FROM Reserves R
WHERE R.sid=S.sid))
SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS (SELECT B.bid
FROM Boats B
WHERE NOT EXISTS (SELECT R.bid
FROM Reserves R
WHERE R.bid=B.bid
AND R.sid=S.sid))
Sailors S such that ...
there is no boat B without ...
a Reserves tuple showing S reserved B
Find sailors who’ve reserved all boats.
(1)
(2)
Database Management Systems, R. Ramakrishnan and J. Gehrke
25
Aggregate Operators
COUNT (*)
COUNT ( [DISTINCT] A)
SUM ( [DISTINCT] A)
AVG ( [DISTINCT] A)
MAX (A)
MIN (A)
SELECT AVG (S.age)
FROM Sailors S
WHERE S.rating=10
SELECT COUNT (*)
FROM Sailors S
SELECT AVG ( DISTINCT S.age)
FROM Sailors S
WHERE S.rating=10
SELECT S.sname
FROM Sailors S
WHERE S.rating= (SELECT MAX(S2.rating)
FROM Sailors S2)
single column
SELECT COUNT (DISTINCT S.rating)
FROM Sailors S
WHERE S.sname=‘Bob’
Database Management Systems, R. Ramakrishnan and J. Gehrke
26
Assertion
CREATE ASSERTION Every_dept_has_emp
CHECK ((Select count(D.did)
from Departments D
where NOT EXISTS (select *
from Works_IN W
where W.did = D.did)) = 0)
Database Management Systems, R. Ramakrishnan and J. Gehrke
27
Find name and age of the oldest sailor(s)
SELECT S.sname, MAX (S.age)
FROM Sailors S
SELECT S.sname, S.age
FROM Sailors S
WHERE S.age =
(SELECT MAX (S2.age)
FROM Sailors S2)
SELECT S.sname, S.age
FROM Sailors S
WHERE (SELECT MAX (S2.age)
FROM Sailors S2)
= S.age
Database Management Systems, R. Ramakrishnan and J. Gehrke
28
GROUP BY and HAVING
SELECT MIN (S.age)
FROM Sailors S
WHERE S.rating = i
For i = 1, 2, ... , 10:
Database Management Systems, R. Ramakrishnan and J. Gehrke
29
Queries With GROUP BY and HAVING
SELECT [DISTINCT] target-list
FROM relation-list
WHERE qualification
GROUP BY grouping-list
HAVING group-qualification
Database Management Systems, R. Ramakrishnan and J. Gehrke
30
(Q31) Find the age of the youngest sailor for each rating level.
Select S.rating, MIN(S.age)
From Sailors S
Group By S.rating
Database Management Systems, R. Ramakrishnan and J. Gehrke
31
Conceptual Evaluation
Database Management Systems, R. Ramakrishnan and J. Gehrke
32
(Q32) Find the age of the youngest sailor who is eligible to vote
(i.e., is at least 18 years old) for each rating level with at least
two such sailors.
see pages 155~157
Database Management Systems, R. Ramakrishnan and J. Gehrke
33
SQL:1999 Extensions
HAVING COUNT(*) > 1 and EVERY (S.age <= 60)
The EVERY keyword requires that every row in a group must satisfy the attached condition to meet the group-qualification.
Database Management Systems, R. Ramakrishnan and J. Gehrke
34
SQL:1999 Extensions (cont)
Database Management Systems, R. Ramakrishnan and J. Gehrke
35
More examples of aggregate queries
Database Management Systems, R. Ramakrishnan and J. Gehrke
36
More examples of aggregate queries
Database Management Systems, R. Ramakrishnan and J. Gehrke
37
More examples of aggregate queries
Database Management Systems, R. Ramakrishnan and J. Gehrke
38
Null Values
Database Management Systems, R. Ramakrishnan and J. Gehrke
39
Embedded SQL
Database Management Systems, R. Ramakrishnan and J. Gehrke
40
Cursors
Database Management Systems, R. Ramakrishnan and J. Gehrke
41
Cursor that gets names of sailors who’ve reserved a red boat, in alphabetical order
EXEC SQL DECLARE sinfo CURSOR FOR
SELECT S.sname
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’
ORDER BY S.sname
Database Management Systems, R. Ramakrishnan and J. Gehrke
42
Embedding SQL in C: An Example
char SQLSTATE[6];
EXEC SQL BEGIN DECLARE SECTION
char c_sname[20]; short c_minrating; float c_age;
EXEC SQL END DECLARE SECTION
c_minrating = random();
EXEC SQL DECLARE sinfo CURSOR FOR
SELECT S.sname, S.age FROM Sailors S
WHERE S.rating > :c_minrating
ORDER BY S.sname;
do {
EXEC SQL FETCH sinfo INTO :c_sname, :c_age;
printf(“%s is %d years old\n”, c_sname, c_age);
} while (SQLSTATE != ‘02000’);
EXEC SQL CLOSE sinfo;
Database Management Systems, R. Ramakrishnan and J. Gehrke
43
Database APIs: Alternative to embedding
Rather than modify compiler, add library with database calls (API)
Database Management Systems, R. Ramakrishnan and J. Gehrke
44
SQL API in Java (JDBC)
Connection con = // connect
DriverManager.getConnection(url, ”login", ”pass");
Statement stmt = con.createStatement(); // set up stmt
String query = "SELECT name, rating FROM Sailors";
ResultSet rs = stmt.executeQuery(query);
try { // handle exceptions
// loop through result tuples
while (rs.next()) {
String s = rs.getString(“name");
Int n = rs.getFloat(“rating");
System.out.println(s + " " + n);
}
} catch(SQLException ex) {
System.out.println(ex.getMessage ()
+ ex.getSQLState () + ex.getErrorCode ());
}
Database Management Systems, R. Ramakrishnan and J. Gehrke
45
Integrity Constraints (Review)
Database Management Systems, R. Ramakrishnan and J. Gehrke
46
General Constraints
CREATE TABLE Sailors
( sid INTEGER,
sname CHAR(10),
rating INTEGER,
age REAL,
PRIMARY KEY (sid),
CHECK ( rating >= 1
AND rating <= 10 )
CREATE TABLE Reserves
( sname CHAR(10),
bid INTEGER,
day DATE,
PRIMARY KEY (bid,day),
CONSTRAINT noInterlakeRes
CHECK (`Interlake’ <>
( SELECT B.bname
FROM Boats B
WHERE B.bid=bid)))
Database Management Systems, R. Ramakrishnan and J. Gehrke
47
Constraints Over Multiple Relations
CREATE TABLE Sailors
( sid INTEGER,
sname CHAR(10),
rating INTEGER,
age REAL,
PRIMARY KEY (sid),
CHECK
( (SELECT COUNT (S.sid) FROM Sailors S)
+ (SELECT COUNT (B.bid) FROM Boats B) < 100 )
CREATE ASSERTION smallClub
CHECK
( (SELECT COUNT (S.sid) FROM Sailors S)
+ (SELECT COUNT (B.bid) FROM Boats B) < 100 )
Number of boats
plus number of
sailors is < 100
Database Management Systems, R. Ramakrishnan and J. Gehrke
48
Triggers
Database Management Systems, R. Ramakrishnan and J. Gehrke
49
Triggers: Example (SQL:1999)
CREATE TRIGGER youngSailorUpdate
AFTER INSERT ON SAILORS
REFERENCING NEW TABLE NewSailors
FOR EACH STATEMENT
INSERT
INTO YoungSailors(sid, name, age, rating)
SELECT sid, name, age, rating
FROM NewSailors N
WHERE N.age <= 18
Database Management Systems, R. Ramakrishnan and J. Gehrke
50
Summary
Database Management Systems, R. Ramakrishnan and J. Gehrke
51
Summary (Contd.)
Database Management Systems, R. Ramakrishnan and J. Gehrke
52