Lecture 14- Advanced SQL#
Announcements
Hw#4 is out due next monday
Lecture exercise 13 out today, due on thursday at 2pm.
Advanced Constructs#
Scalar queries: queries that return a single value
Queries as a set of tuples
value in/not in (set)
value >=ALL (set)
value <=ALL (set)
value >ALL (set)
value <ALL (set)
value =ALL (set)
value <>ALL (set)
Anonymous and non-anonymous queries as new relations in FROM or using WITH
A query is anonymous if it only exists for a single query (regardless of where it is located)
Correlated and uncorrelated subqueries
NOT EXISTS (set) : if the set is empty, then not exists is true!
For all queries
Baker database#
Bakers(baker, fullname, age, occupation, hometown)
Episodes(id, title, firstaired, viewers7day, signature, technical, showstopper)
Favorites(episodeid, baker)
Results(episodeid, baker, result)
Showstoppers(episodeid, baker, make)
Signatures(episodeid, baker, make)
Technicals(episodeid, baker, rank)
load_ext sql
%sql --section baking
%%sql
-- Find all bakers who were in top 3 of technicals more times than 'Manon';
-- Within the subquery, local technicals overwrites the global one!
select
baker
, count(*) as numtimes
from
technicals --- <- global technicals
where
rank <= 3
group by
baker
having
count(*) > (select count(*) from technicals --<- local technicals
where rank <=3 and baker = 'Manon') ;
| baker | numtimes |
|---|---|
| Jon | 4 |
| Briony | 4 |
| Rahul | 5 |
| Ruby | 7 |
%%sql
-- You can use a scalar query anywhere in the query that uses a constant
select
baker
, count(*) as numtimes
, (select count(*) from technicals t1
where t1.rank <=3 and t1.baker = 'Manon') as nummanon
from
technicals
where
rank <= 3
group by
baker
having
count(*) > (select count(*) from technicals t1
where t1.rank <=3 and t1.baker = 'Manon') ;
| baker | numtimes | nummanon |
|---|---|---|
| Jon | 4 | 3 |
| Briony | 4 | 3 |
| Rahul | 5 | 3 |
| Ruby | 7 | 3 |
%%sql
-- In this case, the subquery is not needed but for a larger relation
-- it is likely that the subquery solution is much more efficient for this query.
select
t.baker
, count(distinct t.episodeid) as numtimes
from
technicals t
, technicals t2
where
t.rank <= 3
and t2.rank <= 3
and t2.baker = 'Manon'
and t.baker <> 'Manon'
group by
t.baker
having
count(distinct t.episodeid) > count(distinct t2.episodeid);
| baker | numtimes |
|---|---|
| Briony | 4 |
| Jon | 4 |
| Rahul | 5 |
| Ruby | 7 |
%%sql
-- value in (set of values)
-- value in (result of a query)
select
baker
, count(*) as numtimes
from
technicals
where
rank IN (1,2,3)
group by
baker;
| baker | numtimes |
|---|---|
| Manon | 3 |
| Jon | 4 |
| Briony | 4 |
| Rahul | 5 |
| Ruby | 7 |
| Dan | 3 |
| Terry | 1 |
| Kim-Joy | 3 |
%%sql
--- Find all episodes in which no one was eliminated.
-- Solution 1
-- select *
-- from episodes
-- where id not in (episodes in which someone was eliminated);
select id, title
from episodes
where id not in (select distinct episodeid from results
where result = 'eliminated') ;
| id | title |
|---|---|
| 4 | Desserts |
| 10 | Final |
%%sql
-- Solution 2
select id, title
from episodes
except
select e.id, e.title
from
episodes e, results r
where
e.id = r.episodeid and result = 'eliminated' ;
| id | title |
|---|---|
| 4 | Desserts |
| 10 | Final |
%%sql
-- Solution 3
select e.id, e.title
from episodes e left join results r
on e.id = r.episodeid and r.result = 'eliminated'
where
r.episodeid is null ;
| id | title |
|---|---|
| 4 | Desserts |
| 10 | Final |
%%sql
-- Find bakers who were star baker at least twice, but never in back to
-- back episodes.
select
r.baker
from
results r
where
r.result = 'star baker'
and r.baker not in (select r2.baker
from results r1, results r2
where r1.baker = r2.baker
and r1.episodeid +1 = r2.episodeid
and r1.result = 'star baker'
and r2.result = 'star baker')
group by
r.baker
having
count(*) >= 2;
| baker |
|---|
| Kim-Joy |
%%sql
-- Alternate solution with except
select
r.baker
from
results r
where
r.result = 'star baker'
group by
r.baker
having
count(*) >= 2
except
(select r2.baker
from results r1, results r2
where r1.baker = r2.baker
and r1.episodeid +1 = r2.episodeid
and r1.result = 'star baker'
and r2.result = 'star baker'
)
;
| baker |
|---|
| Kim-Joy |
%%sql
-- Find the episodes with more than the lowest number of favorites, return their
-- title and airdate.
-- First find counts
select
e.id
, count(f.episodeid) as numtimes
from
episodes e left join favorites f
on e.id = f.episodeid
group by
e.id ;
| id | numtimes |
|---|---|
| 4 | 2 |
| 10 | 0 |
| 6 | 2 |
| 2 | 2 |
| 9 | 1 |
| 7 | 1 |
| 3 | 1 |
| 1 | 1 |
| 5 | 1 |
| 8 | 1 |
%%sql
-- Find the lowest number, excluding the last episode
select distinct
count(f.episodeid) as numtimes
from
episodes e left join favorites f
on e.id = f.episodeid
where
e.id < 10
group by
e.id
order by numtimes asc
limit 1
;
| numtimes |
|---|
| 1 |
%%sql
-- Find the episodes with more than the lowest number of favorites, return their
-- title and airdate.
select distinct
e.id, e.title
, count(f.episodeid) as numfavs
from
episodes e left join favorites f
on e.id = f.episodeid
group by
e.id
having
count(f.episodeid) > (select distinct
count(f.episodeid) as numtimes
from
episodes e left join favorites f
on e.id = f.episodeid
group by e.id
order by numtimes asc
limit 1);
| id | title | numfavs |
|---|---|---|
| 1 | Biscuits | 1 |
| 4 | Desserts | 2 |
| 3 | Bread | 1 |
| 2 | Cakes | 2 |
| 8 | Danish | 1 |
| 5 | Spice | 1 |
| 6 | Pastry | 2 |
| 7 | Vegan | 1 |
| 9 | Pâtisserie (Semi-final) | 1 |
%%sql
-- Find the episodes with more than the lowest two number of favorites,
-- return their title and airdate.
select distinct
e.id, e.title
, count(f.episodeid) as numfavs
from
episodes e left join favorites f
on e.id = f.episodeid
group by
e.id
having
count(f.episodeid) >ALL (select distinct
count(f.episodeid) as numtimes
from
episodes e left join favorites f
on e.id = f.episodeid
group by e.id
order by numtimes asc
limit 2);
| id | title | numfavs |
|---|---|---|
| 4 | Desserts | 2 |
| 6 | Pastry | 2 |
| 2 | Cakes | 2 |
You can write queries more succinctly using WITH ()
WITH r1 as ( query1 )
, r2 as (query 2 )
select *
from r1, r2
where r1.a = r2.b ;
%%sql
--- Rewriting an earlier query
-- be very careful how to refer to a WITH query like any other relation
with
manonq as (select count(*) as num
from technicals t1
where t1.rank <=3 and t1.baker = 'Manon')
select
t.baker
, count(*) as numtimes
, mq.num
from
technicals t, manonq mq
where
t.rank <= 3
group by
t.baker, mq.num
having
count(*) > (select num from manonq) ;
| baker | numtimes | num |
|---|---|---|
| Rahul | 5 | 3 |
| Ruby | 7 | 3 |
| Briony | 4 | 3 |
| Jon | 4 | 3 |
%%sql
with counts as (select distinct
count(f.episodeid) as numtimes
from
episodes e left join favorites f
on e.id = f.episodeid
group by e.id
order by numtimes asc
limit 2)
select distinct
e.id, e.title
, count(f.episodeid) as numfavs
from
episodes e left join favorites f
on e.id = f.episodeid
group by
e.id
having
count(f.episodeid) >ALL (select numtimes from counts) ;
| id | title | numfavs |
|---|---|---|
| 4 | Desserts | 2 |
| 6 | Pastry | 2 |
| 2 | Cakes | 2 |
%%sql
-- not exists (set) -- return true if the set is empty.
-- Find bakers who were never eliminated
-- select * from bakers where not exists (a tuple for this baker
-- in results where they are eliminated)
select
*
from
bakers b
where
not exists (select * from results r
where r.baker = b.baker
and r.result = 'eliminated') ;
| baker | fullname | age | occupation | hometown |
|---|---|---|---|---|
| Kim-Joy | Kim-Joy Hewlett | 27 | Mental health specialist | Leeds |
| Rahul | Rahul Mandal | 30 | Research scientist | Rotherham |
| Ruby | Ruby Bhogal | 29 | Project manager | London |
%%sql
-- Find bakers who never improved in the technicals by more than a single
-- position in a subsequent episode
-- select
-- *
-- from
-- bakers b
-- where
-- not exists (two technical tuples showing improvement for this baker )
select
*
from
bakers b
where
not exists (select *
from technicals t1, technicals t2
where t1.baker = t2.baker and t1.baker = b.baker
and t2.rank - t1.rank > 1
and t1.episodeid > t2.episodeid) ;
| baker | fullname | age | occupation | hometown |
|---|---|---|---|---|
| Imelda | Imelda McCarron | 33 | Countryside recreation officer | County Tyrone |
| Luke | Luke Thompson | 30 | Civil servant/house and techno DJ | Sheffield |
%%sql
-- Can also use not in with a non-correlated query
select
*
from
bakers b
where
b.baker not in (select t1.baker
from technicals t1, technicals t2
where t1.baker = t2.baker
and t2.rank - t1.rank > 1
and t1.episodeid > t2.episodeid) ;
| baker | fullname | age | occupation | hometown |
|---|---|---|---|---|
| Imelda | Imelda McCarron | 33 | Countryside recreation officer | County Tyrone |
| Luke | Luke Thompson | 30 | Civil servant/house and techno DJ | Sheffield |
%%sql
-- FOR ALL QUERIES OFTEN requires two set subtractions
-- Find bakers who competed in all the episodes of the season
-- select * from bakers b
-- where not exists ( an episode in the seasons such that
-- there does not exist a tuple in technicals for this
-- baker and this episode)
select * from bakers b
where not exists
(select * from episodes e
where not exists (select * from technicals t
where t.baker = b.baker
and t.episodeid = e.id)) ;
| baker | fullname | age | occupation | hometown |
|---|---|---|---|---|
| Kim-Joy | Kim-Joy Hewlett | 27 | Mental health specialist | Leeds |
| Rahul | Rahul Mandal | 30 | Research scientist | Rotherham |
| Ruby | Ruby Bhogal | 29 | Project manager | London |
%%sql
-- Do not forget that much cheaper ways to write the same query may exist
select
b.baker
, b.fullname
from
bakers b
, technicals t
where
b.baker = t.baker
group by
b.baker
having
count(*) = (select count(*) from episodes) ;
| baker | fullname |
|---|---|
| Rahul | Rahul Mandal |
| Ruby | Ruby Bhogal |
| Kim-Joy | Kim-Joy Hewlett |
%%sql
-- Find the bakers with the highest total number of wins and technical
-- wins combined.
WITH counts as
(select b.baker
, count(distinct r.episodeid)+count(distinct t.episodeid) as numwins
from bakers b left join results r
on b.baker = r.baker and r.result = 'star baker'
left join technicals t
on b.baker = t.baker and t.rank = 1
group by b.baker
)
select baker, numwins
from counts
where numwins = (select max(numwins) from counts) ;
| baker | numwins |
|---|---|
| Ruby | 5 |