{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "%load_ext sql\n",
    "%sql sqlite://"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "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": {},
   "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": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT R.A\n",
    "FROM R, S, T\n",
    "WHERE R.A = S.A OR R.A = T.A;"
   ]
  },
  {
   "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": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "delete from S;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT R.A\n",
    "FROM R, S, T\n",
    "WHERE R.A = S.A OR R.A = T.A;"
   ]
  },
  {
   "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": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT p.category\n",
    "FROM product p, company c\n",
    "WHERE p.manufacturer = c.cname \n",
    "  AND c.country = 'China';"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}