Lecture 11 - SQL Basics#

Announcements#

Lecture Exercise 11 to be released later (may be tomorrow)
- Will post when up, Submitty issue to be resolved
Exam #2 is not yet finished
Hw #3 to be up soon, but not today

Today’s lecture#

Recap: select/where/nulls/like/etc.
Order by/limit
From clause, joins
Set/Bag expressions: UNION/INTERSECT/EXCEPT,UNION ALL/INTERSECT ALL/EXCEPT ALL
- set compatibility revisited
Aggregates
Group by
Having

load_ext sql

sql

%config SqlMagic.displaylimit = None
%sql --section baking

displaylimit: Value None will be treated as 0 (no limit)

Connecting to 'baking'

SELECT like “projection” in relational algebra
WHERE like “selection” in relational algebra
FROM

%%sql 
-- bakers who are younger than 30 and won star baker
select 
   b.baker, r.episodeid, b.age, b.occupation
from 
   results r, bakers b 
where    
   r.baker = b.baker
    and b.age < 30
    and r.result = 'star baker';

Running query in 'baking'

5 rows affected.

baker	episodeid	age	occupation
Manon	1	26	Software project manager
Kim-Joy	5	27	Mental health specialist
Kim-Joy	7	27	Mental health specialist
Ruby	8	29	Project manager
Ruby	9	29	Project manager

%%sql 
-- bakers who are younger than 30 and won star baker, and the make
-- for their showstopper
select 
   b.baker, r.episodeid, b.age, s.make as showstopper, si.make as signature
from 
   results r, bakers b, showstoppers s, signatures si
where    
   r.baker = b.baker
    and r.episodeid = s.episodeid
    and r.baker = s.baker
    and r.episodeid = si.episodeid
    and r.baker = si.baker
    and b.age < 30
    and r.result = 'star baker';

Running query in 'baking'

5 rows affected.

baker	episodeid	age	showstopper	signature
Manon	1	26	Matcha and White Chocolate Ganache Japanese Selfie	Hazelnut Cornish Shortbread
Kim-Joy	5	27	Christmas Spiced Ice Chandelier	Stem Ginger Cake with Poached Pears
Kim-Joy	7	27	Lavender & Lemon Fox Cake	Broccoli & Tomato Quiches and Mascarpone Squirrel Tartlets
Ruby	8	29	Sister Kagekone	Post-Gym Smørrebrød
Ruby	9	29	Parisian Un Bouquet de Fleurs Window Display	Pick Your Own Madeleines

%%sql 
-- find bakers who won star baker and were eliminated in the next 1-3 episodes

select
    r1.baker
from
    results r1, results r2
where
    r1.baker = r2.baker
    and r2.episodeid - r1.episodeid <= 3
    and r1.result = 'star baker'
    and r2.result = 'eliminated' ;

Running query in 'baking'

2 rows affected.

baker
Dan
Briony

%%sql 
-- find bakers who won star baker at least twice

select distinct
    r1.baker
from
    results r1, results r2
where
    r1.baker = r2.baker
    and r1.episodeid > r2.episodeid
    and r1.result = 'star baker'
    and r2.result = 'star baker' ;

Running query in 'baking'

3 rows affected.

baker
Kim-Joy
Rahul
Ruby

Set operations#

UNION
INTERSECT
EXCEPT

SELECT … FROM … WHERE
UNION
SELECT … FROM … WHERE
UNION
…
UNION
SELECT … FROM … WHERE

SELECT … FROM … WHERE
EXCEPT. – set difference
SELECT … FROM … WHERE

%%sql 
-- bakers who were never star baker
SELECT baker FROM bakers
EXCEPT
SELECT baker FROM results WHERE result = 'star baker';

Running query in 'baking'

6 rows affected.

baker
Imelda
Jon
Karen
Luke
Antony
Terry

