The set of
beers Steve or Bill
likes
SELECT name, manf
FROM Beers
WHERE name IN (
SELECT beer FROM Likes WHERE drinker = 'Steve'
or drinker = 'Bill'
);

Example
65

Without subquery
•
Does this query produce the same result?
66
SELECT name, manf
FROM Beers b, Likes l
WHERE b.name = l.beer
and l.drinker = 'Steve' or l.drinker = 'Bill';

Correct equivalent subquery
•
Note the "distinct" and grouping (using
parentheses) of two conditions on drinker
67
SELECT
distinct
name, manf
FROM Beers b, Likes l
WHERE b.name = l.beer
and
(l.drinker = 'Steve' or l.drinker = 'Bill');

Introduced by comparison operators
•
<comparison operator> <any/all> (subquery)
– Comparison operators: =, !=, <, >, <=, >=, <>
• Examples
– x >= all (subquery)
– x <= all (subquery)
– x = any (subquery) // equivalent to "x
in (subquery)"
– x != all (subquery) // equivalent to "x not in
(subquery)"
•
Is "x != any (subquery)" equivalent to "x not in
(subquery)"?
68

69
Example
•
From Sells(bar, beer, price), find the beer(s) sold
for the highest price.
•
What about beers with "the lowest price"?
price from the outer
Sells must not be
less than any price.
SELECT beer
FROM Sells
WHERE price
>= ALL
(
SELECT price
FROM Sells
WHERE price is not NULL);

Introduced by "exists" or "not exists"
•
Both form a boolean expression
•
exists (subquery)
– Evaluated to true if subquery has at least one result
•
not exists (subquery)
– Evaluated to true if subquery has no results
70

71
Example Query with EXISTS
•
What does this query do?
select name
from Beers b1
where not exists (
select name
from Beers b2
where
b2.name <> b1.name and b2.manf = b1.manf);

Agenda
•
SQL DML (Data Manipulation Language)
– SQL query
– Relations as bags
– Joins
– Grouping and aggregation
– Database modification
•
SQL DDL (Data Definition Language)
– Define schema
72

73
Bag Semantics for SFW Queries
•
The SELECT-FROM-WHERE statement uses
bag semantics
– Selection: preserve the number of occurrences
– Projection: preserve the number of occurrences (no
duplicate elimination)
– Cartesian product, join: no duplicate elimination

Set Operations on Bags
• Union:
{a,b,b,c} U {a,b,b,b,e,f,f} =
{a,a,b,b,b,b,b,c,e,f,f}
–
add
the number of occurrences
•
Difference: {a,b,b,b,c,c} - {b,c,c,c,d} = {a,b,b}
– subtract the number of occurrences
•
Intersection: {a,b,b,b,c,c}
{b,b,c,c,c,c,d} = {b,b,c,c}
– minimum of the two numbers of occurrences
74

75
Union, Intersection, and Difference
•
Union, intersection, and difference of relations
are expressed by the following forms, each
involving subqueries:
– ( subquery ) UNION ( subquery )
– ( subquery ) INTERSECT ( subquery )
– ( subquery ) EXCEPT ( subquery )

76
Set Semantics for Set Operations
•
Although the SELECT-FROM-WHERE
statement uses bag semantics, the default for
union, intersection, and difference
is
set
semantics.
– That is, duplicates are eliminated as the operation is
applied.

77
Motivation: Efficiency
•
When doing projection, it is easier to avoid
eliminating duplicates.

#### You've reached the end of your free preview.

Want to read all 167 pages?

- Fall '14
- From Sells