{ "cells": [ { "cell_type": "markdown", "id": "e24cf73a-3ec5-46f6-9e54-09d5a02623e7", "metadata": {}, "source": [ "## Lecture 11 - SQL is here\n", "\n", "**Announcements**\n", "\n", " - The database server is up, linked from course home page: https://www.cs.rpi.edu/~sibel/csci4380/fall2026/\n", " - Passwords are sent by email\n", " - No class on monday next week, but class on Thursday and Friday\n", " - Expect a new lecture exercise on friday, to be due on monday at midnight\n", " - Expect a mini homework on friday, to be due on friday at midnight (based on solely what we end up covering today)\n", "\n", "\n", "## SQL \n", "\n", "- Industry standard\n", "- SQL is bag oriented - not set\n", "\n", " - Queries may return multiple copies of the same tuple - unless we explicitly it to remove them\n", "\n", "- SQL\n", " - DML: data manipulation language (SELECT FROM WHERE)\n", " - DDL: data definition language\n" ] }, { "cell_type": "code", "execution_count": 7, "id": "63b023be-3afb-4799-a39b-898287220590", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Connecting to 'baking'" ], "text/plain": [ "Connecting to 'baking'" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%sql --section baking\n" ] }, { "cell_type": "code", "execution_count": 30, "id": "eefdbee9-41f9-4366-acd7-c0f10610d7e6", "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", "
ageage2bakerinfo
3030Antony Amourdoux - Banker (30)
3333Briony Williams - Full-time parent (33)
3636Dan Beasley-Harling - Full-time parent (36)
3333Imelda McCarron - Countryside recreation officer (33)
4747Jon Jenkins - Blood courier (47)
6060Karen Wright - In-store sampling assistant (60)
2727Kim-Joy Hewlett - Mental health specialist (27)
3030Luke Thompson - Civil servant/house and techno DJ (30)
2626Manon Lagrève - Software project manager (26)
3030Rahul Mandal - Research scientist (30)
\n", "Truncated to displaylimit of 10." ], "text/plain": [ "+-----+------+--------------------------------------------------------+\n", "| age | age2 | bakerinfo |\n", "+-----+------+--------------------------------------------------------+\n", "| 30 | 30 | Antony Amourdoux - Banker (30) |\n", "| 33 | 33 | Briony Williams - Full-time parent (33) |\n", "| 36 | 36 | Dan Beasley-Harling - Full-time parent (36) |\n", "| 33 | 33 | Imelda McCarron - Countryside recreation officer (33) |\n", "| 47 | 47 | Jon Jenkins - Blood courier (47) |\n", "| 60 | 60 | Karen Wright - In-store sampling assistant (60) |\n", "| 27 | 27 | Kim-Joy Hewlett - Mental health specialist (27) |\n", "| 30 | 30 | Luke Thompson - Civil servant/house and techno DJ (30) |\n", "| 26 | 26 | Manon Lagrève - Software project manager (26) |\n", "| 30 | 30 | Rahul Mandal - Research scientist (30) |\n", "+-----+------+--------------------------------------------------------+\n", "Truncated to displaylimit of 10." ] }, "execution_count": 30, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "select \n", " age\n", " , age as age2\n", " , fullname || ' - ' || occupation || ' (' || age::varchar || ')' as bakerinfo \n", "from bakers ;\n" ] }, { "cell_type": "code", "execution_count": 29, "id": "aedf505e-8657-489e-bfaf-82c1ff1a76b4", "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", "
titlefirstaired
Pastry2018-10-02
Vegan2018-10-09
Danish2018-10-16
Pâtisserie (Semi-final)2018-10-23
Final2018-10-30
" ], "text/plain": [ "+-------------------------+------------+\n", "| title | firstaired |\n", "+-------------------------+------------+\n", "| Pastry | 2018-10-02 |\n", "| Vegan | 2018-10-09 |\n", "| Danish | 2018-10-16 |\n", "| Pâtisserie (Semi-final) | 2018-10-23 |\n", "| Final | 2018-10-30 |\n", "+-------------------------+------------+" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", " \n", "select \n", " title\n", " , firstaired \n", "from \n", " episodes \n", "where \n", " firstaired > '10/1/2018'::date \n", " and viewers7day > 9 ;" ] }, { "cell_type": "markdown", "id": "bab7e406-2738-4464-b9e8-237212807584", "metadata": {}, "source": [ "\n", "\n", "Date (10/08/2026)\n", "\n", "Time ('14:43')\n", "\n", "Timestamp ( Date + Time)\n", "\n", "Interval ( Interval of time unit)\n", "\n", "Date-Date -> Interval of days\n", "\n", "Date + Time = Timestamp\n" ] }, { "cell_type": "markdown", "id": "33752cce-bafc-485b-8187-d755052daf73", "metadata": {}, "source": [ "### Baker database\n", "\n", "Bakers(baker, fullname, age, occupation, hometown) \n", "Episodes(id, title, firstaired, viewers7day, signature, technical, showstopper) \n", "Favorites(episodeid, baker) \n", "Results(episodeid, baker, result) \n", "Showstoppers(episodeid, baker, make) \n", "Signatures(episodeid, baker, make) \n", "Technicals(episodeid, baker, rank) " ] }, { "cell_type": "code", "execution_count": 14, "id": "cb8c7b34-ddbc-4d49-86ee-96062300cd57", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "13 rows affected." ], "text/plain": [ "13 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", "
bakermake
ManonMatcha and White Chocolate Ganache Japanese Selfie
BrionyChocolate Fudge and Salted Caramel Creation
DanDark Chocolate and Raspberry Birthday Cake
KarenStrawberry Fayre Chocolate Cake
LukeRaspberry and White Chocolate Collar Cake
RahulChocolate Orange Layer Cake
RubyChocolate Orange \"Jackson Pollock\" Collar Cake
AntonyChocolate and Orange Adventure Korovai
Kim-JoyMelting Chocolate Galaxy
ManonWhite Chocolate Renaissance Surprise
\n", "Truncated to displaylimit of 10." ], "text/plain": [ "+---------+----------------------------------------------------+\n", "| baker | make |\n", "+---------+----------------------------------------------------+\n", "| Manon | Matcha and White Chocolate Ganache Japanese Selfie |\n", "| Briony | Chocolate Fudge and Salted Caramel Creation |\n", "| Dan | Dark Chocolate and Raspberry Birthday Cake |\n", "| Karen | Strawberry Fayre Chocolate Cake |\n", "| Luke | Raspberry and White Chocolate Collar Cake |\n", "| Rahul | Chocolate Orange Layer Cake |\n", "| Ruby | Chocolate Orange \"Jackson Pollock\" Collar Cake |\n", "| Antony | Chocolate and Orange Adventure Korovai |\n", "| Kim-Joy | Melting Chocolate Galaxy |\n", "| Manon | White Chocolate Renaissance Surprise |\n", "+---------+----------------------------------------------------+\n", "Truncated to displaylimit of 10." ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select baker, make from showstoppers where lower(make) like '%chocolate%';" ] }, { "cell_type": "code", "execution_count": 17, "id": "35f660f2-e3b5-4f0d-9d84-4f5d4aaf7301", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "8 rows affected." ], "text/plain": [ "8 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", "
fullnameage
Jon Jenkins47
Kim-Joy Hewlett27
Rahul Mandal30
Manon Lagrève26
Terry Hartill56
Briony Williams33
Ruby Bhogal29
Dan Beasley-Harling36
" ], "text/plain": [ "+---------------------+-----+\n", "| fullname | age |\n", "+---------------------+-----+\n", "| Jon Jenkins | 47 |\n", "| Kim-Joy Hewlett | 27 |\n", "| Rahul Mandal | 30 |\n", "| Manon Lagrève | 26 |\n", "| Terry Hartill | 56 |\n", "| Briony Williams | 33 |\n", "| Ruby Bhogal | 29 |\n", "| Dan Beasley-Harling | 36 |\n", "+---------------------+-----+" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "select distinct\n", " b.fullname\n", " , b.age\n", "from \n", " bakers b\n", " , technicals t\n", "where\n", " b.baker = t.baker\n", " and t.rank <= 3 ;" ] }, { "cell_type": "code", "execution_count": 18, "id": "03ec34aa-b368-4bd5-bda5-082af44440b2", "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", " \n", " \n", " \n", "
fullnameage
Manon Lagrève26
Rahul Mandal30
" ], "text/plain": [ "+---------------+-----+\n", "| fullname | age |\n", "+---------------+-----+\n", "| Manon Lagrève | 26 |\n", "| Rahul Mandal | 30 |\n", "+---------------+-----+" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "-- Find all bakers who made something with chocolate in the showstopper an episode\n", "-- they won the star baker in and return their name and age.\n", "\n", "select distinct\n", " b.fullname, b.age \n", "from\n", " showstoppers s\n", " , results r\n", " , bakers b\n", "where \n", " s.episodeid = r.episodeid\n", " and s.baker = r.baker --same baker, same episode\n", " and r.baker = b.baker --same baker\n", " and r.result = 'star baker'\n", " and lower(s.make) like '%chocolate%' ;" ] }, { "cell_type": "code", "execution_count": 20, "id": "f82c7fcc-08c1-4803-9530-0de0b1c5b2e6", "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", "
bakerfullnamefirstepsecondep
RahulRahul Mandal23
Kim-JoyKim-Joy Hewlett57
RubyRuby Bhogal89
" ], "text/plain": [ "+---------+-----------------+---------+----------+\n", "| baker | fullname | firstep | secondep |\n", "+---------+-----------------+---------+----------+\n", "| Rahul | Rahul Mandal | 2 | 3 |\n", "| Kim-Joy | Kim-Joy Hewlett | 5 | 7 |\n", "| Ruby | Ruby Bhogal | 8 | 9 |\n", "+---------+-----------------+---------+----------+" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "-- Find a baker who won star baker in two episodes that \n", "-- 1 to 3 episodes apart, return their baker name and the episodes\n", "-- they won in\n", "\n", "select\n", " b.baker\n", " , b.fullname\n", " , r2.episodeid as firstep\n", " , r1.episodeid as secondep\n", "from\n", " results r1\n", " , results r2\n", " , bakers b \n", "where \n", " r1.baker = r2.baker\n", " and r1.episodeid - r2.episodeid <= 3\n", " and r1.episodeid - r2.episodeid > 0\n", " and b.baker = r1.baker \n", " and r1.result = 'star baker'\n", " and r2.result = 'star baker' ;" ] }, { "cell_type": "markdown", "id": "2b29635f-e75c-4018-8be8-0592fada41b9", "metadata": {}, "source": [ "\n", "- NULL value means that there is no value for an attribute\n", " - There is no value\n", " - There is a value but I don't know what it is\n", " - I don't know if there is a value or not.\n", "\n", "result = 'star baker'\n", " - returns true if the value stored is 'star baker'\n", " - returns false if the value stored is a string different than 'star baker'\n", " - return unknown if it is NULL\n", "\n", "\n", "Not (unknown) = unknown\n", "\n", "(unknown) AND (true) = unknown\n", "(unknown) AND (false) = false\n", "\n", "(unknown) OR (true) = true\n", "(unknown) OR (false) = unknown\n", "\n", "\n", "result IS NULL\n" ] }, { "cell_type": "code", "execution_count": 27, "id": "59955a5a-295f-4ea8-aa59-19a465469a68", "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", "
fullnameage
Ruby Bhogal29
" ], "text/plain": [ "+-------------+-----+\n", "| fullname | age |\n", "+-------------+-----+\n", "| Ruby Bhogal | 29 |\n", "+-------------+-----+" ] }, "execution_count": 27, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- Find all bakers who won star baker and ranked in top 3 of the same \n", "-- episode aired in october, and they were favorite two episodes ago.\n", "-- Return their name\n", "\n", "select distinct\n", " b.fullname\n", " , b.age\n", "from \n", " results r\n", " , technicals t\n", " , episodes e\n", " , bakers b\n", " , favorites f\n", "where \n", " r.baker = t.baker\n", " and b.baker = r.baker\n", " and f.baker = b.baker\n", " and e.id = r.episodeid\n", " and e.id = t.episodeid\n", " and f.episodeid = e.id - 2\n", " and t.rank <= 3\n", " and r.result = 'star baker'\n", " and extract(month from e.firstaired) = 10;" ] }, { "cell_type": "code", "execution_count": 26, "id": "dba3c1de-7ae2-4c2d-bcc4-31d11a60666e", "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", "
airmonth
8
9
9
9
9
" ], "text/plain": [ "+----------+\n", "| airmonth |\n", "+----------+\n", "| 8 |\n", "| 9 |\n", "| 9 |\n", "| 9 |\n", "| 9 |\n", "+----------+" ] }, "execution_count": 26, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "--remember query execution order, select comes\n", "-- after where! \n", " \n", "select extract(month from firstaired) as airmonth\n", "from episodes \n", "where extract(month from firstaired) < 10 \n", " order by airmonth;" ] }, { "cell_type": "code", "execution_count": null, "id": "3d75e815-411f-427c-84db-1d96ca385cf2", "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 }