{ "cells": [ { "cell_type": "markdown", "id": "28d39777", "metadata": { "scrolled": true }, "source": [ "## Lecture 11 - SQL Basics\n", "\n", "### Announcements\n", "\n", "- Lecture Exercise 11 to be released later (may be tomorrow)\n", " - Will post when up, Submitty issue to be resolved\n", "- Exam #2 is not yet finished\n", "- Hw #3 to be up soon, but not today \n", "\n", "### Today's lecture\n", "- Recap: select/where/nulls/like/etc.\n", "- Order by/limit\n", "- From clause, joins\n", "- Set/Bag expressions: UNION/INTERSECT/EXCEPT,UNION ALL/INTERSECT ALL/EXCEPT ALL\n", " - set compatibility revisited\n", "- Aggregates\n", "- Group by\n", "- Having" ] }, { "cell_type": "code", "execution_count": 1, "id": "300bfe36", "metadata": {}, "outputs": [], "source": [ "load_ext sql" ] }, { "cell_type": "markdown", "id": "b388be31", "metadata": {}, "source": [ "sql" ] }, { "cell_type": "code", "execution_count": 2, "id": "ba21432e", "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": "811f589a", "metadata": {}, "source": [ "- SELECT like \"projection\" in relational algebra\n", "- WHERE like \"selection\" in relational algebra\n", "- FROM " ] }, { "cell_type": "code", "execution_count": 9, "id": "184079a7", "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", "
bakerepisodeidageoccupation
Manon126Software project manager
Kim-Joy527Mental health specialist
Kim-Joy727Mental health specialist
Ruby829Project manager
Ruby929Project manager
" ], "text/plain": [ "+---------+-----------+-----+--------------------------+\n", "| baker | episodeid | age | occupation |\n", "+---------+-----------+-----+--------------------------+\n", "| Manon | 1 | 26 | Software project manager |\n", "| Kim-Joy | 5 | 27 | Mental health specialist |\n", "| Kim-Joy | 7 | 27 | Mental health specialist |\n", "| Ruby | 8 | 29 | Project manager |\n", "| Ruby | 9 | 29 | Project manager |\n", "+---------+-----------+-----+--------------------------+" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "-- bakers who are younger than 30 and won star baker\n", "select \n", " b.baker, r.episodeid, b.age, b.occupation\n", "from \n", " results r, bakers b \n", "where \n", " r.baker = b.baker\n", " and b.age < 30\n", " and r.result = 'star baker';" ] }, { "cell_type": "code", "execution_count": 12, "id": "80a5c428", "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", " \n", " \n", " \n", " \n", " \n", " \n", "
bakerepisodeidageshowstoppersignature
Manon126Matcha and White Chocolate Ganache Japanese SelfieHazelnut Cornish Shortbread
Kim-Joy527Christmas Spiced Ice ChandelierStem Ginger Cake with Poached Pears
Kim-Joy727Lavender & Lemon Fox CakeBroccoli & Tomato Quiches and Mascarpone Squirrel Tartlets
Ruby829Sister KagekonePost-Gym Smørrebrød
Ruby929Parisian Un Bouquet de Fleurs Window DisplayPick Your Own Madeleines
" ], "text/plain": [ "+---------+-----------+-----+----------------------------------------------------+------------------------------------------------------------+\n", "| baker | episodeid | age | showstopper | signature |\n", "+---------+-----------+-----+----------------------------------------------------+------------------------------------------------------------+\n", "| Manon | 1 | 26 | Matcha and White Chocolate Ganache Japanese Selfie | Hazelnut Cornish Shortbread |\n", "| Kim-Joy | 5 | 27 | Christmas Spiced Ice Chandelier | Stem Ginger Cake with Poached Pears |\n", "| Kim-Joy | 7 | 27 | Lavender & Lemon Fox Cake | Broccoli & Tomato Quiches and Mascarpone Squirrel Tartlets |\n", "| Ruby | 8 | 29 | Sister Kagekone | Post-Gym Smørrebrød |\n", "| Ruby | 9 | 29 | Parisian Un Bouquet de Fleurs Window Display | Pick Your Own Madeleines |\n", "+---------+-----------+-----+----------------------------------------------------+------------------------------------------------------------+" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "-- bakers who are younger than 30 and won star baker, and the make\n", "-- for their showstopper\n", "select \n", " b.baker, r.episodeid, b.age, s.make as showstopper, si.make as signature\n", "from \n", " results r, bakers b, showstoppers s, signatures si\n", "where \n", " r.baker = b.baker\n", " and r.episodeid = s.episodeid\n", " and r.baker = s.baker\n", " and r.episodeid = si.episodeid\n", " and r.baker = si.baker\n", " and b.age < 30\n", " and r.result = 'star baker';" ] }, { "cell_type": "code", "execution_count": 16, "id": "94b122c3", "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", "
baker
Dan
Briony
" ], "text/plain": [ "+--------+\n", "| baker |\n", "+--------+\n", "| Dan |\n", "| Briony |\n", "+--------+" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "-- find bakers who won star baker and were eliminated in the next 1-3 episodes\n", "\n", "select\n", " r1.baker\n", "from\n", " results r1, results r2\n", "where\n", " r1.baker = r2.baker\n", " and r2.episodeid - r1.episodeid <= 3\n", " and r1.result = 'star baker'\n", " and r2.result = 'eliminated' ;" ] }, { "cell_type": "code", "execution_count": 17, "id": "218314f7", "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
Kim-Joy
Ruby
" ], "text/plain": [ "+---------+\n", "| baker |\n", "+---------+\n", "| Rahul |\n", "| Kim-Joy |\n", "| Ruby |\n", "+---------+" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "-- find bakers who won star baker at least twice\n", "\n", "select distinct\n", " r1.baker\n", "from\n", " results r1, results r2\n", "where\n", " r1.baker = r2.baker\n", " and r1.episodeid > r2.episodeid\n", " and r1.result = 'star baker'\n", " and r2.result = 'star baker' ;" ] }, { "cell_type": "markdown", "id": "f4b3dede", "metadata": {}, "source": [ "### Set operations\n", "\n", "UNION \n", "INTERSECT \n", "EXCEPT \n", "\n", "\n", "SELECT ... FROM ... WHERE \n", "UNION \n", "SELECT ... FROM ... WHERE \n", "UNION \n", "... \n", "UNION \n", "SELECT ... FROM ... WHERE \n", "\n", "\n", "SELECT ... FROM ... WHERE \n", "EXCEPT. -- set difference \n", "SELECT ... FROM ... WHERE \n", "\n", "![x](lecture11_figures/page1.png)" ] }, { "cell_type": "code", "execution_count": 19, "id": "ec889fcc", "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", "
baker
Imelda
Jon
Karen
Luke
Antony
Terry
" ], "text/plain": [ "+--------+\n", "| baker |\n", "+--------+\n", "| Imelda |\n", "| Jon |\n", "| Karen |\n", "| Luke |\n", "| Antony |\n", "| Terry |\n", "+--------+" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "-- bakers who were never star baker\n", "SELECT baker FROM bakers\n", "EXCEPT\n", "SELECT baker FROM results WHERE result = 'star baker';" ] }, { "cell_type": "code", "execution_count": 23, "id": "47c68961", "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": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT baker FROM results WHERE result = 'star baker'\n", "EXCEPT \n", "SELECT baker FROM results WHERE result = 'eliminated';" ] }, { "cell_type": "code", "execution_count": 28, "id": "240418e5", "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", "
bakerage
Dan36
Rahul30
Ruby29
Manon26
Kim-Joy27
" ], "text/plain": [ "+---------+-----+\n", "| baker | age |\n", "+---------+-----+\n", "| Dan | 36 |\n", "| Rahul | 30 |\n", "| Ruby | 29 |\n", "| Manon | 26 |\n", "| Kim-Joy | 27 |\n", "+---------+-----+" ] }, "execution_count": 28, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "-- relations must still be set compatible, same number of attributes\n", "-- and same data type (or can be cast to be same!)\n", "\n", "select baker, age \n", "from bakers \n", "where age < 30\n", "\n", "union\n", "\n", "select r.baker as starbaker, b.age as ageofbaker\n", "from results r, bakers b \n", "where r.baker = b.baker and r.result = 'star baker' and r.episodeid < 5 ;" ] }, { "cell_type": "code", "execution_count": 34, "id": "426a057b", "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
ManonManon Lagrève26Software project managerLondon
Kim-JoyKim-Joy Hewlett27Mental health specialistLeeds
RubyRuby Bhogal29Project managerLondon
" ], "text/plain": [ "+---------+-----------------+-----+--------------------------+----------+\n", "| baker | fullname | age | occupation | hometown |\n", "+---------+-----------------+-----+--------------------------+----------+\n", "| Manon | Manon Lagrève | 26 | Software project manager | London |\n", "| Kim-Joy | Kim-Joy Hewlett | 27 | Mental health specialist | Leeds |\n", "| Ruby | Ruby Bhogal | 29 | Project manager | London |\n", "+---------+-----------------+-----+--------------------------+----------+" ] }, "execution_count": 34, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select * from bakers where age < 50 \n", "order by age asc, baker desc\n", "limit 3;" ] }, { "cell_type": "code", "execution_count": 39, "id": "780e0b61", "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", "
contestantage
Dan36
Kim-Joy27
Manon26
Rahul30
Ruby29
" ], "text/plain": [ "+------------+-----+\n", "| contestant | age |\n", "+------------+-----+\n", "| Dan | 36 |\n", "| Kim-Joy | 27 |\n", "| Manon | 26 |\n", "| Rahul | 30 |\n", "| Ruby | 29 |\n", "+------------+-----+" ] }, "execution_count": 39, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "-- relations must still be set compatible, same number of attributes\n", "-- and same data type (or can be cast to be same!)\n", "\n", "select baker as contestant, age \n", "from bakers \n", "where age < 30\n", "\n", "union\n", "\n", "select r.baker as starbaker, b.age as ageofbaker\n", "from results r, bakers b \n", "where r.baker = b.baker and r.result = 'star baker' and r.episodeid < 5 \n", "\n", "order by contestant;" ] }, { "cell_type": "markdown", "id": "5090385c", "metadata": {}, "source": [ "### Bag operators\n", "\n", "UNION ALL \n", "INTERSECT ALL \n", "EXCEPT ALL \n", "\n", "\n", "SELECT ... FROM ... WHERE \n", "UNION ALL \n", "SELECT ... FROM ... WHERE \n", "\n", "\n", "R union all S = {return m+n copies of t if t happens m times in R and n times in S}\n", "\n", "R intersect all S = {return min(m,n) copies of t if t happens m times in R and n times in S}\n", "\n", "R except all S = {return max(0,m-n) copies of t if t happens m times in R and n times in S}" ] }, { "cell_type": "code", "execution_count": 41, "id": "85ec9a29", "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", "
starbakerageofbaker
Manon26
Rahul30
Rahul30
Dan36
" ], "text/plain": [ "+-----------+------------+\n", "| starbaker | ageofbaker |\n", "+-----------+------------+\n", "| Manon | 26 |\n", "| Rahul | 30 |\n", "| Rahul | 30 |\n", "| Dan | 36 |\n", "+-----------+------------+" ] }, "execution_count": 41, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select r.baker as starbaker, b.age as ageofbaker\n", "from results r, bakers b \n", "where r.baker = b.baker and r.result = 'star baker' and r.episodeid < 5 \n" ] }, { "cell_type": "code", "execution_count": 43, "id": "c9ea8a65", "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", "
contestantage
Kim-Joy27
Manon26
Ruby29
" ], "text/plain": [ "+------------+-----+\n", "| contestant | age |\n", "+------------+-----+\n", "| Kim-Joy | 27 |\n", "| Manon | 26 |\n", "| Ruby | 29 |\n", "+------------+-----+" ] }, "execution_count": 43, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "select baker as contestant, age \n", "from bakers \n", "where age < 30;\n" ] }, { "cell_type": "code", "execution_count": 44, "id": "c5de0a44", "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
contestantage
Kim-Joy27
Manon26
Ruby29
Manon26
Rahul30
Rahul30
Dan36
" ], "text/plain": [ "+------------+-----+\n", "| contestant | age |\n", "+------------+-----+\n", "| Kim-Joy | 27 |\n", "| Manon | 26 |\n", "| Ruby | 29 |\n", "| Manon | 26 |\n", "| Rahul | 30 |\n", "| Rahul | 30 |\n", "| Dan | 36 |\n", "+------------+-----+" ] }, "execution_count": 44, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "select baker as contestant, age \n", "from bakers \n", "where age < 30\n", "\n", "union all\n", "\n", "select r.baker as starbaker, b.age as ageofbaker\n", "from results r, bakers b \n", "where r.baker = b.baker and r.result = 'star baker' and r.episodeid < 5 ;\n" ] }, { "cell_type": "code", "execution_count": 53, "id": "812ef80f", "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", "
baker
Rahul
Rahul
Dan
Manon
" ], "text/plain": [ "+-------+\n", "| baker |\n", "+-------+\n", "| Rahul |\n", "| Rahul |\n", "| Dan |\n", "| Manon |\n", "+-------+" ] }, "execution_count": 53, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "select baker\n", "from showstoppers\n", "where lower(make) like '%chocolate%'\n", "intersect all\n", "select r.baker \n", "from results r\n", "where r.result = 'star baker' and r.episodeid < 5 ;\n" ] }, { "cell_type": "code", "execution_count": 55, "id": "c5f98480", "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", "
baker
Briony
Ruby
Ruby
Ruby
Luke
Karen
Antony
Kim-Joy
Manon
" ], "text/plain": [ "+---------+\n", "| baker |\n", "+---------+\n", "| Briony |\n", "| Ruby |\n", "| Ruby |\n", "| Ruby |\n", "| Luke |\n", "| Karen |\n", "| Antony |\n", "| Kim-Joy |\n", "| Manon |\n", "+---------+" ] }, "execution_count": 55, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "select baker\n", "from showstoppers\n", "where lower(make) like '%chocolate%'\n", "except all\n", "select r.baker \n", "from results r\n", "where r.result = 'star baker' and r.episodeid < 5 ;\n" ] }, { "cell_type": "markdown", "id": "7ee77c63", "metadata": {}, "source": [ "### Aggregates\n", "\n", "- Apply to the set of tuples, not a single tuple!\n", "\n", "- count/max/min/avg/....\n", "\n", "\n", "SELECT aggregates from the relation produced below \n", " \n" ] }, { "cell_type": "code", "execution_count": 57, "id": "1e761d51", "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
Kim-JoyKim-Joy Hewlett27Mental health specialistLeeds
LukeLuke Thompson30Civil servant/house and techno DJSheffield
ManonManon Lagrève26Software project managerLondon
RahulRahul Mandal30Research scientistRotherham
RubyRuby Bhogal29Project managerLondon
" ], "text/plain": [ "+---------+------------------+-----+-----------------------------------+-----------+\n", "| baker | fullname | age | occupation | hometown |\n", "+---------+------------------+-----+-----------------------------------+-----------+\n", "| Antony | Antony Amourdoux | 30 | Banker | London |\n", "| Kim-Joy | Kim-Joy Hewlett | 27 | Mental health specialist | Leeds |\n", "| Luke | Luke Thompson | 30 | Civil servant/house and techno DJ | Sheffield |\n", "| Manon | Manon Lagrève | 26 | Software project manager | London |\n", "| Rahul | Rahul Mandal | 30 | Research scientist | Rotherham |\n", "| Ruby | Ruby Bhogal | 29 | Project manager | London |\n", "+---------+------------------+-----+-----------------------------------+-----------+" ] }, "execution_count": 57, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "select * from bakers where age <= 30;" ] }, { "cell_type": "code", "execution_count": 62, "id": "60e444cb", "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", "
numbakersminagemaxage
62630
" ], "text/plain": [ "+-----------+--------+--------+\n", "| numbakers | minage | maxage |\n", "+-----------+--------+--------+\n", "| 6 | 26 | 30 |\n", "+-----------+--------+--------+" ] }, "execution_count": 62, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "select count(*) as numbakers , min(age) as minage, max(age) as maxage\n", "from bakers where age <= 30;" ] }, { "cell_type": "code", "execution_count": 65, "id": "3877306e", "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.4
" ], "text/plain": [ "+------+\n", "| avg |\n", "+------+\n", "| 36.4 |\n", "+------+" ] }, "execution_count": 65, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select avg(age)::numeric(4,1)\n", "from bakers;\n" ] }, { "cell_type": "code", "execution_count": 66, "id": "3882d407", "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
44.2
" ], "text/plain": [ "+------+\n", "| avg |\n", "+------+\n", "| 44.2 |\n", "+------+" ] }, "execution_count": 66, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select avg(age)::numeric(4,1)\n", "from bakers\n", "where age>30;\n" ] }, { "cell_type": "code", "execution_count": 67, "id": "a43862f7", "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", " \n", " \n", "
firstepisodedatefinalepisodedateavgviewershighestviewers
2018-08-282018-10-309.410.34
" ], "text/plain": [ "+------------------+------------------+------------+----------------+\n", "| firstepisodedate | finalepisodedate | avgviewers | highestviewers |\n", "+------------------+------------------+------------+----------------+\n", "| 2018-08-28 | 2018-10-30 | 9.4 | 10.34 |\n", "+------------------+------------------+------------+----------------+" ] }, "execution_count": 67, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select min(firstaired) as firstepisodedate\n", " ,max(firstaired) as finalepisodedate\n", " , avg(viewers7day)::numeric(4,1) as avgviewers\n", " , max(viewers7day) as highestviewers\n", "from episodes;" ] }, { "cell_type": "code", "execution_count": 70, "id": "4041634b", "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
44.2
" ], "text/plain": [ "+------+\n", "| avg |\n", "+------+\n", "| 44.2 |\n", "+------+" ] }, "execution_count": 70, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select avg(age)::numeric(4,1)\n", "from bakers\n", "where age>30;\n" ] }, { "cell_type": "markdown", "id": "1b040f31", "metadata": {}, "source": [ "![x](lecture11_figures/page2.png)" ] }, { "cell_type": "code", "execution_count": 71, "id": "9a5df345", "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", "
baker
Briony
Dan
Kim-Joy
Kim-Joy
Manon
Rahul
Rahul
Ruby
Ruby
" ], "text/plain": [ "+---------+\n", "| baker |\n", "+---------+\n", "| Briony |\n", "| Dan |\n", "| Kim-Joy |\n", "| Kim-Joy |\n", "| Manon |\n", "| Rahul |\n", "| Rahul |\n", "| Ruby |\n", "| Ruby |\n", "+---------+" ] }, "execution_count": 71, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select\n", " r.baker\n", "from\n", " results r\n", "where\n", " r.result = 'star baker'\n", "order by \n", " r.baker asc ;" ] }, { "cell_type": "code", "execution_count": 75, "id": "98263a91", "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", "
bakernumwinsfirsteplastep
Briony166
Dan144
Kim-Joy257
Manon111
Rahul223
Ruby289
" ], "text/plain": [ "+---------+---------+---------+--------+\n", "| baker | numwins | firstep | lastep |\n", "+---------+---------+---------+--------+\n", "| Briony | 1 | 6 | 6 |\n", "| Dan | 1 | 4 | 4 |\n", "| Kim-Joy | 2 | 5 | 7 |\n", "| Manon | 1 | 1 | 1 |\n", "| Rahul | 2 | 2 | 3 |\n", "| Ruby | 2 | 8 | 9 |\n", "+---------+---------+---------+--------+" ] }, "execution_count": 75, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- For each baker, return the number of wins and the episode number of their\n", "-- first/last win\n", "\n", "select\n", " r.baker\n", " , count(*) as numwins\n", " , min(episodeid) as firstep\n", " , max(episodeid) as lastep\n", "from\n", " results r\n", "where\n", " r.result = 'star baker'\n", "group by \n", " r.baker\n", "order by \n", " r.baker asc ;" ] }, { "cell_type": "code", "execution_count": 76, "id": "cd8356f2", "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", "
episodeidbaker
1Briony
2Jon
2Dan
3Dan
4Rahul
4Jon
5Rahul
6Rahul
6Ruby
7Rahul
8Briony
9Kim-Joy
" ], "text/plain": [ "+-----------+---------+\n", "| episodeid | baker |\n", "+-----------+---------+\n", "| 1 | Briony |\n", "| 2 | Jon |\n", "| 2 | Dan |\n", "| 3 | Dan |\n", "| 4 | Rahul |\n", "| 4 | Jon |\n", "| 5 | Rahul |\n", "| 6 | Rahul |\n", "| 6 | Ruby |\n", "| 7 | Rahul |\n", "| 8 | Briony |\n", "| 9 | Kim-Joy |\n", "+-----------+---------+" ] }, "execution_count": 76, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "select * from favorites ;" ] }, { "cell_type": "code", "execution_count": 77, "id": "56b0d14f", "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", "
bakernumfavorite
Rahul4
Ruby1
Dan2
Briony2
Jon2
Kim-Joy1
" ], "text/plain": [ "+---------+-------------+\n", "| baker | numfavorite |\n", "+---------+-------------+\n", "| Rahul | 4 |\n", "| Ruby | 1 |\n", "| Dan | 2 |\n", "| Briony | 2 |\n", "| Jon | 2 |\n", "| Kim-Joy | 1 |\n", "+---------+-------------+" ] }, "execution_count": 77, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select\n", " baker\n", " , count(*) as numfavorite\n", "from\n", " favorites\n", "group by\n", " baker;\n" ] }, { "cell_type": "code", "execution_count": 80, "id": "c1195202", "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
episodeidnumbakersminmax
912727
313636
513030
423047
622930
223647
713030
113333
813333
" ], "text/plain": [ "+-----------+-----------+-----+-----+\n", "| episodeid | numbakers | min | max |\n", "+-----------+-----------+-----+-----+\n", "| 9 | 1 | 27 | 27 |\n", "| 3 | 1 | 36 | 36 |\n", "| 5 | 1 | 30 | 30 |\n", "| 4 | 2 | 30 | 47 |\n", "| 6 | 2 | 29 | 30 |\n", "| 2 | 2 | 36 | 47 |\n", "| 7 | 1 | 30 | 30 |\n", "| 1 | 1 | 33 | 33 |\n", "| 8 | 1 | 33 | 33 |\n", "+-----------+-----------+-----+-----+" ] }, "execution_count": 80, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select\n", " f.episodeid\n", " , count(*) as numbakers\n", " , min(b.age)\n", " , max(b.age)\n", "from\n", " favorites f\n", " , bakers b\n", "where\n", " f.baker = b.baker\n", "group by\n", " f.episodeid;\n" ] }, { "cell_type": "code", "execution_count": 81, "id": "a04ba617", "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", "
numbakersminmax
12727
13636
13030
23047
22930
23647
13030
13333
13333
" ], "text/plain": [ "+-----------+-----+-----+\n", "| numbakers | min | max |\n", "+-----------+-----+-----+\n", "| 1 | 27 | 27 |\n", "| 1 | 36 | 36 |\n", "| 1 | 30 | 30 |\n", "| 2 | 30 | 47 |\n", "| 2 | 29 | 30 |\n", "| 2 | 36 | 47 |\n", "| 1 | 30 | 30 |\n", "| 1 | 33 | 33 |\n", "| 1 | 33 | 33 |\n", "+-----------+-----+-----+" ] }, "execution_count": 81, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select\n", "count(*) as numbakers\n", " , min(b.age)\n", " , max(b.age)\n", "from\n", " favorites f\n", " , bakers b\n", "where\n", " f.baker = b.baker\n", "group by\n", " f.episodeid;\n" ] }, { "cell_type": "code", "execution_count": null, "id": "5a766a61", "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 }