%%sql 
SELECT baker FROM results WHERE result = 'star baker'
EXCEPT 
SELECT baker FROM results WHERE result = 'eliminated';

Running query in 'baking'

3 rows affected.

baker
Rahul
Ruby
Kim-Joy

%%sql
-- relations must still be set compatible, same number of attributes
-- and same data type (or can be cast to be same!)

select baker, age 
from bakers 
where age < 30

union

select r.baker as starbaker, b.age as ageofbaker
from results r, bakers b 
where r.baker = b.baker and r.result = 'star baker' and r.episodeid < 5 ;

Running query in 'baking'

5 rows affected.

baker	age
Dan	36
Rahul	30
Ruby	29
Manon	26
Kim-Joy	27

%%sql
select * from bakers where age < 50 
order by age asc, baker desc
limit 3;

Running query in 'baking'

3 rows affected.

baker	fullname	age	occupation	hometown
Manon	Manon Lagrève	26	Software project manager	London
Kim-Joy	Kim-Joy Hewlett	27	Mental health specialist	Leeds
Ruby	Ruby Bhogal	29	Project manager	London

%%sql
-- relations must still be set compatible, same number of attributes
-- and same data type (or can be cast to be same!)

select baker as contestant, age  
from bakers 
where age < 30

union

select r.baker as starbaker, b.age as ageofbaker
from results r, bakers b 
where r.baker = b.baker and r.result = 'star baker' and r.episodeid < 5 

order by contestant;

Running query in 'baking'

5 rows affected.

contestant	age
Dan	36
Kim-Joy	27
Manon	26
Rahul	30
Ruby	29

Bag operators#

UNION ALL
INTERSECT ALL
EXCEPT ALL

SELECT … FROM … WHERE
UNION ALL SELECT … FROM … WHERE

R union all S = {return m+n copies of t if t happens m times in R and n times in S}

R intersect all S = {return min(m,n) copies of t if t happens m times in R and n times in S}

R except all S = {return max(0,m-n) copies of t if t happens m times in R and n times in S}

%%sql
select r.baker as starbaker, b.age as ageofbaker
from results r, bakers b 
where r.baker = b.baker and r.result = 'star baker' and r.episodeid < 5 

Running query in 'baking'

4 rows affected.

starbaker	ageofbaker
Manon	26
Rahul	30
Rahul	30
Dan	36

%%sql 
select baker as contestant, age  
from bakers 
where age < 30;

Running query in 'baking'

3 rows affected.

contestant	age
Kim-Joy	27
Manon	26
Ruby	29

%%sql 
select baker as contestant, age  
from bakers 
where age < 30

union all

select r.baker as starbaker, b.age as ageofbaker
from results r, bakers b 
where r.baker = b.baker and r.result = 'star baker' and r.episodeid < 5 ;

Running query in 'baking'

7 rows affected.

contestant	age
Kim-Joy	27
Manon	26
Ruby	29
Manon	26
Rahul	30
Rahul	30
Dan	36

%%sql 
select baker
from showstoppers
where lower(make) like '%chocolate%'
intersect all
select r.baker 
from results r
where r.result = 'star baker' and r.episodeid < 5 ;

Running query in 'baking'

4 rows affected.

baker
Rahul
Rahul
Dan
Manon

%%sql 
select baker
from showstoppers
where lower(make) like '%chocolate%'
except all
select r.baker 
from results r
where r.result = 'star baker' and r.episodeid < 5 ;

Running query in 'baking'

9 rows affected.

baker
Briony
Ruby
Ruby
Ruby
Luke
Karen
Antony
Kim-Joy
Manon

Aggregates#

Apply to the set of tuples, not a single tuple!
count/max/min/avg/…

SELECT aggregates from the relation produced below

%%sql

select * from bakers where age <= 30;

Running query in 'baking'

6 rows affected.

