{ "cells": [ { "cell_type": "markdown", "id": "0dbce42b", "metadata": {}, "source": [ "## Lecture 13 - Advanced SQL\n", "\n", "### Announcements \n", "\n", "- Homework 3 to be posted later today or by \n", " tomorrow the latest, due next week on thursday \n", "- Homework 4 to be released tomorrow \n", "- Lecture Exercise 12 to be posted at 2pm today\n", "\n", "### Today's lecture \n", "\n", "- Review of basic SQL\n", "- Advanced SQL" ] }, { "cell_type": "code", "execution_count": 1, "id": "92d9c5f6", "metadata": {}, "outputs": [], "source": [ "load_ext sql" ] }, { "cell_type": "code", "execution_count": 2, "id": "9da67124", "metadata": {}, "outputs": [ { "data": { "text/html": [ "displaylimit: Value None will be treated as 0 (no limit)" ], "text/plain": [ "displaylimit: Value None will be treated as 0 (no limit)" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "Connecting to 'baking'" ], "text/plain": [ "Connecting to 'baking'" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%config SqlMagic.displaylimit = None\n", "%sql --section baking" ] }, { "cell_type": "markdown", "id": "3a76ec0a", "metadata": {}, "source": [ "SELECT .. FROM WHERE GROUP BY HAVING .... \n", "UNION/EXCEPT/INTERSECT \n", "SELECT .. FROM WHERE GROUP BY HAVING .... \n" ] }, { "cell_type": "code", "execution_count": 11, "id": "89c600d4", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "9 rows affected." ], "text/plain": [ "9 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
bakerepisodeidresult
Manon1star baker
Rahul2star baker
Rahul3star baker
Dan4star baker
Kim-Joy5star baker
Briony6star baker
Kim-Joy7star baker
Ruby8star baker
Ruby9star baker
" ], "text/plain": [ "+---------+-----------+------------+\n", "| baker | episodeid | result |\n", "+---------+-----------+------------+\n", "| Manon | 1 | star baker |\n", "| Rahul | 2 | star baker |\n", "| Rahul | 3 | star baker |\n", "| Dan | 4 | star baker |\n", "| Kim-Joy | 5 | star baker |\n", "| Briony | 6 | star baker |\n", "| Kim-Joy | 7 | star baker |\n", "| Ruby | 8 | star baker |\n", "| Ruby | 9 | star baker |\n", "+---------+-----------+------------+" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "select \n", " b.baker\n", " , r.episodeid\n", " , r.result\n", "from\n", " bakers b join\n", " results r on b.baker = r.baker\n", " and r.result = 'star baker'\n", "order by\n", " r.episodeid;" ] }, { "cell_type": "code", "execution_count": 14, "id": "b3f5299b", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "6 rows affected." ], "text/plain": [ "6 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
bakernumepisodes
Kim-Joy2
Rahul2
Ruby2
Briony1
Dan1
Manon1
" ], "text/plain": [ "+---------+-------------+\n", "| baker | numepisodes |\n", "+---------+-------------+\n", "| Kim-Joy | 2 |\n", "| Rahul | 2 |\n", "| Ruby | 2 |\n", "| Briony | 1 |\n", "| Dan | 1 |\n", "| Manon | 1 |\n", "+---------+-------------+" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "select \n", " b.baker\n", " , count(*) as numepisodes\n", "from\n", " bakers b join\n", " results r on b.baker = r.baker\n", " and r.result = 'star baker'\n", "group by \n", " b.baker \n", "order by\n", " numepisodes desc\n", " ;\n", " " ] }, { "cell_type": "code", "execution_count": 15, "id": "1278da3a", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "15 rows affected." ], "text/plain": [ "15 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
bakerbaker_1episodeid
AntonyNoneNone
BrionyBriony6
DanDan4
ImeldaNoneNone
JonNoneNone
KarenNoneNone
Kim-JoyKim-Joy5
Kim-JoyKim-Joy7
LukeNoneNone
ManonManon1
RahulRahul3
RahulRahul2
RubyRuby8
RubyRuby9
TerryNoneNone
" ], "text/plain": [ "+---------+---------+-----------+\n", "| baker | baker_1 | episodeid |\n", "+---------+---------+-----------+\n", "| Antony | None | None |\n", "| Briony | Briony | 6 |\n", "| Dan | Dan | 4 |\n", "| Imelda | None | None |\n", "| Jon | None | None |\n", "| Karen | None | None |\n", "| Kim-Joy | Kim-Joy | 5 |\n", "| Kim-Joy | Kim-Joy | 7 |\n", "| Luke | None | None |\n", "| Manon | Manon | 1 |\n", "| Rahul | Rahul | 3 |\n", "| Rahul | Rahul | 2 |\n", "| Ruby | Ruby | 8 |\n", "| Ruby | Ruby | 9 |\n", "| Terry | None | None |\n", "+---------+---------+-----------+" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "select \n", " b.baker\n", " , r.baker\n", " , r.episodeid\n", "from\n", " bakers b left join\n", " results r on b.baker = r.baker\n", " and r.result = 'star baker'\n", "order by\n", " b.baker asc;" ] }, { "cell_type": "code", "execution_count": 19, "id": "c8098ef5", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "12 rows affected." ], "text/plain": [ "12 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
bakernumepisodes
Antony0
Briony1
Dan1
Imelda0
Jon0
Karen0
Kim-Joy2
Luke0
Manon1
Rahul2
Ruby2
Terry0
" ], "text/plain": [ "+---------+-------------+\n", "| baker | numepisodes |\n", "+---------+-------------+\n", "| Antony | 0 |\n", "| Briony | 1 |\n", "| Dan | 1 |\n", "| Imelda | 0 |\n", "| Jon | 0 |\n", "| Karen | 0 |\n", "| Kim-Joy | 2 |\n", "| Luke | 0 |\n", "| Manon | 1 |\n", "| Rahul | 2 |\n", "| Ruby | 2 |\n", "| Terry | 0 |\n", "+---------+-------------+" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- for each baker, return the number of times they won star baker\n", "\n", "select \n", " b.baker\n", " , count(r.episodeid) as numepisodes\n", "from\n", " bakers b left join\n", " results r on b.baker = r.baker\n", " and r.result = 'star baker'\n", "group by\n", " b.baker\n", "order by\n", " b.baker\n", " ;" ] }, { "cell_type": "code", "execution_count": 21, "id": "debee505", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "15 rows affected." ], "text/plain": [ "15 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
bakerep1result1ep2result2
AntonyNoneNone3eliminated
Briony6star baker9eliminated
Dan4star baker6eliminated
ImeldaNoneNone1eliminated
JonNoneNone7eliminated
KarenNoneNone5eliminated
Kim-Joy5star bakerNoneNone
Kim-Joy7star bakerNoneNone
LukeNoneNone2eliminated
Manon1star baker8eliminated
Rahul3star bakerNoneNone
Rahul2star bakerNoneNone
Ruby8star bakerNoneNone
Ruby9star bakerNoneNone
TerryNoneNone5eliminated
" ], "text/plain": [ "+---------+------+------------+------+------------+\n", "| baker | ep1 | result1 | ep2 | result2 |\n", "+---------+------+------------+------+------------+\n", "| Antony | None | None | 3 | eliminated |\n", "| Briony | 6 | star baker | 9 | eliminated |\n", "| Dan | 4 | star baker | 6 | eliminated |\n", "| Imelda | None | None | 1 | eliminated |\n", "| Jon | None | None | 7 | eliminated |\n", "| Karen | None | None | 5 | eliminated |\n", "| Kim-Joy | 5 | star baker | None | None |\n", "| Kim-Joy | 7 | star baker | None | None |\n", "| Luke | None | None | 2 | eliminated |\n", "| Manon | 1 | star baker | 8 | eliminated |\n", "| Rahul | 3 | star baker | None | None |\n", "| Rahul | 2 | star baker | None | None |\n", "| Ruby | 8 | star baker | None | None |\n", "| Ruby | 9 | star baker | None | None |\n", "| Terry | None | None | 5 | eliminated |\n", "+---------+------+------------+------+------------+" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- for each baker, return the number of times they won star baker\n", "-- the number of times they were eliminated\n", "\n", "select \n", " b.baker\n", " , r.episodeid as ep1, r.result as result1\n", " , r2.episodeid as ep2, r2.result as result2\n", "from\n", " bakers b left join results r on b.baker = r.baker\n", " and r.result = 'star baker'\n", " left join results r2 on b.baker = r2.baker\n", " and r2.result = 'eliminated'\n", "order by\n", " b.baker\n", " ;" ] }, { "cell_type": "code", "execution_count": 22, "id": "95970abf", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "12 rows affected." ], "text/plain": [ "12 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
bakernumwinsnumeliminated
Antony01
Briony11
Dan11
Imelda01
Jon01
Karen01
Kim-Joy20
Luke01
Manon11
Rahul20
Ruby20
Terry01
" ], "text/plain": [ "+---------+---------+---------------+\n", "| baker | numwins | numeliminated |\n", "+---------+---------+---------------+\n", "| Antony | 0 | 1 |\n", "| Briony | 1 | 1 |\n", "| Dan | 1 | 1 |\n", "| Imelda | 0 | 1 |\n", "| Jon | 0 | 1 |\n", "| Karen | 0 | 1 |\n", "| Kim-Joy | 2 | 0 |\n", "| Luke | 0 | 1 |\n", "| Manon | 1 | 1 |\n", "| Rahul | 2 | 0 |\n", "| Ruby | 2 | 0 |\n", "| Terry | 0 | 1 |\n", "+---------+---------+---------------+" ] }, "execution_count": 22, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- for each baker, return the number of times they won star baker\n", "-- the number of times they were eliminated\n", "\n", "select \n", " b.baker\n", " , count(distinct r.episodeid) as numwins\n", " , count(distinct r2.episodeid) as numeliminated\n", "from\n", " bakers b left join results r on b.baker = r.baker\n", " and r.result = 'star baker'\n", " left join results r2 on b.baker = r2.baker\n", " and r2.result = 'eliminated'\n", "group by\n", " b.baker\n", "order by\n", " b.baker asc\n", " ;" ] }, { "cell_type": "code", "execution_count": 23, "id": "db68a695", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "3 rows affected." ], "text/plain": [ "3 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
baker
Kim-Joy
Ruby
Rahul
" ], "text/plain": [ "+---------+\n", "| baker |\n", "+---------+\n", "| Kim-Joy |\n", "| Ruby |\n", "| Rahul |\n", "+---------+" ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- people who were never eliminated\n", "\n", "select\n", " b.baker\n", "from\n", " bakers b left join results r \n", " on b.baker = r.baker and r.result='eliminated'\n", "where \n", " r.baker is null ;" ] }, { "cell_type": "markdown", "id": "6ba72f79", "metadata": {}, "source": [ "### Scalar queries\n", "\n", "- Queries that return a single value can be treated as a number \n", " in any part of a query" ] }, { "cell_type": "code", "execution_count": 27, "id": "d1da7247", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "1 rows affected." ], "text/plain": [ "1 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
avg
9.369
" ], "text/plain": [ "+-------+\n", "| avg |\n", "+-------+\n", "| 9.369 |\n", "+-------+" ] }, "execution_count": 27, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "select avg(viewers7day)\n", "from episodes ;" ] }, { "cell_type": "code", "execution_count": 28, "id": "7d833b2f", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "1 rows affected." ], "text/plain": [ "1 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
avg
36.4166666666666667
" ], "text/plain": [ "+---------------------+\n", "| avg |\n", "+---------------------+\n", "| 36.4166666666666667 |\n", "+---------------------+" ] }, "execution_count": 28, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- scalar query\n", "select avg(age)\n", "from bakers ;" ] }, { "cell_type": "code", "execution_count": 29, "id": "e72af66f", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "1 rows affected." ], "text/plain": [ "1 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
avgminmax
36.41666666666666672660
" ], "text/plain": [ "+---------------------+-----+-----+\n", "| avg | min | max |\n", "+---------------------+-----+-----+\n", "| 36.4166666666666667 | 26 | 60 |\n", "+---------------------+-----+-----+" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- not scalar query\n", "select avg(age), min(age), max(age)\n", "from bakers ;" ] }, { "cell_type": "code", "execution_count": 32, "id": "dcb74ee8", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "3 rows affected." ], "text/plain": [ "3 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
bakerfullnameageoccupationhometown
JonJon Jenkins47Blood courierNewport
KarenKaren Wright60In-store sampling assistantWakefield
TerryTerry Hartill56Retired air stewardWest Midlands
" ], "text/plain": [ "+-------+---------------+-----+-----------------------------+---------------+\n", "| baker | fullname | age | occupation | hometown |\n", "+-------+---------------+-----+-----------------------------+---------------+\n", "| Jon | Jon Jenkins | 47 | Blood courier | Newport |\n", "| Karen | Karen Wright | 60 | In-store sampling assistant | Wakefield |\n", "| Terry | Terry Hartill | 56 | Retired air steward | West Midlands |\n", "+-------+---------------+-----+-----------------------------+---------------+" ] }, "execution_count": 32, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- find bakers who are older than average age\n", "\n", "select *\n", "from bakers\n", "where age > (select avg(b2.age) from bakers b2);" ] }, { "cell_type": "code", "execution_count": 38, "id": "bfca87e9", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "12 rows affected." ], "text/plain": [ "12 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
bakerageavgagegreatest
Antony3036.420
Briony3336.420
Dan3636.420
Imelda3336.420
Jon4736.4210.58
Karen6036.4223.58
Kim-Joy2736.420
Luke3036.420
Manon2636.420
Rahul3036.420
Ruby2936.420
Terry5636.4219.58
" ], "text/plain": [ "+---------+-----+--------+----------+\n", "| baker | age | avgage | greatest |\n", "+---------+-----+--------+----------+\n", "| Antony | 30 | 36.42 | 0 |\n", "| Briony | 33 | 36.42 | 0 |\n", "| Dan | 36 | 36.42 | 0 |\n", "| Imelda | 33 | 36.42 | 0 |\n", "| Jon | 47 | 36.42 | 10.58 |\n", "| Karen | 60 | 36.42 | 23.58 |\n", "| Kim-Joy | 27 | 36.42 | 0 |\n", "| Luke | 30 | 36.42 | 0 |\n", "| Manon | 26 | 36.42 | 0 |\n", "| Rahul | 30 | 36.42 | 0 |\n", "| Ruby | 29 | 36.42 | 0 |\n", "| Terry | 56 | 36.42 | 19.58 |\n", "+---------+-----+--------+----------+" ] }, "execution_count": 38, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- for each bakers, return how old they are and how much older\n", "-- they are from average age\n", "\n", "select baker\n", " , age\n", " , (select avg(age)::numeric(5,2) from bakers) as avgage\n", " , greatest(0, age - (select avg(b.age)::numeric(5,2) from bakers b))\n", "from bakers\n", "order by baker;" ] }, { "cell_type": "markdown", "id": "977ea77c", "metadata": {}, "source": [ "## Queries that return a single value, but a set of tuples\n", "\n", "- We can treat them as set of values and do set queries\n", "\n", "value IN (SET -- results of a query) \n", "value NOT IN (SET -- results of a query) \n", "value >= ALL (SET -- results of a query) \n", "\n", "\n", "value < ALL (SET -- results of a query) \n", "value >= ANY (SET -- results of a query) \n", "value = ANY (SET -- results of a query) \n", "value <> ANY (SET -- results of a query) \n", "\n", "- Also you can check if the set is empty or non-empty\n", "\n", "EXISTS (SET -- results of a query)\n", "\n", " -- true if there are tuples returned by the query\n", " \n", "NOT EXISTS (SET -- results of a query)\n", "\n", " -- true if there are no tuples returned by the inner query " ] }, { "cell_type": "code", "execution_count": 39, "id": "f571123e", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "6 rows affected." ], "text/plain": [ "6 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
bakerfullnameageoccupationhometown
BrionyBriony Williams33Full-time parentBristol
DanDan Beasley-Harling36Full-time parentLondon
ImeldaImelda McCarron33Countryside recreation officerCounty Tyrone
JonJon Jenkins47Blood courierNewport
KarenKaren Wright60In-store sampling assistantWakefield
TerryTerry Hartill56Retired air stewardWest Midlands
" ], "text/plain": [ "+--------+---------------------+-----+--------------------------------+---------------+\n", "| baker | fullname | age | occupation | hometown |\n", "+--------+---------------------+-----+--------------------------------+---------------+\n", "| Briony | Briony Williams | 33 | Full-time parent | Bristol |\n", "| Dan | Dan Beasley-Harling | 36 | Full-time parent | London |\n", "| Imelda | Imelda McCarron | 33 | Countryside recreation officer | County Tyrone |\n", "| Jon | Jon Jenkins | 47 | Blood courier | Newport |\n", "| Karen | Karen Wright | 60 | In-store sampling assistant | Wakefield |\n", "| Terry | Terry Hartill | 56 | Retired air steward | West Midlands |\n", "+--------+---------------------+-----+--------------------------------+---------------+" ] }, "execution_count": 39, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "select *\n", "from bakers\n", "where age > (select age from bakers where baker ='Rahul');" ] }, { "cell_type": "code", "execution_count": 44, "id": "4d498c0c", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "4 rows affected." ], "text/plain": [ "4 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
age
30
36
26
29
" ], "text/plain": [ "+-----+\n", "| age |\n", "+-----+\n", "| 30 |\n", "| 36 |\n", "| 26 |\n", "| 29 |\n", "+-----+" ] }, "execution_count": 44, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "select age from bakers where hometown='London';" ] }, { "cell_type": "code", "execution_count": 48, "id": "3010d596", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "3 rows affected." ], "text/plain": [ "3 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
bakeragehometown
Jon47Newport
Karen60Wakefield
Terry56West Midlands
" ], "text/plain": [ "+-------+-----+---------------+\n", "| baker | age | hometown |\n", "+-------+-----+---------------+\n", "| Jon | 47 | Newport |\n", "| Karen | 60 | Wakefield |\n", "| Terry | 56 | West Midlands |\n", "+-------+-----+---------------+" ] }, "execution_count": 48, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "select baker, age , hometown\n", "from bakers \n", "where age > ALL \n", " (select age from bakers where hometown='London');" ] }, { "cell_type": "code", "execution_count": 49, "id": "4d5d594f", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "3 rows affected." ], "text/plain": [ "3 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
bakeragehometown
Jon47Newport
Karen60Wakefield
Terry56West Midlands
" ], "text/plain": [ "+-------+-----+---------------+\n", "| baker | age | hometown |\n", "+-------+-----+---------------+\n", "| Jon | 47 | Newport |\n", "| Karen | 60 | Wakefield |\n", "| Terry | 56 | West Midlands |\n", "+-------+-----+---------------+" ] }, "execution_count": 49, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "select baker, age , hometown\n", "from bakers \n", "where age > \n", " (select max(age) from bakers where hometown='London');" ] }, { "cell_type": "code", "execution_count": 50, "id": "b58adc24", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "3 rows affected." ], "text/plain": [ "3 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
bakerfullnameageoccupationhometown
Kim-JoyKim-Joy Hewlett27Mental health specialistLeeds
RahulRahul Mandal30Research scientistRotherham
RubyRuby Bhogal29Project managerLondon
" ], "text/plain": [ "+---------+-----------------+-----+--------------------------+-----------+\n", "| baker | fullname | age | occupation | hometown |\n", "+---------+-----------------+-----+--------------------------+-----------+\n", "| Kim-Joy | Kim-Joy Hewlett | 27 | Mental health specialist | Leeds |\n", "| Rahul | Rahul Mandal | 30 | Research scientist | Rotherham |\n", "| Ruby | Ruby Bhogal | 29 | Project manager | London |\n", "+---------+-----------------+-----+--------------------------+-----------+" ] }, "execution_count": 50, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "-- find bakers who were never eliminated\n", "\n", "select * from bakers\n", "-- where baker not in (--people who are eliminated)\n", "where baker not in (select baker from results where result='eliminated');" ] }, { "cell_type": "code", "execution_count": 51, "id": "89a6b40c", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "6 rows affected." ], "text/plain": [ "6 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
bakerfullnameageoccupationhometown
AntonyAntony Amourdoux30BankerLondon
ImeldaImelda McCarron33Countryside recreation officerCounty Tyrone
JonJon Jenkins47Blood courierNewport
KarenKaren Wright60In-store sampling assistantWakefield
LukeLuke Thompson30Civil servant/house and techno DJSheffield
TerryTerry Hartill56Retired air stewardWest Midlands
" ], "text/plain": [ "+--------+------------------+-----+-----------------------------------+---------------+\n", "| baker | fullname | age | occupation | hometown |\n", "+--------+------------------+-----+-----------------------------------+---------------+\n", "| Antony | Antony Amourdoux | 30 | Banker | London |\n", "| Imelda | Imelda McCarron | 33 | Countryside recreation officer | County Tyrone |\n", "| Jon | Jon Jenkins | 47 | Blood courier | Newport |\n", "| Karen | Karen Wright | 60 | In-store sampling assistant | Wakefield |\n", "| Luke | Luke Thompson | 30 | Civil servant/house and techno DJ | Sheffield |\n", "| Terry | Terry Hartill | 56 | Retired air steward | West Midlands |\n", "+--------+------------------+-----+-----------------------------------+---------------+" ] }, "execution_count": 51, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "-- find bakers who were never star baker\n", "\n", "select * from bakers\n", "-- where baker not in (--people who are eliminated)\n", "where baker not in \n", " (select baker from results where result='star baker');" ] }, { "cell_type": "code", "execution_count": null, "id": "7a43c42d", "metadata": {}, "outputs": [], "source": [ "%%sql\n", "-- find bakers who were never eliminated\n", "\n", "\n", "select baker, '1' from bakers\n", "where baker not in (select baker from results where result='eliminated');\n", "\n", "select\n", " b.baker, '2'\n", "from\n", " bakers b left join results r \n", " on b.baker = r.baker and r.result='eliminated'\n", "where \n", " r.baker is null ;\n", " \n", "select baker, '3'\n", "from bakers\n", "except \n", "select baker, '3'\n", "from results\n", "where result = 'eliminated';" ] }, { "cell_type": "code", "execution_count": 52, "id": "532783d4", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "5 rows affected." ], "text/plain": [ "5 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
QUERY PLAN
Seq Scan on bakers (cost=13.25..24.63 rows=55 width=70)
  Filter: (NOT (ANY ((baker)::text = ((hashed SubPlan 1).col1)::text)))
  SubPlan 1
    -> Seq Scan on results (cost=0.00..13.25 rows=1 width=218)
          Filter: ((result)::text = 'eliminated'::text)
" ], "text/plain": [ "+-------------------------------------------------------------------------+\n", "| QUERY PLAN |\n", "+-------------------------------------------------------------------------+\n", "| Seq Scan on bakers (cost=13.25..24.63 rows=55 width=70) |\n", "| Filter: (NOT (ANY ((baker)::text = ((hashed SubPlan 1).col1)::text))) |\n", "| SubPlan 1 |\n", "| -> Seq Scan on results (cost=0.00..13.25 rows=1 width=218) |\n", "| Filter: ((result)::text = 'eliminated'::text) |\n", "+-------------------------------------------------------------------------+" ] }, "execution_count": 52, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "explain select baker, '1' from bakers\n", "where baker not in (select baker from results where result='eliminated');\n", "\n" ] }, { "cell_type": "code", "execution_count": 55, "id": "5d37d966", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "63 rows affected." ], "text/plain": [ "63 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
bakerepisodeid
Kim-Joy2
Manon7
Terry5
Jon1
Ruby5
Briony3
Karen2
Kim-Joy10
Briony7
Rahul8
Antony3
Briony9
Manon3
Briony4
Jon7
Antony1
Manon1
Dan1
Jon3
Manon4
Dan4
Luke1
Manon6
Dan6
Rahul1
Briony6
Terry2
Kim-Joy5
Rahul9
Ruby10
Karen5
Jon6
Ruby2
Rahul3
Manon8
Kim-Joy6
Ruby1
Jon5
Terry1
Ruby4
Ruby9
Manon5
Dan5
Rahul10
Ruby7
Rahul2
Ruby3
Briony5
Terry3
Kim-Joy8
Kim-Joy7
Antony2
Manon2
Briony2
Karen3
Ruby8
Luke2
Kim-Joy3
Karen1
Kim-Joy1
Imelda1
Kim-Joy4
Karen4
" ], "text/plain": [ "+---------+-----------+\n", "| baker | episodeid |\n", "+---------+-----------+\n", "| Kim-Joy | 2 |\n", "| Manon | 7 |\n", "| Terry | 5 |\n", "| Jon | 1 |\n", "| Ruby | 5 |\n", "| Briony | 3 |\n", "| Karen | 2 |\n", "| Kim-Joy | 10 |\n", "| Briony | 7 |\n", "| Rahul | 8 |\n", "| Antony | 3 |\n", "| Briony | 9 |\n", "| Manon | 3 |\n", "| Briony | 4 |\n", "| Jon | 7 |\n", "| Antony | 1 |\n", "| Manon | 1 |\n", "| Dan | 1 |\n", "| Jon | 3 |\n", "| Manon | 4 |\n", "| Dan | 4 |\n", "| Luke | 1 |\n", "| Manon | 6 |\n", "| Dan | 6 |\n", "| Rahul | 1 |\n", "| Briony | 6 |\n", "| Terry | 2 |\n", "| Kim-Joy | 5 |\n", "| Rahul | 9 |\n", "| Ruby | 10 |\n", "| Karen | 5 |\n", "| Jon | 6 |\n", "| Ruby | 2 |\n", "| Rahul | 3 |\n", "| Manon | 8 |\n", "| Kim-Joy | 6 |\n", "| Ruby | 1 |\n", "| Jon | 5 |\n", "| Terry | 1 |\n", "| Ruby | 4 |\n", "| Ruby | 9 |\n", "| Manon | 5 |\n", "| Dan | 5 |\n", "| Rahul | 10 |\n", "| Ruby | 7 |\n", "| Rahul | 2 |\n", "| Ruby | 3 |\n", "| Briony | 5 |\n", "| Terry | 3 |\n", "| Kim-Joy | 8 |\n", "| Kim-Joy | 7 |\n", "| Antony | 2 |\n", "| Manon | 2 |\n", "| Briony | 2 |\n", "| Karen | 3 |\n", "| Ruby | 8 |\n", "| Luke | 2 |\n", "| Kim-Joy | 3 |\n", "| Karen | 1 |\n", "| Kim-Joy | 1 |\n", "| Imelda | 1 |\n", "| Kim-Joy | 4 |\n", "| Karen | 4 |\n", "+---------+-----------+" ] }, "execution_count": 55, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- For each baker, return the episodes in which \n", "-- they participated in but were not a favorite\n", "\n", "select\n", " t.baker, t.episodeid\n", "from \n", " technicals t\n", "where\n", " not exists (\n", " select * \n", " from favorites f\n", " where f.baker = t.baker and f.episodeid = t.episodeid);" ] }, { "cell_type": "code", "execution_count": 59, "id": "f1a8de8b", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "63 rows affected." ], "text/plain": [ "63 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
bakerepisodeid
Antony1
Dan1
Imelda1
Jon1
Karen1
Kim-Joy1
Luke1
Manon1
Rahul1
Ruby1
Terry1
Antony2
Briony2
Karen2
Kim-Joy2
Luke2
Manon2
Rahul2
Ruby2
Terry2
Antony3
Briony3
Jon3
Karen3
Kim-Joy3
Manon3
Rahul3
Ruby3
Terry3
Briony4
Dan4
Karen4
Kim-Joy4
Manon4
Ruby4
Briony5
Dan5
Jon5
Karen5
Kim-Joy5
Manon5
Ruby5
Terry5
Briony6
Dan6
Jon6
Kim-Joy6
Manon6
Briony7
Jon7
Kim-Joy7
Manon7
Ruby7
Kim-Joy8
Manon8
Rahul8
Ruby8
Briony9
Rahul9
Ruby9
Kim-Joy10
Rahul10
Ruby10
" ], "text/plain": [ "+---------+-----------+\n", "| baker | episodeid |\n", "+---------+-----------+\n", "| Antony | 1 |\n", "| Dan | 1 |\n", "| Imelda | 1 |\n", "| Jon | 1 |\n", "| Karen | 1 |\n", "| Kim-Joy | 1 |\n", "| Luke | 1 |\n", "| Manon | 1 |\n", "| Rahul | 1 |\n", "| Ruby | 1 |\n", "| Terry | 1 |\n", "| Antony | 2 |\n", "| Briony | 2 |\n", "| Karen | 2 |\n", "| Kim-Joy | 2 |\n", "| Luke | 2 |\n", "| Manon | 2 |\n", "| Rahul | 2 |\n", "| Ruby | 2 |\n", "| Terry | 2 |\n", "| Antony | 3 |\n", "| Briony | 3 |\n", "| Jon | 3 |\n", "| Karen | 3 |\n", "| Kim-Joy | 3 |\n", "| Manon | 3 |\n", "| Rahul | 3 |\n", "| Ruby | 3 |\n", "| Terry | 3 |\n", "| Briony | 4 |\n", "| Dan | 4 |\n", "| Karen | 4 |\n", "| Kim-Joy | 4 |\n", "| Manon | 4 |\n", "| Ruby | 4 |\n", "| Briony | 5 |\n", "| Dan | 5 |\n", "| Jon | 5 |\n", "| Karen | 5 |\n", "| Kim-Joy | 5 |\n", "| Manon | 5 |\n", "| Ruby | 5 |\n", "| Terry | 5 |\n", "| Briony | 6 |\n", "| Dan | 6 |\n", "| Jon | 6 |\n", "| Kim-Joy | 6 |\n", "| Manon | 6 |\n", "| Briony | 7 |\n", "| Jon | 7 |\n", "| Kim-Joy | 7 |\n", "| Manon | 7 |\n", "| Ruby | 7 |\n", "| Kim-Joy | 8 |\n", "| Manon | 8 |\n", "| Rahul | 8 |\n", "| Ruby | 8 |\n", "| Briony | 9 |\n", "| Rahul | 9 |\n", "| Ruby | 9 |\n", "| Kim-Joy | 10 |\n", "| Rahul | 10 |\n", "| Ruby | 10 |\n", "+---------+-----------+" ] }, "execution_count": 59, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- For each baker, return the episodes in which \n", "-- they participated in but were not a favorite\n", "\n", "select\n", " baker, episodeid\n", "from \n", " technicals \n", "where\n", " (baker, episodeid) not in\n", " (select baker, episodeid from favorites);" ] }, { "cell_type": "code", "execution_count": 60, "id": "d1ee2c90", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "3 rows affected." ], "text/plain": [ "3 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
baker
Kim-Joy
Ruby
Rahul
" ], "text/plain": [ "+---------+\n", "| baker |\n", "+---------+\n", "| Kim-Joy |\n", "| Ruby |\n", "| Rahul |\n", "+---------+" ] }, "execution_count": 60, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "select b.baker\n", "from bakers b\n", "where not exists (select * from results r where r.baker = b.baker\n", " and r.result = 'eliminated');" ] }, { "cell_type": "code", "execution_count": 61, "id": "9a9801fc", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "3 rows affected." ], "text/plain": [ "3 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
baker
Kim-Joy
Rahul
Ruby
" ], "text/plain": [ "+---------+\n", "| baker |\n", "+---------+\n", "| Kim-Joy |\n", "| Rahul |\n", "| Ruby |\n", "+---------+" ] }, "execution_count": 61, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "-- Find bakers who competed in every episode!\n", "\n", "-- Find bakers, for whom there does not exists an episode\n", "-- for the show, that they did not compete in\n", "-- (there does not exist a tuple in technicals for this episode\n", "-- and this baker)\n", "\n", "select\n", " b.baker\n", "from\n", " bakers b\n", "where\n", " not exists (\n", " select * from episodes e\n", " where not exists (\n", " select * from technicals t\n", " where t.episodeid = e.id and t.baker = b.baker\n", " )\n", " ) ; " ] }, { "cell_type": "code", "execution_count": 63, "id": "15a7b6f5", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "7 rows affected." ], "text/plain": [ "7 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
QUERY PLAN
Nested Loop Anti Join (cost=0.00..9659.32 rows=55 width=38)
  Join Filter: (NOT (ANY ((e.id = (hashed SubPlan 2).col1) AND ((b.baker)::text = (hashed SubPlan 2).col2))))
  -> Seq Scan on bakers b (cost=0.00..11.10 rows=110 width=38)
  -> Materialize (cost=0.00..11.20 rows=80 width=4)
        -> Seq Scan on episodes e (cost=0.00..10.80 rows=80 width=4)
  SubPlan 2
    -> Seq Scan on technicals t (cost=0.00..1.75 rows=75 width=36)
" ], "text/plain": [ "+---------------------------------------------------------------------------------------------------------------+\n", "| QUERY PLAN |\n", "+---------------------------------------------------------------------------------------------------------------+\n", "| Nested Loop Anti Join (cost=0.00..9659.32 rows=55 width=38) |\n", "| Join Filter: (NOT (ANY ((e.id = (hashed SubPlan 2).col1) AND ((b.baker)::text = (hashed SubPlan 2).col2)))) |\n", "| -> Seq Scan on bakers b (cost=0.00..11.10 rows=110 width=38) |\n", "| -> Materialize (cost=0.00..11.20 rows=80 width=4) |\n", "| -> Seq Scan on episodes e (cost=0.00..10.80 rows=80 width=4) |\n", "| SubPlan 2 |\n", "| -> Seq Scan on technicals t (cost=0.00..1.75 rows=75 width=36) |\n", "+---------------------------------------------------------------------------------------------------------------+" ] }, "execution_count": 63, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "explain select\n", " b.baker\n", "from\n", " bakers b\n", "where\n", " not exists (\n", " select * from episodes e\n", " where not exists (\n", " select * from technicals t\n", " where t.episodeid = e.id and t.baker = b.baker\n", " )\n", " ) ; " ] }, { "cell_type": "code", "execution_count": 62, "id": "2d3892ad", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "3 rows affected." ], "text/plain": [ "3 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
baker
Rahul
Ruby
Kim-Joy
" ], "text/plain": [ "+---------+\n", "| baker |\n", "+---------+\n", "| Rahul |\n", "| Ruby |\n", "| Kim-Joy |\n", "+---------+" ] }, "execution_count": 62, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select \n", " baker\n", "from \n", " technicals\n", "group by \n", " baker\n", "having \n", " count(*) = (select count(*) from episodes);" ] }, { "cell_type": "code", "execution_count": 65, "id": "3e6414a5", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "7 rows affected." ], "text/plain": [ "7 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
QUERY PLAN
HashAggregate (cost=13.13..13.29 rows=1 width=5)
  Group Key: technicals.baker
  Filter: (count(*) = (InitPlan 1).col1)
  InitPlan 1
    -> Aggregate (cost=11.00..11.01 rows=1 width=8)
          -> Seq Scan on episodes (cost=0.00..10.80 rows=80 width=0)
  -> Seq Scan on technicals (cost=0.00..1.75 rows=75 width=5)
" ], "text/plain": [ "+------------------------------------------------------------------------+\n", "| QUERY PLAN |\n", "+------------------------------------------------------------------------+\n", "| HashAggregate (cost=13.13..13.29 rows=1 width=5) |\n", "| Group Key: technicals.baker |\n", "| Filter: (count(*) = (InitPlan 1).col1) |\n", "| InitPlan 1 |\n", "| -> Aggregate (cost=11.00..11.01 rows=1 width=8) |\n", "| -> Seq Scan on episodes (cost=0.00..10.80 rows=80 width=0) |\n", "| -> Seq Scan on technicals (cost=0.00..1.75 rows=75 width=5) |\n", "+------------------------------------------------------------------------+" ] }, "execution_count": 65, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "explain \n", "select \n", " baker\n", "from \n", " technicals\n", "group by \n", " baker\n", "having \n", " count(*) = (select count(*) from episodes);" ] }, { "cell_type": "markdown", "id": "010b5d38", "metadata": {}, "source": [ "Final notes:\n", "\n", "- Learn to write queries in many different ways\n", "- Using subqueries don't often save cost, but may be\n", "necessary for some queries.\n", "- Using subqueries may not significant increase query cost as long as the subquery is uncorrelated, i.e. runs independent of the outer query. In this case, the query can be executed once and results can be saved/cached.\n", "- Correlated subqueries may significantly increase cost, depending on the complexity. \n", "- You can see the cost of a query in Postgres using the explain keyword.\n", "- If you are using IN or EXISTS with a subquery, often that query can be rewritten without a subquery involving a join (though not always, especially if you are counting things!).\n", "- Don't forget, count and left join are very powerful constructs and can help you solve many queries.\n", " " ] }, { "cell_type": "code", "execution_count": null, "id": "e5e6f911", "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.13.5" } }, "nbformat": 4, "nbformat_minor": 5 }