Lecture 11 - SQL is here

Lecture 11 - SQL is here#

Announcements

  • The database server is up, linked from course home page: https://www.cs.rpi.edu/~sibel/csci4380/fall2026/

    • Passwords are sent by email

  • No class on monday next week, but class on Thursday and Friday

  • Expect a new lecture exercise on friday, to be due on monday at midnight

  • Expect a mini homework on friday, to be due on friday at midnight (based on solely what we end up covering today)

SQL#

  • Industry standard

  • SQL is bag oriented - not set

    • Queries may return multiple copies of the same tuple - unless we explicitly it to remove them

  • SQL

    • DML: data manipulation language (SELECT FROM WHERE)

    • DDL: data definition language

%sql --section baking

UsageError: Line magic function `%sql` not found.

%%sql 
select 
    age
    , age as age2
    , fullname || ' - ' ||  occupation || ' (' || age::varchar || ')' as bakerinfo 
from bakers ;
Running query in 'baking'
12 rows affected.
age age2 bakerinfo
30 30 Antony Amourdoux - Banker (30)
33 33 Briony Williams - Full-time parent (33)
36 36 Dan Beasley-Harling - Full-time parent (36)
33 33 Imelda McCarron - Countryside recreation officer (33)
47 47 Jon Jenkins - Blood courier (47)
60 60 Karen Wright - In-store sampling assistant (60)
27 27 Kim-Joy Hewlett - Mental health specialist (27)
30 30 Luke Thompson - Civil servant/house and techno DJ (30)
26 26 Manon Lagrève - Software project manager (26)
30 30 Rahul Mandal - Research scientist (30)
Truncated to displaylimit of 10.
%%sql 
    
select 
    title
    , firstaired 
from 
    episodes 
where 
    firstaired > '10/1/2018'::date 
    and viewers7day > 9 ;
Running query in 'baking'
5 rows affected.
title firstaired
Pastry 2018-10-02
Vegan 2018-10-09
Danish 2018-10-16
Pâtisserie (Semi-final) 2018-10-23
Final 2018-10-30

Date (10/08/2026)

Time (‘14:43’)

Timestamp ( Date + Time)

Interval ( Interval of time unit)

Date-Date -> Interval of days

Date + Time = Timestamp

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)

%%sql
select baker, make from showstoppers where lower(make) like '%chocolate%';
Running query in 'baking'
13 rows affected.
baker make
Manon Matcha and White Chocolate Ganache Japanese Selfie
Briony Chocolate Fudge and Salted Caramel Creation
Dan Dark Chocolate and Raspberry Birthday Cake
Karen Strawberry Fayre Chocolate Cake
Luke Raspberry and White Chocolate Collar Cake
Rahul Chocolate Orange Layer Cake
Ruby Chocolate Orange "Jackson Pollock" Collar Cake
Antony Chocolate and Orange Adventure Korovai
Kim-Joy Melting Chocolate Galaxy
Manon White Chocolate Renaissance Surprise
Truncated to displaylimit of 10.
%%sql

select distinct
  b.fullname
    , b.age
from 
  bakers b
    , technicals t
where
  b.baker = t.baker
  and t.rank <= 3 ;
Running query in 'baking'
8 rows affected.
fullname age
Jon Jenkins 47
Kim-Joy Hewlett 27
Rahul Mandal 30
Manon Lagrève 26
Terry Hartill 56
Briony Williams 33
Ruby Bhogal 29
Dan Beasley-Harling 36 
%%sql 

-- Find all bakers who made something with chocolate in the showstopper an episode
-- they won the star baker in and return their name and age.

select distinct
    b.fullname, b.age 
from
   showstoppers s
   , results r
   , bakers b
where 
   s.episodeid = r.episodeid
   and s.baker = r.baker   --same baker, same episode
   and r.baker = b.baker   --same baker
   and r.result = 'star baker'
   and lower(s.make) like '%chocolate%' ;
Running query in 'baking'
2 rows affected.
fullname age
Manon Lagrève 26
Rahul Mandal 30 
%%sql 

-- Find a baker who won star baker in two episodes that 
-- 1 to 3 episodes apart, return their baker name and the episodes
-- they won in

select
   b.baker
    , b.fullname
    , r2.episodeid as firstep
    , r1.episodeid as secondep
from
   results r1
   , results r2
   , bakers b 
where 
   r1.baker = r2.baker
   and r1.episodeid - r2.episodeid <= 3
   and r1.episodeid - r2.episodeid > 0
   and b.baker = r1.baker 
   and r1.result = 'star baker'
   and r2.result = 'star baker' ;
Running query in 'baking'
3 rows affected.
baker fullname firstep secondep
Rahul Rahul Mandal 2 3
Kim-Joy Kim-Joy Hewlett 5 7
Ruby Ruby Bhogal 8 9

  • NULL value means that there is no value for an attribute

    • There is no value

    • There is a value but I don’t know what it is

    • I don’t know if there is a value or not.

result = ‘star baker’

  • returns true if the value stored is ‘star baker’

  • returns false if the value stored is a string different than ‘star baker’

  • return unknown if it is NULL

Not (unknown) = unknown

(unknown) AND (true) = unknown (unknown) AND (false) = false

(unknown) OR (true) = true (unknown) OR (false) = unknown

result IS NULL

%%sql

-- Find all bakers who won star baker and ranked in top 3 of the same 
-- episode aired in october, and they were favorite two episodes ago.
-- Return their name

select distinct
    b.fullname
    , b.age
from 
    results r
    , technicals t
    , episodes e
    , bakers b
    , favorites f
where 
    r.baker = t.baker
    and b.baker = r.baker
    and f.baker = b.baker
    and e.id = r.episodeid
    and e.id = t.episodeid
    and f.episodeid = e.id - 2
    and t.rank <= 3
    and r.result = 'star baker'
    and extract(month from e.firstaired) = 10;
Running query in 'baking'
1 rows affected.
fullname age
Ruby Bhogal 29 
%%sql
--remember query execution order, select comes
-- after where!    
    
select extract(month from firstaired) as airmonth
from episodes 
where extract(month from firstaired) < 10 
    order by airmonth;
Running query in 'baking'
5 rows affected.
airmonth
8
9
9
9
9
Contents