{ "cells": [ { "cell_type": "code", "execution_count": 1, "metadata": { "slideshow": { "slide_type": "skip" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Requirement already satisfied: ipython-sql in c:\\programdata\\anaconda3\\lib\\site-packages (0.4.0)\n", "Requirement already satisfied: sqlparse in c:\\programdata\\anaconda3\\lib\\site-packages (from ipython-sql) (0.4.1)\n", "Requirement already satisfied: ipython>=1.0 in c:\\programdata\\anaconda3\\lib\\site-packages (from ipython-sql) (7.19.0)\n", "Requirement already satisfied: prettytable<1 in c:\\programdata\\anaconda3\\lib\\site-packages (from ipython-sql) (0.7.2)\n", "Requirement already satisfied: six in c:\\programdata\\anaconda3\\lib\\site-packages (from ipython-sql) (1.15.0)\n", "Requirement already satisfied: sqlalchemy>=0.6.7 in c:\\programdata\\anaconda3\\lib\\site-packages (from ipython-sql) (1.3.20)\n", "Requirement already satisfied: ipython-genutils>=0.1.0 in c:\\programdata\\anaconda3\\lib\\site-packages (from ipython-sql) (0.2.0)\n", "Requirement already satisfied: prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0 in c:\\programdata\\anaconda3\\lib\\site-packages (from ipython>=1.0->ipython-sql) (3.0.8)\n", "Requirement already satisfied: pygments in c:\\programdata\\anaconda3\\lib\\site-packages (from ipython>=1.0->ipython-sql) (2.7.2)\n", "Requirement already satisfied: pickleshare in c:\\programdata\\anaconda3\\lib\\site-packages (from ipython>=1.0->ipython-sql) (0.7.5)\n", "Requirement already satisfied: backcall in c:\\programdata\\anaconda3\\lib\\site-packages (from ipython>=1.0->ipython-sql) (0.2.0)\n", "Requirement already satisfied: setuptools>=18.5 in c:\\programdata\\anaconda3\\lib\\site-packages (from ipython>=1.0->ipython-sql) (50.3.1.post20201107)\n", "Requirement already satisfied: jedi>=0.10 in c:\\programdata\\anaconda3\\lib\\site-packages (from ipython>=1.0->ipython-sql) (0.17.1)\n", "Requirement already satisfied: traitlets>=4.2 in c:\\programdata\\anaconda3\\lib\\site-packages (from ipython>=1.0->ipython-sql) (5.0.5)\n", "Requirement already satisfied: decorator in c:\\programdata\\anaconda3\\lib\\site-packages (from ipython>=1.0->ipython-sql) (4.4.2)\n", "Requirement already satisfied: colorama; sys_platform == \"win32\" in c:\\programdata\\anaconda3\\lib\\site-packages (from ipython>=1.0->ipython-sql) (0.4.4)\n", "Requirement already satisfied: wcwidth in c:\\programdata\\anaconda3\\lib\\site-packages (from prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0->ipython>=1.0->ipython-sql) (0.2.5)\n", "Requirement already satisfied: parso<0.8.0,>=0.7.0 in c:\\programdata\\anaconda3\\lib\\site-packages (from jedi>=0.10->ipython>=1.0->ipython-sql) (0.7.0)\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "The system cannot find the path specified.\n", "The system cannot find the path specified.\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "Requirement already satisfied: mysqlclient in c:\\programdata\\anaconda3\\lib\\site-packages (2.0.3)\n" ] } ], "source": [ "!pip3 install ipython-sql\n", "!pip3 install mysqlclient\n", "import sqlalchemy\n", "%load_ext sql\n", "%sql mysql://pb:pbvaje@pb.fri.uni-lj.si:3306/vaje\n", "\n" ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "slideshow": { "slide_type": "skip" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "0 rows affected.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "mysql://pb:pbvaje@pb.fri.uni-lj.si/mysql -- 'Connected: pb@mysql' %%sql pb@mysql\n", "use vaje;" ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "slideshow": { "slide_type": "skip" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Connection info needed in SQLAlchemy format, example:\n", " postgresql://username:password@hostname/dbname\n", " or an existing connection: dict_keys(['mysql://pb:***@pb.fri.uni-lj.si:3306/vaje', 'mysql://pb:***@pb.fri.uni-lj.si/mysql'])\n", "No module named 'psycopg2'\n", "Connection info needed in SQLAlchemy format, example:\n", " postgresql://username:password@hostname/dbname\n", " or an existing connection: dict_keys(['mysql://pb:***@pb.fri.uni-lj.si:3306/vaje', 'mysql://pb:***@pb.fri.uni-lj.si/mysql'])\n" ] } ], "source": [ "%%sql \n", "postgresql://pb:pbvaje@pb.fri.uni-lj.si/pb -- 'Connected: pb@pb' %%sql pb@pb\n", "set schema 'vaje';" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "---------------------------------\n", "\n", "---------------------------------\n", "\n", "# Naloga: Poišči šifre in imena jadralcev, ki so rezervirali rdeče čolne na dan 10. 10. 2006.\n", "\n", "### Analiza:\n", "\n", "Potrebne relacije oz. tabele:\n", "* $jadralec, rezervacija, coln$\n", "\n", "Stična pogoja:\n", "* $jadralec \\underset{jid}{⋈} rezervacija$\n", "* $rezervacija \\underset{cid}{⋈} coln$\n", "\n", "Selekcijska pogoja:\n", "* rdeča barva\n", "* dan 10. 10. 2006\n", "\n", "Projekcija:\n", "* na $jid$ in $ime$ jadralca\n", "\n", "### Rešitev (relacijska algebra):\n", "$${\\Huge \\pi}_{jadralec.jid, jadralec.ime}\\left(jadralec \\underset{jid}{⋈} \n", "{\\Huge \\sigma}_{dan='10.10.2006'}(rezervacija) \\underset{cid}{⋈} \n", "{\\Huge \\sigma}_{barva='rdeca'}(coln) \\right)$$ \n", "\n", "### Rešitev (SQL) - ločimo stična pogoja od selekcijskih:" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * mysql://pb:***@pb.fri.uni-lj.si/mysql\n", " mysql://pb:***@pb.fri.uni-lj.si:3306/vaje\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
jidime
22Darko
" ], "text/plain": [ "[(22, 'Darko')]" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT j.jid, j.ime\n", " FROM jadralec j JOIN rezervacija r USING (jid) JOIN coln c USING(cid)\n", " WHERE c.barva='rdeca' AND r.dan=DATE'2006-10-10';\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "---------------------------------\n", "\n", "---------------------------------\n", "\n", "# Naloga: Poišči imena vseh jadralcev in šifre čolnov, ki so jih rezervirali (NULL če niso nobenega),\n", "\n", "\n", "### Analiza:\n", "\n", "Potrebne relacije oz. tabele:\n", "* $jadralec, rezervacija, coln$\n", "\n", "Stična pogoja:\n", "* $jadralec \\underset{jid}{⋈} rezervacija$\n", "* $rezervacija \\underset{cid}{⋈} coln$\n", "\n", "Selekcijska pogoja:\n", "* rdeča barva\n", "* dan 10. 10. 2006\n", "\n", "Projekcija:\n", "* na $jid$ in $ime$ jadralca\n", "\n", "### Rešitev (relacijska algebra):\n", "$${\\Huge \\pi}_{ime, cid}\\left(jadralec \\underset{jid}{⋊} rezervacija\\right)$$ \n", "\n", "### Rešitev (SQL) - levi zunanji stik (OUTER lahko izpustimo):" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * mysql://pb:***@pb.fri.uni-lj.si/mysql\n", " mysql://pb:***@pb.fri.uni-lj.si:3306/vaje\n", "16 rows affected.\n" ] }, { "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", "
imecid
AndrejNone
AnzeNone
BineNone
BorutNone
Darko104
Darko101
Darko102
Darko103
Henrik101
Henrik102
Henrik103
Lojze102
Lojze103
Lojze104
RajkoNone
ZdravkoNone
" ], "text/plain": [ "[('Andrej', None),\n", " ('Anze', None),\n", " ('Bine', None),\n", " ('Borut', None),\n", " ('Darko', 104),\n", " ('Darko', 101),\n", " ('Darko', 102),\n", " ('Darko', 103),\n", " ('Henrik', 101),\n", " ('Henrik', 102),\n", " ('Henrik', 103),\n", " ('Lojze', 102),\n", " ('Lojze', 103),\n", " ('Lojze', 104),\n", " ('Rajko', None),\n", " ('Zdravko', None)]" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT DISTINCT ime, cid\n", "FROM jadralec LEFT OUTER JOIN rezervacija USING (jid)\n", "ORDER BY ime;\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "---------------------------------\n", "\n", "---------------------------------\n", "\n", "# Naloga: Poišči imena jadralcev, ki so rezervirali čoln modre barve.\n", "\n", "\n", "### Analiza:\n", "\n", "Potrebne relacije oz. tabele:\n", "* $jadralec, rezervacija, coln$\n", "\n", "Stična pogoja:\n", "* $jadralec \\underset{jid}{⋈} rezervacija$\n", "* $rezervacija \\underset{cid}{⋈} coln$\n", "\n", "Selekcijski pogoj:\n", "* modra barva\n", "\n", "\n", "Projekcija:\n", "* na $ime$ jadralca\n", "\n", "### Rešitev (relacijska algebra) s stiki:\n", "$${\\Huge \\pi}_{jadralec.ime}\\left(jadralec \\underset{jid}{⋈} \n", "rezervacija \\underset{cid}{⋈} \n", "{\\Huge \\sigma}_{barva='modra'}(coln) \\right)$$ \n", "\n", "### Rešitev (relacijska algebra) s podmnožicami:\n", "$${\\Huge \\pi}_{ime} \\left( {\\Huge \\sigma}_\n", "{jid \\in \n", "{\\Huge \\pi}_{jid} \\left( \n", "rezervacija \\underset{cid}{⋈} \n", "{\\Huge \\sigma}_{barva='modra'}(coln) \\right)\n", "}(jadralec)\\right)$$ \n", "\n", "\n", "### Delna rešitev (SQL) - množica šifer jadralcev, ki so rezervirali moder čoln:" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
jid
22
64
" ], "text/plain": [ "[(22,), (64,)]" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT jid \n", "FROM coln JOIN rezervacija USING(cid)\n", "WHERE barva='modra';" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Rešitev (SQL) - s statičnimi množicami:" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ime
Darko
Henrik
" ], "text/plain": [ "[(u'Darko',), (u'Henrik',)]" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT j.ime \n", "FROM jadralec j\n", "WHERE j.jid IN (22,64);" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Rešitev (SQL) - z dinamičnimi množicami (gnezdena poizvedba generira množico):" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ime
Henrik
Darko
" ], "text/plain": [ "[(u'Henrik',), (u'Darko',)]" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT j.ime \n", "FROM jadralec j\n", "WHERE j.jid IN\n", " (SELECT jid \n", " FROM coln JOIN rezervacija USING(cid)\n", " WHERE barva='modra');" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "---------------------------------\n", "### PostgreSQL in MariaDB - primerjava n-teric (tudi za dinamične množice):" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
jidime
22Darko
" ], "text/plain": [ "[(22, u'Darko')]" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT jid, ime\n", "FROM jadralec\n", "WHERE (jid, ime) IN ((22, 'Darko'));" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
jidime
22Darko
" ], "text/plain": [ "[(22, u'Darko')]" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT jid, ime\n", "FROM jadralec\n", "WHERE (jid, ime) IN (\n", " SELECT jid, ime\n", " FROM jadralec\n", " WHERE jid = 22\n", ");" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "---------------------------------\n", "\n", "---------------------------------\n", "\n", "# Kvantifikatorji logičnega pogoja v SQL\n", "\n", "### Eksistenčni $\\exists$ \n", " =ANY (ali ostali primerjalni operatorji)\n", "\n", "### Univerzalni $\\forall$ \n", " =ALL (ali ostali primerjalni operatorji)\n", " \n", "# Naloga: Izpišite šifro in rating jadralcev z najvišjim ratingom!\n", "\n", "Delna rešitev: množica ratingov vseh jadralcev." ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "10 rows affected.\n" ] }, { "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", "
rating
7
1
8
8
10
7
10
9
3
3
" ], "text/plain": [ "[(7,), (1,), (8,), (8,), (10,), (7,), (10,), (9,), (3,), (3,)]" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT rating\n", "FROM jadralec;\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Ideja: jadralec z najvišjim ratingom mora imeti rating višji od vseh ostalih!\n" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "0 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", "
jidrating
" ], "text/plain": [ "[]" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT jid, rating\n", "FROM jadralec\n", "WHERE rating > ALL( \n", " SELECT rating\n", " FROM jadralec);\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "\n", "### Popravek: jadralec z najvišjim ratingom mora imeti rating višji ali enak od vseh ostalih!\n", "Ni dovolj, da ne primerjamo s samim seboj, saj ga ima lahko še kdo drug!" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
jidrating
5810
7110
" ], "text/plain": [ "[(58, 10), (71, 10)]" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT jid, rating\n", "FROM jadralec\n", "WHERE rating >= ALL( \n", " SELECT rating\n", " FROM jadralec);\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "---------------------------------\n", "\n", "---------------------------------\n", "\n", "\n", "# Preverjanje (ne)praznosti množic, rezultatov gnezdenih poizvedb\n", "\n", "### SQL: EXISTS, NOT EXISTS\n", "\n", "# Naloga: Izpiši vse jadralce, ki so kadarkoli rezervirali čoln Bavaria.\n", "\n", "### Rešitev (relacijska algebra) s stiki:\n", "$$jadralec \\underset{jid}{⋈} \n", "rezervacija \\underset{cid}{⋈} \n", "{\\Huge \\sigma}_{ime='Bavaria'}(coln)$$ \n", "\n", "\n", "\n", "### Delna rešitev\n", "\n", "Pomožni korak: jadralec s podano šifro (22) je rezerviral čoln 'Bavaria', kadar je spodnja množica neprazna." ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
cidjiddanimedolzinabarva
104222006-10-07Bavaria50rdeca
" ], "text/plain": [ "[(104, 22, datetime.date(2006, 10, 7), u'Bavaria', 50, u'rdeca')]" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT *\n", "FROM rezervacija r JOIN coln c USING (cid)\n", "WHERE r.jid = 22 AND c.ime = 'Bavaria';\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Rešitev s korelirano vgnezdeno poizvedbo!" ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
jidimeratingstarost
31Lojze855.5
22Darko745.0
" ], "text/plain": [ "[(31, u'Lojze', 8, 55.5), (22, u'Darko', 7, 45.0)]" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT *\n", "FROM jadralec j\n", "WHERE EXISTS\n", " ( SELECT *\n", " FROM rezervacija r JOIN coln c USING (cid)\n", " WHERE r.jid =j.jid AND c.ime = 'Bavaria' );\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Kaj se zgodi, če izpustimo korelacijski pogoj?\n", " r.jid =j.jid" ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "10 rows affected.\n" ] }, { "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", "
jidimeratingstarost
22Darko745.0
29Borut133.0
31Lojze855.5
32Andrej825.5
58Rajko1035.0
64Henrik735.0
71Zdravko1016.0
74Henrik935.0
85Anze325.5
95Bine363.5
" ], "text/plain": [ "[(22, u'Darko', 7, 45.0),\n", " (29, u'Borut', 1, 33.0),\n", " (31, u'Lojze', 8, 55.5),\n", " (32, u'Andrej', 8, 25.5),\n", " (58, u'Rajko', 10, 35.0),\n", " (64, u'Henrik', 7, 35.0),\n", " (71, u'Zdravko', 10, 16.0),\n", " (74, u'Henrik', 9, 35.0),\n", " (85, u'Anze', 3, 25.5),\n", " (95, u'Bine', 3, 63.5)]" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT *\n", "FROM jadralec j\n", "WHERE EXISTS\n", " ( SELECT *\n", " FROM rezervacija r JOIN coln c USING (cid)\n", " WHERE c.ime = 'Bavaria' );\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "---------------------------------\n", "\n", "---------------------------------\n", "\n", "# Množiške operacije v SQL\n", "* UNION: $\\cup$\n", "* INTERSECT: $\\cap$\n", "* EXCEPT ali MINUS: $-$\n", "\n", "MySQL in MariaDB podpirata le UNION, INTERSECT in EXCEPT pa le preko vgnezdenih poizvedb.\n", "\n", "# Naloga: Izpiši imena vseh čolnov, ki jih je rezerviral jadralec Darko *ali* Lojze." ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "3 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ime
Elan
Sun Odyssey
Bavaria
" ], "text/plain": [ "[(u'Elan',), (u'Sun Odyssey',), (u'Bavaria',)]" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT DISTINCT c.ime\n", "FROM coln c, rezervacija r, jadralec j\n", "WHERE c.cid =r.cid AND r.jid = j.jid \n", " AND ( j.ime = 'Darko' OR j.ime='Lojze' );\n" ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "3 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ime
Elan
Sun Odyssey
Bavaria
" ], "text/plain": [ "[(u'Elan',), (u'Sun Odyssey',), (u'Bavaria',)]" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT c.ime\n", "FROM coln c, rezervacija r, jadralec j\n", "WHERE c.cid =r.cid AND r.jid = j.jid \n", " AND j.ime = 'Darko'\n", "UNION\n", "SELECT c.ime\n", "FROM coln c, rezervacija r, jadralec j\n", "WHERE c.cid =r.cid AND r.jid = j.jid \n", " AND j.ime='Lojze';\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "---------------------------------\n", "\n", "---------------------------------\n", "\n", "\n", "# Naloga: Izpiši imena vseh čolnov, sta jih je rezervirala jadralca Darko *in* Lojze." ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "0 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", "
ime
" ], "text/plain": [ "[]" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT DISTINCT c.ime\n", "FROM coln c, rezervacija r, jadralec j\n", "WHERE c.cid =r.cid AND r.jid = j.jid \n", " AND ( j.ime = 'Darko' AND j.ime='Lojze' );\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Dejansko delamo selekcijo nad naslednjim stikom \n", "\n", "Pogoj `( j.ime = 'Darko' AND j.ime='Lojze' )` se preverja znotraj **iste** vrstice!\n" ] }, { "cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "10 rows affected.\n" ] }, { "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
jidcidimedolzinabarvadanime_1ratingstarost
22101Elan34modra2006-10-10Darko745.0
22102Elan34rdeca2006-10-10Darko745.0
22103Sun Odyssey37zelena2006-10-08Darko745.0
22104Bavaria50rdeca2006-10-07Darko745.0
31102Elan34rdeca2006-11-10Lojze855.5
31103Sun Odyssey37zelena2006-11-06Lojze855.5
31104Bavaria50rdeca2006-11-12Lojze855.5
64101Elan34modra2006-09-05Henrik735.0
64102Elan34rdeca2006-09-08Henrik735.0
74103Sun Odyssey37zelena2006-09-08Henrik935.0
" ], "text/plain": [ "[(22, 101, u'Elan', 34, u'modra', datetime.date(2006, 10, 10), u'Darko', 7, 45.0),\n", " (22, 102, u'Elan', 34, u'rdeca', datetime.date(2006, 10, 10), u'Darko', 7, 45.0),\n", " (22, 103, u'Sun Odyssey', 37, u'zelena', datetime.date(2006, 10, 8), u'Darko', 7, 45.0),\n", " (22, 104, u'Bavaria', 50, u'rdeca', datetime.date(2006, 10, 7), u'Darko', 7, 45.0),\n", " (31, 102, u'Elan', 34, u'rdeca', datetime.date(2006, 11, 10), u'Lojze', 8, 55.5),\n", " (31, 103, u'Sun Odyssey', 37, u'zelena', datetime.date(2006, 11, 6), u'Lojze', 8, 55.5),\n", " (31, 104, u'Bavaria', 50, u'rdeca', datetime.date(2006, 11, 12), u'Lojze', 8, 55.5),\n", " (64, 101, u'Elan', 34, u'modra', datetime.date(2006, 9, 5), u'Henrik', 7, 35.0),\n", " (64, 102, u'Elan', 34, u'rdeca', datetime.date(2006, 9, 8), u'Henrik', 7, 35.0),\n", " (74, 103, u'Sun Odyssey', 37, u'zelena', datetime.date(2006, 9, 8), u'Henrik', 9, 35.0)]" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT *\n", "FROM coln c JOIN rezervacija r USING(cid) JOIN jadralec j USING(jid)\n", "-- AND ( j.ime = 'Darko' AND j.ime='Lojze' );\n", "\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Rešitev s presekom:" ] }, { "cell_type": "code", "execution_count": 21, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "3 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ime
Sun Odyssey
Elan
Bavaria
" ], "text/plain": [ "[(u'Sun Odyssey',), (u'Elan',), (u'Bavaria',)]" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT c.ime\n", "FROM coln c, rezervacija r, jadralec j\n", "WHERE c.cid =r.cid AND r.jid = j.jid \n", " AND j.ime = 'Darko'\n", "INTERSECT -- Samo za PostgreSQL\n", "SELECT c.ime\n", "FROM coln c, rezervacija r, jadralec j\n", "WHERE c.cid =r.cid AND r.jid = j.jid \n", " AND j.ime='Lojze';\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Rešitev s presekom (za MySQL, MariaDB, ki ne poznata INTERSECT):" ] }, { "cell_type": "code", "execution_count": 22, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "3 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ime
Elan
Sun Odyssey
Bavaria
" ], "text/plain": [ "[(u'Elan',), (u'Sun Odyssey',), (u'Bavaria',)]" ] }, "execution_count": 22, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT DISTINCT c.ime\n", "FROM coln c, rezervacija r, jadralec j\n", "WHERE c.cid =r.cid AND r.jid = j.jid AND j.ime = 'Darko' AND \n", "c.ime IN ( -- namesto operatorja INTERSECT\n", "SELECT c.ime\n", "FROM coln c, rezervacija r, jadralec j\n", "WHERE c.cid =r.cid AND r.jid = j.jid AND j.ime = 'Lojze');\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "---------------------------------\n", "\n", "---------------------------------\n", "\n", "\n", "# Eksistenčna kvantifikacija logičnega pogoja v WHERE sklopu\n", "\n", "Normalno obnašanje preverjanja logičnega pogoja.\n", "\n", "# Naloga: Izpiši šifre in imena jadralcev, ki so *vsaj enkrat* rezervirali čoln s šifro 103." ] }, { "cell_type": "code", "execution_count": 23, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "3 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
jidime
22Darko
31Lojze
74Henrik
" ], "text/plain": [ "[(22, u'Darko'), (31, u'Lojze'), (74, u'Henrik')]" ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT jid, ime\n", "FROM jadralec j JOIN rezervacija r USING(jid)\n", "WHERE r.cid =103;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "---------------------------------\n", "\n", "---------------------------------\n", "\n", "# Univerzalna kvantifikacija logičnega pogoja v WHERE sklopu\n", "\n", "Rešujemo s pomočjo dvojne negacije (pogoj in negirana množica).\n", "\n", "$$ \\{\\forall vrstica: P(vrstica)\\} = \\\\\n", " \\{^\\neg\\exists vrstica: ^\\neg P(vrstica)\\} = \\\\\n", " \\{vse~vrstice\\} ~-~\\{vrstice: ^\\neg P(vrstica) \\}\n", "$$\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Naloga: Izpiši imena jadralcev, ki so *vedno* rezervirali čoln s šifro 103.\n", "\n", "Ideja: {vsi jadralci} - {tisti, ki kdaj niso rezervirali tega čolna} " ] }, { "cell_type": "code", "execution_count": 24, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
jidime
74Henrik
" ], "text/plain": [ "[(74, u'Henrik')]" ] }, "execution_count": 24, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT jid, ime\n", "FROM jadralec j JOIN rezervacija r USING(jid)\n", "EXCEPT\n", "SELECT jid, ime\n", "FROM jadralec j JOIN rezervacija r USING(jid)\n", "WHERE r.cid <> 103;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "---------------------------------\n", "# Agregiranje podatkov kot razširitev relacijske algebre\n", "Nima ekvivalenta v (osnovni) relacijski algebri. SQL definira pet agregatnih operacij:\n", "* COUNT\n", "* SUM\n", "* AVG\n", "* MIN\n", "* MAX\n", "\n", "### Pozor na pravilno rabo glede na skupinske atribute!" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "\n", "### PostgreSQL pravilno zavrne naslednjo poizvedbo" ] }, { "cell_type": "code", "execution_count": 25, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(ProgrammingError) column \"rezervacija.jid\" must appear in the GROUP BY clause or be used in an aggregate function\n", "LINE 1: SELECT jid, COUNT(cid)\n", " ^\n", " 'SELECT jid, COUNT(cid)\\nFROM rezervacija;' {}\n" ] } ], "source": [ "%%sql pb@pb\n", "SELECT jid, COUNT(cid)\n", "FROM rezervacija;\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### MySQL/MariaDB dopušča naslednjo poizvedbo, vprašljiv je pomen rezultata" ] }, { "cell_type": "code", "execution_count": 26, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
jidCOUNT(cid)
2210
" ], "text/plain": [ "[(22L, 10L)]" ] }, "execution_count": 26, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql pb@mysql\n", "SELECT jid, COUNT(cid)\n", "FROM rezervacija;\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "---------------------------------\n", "### Naloga iz grupiranje: Za vsak rating izpiši število jadralcev!" ] }, { "cell_type": "code", "execution_count": 27, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "10 rows affected.\n" ] }, { "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", "
jidrating
291
853
953
647
227
318
328
749
7110
5810
" ], "text/plain": [ "[(29, 1),\n", " (85, 3),\n", " (95, 3),\n", " (64, 7),\n", " (22, 7),\n", " (31, 8),\n", " (32, 8),\n", " (74, 9),\n", " (71, 10),\n", " (58, 10)]" ] }, "execution_count": 27, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql pb@pb\n", "SELECT jid, rating\n", "FROM jadralec\n", "ORDER BY rating ASC; -- ASC lahko izpustimo" ] }, { "cell_type": "code", "execution_count": 28, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "6 rows affected.\n" ] }, { "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", "
ratingcount
11
32
72
82
91
102
" ], "text/plain": [ "[(1, 1L), (3, 2L), (7, 2L), (8, 2L), (9, 1L), (10, 2L)]" ] }, "execution_count": 28, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT rating, COUNT(*)\n", "FROM jadralec\n", "GROUP BY rating\n", "ORDER BY rating ASC;\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Sintaktično sicer pravilno, vendar semantično vprašljivo: h kateri skupini spada kateri rezultat?" ] }, { "cell_type": "code", "execution_count": 29, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "6 rows affected.\n" ] }, { "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", "
count
1
2
2
2
1
2
" ], "text/plain": [ "[(1L,), (2L,), (2L,), (2L,), (1L,), (2L,)]" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT COUNT(*)\n", "FROM jadralec\n", "GROUP BY rating\n", "ORDER BY rating ASC;\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Izziv: V poizvedbi nekateri ratingi manjkajo. Zakaj?
Smiselno dopolnite poizvedbo, da bodo zastopani vsi! \n", "\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "---------------------------------\n", "\n", "---------------------------------\n", "\n", "# HAVING: Izpiši število čolnov vsake barve, vendar le za tiste barve, kjer imamo več kot en čoln.\n" ] }, { "cell_type": "code", "execution_count": 30, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
barvacount
rdeca2
" ], "text/plain": [ "[(u'rdeca', 2L)]" ] }, "execution_count": 30, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT barva, COUNT(*)\n", "FROM coln\n", "GROUP BY barva\n", "HAVING COUNT(*) > 1;\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Naloga: Za vsak rating v tabeli jadralcev izpiši starost najmlajšega polnoletnega jadralca s tem ratingom, vendar samo za tiste ratinge, ki jih imata vsaj dva polnoletna jadralca!\n" ] }, { "cell_type": "code", "execution_count": 31, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "3 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ratingnajmlajsi
325.5
735.0
825.5
" ], "text/plain": [ "[(3, 25.5), (7, 35.0), (8, 25.5)]" ] }, "execution_count": 31, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT rating, MIN(starost) AS najmlajsi \n", "FROM jadralec\n", "WHERE starost >= 18\n", "GROUP BY rating\n", "HAVING COUNT(*) > 1\n", "ORDER BY rating;\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Naloga: Za vsako desetletje starosti (1-10, 11-20, ...) izpiši število jadralcev.\n", "\n", "Problem: kako grupiramo po *intervalu* in ne po *vrednosti*?" ] }, { "cell_type": "code", "execution_count": 32, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "7 rows affected.\n" ] }, { "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", "
staroststevilo
16.01
25.52
33.01
35.03
45.01
55.51
63.51
" ], "text/plain": [ "[(16.0, 1L),\n", " (25.5, 2L),\n", " (33.0, 1L),\n", " (35.0, 3L),\n", " (45.0, 1L),\n", " (55.5, 1L),\n", " (63.5, 1L)]" ] }, "execution_count": 32, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT starost, COUNT(*) AS stevilo\n", "FROM jadralec\n", "GROUP BY starost\n", "ORDER BY starost ASC;\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Vmesni korak: v izpis dodamo nov atribut, po katerebm bi lahko grupirali." ] }, { "cell_type": "code", "execution_count": 33, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "10 rows affected.\n" ] }, { "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", "
jidimeratingstarostdesetletje
71Zdravko1016.01
85Anze325.52
32Andrej825.52
29Borut133.03
74Henrik935.03
58Rajko1035.03
64Henrik735.03
22Darko745.04
31Lojze855.55
95Bine363.56
" ], "text/plain": [ "[(71, u'Zdravko', 10, 16.0, 1),\n", " (85, u'Anze', 3, 25.5, 2),\n", " (32, u'Andrej', 8, 25.5, 2),\n", " (29, u'Borut', 1, 33.0, 3),\n", " (74, u'Henrik', 9, 35.0, 3),\n", " (58, u'Rajko', 10, 35.0, 3),\n", " (64, u'Henrik', 7, 35.0, 3),\n", " (22, u'Darko', 7, 45.0, 4),\n", " (31, u'Lojze', 8, 55.5, 5),\n", " (95, u'Bine', 3, 63.5, 6)]" ] }, "execution_count": 33, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT jadralec.*, CAST( FLOOR(starost/10) AS INT) AS desetletje\n", "FROM jadralec\n", "ORDER BY starost ASC;\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Ideja: gnezdena poizvedba v **FROM** vrstici!\n", "* rezultat poizvedbe je tabela; zakaj ga ne bi mogli uporabiti v **FROM** vrstici kot začasno tabelo?\n", "* gnezdeno poizvedbo v **FROM** vrstici je pametno eksplicitno poimenovati (s sinonimom oz. aliasom)" ] }, { "cell_type": "code", "execution_count": 34, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "6 rows affected.\n" ] }, { "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", "
desetletjestevilo
10-201
20-302
30-404
40-501
50-601
60-701
" ], "text/plain": [ "[(u'10-20', 1L),\n", " (u'20-30', 2L),\n", " (u'30-40', 4L),\n", " (u'40-50', 1L),\n", " (u'50-60', 1L),\n", " (u'60-70', 1L)]" ] }, "execution_count": 34, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "SELECT CONCAT(\n", " CAST((d.desetletje*10) AS CHAR(2)), '-', CAST((d.desetletje+1)*10 AS CHAR(2))\n", " ) AS Desetletje, COUNT(*) AS Stevilo\n", "FROM (\n", " SELECT *, CAST( FLOOR(starost/10) AS INT ) AS desetletje\n", " FROM jadralec\n", " ) AS d\n", "GROUP BY d.desetletje\n", "ORDER BY d.desetletje\n", "\n", "\n", "\n", "\n", "\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Gre tudi krajše, a manj pregledno in učinkovito (grupiranje in urejanje po nizu znakov)." ] }, { "cell_type": "code", "execution_count": 35, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "6 rows affected.\n" ] }, { "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", "
desetletjestevilo
10-201
20-302
30-404
40-501
50-601
60-701
" ], "text/plain": [ "[(u'10-20', 1L),\n", " (u'20-30', 2L),\n", " (u'30-40', 4L),\n", " (u'40-50', 1L),\n", " (u'50-60', 1L),\n", " (u'60-70', 1L)]" ] }, "execution_count": 35, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT CONCAT(\n", " CAST((CAST( FLOOR(starost/10) AS INT )*10) AS CHAR(2)), '-', CAST((CAST( FLOOR(starost/10) AS INT )+1)*10 AS CHAR(2))\n", " ) AS Desetletje, COUNT(*) AS Stevilo\n", "FROM jadralec\n", "GROUP BY Desetletje\n", "ORDER BY Desetletje\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Primer s CTE." ] }, { "cell_type": "code", "execution_count": 36, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "6 rows affected.\n" ] }, { "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", "
desetletjestevilo
10-201
20-302
30-404
40-501
50-601
60-701
" ], "text/plain": [ "[(u'10-20', 1L),\n", " (u'20-30', 2L),\n", " (u'30-40', 4L),\n", " (u'40-50', 1L),\n", " (u'50-60', 1L),\n", " (u'60-70', 1L)]" ] }, "execution_count": 36, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql pb@pb\n", "\n", "WITH d AS(\n", " SELECT *, CAST( FLOOR(starost/10) AS INT ) AS desetletje\n", " FROM jadralec\n", " ) \n", "SELECT CONCAT(\n", " CAST((d.desetletje*10) AS CHAR(2)), '-', CAST((d.desetletje+1)*10 AS CHAR(2))\n", " ) AS Desetletje, COUNT(*) AS Stevilo\n", "FROM d\n", "GROUP BY d.desetletje\n", "ORDER BY d.desetletje\n", "\n" ] } ], "metadata": { "celltoolbar": "Edit Metadata", "kernelspec": { "display_name": "Python 3", "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.8.5" } }, "nbformat": 4, "nbformat_minor": 1 }