Lecture 13 - Even more SQL#
Announcements
No new lecture exercise today, Lex12 due on monday at midnight.
Expect a new homework today or tomorrow with more complex SQL queries.
SQL pointers#
There is more than one way to write a query. The performance of the query is determined by a combination of different factors.
Databases differ in how the data are stored and which indices are available.
Each query is sent to a very powerful query optimizer that can find the most efficient way to execute a query. The way you write the query may make it difficult for the optimizer to find some good execution methods.
DB servers differ in their implementation of query operations and how they keep track of the data statistics.
Database queries don’t follow the basic programming logic of modularity. It is good to push some complexity to queries. The main point is figuring out when to break up a query to modules.
Wisdom from an older db programmer: Learn to write a query in multiple ways. Check the expected and real costs.
explain SQLquery;
will show you the expected cost of a query in Postgresql.
load_ext sql
%sql --section baking
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)
Find all bakers who have used an orange in the showstopper or signature challenge. Return the name of the baker and the number of episodes in which they competed in.
%%sql
-- 1. Find the episodes in which no one was chosen to be a favorite.
-- Return the episodeid and air date of the episode.
-- (all episodes) except (episodes with favorites)
select id, firstaired from episodes
except
select e.id, e.firstaired
from favorites f, episodes e
where f.episodeid = e.id;
| id | firstaired |
|---|---|
| 10 | 2018-10-30 |
%%sql
-- 2. For each baker, find the total number of shows in which they were a
-- favorite, the number of shows in which they were 'star baker' and the
-- episode id of the show in which they were eliminated.
select
f.baker
, r2.episodeid as wheneliminated
, count(distinct f.episodeid) as numfav
, count(distinct r.episodeid) as numstar
from
favorites f
, results r
, results r2
where
f.baker = r.baker
and r.result = 'star baker'
and r2.baker = r.baker
and r2.result = 'eliminated'
group by
f.baker
, r2.episodeid ;
| baker | wheneliminated | numfav | numstar |
|---|---|---|---|
| Briony | 9 | 2 | 1 |
| Dan | 6 | 2 | 1 |
%%sql
-- Find all bakers who have used an orange in the showstopper or
-- signature challenge. Return the name of the baker and the number of
-- episodes in which they competed in.
select
s.baker
, count(distinct s2.episodeid) as numepisodes
from
showstoppers s, showstoppers s2
where
lower(s.make) like '%orange%'
and s.baker = s2.baker
group by
s.baker
union
select
s.baker
, count(distinct s2.episodeid) as numepisodes
from
signatures s, showstoppers s2
where
lower(s.make) like '%orange%'
and s.baker = s2.baker
group by
s.baker ;
| baker | numepisodes |
|---|---|
| Antony | 3 |
| Briony | 9 |
| Dan | 6 |
| Kim-Joy | 10 |
| Rahul | 10 |
| Ruby | 10 |
Note you can write above query (3) using joins only as seen below, but this join is not very selective (it is mostly a Cartesian product) and as a result this query is grossly inefficient (use explain to see for yourself).
select
t.baker
, count(distinct t.episodeid) as numepisodes
from
signatures s, showstoppers s2, technicals t
where
(lower(s.make) like '%orange%' and s.baker = t.baker)
or (lower(s2.make) like '%orange%' and s2.baker = t.baker)
group by
t.baker ;
OUTER JOIN vs. INNER JOIN#
select baker from bakers ;
select distinct baker from favorites;
select distinct baker from results where result = 'star baker' ;
This query only returns bakers who were both star baker and were favorite.
select distinct r.baker from favorites f, results r
where r.baker = f.baker and r.result = 'star baker' ;
This query only returns bakers who were favorite.
select distinct b.baker, f.episodeid from bakers b, favorites f
where f.baker = b.baker ;

