Lecture 13 - Even more SQL#

Announcements

  • No new lecture exercise today, Lex12 due on monday at midnight.

  • Expect a new homework today or tomorrow with more complex SQL queries.

SQL pointers#

  • There is more than one way to write a query. The performance of the query is determined by a combination of different factors.

  • Databases differ in how the data are stored and which indices are available.

  • Each query is sent to a very powerful query optimizer that can find the most efficient way to execute a query. The way you write the query may make it difficult for the optimizer to find some good execution methods.

  • DB servers differ in their implementation of query operations and how they keep track of the data statistics.

  • Database queries don’t follow the basic programming logic of modularity. It is good to push some complexity to queries. The main point is figuring out when to break up a query to modules.

  • Wisdom from an older db programmer: Learn to write a query in multiple ways. Check the expected and real costs.

explain SQLquery;

will show you the expected cost of a query in Postgresql.

load_ext sql
%sql --section baking
Connecting to 'baking'

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)

  1. Find all bakers who have used an orange in the showstopper or signature challenge. Return the name of the baker and the number of episodes in which they competed in.

%%sql

-- 1. Find the episodes in which no one was chosen to be a favorite.
-- Return the episodeid and air date of the episode.

-- (all episodes) except (episodes with favorites)

select id, firstaired from episodes
except
select e.id, e.firstaired
from favorites f, episodes e
where f.episodeid = e.id;
Running query in 'baking'
1 rows affected.
id firstaired
10 2018-10-30
%%sql

-- 2. For each baker, find the total number of shows in which they were a
-- favorite, the number of shows in which they were 'star baker' and the
-- episode id of the show in which they were eliminated.

select
    f.baker
    , r2.episodeid as wheneliminated
    , count(distinct f.episodeid) as numfav
    , count(distinct r.episodeid) as numstar
from
    favorites f
    , results r
    , results r2
where
    f.baker = r.baker
    and r.result = 'star baker'
    and r2.baker = r.baker
    and r2.result = 'eliminated'
group by
    f.baker
    , r2.episodeid ;
Running query in 'baking'
2 rows affected.
baker wheneliminated numfav numstar
Briony 9 2 1
Dan 6 2 1
%%sql 
    
-- Find all bakers who have used an orange in the showstopper or
-- signature challenge. Return the name of the baker and the number of
-- episodes in which they competed in.

select
   s.baker
   , count(distinct s2.episodeid) as numepisodes
from
   showstoppers s, showstoppers s2
where
   lower(s.make) like '%orange%'
   and s.baker = s2.baker
group by
   s.baker 
union
select
   s.baker
   , count(distinct s2.episodeid) as numepisodes
from
   signatures s, showstoppers s2
where
   lower(s.make) like '%orange%'
   and s.baker = s2.baker
group by
   s.baker ;
Running query in 'baking'
6 rows affected.
baker numepisodes
Antony 3
Briony 9
Dan 6
Kim-Joy 10
Rahul 10
Ruby 10

Note you can write above query (3) using joins only as seen below, but this join is not very selective (it is mostly a Cartesian product) and as a result this query is grossly inefficient (use explain to see for yourself).

select
   t.baker
   , count(distinct t.episodeid) as numepisodes
from
   signatures s, showstoppers s2, technicals t
where
   (lower(s.make) like '%orange%' and s.baker = t.baker)
   or (lower(s2.make) like '%orange%' and s2.baker = t.baker)
group by
   t.baker ;

OUTER JOIN vs. INNER JOIN#

select baker from bakers ;

select distinct baker from favorites;

select distinct baker from results where result = 'star baker' ;

This query only returns bakers who were both star baker and were favorite.

select distinct r.baker from favorites f, results r
where r.baker = f.baker and r.result = 'star baker' ;

This query only returns bakers who were favorite.

select distinct b.baker, f.episodeid from bakers b, favorites f
where f.baker = b.baker ;

Outer Join

%%sql
-- For each baker, find all episodes in which they were a favorite

select distinct
   b.baker
   , f.episodeid
from
   bakers b left join
   favorites f  on f.baker = b.baker
order by b.baker
;
Running query in 'baking'
18 rows affected.
baker episodeid
Antony None
Briony 1
Briony 8
Dan 2
Dan 3
Imelda None
Jon 2
Jon 4
Karen None
Kim-Joy 9
Truncated to displaylimit of 10.
%%sql
    
-- For each baker, find all episodes in which they were a favorite


