{ "cells": [ { "cell_type": "code", "execution_count": 1, "metadata": { "slideshow": { "slide_type": "slide" } }, "outputs": [ { "data": { "text/plain": [ "'Connected: @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": [ " * sqlite://\n", "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." ] }, { "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": [ " * sqlite://\n", "Done.\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", "
pnamepricecategorymanufacturer
Gizmo19.99GadgetsGizmoWorks
PowerGizmo29.99GadgetsGizmoWorks
MultiTouch203.99HouseholdHitachi
SingleTouch149.99PhotographyCanon
" ], "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": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
pnameprice
SingleTouch149.99
MultiTouch203.99
" ], "text/plain": [ "[(u'SingleTouch', 149.99), (u'MultiTouch', 203.99)]" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT pname, price 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": 5, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
manufacturer
GizmoWorks
" ], "text/plain": [ "[(u'GizmoWorks',)]" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT DISTINCT manufacturer FROM product\n", "WHERE pname LIKE '%Gizmo%';" ] }, { "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." ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "slideshow": { "slide_type": "subslide" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
category
Gadgets
Household
Photography
" ], "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 category;" ] }, { "cell_type": "code", "execution_count": 7, "metadata": { "slideshow": { "slide_type": "subslide" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
category
Gadgets
Household
Gadgets
Photography
" ], "text/plain": [ "[(u'Gadgets',), (u'Household',), (u'Gadgets',), (u'Photography',)]" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT category FROM product ORDER BY pname;" ] }, { "cell_type": "code", "execution_count": 8, "metadata": { "slideshow": { "slide_type": "subslide" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
category
Gadgets
Household
Photography
" ], "text/plain": [ "[(u'Gadgets',), (u'Household',), (u'Photography',)]" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT DISTINCT category FROM product ORDER BY pname;" ] } ], "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.15" } }, "nbformat": 4, "nbformat_minor": 1 }