Lecture 12 - SQL and moreSQL#
Announcements#
Homework 3 to be posted later today or by tomorrow the latest, due next week on thursday
Exam 2 grades to be released later today
Lecture Exercise 11 deadline was extended to today at midnight
Lecture Exercise 12 to be posted tomorrow
No class on monday!
Today’s class#
Review of main SQL syntax
Outer join
load_ext sql
%config SqlMagic.displaylimit = None
%sql --section baking
Find episodes in which somebody who was a favorite in the previous episode won the star baker.
For each baker, find the number of times they used chocolate in their showstopper makes.
Find bakers who used chocolate more than 2 times in their showstopper makes.
Find episodes in which two people were eliminated.
Find episodes in which nobody was eliminated.
Find bakers and episodes in which the baker used chocolate either in the signature make or showstopper make.
Find bakers who was a star baker at least once, but never won in a technical challenge.
Find bakers who were favorite at least twice and won star baker at least twice.
%%sql
-- 1. Find episodes in which somebody who was a favorite
-- in the previous episode won the star baker.
select DISTINCT
r.episodeid
from
results r
, favorites f
where
r.baker = f.baker
and r.episodeid = f.episodeid+1
and r.result = 'star baker'
| episodeid |
|---|
| 4 |
%%sql
-- 2. For each baker, find the number of times they used
-- chocolate in their showstopper makes.
select
baker
, count(*) as nummakes
from
showstoppers s
where
lower(s.make) like '%chocolate%'
group by
s.baker
order by
nummakes desc
;
| baker | nummakes |
|---|---|
| Ruby | 3 |
| Manon | 2 |
| Rahul | 2 |
| Karen | 1 |
| Antony | 1 |
| Luke | 1 |
| Kim-Joy | 1 |
| Briony | 1 |
| Dan | 1 |
SELECT
FROM relations
WHERE …
GROUP BY a,b
HAVING
%%sql
-- 3. Find bakers who used chocolate more than 2 times
-- in their showstopper makes.
select
baker
, count(*) as nummakes
from
showstoppers s
where
lower(s.make) like '%chocolate%'
group by
s.baker
having
count(*)>1
;
| baker | nummakes |
|---|---|
| Manon | 2 |
| Rahul | 2 |
| Ruby | 3 |
%%sql
-- 4. Find id and airdate of episodes in which
-- two people were eliminated.
select
r.episodeid
, e.firstaired
from
results r
, episodes e
where
r.episodeid = e.id
and r.result = 'eliminated'
group by
r.episodeid
, e.firstaired
having
count(*) = 2;
| episodeid | firstaired |
|---|---|
| 5 | 2018-09-25 |
%%sql
-- 4. Find id and airdate of episodes in which
-- two people were eliminated.
select
e.id
, e.firstaired
from
results r
, episodes e
where
r.episodeid = e.id
and r.result = 'eliminated'
group by
e.id
having
count(*) = 2;
| id | firstaired |
|---|---|
| 5 | 2018-09-25 |
%%sql
-- 5. Find episodes in which nobody was eliminated.
select id -- all episodes
from episodes
except
select -- episodes in which someone is eliminated
r.episodeid
from
results r
where
r.result = 'eliminated' ;
| id |
|---|
| 4 |
| 10 |
%%sql
-- 6. Find bakers and episodes in which the baker used
-- chocolate either in the signature make or showstopper make.
select
baker, episodeid
from
showstoppers
where
lower(make) like '%chocolate%'
union all
select
baker, episodeid
from
signatures
where
lower(make) like '%chocolate%' ;
| baker | episodeid |
|---|---|
| Manon | 1 |
| Briony | 2 |
| Dan | 2 |
| Karen | 2 |
| Luke | 2 |
| Rahul | 2 |
| Ruby | 2 |
| Antony | 3 |
| Kim-Joy | 4 |
| Manon | 4 |
| Ruby | 4 |
| Rahul | 7 |
| Ruby | 7 |
| Imelda | 1 |
%%sql
-- 7. Find bakers who was a star baker at least once,
-- but never won in a technical challenge.
select -- bakers who were star baker
baker
from
results
where
result = 'star baker'
except
select -- bakers who won the technical challenge
baker
from
technicals
where
rank =1 ;
| baker |
|---|
| Manon |
%%sql
-- 8. Find bakers who were favorite at least twice and
-- won star baker at least twice.
select
r.baker
from
favorites f
, results r
where
f.baker = r.baker
and r.result = 'star baker'
group by
r.baker
having
count(distinct r.episodeid)>= 2
and count(distinct f.episodeid)>= 2
| baker |
|---|
| Rahul |
%%sql
-- Sanity check for count, to see if we are returning the
-- correct results for query 8!
select
r.baker, f.episodeid as fav, r.episodeid as sb
from
favorites f
, results r
where
f.baker = r.baker
and r.result = 'star baker'
order by r.baker asc;
| baker | fav | sb |
|---|---|---|
| Briony | 8 | 6 |
| Briony | 1 | 6 |
| Dan | 2 | 4 |
| Dan | 3 | 4 |
| Kim-Joy | 9 | 5 |
| Kim-Joy | 9 | 7 |
| Rahul | 5 | 3 |
| Rahul | 6 | 3 |
| Rahul | 6 | 2 |
| Rahul | 4 | 3 |
| Rahul | 5 | 2 |
| Rahul | 7 | 3 |
| Rahul | 7 | 2 |
| Rahul | 4 | 2 |
| Ruby | 6 | 9 |
| Ruby | 6 | 8 |
Inner vs. Outer Join#
– Inner join of two relations A and B returns only those tuples that match the join condition. The other tuples are eliminated from both relations.
In A JOIN B, A is the left relation and B is right relation
A LEFT OUTER JOIN B ON condition: tuples in (A INNER JOIN B) union all (all tuples in A that did not join with B on the given condition)
A RIGHT OUTER JOIN B ON condition: tuples in (A INNER JOIN B) union all (all tuples in B that did not join with A on the given condition)
A FULL OUTER JOIN B on condition:
tuples in (A INNER JOIN B) union all
(all tuples in B that did not join with A on the given condition) union all
(all tuples in A that did not join with B on the given condition)
%%sql
drop table a;
drop table b;
create table a(aid int, val varchar(2));
create table b(bid int, val varchar(2));
insert into a values(1,'a');
insert into a values(2,'b');
insert into a values(4,'c');
insert into b values(2,'d');
insert into b values(4,'e');
insert into b values(4,'g');
insert into b values(5,'f');
%%sql
select * from a;
| aid | val |
|---|---|
| 1 | a |
| 2 | b |
| 4 | c |
%%sql
select * from b;
| bid | val |
|---|---|
| 2 | d |
| 4 | e |
| 4 | g |
| 5 | f |
%%sql
select aid, a.val as avalue, bid, b.val as bvalue from a,b where a.aid=b.bid;
| aid | avalue | bid | bvalue |
|---|---|---|---|
| 2 | b | 2 | d |
| 4 | c | 4 | e |
| 4 | c | 4 | g |
%%sql
select aid, a.val as avalue, bid, b.val as bvalue
from a left outer join b on a.aid=b.bid;
| aid | avalue | bid | bvalue |
|---|---|---|---|
| 1 | a | None | None |
| 2 | b | 2 | d |
| 4 | c | 4 | e |
| 4 | c | 4 | g |
%%sql
select aid, a.val as avalue, bid, b.val as bvalue
from a right outer join b on a.aid=b.bid;
| aid | avalue | bid | bvalue |
|---|---|---|---|
| 2 | b | 2 | d |
| 4 | c | 4 | e |
| 4 | c | 4 | g |
| None | None | 5 | f |
%%sql
select aid, a.val as avalue, bid, b.val as bvalue
from a full outer join b on a.aid=b.bid;
| aid | avalue | bid | bvalue |
|---|---|---|---|
| 1 | a | None | None |
| 2 | b | 2 | d |
| 4 | c | 4 | e |
| 4 | c | 4 | g |
| None | None | 5 | f |
%%sql
-- For each baker, find the number of episodes
-- they were favorite in
-- all bakers who were favorites
-- and all bakers who never appeared in favorite relation!
select
b.baker
, count(episodeid) as numepisodes
, count(*) as numtuples
from
bakers b left join
favorites f
on b.baker = f.baker
group by
b.baker
;
| baker | numepisodes | numtuples |
|---|---|---|
| Rahul | 4 | 4 |
| Imelda | 0 | 1 |
| Jon | 2 | 2 |
| Karen | 0 | 1 |
| Ruby | 1 | 1 |
| Luke | 0 | 1 |
| Dan | 2 | 2 |
| Antony | 0 | 1 |
| Manon | 0 | 1 |
| Briony | 2 | 2 |
| Terry | 0 | 1 |
| Kim-Joy | 1 | 1 |
%%sql
-- For each baker, find the number of episodes
-- they won star baker
select
b.baker
, count(r.episodeid)
from
bakers b
left join results r
on b.baker = r.baker and r.result = 'star baker'
group by
b.baker
| baker | count |
|---|---|
| Rahul | 2 |
| Imelda | 0 |
| Jon | 0 |
| Karen | 0 |
| Ruby | 2 |
| Luke | 0 |
| Dan | 1 |
| Antony | 0 |
| Manon | 1 |
| Briony | 1 |
| Terry | 0 |
| Kim-Joy | 2 |
%%sql
-- Find episodes in which no one was eliminated
select
e.id, r.episodeid
from
episodes e
left join
results r
on r.episodeid = e.id and r.result = 'eliminated'
| id | episodeid |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | None |
| 5 | 5 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
| 10 | None |
%%sql
-- Find episodes in which no one was eliminated
select
e.id
from
episodes e
left join
results r
on r.episodeid = e.id and r.result = 'eliminated'
where
r.episodeid is null
| id |
|---|
| 4 |
| 10 |