select 
   b.baker
   , count(f.episodeid) as numfav
from
   bakers b left join
   favorites f  on f.baker = b.baker
group by b.baker
;
Running query in 'baking'
12 rows affected.
baker numfav
Rahul 4
Imelda 0
Jon 2
Karen 0
Ruby 1
Luke 0
Dan 2
Antony 0
Manon 0
Briony 2
Truncated to displaylimit of 10.
%%sql
    
-- For each baker, find the number of episodes in which
-- they were a star baker.

-- (bakers)  left join/
--          keep bakers even if they don't participate in the join
-- (bakers who were star baker)	  


select
   b.baker
   , count(r.episodeid) as numep
from
   bakers b left join
   results r on b.baker = r.baker and r.result = 'star baker'
group by
   b.baker ;
Running query in 'baking'
12 rows affected.
baker numep
Rahul 2
Imelda 0
Jon 0
Karen 0
Ruby 2
Luke 0
Dan 1
Antony 0
Manon 1
Briony 1
Truncated to displaylimit of 10.
%%sql
    
-- 2. For each baker, find the total number of shows in which they were a
-- favorite, the number of shows in which they were 'star baker' and the
-- episode id of the show in which they were eliminated.


select
    b.baker
    , r2.episodeid as wheneliminated
    , count(distinct f.episodeid) as numfav
    , count(distinct r.episodeid) as numstar
from
    bakers b
    left join favorites f on f.baker = b.baker
    left join results r on b.baker = r.baker and r.result = 'star baker'
    left join results r2 on b.baker = r2.baker and r2.result = 'eliminated'
group by
    b.baker
    , r2.episodeid
order by
    b.baker asc;
Running query in 'baking'
12 rows affected.
baker wheneliminated numfav numstar
Antony 3 0 0
Briony 9 2 1
Dan 6 2 1
Imelda 1 0 0
Jon 7 2 0
Karen 5 0 0
Kim-Joy None 1 2
Luke 2 0 0
Manon 8 0 1
Rahul None 4 2
Truncated to displaylimit of 10.
%%sql

-- 1. Find the episodes in which no one was chosen to be a favorite.
-- Return the episodeid and air date of the episode.

-- You can now do set subtraction using left join

select
   e.id
   , e.firstaired
from
   episodes e
   left join favorites f on e.id = f.episodeid
where
   f.episodeid is null;
Running query in 'baking'
1 rows affected.
id firstaired
10 2018-10-30
%%sql 

    
-- For inner join, using join in from or using in WHERE
-- are identical logically and for execution costs    

select
    f.baker
    , r2.episodeid as wheneliminated
    , count(distinct f.episodeid) as numfav
    , count(distinct r.episodeid) as numstar
from
    favorites f
       join results r on f.baker = r.baker
       join results r2 on r2.baker = r.baker
where
    r.result = 'star baker'
    and r2.result = 'eliminated'
group by
    f.baker
    , r2.episodeid ;
Running query in 'baking'
2 rows affected.
baker wheneliminated numfav numstar
Briony 9 2 1
Dan 6 2 1
%%sql
    
select
   b.baker
   , count(distinct r.episodeid) as numep
   , min(t.rank) as besttechnical
from
   bakers b left join
   results r on b.baker = r.baker and r.result = 'star baker'
   , technicals t
where
   t.baker = b.baker
group by
   b.baker ;
Running query in 'baking'
12 rows affected.
baker numep besttechnical
Antony 0 8
Briony 1 1
Dan 1 1
Imelda 0 11
Jon 0 1
Karen 0 5
Kim-Joy 2 1
Luke 0 7
Manon 1 2
Rahul 2 1
Truncated to displaylimit of 10.

Advanced SQL#

Queries may return:

  • Multiple tuples - treat it like a relation/table in the database

  • Single number - treat the query like a number (Scalar queries)

%%sql 
    
-- Find all bakers who won star baker more times than 'Briony'

select
   baker
from
   results
where
   result = 'star baker'
group by
   baker
having
   count(*) > (select count(*) from results
               where result = 'star baker'
	             and baker = 'Briony');
Running query in 'baking'
3 rows affected.
baker
Rahul
Ruby
Kim-Joy
%%sql 

-- Find all episodes after the episode that 'Briony'
-- was eliminated in.

select
   e.id
from
   episodes e
where
   e.id > (select episodeid from results
           where baker = 'Briony' and result = 'eliminated')
Running query in 'baking'
1 rows affected.
id
10