%%sql
-- For each baker, find all episodes in which they were a favorite
select distinct
b.baker
, f.episodeid
from
bakers b left join
favorites f on f.baker = b.baker
order by b.baker
;
| baker | episodeid |
|---|---|
| Antony | None |
| Briony | 1 |
| Briony | 8 |
| Dan | 2 |
| Dan | 3 |
| Imelda | None |
| Jon | 2 |
| Jon | 4 |
| Karen | None |
| Kim-Joy | 9 |
%%sql
-- For each baker, find all episodes in which they were a favorite
select
b.baker
, count(f.episodeid) as numfav
from
bakers b left join
favorites f on f.baker = b.baker
group by b.baker
;
| baker | numfav |
|---|---|
| Rahul | 4 |
| Imelda | 0 |
| Jon | 2 |
| Karen | 0 |
| Ruby | 1 |
| Luke | 0 |
| Dan | 2 |
| Antony | 0 |
| Manon | 0 |
| Briony | 2 |
%%sql
-- For each baker, find the number of episodes in which
-- they were a star baker.
-- (bakers) left join/
-- keep bakers even if they don't participate in the join
-- (bakers who were star baker)
select
b.baker
, count(r.episodeid) as numep
from
bakers b left join
results r on b.baker = r.baker and r.result = 'star baker'
group by
b.baker ;
| baker | numep |
|---|---|
| Rahul | 2 |
| Imelda | 0 |
| Jon | 0 |
| Karen | 0 |
| Ruby | 2 |
| Luke | 0 |
| Dan | 1 |
| Antony | 0 |
| Manon | 1 |
| Briony | 1 |
%%sql
-- 2. For each baker, find the total number of shows in which they were a
-- favorite, the number of shows in which they were 'star baker' and the
-- episode id of the show in which they were eliminated.
select
b.baker
, r2.episodeid as wheneliminated
, count(distinct f.episodeid) as numfav
, count(distinct r.episodeid) as numstar
from
bakers b
left join favorites f on f.baker = b.baker
left join results r on b.baker = r.baker and r.result = 'star baker'
left join results r2 on b.baker = r2.baker and r2.result = 'eliminated'
group by
b.baker
, r2.episodeid
order by
b.baker asc;
| baker | wheneliminated | numfav | numstar |
|---|---|---|---|
| Antony | 3 | 0 | 0 |
| Briony | 9 | 2 | 1 |
| Dan | 6 | 2 | 1 |
| Imelda | 1 | 0 | 0 |
| Jon | 7 | 2 | 0 |
| Karen | 5 | 0 | 0 |
| Kim-Joy | None | 1 | 2 |
| Luke | 2 | 0 | 0 |
| Manon | 8 | 0 | 1 |
| Rahul | None | 4 | 2 |
%%sql
-- 1. Find the episodes in which no one was chosen to be a favorite.
-- Return the episodeid and air date of the episode.
-- You can now do set subtraction using left join
select
e.id
, e.firstaired
from
episodes e
left join favorites f on e.id = f.episodeid
where
f.episodeid is null;
| id | firstaired |
|---|---|
| 10 | 2018-10-30 |
%%sql
-- For inner join, using join in from or using in WHERE
-- are identical logically and for execution costs
select
f.baker
, r2.episodeid as wheneliminated
, count(distinct f.episodeid) as numfav
, count(distinct r.episodeid) as numstar
from
favorites f
join results r on f.baker = r.baker
join results r2 on r2.baker = r.baker
where
r.result = 'star baker'
and r2.result = 'eliminated'
group by
f.baker
, r2.episodeid ;
| baker | wheneliminated | numfav | numstar |
|---|---|---|---|
| Briony | 9 | 2 | 1 |
| Dan | 6 | 2 | 1 |
%%sql
select
b.baker
, count(distinct r.episodeid) as numep
, min(t.rank) as besttechnical
from
bakers b left join
results r on b.baker = r.baker and r.result = 'star baker'
, technicals t
where
t.baker = b.baker
group by
b.baker ;
| baker | numep | besttechnical |
|---|---|---|
| Antony | 0 | 8 |
| Briony | 1 | 1 |
| Dan | 1 | 1 |
| Imelda | 0 | 11 |
| Jon | 0 | 1 |
| Karen | 0 | 5 |
| Kim-Joy | 2 | 1 |
| Luke | 0 | 7 |
| Manon | 1 | 2 |
| Rahul | 2 | 1 |
Advanced SQL#
Queries may return:
Multiple tuples - treat it like a relation/table in the database
Single number - treat the query like a number (Scalar queries)
%%sql
-- Find all bakers who won star baker more times than 'Briony'
select
baker
from
results
where
result = 'star baker'
group by
baker
having
count(*) > (select count(*) from results
where result = 'star baker'
and baker = 'Briony');
| baker |
|---|
| Rahul |
| Ruby |
| Kim-Joy |
%%sql
-- Find all episodes after the episode that 'Briony'
-- was eliminated in.
select
e.id
from
episodes e
where
e.id > (select episodeid from results
where baker = 'Briony' and result = 'eliminated')
| id |
|---|
| 10 |