{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "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": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "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": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "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');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Activity 2-3:\n",
    "-------------\n",
    "\n",
    "Multi-table queries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Exercise #1:\n",
    "-----------\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",
    " \n",
    "Can you write a query to select $R \\cap (S \\cup T)$- in other words elements that are in $R$ and either $S$ or $T$?\n",
    "\n",
    "Write your query here:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "Now test your query above for the case where $S = \\emptyset$- what happens and why?\n",
    "\n",
    "Execute the below, then re-run your query above"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "delete from S;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Exercise #2\n",
    "-----------\n",
    "\n",
    "* Schema is same as before\n",
    "\n",
    "> Product (<u>pname</u>,  price, category, manufacturer)<br>\n",
    "> Company (<u>cname</u>, stockPrice, country)\n",
    "\n",
    "* Our goal is to answer the following question:\n",
    "\n",
    "> Find all categories of products that are made by Chinese companies\n",
    "\n",
    "Write your query here:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "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": 0
}