Lecture 14- Advanced SQL

Lecture 14- Advanced SQL#

Announcements

  • Hw#4 is out due next monday

  • Lecture exercise 13 out today, due on thursday at 2pm.

Advanced Constructs#

  • Scalar queries: queries that return a single value

  • Queries as a set of tuples

value in/not in (set)
value >=ALL (set)
value <=ALL (set)
value >ALL (set)
value <ALL (set)
value =ALL (set)
value <>ALL (set)  
  • Anonymous and non-anonymous queries as new relations in FROM or using WITH

    • A query is anonymous if it only exists for a single query (regardless of where it is located)

  • Correlated and uncorrelated subqueries

  • NOT EXISTS (set) : if the set is empty, then not exists is true!

  • For all queries

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)

load_ext sql
%sql --section baking
Connecting to 'baking'
%%sql 
    
-- Find all bakers who were in top 3 of technicals more times than 'Manon';

-- Within the subquery, local technicals overwrites the global one!

select
   baker
   , count(*) as numtimes
from
   technicals  --- <- global technicals
where
   rank <= 3
group by
   baker
having
   count(*) > (select count(*) from technicals   --<- local technicals
               where rank <=3 and baker = 'Manon') ;
Running query in 'baking'
4 rows affected.
baker numtimes
Jon 4
Briony 4
Rahul 5
Ruby 7
%%sql

-- You can use a scalar query anywhere in the query that uses a constant

select
   baker
   , count(*) as numtimes
   , (select count(*) from technicals t1
               where t1.rank <=3 and t1.baker = 'Manon') as nummanon
from
   technicals 
where
   rank <= 3
group by
   baker
having
   count(*) > (select count(*) from technicals t1
               where t1.rank <=3 and t1.baker = 'Manon') ;
Running query in 'baking'
4 rows affected.
baker numtimes nummanon
Jon 4 3
Briony 4 3
Rahul 5 3
Ruby 7 3
%%sql 

-- In this case, the subquery is not needed but for a larger relation
-- it is likely that the subquery solution is much more efficient for this query.


select
   t.baker
   , count(distinct t.episodeid) as numtimes
from
   technicals t
   , technicals t2
where
   t.rank <= 3
   and t2.rank <= 3
   and t2.baker = 'Manon'
   and t.baker <> 'Manon'
group by
   t.baker
having
   count(distinct t.episodeid) > count(distinct t2.episodeid);
Running query in 'baking'
4 rows affected.
baker numtimes
Briony 4
Jon 4
Rahul 5
Ruby 7
%%sql

-- value in (set of values)
-- value in (result of a query)

select
   baker
   , count(*) as numtimes
from
   technicals 
where
   rank IN (1,2,3)
group by 
   baker;
Running query in 'baking'
8 rows affected.
baker numtimes
Manon 3
Jon 4
Briony 4
Rahul 5
Ruby 7
Dan 3
Terry 1
Kim-Joy 3
%%sql
--- Find all episodes in which no one was eliminated.

-- Solution 1
-- select *
-- from episodes
-- where id not in (episodes in which someone was eliminated);
    
select id, title
from episodes
where id not in (select distinct episodeid from results
                 where result = 'eliminated') ;
Running query in 'baking'
2 rows affected.
id title
4 Desserts
10 Final
%%sql 

-- Solution 2

select id, title
from episodes
except
select e.id, e.title
from
   episodes e, results r
where
   e.id = r.episodeid and result = 'eliminated' ;
Running query in 'baking'
2 rows affected.
id title
4 Desserts
10 Final
%%sql

-- Solution 3
select e.id, e.title
from episodes e left join results r
     on e.id = r.episodeid and r.result = 'eliminated'
where
     r.episodeid is null ;
Running query in 'baking'
2 rows affected.
id title
4 Desserts
10 Final
%%sql


-- Find bakers who were star baker at least twice, but never in back to
--  back episodes.


select
   r.baker
from
   results r
