{ "cells": [ { "cell_type": "markdown", "id": "33f01438", "metadata": { "scrolled": true }, "source": [ "## Lecture 12 - SQL and moreSQL\n", "\n", "### Announcements \n", "\n", "- Homework 3 to be posted later today or by \n", " tomorrow the latest, due next week on thursday \n", "- Exam 2 grades to be released later today\n", "- Lecture Exercise 11 deadline was extended to today at midnight\n", "- Lecture Exercise 12 to be posted tomorrow\n", "\n", "- No class on monday!\n", "\n", "### Today's class\n", "\n", "- Review of main SQL syntax\n", "- Outer join\n" ] }, { "cell_type": "code", "execution_count": 1, "id": "ba21432e", "metadata": {}, "outputs": [], "source": [ "load_ext sql" ] }, { "cell_type": "code", "execution_count": 2, "id": "184079a7", "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\n" ] }, { "cell_type": "markdown", "id": "a8b7e653", "metadata": {}, "source": [ "1. Find episodes in which somebody who was a favorite in the previous episode won the star baker.\n", "\n", "2. For each baker, find the number of times they used chocolate in their showstopper makes.\n", "\n", "3. Find bakers who used chocolate more than 2 times in their showstopper makes.\n", "\n", "4. Find episodes in which two people were eliminated.\n", "\n", "5. Find episodes in which nobody was eliminated.\n", "\n", "6. Find bakers and episodes in which the baker used chocolate either in the signature make or showstopper make.\n", "\n", "7. Find bakers who was a star baker at least once, but never won in a technical challenge.\n", "\n", "8. Find bakers who were favorite at least twice and won star baker at least twice." ] }, { "cell_type": "code", "execution_count": 3, "id": "f7464daa", "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", "
episodeid
4
" ], "text/plain": [ "+-----------+\n", "| episodeid |\n", "+-----------+\n", "| 4 |\n", "+-----------+" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- 1. Find episodes in which somebody who was a favorite \n", "-- in the previous episode won the star baker.\n", "\n", "select DISTINCT\n", " r.episodeid\n", "from\n", " results r\n", " , favorites f\n", "where \n", " r.baker = f.baker\n", " and r.episodeid = f.episodeid+1\n", " and r.result = 'star baker'" ] }, { "cell_type": "code", "execution_count": 10, "id": "bbf8ad05", "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", "
bakernummakes
Ruby3
Manon2
Rahul2
Karen1
Antony1
Luke1
Kim-Joy1
Briony1
Dan1
" ], "text/plain": [ "+---------+----------+\n", "| baker | nummakes |\n", "+---------+----------+\n", "| Ruby | 3 |\n", "| Manon | 2 |\n", "| Rahul | 2 |\n", "| Karen | 1 |\n", "| Antony | 1 |\n", "| Luke | 1 |\n", "| Kim-Joy | 1 |\n", "| Briony | 1 |\n", "| Dan | 1 |\n", "+---------+----------+" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- 2. For each baker, find the number of times they used \n", "-- chocolate in their showstopper makes.\n", "\n", "select\n", " baker\n", " , count(*) as nummakes\n", "from\n", " showstoppers s\n", "where\n", " lower(s.make) like '%chocolate%' \n", "group by\n", " s.baker\n", "order by\n", " nummakes desc\n", " ;" ] }, { "cell_type": "markdown", "id": "1a7b9b4b", "metadata": {}, "source": [ "SELECT \n", "FROM relations\n", "WHERE ...\n", "GROUP BY a,b\n", "HAVING " ] }, { "cell_type": "code", "execution_count": 16, "id": "05c347e8", "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", "
bakernummakes
Manon2
Rahul2
Ruby3
" ], "text/plain": [ "+-------+----------+\n", "| baker | nummakes |\n", "+-------+----------+\n", "| Manon | 2 |\n", "| Rahul | 2 |\n", "| Ruby | 3 |\n", "+-------+----------+" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "-- 3. Find bakers who used chocolate more than 2 times \n", "-- in their showstopper makes.\n", "\n", "select\n", " baker\n", " , count(*) as nummakes\n", "from\n", " showstoppers s\n", "where\n", " lower(s.make) like '%chocolate%' \n", "group by\n", " s.baker\n", "having\n", " count(*)>1\n", " ;" ] }, { "cell_type": "markdown", "id": "aee2d2ab", "metadata": {}, "source": [] }, { "cell_type": "code", "execution_count": 19, "id": "e8f80eae", "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", "
idfirstaired
52018-09-25
" ], "text/plain": [ "+----+------------+\n", "| id | firstaired |\n", "+----+------------+\n", "| 5 | 2018-09-25 |\n", "+----+------------+" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- 4. Find id and airdate of episodes in which \n", "-- two people were eliminated.\n", "\n", "select\n", " r.episodeid\n", " , e.firstaired\n", "from\n", " results r\n", " , episodes e\n", "where\n", " r.episodeid = e.id\n", " and r.result = 'eliminated'\n", "group by\n", " r.episodeid\n", " , e.firstaired\n", "having\n", " count(*) = 2;\n" ] }, { "cell_type": "code", "execution_count": 21, "id": "7ea26b4f", "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", "
idfirstaired
52018-09-25
" ], "text/plain": [ "+----+------------+\n", "| id | firstaired |\n", "+----+------------+\n", "| 5 | 2018-09-25 |\n", "+----+------------+" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- 4. Find id and airdate of episodes in which \n", "-- two people were eliminated.\n", "\n", "select\n", " e.id\n", " , e.firstaired\n", "from\n", " results r\n", " , episodes e\n", "where\n", " r.episodeid = e.id\n", " and r.result = 'eliminated'\n", "group by\n", " e.id\n", "having\n", " count(*) = 2;\n" ] }, { "cell_type": "code", "execution_count": 23, "id": "74fea6e3", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "2 rows affected." ], "text/plain": [ "2 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
id
4
10
" ], "text/plain": [ "+----+\n", "| id |\n", "+----+\n", "| 4 |\n", "| 10 |\n", "+----+" ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- 5. Find episodes in which nobody was eliminated.\n", "\n", "select id -- all episodes\n", "from episodes\n", "except \n", "select -- episodes in which someone is eliminated\n", " r.episodeid\n", "from\n", " results r\n", "where\n", " r.result = 'eliminated' ;\n" ] }, { "cell_type": "code", "execution_count": 25, "id": "b5ea7317", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "14 rows affected." ], "text/plain": [ "14 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", "
bakerepisodeid
Antony3
Briony2
Dan2
Imelda1
Karen2
Kim-Joy4
Luke2
Manon1
Manon4
Rahul2
Rahul7
Ruby2
Ruby4
Ruby7
" ], "text/plain": [ "+---------+-----------+\n", "| baker | episodeid |\n", "+---------+-----------+\n", "| Antony | 3 |\n", "| Briony | 2 |\n", "| Dan | 2 |\n", "| Imelda | 1 |\n", "| Karen | 2 |\n", "| Kim-Joy | 4 |\n", "| Luke | 2 |\n", "| Manon | 1 |\n", "| Manon | 4 |\n", "| Rahul | 2 |\n", "| Rahul | 7 |\n", "| Ruby | 2 |\n", "| Ruby | 4 |\n", "| Ruby | 7 |\n", "+---------+-----------+" ] }, "execution_count": 25, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- 6. Find bakers and episodes in which the baker used \n", "-- chocolate either in the signature make or showstopper make.\n", "\n", "select\n", " baker, episodeid\n", "from\n", " showstoppers\n", "where\n", " lower(make) like '%chocolate%'\n", "union all\n", "select\n", " baker, episodeid\n", "from \n", " signatures\n", "where\n", " lower(make) like '%chocolate%' ;\n" ] }, { "cell_type": "code", "execution_count": 29, "id": "94a8d541", "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", "
baker
Manon
" ], "text/plain": [ "+-------+\n", "| baker |\n", "+-------+\n", "| Manon |\n", "+-------+" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- 7. Find bakers who was a star baker at least once, \n", "-- but never won in a technical challenge.\n", "\n", "\n", "select -- bakers who were star baker\n", " baker\n", "from \n", " results\n", "where \n", " result = 'star baker'\n", "except \n", "select -- bakers who won the technical challenge\n", " baker\n", "from \n", " technicals\n", "where \n", " rank =1 ;\n", "\n" ] }, { "cell_type": "code", "execution_count": 33, "id": "02978e1d", "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", "
baker
Rahul
" ], "text/plain": [ "+-------+\n", "| baker |\n", "+-------+\n", "| Rahul |\n", "+-------+" ] }, "execution_count": 33, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "-- 8. Find bakers who were favorite at least twice and \n", "-- won star baker at least twice.\n", "\n", "select\n", " r.baker\n", "from\n", " favorites f\n", " , results r\n", "where\n", " f.baker = r.baker\n", " and r.result = 'star baker'\n", "group by\n", " r.baker\n", "having \n", " count(distinct r.episodeid)>= 2\n", " and count(distinct f.episodeid)>= 2" ] }, { "cell_type": "code", "execution_count": 32, "id": "61b82023", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "16 rows affected." ], "text/plain": [ "16 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", "
bakerfavsb
Briony86
Briony16
Dan24
Dan34
Kim-Joy95
Kim-Joy97
Rahul53
Rahul63
Rahul62
Rahul43
Rahul52
Rahul73
Rahul72
Rahul42
Ruby69
Ruby68
" ], "text/plain": [ "+---------+-----+----+\n", "| baker | fav | sb |\n", "+---------+-----+----+\n", "| Briony | 8 | 6 |\n", "| Briony | 1 | 6 |\n", "| Dan | 2 | 4 |\n", "| Dan | 3 | 4 |\n", "| Kim-Joy | 9 | 5 |\n", "| Kim-Joy | 9 | 7 |\n", "| Rahul | 5 | 3 |\n", "| Rahul | 6 | 3 |\n", "| Rahul | 6 | 2 |\n", "| Rahul | 4 | 3 |\n", "| Rahul | 5 | 2 |\n", "| Rahul | 7 | 3 |\n", "| Rahul | 7 | 2 |\n", "| Rahul | 4 | 2 |\n", "| Ruby | 6 | 9 |\n", "| Ruby | 6 | 8 |\n", "+---------+-----+----+" ] }, "execution_count": 32, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "-- Sanity check for count, to see if we are returning the\n", "-- correct results for query 8!\n", "\n", "select\n", " r.baker, f.episodeid as fav, r.episodeid as sb\n", "from\n", " favorites f\n", " , results r\n", "where\n", " f.baker = r.baker\n", " and r.result = 'star baker'\n", "order by r.baker asc;" ] }, { "cell_type": "markdown", "id": "9f72a0e0", "metadata": {}, "source": [ "### Inner vs. Outer Join\n", "\n", "-- Inner join of two relations A and B returns only those tuples that match the join condition. The other tuples are eliminated from **both** relations.\n", "\n", "\n", "- In A JOIN B, A is the left relation and B is right relation\n", "\n", "- A LEFT OUTER JOIN B ON condition: tuples in (A INNER JOIN B) union all (all tuples in A that did not join with B on the given condition)\n", "\n", "- A RIGHT OUTER JOIN B ON condition: tuples in (A INNER JOIN B) union all (all tuples in B that did not join with A on the given condition)\n", "\n", "- A FULL OUTER JOIN B on condition: \n", " - tuples in (A INNER JOIN B) union all \n", " - (all tuples in B that did not join with A on the given condition) union all\n", " - (all tuples in A that did not join with B on the given condition)" ] }, { "cell_type": "code", "execution_count": 39, "id": "70f3f95b", "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": [ "1 rows affected." ], "text/plain": [ "1 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "1 rows affected." ], "text/plain": [ "1 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "1 rows affected." ], "text/plain": [ "1 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "1 rows affected." ], "text/plain": [ "1 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "1 rows affected." ], "text/plain": [ "1 rows affected." ] }, "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", "
" ], "text/plain": [ "++\n", "||\n", "++\n", "++" ] }, "execution_count": 39, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "drop table a;\n", "drop table b;\n", "create table a(aid int, val varchar(2));\n", "create table b(bid int, val varchar(2));\n", "insert into a values(1,'a');\n", "insert into a values(2,'b');\n", "insert into a values(4,'c');\n", "\n", "insert into b values(2,'d');\n", "insert into b values(4,'e');\n", "insert into b values(4,'g');\n", "insert into b values(5,'f');\n" ] }, { "cell_type": "code", "execution_count": 40, "id": "b8f66edb", "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", "
aidval
1a
2b
4c
" ], "text/plain": [ "+-----+-----+\n", "| aid | val |\n", "+-----+-----+\n", "| 1 | a |\n", "| 2 | b |\n", "| 4 | c |\n", "+-----+-----+" ] }, "execution_count": 40, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "select * from a;" ] }, { "cell_type": "code", "execution_count": 41, "id": "022a2ccb", "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", " \n", " \n", " \n", " \n", " \n", "
bidval
2d
4e
4g
5f
" ], "text/plain": [ "+-----+-----+\n", "| bid | val |\n", "+-----+-----+\n", "| 2 | d |\n", "| 4 | e |\n", "| 4 | g |\n", "| 5 | f |\n", "+-----+-----+" ] }, "execution_count": 41, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "select * from b;" ] }, { "cell_type": "code", "execution_count": 45, "id": "418b5668", "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", "
aidavaluebidbvalue
2b2d
4c4e
4c4g
" ], "text/plain": [ "+-----+--------+-----+--------+\n", "| aid | avalue | bid | bvalue |\n", "+-----+--------+-----+--------+\n", "| 2 | b | 2 | d |\n", "| 4 | c | 4 | e |\n", "| 4 | c | 4 | g |\n", "+-----+--------+-----+--------+" ] }, "execution_count": 45, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select aid, a.val as avalue, bid, b.val as bvalue from a,b where a.aid=b.bid;" ] }, { "cell_type": "code", "execution_count": 46, "id": "8260b14b", "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
aidavaluebidbvalue
1aNoneNone
2b2d
4c4e
4c4g
" ], "text/plain": [ "+-----+--------+------+--------+\n", "| aid | avalue | bid | bvalue |\n", "+-----+--------+------+--------+\n", "| 1 | a | None | None |\n", "| 2 | b | 2 | d |\n", "| 4 | c | 4 | e |\n", "| 4 | c | 4 | g |\n", "+-----+--------+------+--------+" ] }, "execution_count": 46, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select aid, a.val as avalue, bid, b.val as bvalue \n", "from a left outer join b on a.aid=b.bid;" ] }, { "cell_type": "code", "execution_count": 47, "id": "a6617ae3", "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
aidavaluebidbvalue
2b2d
4c4e
4c4g
NoneNone5f
" ], "text/plain": [ "+------+--------+-----+--------+\n", "| aid | avalue | bid | bvalue |\n", "+------+--------+-----+--------+\n", "| 2 | b | 2 | d |\n", "| 4 | c | 4 | e |\n", "| 4 | c | 4 | g |\n", "| None | None | 5 | f |\n", "+------+--------+-----+--------+" ] }, "execution_count": 47, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select aid, a.val as avalue, bid, b.val as bvalue \n", "from a right outer join b on a.aid=b.bid;" ] }, { "cell_type": "code", "execution_count": 48, "id": "66d45113", "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
aidavaluebidbvalue
1aNoneNone
2b2d
4c4e
4c4g
NoneNone5f
" ], "text/plain": [ "+------+--------+------+--------+\n", "| aid | avalue | bid | bvalue |\n", "+------+--------+------+--------+\n", "| 1 | a | None | None |\n", "| 2 | b | 2 | d |\n", "| 4 | c | 4 | e |\n", "| 4 | c | 4 | g |\n", "| None | None | 5 | f |\n", "+------+--------+------+--------+" ] }, "execution_count": 48, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select aid, a.val as avalue, bid, b.val as bvalue \n", "from a full outer join b on a.aid=b.bid;" ] }, { "cell_type": "code", "execution_count": 54, "id": "e5dd08fd", "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", "
bakernumepisodesnumtuples
Rahul44
Imelda01
Jon22
Karen01
Ruby11
Luke01
Dan22
Antony01
Manon01
Briony22
Terry01
Kim-Joy11
" ], "text/plain": [ "+---------+-------------+-----------+\n", "| baker | numepisodes | numtuples |\n", "+---------+-------------+-----------+\n", "| Rahul | 4 | 4 |\n", "| Imelda | 0 | 1 |\n", "| Jon | 2 | 2 |\n", "| Karen | 0 | 1 |\n", "| Ruby | 1 | 1 |\n", "| Luke | 0 | 1 |\n", "| Dan | 2 | 2 |\n", "| Antony | 0 | 1 |\n", "| Manon | 0 | 1 |\n", "| Briony | 2 | 2 |\n", "| Terry | 0 | 1 |\n", "| Kim-Joy | 1 | 1 |\n", "+---------+-------------+-----------+" ] }, "execution_count": 54, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- For each baker, find the number of episodes\n", "-- they were favorite in\n", "-- all bakers who were favorites\n", "-- and all bakers who never appeared in favorite relation!\n", "select\n", " b.baker\n", " , count(episodeid) as numepisodes\n", " , count(*) as numtuples\n", "from\n", " bakers b left join \n", " favorites f\n", " on b.baker = f.baker\n", "group by \n", " b.baker\n", " ;\n" ] }, { "cell_type": "code", "execution_count": 57, "id": "9b45de68", "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", "
bakercount
Rahul2
Imelda0
Jon0
Karen0
Ruby2
Luke0
Dan1
Antony0
Manon1
Briony1
Terry0
Kim-Joy2
" ], "text/plain": [ "+---------+-------+\n", "| baker | count |\n", "+---------+-------+\n", "| Rahul | 2 |\n", "| Imelda | 0 |\n", "| Jon | 0 |\n", "| Karen | 0 |\n", "| Ruby | 2 |\n", "| Luke | 0 |\n", "| Dan | 1 |\n", "| Antony | 0 |\n", "| Manon | 1 |\n", "| Briony | 1 |\n", "| Terry | 0 |\n", "| Kim-Joy | 2 |\n", "+---------+-------+" ] }, "execution_count": 57, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- For each baker, find the number of episodes\n", "-- they won star baker\n", "\n", "select\n", " b.baker\n", " , count(r.episodeid)\n", "from\n", " bakers b\n", " left join results r \n", " on b.baker = r.baker and r.result = 'star baker'\n", "group by\n", " b.baker\n" ] }, { "cell_type": "code", "execution_count": 58, "id": "71a2d973", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "11 rows affected." ], "text/plain": [ "11 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", "
idepisodeid
11
22
33
4None
55
55
66
77
88
99
10None
" ], "text/plain": [ "+----+-----------+\n", "| id | episodeid |\n", "+----+-----------+\n", "| 1 | 1 |\n", "| 2 | 2 |\n", "| 3 | 3 |\n", "| 4 | None |\n", "| 5 | 5 |\n", "| 5 | 5 |\n", "| 6 | 6 |\n", "| 7 | 7 |\n", "| 8 | 8 |\n", "| 9 | 9 |\n", "| 10 | None |\n", "+----+-----------+" ] }, "execution_count": 58, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- Find episodes in which no one was eliminated\n", " \n", "select\n", " e.id, r.episodeid\n", "from\n", " episodes e\n", " left join \n", " results r\n", " on r.episodeid = e.id and r.result = 'eliminated'\n" ] }, { "cell_type": "code", "execution_count": 60, "id": "d7690d16", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "2 rows affected." ], "text/plain": [ "2 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
id
4
10
" ], "text/plain": [ "+----+\n", "| id |\n", "+----+\n", "| 4 |\n", "| 10 |\n", "+----+" ] }, "execution_count": 60, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- Find episodes in which no one was eliminated\n", " \n", "select\n", " e.id\n", "from\n", " episodes e\n", " left join \n", " results r\n", " on r.episodeid = e.id and r.result = 'eliminated'\n", "where\n", " r.episodeid is null" ] }, { "cell_type": "code", "execution_count": null, "id": "ccef671a", "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 }