{ "cells": [ { "cell_type": "code", "execution_count": 2, "metadata": { "slideshow": { "slide_type": "slide" } }, "outputs": [ { "data": { "text/plain": [ "'Connected: None@None'" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%load_ext sql\n", "%sql sqlite://" ] }, { "cell_type": "code", "execution_count": 4, "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": 4, "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": 5, "metadata": { "slideshow": { "slide_type": "fragment" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "
pname | \n", "price | \n", "category | \n", "manufacturer | \n", "
---|---|---|---|
Gizmo | \n", "19.99 | \n", "Gadgets | \n", "GizmoWorks | \n", "
PowerGizmo | \n", "29.99 | \n", "Gadgets | \n", "GizmoWorks | \n", "
MultiTouch | \n", "203.99 | \n", "Household | \n", "Hitachi | \n", "
SingleTouch | \n", "149.99 | \n", "Photography | \n", "Canon | \n", "