{ "cells": [ { "cell_type": "markdown", "id": "9b3e34d2", "metadata": { "scrolled": true }, "source": [ "## Lecture 10: ER wrap up and SQL\n", "\n", "### Announcements\n", "\n", "- No new hw or lecture exercise today, lecture exercises will resume on monday\n", "- Grading to be completed sometime next week, we will grade hw#2 after that\n", "- Now is the time start installing a db on your machine if you want to get the experience\n", "\n", "\n", "### Topics for today\n", "\n", "- Hierarchies in ER Diagrams (students, faculty, staff or car,truck,motorcycle)\n", "- Introduction to SQL\n", " - DBMS install\n", " - standards\n", " - bag standards\n", " - control flow\n", " - case insensitive, special characters, comments\n", " - keywords!\n", " - SELECT, distinct, attributes/functions\n", " - WHERE like, null values\n", " - functions (strongly typed): string, numerical, math, utilities\n", " - date math\n", " \n", "![x](lecture10_figures/page1.png)\n", "![x](lecture10_figures/page2.png)\n", "![x](lecture10_figures/page3.png)\n", "![x](lecture10_figures/page4.png)" ] }, { "cell_type": "markdown", "id": "711a9a48", "metadata": {}, "source": [ "## SQL\n", "\n", "Standard Query Language - Industry Standard\n", "\n", "We will use V17 in this class\n", "\n", "\n", "SQL Implements bag semantics, i.e. multiple copies of the same tuple is allowed in a table unless there is a primary key.\n", "\n", "SQL is case insensitive\n", "\n", "SELECT ... FROM ... WHERE .... ; \n", "--semicolon marks the end of a query\n" ] }, { "cell_type": "code", "execution_count": 1, "id": "af089638", "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": [ "Connecting to 'baking'" ], "text/plain": [ "Connecting to 'baking'" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%sql --section baking" ] }, { "cell_type": "code", "execution_count": 18, "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": [ "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
bakerbaker_1upnameageseasonbakerwithoc?column?
AntonyAntonyANTONY305Antony BankerAntony30
BrionyBrionyBRIONY335Briony Full-time parentBriony33
DanDanDAN365Dan Full-time parentDan36
ImeldaImeldaIMELDA335Imelda Countryside recreation officerImelda33
JonJonJON475Jon Blood courierJon47
KarenKarenKAREN605Karen In-store sampling assistantKaren60
Kim-JoyKim-JoyKIM-JOY275Kim-Joy Mental health specialistKim-Joy27
LukeLukeLUKE305Luke Civil servant/house and techno DJLuke30
ManonManonMANON265Manon Software project managerManon26
RahulRahulRAHUL305Rahul Research scientistRahul30
\n", "Truncated to displaylimit of 10." ], "text/plain": [ "+---------+---------+---------+-----+--------+----------------------------------------+-----------+\n", "| baker | baker_1 | upname | age | season | bakerwithoc | ?column? |\n", "+---------+---------+---------+-----+--------+----------------------------------------+-----------+\n", "| Antony | Antony | ANTONY | 30 | 5 | Antony Banker | Antony30 |\n", "| Briony | Briony | BRIONY | 33 | 5 | Briony Full-time parent | Briony33 |\n", "| Dan | Dan | DAN | 36 | 5 | Dan Full-time parent | Dan36 |\n", "| Imelda | Imelda | IMELDA | 33 | 5 | Imelda Countryside recreation officer | Imelda33 |\n", "| Jon | Jon | JON | 47 | 5 | Jon Blood courier | Jon47 |\n", "| Karen | Karen | KAREN | 60 | 5 | Karen In-store sampling assistant | Karen60 |\n", "| Kim-Joy | Kim-Joy | KIM-JOY | 27 | 5 | Kim-Joy Mental health specialist | Kim-Joy27 |\n", "| Luke | Luke | LUKE | 30 | 5 | Luke Civil servant/house and techno DJ | Luke30 |\n", "| Manon | Manon | MANON | 26 | 5 | Manon Software project manager | Manon26 |\n", "| Rahul | Rahul | RAHUL | 30 | 5 | Rahul Research scientist | Rahul30 |\n", "+---------+---------+---------+-----+--------+----------------------------------------+-----------+\n", "Truncated to displaylimit of 10." ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT \n", " baker\n", " , baker\n", " , upper(baker) as upname\n", " , age\n", " , 5 as season\n", " ,baker ||' ' || occupation as bakerwithoc\n", " , baker || cast(age as varchar) || age::varchar\n", "FROM \n", " bakers;" ] }, { "cell_type": "code", "execution_count": 21, "id": "d78db4fe", "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", "
hometown
London
Leeds
Rotherham
County Tyrone
Sheffield
Bristol
" ], "text/plain": [ "+---------------+\n", "| hometown |\n", "+---------------+\n", "| London |\n", "| Leeds |\n", "| Rotherham |\n", "| County Tyrone |\n", "| Sheffield |\n", "| Bristol |\n", "+---------------+" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT DISTINCT -- remove the copies in the result\n", " hometown\n", "FROM\n", " bakers\n", "WHERE\n", " age < 35 ;" ] }, { "cell_type": "code", "execution_count": 28, "id": "10fd5e52", "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", " \n", " \n", " \n", " \n", " \n", "
bakerfullnameageoccupationhometown
AntonyAntony Amourdoux30BankerLondon
DanDan Beasley-Harling36Full-time parentLondon
ManonManon Lagrève26Software project managerLondon
RubyRuby Bhogal29Project managerLondon
" ], "text/plain": [ "+--------+---------------------+-----+--------------------------+----------+\n", "| baker | fullname | age | occupation | hometown |\n", "+--------+---------------------+-----+--------------------------+----------+\n", "| Antony | Antony Amourdoux | 30 | Banker | London |\n", "| Dan | Dan Beasley-Harling | 36 | Full-time parent | London |\n", "| Manon | Manon Lagrève | 26 | Software project manager | London |\n", "| Ruby | Ruby Bhogal | 29 | Project manager | London |\n", "+--------+---------------------+-----+--------------------------+----------+" ] }, "execution_count": 28, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT \n", " * \n", "FROM \n", " bakers \n", "WHERE \n", " hometown = 'London';" ] }, { "cell_type": "code", "execution_count": 37, "id": "299766d2", "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
bakerfullnameageoccupationhometown
BrionyBriony Williams33Full-time parentBristol
DanDan Beasley-Harling36Full-time parentLondon
" ], "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", "+--------+---------------------+-----+------------------+----------+" ] }, "execution_count": 37, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT\n", " *\n", "FROM\n", " bakers\n", "WHERE\n", " lower(occupation) LIKE '%full%'\n", ";" ] }, { "cell_type": "code", "execution_count": 43, "id": "377e9182", "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", "
bakerepisodeid
Luke1
Terry1
Briony5
Dan5
Jon5
Karen5
Kim-Joy5
Manon5
Rahul5
Ruby5
\n", "Truncated to displaylimit of 10." ], "text/plain": [ "+---------+-----------+\n", "| baker | episodeid |\n", "+---------+-----------+\n", "| Luke | 1 |\n", "| Terry | 1 |\n", "| Briony | 5 |\n", "| Dan | 5 |\n", "| Jon | 5 |\n", "| Karen | 5 |\n", "| Kim-Joy | 5 |\n", "| Manon | 5 |\n", "| Rahul | 5 |\n", "| Ruby | 5 |\n", "+---------+-----------+\n", "Truncated to displaylimit of 10." ] }, "execution_count": 43, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT\n", " baker, episodeid\n", "FROM\n", " signatures\n", "WHERE\n", " lower(make) like '%ginger%'\n", ";" ] }, { "cell_type": "code", "execution_count": 46, "id": "0128925f", "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idtitlefirstairedviewers7daysignaturetechnicalshowstopper
5Spice2018-09-258.67Ginger Cake12 Ma'amoulSpiced Biscuit Chandelier
6Pastry2018-10-029.312 Samosas6 Puits D'amourShaped Banquet Pie
7Vegan2018-10-099.548 Savoury Vegan TartletsVegan Tropical Fruit PavlovaVegan Celebratory Cake
8Danish2018-10-169.692 Smørrebrød14 ÆbleskiverKagemand/Kagekone
9Pâtisserie (Semi-final)2018-10-239.524 MadeleinesTorta SetteveliParisian Pâtisserie Window
10Final2018-10-3010.3412 Iced Doughnuts6 Campfire Pita BreadsLandscape Dessert
" ], "text/plain": [ "+----+-------------------------+------------+-------------+--------------------------+------------------------------+----------------------------+\n", "| id | title | firstaired | viewers7day | signature | technical | showstopper |\n", "+----+-------------------------+------------+-------------+--------------------------+------------------------------+----------------------------+\n", "| 5 | Spice | 2018-09-25 | 8.67 | Ginger Cake | 12 Ma'amoul | Spiced Biscuit Chandelier |\n", "| 6 | Pastry | 2018-10-02 | 9.3 | 12 Samosas | 6 Puits D'amour | Shaped Banquet Pie |\n", "| 7 | Vegan | 2018-10-09 | 9.54 | 8 Savoury Vegan Tartlets | Vegan Tropical Fruit Pavlova | Vegan Celebratory Cake |\n", "| 8 | Danish | 2018-10-16 | 9.69 | 2 Smørrebrød | 14 Æbleskiver | Kagemand/Kagekone |\n", "| 9 | Pâtisserie (Semi-final) | 2018-10-23 | 9.5 | 24 Madeleines | Torta Setteveli | Parisian Pâtisserie Window |\n", "| 10 | Final | 2018-10-30 | 10.34 | 12 Iced Doughnuts | 6 Campfire Pita Breads | Landscape Dessert |\n", "+----+-------------------------+------------+-------------+--------------------------+------------------------------+----------------------------+" ] }, "execution_count": 46, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select \n", " * \n", "from \n", " episodes\n", "where \n", " firstaired > '2018-09-18'::date;" ] }, { "cell_type": "code", "execution_count": 48, "id": "c28a713e", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "10 rows affected." ], "text/plain": [ "10 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", "
idtitlefirstaired
1Biscuits2018-08-28
2Cakes2018-09-04
3Bread2018-09-11
4Desserts2018-09-18
5Spice2018-09-25
6Pastry2018-10-02
7Vegan2018-10-09
8Danish2018-10-16
9Pâtisserie (Semi-final)2018-10-23
10Final2018-10-30
\n", "Truncated to displaylimit of 10." ], "text/plain": [ "+----+-------------------------+------------+\n", "| id | title | firstaired |\n", "+----+-------------------------+------------+\n", "| 1 | Biscuits | 2018-08-28 |\n", "| 2 | Cakes | 2018-09-04 |\n", "| 3 | Bread | 2018-09-11 |\n", "| 4 | Desserts | 2018-09-18 |\n", "| 5 | Spice | 2018-09-25 |\n", "| 6 | Pastry | 2018-10-02 |\n", "| 7 | Vegan | 2018-10-09 |\n", "| 8 | Danish | 2018-10-16 |\n", "| 9 | Pâtisserie (Semi-final) | 2018-10-23 |\n", "| 10 | Final | 2018-10-30 |\n", "+----+-------------------------+------------+\n", "Truncated to displaylimit of 10." ] }, "execution_count": 48, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select \n", " id\n", " , title\n", " , firstaired\n", " , '2018-10-30'::date-firstaired as numdays\n", "from \n", " episodes \n", ";" ] }, { "cell_type": "markdown", "id": "2adb2f9b", "metadata": {}, "source": [ "Date operations:\n", "\n", "\n", "date - date = days\n", "\n", "date + time = timestamp" ] }, { "cell_type": "code", "execution_count": 52, "id": "25e2aea7", "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": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
time
13:32:00
" ], "text/plain": [ "+----------+\n", "| time |\n", "+----------+\n", "| 13:32:00 |\n", "+----------+" ] }, "execution_count": 52, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select '2025-10-02'::date ;\n", " \n", "select '13:32'::time;" ] }, { "cell_type": "code", "execution_count": 53, "id": "d6a078b6", "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", "
?column?
2025-10-02 13:32:00
" ], "text/plain": [ "+---------------------+\n", "| ?column? |\n", "+---------------------+\n", "| 2025-10-02 13:32:00 |\n", "+---------------------+" ] }, "execution_count": 53, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select '2025-10-02'::date + '13:32'::time;" ] }, { "cell_type": "code", "execution_count": 57, "id": "72405d8d", "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", "
?column?
0:18:00
" ], "text/plain": [ "+----------+\n", "| ?column? |\n", "+----------+\n", "| 0:18:00 |\n", "+----------+" ] }, "execution_count": 57, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select ('13:50'::time - '13:32'::time);\n", " " ] }, { "cell_type": "code", "execution_count": 58, "id": "c212d511", "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", "
now
2025-10-02 13:34:56.655545-04:00
" ], "text/plain": [ "+----------------------------------+\n", "| now |\n", "+----------------------------------+\n", "| 2025-10-02 13:34:56.655545-04:00 |\n", "+----------------------------------+" ] }, "execution_count": 58, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select now();" ] }, { "cell_type": "code", "execution_count": 59, "id": "5d1b895b", "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", "
random
0.08370699853698604
" ], "text/plain": [ "+---------------------+\n", "| random |\n", "+---------------------+\n", "| 0.08370699853698604 |\n", "+---------------------+" ] }, "execution_count": 59, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select random();" ] }, { "cell_type": "markdown", "id": "a1cde373", "metadata": {}, "source": [ "### Null values\n", "\n", "Means there is no value for an attribute\n", "\n", "- there is currently no value\n", "- there is a value but it is unknown\n", "- it is not known if there is a value or not\n", "\n" ] }, { "cell_type": "code", "execution_count": 60, "id": "33ebdde6", "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": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
" ], "text/plain": [ "++\n", "||\n", "++\n", "++" ] }, "execution_count": 60, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "create table tmp(id1 int, id2 int);\n", "insert into tmp values(1,2);\n", "insert into tmp values(3,NULL);\n", "insert into tmp values(4,NULL);\n", "insert into tmp values(NULL,5);\n", "insert into tmp values(NULL,NULL);\n" ] }, { "cell_type": "code", "execution_count": 61, "id": "bc285fad", "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", "
id1id2
12
3None
4None
None5
NoneNone
" ], "text/plain": [ "+------+------+\n", "| id1 | id2 |\n", "+------+------+\n", "| 1 | 2 |\n", "| 3 | None |\n", "| 4 | None |\n", "| None | 5 |\n", "| None | None |\n", "+------+------+" ] }, "execution_count": 61, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select * from tmp;" ] }, { "cell_type": "code", "execution_count": 63, "id": "c9cbd57c", "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", "
id1id2
12
" ], "text/plain": [ "+-----+-----+\n", "| id1 | id2 |\n", "+-----+-----+\n", "| 1 | 2 |\n", "+-----+-----+" ] }, "execution_count": 63, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select * from tmp\n", "where id2 > 2;" ] }, { "cell_type": "markdown", "id": "ab1be295", "metadata": {}, "source": [ "5 = NULL - evaluates to UNKNOWN\n", "NULL<5 - evaluates to UNKNOWN\n", "\n", "I only return the tuples that evaluate to True\n", "\n", "\n", "NOT (UNKNOWN) = UNKNOWN\n", "\n", "TRUE AND UNKNOWN = UNKNOWN \n", "FALSE AND UNKNOWN = FALSE \n", "TRUE OR UNKNOWN = TRUE \n", "FALSE OR UNKNOWN = UNKNOWN " ] }, { "cell_type": "code", "execution_count": 71, "id": "4d3ab1ef", "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", "
id1id2
3None
4None
None5
NoneNone
" ], "text/plain": [ "+------+------+\n", "| id1 | id2 |\n", "+------+------+\n", "| 3 | None |\n", "| 4 | None |\n", "| None | 5 |\n", "| None | None |\n", "+------+------+" ] }, "execution_count": 71, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "select * \n", "from tmp\n", "where\n", " id2>2\n", " or id2 is NULL;" ] }, { "cell_type": "code", "execution_count": null, "id": "42a1bb29", "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 }