Transcript SimpleQueries

SQL
Part I: Standard Queries
Example Instances
Reserves
Sailors S
sid sname rating age
22 debby 7
31 debby 8
58 lilly
10
35
55
35
R
sid bid day
22 101
2005/24/07
58 103
2005/07/30
31 103
2005/07/28
58 103
2005/07/31
Boat B
bid
bname color
101 Interlake green
103 Snapper red
COMP-421: Database Systems - SQL Queries I
2
Principle Form of a Query



sid sname rating age
SELECT desired attributes
FROM list of relations
WHERE qualification
(where clause is optional)
SELECT rating,age
FROM Sailors
WHERE rating <= 6 OR age < 40
22 debby
31 debby
58 lilly
7
8
10
rating age
35
55
35
7 35
10 35
Operational Semantics
 Imagine a tuple variable ranging over all tuples of the relation
 For each tuple: check if is satisfies the WHERE clause. If so, print the
attributes in SELECT.

Conversion to Relational Algebra
 rating,age (rating<=6
 age<40
(Sailors))
 Start with the relation in the FROM clause
 Apply
, using condition in WHERE clause (selection)
 Apply ∏, using attributes in SELECT clause (projection)
 Difference: duplicates not eliminated
COMP-421: Database Systems - SQL Queries I
3
The WHERE Clause

Comparison:
 attr1 op const, or
 attr1 op attr2,
 op is one of , , , , , , LIKE
 We may apply the usual arithmetic operations +, *, etc. to numeric values
before we compare

Qualification/Condition: Comparisons combined using AND, OR and
NOT
 name =‘Cheng’ AND NOT age = 18
 name LIKE ‘%e_g’ (%: any string, _:any character)
 Further string operations, e.g., concatenation, string-length, etc.
As default for most statements, SQL uses ‘multiset’ semantic, i.e.,
duplicates are allowed and not eliminated (as long as they do not
violate a primary key / unique constraint)
 DISTINCT is an optional keyword indicating that the answer should
not contain duplicates.

COMP-421: Database Systems - SQL Queries I
4
Attribute Lists



Distinct
SELECT DISTINCT sname
FROM Sailors
(no WHERE clause OK)
Star as list of all attributes
SELECT *
FROM Sailors
WHERE rating < 9
sname
debby
lilly
22 debby
31 debby
58 lilly
7
8
10
35
55
35
sid sname rating age
22
31
Renaming columns
SELECT sid, sname AS sailor
FROM Sailors
WHERE rating < 9
COMP-421: Database Systems - SQL Queries I
age
sid sname rating
debby
debby
7
8
35
55
sid
sailor
22
31
debby
debby
5
Attribute Lists (contd)



Expressions as values in columns
SELECT sname, rating+1 AS upgrade
FROM Sailors
sname
upgrade
debby
debby
lilly
8
9
11
Constants as attribute values
SELECT rating AS reality, ‘10’ AS dream
FROM Sailors
reality dream
WHERE sname LIKE ‘_e%y’
7
10
Ordered Output
SELECT *
FROM Sailors
ORDER BY age,rating
8
sid sname rating age
22 debby
58 lilly
31 debby
COMP-421: Database Systems - SQL Queries I
10
7
10
8
35
35
55
6
Multirelational Queries
List of relations in FROM clause
 Relation-dot-attribute disambiguates attributes from several relations.
 Cross-Product:

SELECT *
FROM Sailors, Reserves
Sailors

Join:
X Reserves
 Have to indicate comparison even with natural join
 Example: “give me the names of all sailors that have reserved boat #103”
SELECT sname
FROM Sailors, Reserves
WHERE Sailors.sid = Reserves.sid AND bid = 103
sname (bid=103 (Reserves)
COMP-421: Database Systems - SQL Queries I
Sailors)
7
Semantics

Conversion to Relational Algebra
 Same as for single relation, but start with the
product (X) of all the relations mentioned in the FROM
clause

Parallel assignment of tuple variables
 Consider a tuple variable for each relation in the
FROM
 Consider all possible assignments
 For each such assignment of tuple variables to tuples
sid bid
that makes the WHERE
true,
dayoutput the attributes
of
sname
sid sname
rating age
the SELECT
22 debby
31 debby
58 lilly
7
8
10
35
55
35
COMP-421: Database Systems - SQL Queries I
22 101
2005/24/07
58 103
2005/07/30
31 103
2005/07/28
58 103
2005/07/31
debby
Lilly
lilly
8
Semantics (contd).

Nested Loop Assignment
LET the tuple variables in the from clause range over
relations R1, R2, …., Rn
FOR each tuple t1 in relation R1 DO
FOR each tuple t2 in relation R2 DO
…
FOR each tuple tn in relation Rn DO
If the where clause is satisfied when
the values from t1, t2, … tn are
substituted for all attribute references
THEN evaluate the attributes of the
select clause according to
t1, t2, …. tn and produce the
tuple of values that results.
COMP-421: Database Systems - SQL Queries I
9
Range Variables

Optional use of range variables
SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid = R.sid AND R.bid = 103

Use of range variable required when the same relation appears
twice in the FROM clause