where
   r.result = 'star baker'
   and r.baker not in (select r2.baker
                       from results r1, results r2
                       where r1.baker = r2.baker
                       and r1.episodeid +1 = r2.episodeid
                       and r1.result = 'star baker'
                       and r2.result = 'star baker')
group by
   r.baker
having
   count(*) >= 2;
Running query in 'baking'
1 rows affected.
baker
Kim-Joy
%%sql

-- Alternate solution with except

select
   r.baker
from
   results r
where
   r.result = 'star baker'
group by
   r.baker
having
   count(*) >= 2
except
(select r2.baker
from results r1, results r2
where r1.baker = r2.baker
and r1.episodeid +1 = r2.episodeid
and r1.result = 'star baker'
and r2.result = 'star baker'
)

;
Running query in 'baking'
1 rows affected.
baker
Kim-Joy
%%sql 

-- Find the episodes with more than the lowest number of favorites, return their
--  title and airdate.

-- First find counts



select
   e.id
   , count(f.episodeid) as numtimes
from
   episodes e left join favorites f
   on e.id = f.episodeid
group by
   e.id ;
Running query in 'baking'
10 rows affected.
id numtimes
4 2
10 0
6 2
2 2
9 1
7 1
3 1
1 1
5 1
8 1
Truncated to displaylimit of 10.
%%sql

-- Find the lowest number, excluding the last episode 
    
select distinct
   count(f.episodeid) as numtimes
from
   episodes e left join favorites f
   on e.id = f.episodeid
where
   e.id < 10
group by
   e.id
order by numtimes asc
limit 1
;
Running query in 'baking'
1 rows affected.
numtimes
1
%%sql

-- Find the episodes with more than the lowest number of favorites, return their
--  title and airdate.


select distinct
   e.id, e.title
   , count(f.episodeid) as numfavs
from
   episodes e left join favorites f
   on e.id = f.episodeid
group by
    e.id
having
   count(f.episodeid) > (select distinct
                          count(f.episodeid) as numtimes
                          from
                         episodes e left join favorites f
                         on e.id = f.episodeid
                        group by e.id
                        order by numtimes asc
                        limit 1);
Running query in 'baking'
9 rows affected.
id title numfavs
1 Biscuits 1
4 Desserts 2
3 Bread 1
2 Cakes 2
8 Danish 1
5 Spice 1
6 Pastry 2
7 Vegan 1
9 Pâtisserie (Semi-final) 1
%%sql


-- Find the episodes with more than the lowest two number of favorites,
--  return their title and airdate.


select distinct
   e.id, e.title
   , count(f.episodeid) as numfavs
from
   episodes e left join favorites f
   on e.id = f.episodeid
group by
    e.id
having
   count(f.episodeid) >ALL (select distinct
                          count(f.episodeid) as numtimes
                          from
                         episodes e left join favorites f
                         on e.id = f.episodeid
                        group by e.id
                        order by numtimes asc
                        limit 2);
Running query in 'baking'
3 rows affected.
id title numfavs
4 Desserts 2
6 Pastry 2
2 Cakes 2

You can write queries more succinctly using WITH ()

WITH r1 as ( query1 ) 
     , r2 as (query 2 )

select *
from r1, r2
where r1.a = r2.b ;
%%sql

--- Rewriting an earlier query
-- be very careful how to refer to a WITH query like any other relation

with
    manonq as (select count(*) as num
     from technicals t1
     where t1.rank <=3 and t1.baker = 'Manon') 
select
   t.baker
   , count(*) as numtimes
   , mq.num
from
   technicals t, manonq mq
where
   t.rank <= 3
group by
   t.baker, mq.num
having
   count(*) > (select num from manonq) ;
Running query in 'baking'
4 rows affected.
baker numtimes num
Rahul 5 3
Ruby 7 3
Briony 4 3
Jon 4 3
%%sql

with counts as (select distinct
                      count(f.episodeid) as numtimes
                from
                     episodes e left join favorites f
                      on e.id = f.episodeid
                group by e.id
                order by numtimes asc
                limit 2)
