{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## CS 200: Data Bases and SQL\n",
    "\n",
    "\n",
    "<p>\n",
    "<script language=\"JavaScript\">\n",
    "document.write(\"Last modified: \" + document.lastModified)\n",
    "</script>\n",
    "    \n",
    "    \n",
    "Video: <a target=rr href=\"https://www.youtube.com/playlist?list=PLlsaguFT2REiDlkzxso6xS2-wTHG0oqzs\">Socratica SQL Youtube channel</a>\n",
    "    \n",
    "SQL implements a relational database.  This model was introduced by \n",
    "    <a target=we href=\"https://en.wikipedia.org/wiki/Edgar_F._Codd\">E.F.Codd</a>\n",
    "in his 1970 paper: <a target=qq href=\"https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf\">A Relational Model of Data for\n",
    "Large Shared Data Banks</a>.\n",
    "    \n",
    "This notebook implements the code from  <a target=ww href=\"https://sqlbolt.com/lesson/introduction\">SQL tutorial</a> 18 lessons\n",
    "\n",
    "SQL Keywords (we will not focus on the last four):\n",
    "\n",
    "- CREATE + INTEGER, TEXT, BLOB, PRIMARY KEY, AUTOINCREMENT, UNIQUE, NOT NULL, FOREIGN KEY (referential integrity check)\n",
    "- INSERT INTO ... VALUES\n",
    "- SELECT FROM + WHERE, ORDER BY (DESC), LIMIT, OFFSET, AND, OR, LIKE, IS, IS NOT\n",
    "- JOIN: LEFT, RIGHT, INNER, OUTER, ON\n",
    "- UPDATE ... SET\n",
    "- DELETE FROM\n",
    "- ALTER TABLE + ADD, DROP, RENAME\n",
    "- DROP TABLE\n",
    "    \n",
    "<a target=rr href=\"https://www.pythoncentral.io/introduction-to-sqlite-in-python/\">sqlite3 tutorial</a> There are many out there among which to choose.\n",
    "    \n",
    "<a TARGET=QQ HREF=\"sql.py\">sql.py</a> The source code for most of this notebook."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### An introduction to SQL using Python's sqlite3 module\n",
    "\n",
    "\n",
    "Demonstrates Python module sqlite3 using examples from the <a target=ww href=\"https://sqlbolt.com/lesson/introduction\">SQL tutorial</a>\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "## for capturing exception error messages\n",
    "import sys"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a connection to database: <code>mydb</code> and a cursor to point inside the object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = sqlite3.connect(\"mydb.db\")\n",
    "cursor = connection.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use the CREATE command to build a table with the given fields.  The example database is for movies."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "c1 = \"\"\"\n",
    "CREATE TABLE movies (\n",
    "    id INTEGER PRIMARY KEY,\n",
    "    title TEXT,\n",
    "    director TEXT,\n",
    "    year INTEGER, \n",
    "    length_minutes INTEGER\n",
    ");\n",
    "\"\"\"\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Execute the command c1, after first dropping the movies table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7ff5c47150a0>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(\"drop table movies\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7ff5c47150a0>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(c1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here is how you get column names for a table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def desc(table = 'movies'):\n",
    "    cursor = connection.cursor()\n",
    "    command = \"PRAGMA table_info('{}')\".format(table)\n",
    "    print (\"SQL Command: \", command)\n",
    "    result = cursor.execute(command)\n",
    "    for r in result:\n",
    "        print (r)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  PRAGMA table_info('movies')\n",
      "(0, 'id', 'INTEGER', 0, None, 1)\n",
      "(1, 'title', 'TEXT', 0, None, 0)\n",
      "(2, 'director', 'TEXT', 0, None, 0)\n",
      "(3, 'year', 'INTEGER', 0, None, 0)\n",
      "(4, 'length_minutes', 'INTEGER', 0, None, 0)\n"
     ]
    }
   ],
   "source": [
    "desc()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is advisable to wrap your database commands with try.\n",
    "\n",
    "The output here is hardwired for the movies database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def tryit(command):\n",
    "    cursor = connection.cursor()\n",
    "    try:\n",
    "        cursor.execute(command)\n",
    "        print(\"fetchall: \" + command)\n",
    "        result = cursor.fetchall() \n",
    "        for r in result:\n",
    "            rformat = \"{:3d} {:20} {:15} {:4} {:3}\".format(r[0], \n",
    "                                                           r[1], r[2], r[3], r[4])\n",
    "            print(rformat)\n",
    "        return list(result)\n",
    "    except:\n",
    "        e = sys.exc_info()[0]\n",
    "        print ( \"Error: {}\".format(e))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Below we use the INSERT command to add a record to the movie database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "c2 = \"\"\"\n",
    "INSERT INTO movies\n",
    "(id, title, director, year, length_minutes)\n",
    "VALUES\n",
    "(1, 'Toy Story', 'John Lasseter', 1995, 81);\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7ff5c47150a0>"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(c2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There is now one record in the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "fetchall: select *  from movies\n",
      "  1 Toy Story            John Lasseter   1995  81\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(1, 'Toy Story', 'John Lasseter', 1995, 81)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tryit('select *  from movies')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use Python to insert a bunch of data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "movie_data = [\n",
    "    (1, 'Toy Story', 'John Lasseter', 1995, 81),\n",
    "    (2, \"A Bug's Life\", 'John Lasseter', 1998, 95),\n",
    "    (3, 'Toy Story 2', 'John Lasseter', 1999, 93),\n",
    "    (4,\t\"Monsters, Inc.\",\"Pete Docter\",\t2001, 92),\n",
    "    (5,\t\"Finding Nemo\",\"Andrew Stanton\", 2003, 107),\n",
    "    (6,\t\"The Incredibles\",\"Brad Bird\", 2004, 116),\n",
    "    (7,\t\"Cars\",\"John Lasseter\",\t2006, 117),\n",
    "    (8,\t\"Ratatouille\",\"Brad Bird\", 2007, 115),\n",
    "    (9,\t\"WALL-E\",\"Andrew Stanton\", 2008, 104),\n",
    "    (10, \"Up\",\"Pete Docter\", 2009, 101),\n",
    "    (11, \"Toy Story 3\",\"Lee Unkrich\", 2010, 103),\n",
    "    (12, \"Cars 2\",\"John Lasseter\", 2011, 120),\n",
    "    (13, \"Brave\",\"Brenda Chapman\", 2012, 102),\n",
    "    (14, \"Monsters University\",\"Dan Scanlon\", 2013, 110) ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "def loadmovies():\n",
    "    for m in movie_data:\n",
    "        format_str = \"\"\"INSERT INTO movies \n",
    "        (id, title, director, year, length_minutes)\n",
    "        VALUES ({id}, \"{title}\", \"{director}\", \"{year}\", \"{length_minutes}\");\"\"\"\n",
    "\n",
    "        sql_command = format_str.format(id=m[0], title=m[1], director=m[2], \n",
    "                                        year = m[3], length_minutes = m[4])\n",
    "        cursor.execute(sql_command)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before we add these movies, we should remove the first entry, using the DELETE command."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "ename": "IntegrityError",
     "evalue": "UNIQUE constraint failed: movies.id",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mIntegrityError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[0;32m/tmp/ipykernel_3583178/486945081.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mloadmovies\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m/tmp/ipykernel_3583178/1473066970.py\u001b[0m in \u001b[0;36mloadmovies\u001b[0;34m()\u001b[0m\n\u001b[1;32m      7\u001b[0m         sql_command = format_str.format(id=m[0], title=m[1], director=m[2], \n\u001b[1;32m      8\u001b[0m                                         year = m[3], length_minutes = m[4])\n\u001b[0;32m----> 9\u001b[0;31m         \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msql_command\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mIntegrityError\u001b[0m: UNIQUE constraint failed: movies.id"
     ]
    }
   ],
   "source": [
    "loadmovies()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Aha, we have a duplicate entry from having INSERTed Toy Story before."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "fetchall: select * from movies\n",
      "  1 Toy Story            John Lasseter   1995  81\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(1, 'Toy Story', 'John Lasseter', 1995, 81)]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tryit('select * from movies')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will erase the table using the DELETE command.  Note: WHERE 1=1 is an SQL idiom for everywhere.  That is, it will delete every record."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "fetchall: DELETE FROM movies WHERE 1=1\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tryit('DELETE FROM movies WHERE 1=1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "fetchall: select * from movies\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tryit('select * from movies')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Success!  Now let's try loading the movies again."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "loadmovies()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "fetchall: select * from movies\n",
      "  1 Toy Story            John Lasseter   1995  81\n",
      "  2 A Bug's Life         John Lasseter   1998  95\n",
      "  3 Toy Story 2          John Lasseter   1999  93\n",
      "  4 Monsters, Inc.       Pete Docter     2001  92\n",
      "  5 Finding Nemo         Andrew Stanton  2003 107\n",
      "  6 The Incredibles      Brad Bird       2004 116\n",
      "  7 Cars                 John Lasseter   2006 117\n",
      "  8 Ratatouille          Brad Bird       2007 115\n",
      "  9 WALL-E               Andrew Stanton  2008 104\n",
      " 10 Up                   Pete Docter     2009 101\n",
      " 11 Toy Story 3          Lee Unkrich     2010 103\n",
      " 12 Cars 2               John Lasseter   2011 120\n",
      " 13 Brave                Brenda Chapman  2012 102\n",
      " 14 Monsters University  Dan Scanlon     2013 110\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(1, 'Toy Story', 'John Lasseter', 1995, 81),\n",
       " (2, \"A Bug's Life\", 'John Lasseter', 1998, 95),\n",
       " (3, 'Toy Story 2', 'John Lasseter', 1999, 93),\n",
       " (4, 'Monsters, Inc.', 'Pete Docter', 2001, 92),\n",
       " (5, 'Finding Nemo', 'Andrew Stanton', 2003, 107),\n",
       " (6, 'The Incredibles', 'Brad Bird', 2004, 116),\n",
       " (7, 'Cars', 'John Lasseter', 2006, 117),\n",
       " (8, 'Ratatouille', 'Brad Bird', 2007, 115),\n",
       " (9, 'WALL-E', 'Andrew Stanton', 2008, 104),\n",
       " (10, 'Up', 'Pete Docter', 2009, 101),\n",
       " (11, 'Toy Story 3', 'Lee Unkrich', 2010, 103),\n",
       " (12, 'Cars 2', 'John Lasseter', 2011, 120),\n",
       " (13, 'Brave', 'Brenda Chapman', 2012, 102),\n",
       " (14, 'Monsters University', 'Dan Scanlon', 2013, 110)]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tryit('select * from movies')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This looks good.  Now we will write the database to the file using commit()."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SQL Lesson 1. SELECT Queries 101\n",
    "\n",
    "We define a fetchall() function to query the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "def fetchall(fields = [], table = 'movies'):\n",
    "    cursor = connection.cursor()\n",
    "    fstr = '*'\n",
    "    if fields:\n",
    "        fstr = ', '.join(fields)\n",
    "    command = \"SELECT \" + fstr + \" FROM \" + table\n",
    "    print (\"SQL Command: \", command)\n",
    "    cursor.execute(command)\n",
    "    print(\"fetchall:\")\n",
    "    result = cursor.fetchall() \n",
    "    for r in result:\n",
    "        print(r)\n",
    "    return list(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  SELECT * FROM movies\n",
      "fetchall:\n",
      "(1, 'Toy Story', 'John Lasseter', 1995, 81)\n",
      "(2, \"A Bug's Life\", 'John Lasseter', 1998, 95)\n",
      "(3, 'Toy Story 2', 'John Lasseter', 1999, 93)\n",
      "(4, 'Monsters, Inc.', 'Pete Docter', 2001, 92)\n",
      "(5, 'Finding Nemo', 'Andrew Stanton', 2003, 107)\n",
      "(6, 'The Incredibles', 'Brad Bird', 2004, 116)\n",
      "(7, 'Cars', 'John Lasseter', 2006, 117)\n",
      "(8, 'Ratatouille', 'Brad Bird', 2007, 115)\n",
      "(9, 'WALL-E', 'Andrew Stanton', 2008, 104)\n",
      "(10, 'Up', 'Pete Docter', 2009, 101)\n",
      "(11, 'Toy Story 3', 'Lee Unkrich', 2010, 103)\n",
      "(12, 'Cars 2', 'John Lasseter', 2011, 120)\n",
      "(13, 'Brave', 'Brenda Chapman', 2012, 102)\n",
      "(14, 'Monsters University', 'Dan Scanlon', 2013, 110)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(1, 'Toy Story', 'John Lasseter', 1995, 81),\n",
       " (2, \"A Bug's Life\", 'John Lasseter', 1998, 95),\n",
       " (3, 'Toy Story 2', 'John Lasseter', 1999, 93),\n",
       " (4, 'Monsters, Inc.', 'Pete Docter', 2001, 92),\n",
       " (5, 'Finding Nemo', 'Andrew Stanton', 2003, 107),\n",
       " (6, 'The Incredibles', 'Brad Bird', 2004, 116),\n",
       " (7, 'Cars', 'John Lasseter', 2006, 117),\n",
       " (8, 'Ratatouille', 'Brad Bird', 2007, 115),\n",
       " (9, 'WALL-E', 'Andrew Stanton', 2008, 104),\n",
       " (10, 'Up', 'Pete Docter', 2009, 101),\n",
       " (11, 'Toy Story 3', 'Lee Unkrich', 2010, 103),\n",
       " (12, 'Cars 2', 'John Lasseter', 2011, 120),\n",
       " (13, 'Brave', 'Brenda Chapman', 2012, 102),\n",
       " (14, 'Monsters University', 'Dan Scanlon', 2013, 110)]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  SELECT title, year FROM movies\n",
      "fetchall:\n",
      "('Toy Story', 1995)\n",
      "(\"A Bug's Life\", 1998)\n",
      "('Toy Story 2', 1999)\n",
      "('Monsters, Inc.', 2001)\n",
      "('Finding Nemo', 2003)\n",
      "('The Incredibles', 2004)\n",
      "('Cars', 2006)\n",
      "('Ratatouille', 2007)\n",
      "('WALL-E', 2008)\n",
      "('Up', 2009)\n",
      "('Toy Story 3', 2010)\n",
      "('Cars 2', 2011)\n",
      "('Brave', 2012)\n",
      "('Monsters University', 2013)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[('Toy Story', 1995),\n",
       " (\"A Bug's Life\", 1998),\n",
       " ('Toy Story 2', 1999),\n",
       " ('Monsters, Inc.', 2001),\n",
       " ('Finding Nemo', 2003),\n",
       " ('The Incredibles', 2004),\n",
       " ('Cars', 2006),\n",
       " ('Ratatouille', 2007),\n",
       " ('WALL-E', 2008),\n",
       " ('Up', 2009),\n",
       " ('Toy Story 3', 2010),\n",
       " ('Cars 2', 2011),\n",
       " ('Brave', 2012),\n",
       " ('Monsters University', 2013)]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fetchall(['title', 'year'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  SELECT title, year, year FROM movies\n",
      "fetchall:\n",
      "('Toy Story', 1995, 1995)\n",
      "(\"A Bug's Life\", 1998, 1998)\n",
      "('Toy Story 2', 1999, 1999)\n",
      "('Monsters, Inc.', 2001, 2001)\n",
      "('Finding Nemo', 2003, 2003)\n",
      "('The Incredibles', 2004, 2004)\n",
      "('Cars', 2006, 2006)\n",
      "('Ratatouille', 2007, 2007)\n",
      "('WALL-E', 2008, 2008)\n",
      "('Up', 2009, 2009)\n",
      "('Toy Story 3', 2010, 2010)\n",
      "('Cars 2', 2011, 2011)\n",
      "('Brave', 2012, 2012)\n",
      "('Monsters University', 2013, 2013)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[('Toy Story', 1995, 1995),\n",
       " (\"A Bug's Life\", 1998, 1998),\n",
       " ('Toy Story 2', 1999, 1999),\n",
       " ('Monsters, Inc.', 2001, 2001),\n",
       " ('Finding Nemo', 2003, 2003),\n",
       " ('The Incredibles', 2004, 2004),\n",
       " ('Cars', 2006, 2006),\n",
       " ('Ratatouille', 2007, 2007),\n",
       " ('WALL-E', 2008, 2008),\n",
       " ('Up', 2009, 2009),\n",
       " ('Toy Story 3', 2010, 2010),\n",
       " ('Cars 2', 2011, 2011),\n",
       " ('Brave', 2012, 2012),\n",
       " ('Monsters University', 2013, 2013)]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fetchall(['title','year','year'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We use fetchone to get a single record."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "def fetchone(table = 'movies'):\n",
    "    cursor = connection.cursor()\n",
    "    command = \"SELECT * FROM \" + table\n",
    "    print (\"SQL Command: \", command)\n",
    "    cursor.execute(command)\n",
    "    print(\"fetchone:\")\n",
    "    result = cursor.fetchone() \n",
    "    print (result)\n",
    "    result = cursor.fetchone()\n",
    "    print (result)\n",
    "    return result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  SELECT * FROM movies\n",
      "fetchone:\n",
      "(1, 'Toy Story', 'John Lasseter', 1995, 81)\n",
      "(2, \"A Bug's Life\", 'John Lasseter', 1998, 95)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(2, \"A Bug's Life\", 'John Lasseter', 1998, 95)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fetchone()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SQL Lesson 2: Queries with constraints (Pt. 2)\n",
    "\n",
    "The WHERE clause adds constraints to the query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "def fetchwhere(fields = [], where = [], table = 'movies'):\n",
    "    cursor = connection.cursor()\n",
    "    fstr = '*'\n",
    "    wstr = ' 1=1 '\n",
    "    if fields:\n",
    "        fstr = ', '.join(fields)\n",
    "    if where:\n",
    "        wstr = ' AND '.join(where)\n",
    "    command = \"SELECT \" + fstr + \" FROM \" + table + \" WHERE \" + wstr\n",
    "    print (\"SQL Command: \", command)\n",
    "    cursor.execute(command)\n",
    "    print(\"fetchall:\")\n",
    "    result = cursor.fetchall() \n",
    "    for r in result:\n",
    "        print(r)\n",
    "    return list(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use comparison operators for numeric fields.\n",
    "\n",
    "Multiple where clauses are AND conditions.  Use the OR operator for disjoint conditions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  SELECT title, year FROM movies WHERE year > 2000\n",
      "fetchall:\n",
      "('Monsters, Inc.', 2001)\n",
      "('Finding Nemo', 2003)\n",
      "('The Incredibles', 2004)\n",
      "('Cars', 2006)\n",
      "('Ratatouille', 2007)\n",
      "('WALL-E', 2008)\n",
      "('Up', 2009)\n",
      "('Toy Story 3', 2010)\n",
      "('Cars 2', 2011)\n",
      "('Brave', 2012)\n",
      "('Monsters University', 2013)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[('Monsters, Inc.', 2001),\n",
       " ('Finding Nemo', 2003),\n",
       " ('The Incredibles', 2004),\n",
       " ('Cars', 2006),\n",
       " ('Ratatouille', 2007),\n",
       " ('WALL-E', 2008),\n",
       " ('Up', 2009),\n",
       " ('Toy Story 3', 2010),\n",
       " ('Cars 2', 2011),\n",
       " ('Brave', 2012),\n",
       " ('Monsters University', 2013)]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fetchwhere(['title', 'year'], ['year > 2000'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  SELECT title, year FROM movies WHERE year > 2000 AND year < 2010\n",
      "fetchall:\n",
      "('Monsters, Inc.', 2001)\n",
      "('Finding Nemo', 2003)\n",
      "('The Incredibles', 2004)\n",
      "('Cars', 2006)\n",
      "('Ratatouille', 2007)\n",
      "('WALL-E', 2008)\n",
      "('Up', 2009)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[('Monsters, Inc.', 2001),\n",
       " ('Finding Nemo', 2003),\n",
       " ('The Incredibles', 2004),\n",
       " ('Cars', 2006),\n",
       " ('Ratatouille', 2007),\n",
       " ('WALL-E', 2008),\n",
       " ('Up', 2009)]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fetchwhere(['title', 'year'], ['year > 2000', 'year < 2010'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  SELECT title, year FROM movies WHERE year < 2000 OR year > 2010\n",
      "fetchall:\n",
      "('Toy Story', 1995)\n",
      "(\"A Bug's Life\", 1998)\n",
      "('Toy Story 2', 1999)\n",
      "('Cars 2', 2011)\n",
      "('Brave', 2012)\n",
      "('Monsters University', 2013)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[('Toy Story', 1995),\n",
       " (\"A Bug's Life\", 1998),\n",
       " ('Toy Story 2', 1999),\n",
       " ('Cars 2', 2011),\n",
       " ('Brave', 2012),\n",
       " ('Monsters University', 2013)]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fetchwhere(['title', 'year'], ['year < 2000 OR year > 2010'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SQL Lesson 3: Queries with constraints (Pt. 2)\n",
    "\n",
    "You can use LIKE for pattern matching, with % as a wildcard character."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  SELECT title, director FROM movies WHERE director like \"Doc\"\n",
      "fetchall:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fetchwhere(['title', 'director'], ['director like \"Doc\"'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  SELECT title, director FROM movies WHERE director like \"Pete%\"\n",
      "fetchall:\n",
      "('Monsters, Inc.', 'Pete Docter')\n",
      "('Up', 'Pete Docter')\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[('Monsters, Inc.', 'Pete Docter'), ('Up', 'Pete Docter')]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fetchwhere(['title', 'director'], ['director like \"Pete%\"'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  SELECT title, director FROM movies WHERE director like \"%Doc%\"\n",
      "fetchall:\n",
      "('Monsters, Inc.', 'Pete Docter')\n",
      "('Up', 'Pete Docter')\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[('Monsters, Inc.', 'Pete Docter'), ('Up', 'Pete Docter')]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fetchwhere(['title', 'director'], ['director like \"%Doc%\"'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SQL Lesson 4: Filtering and sorting Query results\n",
    "\n",
    "We use the ORDER BY keywords to sort the output, and the LIMIT keyword to cap the number of results.\n",
    "\n",
    "We incorporate both in fetchwheresort()."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "def fetchwheresort(fields = [], where = [], sort = [], limit = [], table = 'movies'):\n",
    "    cursor = connection.cursor()\n",
    "    fstr = '*'\n",
    "    wstr = ' 1=1 '\n",
    "    sstr = ''\n",
    "    if fields:\n",
    "        fstr = ', '.join(fields)\n",
    "    if where:\n",
    "        wstr = ' AND '.join(where)\n",
    "    command = \"SELECT \" + fstr + \" FROM \" + table + \" WHERE \" + wstr\n",
    "    if sort:\n",
    "        sstr = ', '.join(sort)\n",
    "        command += \" ORDER BY \" + sstr\n",
    "    if limit:\n",
    "        command += \" LIMIT \" + str(limit)\n",
    "    print (\"SQL Command: \", command)\n",
    "    cursor.execute(command)\n",
    "    print(\"fetchall:\")\n",
    "    result = cursor.fetchall() \n",
    "    for r in result:\n",
    "        print(r)\n",
    "    return list(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  SELECT * FROM movies WHERE year > 2000 ORDER BY title LIMIT 4\n",
      "fetchall:\n",
      "(13, 'Brave', 'Brenda Chapman', 2012, 102)\n",
      "(7, 'Cars', 'John Lasseter', 2006, 117)\n",
      "(12, 'Cars 2', 'John Lasseter', 2011, 120)\n",
      "(5, 'Finding Nemo', 'Andrew Stanton', 2003, 107)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(13, 'Brave', 'Brenda Chapman', 2012, 102),\n",
       " (7, 'Cars', 'John Lasseter', 2006, 117),\n",
       " (12, 'Cars 2', 'John Lasseter', 2011, 120),\n",
       " (5, 'Finding Nemo', 'Andrew Stanton', 2003, 107)]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fetchwheresort([], ['year > 2000'], ['title'], 4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3 id=\"join\">SQL Lesson 6: Multi-table queries with JOINs</h3>\n",
    "\n",
    "\n",
    "Joins are commonly used to link together tables.\n",
    "\n",
    "For example, you might have the following tables:\n",
    "\n",
    "<pre>\n",
    "students (id, name, DOB, college, etc.)\n",
    "course (id, title, department, hours, credithours, etc.)\n",
    "classroom (id, building, room, size, AV, etc.)\n",
    "</pre>\n",
    "\n",
    "Then you would have other tables linking the above:\n",
    "\n",
    "<pre>\n",
    "enrollment (student.id, course.id, term, status, etc.)\n",
    "assignedrooms (course.id, classroom.id, time)\n",
    "</pre>\n",
    "\n",
    "This way you can have one student enrolled in multiple courses in\n",
    "multiple terms, and have a classroom allocated to multiple courses\n",
    "over time.\n",
    "\n",
    "This approach is known as normalization.\n",
    "\n",
    "See https://en.wikipedia.org/wiki/Database_normalization .\n",
    "\n",
    "Below we create a new table of box office revenue for movies, both domestic and international sales. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "c10 = \"\"\"\n",
    "CREATE TABLE Boxoffice (\n",
    "    Movie_id INTEGER PRIMARY KEY,\n",
    "    Rating float,\n",
    "    Domestic_sales integer,\n",
    "    International_sales integer\n",
    ");\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7f54854bdb90>"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute('DROP TABLE Boxoffice')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7f54854bdb90>"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(c10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  PRAGMA table_info('boxoffice')\n",
      "(0, 'Movie_id', 'INTEGER', 0, None, 1)\n",
      "(1, 'Rating', 'float', 0, None, 0)\n",
      "(2, 'Domestic_sales', 'integer', 0, None, 0)\n",
      "(3, 'International_sales', 'integer', 0, None, 0)\n"
     ]
    }
   ],
   "source": [
    "desc('boxoffice')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "'''\n",
    "Movie_id\tRating\tDomestic_sales\tInternational_sales\n",
    "5\t8.2\t380843261\t555900000\n",
    "14\t7.4\t268492764\t475066843\n",
    "8\t8\t206445654\t417277164\n",
    "12\t6.4\t191452396\t368400000\n",
    "3\t7.9\t245852179\t239163000\n",
    "6\t8\t261441092\t370001000\n",
    "9\t8.5\t223808164\t297503696\n",
    "11\t8.4\t415004880\t648167031\n",
    "1\t8.3\t191796233\t170162503\n",
    "7\t7.2\t244082982\t217900167\n",
    "10\t8.3\t293004164\t438338580\n",
    "4\t8.1\t289916256\t272900000\n",
    "2\t7.2\t162798565\t200600000\n",
    "13\t7.2\t237283207\t301700000\n",
    "'''\n",
    "\n",
    "boxoffice_data = [\n",
    "    (5,8.2,380843261,555900000),\n",
    "    (14,7.4,268492764,475066843),\n",
    "    (8,8,206445654,417277164),\n",
    "    (12,6.4,191452396,368400000),\n",
    "    (3,7.9,245852179,239163000),\n",
    "    (6,8,261441092,370001000),\n",
    "    (9,8.5,223808164,297503696),\n",
    "    (11,8.4,415004880,648167031),\n",
    "    (1,8.3,191796233,170162503),\n",
    "    (7,7.2,244082982,217900167),\n",
    "    (10,8.3,293004164,438338580),\n",
    "    (4,8.1,289916256,272900000),\n",
    "    (2,7.2,162798565,200600000),\n",
    "    (13,7.2,237283207,301700000) ]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "def loadboxoffice():\n",
    "    for m in boxoffice_data:\n",
    "        format_str = \"\"\"INSERT INTO Boxoffice\n",
    "        (Movie_id, Rating, Domestic_sales, International_sales)\n",
    "        VALUES ({id}, {rating}, {domestic}, {international});\"\"\"\n",
    "\n",
    "        sql_command = format_str.format(id=m[0], rating=m[1],\n",
    "                                        domestic=m[2], international= m[3])\n",
    "        cursor.execute(sql_command)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "loadboxoffice()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  SELECT * FROM boxoffice\n",
      "fetchall:\n",
      "(1, 8.3, 191796233, 170162503)\n",
      "(2, 7.2, 162798565, 200600000)\n",
      "(3, 7.9, 245852179, 239163000)\n",
      "(4, 8.1, 289916256, 272900000)\n",
      "(5, 8.2, 380843261, 555900000)\n",
      "(6, 8.0, 261441092, 370001000)\n",
      "(7, 7.2, 244082982, 217900167)\n",
      "(8, 8.0, 206445654, 417277164)\n",
      "(9, 8.5, 223808164, 297503696)\n",
      "(10, 8.3, 293004164, 438338580)\n",
      "(11, 8.4, 415004880, 648167031)\n",
      "(12, 6.4, 191452396, 368400000)\n",
      "(13, 7.2, 237283207, 301700000)\n",
      "(14, 7.4, 268492764, 475066843)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(1, 8.3, 191796233, 170162503),\n",
       " (2, 7.2, 162798565, 200600000),\n",
       " (3, 7.9, 245852179, 239163000),\n",
       " (4, 8.1, 289916256, 272900000),\n",
       " (5, 8.2, 380843261, 555900000),\n",
       " (6, 8.0, 261441092, 370001000),\n",
       " (7, 7.2, 244082982, 217900167),\n",
       " (8, 8.0, 206445654, 417277164),\n",
       " (9, 8.5, 223808164, 297503696),\n",
       " (10, 8.3, 293004164, 438338580),\n",
       " (11, 8.4, 415004880, 648167031),\n",
       " (12, 6.4, 191452396, 368400000),\n",
       " (13, 7.2, 237283207, 301700000),\n",
       " (14, 7.4, 268492764, 475066843)]"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fetchall([],'boxoffice')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  SELECT * FROM Boxoffice WHERE  1=1  LIMIT 4\n",
      "fetchall:\n",
      "(1, 8.3, 191796233, 170162503)\n",
      "(2, 7.2, 162798565, 200600000)\n",
      "(3, 7.9, 245852179, 239163000)\n",
      "(4, 8.1, 289916256, 272900000)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(1, 8.3, 191796233, 170162503),\n",
       " (2, 7.2, 162798565, 200600000),\n",
       " (3, 7.9, 245852179, 239163000),\n",
       " (4, 8.1, 289916256, 272900000)]"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fetchwheresort([], [], [], 4, \"Boxoffice\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We create fectchwherejoin() to JOIN the movies table with the boxoffice table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "def fetchwherejoin(fields = [], where = [], sort = [], limit = [], \n",
    "                   table = 'movies', join = []):\n",
    "    cursor = connection.cursor()\n",
    "    fstr = '*'\n",
    "    wstr = ' 1=1 '\n",
    "    sstr = ''\n",
    "    if fields:\n",
    "        fstr = ', '.join(fields)\n",
    "    command = \"SELECT \" + fstr + \" FROM \" + table \n",
    "    ## can handle LEFT, RIGHT, OUTER joins\n",
    "    ## default is INNER join\n",
    "    if join:\n",
    "        if (join.lower().find('join') < 0):\n",
    "            command += \" INNER JOIN \" + join\n",
    "        else:\n",
    "            command += \" \" + join\n",
    "    if where:\n",
    "        wstr = ' AND '.join(where)\n",
    "        command += \" WHERE \" + wstr\n",
    "    if sort:\n",
    "        sstr = ', '.join(sort)\n",
    "        command += \" ORDER BY \" + sstr\n",
    "    if limit:\n",
    "        command += \" LIMIT \" + str(limit)\n",
    "\n",
    "    print (\"SQL Command: \", command)\n",
    "    cursor.execute(command)\n",
    "    print(\"fetchall:\")\n",
    "    result = cursor.fetchall() \n",
    "    for r in result:\n",
    "        print(r)\n",
    "    return list(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  SELECT * FROM movies INNER JOIN Boxoffice ON movies.id = Boxoffice.movie_id\n",
      "fetchall:\n",
      "(1, 'Toy Story', 'John Lasseter', 1995, 81, 1, 8.3, 191796233, 170162503)\n",
      "(2, \"A Bug's Life\", 'John Lasseter', 1998, 95, 2, 7.2, 162798565, 200600000)\n",
      "(3, 'Toy Story 2', 'John Lasseter', 1999, 93, 3, 7.9, 245852179, 239163000)\n",
      "(4, 'Monsters, Inc.', 'Pete Docter', 2001, 92, 4, 8.1, 289916256, 272900000)\n",
      "(5, 'Finding Nemo', 'Andrew Stanton', 2003, 107, 5, 8.2, 380843261, 555900000)\n",
      "(6, 'The Incredibles', 'Brad Bird', 2004, 116, 6, 8.0, 261441092, 370001000)\n",
      "(7, 'Cars', 'John Lasseter', 2006, 117, 7, 7.2, 244082982, 217900167)\n",
      "(8, 'Ratatouille', 'Brad Bird', 2007, 115, 8, 8.0, 206445654, 417277164)\n",
      "(9, 'WALL-E', 'Andrew Stanton', 2008, 104, 9, 8.5, 223808164, 297503696)\n",
      "(10, 'Up', 'Pete Docter', 2009, 101, 10, 8.3, 293004164, 438338580)\n",
      "(11, 'Toy Story 3', 'Lee Unkrich', 2010, 103, 11, 8.4, 415004880, 648167031)\n",
      "(12, 'Cars 2', 'John Lasseter', 2011, 120, 12, 6.4, 191452396, 368400000)\n",
      "(13, 'Brave', 'Brenda Chapman', 2012, 102, 13, 7.2, 237283207, 301700000)\n",
      "(14, 'Monsters University', 'Dan Scanlon', 2013, 110, 14, 7.4, 268492764, 475066843)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(1, 'Toy Story', 'John Lasseter', 1995, 81, 1, 8.3, 191796233, 170162503),\n",
       " (2, \"A Bug's Life\", 'John Lasseter', 1998, 95, 2, 7.2, 162798565, 200600000),\n",
       " (3, 'Toy Story 2', 'John Lasseter', 1999, 93, 3, 7.9, 245852179, 239163000),\n",
       " (4, 'Monsters, Inc.', 'Pete Docter', 2001, 92, 4, 8.1, 289916256, 272900000),\n",
       " (5,\n",
       "  'Finding Nemo',\n",
       "  'Andrew Stanton',\n",
       "  2003,\n",
       "  107,\n",
       "  5,\n",
       "  8.2,\n",
       "  380843261,\n",
       "  555900000),\n",
       " (6, 'The Incredibles', 'Brad Bird', 2004, 116, 6, 8.0, 261441092, 370001000),\n",
       " (7, 'Cars', 'John Lasseter', 2006, 117, 7, 7.2, 244082982, 217900167),\n",
       " (8, 'Ratatouille', 'Brad Bird', 2007, 115, 8, 8.0, 206445654, 417277164),\n",
       " (9, 'WALL-E', 'Andrew Stanton', 2008, 104, 9, 8.5, 223808164, 297503696),\n",
       " (10, 'Up', 'Pete Docter', 2009, 101, 10, 8.3, 293004164, 438338580),\n",
       " (11, 'Toy Story 3', 'Lee Unkrich', 2010, 103, 11, 8.4, 415004880, 648167031),\n",
       " (12, 'Cars 2', 'John Lasseter', 2011, 120, 12, 6.4, 191452396, 368400000),\n",
       " (13, 'Brave', 'Brenda Chapman', 2012, 102, 13, 7.2, 237283207, 301700000),\n",
       " (14,\n",
       "  'Monsters University',\n",
       "  'Dan Scanlon',\n",
       "  2013,\n",
       "  110,\n",
       "  14,\n",
       "  7.4,\n",
       "  268492764,\n",
       "  475066843)]"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fetchwherejoin([], [], [], [], 'movies', 'Boxoffice ON movies.id = Boxoffice.movie_id')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Show the sales numbers for each movie that did better internationally rather than domestically"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  SELECT Rating, title, Domestic_sales, International_sales FROM movies INNER JOIN Boxoffice ON movies.id = Boxoffice.movie_id WHERE Domestic_sales < International_sales ORDER BY Rating\n",
      "fetchall:\n",
      "(6.4, 'Cars 2', 191452396, 368400000)\n",
      "(7.2, \"A Bug's Life\", 162798565, 200600000)\n",
      "(7.2, 'Brave', 237283207, 301700000)\n",
      "(7.4, 'Monsters University', 268492764, 475066843)\n",
      "(8.0, 'The Incredibles', 261441092, 370001000)\n",
      "(8.0, 'Ratatouille', 206445654, 417277164)\n",
      "(8.2, 'Finding Nemo', 380843261, 555900000)\n",
      "(8.3, 'Up', 293004164, 438338580)\n",
      "(8.4, 'Toy Story 3', 415004880, 648167031)\n",
      "(8.5, 'WALL-E', 223808164, 297503696)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(6.4, 'Cars 2', 191452396, 368400000),\n",
       " (7.2, \"A Bug's Life\", 162798565, 200600000),\n",
       " (7.2, 'Brave', 237283207, 301700000),\n",
       " (7.4, 'Monsters University', 268492764, 475066843),\n",
       " (8.0, 'The Incredibles', 261441092, 370001000),\n",
       " (8.0, 'Ratatouille', 206445654, 417277164),\n",
       " (8.2, 'Finding Nemo', 380843261, 555900000),\n",
       " (8.3, 'Up', 293004164, 438338580),\n",
       " (8.4, 'Toy Story 3', 415004880, 648167031),\n",
       " (8.5, 'WALL-E', 223808164, 297503696)]"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fetchwherejoin(['Rating', 'title', 'Domestic_sales', 'International_sales'], \n",
    "               ['Domestic_sales < International_sales'], ['Rating'], [], 'movies', \n",
    "               'Boxoffice ON movies.id = Boxoffice.movie_id')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SQL Lesson 7: OUTER JOINs\n",
    "\n",
    "Used when tables have asymetric data - may not have sales data for some movies."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SQL Lesson 8: A short note on NULLs\n",
    "\n",
    "To process or identify missing data use\n",
    "WHERE column IS / IS NOT NULL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "c12 = \"\"\"\n",
    "INSERT INTO movies\n",
    "(id, title, director, year)\n",
    "VALUES\n",
    "(100, 'Gone With the Wind', 'Victor Fleming', 1939);\n",
    "\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7f54854bdb90>"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(c12)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  SELECT * FROM movies\n",
      "fetchall:\n",
      "(1, 'Toy Story', 'John Lasseter', 1995, 81)\n",
      "(2, \"A Bug's Life\", 'John Lasseter', 1998, 95)\n",
      "(3, 'Toy Story 2', 'John Lasseter', 1999, 93)\n",
      "(4, 'Monsters, Inc.', 'Pete Docter', 2001, 92)\n",
      "(5, 'Finding Nemo', 'Andrew Stanton', 2003, 107)\n",
      "(6, 'The Incredibles', 'Brad Bird', 2004, 116)\n",
      "(7, 'Cars', 'John Lasseter', 2006, 117)\n",
      "(8, 'Ratatouille', 'Brad Bird', 2007, 115)\n",
      "(9, 'WALL-E', 'Andrew Stanton', 2008, 104)\n",
      "(10, 'Up', 'Pete Docter', 2009, 101)\n",
      "(11, 'Toy Story 3', 'Lee Unkrich', 2010, 103)\n",
      "(12, 'Cars 2', 'John Lasseter', 2011, 120)\n",
      "(13, 'Brave', 'Brenda Chapman', 2012, 102)\n",
      "(14, 'Monsters University', 'Dan Scanlon', 2013, 110)\n",
      "(100, 'Gone With the Wind', 'Victor Fleming', 1939, None)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(1, 'Toy Story', 'John Lasseter', 1995, 81),\n",
       " (2, \"A Bug's Life\", 'John Lasseter', 1998, 95),\n",
       " (3, 'Toy Story 2', 'John Lasseter', 1999, 93),\n",
       " (4, 'Monsters, Inc.', 'Pete Docter', 2001, 92),\n",
       " (5, 'Finding Nemo', 'Andrew Stanton', 2003, 107),\n",
       " (6, 'The Incredibles', 'Brad Bird', 2004, 116),\n",
       " (7, 'Cars', 'John Lasseter', 2006, 117),\n",
       " (8, 'Ratatouille', 'Brad Bird', 2007, 115),\n",
       " (9, 'WALL-E', 'Andrew Stanton', 2008, 104),\n",
       " (10, 'Up', 'Pete Docter', 2009, 101),\n",
       " (11, 'Toy Story 3', 'Lee Unkrich', 2010, 103),\n",
       " (12, 'Cars 2', 'John Lasseter', 2011, 120),\n",
       " (13, 'Brave', 'Brenda Chapman', 2012, 102),\n",
       " (14, 'Monsters University', 'Dan Scanlon', 2013, 110),\n",
       " (100, 'Gone With the Wind', 'Victor Fleming', 1939, None)]"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  SELECT * FROM movies WHERE Length_minutes IS NULL\n",
      "fetchall:\n",
      "(100, 'Gone With the Wind', 'Victor Fleming', 1939, None)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(100, 'Gone With the Wind', 'Victor Fleming', 1939, None)]"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fetchwhere([], ['Length_minutes IS NULL'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  SELECT * FROM movies INNER JOIN Boxoffice ON movies.id = Boxoffice.movie_id\n",
      "fetchall:\n",
      "(1, 'Toy Story', 'John Lasseter', 1995, 81, 1, 8.3, 191796233, 170162503)\n",
      "(2, \"A Bug's Life\", 'John Lasseter', 1998, 95, 2, 7.2, 162798565, 200600000)\n",
      "(3, 'Toy Story 2', 'John Lasseter', 1999, 93, 3, 7.9, 245852179, 239163000)\n",
      "(4, 'Monsters, Inc.', 'Pete Docter', 2001, 92, 4, 8.1, 289916256, 272900000)\n",
      "(5, 'Finding Nemo', 'Andrew Stanton', 2003, 107, 5, 8.2, 380843261, 555900000)\n",
      "(6, 'The Incredibles', 'Brad Bird', 2004, 116, 6, 8.0, 261441092, 370001000)\n",
      "(7, 'Cars', 'John Lasseter', 2006, 117, 7, 7.2, 244082982, 217900167)\n",
      "(8, 'Ratatouille', 'Brad Bird', 2007, 115, 8, 8.0, 206445654, 417277164)\n",
      "(9, 'WALL-E', 'Andrew Stanton', 2008, 104, 9, 8.5, 223808164, 297503696)\n",
      "(10, 'Up', 'Pete Docter', 2009, 101, 10, 8.3, 293004164, 438338580)\n",
      "(11, 'Toy Story 3', 'Lee Unkrich', 2010, 103, 11, 8.4, 415004880, 648167031)\n",
      "(12, 'Cars 2', 'John Lasseter', 2011, 120, 12, 6.4, 191452396, 368400000)\n",
      "(13, 'Brave', 'Brenda Chapman', 2012, 102, 13, 7.2, 237283207, 301700000)\n",
      "(14, 'Monsters University', 'Dan Scanlon', 2013, 110, 14, 7.4, 268492764, 475066843)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(1, 'Toy Story', 'John Lasseter', 1995, 81, 1, 8.3, 191796233, 170162503),\n",
       " (2, \"A Bug's Life\", 'John Lasseter', 1998, 95, 2, 7.2, 162798565, 200600000),\n",
       " (3, 'Toy Story 2', 'John Lasseter', 1999, 93, 3, 7.9, 245852179, 239163000),\n",
       " (4, 'Monsters, Inc.', 'Pete Docter', 2001, 92, 4, 8.1, 289916256, 272900000),\n",
       " (5,\n",
       "  'Finding Nemo',\n",
       "  'Andrew Stanton',\n",
       "  2003,\n",
       "  107,\n",
       "  5,\n",
       "  8.2,\n",
       "  380843261,\n",
       "  555900000),\n",
       " (6, 'The Incredibles', 'Brad Bird', 2004, 116, 6, 8.0, 261441092, 370001000),\n",
       " (7, 'Cars', 'John Lasseter', 2006, 117, 7, 7.2, 244082982, 217900167),\n",
       " (8, 'Ratatouille', 'Brad Bird', 2007, 115, 8, 8.0, 206445654, 417277164),\n",
       " (9, 'WALL-E', 'Andrew Stanton', 2008, 104, 9, 8.5, 223808164, 297503696),\n",
       " (10, 'Up', 'Pete Docter', 2009, 101, 10, 8.3, 293004164, 438338580),\n",
       " (11, 'Toy Story 3', 'Lee Unkrich', 2010, 103, 11, 8.4, 415004880, 648167031),\n",
       " (12, 'Cars 2', 'John Lasseter', 2011, 120, 12, 6.4, 191452396, 368400000),\n",
       " (13, 'Brave', 'Brenda Chapman', 2012, 102, 13, 7.2, 237283207, 301700000),\n",
       " (14,\n",
       "  'Monsters University',\n",
       "  'Dan Scanlon',\n",
       "  2013,\n",
       "  110,\n",
       "  14,\n",
       "  7.4,\n",
       "  268492764,\n",
       "  475066843)]"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fetchwherejoin([], [], [], [], 'movies', \n",
    "               'Boxoffice ON movies.id = Boxoffice.movie_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  SELECT * FROM movies LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id\n",
      "fetchall:\n",
      "(1, 'Toy Story', 'John Lasseter', 1995, 81, 1, 8.3, 191796233, 170162503)\n",
      "(2, \"A Bug's Life\", 'John Lasseter', 1998, 95, 2, 7.2, 162798565, 200600000)\n",
      "(3, 'Toy Story 2', 'John Lasseter', 1999, 93, 3, 7.9, 245852179, 239163000)\n",
      "(4, 'Monsters, Inc.', 'Pete Docter', 2001, 92, 4, 8.1, 289916256, 272900000)\n",
      "(5, 'Finding Nemo', 'Andrew Stanton', 2003, 107, 5, 8.2, 380843261, 555900000)\n",
      "(6, 'The Incredibles', 'Brad Bird', 2004, 116, 6, 8.0, 261441092, 370001000)\n",
      "(7, 'Cars', 'John Lasseter', 2006, 117, 7, 7.2, 244082982, 217900167)\n",
      "(8, 'Ratatouille', 'Brad Bird', 2007, 115, 8, 8.0, 206445654, 417277164)\n",
      "(9, 'WALL-E', 'Andrew Stanton', 2008, 104, 9, 8.5, 223808164, 297503696)\n",
      "(10, 'Up', 'Pete Docter', 2009, 101, 10, 8.3, 293004164, 438338580)\n",
      "(11, 'Toy Story 3', 'Lee Unkrich', 2010, 103, 11, 8.4, 415004880, 648167031)\n",
      "(12, 'Cars 2', 'John Lasseter', 2011, 120, 12, 6.4, 191452396, 368400000)\n",
      "(13, 'Brave', 'Brenda Chapman', 2012, 102, 13, 7.2, 237283207, 301700000)\n",
      "(14, 'Monsters University', 'Dan Scanlon', 2013, 110, 14, 7.4, 268492764, 475066843)\n",
      "(100, 'Gone With the Wind', 'Victor Fleming', 1939, None, None, None, None, None)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(1, 'Toy Story', 'John Lasseter', 1995, 81, 1, 8.3, 191796233, 170162503),\n",
       " (2, \"A Bug's Life\", 'John Lasseter', 1998, 95, 2, 7.2, 162798565, 200600000),\n",
       " (3, 'Toy Story 2', 'John Lasseter', 1999, 93, 3, 7.9, 245852179, 239163000),\n",
       " (4, 'Monsters, Inc.', 'Pete Docter', 2001, 92, 4, 8.1, 289916256, 272900000),\n",
       " (5,\n",
       "  'Finding Nemo',\n",
       "  'Andrew Stanton',\n",
       "  2003,\n",
       "  107,\n",
       "  5,\n",
       "  8.2,\n",
       "  380843261,\n",
       "  555900000),\n",
       " (6, 'The Incredibles', 'Brad Bird', 2004, 116, 6, 8.0, 261441092, 370001000),\n",
       " (7, 'Cars', 'John Lasseter', 2006, 117, 7, 7.2, 244082982, 217900167),\n",
       " (8, 'Ratatouille', 'Brad Bird', 2007, 115, 8, 8.0, 206445654, 417277164),\n",
       " (9, 'WALL-E', 'Andrew Stanton', 2008, 104, 9, 8.5, 223808164, 297503696),\n",
       " (10, 'Up', 'Pete Docter', 2009, 101, 10, 8.3, 293004164, 438338580),\n",
       " (11, 'Toy Story 3', 'Lee Unkrich', 2010, 103, 11, 8.4, 415004880, 648167031),\n",
       " (12, 'Cars 2', 'John Lasseter', 2011, 120, 12, 6.4, 191452396, 368400000),\n",
       " (13, 'Brave', 'Brenda Chapman', 2012, 102, 13, 7.2, 237283207, 301700000),\n",
       " (14,\n",
       "  'Monsters University',\n",
       "  'Dan Scanlon',\n",
       "  2013,\n",
       "  110,\n",
       "  14,\n",
       "  7.4,\n",
       "  268492764,\n",
       "  475066843),\n",
       " (100,\n",
       "  'Gone With the Wind',\n",
       "  'Victor Fleming',\n",
       "  1939,\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None,\n",
       "  None)]"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fetchwherejoin([], [], [], [], 'movies', \n",
    "               'LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "RIGHT and FULL are not currently supported in sqlite3\n",
    "\n",
    "switch order of tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  SELECT * FROM Boxoffice LEFT JOIN Movies ON movies.id = Boxoffice.movie_id\n",
      "fetchall:\n",
      "(1, 8.3, 191796233, 170162503, 1, 'Toy Story', 'John Lasseter', 1995, 81)\n",
      "(2, 7.2, 162798565, 200600000, 2, \"A Bug's Life\", 'John Lasseter', 1998, 95)\n",
      "(3, 7.9, 245852179, 239163000, 3, 'Toy Story 2', 'John Lasseter', 1999, 93)\n",
      "(4, 8.1, 289916256, 272900000, 4, 'Monsters, Inc.', 'Pete Docter', 2001, 92)\n",
      "(5, 8.2, 380843261, 555900000, 5, 'Finding Nemo', 'Andrew Stanton', 2003, 107)\n",
      "(6, 8.0, 261441092, 370001000, 6, 'The Incredibles', 'Brad Bird', 2004, 116)\n",
      "(7, 7.2, 244082982, 217900167, 7, 'Cars', 'John Lasseter', 2006, 117)\n",
      "(8, 8.0, 206445654, 417277164, 8, 'Ratatouille', 'Brad Bird', 2007, 115)\n",
      "(9, 8.5, 223808164, 297503696, 9, 'WALL-E', 'Andrew Stanton', 2008, 104)\n",
      "(10, 8.3, 293004164, 438338580, 10, 'Up', 'Pete Docter', 2009, 101)\n",
      "(11, 8.4, 415004880, 648167031, 11, 'Toy Story 3', 'Lee Unkrich', 2010, 103)\n",
      "(12, 6.4, 191452396, 368400000, 12, 'Cars 2', 'John Lasseter', 2011, 120)\n",
      "(13, 7.2, 237283207, 301700000, 13, 'Brave', 'Brenda Chapman', 2012, 102)\n",
      "(14, 7.4, 268492764, 475066843, 14, 'Monsters University', 'Dan Scanlon', 2013, 110)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(1, 8.3, 191796233, 170162503, 1, 'Toy Story', 'John Lasseter', 1995, 81),\n",
       " (2, 7.2, 162798565, 200600000, 2, \"A Bug's Life\", 'John Lasseter', 1998, 95),\n",
       " (3, 7.9, 245852179, 239163000, 3, 'Toy Story 2', 'John Lasseter', 1999, 93),\n",
       " (4, 8.1, 289916256, 272900000, 4, 'Monsters, Inc.', 'Pete Docter', 2001, 92),\n",
       " (5,\n",
       "  8.2,\n",
       "  380843261,\n",
       "  555900000,\n",
       "  5,\n",
       "  'Finding Nemo',\n",
       "  'Andrew Stanton',\n",
       "  2003,\n",
       "  107),\n",
       " (6, 8.0, 261441092, 370001000, 6, 'The Incredibles', 'Brad Bird', 2004, 116),\n",
       " (7, 7.2, 244082982, 217900167, 7, 'Cars', 'John Lasseter', 2006, 117),\n",
       " (8, 8.0, 206445654, 417277164, 8, 'Ratatouille', 'Brad Bird', 2007, 115),\n",
       " (9, 8.5, 223808164, 297503696, 9, 'WALL-E', 'Andrew Stanton', 2008, 104),\n",
       " (10, 8.3, 293004164, 438338580, 10, 'Up', 'Pete Docter', 2009, 101),\n",
       " (11, 8.4, 415004880, 648167031, 11, 'Toy Story 3', 'Lee Unkrich', 2010, 103),\n",
       " (12, 6.4, 191452396, 368400000, 12, 'Cars 2', 'John Lasseter', 2011, 120),\n",
       " (13, 7.2, 237283207, 301700000, 13, 'Brave', 'Brenda Chapman', 2012, 102),\n",
       " (14,\n",
       "  7.4,\n",
       "  268492764,\n",
       "  475066843,\n",
       "  14,\n",
       "  'Monsters University',\n",
       "  'Dan Scanlon',\n",
       "  2013,\n",
       "  110)]"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fetchwherejoin([], [], [], [], 'Boxoffice', \n",
    "               'LEFT JOIN Movies ON movies.id = Boxoffice.movie_id')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SQL Lesson 9: Queries with expressions\n",
    "\n",
    "Use AS for column aliases."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  SELECT title, International_sales AS Isales, Domestic_sales AS Dsales FROM movies LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id WHERE Dsales < Isales\n",
      "fetchall:\n",
      "(\"A Bug's Life\", 200600000, 162798565)\n",
      "('Finding Nemo', 555900000, 380843261)\n",
      "('The Incredibles', 370001000, 261441092)\n",
      "('Ratatouille', 417277164, 206445654)\n",
      "('WALL-E', 297503696, 223808164)\n",
      "('Up', 438338580, 293004164)\n",
      "('Toy Story 3', 648167031, 415004880)\n",
      "('Cars 2', 368400000, 191452396)\n",
      "('Brave', 301700000, 237283207)\n",
      "('Monsters University', 475066843, 268492764)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(\"A Bug's Life\", 200600000, 162798565),\n",
       " ('Finding Nemo', 555900000, 380843261),\n",
       " ('The Incredibles', 370001000, 261441092),\n",
       " ('Ratatouille', 417277164, 206445654),\n",
       " ('WALL-E', 297503696, 223808164),\n",
       " ('Up', 438338580, 293004164),\n",
       " ('Toy Story 3', 648167031, 415004880),\n",
       " ('Cars 2', 368400000, 191452396),\n",
       " ('Brave', 301700000, 237283207),\n",
       " ('Monsters University', 475066843, 268492764)]"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fetchwherejoin(['title', 'International_sales AS Isales', \n",
    "                'Domestic_sales AS Dsales'], ['Dsales < Isales'], [], [], \n",
    "               'movies', 'LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create new column: Sales ratio."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  SELECT title, International_sales / Domestic_sales AS Sratio FROM movies LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id WHERE Sratio > .5\n",
      "fetchall:\n",
      "(\"A Bug's Life\", 1)\n",
      "('Finding Nemo', 1)\n",
      "('The Incredibles', 1)\n",
      "('Ratatouille', 2)\n",
      "('WALL-E', 1)\n",
      "('Up', 1)\n",
      "('Toy Story 3', 1)\n",
      "('Cars 2', 1)\n",
      "('Brave', 1)\n",
      "('Monsters University', 1)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(\"A Bug's Life\", 1),\n",
       " ('Finding Nemo', 1),\n",
       " ('The Incredibles', 1),\n",
       " ('Ratatouille', 2),\n",
       " ('WALL-E', 1),\n",
       " ('Up', 1),\n",
       " ('Toy Story 3', 1),\n",
       " ('Cars 2', 1),\n",
       " ('Brave', 1),\n",
       " ('Monsters University', 1)]"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fetchwherejoin(['title', 'International_sales / Domestic_sales AS Sratio'], \n",
    "               ['Sratio > .5'], [], [], 'movies', \n",
    "               'LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create new column: gross sales millions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  SELECT title, (International_sales + Domestic_sales) / 1000000 AS Gross_sales_MM FROM movies LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id ORDER BY Gross_sales_MM\n",
      "fetchall:\n",
      "('Gone With the Wind', None)\n",
      "('Toy Story', 361)\n",
      "(\"A Bug's Life\", 363)\n",
      "('Cars', 461)\n",
      "('Toy Story 2', 485)\n",
      "('WALL-E', 521)\n",
      "('Brave', 538)\n",
      "('Cars 2', 559)\n",
      "('Monsters, Inc.', 562)\n",
      "('Ratatouille', 623)\n",
      "('The Incredibles', 631)\n",
      "('Up', 731)\n",
      "('Monsters University', 743)\n",
      "('Finding Nemo', 936)\n",
      "('Toy Story 3', 1063)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[('Gone With the Wind', None),\n",
       " ('Toy Story', 361),\n",
       " (\"A Bug's Life\", 363),\n",
       " ('Cars', 461),\n",
       " ('Toy Story 2', 485),\n",
       " ('WALL-E', 521),\n",
       " ('Brave', 538),\n",
       " ('Cars 2', 559),\n",
       " ('Monsters, Inc.', 562),\n",
       " ('Ratatouille', 623),\n",
       " ('The Incredibles', 631),\n",
       " ('Up', 731),\n",
       " ('Monsters University', 743),\n",
       " ('Finding Nemo', 936),\n",
       " ('Toy Story 3', 1063)]"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fetchwherejoin(['title', \n",
    "                '(International_sales + Domestic_sales) / 1000000 AS Gross_sales_MM'], \n",
    "               [], ['Gross_sales_MM'], [], 'movies', \n",
    "               'LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SQL Lesson 10: Queries with aggregates (Pt. 1)\n",
    "\n",
    "Calculate average domestic sales using AVG()."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  SELECT AVG(Domestic_sales) FROM movies LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id\n",
      "fetchall:\n",
      "(258015842.64285713,)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(258015842.64285713,)]"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fetchwherejoin(['AVG(Domestic_sales)'], [], [], [], 'movies', \n",
    "               'LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many movies have a rating < 8?  Use COUNT()."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  SELECT COUNT() FROM movies LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id WHERE Rating < 8\n",
      "fetchall:\n",
      "(6,)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(6,)]"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fetchwherejoin(['COUNT()'], ['Rating < 8'], [], [], 'movies', \n",
    "               'LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What are the movies with a rating < 8?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  SELECT title, Rating FROM movies LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id WHERE Rating < 8 ORDER BY Rating\n",
      "fetchall:\n",
      "('Cars 2', 6.4)\n",
      "(\"A Bug's Life\", 7.2)\n",
      "('Cars', 7.2)\n",
      "('Brave', 7.2)\n",
      "('Monsters University', 7.4)\n",
      "('Toy Story 2', 7.9)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[('Cars 2', 6.4),\n",
       " (\"A Bug's Life\", 7.2),\n",
       " ('Cars', 7.2),\n",
       " ('Brave', 7.2),\n",
       " ('Monsters University', 7.4),\n",
       " ('Toy Story 2', 7.9)]"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fetchwherejoin(['title', 'Rating'], ['Rating < 8'], ['Rating'], [], 'movies', \n",
    "               'LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calculate the max, min, avg ratings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  SELECT max(Rating), min(Rating), avg(Rating) FROM movies LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id\n",
      "fetchall:\n",
      "(8.5, 6.4, 7.7928571428571445)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(8.5, 6.4, 7.7928571428571445)]"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fetchwherejoin(['max(Rating)', 'min(Rating)', 'avg(Rating)'], [], [], [], \n",
    "               'movies', 'LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SQL Lesson 11: Queries with aggregates (Pt. 2)\n",
    "\n",
    "### SQL Lesson 12: Order of execution of a Query\n",
    "\n",
    "### SQL Lesson 13: Inserting rows\n",
    "\n",
    "We did this above after creating the table\n",
    "\n",
    "### SQL Lesson 14: Updating rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "def updatewhere(pairs = [], where = [], table = 'movies'):\n",
    "    cursor = connection.cursor()\n",
    "    fstr = '*'\n",
    "    wstr = ' 1=1 '\n",
    "    command = \"UPDATE \" + table + \" SET \"\n",
    "    if pairs:\n",
    "        count = len(pairs)\n",
    "        for p in pairs:\n",
    "            (column, value) = p\n",
    "            command += column + \" = \" + str(value)\n",
    "            if (count > 1):\n",
    "                command += \", \"\n",
    "                count -= 1\n",
    "    if where:\n",
    "        wstr = ' AND '.join(where)\n",
    "    command += \" WHERE \" + wstr\n",
    "    print (\"SQL Command: \", command)\n",
    "    cursor.execute(command)\n",
    "    ## show results\n",
    "    fetchall([], table)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Change title and year.  This is a bogus example."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  UPDATE movies SET title = \"s\", year = 1000 WHERE year < 2000\n",
      "SQL Command:  SELECT * FROM movies\n",
      "fetchall:\n",
      "(1, 's', 'John Lasseter', 1000, 81)\n",
      "(2, 's', 'John Lasseter', 1000, 95)\n",
      "(3, 's', 'John Lasseter', 1000, 93)\n",
      "(4, 'Monsters, Inc.', 'Pete Docter', 2001, 92)\n",
      "(5, 'Finding Nemo', 'Andrew Stanton', 2003, 107)\n",
      "(6, 'The Incredibles', 'Brad Bird', 2004, 116)\n",
      "(7, 'Cars', 'John Lasseter', 2006, 117)\n",
      "(8, 'Ratatouille', 'Brad Bird', 2007, 115)\n",
      "(9, 'WALL-E', 'Andrew Stanton', 2008, 104)\n",
      "(10, 'Up', 'Pete Docter', 2009, 101)\n",
      "(11, 'Toy Story 3', 'Lee Unkrich', 2010, 103)\n",
      "(12, 'Cars 2', 'John Lasseter', 2011, 120)\n",
      "(13, 'Brave', 'Brenda Chapman', 2012, 102)\n",
      "(14, 'Monsters University', 'Dan Scanlon', 2013, 110)\n",
      "(100, 's', 'Victor Fleming', 1000, None)\n"
     ]
    }
   ],
   "source": [
    "updatewhere([('title', '\"s\"'), ('year', 1000)], ['year < 2000'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  SELECT * FROM movies\n",
      "fetchall:\n",
      "(1, 's', 'John Lasseter', 1000, 81)\n",
      "(2, 's', 'John Lasseter', 1000, 95)\n",
      "(3, 's', 'John Lasseter', 1000, 93)\n",
      "(4, 'Monsters, Inc.', 'Pete Docter', 2001, 92)\n",
      "(5, 'Finding Nemo', 'Andrew Stanton', 2003, 107)\n",
      "(6, 'The Incredibles', 'Brad Bird', 2004, 116)\n",
      "(7, 'Cars', 'John Lasseter', 2006, 117)\n",
      "(8, 'Ratatouille', 'Brad Bird', 2007, 115)\n",
      "(9, 'WALL-E', 'Andrew Stanton', 2008, 104)\n",
      "(10, 'Up', 'Pete Docter', 2009, 101)\n",
      "(11, 'Toy Story 3', 'Lee Unkrich', 2010, 103)\n",
      "(12, 'Cars 2', 'John Lasseter', 2011, 120)\n",
      "(13, 'Brave', 'Brenda Chapman', 2012, 102)\n",
      "(14, 'Monsters University', 'Dan Scanlon', 2013, 110)\n",
      "(100, 's', 'Victor Fleming', 1000, None)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(1, 's', 'John Lasseter', 1000, 81),\n",
       " (2, 's', 'John Lasseter', 1000, 95),\n",
       " (3, 's', 'John Lasseter', 1000, 93),\n",
       " (4, 'Monsters, Inc.', 'Pete Docter', 2001, 92),\n",
       " (5, 'Finding Nemo', 'Andrew Stanton', 2003, 107),\n",
       " (6, 'The Incredibles', 'Brad Bird', 2004, 116),\n",
       " (7, 'Cars', 'John Lasseter', 2006, 117),\n",
       " (8, 'Ratatouille', 'Brad Bird', 2007, 115),\n",
       " (9, 'WALL-E', 'Andrew Stanton', 2008, 104),\n",
       " (10, 'Up', 'Pete Docter', 2009, 101),\n",
       " (11, 'Toy Story 3', 'Lee Unkrich', 2010, 103),\n",
       " (12, 'Cars 2', 'John Lasseter', 2011, 120),\n",
       " (13, 'Brave', 'Brenda Chapman', 2012, 102),\n",
       " (14, 'Monsters University', 'Dan Scanlon', 2013, 110),\n",
       " (100, 's', 'Victor Fleming', 1000, None)]"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SQL Lesson 15: Deleting rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "def deletewhere(where = [], table = 'movies'):\n",
    "    cursor = connection.cursor()\n",
    "    wstr = ' 1=1 '\n",
    "    command = \"DELETE FROM \" + table \n",
    "    if where:\n",
    "        wstr = ' AND '.join(where)\n",
    "    command += \" WHERE \" + wstr\n",
    "    print (\"SQL Command: \", command)\n",
    "    cursor.execute(command)\n",
    "    ## show results\n",
    "    fetchall([], table)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  DELETE FROM movies WHERE year < 2000\n",
      "SQL Command:  SELECT * FROM movies\n",
      "fetchall:\n",
      "(4, 'Monsters, Inc.', 'Pete Docter', 2001, 92)\n",
      "(5, 'Finding Nemo', 'Andrew Stanton', 2003, 107)\n",
      "(6, 'The Incredibles', 'Brad Bird', 2004, 116)\n",
      "(7, 'Cars', 'John Lasseter', 2006, 117)\n",
      "(8, 'Ratatouille', 'Brad Bird', 2007, 115)\n",
      "(9, 'WALL-E', 'Andrew Stanton', 2008, 104)\n",
      "(10, 'Up', 'Pete Docter', 2009, 101)\n",
      "(11, 'Toy Story 3', 'Lee Unkrich', 2010, 103)\n",
      "(12, 'Cars 2', 'John Lasseter', 2011, 120)\n",
      "(13, 'Brave', 'Brenda Chapman', 2012, 102)\n",
      "(14, 'Monsters University', 'Dan Scanlon', 2013, 110)\n"
     ]
    }
   ],
   "source": [
    "deletewhere(['year < 2000'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SQL Lesson 16: Creating tables\n",
    "\n",
    "<pre>\n",
    "CREATE TABLE IF NOT EXISTS mytable (\n",
    "    column DataType TableConstraint DEFAULT default_value,\n",
    "    another_column DataType TableConstraint DEFAULT default_value,\n",
    "    …\n",
    ");\n",
    "</pre>\n",
    "\n",
    "\n",
    "Common data types:\n",
    "- Integer\n",
    "- Boolean\n",
    "- Float, Double, Real\n",
    "- Character(size)\n",
    "- Varchar(size)\n",
    "- Text\n",
    "- Date\n",
    "- DateTime\n",
    "- Blob  (binary objects, e.g., video, audio, code)\n",
    "\n",
    "\n",
    "Common table constraints:\n",
    "- PRIMARY KEY - most efficient index\n",
    "- AUTOINCREMENT - useful for id fields\n",
    "- UNIQUE - no duplicate entries\n",
    "- NOT NULL - required field\n",
    "- CHECK (expression) - check for type, range, etc.\n",
    "- FOREIGN KEY - referential integrity check"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SQL Lesson 17: Altering tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "c12 = '''\n",
    "ALTER TABLE movies\n",
    "ADD seenit BOOLEAN DEFAULT FALSE\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7f54854bdb90>"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(c12)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  PRAGMA table_info('movies')\n",
      "(0, 'id', 'INTEGER', 0, None, 1)\n",
      "(1, 'title', 'TEXT', 0, None, 0)\n",
      "(2, 'director', 'TEXT', 0, None, 0)\n",
      "(3, 'year', 'INTEGER', 0, None, 0)\n",
      "(4, 'length_minutes', 'INTEGER', 0, None, 0)\n",
      "(5, 'seenit', 'BOOLEAN', 0, 'FALSE', 0)\n"
     ]
    }
   ],
   "source": [
    "desc()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  UPDATE movies SET seenit = \"TRUE\" WHERE year < 2010\n",
      "SQL Command:  SELECT * FROM movies\n",
      "fetchall:\n",
      "(4, 'Monsters, Inc.', 'Pete Docter', 2001, 92, 'TRUE')\n",
      "(5, 'Finding Nemo', 'Andrew Stanton', 2003, 107, 'TRUE')\n",
      "(6, 'The Incredibles', 'Brad Bird', 2004, 116, 'TRUE')\n",
      "(7, 'Cars', 'John Lasseter', 2006, 117, 'TRUE')\n",
      "(8, 'Ratatouille', 'Brad Bird', 2007, 115, 'TRUE')\n",
      "(9, 'WALL-E', 'Andrew Stanton', 2008, 104, 'TRUE')\n",
      "(10, 'Up', 'Pete Docter', 2009, 101, 'TRUE')\n",
      "(11, 'Toy Story 3', 'Lee Unkrich', 2010, 103, 0)\n",
      "(12, 'Cars 2', 'John Lasseter', 2011, 120, 0)\n",
      "(13, 'Brave', 'Brenda Chapman', 2012, 102, 0)\n",
      "(14, 'Monsters University', 'Dan Scanlon', 2013, 110, 0)\n"
     ]
    }
   ],
   "source": [
    "updatewhere([('seenit', '\"TRUE\"')], ['year < 2010'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  SELECT * FROM movies\n",
      "fetchall:\n",
      "(4, 'Monsters, Inc.', 'Pete Docter', 2001, 92, 'TRUE')\n",
      "(5, 'Finding Nemo', 'Andrew Stanton', 2003, 107, 'TRUE')\n",
      "(6, 'The Incredibles', 'Brad Bird', 2004, 116, 'TRUE')\n",
      "(7, 'Cars', 'John Lasseter', 2006, 117, 'TRUE')\n",
      "(8, 'Ratatouille', 'Brad Bird', 2007, 115, 'TRUE')\n",
      "(9, 'WALL-E', 'Andrew Stanton', 2008, 104, 'TRUE')\n",
      "(10, 'Up', 'Pete Docter', 2009, 101, 'TRUE')\n",
      "(11, 'Toy Story 3', 'Lee Unkrich', 2010, 103, 0)\n",
      "(12, 'Cars 2', 'John Lasseter', 2011, 120, 0)\n",
      "(13, 'Brave', 'Brenda Chapman', 2012, 102, 0)\n",
      "(14, 'Monsters University', 'Dan Scanlon', 2013, 110, 0)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(4, 'Monsters, Inc.', 'Pete Docter', 2001, 92, 'TRUE'),\n",
       " (5, 'Finding Nemo', 'Andrew Stanton', 2003, 107, 'TRUE'),\n",
       " (6, 'The Incredibles', 'Brad Bird', 2004, 116, 'TRUE'),\n",
       " (7, 'Cars', 'John Lasseter', 2006, 117, 'TRUE'),\n",
       " (8, 'Ratatouille', 'Brad Bird', 2007, 115, 'TRUE'),\n",
       " (9, 'WALL-E', 'Andrew Stanton', 2008, 104, 'TRUE'),\n",
       " (10, 'Up', 'Pete Docter', 2009, 101, 'TRUE'),\n",
       " (11, 'Toy Story 3', 'Lee Unkrich', 2010, 103, 0),\n",
       " (12, 'Cars 2', 'John Lasseter', 2011, 120, 0),\n",
       " (13, 'Brave', 'Brenda Chapman', 2012, 102, 0),\n",
       " (14, 'Monsters University', 'Dan Scanlon', 2013, 110, 0)]"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [],
   "source": [
    "c13 = '''\n",
    "ALTER TABLE movies\n",
    "DROP seenit \n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "ename": "OperationalError",
     "evalue": "near \"DROP\": syntax error",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m/tmp/ipykernel_3583178/3994672272.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mc13\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mOperationalError\u001b[0m: near \"DROP\": syntax error"
     ]
    }
   ],
   "source": [
    "cursor.execute(c13)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "DROP not supported by sqlite."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "c14 = '''\n",
    "ALTER TABLE movies\n",
    "RENAME TO films;\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7f54854bdb90>"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(c14)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  SELECT * FROM films\n",
      "fetchall:\n",
      "(4, 'Monsters, Inc.', 'Pete Docter', 2001, 92, 'TRUE')\n",
      "(5, 'Finding Nemo', 'Andrew Stanton', 2003, 107, 'TRUE')\n",
      "(6, 'The Incredibles', 'Brad Bird', 2004, 116, 'TRUE')\n",
      "(7, 'Cars', 'John Lasseter', 2006, 117, 'TRUE')\n",
      "(8, 'Ratatouille', 'Brad Bird', 2007, 115, 'TRUE')\n",
      "(9, 'WALL-E', 'Andrew Stanton', 2008, 104, 'TRUE')\n",
      "(10, 'Up', 'Pete Docter', 2009, 101, 'TRUE')\n",
      "(11, 'Toy Story 3', 'Lee Unkrich', 2010, 103, 0)\n",
      "(12, 'Cars 2', 'John Lasseter', 2011, 120, 0)\n",
      "(13, 'Brave', 'Brenda Chapman', 2012, 102, 0)\n",
      "(14, 'Monsters University', 'Dan Scanlon', 2013, 110, 0)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(4, 'Monsters, Inc.', 'Pete Docter', 2001, 92, 'TRUE'),\n",
       " (5, 'Finding Nemo', 'Andrew Stanton', 2003, 107, 'TRUE'),\n",
       " (6, 'The Incredibles', 'Brad Bird', 2004, 116, 'TRUE'),\n",
       " (7, 'Cars', 'John Lasseter', 2006, 117, 'TRUE'),\n",
       " (8, 'Ratatouille', 'Brad Bird', 2007, 115, 'TRUE'),\n",
       " (9, 'WALL-E', 'Andrew Stanton', 2008, 104, 'TRUE'),\n",
       " (10, 'Up', 'Pete Docter', 2009, 101, 'TRUE'),\n",
       " (11, 'Toy Story 3', 'Lee Unkrich', 2010, 103, 0),\n",
       " (12, 'Cars 2', 'John Lasseter', 2011, 120, 0),\n",
       " (13, 'Brave', 'Brenda Chapman', 2012, 102, 0),\n",
       " (14, 'Monsters University', 'Dan Scanlon', 2013, 110, 0)]"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fetchall([], 'films')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SQL Lesson 18: Dropping tables\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [],
   "source": [
    "c15 = '''\n",
    "DROP TABLE IF EXISTS films\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7f54854bdb90>"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(c15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  PRAGMA table_info('films')\n"
     ]
    }
   ],
   "source": [
    "desc('films')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL Command:  SELECT * FROM films\n"
     ]
    },
    {
     "ename": "OperationalError",
     "evalue": "no such table: films",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m/tmp/ipykernel_3583178/4189900663.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mfetchall\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m'films'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m/tmp/ipykernel_3583178/3724683776.py\u001b[0m in \u001b[0;36mfetchall\u001b[0;34m(fields, table)\u001b[0m\n\u001b[1;32m      6\u001b[0m     \u001b[0mcommand\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m\"SELECT \"\u001b[0m \u001b[0;34m+\u001b[0m \u001b[0mfstr\u001b[0m \u001b[0;34m+\u001b[0m \u001b[0;34m\" FROM \"\u001b[0m \u001b[0;34m+\u001b[0m \u001b[0mtable\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      7\u001b[0m     \u001b[0mprint\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0;34m\"SQL Command: \"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcommand\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 8\u001b[0;31m     \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcommand\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      9\u001b[0m     \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"fetchall:\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     10\u001b[0m     \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfetchall\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mOperationalError\u001b[0m: no such table: films"
     ]
    }
   ],
   "source": [
    "fetchall([],'films')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Advanced sqlite\n",
    "\n",
    "From: http://sebastianraschka.com/Articles/2014_sqlite_in_python_tutorial.html\n",
    "\n",
    "Should execute \"connection.close()\" before running doall()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [],
   "source": [
    "def total_rows(cursor, table_name, print_out=False):\n",
    "    \"\"\" Returns the total number of rows in the database \"\"\"\n",
    "    cursor.execute('SELECT COUNT(*) FROM {}'.format(table_name))\n",
    "    count = cursor.fetchall()\n",
    "    if print_out:\n",
    "        print('\\nTotal rows: {}'.format(count[0][0]))\n",
    "    return count[0][0]\n",
    "\n",
    "def table_col_info(cursor, table_name, print_out=False):\n",
    "    \"\"\" Returns a list of tuples with column informations:\n",
    "        (id, name, type, notnull, default_value, primary_key)\n",
    "    \"\"\"\n",
    "    cursor.execute('PRAGMA TABLE_INFO({})'.format(table_name))\n",
    "    info = cursor.fetchall()\n",
    "\n",
    "    if print_out:\n",
    "        print(\"\\nColumn Info:\\nID, Name, Type, NotNull, DefaultVal, PrimaryKey\")\n",
    "        for col in info:\n",
    "            print(col)\n",
    "    return info\n",
    "\n",
    "def values_in_col(cursor, table_name, print_out=True):\n",
    "    \"\"\" Returns a dictionary with columns as keys and the number of not-null\n",
    "        entries as associated values.\n",
    "    \"\"\"\n",
    "    cursor.execute('PRAGMA TABLE_INFO({})'.format(table_name))\n",
    "    info = cursor.fetchall()\n",
    "    col_dict = dict()\n",
    "    for col in info:\n",
    "        col_dict[col[1]] = 0\n",
    "    for col in col_dict:\n",
    "        cursor.execute('SELECT ({0}) FROM {1} WHERE {0} IS NOT NULL'.\n",
    "                       format(col, table_name))\n",
    "        # In my case this approach resulted in a better performance than using COUNT\n",
    "        number_rows = len(cursor.fetchall())\n",
    "        col_dict[col] = number_rows\n",
    "    if print_out:\n",
    "        print(\"\\nNumber of entries per column:\")\n",
    "        for i in col_dict.items():\n",
    "            print('{}: {}'.format(i[0], i[1]))\n",
    "    return col_dict\n",
    "\n",
    "\n",
    "## should execute \"connection.close()\" before running doall()\n",
    "def doall():\n",
    "    sqlite_file = 'mydb.db'\n",
    "    table_name = 'movies'\n",
    "\n",
    "    conn = sqlite3.connect(sqlite_file)\n",
    "    cursor = conn.cursor()\n",
    "    total_rows(cursor, table_name, print_out=True)\n",
    "    table_col_info(cursor, table_name, print_out=True)\n",
    "    values_in_col(cursor, table_name, print_out=True) # slow on large data bases\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Total rows: 14\n",
      "\n",
      "Column Info:\n",
      "ID, Name, Type, NotNull, DefaultVal, PrimaryKey\n",
      "(0, 'id', 'INTEGER', 0, None, 1)\n",
      "(1, 'title', 'TEXT', 0, None, 0)\n",
      "(2, 'director', 'TEXT', 0, None, 0)\n",
      "(3, 'year', 'INTEGER', 0, None, 0)\n",
      "(4, 'length_minutes', 'INTEGER', 0, None, 0)\n",
      "\n",
      "Number of entries per column:\n",
      "id: 14\n",
      "title: 14\n",
      "director: 14\n",
      "year: 14\n",
      "length_minutes: 14\n"
     ]
    }
   ],
   "source": [
    "doall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### evalsql()\n",
    "\n",
    "From: https://docs.python.org/2/library/sqlite3.html\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [],
   "source": [
    "def evalsql():\n",
    "    con = sqlite3.connect(\"mydb.db\")\n",
    "    con.isolation_level = None\n",
    "    cur = con.cursor()\n",
    "\n",
    "    buffer = \"\"\n",
    "\n",
    "    print (\"Enter your SQL commands to execute in sqlite3.\")\n",
    "    print (\"Enter a blank line to exit.\")\n",
    "\n",
    "    while True:\n",
    "        line = input()\n",
    "        if line == \"\":\n",
    "            break\n",
    "        buffer += line\n",
    "        print (buffer)\n",
    "        if sqlite3.complete_statement(buffer):\n",
    "            try:\n",
    "                buffer = buffer.strip()\n",
    "                cur.execute(buffer)\n",
    "                \n",
    "                if buffer.lstrip().upper().startswith(\"SELECT\"):\n",
    "                    print (cur.fetchall())\n",
    "                buffer = \"\"\n",
    "            except sqlite3.Error as e:\n",
    "                print (\"An error occurred:\", e.args[0])\n",
    "                buffer = \"\"\n",
    "\n",
    "    con.close()    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter your SQL commands to execute in sqlite3.\n",
      "Enter a blank line to exit.\n",
      "select * from movies\n",
      "select * from movies\n",
      "\n"
     ]
    }
   ],
   "source": [
    "evalsql()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}