{ "cells": [ { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "skip" } }, "source": [ "NOTE:\n", "-----\n", "\n", "Please run the below cells first before proceeding- you'll need them soon!" ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "slideshow": { "slide_type": "skip" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "The sql extension is already loaded. To reload it, use:\n", " %reload_ext sql\n" ] }, { "data": { "text/plain": [ "u'Connected: None@dataset_1.db'" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%load_ext sql\n", "%sql sqlite:///dataset_1.db" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Activity 5-1\n", "------------\n", "Quantifiers, NULLs, and Outer Joins" ] }, { "cell_type": "markdown", "metadata": { "collapsed": true }, "source": [ "Exercise #1\n", "-----------\n", "\n", "Recall that the tables we just looked at:\n", "\n", "`bagel`, which describes types of bagels made by the different bagel companies:\n", "> * name STRING\n", "> * price FLOAT\n", "> * made_by STRING\n", "\n", "And `purchase`:\n", "> * bagel_name STRING\n", "> * franchise STRING\n", "> * date INT\n", "> * quantity INT\n", "> * purchaser_age INT\n", "\n", "Where `purchase.bagel_name` references `bagel.name` and `purchase.franchise` references `bagel.made_by`.\n", "\n", "**Can you find out if there were any purchases of products not on one of the company's official lists (i.e. the `bagel` table), using a single SQL query?**\n", "\n", "Write your query here:" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Official List NamePurchase ItemBought Fromdatequantity
NoneDrugsBAGEL CORP71000
" ], "text/plain": [ "[(None, u'Drugs', u'BAGEL CORP', 7, 1000)]" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT b.name AS \"Official List Name\", p.bagel_name AS \"Purchase Item\", franchise AS \"Bought From\", p.date, p.quantity\n", "FROM purchase p\n", "LEFT OUTER JOIN bagel b\n", " ON b.name = p.bagel_name AND b.made_by = p.franchise\n", "WHERE b.name IS NULL;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**_Oh my!_**" ] } ], "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 }