{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction to the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "conn = sqlite3.connect(\"nominations.db\")\n",
    "schema = conn.execute(\"pragma table_info(nominations);\").fetchall()\n",
    "first_ten = conn.execute(\"select * from nominations limit 10;\").fetchall()\n",
    "\n",
    "for r in schema:\n",
    "    print(r)\n",
    "    \n",
    "for r in first_ten:\n",
    "    print(r)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Creating the ceremonies table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "years_hosts = [(2010, \"Steve Martin\"),\n",
    "               (2009, \"Hugh Jackman\"),\n",
    "               (2008, \"Jon Stewart\"),\n",
    "               (2007, \"Ellen DeGeneres\"),\n",
    "               (2006, \"Jon Stewart\"),\n",
    "               (2005, \"Chris Rock\"),\n",
    "               (2004, \"Billy Crystal\"),\n",
    "               (2003, \"Steve Martin\"),\n",
    "               (2002, \"Whoopi Goldberg\"),\n",
    "               (2001, \"Steve Martin\"),\n",
    "               (2000, \"Billy Crystal\"),\n",
    "            ]\n",
    "create_ceremonies = \"create table ceremonies (id integer primary key, year integer, host text);\"\n",
    "conn.execute(create_ceremonies)\n",
    "insert_query = \"insert into ceremonies (Year, Host) values (?,?);\"\n",
    "conn.executemany(insert_query, years_hosts)\n",
    "\n",
    "print(conn.execute(\"select * from ceremonies limit 10;\").fetchall())\n",
    "print(conn.execute(\"pragma table_info(ceremonies);\").fetchall())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Foreign key constraints"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "conn.execute(\"PRAGMA foreign_keys = ON;\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Setting up one-to-many"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "create_nominations_two = '''create table nominations_two \n",
    "(id integer primary key, \n",
    "category text, \n",
    "nominee text, \n",
    "movie text, \n",
    "character text, \n",
    "won text, \n",
    "foreign key(ceremony_id) references ceremonies(id));\n",
    "'''\n",
    "\n",
    "nom_query = '''\n",
    "select ceremonies.id as ceremony_id, nominations.category as category, \n",
    "nominations.nominee as nominee, nominations.movie as movie, \n",
    "nominations.character as character, nominations.won as won\n",
    "from nominations\n",
    "inner join ceremonies \n",
    "on nominations.year == ceremonies.year\n",
    ";\n",
    "'''\n",
    "joined_nominations = conn.execute(nom_query).fetchall()\n",
    "\n",
    "conn.execute(create_nominations_two)\n",
    "\n",
    "insert_nominations_two = '''insert into nominations_two (ceremony_id, category, nominee, movie, character, won) \n",
    "values (?,?,?,?,?,?);\n",
    "'''\n",
    "\n",
    "conn.executemany(insert_nominations_two, joined_nominations)\n",
    "print(conn.execute(\"select * from nominations_two limit 5;\").fetchall())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Deleting and renaming tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "drop_nomimations = \"drop table nominations;\"\n",
    "conn.execute(drop_nominations_two)\n",
    "\n",
    "rename_nominations_two = \"alter table nominations_two rename to nominations;\"\n",
    "conn.execute(rename_nominations_two)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Creating a join table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "create_movies = \"create table movies (id integer primary key,movie text);\"\n",
    "create_actors = \"create table actors (id integer primary key,actor text);\"\n",
    "create_movies_actors = '''create table movies_actors (id INTEGER PRIMARY KEY,\n",
    "movie_id INTEGER references movies(id), actor_id INTEGER references actors(id));\n",
    "'''\n",
    "conn.execute(create_movies)\n",
    "conn.execute(create_actors)\n",
    "conn.execute(create_movies_actors)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Populating the movies and actors tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "insert_movies = \"insert into movies (movie) select distinct movie from nominations;\"\n",
    "insert_actors = \"insert into actors (actor) select distinct nominee from nominations;\"\n",
    "conn.execute(insert_movies)\n",
    "conn.execute(insert_actors)\n",
    "\n",
    "print(conn.execute(\"select * from movies limit 5;\").fetchall())\n",
    "print(conn.execute(\"select * from actors limit 5;\").fetchall())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Populating a join table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "pairs_query = \"select movie,nominee from nominations;\"\n",
    "movie_actor_pairs = conn.execute(pairs_query).fetchall()\n",
    "\n",
    "join_table_insert = \"insert into movies_actors (movie_id, actor_id) values ((select id from movies where movie == ?),(select id from actors where actor == ?));\"\n",
    "conn.executemany(join_table_insert,movie_actor_pairs)\n",
    "\n",
    "print(conn.execute(\"select * from movies_actors limit 5;\").fetchall())"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}