{
"cells": [
{
"cell_type": "code",
"execution_count": 10,
"metadata": {
"collapsed": false,
"deletable": true,
"editable": true
},
"outputs": [
{
"data": {
"text/plain": [
"'Connected: None@None'"
]
},
"execution_count": 10,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%reload_ext sql\n",
"%sql sqlite://"
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {
"collapsed": false,
"deletable": true,
"editable": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n",
"Done.\n"
]
},
{
"data": {
"text/plain": [
"[]"
]
},
"execution_count": 11,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql DROP TABLE IF EXISTS students;\n",
"CREATE TABLE students(\n",
" student_id INT PRIMARY KEY,\n",
" name VARCHAR(1000),\n",
" station_id INT,\n",
" state_code INT,\n",
" start_hour INT,\n",
" end_hour INT,\n",
" off_day INT\n",
")"
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {
"collapsed": false,
"deletable": true,
"editable": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/html": [
"
\n",
" \n",
" cid | \n",
" name | \n",
" type | \n",
" notnull | \n",
" dflt_value | \n",
" pk | \n",
"
\n",
" \n",
" 0 | \n",
" student_id | \n",
" INT | \n",
" 0 | \n",
" None | \n",
" 1 | \n",
"
\n",
" \n",
" 1 | \n",
" name | \n",
" VARCHAR(1000) | \n",
" 0 | \n",
" None | \n",
" 0 | \n",
"
\n",
" \n",
" 2 | \n",
" station_id | \n",
" INT | \n",
" 0 | \n",
" None | \n",
" 0 | \n",
"
\n",
" \n",
" 3 | \n",
" state_code | \n",
" INT | \n",
" 0 | \n",
" None | \n",
" 0 | \n",
"
\n",
" \n",
" 4 | \n",
" start_hour | \n",
" INT | \n",
" 0 | \n",
" None | \n",
" 0 | \n",
"
\n",
" \n",
" 5 | \n",
" end_hour | \n",
" INT | \n",
" 0 | \n",
" None | \n",
" 0 | \n",
"
\n",
" \n",
" 6 | \n",
" off_day | \n",
" INT | \n",
" 0 | \n",
" None | \n",
" 0 | \n",
"
\n",
"
"
],
"text/plain": [
"[(0, 'student_id', 'INT', 0, None, 1),\n",
" (1, 'name', 'VARCHAR(1000)', 0, None, 0),\n",
" (2, 'station_id', 'INT', 0, None, 0),\n",
" (3, 'state_code', 'INT', 0, None, 0),\n",
" (4, 'start_hour', 'INT', 0, None, 0),\n",
" (5, 'end_hour', 'INT', 0, None, 0),\n",
" (6, 'off_day', 'INT', 0, None, 0)]"
]
},
"execution_count": 12,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql PRAGMA table_info(students);"
]
},
{
"cell_type": "code",
"execution_count": 13,
"metadata": {
"collapsed": false,
"deletable": true,
"editable": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n",
"Done.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n"
]
},
{
"data": {
"text/plain": [
"[]"
]
},
"execution_count": 13,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql DROP TABLE IF EXISTS product;\n",
"create table product(\n",
" pname varchar primary key, -- name of the product\n",
" price money, -- price of the product\n",
" category varchar, -- category\n",
" manufacturer varchar NOT NULL -- manufacturer\n",
");\n",
"insert into product values('Gizmo', 19.99, 'Gadgets', 'GizmoWorks');\n",
"insert into product values('PowerGizmo', 29.99, 'Gadgets', 'GizmoWorks');\n",
"insert into product values('MultiTouch', 203.99, 'Household', 'Hitachi');\n",
"insert into product values('SingleTouch', 149.99, 'Photography', 'Canon');"
]
},
{
"cell_type": "code",
"execution_count": 14,
"metadata": {
"collapsed": false,
"deletable": true,
"editable": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"1 rows affected.\n"
]
},
{
"data": {
"text/plain": [
"[]"
]
},
"execution_count": 14,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql insert into product values('ingleTouch', 149.99, 'Potography', 'Canon');"
]
},
{
"cell_type": "markdown",
"metadata": {
"deletable": true,
"editable": true
},
"source": [
"Activity 2-2:\n",
"Single table queries"
]
},
{
"cell_type": "markdown",
"metadata": {
"deletable": true,
"editable": true
},
"source": [
"Exercise #1\n",
"Try writing a query to get an output table of all the products with \"Touch\" in the name, showing just their name and price, and sorted alphabetically by manufacturer."
]
},