select distinct
   e.id, e.title
   , count(f.episodeid) as numfavs
from
   episodes e left join favorites f
   on e.id = f.episodeid
group by
    e.id
having
   count(f.episodeid) >ALL (select numtimes from counts) ;
Running query in 'baking'
3 rows affected.
id title numfavs
4 Desserts 2
6 Pastry 2
2 Cakes 2
%%sql

-- not exists (set) -- return true if the set is empty.

-- Find bakers who were never eliminated

-- select * from bakers where not exists (a tuple for this baker
--                                        in results where they are eliminated)

select
     *
from
     bakers b
where
     not exists (select * from results r
                 where r.baker = b.baker
		       and r.result = 'eliminated') ;
Running query in 'baking'
3 rows affected.
baker fullname age occupation hometown
Kim-Joy Kim-Joy Hewlett 27 Mental health specialist Leeds
Rahul Rahul Mandal 30 Research scientist Rotherham
Ruby Ruby Bhogal 29 Project manager London
%%sql

-- Find bakers who never improved in the technicals by more than a single
-- position in a subsequent episode

-- select
--     *
-- from
--     bakers b
-- where
--     not exists (two technical tuples showing improvement for this baker )

select
    *
from
    bakers b
where
    not exists (select *
                from technicals t1, technicals t2
		where t1.baker = t2.baker and t1.baker = b.baker
		      and t2.rank - t1.rank > 1
		      and t1.episodeid > t2.episodeid) ;
Running query in 'baking'
2 rows affected.
baker fullname age occupation hometown
Imelda Imelda McCarron 33 Countryside recreation officer County Tyrone
Luke Luke Thompson 30 Civil servant/house and techno DJ Sheffield
%%sql

-- Can also use not in with a non-correlated query
select
    *
from
    bakers b
where
    b.baker not in (select t1.baker
                from technicals t1, technicals t2
		where t1.baker = t2.baker
		      and t2.rank - t1.rank > 1
		      and t1.episodeid > t2.episodeid) ;
Running query in 'baking'
2 rows affected.
baker fullname age occupation hometown
Imelda Imelda McCarron 33 Countryside recreation officer County Tyrone
Luke Luke Thompson 30 Civil servant/house and techno DJ Sheffield
%%sql

-- FOR ALL QUERIES OFTEN requires two set subtractions

-- Find bakers who competed in all the episodes of the season

-- select * from bakers b
--      where not exists ( an episode in the seasons such that
--          there does not exist a tuple in technicals for this
-- 	        baker and this episode)


select * from bakers b
where not exists
     (select * from episodes e
      where not exists (select * from technicals t
                        where t.baker = b.baker
			      and t.episodeid = e.id)) ;
Running query in 'baking'
3 rows affected.
baker fullname age occupation hometown
Kim-Joy Kim-Joy Hewlett 27 Mental health specialist Leeds
Rahul Rahul Mandal 30 Research scientist Rotherham
Ruby Ruby Bhogal 29 Project manager London
%%sql

-- Do not forget that much cheaper ways to write the same query may exist

select
    b.baker
    , b.fullname
from
    bakers b
    , technicals t
where
    b.baker = t.baker
group by
    b.baker
having
    count(*) = (select count(*) from episodes) ;
Running query in 'baking'
3 rows affected.
baker fullname
Rahul Rahul Mandal
Ruby Ruby Bhogal
Kim-Joy Kim-Joy Hewlett
%%sql

-- Find the bakers with the highest total number of wins and technical
--  wins combined.


WITH counts as
     (select b.baker
          , count(distinct r.episodeid)+count(distinct t.episodeid) as numwins
     from bakers b left join results r
          on b.baker = r.baker and r.result = 'star baker'
	  left join technicals t 
              on b.baker = t.baker and t.rank = 1
      group by b.baker	      
)
select baker, numwins
from counts
where  numwins = (select max(numwins) from counts) ;
Running query in 'baking'
1 rows affected.
baker numwins
Ruby 5