{ "cells": [ { "cell_type": "markdown", "id": "ad8cb3d3", "metadata": {}, "source": [ "## Lecture 18 \n", "\n", "### Announcements\n", "\n", "- Homework #5 is now out, due after exam#2\n", " \n", "- Second optional lecture exercise is out later today or tomorrow, due on friday.\n", "\n", "- Exam #2 is next monday, November 10. It is open book, open notes \n", " as before. Nothing electronic is allowed. \n", " - It covers everything we have done in class since Exam #1, yes including ER hierarchies!\n", "\n", "### Today's lecture\n", "\n", "- Views\n", "- Other SQL constructs\n", "- Object relational extensions" ] }, { "cell_type": "code", "execution_count": 1, "id": "c6e9f11e", "metadata": {}, "outputs": [], "source": [ "load_ext sql" ] }, { "cell_type": "code", "execution_count": 2, "id": "5c2f5b82", "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": "code", "execution_count": 4, "id": "a7210d5b", "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", "
bakernumwinsbesttechnicalworsttechnical
Kim-Joy1110
Rahul117
Ruby1110
" ], "text/plain": [ "+---------+---------+---------------+----------------+\n", "| baker | numwins | besttechnical | worsttechnical |\n", "+---------+---------+---------------+----------------+\n", "| Kim-Joy | 1 | 1 | 10 |\n", "| Rahul | 1 | 1 | 7 |\n", "| Ruby | 1 | 1 | 10 |\n", "+---------+---------+---------------+----------------+" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "with finalists as \n", " (select baker from bakers \n", " where baker not in (select baker from results \n", " where result = 'eliminated'))\n", "select\n", " f.baker\n", " , count(distinct r.baker) as numwins\n", " , min(t.rank) as besttechnical\n", " , max(t.rank) as worsttechnical\n", "from \n", " finalists f\n", " left join results r\n", " on f.baker = r.baker and r.result = 'star baker'\n", " , technicals t\n", "where \n", " f.baker = t.baker \n", "group by \n", " f.baker\n" ] }, { "cell_type": "code", "execution_count": 8, "id": "8896c99a", "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", "
bakernumwinsbesttechnicalworsttechnical
Kim-Joy1110
Rahul117
Ruby1110
" ], "text/plain": [ "+---------+---------+---------------+----------------+\n", "| baker | numwins | besttechnical | worsttechnical |\n", "+---------+---------+---------------+----------------+\n", "| Kim-Joy | 1 | 1 | 10 |\n", "| Rahul | 1 | 1 | 7 |\n", "| Ruby | 1 | 1 | 10 |\n", "+---------+---------+---------------+----------------+" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "select\n", " b.baker\n", " , count(distinct r.baker) as numwins\n", " , min(t.rank) as besttechnical\n", " , max(t.rank) as worsttechnical\n", "from \n", " bakers b\n", " left join results r\n", " on b.baker = r.baker and r.result = 'star baker'\n", " , technicals t\n", "where \n", " b.baker = t.baker \n", " and b.baker not in (select baker from results \n", " where result ='eliminated')\n", "group by \n", " b.baker" ] }, { "cell_type": "code", "execution_count": 9, "id": "97852e1d", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
" ], "text/plain": [ "++\n", "||\n", "++\n", "++" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "create or replace view finalists(baker, fullname, age) as\n", "select \n", " baker \n", " , fullname\n", " , age\n", "from \n", " bakers\n", "where\n", " baker not in (select baker from results \n", " where result = 'eliminated');" ] }, { "cell_type": "code", "execution_count": 10, "id": "4136041d", "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", "
bakerfullnamenumwinsbesttechnicalworsttechnical
Kim-JoyKim-Joy Hewlett1110
RahulRahul Mandal117
RubyRuby Bhogal1110
" ], "text/plain": [ "+---------+-----------------+---------+---------------+----------------+\n", "| baker | fullname | numwins | besttechnical | worsttechnical |\n", "+---------+-----------------+---------+---------------+----------------+\n", "| Kim-Joy | Kim-Joy Hewlett | 1 | 1 | 10 |\n", "| Rahul | Rahul Mandal | 1 | 1 | 7 |\n", "| Ruby | Ruby Bhogal | 1 | 1 | 10 |\n", "+---------+-----------------+---------+---------------+----------------+" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "select\n", " f.baker\n", " , f.fullname\n", " , count(distinct r.baker) as numwins\n", " , min(t.rank) as besttechnical\n", " , max(t.rank) as worsttechnical\n", "from \n", " finalists f\n", " left join results r\n", " on f.baker = r.baker and r.result = 'star baker'\n", " , technicals t\n", "where \n", " f.baker = t.baker \n", "group by \n", " f.baker, f.fullname;" ] }, { "cell_type": "markdown", "id": "719e74e2", "metadata": {}, "source": [ "### Views\n", "\n", "Views are virtual tables. They do not contain tuples, just query definition.\n", "\n", "- You can use views in SQL SELECT queries\n", " - Query definition of the view is inserted into your query and optimized together.\n", " \n", " - Write once (correctly!) and execute many times.\n", " - Use views to define access control.\n", " \n", "- You can sometimes use a view to INSERT/UPDATE/DELETE underlying table. \n", " - It is only possible if the view is updatable, which means\n", " - There is a single relation in the from of the view\n", " - The view does not have distinct or group by\n", " \n", "- If the view does not match the functions it is used for in other queries, it may increase the cost of your queries. \n" ] }, { "cell_type": "code", "execution_count": 11, "id": "b51b5dfc", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
" ], "text/plain": [ "++\n", "||\n", "++\n", "++" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- not updatable\n", "create view olderages(age) as\n", "select distinct age from bakers ;" ] }, { "cell_type": "code", "execution_count": 12, "id": "7cb49848", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
" ], "text/plain": [ "++\n", "||\n", "++\n", "++" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "create view olderbakers(baker, age) as\n", "select baker, age\n", "from bakers\n", "where age > 35 ;\n", "\n", "-- Updateable and even can change age of people!" ] }, { "cell_type": "code", "execution_count": 13, "id": "d2f208a1", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'baking'" ], "text/plain": [ "Running query in 'baking'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
" ], "text/plain": [ "++\n", "||\n", "++\n", "++" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "drop view olderbakers ;\n", "\n", "create view olderbakers(baker, age) as\n", "select baker, age\n", "from bakers\n", "where age > 35 \n", "with check option;" ] }, { "cell_type": "code", "execution_count": 15, "id": "b6ecff66", "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", "
bakerage
Karenold
Terryold
Danold
Jonold
Antonyyoung
Manonyoung
Imeldayoung
Brionyyoung
Lukeyoung
Kim-Joyyoung
Rahulyoung
Rubyyoung
" ], "text/plain": [ "+---------+-------+\n", "| baker | age |\n", "+---------+-------+\n", "| Karen | old |\n", "| Terry | old |\n", "| Dan | old |\n", "| Jon | old |\n", "| Antony | young |\n", "| Manon | young |\n", "| Imelda | young |\n", "| Briony | young |\n", "| Luke | young |\n", "| Kim-Joy | young |\n", "| Rahul | young |\n", "| Ruby | young |\n", "+---------+-------+" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- CASE Statement for implementing IF\n", "\n", "select\n", " baker\n", " , 'young' as age\n", "from \n", " bakers \n", "where \n", " age <= 35 \n", "union\n", "select baker, 'old' as age from bakers where age>35\n", "order by\n", " age asc;" ] }, { "cell_type": "code", "execution_count": 18, "id": "7c4013d8", "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", "
bakerage
Jonold
Terryold
Danold
Karenold
Antonyyoung
Rahulyoung
Rubyyoung
Kim-Joyyoung
Manonyoung
Brionyyoung
Imeldayoung
Lukeyoung
" ], "text/plain": [ "+---------+-------+\n", "| baker | age |\n", "+---------+-------+\n", "| Jon | old |\n", "| Terry | old |\n", "| Dan | old |\n", "| Karen | old |\n", "| Antony | young |\n", "| Rahul | young |\n", "| Ruby | young |\n", "| Kim-Joy | young |\n", "| Manon | young |\n", "| Briony | young |\n", "| Imelda | young |\n", "| Luke | young |\n", "+---------+-------+" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "select \n", " baker\n", " , case when age>=35 then 'old' else 'young' end as age\n", "from\n", " bakers \n", "order by\n", " age asc;" ] }, { "cell_type": "code", "execution_count": 19, "id": "f846650c", "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": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "create table tmp(id int);\n", "insert into tmp values(1);\n", "insert into tmp values(null);\n", "insert into tmp values(null);\n", "insert into tmp values(3);\n", "insert into tmp values(4);\n" ] }, { "cell_type": "code", "execution_count": 20, "id": "b955a19d", "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
2.6666666666666667
" ], "text/plain": [ "+--------------------+\n", "| avg |\n", "+--------------------+\n", "| 2.6666666666666667 |\n", "+--------------------+" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "select avg(id) from tmp ;" ] }, { "cell_type": "code", "execution_count": 21, "id": "67188104", "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
1.6000000000000000
" ], "text/plain": [ "+--------------------+\n", "| avg |\n", "+--------------------+\n", "| 1.6000000000000000 |\n", "+--------------------+" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "select avg(case when id is null then 0 else id end) from tmp ;" ] }, { "cell_type": "markdown", "id": "72ad1ce4", "metadata": {}, "source": [ "### Grouping functions\n", "\n", "- Group by \n", " - GROUPING SETS ((day),(time),());\n", " - Cube (day, time)\n", "- Aggregate over a partition (without grouping)\n", " - OVER (partition by day)\n", "- Filter aggregates in select\n", " - sum(attr) filter (where attr>X) \n", "\n" ] }, { "cell_type": "code", "execution_count": 24, "id": "a1e54dde", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Connecting and switching to connection 'class'" ], "text/plain": [ "Connecting and switching to connection 'class'" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%sql --section class" ] }, { "cell_type": "code", "execution_count": 26, "id": "0d517162", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'class'" ], "text/plain": [ "Running query in 'class'" ] }, "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": 26, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "drop table if exists events;\n", "\n", "CREATE TABLE events (\n", " name VARCHAR(10)\n", " , day VARCHAR(10)\n", " , time VARCHAR(10)\n", " , price INT\n", ") ;\n", "\n", "INSERT INTO events VALUES ('sitting','M','12:00',5);\n", "INSERT INTO events VALUES ('reading','W','2:00',10);\n", "INSERT INTO events VALUES ('sleeping','M','2:00',12);\n", "INSERT INTO events VALUES ('hopping','W','12:00',8);\n", "INSERT INTO events VALUES ('jumping','M','4:00',22);" ] }, { "cell_type": "code", "execution_count": 31, "id": "0c47d561", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'class'" ], "text/plain": [ "Running query in 'class'" ] }, "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", "
daytimeavg
WNone9.0000000000000000
MNone13.0000000000000000
None4:0022.0000000000000000
None12:006.5000000000000000
None2:0011.0000000000000000
" ], "text/plain": [ "+------+-------+---------------------+\n", "| day | time | avg |\n", "+------+-------+---------------------+\n", "| W | None | 9.0000000000000000 |\n", "| M | None | 13.0000000000000000 |\n", "| None | 4:00 | 22.0000000000000000 |\n", "| None | 12:00 | 6.5000000000000000 |\n", "| None | 2:00 | 11.0000000000000000 |\n", "+------+-------+---------------------+" ] }, "execution_count": 31, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "-- name, day,time,price\n", "\n", "select \n", " day, time, avg(price)\n", "from \n", " events\n", "group by \n", " grouping sets ((day), (time),()) ;" ] }, { "cell_type": "markdown", "id": "bce9caf7", "metadata": {}, "source": [ "Cube (x,y,z): (x,y,z), (x,y), (x,z), (y,z), (x),(y),(z),()\n", "\n", "Cube (x,y): (x,y), (x),(y),()\n", "\n", "Rollup (x,y,z): (x,y,z), (x,y), (x), ()\n" ] }, { "cell_type": "code", "execution_count": 32, "id": "7b517193", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'class'" ], "text/plain": [ "Running query in 'class'" ] }, "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
daytimeavg
NoneNone11.4000000000000000
M2:0012.0000000000000000
M12:005.0000000000000000
W12:008.0000000000000000
W2:0010.0000000000000000
M4:0022.0000000000000000
WNone9.0000000000000000
MNone13.0000000000000000
None4:0022.0000000000000000
None12:006.5000000000000000
None2:0011.0000000000000000
" ], "text/plain": [ "+------+-------+---------------------+\n", "| day | time | avg |\n", "+------+-------+---------------------+\n", "| None | None | 11.4000000000000000 |\n", "| M | 2:00 | 12.0000000000000000 |\n", "| M | 12:00 | 5.0000000000000000 |\n", "| W | 12:00 | 8.0000000000000000 |\n", "| W | 2:00 | 10.0000000000000000 |\n", "| M | 4:00 | 22.0000000000000000 |\n", "| W | None | 9.0000000000000000 |\n", "| M | None | 13.0000000000000000 |\n", "| None | 4:00 | 22.0000000000000000 |\n", "| None | 12:00 | 6.5000000000000000 |\n", "| None | 2:00 | 11.0000000000000000 |\n", "+------+-------+---------------------+" ] }, "execution_count": 32, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "select \n", " day, time, avg(price)\n", "from \n", " events\n", "group by \n", " cube(day,time) ;" ] }, { "cell_type": "code", "execution_count": 34, "id": "c6794850", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'class'" ], "text/plain": [ "Running query in 'class'" ] }, "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namedaytimeavg
NoneNoneNone11.4000000000000000
sittingM12:005.0000000000000000
readingW2:0010.0000000000000000
sleepingM2:0012.0000000000000000
hoppingW12:008.0000000000000000
jumpingM4:0022.0000000000000000
hoppingWNone8.0000000000000000
sleepingMNone12.0000000000000000
sittingMNone5.0000000000000000
readingWNone10.0000000000000000
jumpingMNone22.0000000000000000
sleepingNoneNone12.0000000000000000
readingNoneNone10.0000000000000000
hoppingNoneNone8.0000000000000000
jumpingNoneNone22.0000000000000000
sittingNoneNone5.0000000000000000
" ], "text/plain": [ "+----------+------+-------+---------------------+\n", "| name | day | time | avg |\n", "+----------+------+-------+---------------------+\n", "| None | None | None | 11.4000000000000000 |\n", "| sitting | M | 12:00 | 5.0000000000000000 |\n", "| reading | W | 2:00 | 10.0000000000000000 |\n", "| sleeping | M | 2:00 | 12.0000000000000000 |\n", "| hopping | W | 12:00 | 8.0000000000000000 |\n", "| jumping | M | 4:00 | 22.0000000000000000 |\n", "| hopping | W | None | 8.0000000000000000 |\n", "| sleeping | M | None | 12.0000000000000000 |\n", "| sitting | M | None | 5.0000000000000000 |\n", "| reading | W | None | 10.0000000000000000 |\n", "| jumping | M | None | 22.0000000000000000 |\n", "| sleeping | None | None | 12.0000000000000000 |\n", "| reading | None | None | 10.0000000000000000 |\n", "| hopping | None | None | 8.0000000000000000 |\n", "| jumping | None | None | 22.0000000000000000 |\n", "| sitting | None | None | 5.0000000000000000 |\n", "+----------+------+-------+---------------------+" ] }, "execution_count": 34, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "select \n", " name,day, time, avg(price)\n", "from \n", " events\n", "group by \n", " rollup(name,day,time) ;" ] }, { "cell_type": "code", "execution_count": 35, "id": "2e02502f", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'class'" ], "text/plain": [ "Running query in 'class'" ] }, "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", "
namedaytimepricesum
sittingM12:00539
sleepingM2:001239
jumpingM4:002239
readingW2:001018
hoppingW12:00818
" ], "text/plain": [ "+----------+-----+-------+-------+-----+\n", "| name | day | time | price | sum |\n", "+----------+-----+-------+-------+-----+\n", "| sitting | M | 12:00 | 5 | 39 |\n", "| sleeping | M | 2:00 | 12 | 39 |\n", "| jumping | M | 4:00 | 22 | 39 |\n", "| reading | W | 2:00 | 10 | 18 |\n", "| hopping | W | 12:00 | 8 | 18 |\n", "+----------+-----+-------+-------+-----+" ] }, "execution_count": 35, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "select \n", " name\n", " , day\n", " , time\n", " , price\n", " , sum(price) over (partition by day) \n", "from events ;" ] }, { "cell_type": "code", "execution_count": 36, "id": "ff0cf833", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'class'" ], "text/plain": [ "Running query in 'class'" ] }, "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", "
namedaytimepricetotalprice
sittingM12:00539
readingW2:001018
sleepingM2:001239
hoppingW12:00818
jumpingM4:002239
" ], "text/plain": [ "+----------+-----+-------+-------+------------+\n", "| name | day | time | price | totalprice |\n", "+----------+-----+-------+-------+------------+\n", "| sitting | M | 12:00 | 5 | 39 |\n", "| reading | W | 2:00 | 10 | 18 |\n", "| sleeping | M | 2:00 | 12 | 39 |\n", "| hopping | W | 12:00 | 8 | 18 |\n", "| jumping | M | 4:00 | 22 | 39 |\n", "+----------+-----+-------+-------+------------+" ] }, "execution_count": 36, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "with sums as (select day, sum(price) as totalprice from events group by day)\n", "select\n", " e.*, s.totalprice\n", "from events e, sums s\n", "where e.day = s.day;" ] }, { "cell_type": "markdown", "id": "097368ad", "metadata": {}, "source": [ "### Recursion!\n", "\n", "- Implemented using WITH statement\n", " \n", " - Write one query using base tables only!\n", " - Write a recursive query using the relation being defined and base relations.\n", " \n", "anc(X,Y) :- parent(X,Y)\n", "anc(X,Z) :- anc(X,Y), parent(Y,Z)" ] }, { "cell_type": "code", "execution_count": 39, "id": "7d867934", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'class'" ], "text/plain": [ "Running query in 'class'" ] }, "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 if exists parents ;\n", "\n", "CREATE TABLE parents (\n", " parent varchar(100)\n", " , child varchar(100)\n", ") ;\n", "\n", "\n", "INSERT INTO parents VALUES('Dakota','Madison');\n", "INSERT INTO parents VALUES('Madison','Ava');\n", "INSERT INTO parents VALUES('Madison','Sophia');\n", "INSERT INTO parents VALUES('Sophia','Noah');\n", "INSERT INTO parents VALUES('Noah','Emma');\n" ] }, { "cell_type": "code", "execution_count": 41, "id": "b73c4c6d", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'class'" ], "text/plain": [ "Running query in 'class'" ] }, "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", "
parentchilddegree
DakotaMadison1
MadisonAva1
MadisonSophia1
SophiaNoah1
NoahEmma1
DakotaSophia2
DakotaAva2
MadisonNoah2
SophiaEmma2
DakotaNoah3
MadisonEmma3
DakotaEmma4
" ], "text/plain": [ "+---------+---------+--------+\n", "| parent | child | degree |\n", "+---------+---------+--------+\n", "| Dakota | Madison | 1 |\n", "| Madison | Ava | 1 |\n", "| Madison | Sophia | 1 |\n", "| Sophia | Noah | 1 |\n", "| Noah | Emma | 1 |\n", "| Dakota | Sophia | 2 |\n", "| Dakota | Ava | 2 |\n", "| Madison | Noah | 2 |\n", "| Sophia | Emma | 2 |\n", "| Dakota | Noah | 3 |\n", "| Madison | Emma | 3 |\n", "| Dakota | Emma | 4 |\n", "+---------+---------+--------+" ] }, "execution_count": 41, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "-- anc(X,Y) :- parent(X,Y)\n", "-- anc(X,Z) :- anc(X,Y), parent(Y,Z)\n", "\n", "with recursive anc as (\n", " select parent, child, 1 as degree from parents\n", " union\n", " select a.parent, p.child, a.degree+1\n", " from anc a, parents p\n", " where a.child = p.parent \n", ")\n", "select * from anc \n", "order by degree asc;" ] }, { "cell_type": "code", "execution_count": 43, "id": "734e77f0", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'class'" ], "text/plain": [ "Running query in 'class'" ] }, "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": 43, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- Hierarchies\n", "\n", "CREATE TABLE cities (\n", " name text primary key\n", " , population int\n", " , altitude int -- in feet\n", ");\n", " \n", "CREATE TABLE capitals (\n", " state char(2)\n", ") INHERITS (cities);\n", "\n", "insert into cities values('New York City', 8175133, 33);\n", "insert into cities values('Syracuse', 145170, 380);\n", "insert into capitals values('Albany', 97856, 312, 'NY');\n" ] }, { "cell_type": "code", "execution_count": 44, "id": "ea268d6c", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'class'" ], "text/plain": [ "Running query in 'class'" ] }, "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", "
namepopulationaltitudestate
Albany97856312NY
" ], "text/plain": [ "+--------+------------+----------+-------+\n", "| name | population | altitude | state |\n", "+--------+------------+----------+-------+\n", "| Albany | 97856 | 312 | NY |\n", "+--------+------------+----------+-------+" ] }, "execution_count": 44, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "select * from capitals;" ] }, { "cell_type": "code", "execution_count": 45, "id": "870738a6", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'class'" ], "text/plain": [ "Running query in 'class'" ] }, "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", "
namepopulationaltitude
New York City817513333
Syracuse145170380
Albany97856312
" ], "text/plain": [ "+---------------+------------+----------+\n", "| name | population | altitude |\n", "+---------------+------------+----------+\n", "| New York City | 8175133 | 33 |\n", "| Syracuse | 145170 | 380 |\n", "| Albany | 97856 | 312 |\n", "+---------------+------------+----------+" ] }, "execution_count": 45, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "select * from cities ;" ] }, { "cell_type": "code", "execution_count": 46, "id": "e262cd0b", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'class'" ], "text/plain": [ "Running query in 'class'" ] }, "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", "
namepopulationaltitude
New York City817513333
Syracuse145170380
" ], "text/plain": [ "+---------------+------------+----------+\n", "| name | population | altitude |\n", "+---------------+------------+----------+\n", "| New York City | 8175133 | 33 |\n", "| Syracuse | 145170 | 380 |\n", "+---------------+------------+----------+" ] }, "execution_count": 46, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "select * from only(cities);" ] }, { "cell_type": "code", "execution_count": null, "id": "ef5f506c", "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 }