Lecture 12 - More on SQL#
Announcements
Class this week on Thursday and Friday
A new lecture exercise today, due on monday at midnight.
No lecture exercises tomorrow.
Expect a new homework this friday with more complex SQL queries.
SQL#
Industry standard
Bag oriented - not set
Learned about
string comparisons using LIKE
joins
DISTINCT for removal of duplicates
null values
date/time/timestamp data types.
Basic execution of operations
load_ext sql
%sql --section baking
%%sql
-- Find the name and age of all bakers who won star baker.
SELECT DISTINCT
b.fullname as bakername
, b.age
FROM
bakers b
, results r
WHERE
b.baker = r.baker
and r.result = 'star baker'
ORDER BY
bakername ASC ;
| bakername | age |
|---|---|
| Briony Williams | 33 |
| Dan Beasley-Harling | 36 |
| Kim-Joy Hewlett | 27 |
| Manon Lagrève | 26 |
| Rahul Mandal | 30 |
| Ruby Bhogal | 29 |
SET OPERATIONS#
UNION (set union) – tuples from R1 or R2
INTERSECT (set intersection) – tuples from R1 and R2
EXCEPT (set subtraction) – tuples from R1 and not from R2
Set/bag operation pointers:
Set compatibility is needed, so the SELECT operations must return the same schema.
Note the ordering of operations. The resulting schema will have the attribute names of the first relation.
Note when certain set/bag operations are needed, especially union, they will impact the performance signicantly.
%%sql
-- Find bakers who were star baker or a favorite
SELECT
baker as toprbaker
FROM
results
WHERE
result = 'star baker'
UNION
SELECT
baker as lowerrbaker
FROM
favorites
order by toprbaker
;
| toprbaker |
|---|
| Briony |
| Dan |
| Jon |
| Kim-Joy |
| Manon |
| Rahul |
| Ruby |
%%sql
-- Find bakers who were never in the top 5 of a technical episode.
-- (all bakers) - (all bakers who were in top 5)
SELECT
baker
FROM
bakers
EXCEPT
SELECT
baker
FROM
technicals
WHERE
rank <= 5;
| baker |
|---|
| Imelda |
| Luke |
| Antony |
BAG OPERATIONS (which retain copies of tuples!)#
Suppose t occurs m times in R and n times in S
R UNION ALL S = {Return t: m+n times}
R INTERSECT ALL S = {Return t: min(m,n) times}
R EXCEPT ALL S = {Return t: max(0, (m-n)) times}
%%sql
-- Find bakers who were favorite baker more times than
-- star baker;
SELECT
baker
FROM
favorites
EXCEPT ALL
SELECT
baker
FROM
results
WHERE
result = 'star baker';
| baker |
|---|
| Rahul |
| Rahul |
| Dan |
| Briony |
| Jon |
| Jon |
%%sql
-- Find all episodes they were a star baker or a favorite.
SELECT
baker
FROM
favorites
UNION ALL
SELECT
baker
FROM
results
WHERE
result = 'star baker'
;
| baker |
|---|
| Briony |
| Jon |
| Dan |
| Dan |
| Rahul |
| Jon |
| Rahul |
| Rahul |
| Ruby |
| Rahul |
%%sql
-- Return the full name and age of bakers....
SELECT
b.fullname
, b.age
FROM
favorites f
, bakers b
WHERE
f.baker = b.baker
EXCEPT
SELECT
b.fullname
, b.age
FROM
results r
, bakers b
WHERE
b.baker = r.baker
and result = 'star baker'
ORDER BY
fullname, age
;
| fullname | age |
|---|---|
| Jon Jenkins | 47 |
%%sql
-- Find bakers who were star baker or a favorite
SELECT
baker as toprbaker
FROM
results
WHERE
result = 'star baker'
UNION
SELECT
baker as lowerrbaker
FROM
favorites
order by toprbaker
;
| toprbaker |
|---|
| Briony |
| Dan |
| Jon |
| Kim-Joy |
| Manon |
| Rahul |
| Ruby |
– Don’t do this, very inefficient!!!
SELECT DISTINCT
b.baker
FROM
results r, favorites f, bakers b
WHERE
(b.baker = r.baker and r.result = 'star baker')
or (b.baker = f.baker)
order by baker;
%%sql
-- 1. Find all bakers who were eliminated before 5th episode or have been
-- in rank 6 or higher in at least 2 episodes before the 5th episode.
-- (eliminated before 5th episode) union (have been
-- in rank 6 or higher in at least 2 episodes before the 5th episode.)
select
baker
from
results
where
result = 'eliminated'
and episodeid < 5
union
select
t1.baker
from
technicals t1,
technicals t2
where
t1.baker = t2.baker
and t1.rank >= 6
and t2.rank >= 6
and t1.episodeid < 5
and t2.episodeid < 5
and t1.episodeid <> t2.episodeid ;
| baker |
|---|
| Karen |
| Antony |
| Manon |
| Ruby |
| Luke |
| Terry |
| Imelda |
| Kim-Joy |
%%sql
-- Find bakers who have only started being a favorite after the 6th
-- episode
-- (all favorites) - (all favorites before the 7th episode)
SELECT baker FROM favorites
EXCEPT
SELECT baker FROM favorites WHERE episodeid < 7;
| baker |
|---|
| Kim-Joy |

