
Announcements:

 There could be chainsaw noise!
 Unavailable this weekend and monday!
 New DB server (whitelist: rpidbsprof@gmail.com)
 New Submitty exercise format
 Hw#4 is coming.... (may be by the end of day, though not guaranteed)
 Exam #1 is being graded
> some checks will be done after grades are released.

SQL


SELECT
attributes to return
FROM
relations
WHERE
join conditions and other conditions

bakers(baker,fullname,age,occupation,hometown)
episodes(id,title,firstaired,viewers7day,signature,technical,showstopper)
results(episodeid,baker,result)
technicals(episodeid,baker,rank)
signatures(episodeid,baker,make)
showstoppers(episodeid,baker,make)
Find pair of bakers A,B who are from the same town and have placed in
ranks one place apart (eg 2nd 3rd, or 4tg 5th) in the technical
challenge of the same episode.
SELECT DISTINCT
b1.fullname
, b2.fullname
FROM
bakers b1
, bakers b2
, technicals t1
, technicals t2
WHERE
b1.baker > b2.baker
and t1.baker = b1.baker
and t2.baker = b2.baker
and t1.episodeid = t2.episodeid
and b1.hometown = b2.hometown
and abs(t1.rankt2.rank) = 1
ORDER BY
b1.fullname asc
, b2.fullname
;

SELECT
baker as bakershortname
, fullname as bakerfullname
FROM
bakers
WHERE
age >= 30
EXCEPT
SELECT
b.baker
, b.fullname
FROM
bakers b
, results r
WHERE
b.baker = r.baker
and r.result = 'eliminated'
ORDER BY
bakershortname
;
Find bakers who
(are either at least 30 years old or have won technical 2 times)
and were never eliminated.
((30 years old) union (bakers who won 2 times))  (bakers eliminated)
(SELECT
baker as baker1
, fullname as f1
FROM
bakers
WHERE
age >= 30
UNION
SELECT
b.baker
, b.fullname
FROM
bakers b
, technicals t1
, technicals t2
WHERE
b.baker = t1.baker
and b.baker = t2.baker
and t1.episodeid <> t2.episodeid
and t1.rank = 1
and t2.rank = 1
)
EXCEPT
SELECT
b.baker
, b.fullname
FROM
bakers b
, results r
WHERE
b.baker = r.baker
and r.result = 'eliminated'
ORDER BY
baker1
, f1
;

SELECT
baker as bakershortname
, fullname as bakerfullname
FROM
bakers
WHERE
age >= 30
EXCEPT
SELECT
b.baker
, b.fullname
FROM
bakers b
, results r
WHERE
b.baker = r.baker
and r.result = 'eliminated';
Find bakers who
(are either at least 30 years old or have won technical 2 times)
and were never eliminated.

set operations
SELECT FROM WHERE
UNION/INTERSECT/EXCEPT
SELECT FROM WHERE
bag operations!
SELECT FROM WHERE
UNION ALL/INTERSECT ALL/EXCEPT ALL
SELECT FROM WHERE
 or are at least 30 years old.

Aggregates:
min,max,sum,avg,stdev
SELECT
age
, hometown
FROM
bakers;
SELECT
min(age)
, max(age)
, max(age)  min(age) as agedifference
, min(hometown)
FROM
bakers;
baking=> select * from foobar ;
id  name
+
1  abc
2  def
 ghi

2  klm
(5 rows)
baking=> select count(*) from foobar ;
count

5
(1 row)
baking=> select count(*), count(id) from foobar ;
count  count
+
5  3
(1 row)
baking=> select count(*), count(id), count(distinct id) from foobar ;
count  count  count
++
5  3  2
(1 row)

SELECT
b.baker
, b.hometown
, count(*)
FROM
bakers b
, technicals t1
, technicals t2
WHERE
b.baker = t1.baker
and b.baker = t2.baker
and t1.episodeid <> t2.episodeid
and t1.rank = 1
and t2.rank = 1
GROUP BY
b.baker
, b.hometown
;

For each bakers, find how many times they won technical.
SELECT
t.baker
, count(*) as numtechnicalwon
FROM
technicals t
WHERE
t.rank = 1
GROUP BY
t.baker;
Find bakers who have won technical 2 times.
SELECT
t.baker
, count(*) as numtechnicalwon
FROM
technicals t
WHERE
t.rank = 1
GROUP BY
t.baker
HAVING
count(*) > 1
;

For each baker and rank, find how many times they got that rank in
technicals.
SELECT
b.baker
, t.rank
, count(*) as numtimes
FROM
technicals t
, bakers b
WHERE
b.baker = t.baker
GROUP BY
b.baker
, t.rank;
For each baker and rank, find how many times they got that rank in
technicals.
 but only for ranks less than 5
SELECT
b.baker
, t.rank
, count(*) as numtimes
FROM
technicals t
, bakers b
WHERE
b.baker = t.baker
and t.rank < 5
GROUP BY
b.baker
, t.rank
;

Bakers who competed in more than 4 episodes
SELECT  who competed in more than 4 episodes
t.baker
FROM
technicals t
GROUP BY
t.baker
HAVING
count(*)>4
For each baker and rank, find how many times they got that rank in
technicals.
 but only for ranks less than 5
 who competed in more than 4 episodes
SELECT
b.baker
, t.rank
, count(DISTINCT t.episodeid) as numtimes
FROM
technicals t
, bakers b
, signatures s
WHERE
b.baker = t.baker
and t.rank < 5
and s.baker = b.baker
GROUP BY
b.baker
, t.rank
HAVING
count(DISTINCT s.episodeid)>4
;

Find bakers who were never eliminated and have won technical at least 2 times.
SELECT
t.baker
FROM
technicals t
WHERE
t.rank = 1
GROUP BY
t.baker
HAVING
count(*)>=2
EXCEPT
SELECT
baker
FROM
results
WHERE
result = 'eliminated';

Find the last episode in the series.
Find the baker who won technical in the last episode
Find the biggest drop in the viewers from one episode to the
next. (Who was eliminated?)
For each month, find the average viewers.
