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
Connecting to '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 ;
Running query in 'baking'
6 rows affected.
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
;
Running query in 'baking'
7 rows affected.
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;
Running query in 'baking'
3 rows affected.
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';
Running query in 'baking'
6 rows affected.
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'
;
Running query in 'baking'
21 rows affected.
baker
Briony
Jon
Dan
Dan
Rahul
Jon
Rahul
Rahul
Ruby
Rahul
Truncated to displaylimit of 10.
%%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
;
Running query in 'baking'
1 rows affected.
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
;
Running query in 'baking'
7 rows affected.
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 ;
Running query in 'baking'
8 rows affected.
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;
Running query in 'baking'
1 rows affected.
baker
Kim-Joy

Query Execution Ordering

%%sql
    
-- Counting
SELECT 
   count(baker) as numepisodes
   , count(distinct baker) as numbakers
FROM
   results
WHERE
   result = 'star baker';
Running query in 'baking'
1 rows affected.
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'
Running query in 'baking'
1 rows affected.
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 ;
Running query in 'baking'
7 rows affected.
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  ;
Running query in 'baking'
10 rows affected.
baker numtimes
Briony 1
Rahul 1
Ruby 2
Karen 2
Luke 2
Terry 2
Imelda 1
Antony 3
Kim-Joy 2
Manon 1
Truncated to displaylimit of 10.
%%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
;
Running query in 'baking'
6 rows affected.
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
;
Running query in 'baking'
6 rows affected.
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
;
Running query in 'baking'
2 rows affected.
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
;
Running query in 'baking'
2 rows affected.
fullname age
Kim-Joy Hewlett 27
Rahul Mandal 30

Query Execution Ordering

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