{
 "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": null,
   "metadata": {
    "collapsed": false,
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "%load_ext sql\n",
    "%sql sqlite://"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "DROP TABLE IF EXISTS Movies;\n",
    "CREATE TABLE Movies(title VARCHAR(50), year INT, director VARCHAR(50), length INT);\n",
    "INSERT INTO Movies VALUES('Database Wars', 1967, 'John Joe', 123);\n",
    "INSERT INTO Movies VALUES('The Databaser', 1992, 'John Bob', 190);\n",
    "INSERT INTO Movies VALUES('Database Wars', 1998, 'John Jim', 176);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%sql DROP TABLE IF EXISTS A; DROP TABLE IF EXISTS B;\n",
    "%sql CREATE TABLE A (x int, y int); CREATE TABLE B (x int, y int);\n",
    "for i in range(1,6):\n",
    "    %sql INSERT INTO A VALUES (:i, :i+1)\n",
    "for i in range(1,11,3):\n",
    "    %sql INSERT INTO B VALUES (:i, :i+2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Activity 3-1:\n",
    "------------\n",
    "\n",
    "ORDER BY semantics, set operators & nested queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%sql SELECT * FROM movies"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Exercise #1\n",
    "-----------\n",
    "\n",
    "**Can you write the movie query from lecture as a single SFW query?**\n",
    "\n",
    "Recall that we are trying to find **all movie titles that were used for more than one movie.** You may assume that no two movies in the same year have the same title. Our schema for the `movies` table is:\n",
    "\n",
    "> * title STRING\n",
    "> * year INT\n",
    "> * director STRING\n",
    "> * length INT\n",
    "\n",
    "Let's try to write the nested query that solves this from lecture:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT m.title \n",
    "FROM Movies m\n",
    "WHERE m.year <> ANY(SELECT year FROM Movie WHERE title = m.title);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What?  This doesn't work?  Why?\n",
    "\n",
    "**ANY doesn't exist in SQLite!**  Can we do this query without nesting?  Write your query here:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Exercise #2\n",
    "--------------------\n",
    "\n",
    "Consider the two relations $A$ and $B$ below:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%sql SELECT * FROM A;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%sql SELECT * FROM B;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Assuming no duplicates, can you write an `INTERSECT` query, **just over the $x$ attribute**, without using `INTERSECT` OR nested queries?  Write your query here:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What is this operation called?\n",
    "\n",
    "Next, using set operators again as well, can you return all the _full_ tuples in $A$ and $B$ that overlap in $x$ attributes?  Write your query here:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  }
 ],
 "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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}