Introduction to Database Systems
Lecture 7: SQL Wrap-up
1
Recap from last lecture
• Subqueries can occur in many clauses:
– SELECT
– FROM
– WHERE
• Monotone queries: SELECT-FROM-WHERE
– Existential quantifier
• Non-monotone queries
– Universal quantifier
– Aggregation
2
Examples of Complex Queries
Likes(drinker, beer)
Frequents(drinker, bar)
Serves(bar, beer)
1. Find drinkers that frequent some bar that serves some beer they like.
2. Find drinkers that frequent some bar that serves only beers they don’t like.
3. Find drinkers that frequent only bars that serves some beer they like.
Likes(drinker, beer)
Frequents(drinker, bar)
Serves(bar, beer) Example 1
Find drinkers that frequent some bar that serves some beer they like.
SELECT DISTINCT [Link]
FROM Frequents X, Serves Y, Likes Z
WHERE [Link] = [Link] AND
[Link] = [Link] AND
[Link] = [Link]
drinker + bar they frequent + beer served that they like
=> drinker is an answer
(even though we only want the drinker,
we need the rest to know it’s an answer.) 5
Likes(drinker, beer)
Frequents(drinker, bar)
Serves(bar, beer) Example 1
Find drinkers that frequent some bar that serves some beer they like.
SELECT DISTINCT [Link]
FROM Frequents X, Serves Y, Likes Z
WHERE [Link] = [Link] AND
[Link] = [Link] AND
[Link] = [Link]
What happens if we didn’t write DISTINCT?
5
Likes(drinker, beer)
Frequents(drinker, bar)
Serves(bar, beer) Example 2
Find drinkers that frequent some bar that serves only beers they don’t like
Existential Universal
6
Likes(drinker, beer)
Frequents(drinker, bar)
Serves(bar, beer) Example 2
Find drinkers that frequent some bar that serves only beers they don’t like
bar serves only beers that X does not like =
bar that does NOT serve some beer that X does like
Let’s find the others (drop the NOT):
Drinkers that frequent some bars that serves some beer they like.
7
Likes(drinker, beer)
Frequents(drinker, bar)
Serves(bar, beer) Example 2
Find drinkers that frequent some bar that serves only beers they don’t like
Let’s find the others (drop the NOT):
Drinkers that frequent some bars that serves some beer they like.
That’s the previous query…
SELECT DISTINCT [Link]
FROM Frequents X, Serves Y, Likes Z
WHERE [Link] = [Link] AND
[Link] = [Link] AND
[Link] = [Link]
8
Likes(drinker, beer)
Frequents(drinker, bar)
Serves(bar, beer) Example 2
Find drinkers that frequent some bar that serves only beers they don’t like
Let’s find the others (drop the NOT):
Drinkers that frequent some bars that serves some beer they like.
That’s the previous query… Let’s write it with a subquery:
SELECT DISTINCT [Link]
FROM Frequents X
WHERE EXISTS (SELECT *
FROM Serves Y, Likes Z
WHERE [Link]=[Link] AND
[Link]=[Link] AND
[Link] = [Link]) 10
Likes(drinker, beer)
Frequents(drinker, bar)
Serves(bar, beer) Example 2
Find drinkers that frequent some bar that serves only beers they don’t like
Let’s find the others (drop the NOT):
Drinkers that frequent some bars that serves some beer they like.
bar serves only beers that X does not like =
bar that does NOT serve some beer that X does like
Now negate!
SELECT DISTINCT [Link]
FROM Frequents X
WHERE NOT EXISTS (SELECT *
FROM Serves Y, Likes Z
WHERE [Link]=[Link] AND
[Link]=[Link] AND
[Link] = [Link]) 11
Likes(drinker, beer)
Frequents(drinker, bar)
Serves(bar, beer) Example 3
Find drinkers that frequent only bars that serves some beer they like.
Universal Existential
11
Likes(drinker, beer)
Frequents(drinker, bar)
Serves(bar, beer) Example 3
Find drinkers that frequent only bars that serves some beer they like.
X frequents only bars that serve some beer X likes =
X does NOT frequent some bar that serves only beer X doesn’t like
Let’s find the others (drop the NOT):
Drinkers that frequent some bar that serves only beer they don’t like.
12
Likes(drinker, beer)
Frequents(drinker, bar)
Serves(bar, beer) Example 3
Find drinkers that frequent only bars that serves some beer they like.
Let’s find the others (drop the NOT):
Drinkers that frequent some bar that serves only beer they don’t like.
That’s the previous query!
13
Likes(drinker, beer)
Frequents(drinker, bar)
Serves(bar, beer) Example 3
Find drinkers that frequent only bars that serves some beer they like.
Let’s find the others (drop the NOT):
Drinkers that frequent some bar that serves only beer they don’t like.
That’s the previous query!
SELECT DISTINCT [Link]
FROM Frequents X
WHERE NOT EXISTS (SELECT *
FROM Serves Y, Likes Z
WHERE [Link]=[Link] AND
[Link]=[Link] AND
[Link] = [Link]) 15
Likes(drinker, beer)
Frequents(drinker, bar)
Serves(bar, beer) Example 3
Find drinkers that frequent only bars that serves some beer they like.
Let’s find the others (drop the NOT):
Drinkers that frequent some bar that serves only beer they don’t like.
That’s the previous query! But write it as a nested query:
SELECT DISTINCT [Link]
FROM Frequents U
WHERE [Link] IN
(SELECT DISTINCT [Link]
FROM Frequents X
WHERE NOT EXISTS (SELECT *
FROM Serves Y, Likes Z
WHERE [Link]=[Link] AND
[Link]=[Link] AND 16
[Link] = [Link]))
Likes(drinker, beer)
Frequents(drinker, bar)
Serves(bar, beer) Example 3
Find drinkers that frequent only bars that serves some beer they like.
Let’s find the others (drop the NOT):
Drinkers that frequent some bar that serves only beer they don’t like.
X frequents only bars that serve some beer X likes =
X does NOT frequent some bar that serves only beer X doesn’t like
Now need three
Now negate! nested queries
SELECT DISTINCT [Link]
FROM Frequents U
WHERE [Link] NOT IN
(SELECT DISTINCT [Link]
FROM Frequents X
WHERE NOT EXISTS (SELECT *
FROM Serves Y, Likes Z
WHERE [Link]=[Link] AND
[Link]=[Link] AND 17
[Link] = [Link]))
Product (pname, price, cid)
Company(cid, cname, city)
Unnesting Aggregates
Find the number of companies in each city
SELECT DISTINCT [Link], (SELECT count(*)
FROM Company Y
WHERE [Link] = [Link])
FROM Company X
SELECT city, count(*)
FROM Company Note: no need for DISTINCT
GROUP BY city (DISTINCT is the same as GROUP BY)
17
Product (pname, price, cid)
Company(cid, cname, city)
Unnesting Aggregates
Find the number of companies in each city
SELECT DISTINCT [Link], (SELECT count(*)
FROM Company Y
WHERE [Link] = [Link])
FROM Company X
SELECT city, count(*)
FROM Company Equivalent queries
GROUP BY city
18
Product (pname, price, cid)
Company(cid, cname, city)
Unnesting Aggregates
Find the number of companies in each city
SELECT DISTINCT [Link], (SELECT count(*)
FROM Company Y
WHERE [Link] = [Link])
FROM Company X
SELECT city, count(*)
FROM Company Wait… are they equivalent?
GROUP BY city
19
Purchase(pid, product, quantity, price)
Grouping vs. Nested Queries
SELECT product, Sum(quantity) AS TotalSales
FROM Purchase
WHERE price > 1
GROUP BY product
SELECT DISTINCT [Link], (SELECT Sum([Link])
FROM Purchase y
WHERE [Link] = [Link]
AND [Link] > 1)
AS TotalSales
FROM Purchase x
WHERE [Link] > 1
Why twice ? 21
Author(login, name)
Wrote(login, url)
More Unnesting
Find authors who wrote ≥ 10 documents: This is
Attempt 1: with nested queries SQL by
a novice
SELECT [Link]
FROM Author
WHERE 10 <= (SELECT count(url)
FROM Wrote
WHERE [Link]=[Link])
21
Author(login, name)
Wrote(login, url)
More Unnesting
Find authors who wrote ≥ 10 documents:
Attempt 1: with nested queries
Attempt 2: using GROUP BY and HAVING
SELECT name
FROM Author, Wrote This is
WHERE [Link]=[Link] SQL by
GROUP BY name an expert
HAVING count(url) >= 10
22
Product (pname, price, cid)
Company(cid, cname, city)
Finding Witnesses
For each city, find the most expensive product made in that city
Finding the maximum price is easy…
SELECT [Link], max([Link])
FROM Company x, Product y
WHERE [Link] = [Link]
GROUP BY [Link];
But we need the witnesses, i.e. the products with max price
23
Product (pname, price, cid)
Company(cid, cname, city)
Finding Witnesses
For each city, find the most expensive product made in that city
To find the witnesses:
compute the maximum price in a subquery
SELECT DISTINCT [Link], [Link], [Link]
FROM Company u, Product v,
Not a bad
(SELECT [Link], max([Link]) as maxprice solution…
FROM Company x, Product y
WHERE [Link] = [Link]
GROUP BY [Link]) w
WHERE [Link] = [Link]
and [Link] = [Link]
and [Link]=[Link];
24
Product (pname, price, cid)
Company(cid, cname, city)
Finding Witnesses
For each city, find the most expensive product made in that city
Or we can use a subquery in where clause
SELECT [Link], [Link], [Link]
FROM Company u, Product v
WHERE [Link] = [Link] AND
[Link] >= ALL (SELECT [Link]
FROM Company x, Product y
WHERE [Link]=[Link]
and [Link]=[Link]);
25
Product (pname, price, cid)
Company(cid, cname, city)
Finding Witnesses
For each city, find the most expensive product made in that city
There is a more concise solution here:
Idea: Product JOIN Product ON “made in the same city”
Then group by first product.
Then check that first product has equal or higher price than
each of the second products in the group.
SELECT [Link], [Link], [Link]
FROM Company u, Product v, Company x, Product y
WHERE [Link] = [Link] and [Link] = [Link] and [Link] = [Link]
GROUP BY [Link], [Link], [Link]
HAVING [Link] = max([Link]);
26