baker	fullname	age	occupation	hometown
Antony	Antony Amourdoux	30	Banker	London
Kim-Joy	Kim-Joy Hewlett	27	Mental health specialist	Leeds
Luke	Luke Thompson	30	Civil servant/house and techno DJ	Sheffield
Manon	Manon Lagrève	26	Software project manager	London
Rahul	Rahul Mandal	30	Research scientist	Rotherham
Ruby	Ruby Bhogal	29	Project manager	London

%%sql

select count(*) as numbakers , min(age) as minage, max(age) as maxage
from bakers where age <= 30;

Running query in 'baking'

1 rows affected.

numbakers	minage	maxage
6	26	30

%%sql
select avg(age)::numeric(4,1)
from bakers;

Running query in 'baking'

1 rows affected.

avg
36.4

%%sql
select avg(age)::numeric(4,1)
from bakers
where age>30;

Running query in 'baking'

1 rows affected.

avg
44.2

%%sql
select min(firstaired) as firstepisodedate
    ,max(firstaired) as finalepisodedate
    , avg(viewers7day)::numeric(4,1) as avgviewers
    , max(viewers7day) as highestviewers
from episodes;

Running query in 'baking'

1 rows affected.

firstepisodedate	finalepisodedate	avgviewers	highestviewers
2018-08-28	2018-10-30	9.4	10.34

%%sql
select avg(age)::numeric(4,1)
from bakers
where age>30;

Running query in 'baking'

1 rows affected.

avg
44.2

%%sql
select
   r.baker
from
   results r
where
   r.result = 'star baker'
order by 
   r.baker asc ;

Running query in 'baking'

9 rows affected.

baker
Briony
Dan
Kim-Joy
Kim-Joy
Manon
Rahul
Rahul
Ruby
Ruby

%%sql

-- For each baker, return the number of wins and the episode number of their
-- first/last win

select
   r.baker
    , count(*) as numwins
    , min(episodeid) as firstep
    , max(episodeid) as lastep
from
   results r
where
   r.result = 'star baker'
group by 
   r.baker
order by 
   r.baker asc ;

Running query in 'baking'

6 rows affected.

baker	numwins	firstep	lastep
Briony	1	6	6
Dan	1	4	4
Kim-Joy	2	5	7
Manon	1	1	1
Rahul	2	2	3
Ruby	2	8	9

%%sql

select * from favorites ;

Running query in 'baking'

12 rows affected.

episodeid	baker
1	Briony
2	Jon
2	Dan
3	Dan
4	Rahul
4	Jon
5	Rahul
6	Rahul
6	Ruby
7	Rahul
8	Briony
9	Kim-Joy

%%sql
select
   baker
    , count(*) as numfavorite
from
   favorites
group by
   baker;

Running query in 'baking'

6 rows affected.

baker	numfavorite
Rahul	4
Ruby	1
Dan	2
Briony	2
Jon	2
Kim-Joy	1

%%sql
select
   f.episodeid
    , count(*) as numbakers
    , min(b.age)
    , max(b.age)
from
   favorites f
    , bakers b
where
    f.baker = b.baker
group by
   f.episodeid;

Running query in 'baking'

9 rows affected.

episodeid	numbakers	min	max
9	1	27	27
3	1	36	36
5	1	30	30
4	2	30	47
6	2	29	30
2	2	36	47
7	1	30	30
1	1	33	33
8	1	33	33

%%sql
select
count(*) as numbakers
    , min(b.age)
    , max(b.age)
from
   favorites f
    , bakers b
where
    f.baker = b.baker
group by
   f.episodeid;

Running query in 'baking'

9 rows affected.

numbakers	min	max
1	27	27
1	36	36
1	30	30
2	30	47
2	29	30
2	36	47
1	30	30
1	33	33
1	33	33

Lecture 11 - SQL Basics

Contents

Lecture 11 - SQL Basics#

Announcements#

Today’s lecture#

Set operations#

Bag operators#

Aggregates#