%%sql
-- Counting
SELECT
count(baker) as numepisodes
, count(distinct baker) as numbakers
FROM
results
WHERE
result = 'star baker';
| numepisodes | numbakers |
|---|---|
| 9 | 6 |
%%sql
-- What is the highest rank 'Dan' achieved in technicals?
SELECT
min(rank) as bestrank
, max(rank) as worstrank
FROM
technicals
WHERE
baker = 'Dan'
| bestrank | worstrank |
|---|---|
| 1 | 6 |
Aggregates: count, min, max, sum, avg, stdev
count(*) - number of tuples (includes null values too)
count(a) - number of tuples in which A has a value
count(distinct a) - number of different values for A
create table numbers (id int) ;
insert into numbers values(1);
insert into numbers values(1);
insert into numbers values(3);
insert into numbers values(null);
select min(id), max(id), avg(id) from numbers ;
-- average of the not-null values
select count(*), count(id), count(distinct id) from numbers ;
%%sql
-- Find for each baker, the number of times they were in rank 1
-- of technicals
select
t.baker
, count(*) as numtop1
, min(t.episodeid) as firstep
, max(t.episodeid) as lastep
from
technicals t
where
t.rank = 1
group by
t.baker ;
| baker | numtop1 | firstep | lastep |
|---|---|---|---|
| Dan | 1 | 4 | 4 |
| Briony | 2 | 6 | 8 |
| Terry | 1 | 3 | 3 |
| Jon | 1 | 2 | 2 |
| Rahul | 1 | 7 | 7 |
| Kim-Joy | 1 | 10 | 10 |
| Ruby | 3 | 1 | 9 |
%%sql
--- For bakers, find the number of times they have been
-- in rank 6 or higher before the 5th episode
select
baker
, count(*) as numtimes
from
technicals
where
rank > 6
and episodeid <5
group by
baker ;
| baker | numtimes |
|---|---|
| Briony | 1 |
| Rahul | 1 |
| Ruby | 2 |
| Karen | 2 |
| Luke | 2 |
| Terry | 2 |
| Imelda | 1 |
| Antony | 3 |
| Kim-Joy | 2 |
| Manon | 1 |
%%sql
--- Find bakers who have been
-- in rank 6 or higher in at least 2 episodes before the 5th episode
select
baker
, count(*) as numtimes
from
technicals
where
rank > 6
and episodeid <5
group by
baker
having
count(*) >= 2
;
| baker | numtimes |
|---|---|
| Ruby | 2 |
| Karen | 2 |
| Luke | 2 |
| Terry | 2 |
| Antony | 3 |
| Kim-Joy | 2 |
%%sql
--- Return the fullname and age of bakers who have been
-- in rank 6 or higher in at least 2 episodes before the 5th episode
select
b.fullname
, b.age
, count(*) as numtimes
from
technicals t
, bakers b
where
t.baker = b.baker
and t.rank > 6
and t.episodeid <5
group by
b.baker
having
count(*) >= 2
;
| fullname | age | numtimes |
|---|---|---|
| Ruby Bhogal | 29 | 2 |
| Karen Wright | 60 | 2 |
| Luke Thompson | 30 | 2 |
| Terry Hartill | 56 | 2 |
| Antony Amourdoux | 30 | 3 |
| Kim-Joy Hewlett | 27 | 2 |
Note: you can also group by b.baker, b.fullname, b.age The result would be the same However, if you grouped by t.baker, you cannot return b.fullname (because it does not know that t.baker implies b.fullname and b.age.
%%sql
-- Find bakers who were in top 3 rank in technicals at least 3 times and
-- were star baker at least 2 times and made an 'orange' thing in their
-- signature bake in an episode. Return their fullname and age.
SELECT
b.fullname
, b.age
FROM
technicals t
, bakers b
, signatures s
, results r
WHERE
t.baker = b.baker
and b.baker = s.baker
and r.baker = b.baker
and t.rank <= 3
and r.result = 'star baker'
and lower(s.make) like '%orange%'
GROUP BY
b.baker
HAVING
count(distinct t.episodeid) >= 3
and count(distinct r.episodeid) >= 2
ORDER BY
b.fullname
, b.age
;
| fullname | age |
|---|---|
| Kim-Joy Hewlett | 27 |
| Rahul Mandal | 30 |
%%sql
-- The same query can also be solved using INTERSECT
SELECT
b.fullname
, b.age
FROM
technicals t
, bakers b
WHERE
b.baker = t.baker
and t.rank <= 3
GROUP BY b.baker
HAVING count(*)>=3
INTERSECT
SELECT
b.fullname
, b.age
FROM
results r
, bakers b
WHERE
b.baker = r.baker
and r.result = 'star baker'
GROUP BY b.baker
HAVING count(*)>=2
INTERSECT
SELECT
b.fullname
, b.age
FROM
signatures s
, bakers b
WHERE
b.baker = s.baker
and lower(s.make) like '%orange%'
ORDER BY
fullname
, age
;
| fullname | age |
|---|---|
| Kim-Joy Hewlett | 27 |
| Rahul Mandal | 30 |

Note the images are generated by ChatGPT based on my prompt.