
{
"cell_type": "code",
"execution_count": 15,
"metadata": {
"collapsed": false,
"deletable": true,
"editable": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/html": [
"\n",
" \n",
" pname | \n",
" price | \n",
" category | \n",
" manufacturer | \n",
"
\n",
" \n",
" Gizmo | \n",
" 19.99 | \n",
" Gadgets | \n",
" GizmoWorks | \n",
"
\n",
" \n",
" PowerGizmo | \n",
" 29.99 | \n",
" Gadgets | \n",
" GizmoWorks | \n",
"
\n",
" \n",
" MultiTouch | \n",
" 203.99 | \n",
" Household | \n",
" Hitachi | \n",
"
\n",
" \n",
" SingleTouch | \n",
" 149.99 | \n",
" Photography | \n",
" Canon | \n",
"
\n",
" \n",
" ingleTouch | \n",
" 149.99 | \n",
" Potography | \n",
" Canon | \n",
"
\n",
"
"
],
"text/plain": [
"[('Gizmo', 19.99, 'Gadgets', 'GizmoWorks'),\n",
" ('PowerGizmo', 29.99, 'Gadgets', 'GizmoWorks'),\n",
" ('MultiTouch', 203.99, 'Household', 'Hitachi'),\n",
" ('SingleTouch', 149.99, 'Photography', 'Canon'),\n",
" ('ingleTouch', 149.99, 'Potography', 'Canon')]"
]
},
"execution_count": 15,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql select * from product;"
]
},
{
"cell_type": "code",
"execution_count": 16,
"metadata": {
"collapsed": false,
"deletable": true,
"editable": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/html": [
"\n",
" \n",
" pname | \n",
" price | \n",
"
\n",
"
"
],
"text/plain": [
"[]"
]
},
"execution_count": 16,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"SELECT pname, price FROM product\n",
"WHERE pname LIKE '%k%'\n",
"ORDER BY manufacturer DESC;"
]
},
{
"cell_type": "markdown",
"metadata": {
"deletable": true,
"editable": true
},
"source": [
"Next, write a query that returns the _distinct_ names of manufacturers that make products with \"Gizmo\" in the name:"
]
},
{
"cell_type": "code",
"execution_count": 17,
"metadata": {
"collapsed": false,
"deletable": true,
"editable": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/html": [
"\n",
" \n",
" manufacturer | \n",
"
\n",
" \n",
" GizmoWorks | \n",
"
\n",
"
"
],
"text/plain": [
"[('GizmoWorks',)]"
]
},
"execution_count": 17,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"SELECT DISTINCT manufacturer FROM product\n",
"WHERE pname LIKE '%Gizmo%';"
]
},
{
"cell_type": "markdown",
"metadata": {
"deletable": true,
"editable": true
},
"source": [
"Exercise #2:\n",
"More on ORDER BY\n",
"Try some of these queries but first guess what they return.\n",
"\n"
]
},
{
"cell_type": "code",
"execution_count": 18,
"metadata": {
"collapsed": false,
"deletable": true,
"editable": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/html": [
"\n",
" \n",
" category | \n",
"
\n",
" \n",
" Gadgets | \n",
"
\n",
" \n",
" Household | \n",
"
\n",
" \n",
" Photography | \n",
"
\n",
" \n",
" Potography | \n",
"
\n",
"
"
],
"text/plain": [
"[('Gadgets',), ('Household',), ('Photography',), ('Potography',)]"
]
},
"execution_count": 18,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql SELECT DISTINCT category FROM product ORDER BY category;"
]
},
{
"cell_type": "code",
"execution_count": 19,
"metadata": {
"collapsed": false,
"deletable": true,
"editable": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/html": [
"\n",
" \n",
" category | \n",
"
\n",
" \n",
" Gadgets | \n",
"
\n",
" \n",
" Household | \n",
"
\n",
" \n",
" Gadgets | \n",
"
\n",
" \n",
" Photography | \n",
"
\n",
" \n",
" Potography | \n",
"
\n",
"
"
],
"text/plain": [
"[('Gadgets',), ('Household',), ('Gadgets',), ('Photography',), ('Potography',)]"
]
},
"execution_count": 19,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql SELECT category FROM product ORDER BY pname;"
]
},
{
"cell_type": "code",
"execution_count": 20,
"metadata": {
"collapsed": false,
"deletable": true,
"editable": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/html": [
"\n",
" \n",
" category | \n",
"
\n",
" \n",
" Gadgets | \n",
"
\n",
" \n",
" Household | \n",
"
\n",
" \n",
" Photography | \n",
"
\n",
" \n",
" Potography | \n",
"
\n",
"
"
],
"text/plain": [
"[('Gadgets',), ('Household',), ('Photography',), ('Potography',)]"
]
},
"execution_count": 20,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql SELECT DISTINCT category FROM product ORDER BY pname;"
]
},