Example: “find pairs of sailors that have rented the same boat”
SELECT r1.sid, r2.sid
FROM Reserves r1, Reserves r2
WHERE r1.bid = r2.bid AND r1.sid < r2.sid
(note that r1.sid < r2.sid is needed to avoid producing (58,58) and to
avoid producing a pair in both directions.
COMP-421: Database Systems - SQL Queries I
10
Union, Intersection,
Difference

Input relations for set operators must be
set-compatible, I.e. they must have
 Same number of attributes
 The attributes, taken in order, must have same
type
As default, result relation is a set!!! (no
multiset)
 Many systems do not provide primitives for
intersection and difference

COMP-421: Database Systems - SQL Queries I
11
Union


Sailors(sid,sname,rating,age)
Reserves(sid,bid,day)
Boats(bid,bname,color)
Find sailors that have reserved a red or a green boat
SELECT R.sid
FROM Reserves R, Boats B
WHERE R.bid = B.bid AND
(B.color = ‘red’ OR B.color = ‘green’)
SELECT R.sid
FROM Reserves
WHERE R.bid =
UNION
SELECT R.sid
FROM Reserves
WHERE R.bid =
R, Boats B
B.bid AND B.color = ‘red’
R, Boats B
B.bid AND B.color = ‘green’
COMP-421: Database Systems - SQL Queries I
12
Intersection

Find sailors that have reserved a red and a green boat
(1) SELECT R.sid
FROM Reserves R, Boats B
WHERE R.bid = B.bid AND B.color = ‘red’
INTERSECT
SELECT R.sid
FROM Reserves R, Boats B
WHERE R.bid = B.bid AND B.color = ‘green’
(2) SELECT R1.sid
FROM Reserves R1, Reserves R2, Boats B1, Boats B2
WHERE (R1.bid = B1.bid AND B1.color = ‘red’) AND
(R2.bid = B2.bid AND B2.color = ‘green’) AND
R1.sid = R2.sid)
sid bid day
sid bid day
22 101
2005/24/07
22 101
2005/24/07
58 103
2005/07/30
58 103
2005/07/30
22 103
2005/07/28
bid color
22 103
2005/07/28
58 103
2005/07/31
58 103
2005/07/31
101 green 101 green
103 red
103 red
COMP-421: Database Systems - SQL Queries I
bid color
13
Difference

Find sailors that have reserved a red but not a
green boat
SELECT R.sid
FROM Reserves
WHERE R.bid =
EXCEPT
SELECT R.sid
FROM Reserves
WHERE R.bid =
R, Boats B
B.bid AND B.color = ‘red’
(Oracle: MINUS)
R, Boats B
B.bid AND B.color = ‘green’
COMP-421: Database Systems - SQL Queries I
14
Multiset Semantic

A multiset (bag) may contain the same tuple more than once,
although there is no specified order (unlike a list).
 Example: {1, 2, 1, 3} is a multiset, but not a set

Multiset Union:
 Sum the times an element appears in the two multisets
 Example: {1, 2, 2}  {1, 2, 3, 3} = {1, 1, 2, 2, 2, 3, 3}

Multiset Intersection:
 Take the minimum of the number of occurrences in each multiset.
 Example: {1, 2, 2}  {1, 1, 2, 2, 3, 3} = {1, 2,2}

Multiset Difference:
 Subtract the number of occurrences in the two multisets
 Examples: {1, 2, 2} - {1, 2, 3, 3} = {2}

Some familiar laws for sets also hold for multisets (e.g., union is
commutative); but other laws do not hold (e.g., R  (S  T)  (R
 S)  (R  T)
COMP-421: Database Systems - SQL Queries I
15
Multiset Semantic in SQL
Although SQL generally works with multisets, it
uses set semantic for
union/intersection/difference
 To enforce multiset semantic for these
operators use

 UNION ALL, INTERSECT ALL, EXCEPT ALL
SELECT R.sid
FROM Reserves
WHERE R.bid =
UNION ALL
SELECT R.sid
FROM Reserves
WHERE R.bid =
R, Boats B
B.bid AND B.color = ‘red’
R, Boats B
B.bid AND B.color = ‘green’
COMP-421: Database Systems - SQL Queries I
16
Nested queries: The IN operator





A where clause can itself contain an SQL query. The
inner query is called a subquery
Find names of sailors who have reserved boat #103
SELECT S.sname
FROM Sailors S
WHERE S.sid IN (SELECT R.sid
FROM Reserves R
WHERE R.bid = 103)
To find sailors who have NOT reserved boat #103 use
NOT IN
Semantics best understood by nested loop assignment
Multiple attributes:
 WHERE (a1,a2) IN (SELECT a3, a4…
COMP-421: Database Systems - SQL Queries I
17
Exists Operator
EXISTS (relation) is true iff the relation is non-empty
Find names of sailors who have reserved boat #103
SELECT S.sname
FROM Sailors S
WHERE EXISTS (SELECT *
FROM Reserves R
WHERE R.bid = 103 AND
R.sid = S.sid)
 Scoping rule: to refer to outer Sailors in the inner subquery, we
need to give a range variable to the outer relation.
 A subquery that refers to values from a surrounding query is called
a correlated subquery.
 Since the inner query depends on the row of the outer query it
must be reevaluated for each row in the outer query


COMP-421: Database Systems - SQL Queries I
18
Quantifiers


ANY and ALL behave as existential and universal
quantifiers, respectively.
Syntax
 WHERE attr op ANY (SELECT …
 WHERE attr op ALL (SELECT
 op is one of , , , , , 

Find the sailors with the highest rating
SELECT *
FROM Sailors
WHERE rating  ALL (SELECT rating
FROM Sailors)
COMP-421: Database Systems - SQL Queries I
19
Complex queries (Division)

Find sailors who have reserved all boats
SELECT 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 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))
COMP-421: Database Systems - SQL Queries I
20