{
"cells": [
{
"cell_type": "code",
"execution_count": 1,
"metadata": {
"slideshow": {
"slide_type": "slide"
}
},
"outputs": [
{
"data": {
"text/plain": [
"'Connected: None@None'"
]
},
"execution_count": 1,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%load_ext sql\n",
"%sql sqlite://"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {
"slideshow": {
"slide_type": "fragment"
}
},
"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": 2,
"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": "markdown",
"metadata": {},
"source": [
"Activity 2-2:\n",
"-------------\n",
"\n",
"Single table queries"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Exercise #1\n",
"-----------\n",
"\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. Also think about whether or not this query is \"proper\" SQL or not?"
]
},
{
"cell_type": "markdown",
"metadata": {
"slideshow": {
"slide_type": "slide"
}
},
"source": [
"Let's look at the products first:"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {
"slideshow": {
"slide_type": "fragment"
}
},
"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",
"
"
],
"text/plain": [
"[(u'Gizmo', 19.99, u'Gadgets', u'GizmoWorks'),\n",
" (u'PowerGizmo', 29.99, u'Gadgets', u'GizmoWorks'),\n",
" (u'MultiTouch', 203.99, u'Household', u'Hitachi'),\n",
" (u'SingleTouch', 149.99, u'Photography', u'Canon')]"
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql select * from product;"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Write your query here:"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/html": [
"\n",
" \n",
" | pname | \n",
" price | \n",
"
\n",
" \n",
" | SingleTouch | \n",
" 149.99 | \n",
"
\n",
" \n",
" | MultiTouch | \n",
" 203.99 | \n",
"
\n",
"
"
],
"text/plain": [
"[(u'SingleTouch', 149.99), (u'MultiTouch', 203.99)]"
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"SELECT pname, price\n",
"FROM product\n",
"where pname like '%Touch%'\n",
"order by manufacturer;"
]
},
{
"cell_type": "markdown",
"metadata": {},
"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": null,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": []
},
{
"cell_type": "markdown",
"metadata": {
"slideshow": {
"slide_type": "slide"
}
},
"source": [
"Exercise #2:\n",
"------------\n",
"\n",
"_More on ORDER BY_\n",
"\n",
"Try some of these queries but first guess what they return. Which ones are \"proper\" SQL?"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {
"slideshow": {
"slide_type": "subslide"
}
},
"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",
"
"
],
"text/plain": [
"[(u'Gadgets',), (u'Household',), (u'Photography',)]"
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql SELECT DISTINCT category FROM product ORDER BY category;"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {
"slideshow": {
"slide_type": "subslide"
}
},
"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",
"
"
],
"text/plain": [
"[(u'Gadgets',), (u'Household',), (u'Gadgets',), (u'Photography',)]"
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql SELECT category FROM product ORDER BY pname;"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {
"slideshow": {
"slide_type": "subslide"
}
},
"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",
"
"
],
"text/plain": [
"[(u'Gadgets',), (u'Household',), (u'Photography',)]"
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql SELECT DISTINCT category FROM product ORDER BY pname;"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 2",
"language": "python",
"name": "python2"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 2
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython2",
"version": "2.7.13"
}
},
"nbformat": 4,
"nbformat_minor": 1
}