
{
"cell_type": "markdown",
"metadata": {
"deletable": true,
"editable": true
},
"source": [
"More"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {
"collapsed": false,
"deletable": true,
"editable": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n",
"Done.\n",
"Done.\n",
"Done.\n",
"Done.\n",
"Done.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n"
]
}
],
"source": [
"# Create tables & insert some random numbers\n",
"# Note: in Postgresql, try the generate_series function...\n",
"%sql DROP TABLE IF EXISTS R; DROP TABLE IF EXISTS S; DROP TABLE IF EXISTS T;\n",
"%sql CREATE TABLE R (A int); CREATE TABLE S (A int); CREATE TABLE T (A int);\n",
"for i in range(1,6):\n",
" %sql INSERT INTO R VALUES (:i)\n",
"for i in range(1,10,2):\n",
" %sql INSERT INTO S VALUES (:i)\n",
"for i in range(1,11,3):\n",
" %sql INSERT INTO T VALUES (:i)"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {
"collapsed": false,
"deletable": true,
"editable": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/html": [
"\n",
" \n",
" A | \n",
"
\n",
" \n",
" 1 | \n",
"
\n",
" \n",
" 2 | \n",
"
\n",
" \n",
" 3 | \n",
"
\n",
" \n",
" 4 | \n",
"
\n",
" \n",
" 5 | \n",
"
\n",
"
"
],
"text/plain": [
"[(1,), (2,), (3,), (4,), (5,)]"
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql select * from R;\n"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {
"collapsed": false,
"deletable": true,
"editable": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/html": [
"\n",
" \n",
" A | \n",
"
\n",
" \n",
" 1 | \n",
"
\n",
" \n",
" 3 | \n",
"
\n",
" \n",
" 5 | \n",
"
\n",
" \n",
" 7 | \n",
"
\n",
" \n",
" 9 | \n",
"
\n",
"
"
],
"text/plain": [
"[(1,), (3,), (5,), (7,), (9,)]"
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql select * from S;\n"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {
"collapsed": false,
"deletable": true,
"editable": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/html": [
"\n",
" \n",
" A | \n",
"
\n",
" \n",
" 1 | \n",
"
\n",
" \n",
" 4 | \n",
"
\n",
" \n",
" 7 | \n",
"
\n",
" \n",
" 10 | \n",
"
\n",
"
"
],
"text/plain": [
"[(1,), (4,), (7,), (10,)]"
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql select * from T;\n"
]
},
{
"cell_type": "markdown",
"metadata": {
"deletable": true,
"editable": true
},
"source": [
"Activity:\n",
"Multi-table queries"
]
},
{
"cell_type": "markdown",
"metadata": {
"deletable": true,
"editable": true
},
"source": [
"Exercise #1:\n",
"For three tables R,S,T that only have one attribute A:\n",
"R = {1,2,3,4,5}\n",
"S = {1,3,5,7,9}\n",
"T = {1,4,7,10}\n",
"Can you write a query to select R∩(S∪T)- in other words elements that are in R and either S or T?\n",
"Write your query here:"
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/html": [
"\n",
" \n",
" A | \n",
"
\n",
" \n",
" 1 | \n",
"
\n",
" \n",
" 3 | \n",
"
\n",
" \n",
" 4 | \n",
"
\n",
" \n",
" 5 | \n",
"
\n",
"
"
],
"text/plain": [
"[(1,), (3,), (4,), (5,)]"
]
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"SELECT DISTINCT R.A\n",
"FROM R, S, T\n",
"WHERE R.A = S.A OR R.A = T.A;"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {
"collapsed": false,
"deletable": true,
"editable": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n",
"Done.\n",
"Done.\n",
"Done.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"Done.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n"
]
},
{
"data": {
"text/plain": [
"[]"
]
},
"execution_count": 7,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"drop table if exists product; -- This needs to be dropped if exists, see why further down!\n",
"drop table if exists company;\n",
"pragma foreign_keys = ON; -- WARNING by default off in sqlite\n",
"create table company (\n",
" cname varchar primary key, -- company name uniquely identifies the company.\n",
" stockprice money, -- stock price is in money \n",
" country varchar); -- country is just a string\n",
"insert into company values ('ToyWorks', 25.0, 'USA');\n",
"insert into company values ('ToyFriends', 65.0, 'China');\n",
"insert into company values ('ToyCo', 15.0, 'China');\n",
"\n",
"create table product(\n",
" pname varchar, -- name of the product\n",
" price money, -- price of the product\n",
" category varchar, -- category\n",
" manufacturer varchar, -- manufacturer\n",
" primary key (pname, manufacturer),\n",
" foreign key (manufacturer) references company(cname));\n",
"insert into product values('Pikachu', 19.99, 'Toy', 'ToyWorks');\n",
"insert into product values('Pikachu', 19.99, 'Toy', 'ToyFriends');\n",
"insert into product values('Pokeball', 29.99, 'Electronic', 'ToyCo');\n",
"insert into product values('Bulbasaur', 149.99, 'Toy', 'ToyFriends');\n",
"insert into product values('Charizard', 203.99, 'Toy', 'ToyCo');\n",
"insert into product values('PokeCamera', 19.99, 'Electronic', 'ToyWorks');"
]
}
],
"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.5.2"
}
},
"nbformat": 4,
"nbformat_minor": 0
}