queries2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SQL Queries\n",
    "\n",
    "To be able to execute SQL queries from within a Jupyter notebook, we will use the `sql_magic` extension (https://github.com/pivotal/sql_magic)\n",
    "\n",
    "    sudo pip3 install -U sql_magic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn_string = 'mysql://{user}:{password}@{host}/?charset=utf8'.format(\n",
    "    host = 'db.ipeirotis.org', \n",
    "    user = 'student',\n",
    "    password = 'dwdstudent2015',\n",
    "    encoding = 'utf-8')\n",
    "engine = create_engine(conn_string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "\n",
       "    require(['notebook/js/codecell'], function(codecell) {\n",
       "      // https://github.com/jupyter/notebook/issues/2453\n",
       "      codecell.CodeCell.options_default.highlight_modes['magic_text/x-sql'] = {'reg':[/^%read_sql/, /.*=\\s*%read_sql/,\n",
       "                                                                                      /^%%read_sql/]};\n",
       "      Jupyter.notebook.events.one('kernel_ready.Kernel', function(){\n",
       "          console.log('BBBBB');\n",
       "          Jupyter.notebook.get_cells().map(function(cell){\n",
       "              if (cell.cell_type == 'code'){ cell.auto_highlight(); } }) ;\n",
       "      });\n",
       "    });\n",
       "    "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%reload_ext sql_magic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "%config SQL.conn_name = 'engine'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aggregation Queries (IMDB Database)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:10:04 PM UTC; Query executed in 0.00 m"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<sql_magic.exceptions.EmptyResult at 0x7f610325f4a8>"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "USE imdb;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Number of movies for each director"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:10:04 PM UTC; Query executed in 0.00 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>director_id</th>\n",
       "      <th>NumberOfMovies</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>96</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>97</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>98</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>99</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>100</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    director_id  NumberOfMovies\n",
       "0             1               1\n",
       "1             2               1\n",
       "2             3               2\n",
       "3             4               1\n",
       "4             5               1\n",
       "..          ...             ...\n",
       "95           96               1\n",
       "96           97               5\n",
       "97           98               2\n",
       "98           99               4\n",
       "99          100               2\n",
       "\n",
       "[100 rows x 2 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "SELECT director_id, count(*) AS NumberOfMovies\n",
    "FROM movies_directors \n",
    "GROUP BY director_id\n",
    "LIMIT 100;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Rank directors by the number of movies they directed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:10:04 PM UTC; Query executed in 0.01 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>director_id</th>\n",
       "      <th>NumberOfMovies</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>25116</td>\n",
       "      <td>619</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>56530</td>\n",
       "      <td>562</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>30570</td>\n",
       "      <td>536</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9277</td>\n",
       "      <td>370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1958</td>\n",
       "      <td>360</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88599</th>\n",
       "      <td>8401</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88600</th>\n",
       "      <td>511</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88601</th>\n",
       "      <td>68245</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88602</th>\n",
       "      <td>57729</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88603</th>\n",
       "      <td>61833</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>88604 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       director_id  NumberOfMovies\n",
       "0            25116             619\n",
       "1            56530             562\n",
       "2            30570             536\n",
       "3             9277             370\n",
       "4             1958             360\n",
       "...            ...             ...\n",
       "88599         8401               1\n",
       "88600          511               1\n",
       "88601        68245               1\n",
       "88602        57729               1\n",
       "88603        61833               1\n",
       "\n",
       "[88604 rows x 2 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "SELECT director_id, count(*) AS NumberOfMovies\n",
    "FROM movies_directors \n",
    "GROUP BY director_id\n",
    "ORDER BY count(*) desc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Find the number of actors in each movie"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:10:05 PM UTC; Query executed in 0.10 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>movie_id</th>\n",
       "      <th>count(*)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300247</th>\n",
       "      <td>412315</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300248</th>\n",
       "      <td>412316</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300249</th>\n",
       "      <td>412317</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300250</th>\n",
       "      <td>412318</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300251</th>\n",
       "      <td>412319</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>300252 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        movie_id  count(*)\n",
       "0              0         2\n",
       "1              2        20\n",
       "2              3         4\n",
       "3              4         4\n",
       "4              5         1\n",
       "...          ...       ...\n",
       "300247    412315         1\n",
       "300248    412316        15\n",
       "300249    412317        11\n",
       "300250    412318         9\n",
       "300251    412319        13\n",
       "\n",
       "[300252 rows x 2 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "SELECT movie_id, count(*) \n",
    "FROM roles \n",
    "GROUP BY movie_id"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Find the movies with more than 100 actors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:10:11 PM UTC; Query executed in 0.10 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>movie_id</th>\n",
       "      <th>count(*)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>687</td>\n",
       "      <td>153</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>846</td>\n",
       "      <td>174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1674</td>\n",
       "      <td>164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1703</td>\n",
       "      <td>114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2250</td>\n",
       "      <td>112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>544</th>\n",
       "      <td>411545</td>\n",
       "      <td>188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>545</th>\n",
       "      <td>411546</td>\n",
       "      <td>205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>546</th>\n",
       "      <td>411585</td>\n",
       "      <td>108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>547</th>\n",
       "      <td>411802</td>\n",
       "      <td>533</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>548</th>\n",
       "      <td>411809</td>\n",
       "      <td>129</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>549 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     movie_id  count(*)\n",
       "0         687       153\n",
       "1         846       174\n",
       "2        1674       164\n",
       "3        1703       114\n",
       "4        2250       112\n",
       "..        ...       ...\n",
       "544    411545       188\n",
       "545    411546       205\n",
       "546    411585       108\n",
       "547    411802       533\n",
       "548    411809       129\n",
       "\n",
       "[549 rows x 2 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "SELECT movie_id, count(*) \n",
    "FROM roles \n",
    "GROUP BY movie_id\n",
    "HAVING count(*) > 100;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Find the most popular genres (basd on the number of movies)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:10:17 PM UTC; Query executed in 0.01 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>genre</th>\n",
       "      <th>count(*)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Short</td>\n",
       "      <td>82597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Drama</td>\n",
       "      <td>74615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Comedy</td>\n",
       "      <td>57860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Documentary</td>\n",
       "      <td>42320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Adult</td>\n",
       "      <td>20667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Animation</td>\n",
       "      <td>17888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Action</td>\n",
       "      <td>14885</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Romance</td>\n",
       "      <td>13873</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Crime</td>\n",
       "      <td>12966</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Family</td>\n",
       "      <td>11232</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Thriller</td>\n",
       "      <td>10953</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Adventure</td>\n",
       "      <td>8992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Western</td>\n",
       "      <td>8539</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Horror</td>\n",
       "      <td>7520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Musical</td>\n",
       "      <td>7055</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Music</td>\n",
       "      <td>5664</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Fantasy</td>\n",
       "      <td>5223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Sci-Fi</td>\n",
       "      <td>5111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Mystery</td>\n",
       "      <td>4971</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>War</td>\n",
       "      <td>4436</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Film-Noir</td>\n",
       "      <td>417</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          genre  count(*)\n",
       "0         Short     82597\n",
       "1         Drama     74615\n",
       "2        Comedy     57860\n",
       "3   Documentary     42320\n",
       "4         Adult     20667\n",
       "5     Animation     17888\n",
       "6        Action     14885\n",
       "7       Romance     13873\n",
       "8         Crime     12966\n",
       "9        Family     11232\n",
       "10     Thriller     10953\n",
       "11    Adventure      8992\n",
       "12      Western      8539\n",
       "13       Horror      7520\n",
       "14      Musical      7055\n",
       "15        Music      5664\n",
       "16      Fantasy      5223\n",
       "17       Sci-Fi      5111\n",
       "18      Mystery      4971\n",
       "19          War      4436\n",
       "20    Film-Noir       417"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "SELECT genre, count(*) \n",
    "FROM movies_genres\n",
    "GROUP BY genre\n",
    "ORDER BY count(*) desc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Find the average rank of the movies in the database, per year of release"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:10:17 PM UTC; Query executed in 0.01 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>avg(rank)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1888</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1890</td>\n",
       "      <td>7.300000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1891</td>\n",
       "      <td>3.683333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1892</td>\n",
       "      <td>2.866667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1893</td>\n",
       "      <td>6.800000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115</th>\n",
       "      <td>2004</td>\n",
       "      <td>6.217399</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116</th>\n",
       "      <td>2005</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>2006</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>2007</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>2008</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>120 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     year  avg(rank)\n",
       "0    1888        NaN\n",
       "1    1890   7.300000\n",
       "2    1891   3.683333\n",
       "3    1892   2.866667\n",
       "4    1893   6.800000\n",
       "..    ...        ...\n",
       "115  2004   6.217399\n",
       "116  2005        NaN\n",
       "117  2006        NaN\n",
       "118  2007        NaN\n",
       "119  2008        NaN\n",
       "\n",
       "[120 rows x 2 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "SELECT year, avg(rank) \n",
    "FROM movies\n",
    "GROUP BY year"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aggregation Queries (Facebook database)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:10:18 PM UTC; Query executed in 0.00 m"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<sql_magic.exceptions.EmptyResult at 0x7f610325f4e0>"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "USE facebook"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### List the number of males and females"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:10:18 PM UTC; Query executed in 0.00 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sex</th>\n",
       "      <th>count(*)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>4498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Female</td>\n",
       "      <td>12311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Male</td>\n",
       "      <td>8975</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      sex  count(*)\n",
       "0    None      4498\n",
       "1  Female     12311\n",
       "2    Male      8975"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "SELECT sex, count(*) \n",
    "FROM Profiles \n",
    "GROUP BY sex"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### List the number of students for each political view"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:10:18 PM UTC; Query executed in 0.00 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PoliticalViews</th>\n",
       "      <th>count(*)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>11091</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Apathetic</td>\n",
       "      <td>805</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Conservative</td>\n",
       "      <td>936</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Liberal</td>\n",
       "      <td>6461</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Libertarian</td>\n",
       "      <td>325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Moderate</td>\n",
       "      <td>2898</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Other</td>\n",
       "      <td>824</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Very Conservative</td>\n",
       "      <td>167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Very Liberal</td>\n",
       "      <td>2277</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      PoliticalViews  count(*)\n",
       "0               None     11091\n",
       "1          Apathetic       805\n",
       "2       Conservative       936\n",
       "3            Liberal      6461\n",
       "4        Libertarian       325\n",
       "5           Moderate      2898\n",
       "6              Other       824\n",
       "7  Very Conservative       167\n",
       "8       Very Liberal      2277"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "SELECT PoliticalViews, count(*) \n",
    "FROM Profiles \n",
    "GROUP BY PoliticalViews"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### List the number of males and female students for each political view"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:10:18 PM UTC; Query executed in 0.00 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Sex</th>\n",
       "      <th>PoliticalViews</th>\n",
       "      <th>count(*)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Female</td>\n",
       "      <td>Apathetic</td>\n",
       "      <td>309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Female</td>\n",
       "      <td>Conservative</td>\n",
       "      <td>428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Female</td>\n",
       "      <td>Liberal</td>\n",
       "      <td>4054</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Female</td>\n",
       "      <td>Libertarian</td>\n",
       "      <td>113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Female</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>1444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Female</td>\n",
       "      <td>Other</td>\n",
       "      <td>280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Female</td>\n",
       "      <td>Very Conservative</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Female</td>\n",
       "      <td>Very Liberal</td>\n",
       "      <td>1362</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Male</td>\n",
       "      <td>Apathetic</td>\n",
       "      <td>462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Male</td>\n",
       "      <td>Conservative</td>\n",
       "      <td>474</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Male</td>\n",
       "      <td>Liberal</td>\n",
       "      <td>2196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Male</td>\n",
       "      <td>Libertarian</td>\n",
       "      <td>196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Male</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>1379</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Male</td>\n",
       "      <td>Other</td>\n",
       "      <td>482</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Male</td>\n",
       "      <td>Very Conservative</td>\n",
       "      <td>107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Male</td>\n",
       "      <td>Very Liberal</td>\n",
       "      <td>813</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Sex     PoliticalViews  count(*)\n",
       "0   Female          Apathetic       309\n",
       "1   Female       Conservative       428\n",
       "2   Female            Liberal      4054\n",
       "3   Female        Libertarian       113\n",
       "4   Female           Moderate      1444\n",
       "5   Female              Other       280\n",
       "6   Female  Very Conservative        38\n",
       "7   Female       Very Liberal      1362\n",
       "8     Male          Apathetic       462\n",
       "9     Male       Conservative       474\n",
       "10    Male            Liberal      2196\n",
       "11    Male        Libertarian       196\n",
       "12    Male           Moderate      1379\n",
       "13    Male              Other       482\n",
       "14    Male  Very Conservative       107\n",
       "15    Male       Very Liberal       813"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "SELECT Sex, PoliticalViews, count(*) \n",
    "FROM Profiles \n",
    "WHERE Sex is not null and PoliticalViews is not null\n",
    "GROUP BY Sex, PoliticalViews"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### List the number of students per each birth year: Use the Year(date) function to get the year value FROM a dateime column List only years that have at least 10 students"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:10:18 PM UTC; Query executed in 0.00 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>YEAR(birthday)</th>\n",
       "      <th>count(*)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1905</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1974</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1975</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1976</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1977</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1978</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1979</td>\n",
       "      <td>159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1980</td>\n",
       "      <td>309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1981</td>\n",
       "      <td>585</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1982</td>\n",
       "      <td>1387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1983</td>\n",
       "      <td>2601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1984</td>\n",
       "      <td>2985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1985</td>\n",
       "      <td>3525</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1986</td>\n",
       "      <td>3735</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1987</td>\n",
       "      <td>2870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1988</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    YEAR(birthday)  count(*)\n",
       "0             1905        34\n",
       "1             1974        29\n",
       "2             1975        28\n",
       "3             1976        35\n",
       "4             1977        61\n",
       "5             1978        90\n",
       "6             1979       159\n",
       "7             1980       309\n",
       "8             1981       585\n",
       "9             1982      1387\n",
       "10            1983      2601\n",
       "11            1984      2985\n",
       "12            1985      3525\n",
       "13            1986      3735\n",
       "14            1987      2870\n",
       "15            1988        57"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "SELECT YEAR(birthday), count(*) \n",
    "FROM Profiles \n",
    "WHERE birthday is not null\n",
    "GROUP BY YEAR(birthday)\n",
    "HAVING count(*) > 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Find the most popular tv shows and books"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:10:18 PM UTC; Query executed in 0.00 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TVShow</th>\n",
       "      <th>count(*)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Family Guy</td>\n",
       "      <td>1146</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Sex And The City</td>\n",
       "      <td>649</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lost</td>\n",
       "      <td>640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arrested Development</td>\n",
       "      <td>610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Grey s Anatomy</td>\n",
       "      <td>575</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6482</th>\n",
       "      <td>Laverne And Shirley</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6483</th>\n",
       "      <td>Taradise Athf Chapelle Arrested Development Si...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6484</th>\n",
       "      <td>Ellen Degeneres Show</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6485</th>\n",
       "      <td>Hilarious Reality Shows</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6486</th>\n",
       "      <td>Family Guy By Far</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6487 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 TVShow  count(*)\n",
       "0                                            Family Guy      1146\n",
       "1                                      Sex And The City       649\n",
       "2                                                  Lost       640\n",
       "3                                  Arrested Development       610\n",
       "4                                        Grey s Anatomy       575\n",
       "...                                                 ...       ...\n",
       "6482                                Laverne And Shirley         1\n",
       "6483  Taradise Athf Chapelle Arrested Development Si...         1\n",
       "6484                               Ellen Degeneres Show         1\n",
       "6485                            Hilarious Reality Shows         1\n",
       "6486                                  Family Guy By Far         1\n",
       "\n",
       "[6487 rows x 2 columns]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "SELECT TVShow, count(*) \n",
    "FROM FavoriteTVShows \n",
    "WHERE TVShow is not null \n",
    "GROUP BY TVShow\n",
    "ORDER BY count(*) desc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:10:18 PM UTC; Query executed in 0.04 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Book</th>\n",
       "      <th>count(*)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Harry Potter</td>\n",
       "      <td>1320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Catcher In The Rye</td>\n",
       "      <td>1079</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>The Great Gatsby</td>\n",
       "      <td>963</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1984</td>\n",
       "      <td>725</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Pride And Prejudice</td>\n",
       "      <td>602</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28665</th>\n",
       "      <td>Fantasy Stuff Like Narnia And Madeline L Engle</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28666</th>\n",
       "      <td>Fantasy Stuff Sword Of Truth And Exotic Car Ma...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28667</th>\n",
       "      <td>Far Away</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28668</th>\n",
       "      <td>Far From The Maddening Croud</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28669</th>\n",
       "      <td>Far From The Madding Crowd Hardy</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>28670 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    Book  count(*)\n",
       "0                                           Harry Potter      1320\n",
       "1                                     Catcher In The Rye      1079\n",
       "2                                       The Great Gatsby       963\n",
       "3                                                   1984       725\n",
       "4                                    Pride And Prejudice       602\n",
       "...                                                  ...       ...\n",
       "28665     Fantasy Stuff Like Narnia And Madeline L Engle         1\n",
       "28666  Fantasy Stuff Sword Of Truth And Exotic Car Ma...         1\n",
       "28667                                           Far Away         1\n",
       "28668                       Far From The Maddening Croud         1\n",
       "28669                   Far From The Madding Crowd Hardy         1\n",
       "\n",
       "[28670 rows x 2 columns]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "SELECT Book, count(*) \n",
    "FROM FavoriteBooks\n",
    "WHERE Book is not null \n",
    "GROUP BY Book\n",
    "ORDER BY count(*) desc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Find the number of students in various relationship statuses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:10:21 PM UTC; Query executed in 0.00 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Status</th>\n",
       "      <th>count(*)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Engaged</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>In a Relationship</td>\n",
       "      <td>4851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>In an Open Relationship</td>\n",
       "      <td>565</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>It's complicated</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Married</td>\n",
       "      <td>2337</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Single</td>\n",
       "      <td>7872</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Status  count(*)\n",
       "0                  Engaged         1\n",
       "1        In a Relationship      4851\n",
       "2  In an Open Relationship       565\n",
       "3         It's complicated        17\n",
       "4                  Married      2337\n",
       "5                   Single      7872"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "SELECT Status, count(*) \n",
    "FROM Relationship\n",
    "GROUP BY Status"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Find the most popular majors (concentrations)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:10:21 PM UTC; Query executed in 0.00 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Concentration</th>\n",
       "      <th>count(*)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Finance</td>\n",
       "      <td>1810</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Psychology</td>\n",
       "      <td>1571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Economics</td>\n",
       "      <td>1533</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Journalism and Mass Communication</td>\n",
       "      <td>1267</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Politics</td>\n",
       "      <td>1196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134</th>\n",
       "      <td>Education (minor only; through School of Educa...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135</th>\n",
       "      <td>Slavic Studies</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>German and Linguistics (major only)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>Ancient Studies (minor only)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138</th>\n",
       "      <td>Irish Studies (minor only)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>139 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         Concentration  count(*)\n",
       "0                                              Finance      1810\n",
       "1                                           Psychology      1571\n",
       "2                                            Economics      1533\n",
       "3                    Journalism and Mass Communication      1267\n",
       "4                                             Politics      1196\n",
       "..                                                 ...       ...\n",
       "134  Education (minor only; through School of Educa...         1\n",
       "135                                     Slavic Studies         1\n",
       "136                German and Linguistics (major only)         1\n",
       "137                       Ancient Studies (minor only)         1\n",
       "138                         Irish Studies (minor only)         1\n",
       "\n",
       "[139 rows x 2 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "SELECT Concentration,count(*) FROM Concentration\n",
    "WHERE Concentration is not null\n",
    "GROUP BY Concentration\n",
    "ORDER BY count(*) desc;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### JOINS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:10:21 PM UTC; Query executed in 0.00 m"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<sql_magic.exceptions.EmptyResult at 0x7f61031d4ef0>"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "USE imdb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:10:21 PM UTC; Query executed in 0.01 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>year</th>\n",
       "      <th>rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>#7 Train: An Immigrant Journey, The</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>31</td>\n",
       "      <td>$pent</td>\n",
       "      <td>2000</td>\n",
       "      <td>4.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>34</td>\n",
       "      <td>&amp; frres</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>46</td>\n",
       "      <td>'70s: The Decade That Changed Television, The</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>176</td>\n",
       "      <td>'N Sync &amp; Britney Spears: Your #1 Video Reques...</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11638</th>\n",
       "      <td>412155</td>\n",
       "      <td>\"Quin dijo miedo?\"</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11639</th>\n",
       "      <td>412218</td>\n",
       "      <td>\"ndernes magt\"</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11640</th>\n",
       "      <td>412221</td>\n",
       "      <td>\"pent hus\"</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11641</th>\n",
       "      <td>412232</td>\n",
       "      <td>\"a s'en va et a revient\"</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11642</th>\n",
       "      <td>412267</td>\n",
       "      <td>\"v Hangja, Az\"</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11643 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           id                                               name  year  rank\n",
       "0           1                #7 Train: An Immigrant Journey, The  2000   NaN\n",
       "1          31                                              $pent  2000   4.3\n",
       "2          34                                            & frres  2000   NaN\n",
       "3          46      '70s: The Decade That Changed Television, The  2000   NaN\n",
       "4         176  'N Sync & Britney Spears: Your #1 Video Reques...  2000   NaN\n",
       "...       ...                                                ...   ...   ...\n",
       "11638  412155                                 \"Quin dijo miedo?\"  2000   NaN\n",
       "11639  412218                                     \"ndernes magt\"  2000   NaN\n",
       "11640  412221                                         \"pent hus\"  2000   NaN\n",
       "11641  412232                           \"a s'en va et a revient\"  2000   NaN\n",
       "11642  412267                                     \"v Hangja, Az\"  2000   NaN\n",
       "\n",
       "[11643 rows x 4 columns]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "# List all the movies from year 2000\n",
    "SELECT *\n",
    "FROM movies M\n",
    "WHERE M.year = 2000;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### List all the movies from year 2000 and their genres"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:10:22 PM UTC; Query executed in 0.01 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>year</th>\n",
       "      <th>rank</th>\n",
       "      <th>movie_id</th>\n",
       "      <th>genre</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>#7 Train: An Immigrant Journey, The</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>Short</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>#7 Train: An Immigrant Journey, The</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>Documentary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>31</td>\n",
       "      <td>$pent</td>\n",
       "      <td>2000</td>\n",
       "      <td>4.3</td>\n",
       "      <td>31</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>34</td>\n",
       "      <td>&amp; frres</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34</td>\n",
       "      <td>Short</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>46</td>\n",
       "      <td>'70s: The Decade That Changed Television, The</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46</td>\n",
       "      <td>Documentary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12138</th>\n",
       "      <td>378537</td>\n",
       "      <td>ber die Montage</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>378537</td>\n",
       "      <td>Short</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12139</th>\n",
       "      <td>378550</td>\n",
       "      <td>berfall, Der</td>\n",
       "      <td>2000</td>\n",
       "      <td>7.0</td>\n",
       "      <td>378550</td>\n",
       "      <td>Comedy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12140</th>\n",
       "      <td>378550</td>\n",
       "      <td>berfall, Der</td>\n",
       "      <td>2000</td>\n",
       "      <td>7.0</td>\n",
       "      <td>378550</td>\n",
       "      <td>Crime</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12141</th>\n",
       "      <td>378550</td>\n",
       "      <td>berfall, Der</td>\n",
       "      <td>2000</td>\n",
       "      <td>7.0</td>\n",
       "      <td>378550</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12142</th>\n",
       "      <td>378557</td>\n",
       "      <td>berleben in Belfast</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>378557</td>\n",
       "      <td>Documentary</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>12143 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           id                                           name  year  rank  \\\n",
       "0           1            #7 Train: An Immigrant Journey, The  2000   NaN   \n",
       "1           1            #7 Train: An Immigrant Journey, The  2000   NaN   \n",
       "2          31                                          $pent  2000   4.3   \n",
       "3          34                                        & frres  2000   NaN   \n",
       "4          46  '70s: The Decade That Changed Television, The  2000   NaN   \n",
       "...       ...                                            ...   ...   ...   \n",
       "12138  378537                                ber die Montage  2000   NaN   \n",
       "12139  378550                                   berfall, Der  2000   7.0   \n",
       "12140  378550                                   berfall, Der  2000   7.0   \n",
       "12141  378550                                   berfall, Der  2000   7.0   \n",
       "12142  378557                            berleben in Belfast  2000   NaN   \n",
       "\n",
       "       movie_id        genre  \n",
       "0             1        Short  \n",
       "1             1  Documentary  \n",
       "2            31        Drama  \n",
       "3            34        Short  \n",
       "4            46  Documentary  \n",
       "...         ...          ...  \n",
       "12138    378537        Short  \n",
       "12139    378550       Comedy  \n",
       "12140    378550        Crime  \n",
       "12141    378550        Drama  \n",
       "12142    378557  Documentary  \n",
       "\n",
       "[12143 rows x 6 columns]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "SELECT *\n",
    "FROM movies M INNER JOIN movies_genres MG ON M.id=MG.movie_id\n",
    "WHERE M.year = 2000;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### List all the Drama movies from year 2000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:10:22 PM UTC; Query executed in 0.01 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>year</th>\n",
       "      <th>rank</th>\n",
       "      <th>movie_id</th>\n",
       "      <th>genre</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>31</td>\n",
       "      <td>$pent</td>\n",
       "      <td>2000</td>\n",
       "      <td>4.3</td>\n",
       "      <td>31</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>428</td>\n",
       "      <td>...Or Forever Hold Your Peace</td>\n",
       "      <td>2000</td>\n",
       "      <td>6.9</td>\n",
       "      <td>428</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>598</td>\n",
       "      <td>1. de ei</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>598</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>992</td>\n",
       "      <td>12 Rounds</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>992</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1023</td>\n",
       "      <td>12x12</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1023</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1864</th>\n",
       "      <td>377490</td>\n",
       "      <td>a, c'est vraiment toi</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>377490</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1865</th>\n",
       "      <td>378026</td>\n",
       "      <td>tre chair, L'</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>378026</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1866</th>\n",
       "      <td>378121</td>\n",
       "      <td>skabrn jarinnar</td>\n",
       "      <td>2000</td>\n",
       "      <td>4.2</td>\n",
       "      <td>378121</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1867</th>\n",
       "      <td>378308</td>\n",
       "      <td>tztalmann und seine Welt. Das Jahr bevor er sc...</td>\n",
       "      <td>2000</td>\n",
       "      <td>6.3</td>\n",
       "      <td>378308</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1868</th>\n",
       "      <td>378550</td>\n",
       "      <td>berfall, Der</td>\n",
       "      <td>2000</td>\n",
       "      <td>7.0</td>\n",
       "      <td>378550</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1869 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          id                                               name  year  rank  \\\n",
       "0         31                                              $pent  2000   4.3   \n",
       "1        428                      ...Or Forever Hold Your Peace  2000   6.9   \n",
       "2        598                                           1. de ei  2000   NaN   \n",
       "3        992                                          12 Rounds  2000   NaN   \n",
       "4       1023                                              12x12  2000   NaN   \n",
       "...      ...                                                ...   ...   ...   \n",
       "1864  377490                              a, c'est vraiment toi  2000   NaN   \n",
       "1865  378026                                      tre chair, L'  2000   NaN   \n",
       "1866  378121                                    skabrn jarinnar  2000   4.2   \n",
       "1867  378308  tztalmann und seine Welt. Das Jahr bevor er sc...  2000   6.3   \n",
       "1868  378550                                       berfall, Der  2000   7.0   \n",
       "\n",
       "      movie_id  genre  \n",
       "0           31  Drama  \n",
       "1          428  Drama  \n",
       "2          598  Drama  \n",
       "3          992  Drama  \n",
       "4         1023  Drama  \n",
       "...        ...    ...  \n",
       "1864    377490  Drama  \n",
       "1865    378026  Drama  \n",
       "1866    378121  Drama  \n",
       "1867    378308  Drama  \n",
       "1868    378550  Drama  \n",
       "\n",
       "[1869 rows x 6 columns]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "\n",
    "SELECT *\n",
    "FROM movies M INNER JOIN movies_genres MG ON M.id=MG.movie_id\n",
    "WHERE M.year = 2000 AND MG.genre = 'Drama';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### List all the movies from year 2000 and their average rating broken down by genre"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:10:23 PM UTC; Query executed in 0.01 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AVG(M.rank)</th>\n",
       "      <th>genre</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.454639</td>\n",
       "      <td>Action</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5.782759</td>\n",
       "      <td>Adventure</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6.505882</td>\n",
       "      <td>Animation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.899568</td>\n",
       "      <td>Comedy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5.908228</td>\n",
       "      <td>Crime</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6.913761</td>\n",
       "      <td>Documentary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>6.087035</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>5.787879</td>\n",
       "      <td>Family</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5.938158</td>\n",
       "      <td>Fantasy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>4.973913</td>\n",
       "      <td>Horror</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>6.512500</td>\n",
       "      <td>Music</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>6.070732</td>\n",
       "      <td>Musical</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>5.956897</td>\n",
       "      <td>Mystery</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>6.199571</td>\n",
       "      <td>Romance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>5.398592</td>\n",
       "      <td>Sci-Fi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>6.560808</td>\n",
       "      <td>Short</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>5.281185</td>\n",
       "      <td>Thriller</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>6.440741</td>\n",
       "      <td>War</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>5.290909</td>\n",
       "      <td>Western</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    AVG(M.rank)        genre\n",
       "0      5.454639       Action\n",
       "1           NaN        Adult\n",
       "2      5.782759    Adventure\n",
       "3      6.505882    Animation\n",
       "4      5.899568       Comedy\n",
       "5      5.908228        Crime\n",
       "6      6.913761  Documentary\n",
       "7      6.087035        Drama\n",
       "8      5.787879       Family\n",
       "9      5.938158      Fantasy\n",
       "10     4.973913       Horror\n",
       "11     6.512500        Music\n",
       "12     6.070732      Musical\n",
       "13     5.956897      Mystery\n",
       "14     6.199571      Romance\n",
       "15     5.398592       Sci-Fi\n",
       "16     6.560808        Short\n",
       "17     5.281185     Thriller\n",
       "18     6.440741          War\n",
       "19     5.290909      Western"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "SELECT AVG(M.rank), MG.genre\n",
    "FROM movies M INNER JOIN movies_genres MG ON M.id=MG.movie_id\n",
    "WHERE M.year = 2000\n",
    "GROUP BY MG.genre;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:10:23 PM UTC; Query executed in 0.02 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>avg_rating</th>\n",
       "      <th>genre</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6.701768</td>\n",
       "      <td>Film-Noir</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6.557896</td>\n",
       "      <td>Animation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6.496829</td>\n",
       "      <td>Documentary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6.428049</td>\n",
       "      <td>Adult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6.415912</td>\n",
       "      <td>Music</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6.394257</td>\n",
       "      <td>Short</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6.377797</td>\n",
       "      <td>War</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>6.315130</td>\n",
       "      <td>Family</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>6.156967</td>\n",
       "      <td>Romance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>6.137848</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>6.087347</td>\n",
       "      <td>Musical</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>5.932294</td>\n",
       "      <td>Mystery</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>5.905311</td>\n",
       "      <td>Comedy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>5.860042</td>\n",
       "      <td>Crime</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>5.848193</td>\n",
       "      <td>Fantasy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>5.651213</td>\n",
       "      <td>Western</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>5.581717</td>\n",
       "      <td>Adventure</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>5.527481</td>\n",
       "      <td>Thriller</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>5.339820</td>\n",
       "      <td>Action</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>5.010579</td>\n",
       "      <td>Sci-Fi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>4.756930</td>\n",
       "      <td>Horror</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    avg_rating        genre\n",
       "0     6.701768    Film-Noir\n",
       "1     6.557896    Animation\n",
       "2     6.496829  Documentary\n",
       "3     6.428049        Adult\n",
       "4     6.415912        Music\n",
       "5     6.394257        Short\n",
       "6     6.377797          War\n",
       "7     6.315130       Family\n",
       "8     6.156967      Romance\n",
       "9     6.137848        Drama\n",
       "10    6.087347      Musical\n",
       "11    5.932294      Mystery\n",
       "12    5.905311       Comedy\n",
       "13    5.860042        Crime\n",
       "14    5.848193      Fantasy\n",
       "15    5.651213      Western\n",
       "16    5.581717    Adventure\n",
       "17    5.527481     Thriller\n",
       "18    5.339820       Action\n",
       "19    5.010579       Sci-Fi\n",
       "20    4.756930       Horror"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "# List the average ratings for the movies broken down by genre\n",
    "SELECT AVG(M.rank) AS avg_rating, MG.genre\n",
    "FROM movies M INNER JOIN movies_genres MG ON M.id=MG.movie_id\n",
    "GROUP BY MG.genre\n",
    "ORDER BY avg_rating DESC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:10:24 PM UTC; Query executed in 0.04 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Dan</td>\n",
       "      <td>Aykroyd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>E. Hampton</td>\n",
       "      <td>Beagle</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ned</td>\n",
       "      <td>Beatty</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>John</td>\n",
       "      <td>Belushi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jordan</td>\n",
       "      <td>Brian</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1635</th>\n",
       "      <td>David</td>\n",
       "      <td>Soul</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1636</th>\n",
       "      <td>Joan</td>\n",
       "      <td>Darling</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1637</th>\n",
       "      <td>Christine</td>\n",
       "      <td>Matchett</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1638</th>\n",
       "      <td>Luther</td>\n",
       "      <td>Adler</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1639</th>\n",
       "      <td>Roy</td>\n",
       "      <td>Thinnes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1640 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      first_name last_name\n",
       "0            Dan   Aykroyd\n",
       "1     E. Hampton    Beagle\n",
       "2            Ned    Beatty\n",
       "3           John   Belushi\n",
       "4         Jordan     Brian\n",
       "...          ...       ...\n",
       "1635       David      Soul\n",
       "1636        Joan   Darling\n",
       "1637   Christine  Matchett\n",
       "1638      Luther     Adler\n",
       "1639         Roy   Thinnes\n",
       "\n",
       "[1640 rows x 2 columns]"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "SELECT distinct a.first_name, a.last_name \n",
    "FROM actors a INNER JOIN roles r ON a.id = r.actor_id\n",
    "    INNER JOIN movies_directors md ON r.movie_id = md.movie_id\n",
    "    INNER JOIN directors d ON d.id = md.director_id\n",
    "WHERE d.first_name = 'Steven' and d.last_name='Spielberg'; "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### List all the actors that worked with Steven Spielberg"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Compute the average rank for the movies direcred by steven spielberg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:10:27 PM UTC; Query executed in 0.00 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>avg(rank)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7.546429</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   avg(rank)\n",
       "0   7.546429"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "SELECT avg(rank) \n",
    "FROM movies m \n",
    "    INNER JOIN movies_directors md ON m.id = md.movie_id\n",
    "    INNER JOIN directors d ON d.id = md.director_id\n",
    "WHERE d.first_name = 'Steven' and d.last_name='Spielberg'; \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### List the movies of Brad Pitt - exclude those that he plays himself"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:10:27 PM UTC; Query executed in 0.00 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Across the Tracks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Confessions of a Dangerous Mind</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Contact</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Cool World</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Dark Side of the Sun, The</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Devil's Own, The</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Favor, The</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Fight Club</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Growing Up Grizzly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Happy Together (1989/I)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Image, The</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Interview with the Vampire: The Vampire Chroni...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Johnny Suede</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Kalifornia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Legends of the Fall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Less Than Zero</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Meet Joe Black</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Mexican, The</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Mr. and Mrs. Smith</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>No Man's Land</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Ocean's Eleven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Ocean's Twelve</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>River Runs Through It, A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Se7en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Seven Years in Tibet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Sinbad: Legend of the Seven Seas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Sleepers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Snatch.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Spy Game</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Stoning in Fulham County, A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Thelma &amp; Louise</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Too Young to Die?</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Troy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>True Romance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Twelve Monkeys</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Two-Fisted Tales</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Ultimate Fights from the Movies</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>\"Another World\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>\"Glory Days\"</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 name\n",
       "0                                   Across the Tracks\n",
       "1                     Confessions of a Dangerous Mind\n",
       "2                                             Contact\n",
       "3                                          Cool World\n",
       "4                           Dark Side of the Sun, The\n",
       "5                                    Devil's Own, The\n",
       "6                                          Favor, The\n",
       "7                                          Fight Club\n",
       "8                                  Growing Up Grizzly\n",
       "9                             Happy Together (1989/I)\n",
       "10                                         Image, The\n",
       "11  Interview with the Vampire: The Vampire Chroni...\n",
       "12                                       Johnny Suede\n",
       "13                                         Kalifornia\n",
       "14                                Legends of the Fall\n",
       "15                                     Less Than Zero\n",
       "16                                     Meet Joe Black\n",
       "17                                       Mexican, The\n",
       "18                                 Mr. and Mrs. Smith\n",
       "19                                      No Man's Land\n",
       "20                                     Ocean's Eleven\n",
       "21                                     Ocean's Twelve\n",
       "22                           River Runs Through It, A\n",
       "23                                              Se7en\n",
       "24                               Seven Years in Tibet\n",
       "25                   Sinbad: Legend of the Seven Seas\n",
       "26                                           Sleepers\n",
       "27                                            Snatch.\n",
       "28                                           Spy Game\n",
       "29                        Stoning in Fulham County, A\n",
       "30                                    Thelma & Louise\n",
       "31                                  Too Young to Die?\n",
       "32                                               Troy\n",
       "33                                       True Romance\n",
       "34                                     Twelve Monkeys\n",
       "35                                   Two-Fisted Tales\n",
       "36                    Ultimate Fights from the Movies\n",
       "37                                    \"Another World\"\n",
       "38                                       \"Glory Days\""
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "SELECT m.name \n",
    "FROM movies m \n",
    "    INNER JOIN roles r on m.id = r.movie_id \n",
    "    INNER JOIN actors a on r.actor_id = a.id\n",
    "WHERE r.role not like '%%Himself%%' and a.first_name = 'Brad' and a.last_name = 'Pitt' "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Average rank of Brad Pitt's movies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:10:27 PM UTC; Query executed in 0.00 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>avg(rank)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6.52069</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   avg(rank)\n",
       "0    6.52069"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "SELECT avg(rank) \n",
    "FROM movies m \n",
    "    INNER JOIN roles r on m.id = r.movie_id \n",
    "    INNER JOIN actors a on r.actor_id = a.id\n",
    "WHERE r.role not like '%%Himself%%' and a.first_name = 'Brad' and a.last_name = 'Pitt' \n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### List the genre of the movies where Sean Connery appears, and rank them in descending order by count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:10:27 PM UTC; Query executed in 0.00 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>genre</th>\n",
       "      <th>count(*)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Drama</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Action</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Adventure</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Thriller</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Crime</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Sci-Fi</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Comedy</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Romance</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Fantasy</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Mystery</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>War</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Documentary</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Short</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Family</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Western</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          genre  count(*)\n",
       "0         Drama        32\n",
       "1        Action        28\n",
       "2     Adventure        22\n",
       "3      Thriller        21\n",
       "4         Crime        15\n",
       "5        Sci-Fi         8\n",
       "6        Comedy         8\n",
       "7       Romance         7\n",
       "8       Fantasy         6\n",
       "9       Mystery         6\n",
       "10          War         4\n",
       "11  Documentary         3\n",
       "12        Short         2\n",
       "13       Family         1\n",
       "14      Western         1"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "SELECT mg.genre, count(*) \n",
    "FROM movies_genres mg \n",
    "    INNER JOIN movies m ON m.id =mg.movie_id\n",
    "    INNER JOIN roles r ON m.id = r.movie_id \n",
    "    INNER JOIN actors a ON r.actor_id = a.id\n",
    "WHERE r.role not like '%%Himself%%' and a.first_name = 'Sean' and a.last_name = 'Connery'\n",
    "GROUP BY genre\n",
    "ORDER BY count(*) desc\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Compute the average rank for the movies of each actor and rank the actors in descending order based on that rank. List only actors with at least 20 rated movies."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:10:27 PM UTC; Query executed in 3.57 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>gender</th>\n",
       "      <th>avg_rating</th>\n",
       "      <th>num_movies</th>\n",
       "      <th>rated_movies</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>234674</td>\n",
       "      <td>Michel</td>\n",
       "      <td>Journiac</td>\n",
       "      <td>M</td>\n",
       "      <td>8.195000</td>\n",
       "      <td>21</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>40834</td>\n",
       "      <td>Ion</td>\n",
       "      <td>Besoiu</td>\n",
       "      <td>M</td>\n",
       "      <td>8.181250</td>\n",
       "      <td>79</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>391209</td>\n",
       "      <td>Colea</td>\n",
       "      <td>Rautu</td>\n",
       "      <td>M</td>\n",
       "      <td>8.161905</td>\n",
       "      <td>56</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>123218</td>\n",
       "      <td>Gheorghe</td>\n",
       "      <td>Dinica</td>\n",
       "      <td>M</td>\n",
       "      <td>8.135484</td>\n",
       "      <td>59</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>724198</td>\n",
       "      <td>Leela</td>\n",
       "      <td>Mishra</td>\n",
       "      <td>F</td>\n",
       "      <td>8.122727</td>\n",
       "      <td>130</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9500</th>\n",
       "      <td>225254</td>\n",
       "      <td>Jon (II)</td>\n",
       "      <td>Jacobs</td>\n",
       "      <td>M</td>\n",
       "      <td>3.290476</td>\n",
       "      <td>30</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9501</th>\n",
       "      <td>727436</td>\n",
       "      <td>Melissa</td>\n",
       "      <td>Moore</td>\n",
       "      <td>F</td>\n",
       "      <td>3.286364</td>\n",
       "      <td>28</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9502</th>\n",
       "      <td>606609</td>\n",
       "      <td>Griffin</td>\n",
       "      <td>Drew</td>\n",
       "      <td>F</td>\n",
       "      <td>3.247619</td>\n",
       "      <td>36</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9503</th>\n",
       "      <td>497017</td>\n",
       "      <td>Lars</td>\n",
       "      <td>von Trier</td>\n",
       "      <td>M</td>\n",
       "      <td>3.172727</td>\n",
       "      <td>32</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9504</th>\n",
       "      <td>644511</td>\n",
       "      <td>Gabriella</td>\n",
       "      <td>Hall</td>\n",
       "      <td>F</td>\n",
       "      <td>2.765000</td>\n",
       "      <td>38</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9505 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          id first_name  last_name gender  avg_rating  num_movies  \\\n",
       "0     234674     Michel   Journiac      M    8.195000          21   \n",
       "1      40834        Ion     Besoiu      M    8.181250          79   \n",
       "2     391209      Colea      Rautu      M    8.161905          56   \n",
       "3     123218   Gheorghe     Dinica      M    8.135484          59   \n",
       "4     724198      Leela     Mishra      F    8.122727         130   \n",
       "...      ...        ...        ...    ...         ...         ...   \n",
       "9500  225254   Jon (II)     Jacobs      M    3.290476          30   \n",
       "9501  727436    Melissa      Moore      F    3.286364          28   \n",
       "9502  606609    Griffin       Drew      F    3.247619          36   \n",
       "9503  497017       Lars  von Trier      M    3.172727          32   \n",
       "9504  644511  Gabriella       Hall      F    2.765000          38   \n",
       "\n",
       "      rated_movies  \n",
       "0               20  \n",
       "1               32  \n",
       "2               21  \n",
       "3               31  \n",
       "4               22  \n",
       "...            ...  \n",
       "9500            21  \n",
       "9501            22  \n",
       "9502            21  \n",
       "9503            22  \n",
       "9504            20  \n",
       "\n",
       "[9505 rows x 7 columns]"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "SELECT a.*, avg(rank) as avg_rating, count(m.id) as num_movies, count(m.rank) as rated_movies\n",
    "FROM movies m \n",
    "    INNER JOIN roles r on m.id = r.movie_id \n",
    "    INNER JOIN actors a ON a.id = r.actor_id\n",
    "GROUP BY a.id\n",
    "HAVING count(m.rank)>=20\n",
    "ORDER BY avg(rank) desc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Subqueries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### List all the actors that have not worked with Francis Ford Coppola. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:14:02 PM UTC; Query executed in 0.05 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>Michael</td>\n",
       "      <td>'babeepower' Viera</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>Eloy</td>\n",
       "      <td>'Chincheta'</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>Dieguito</td>\n",
       "      <td>'El Cigala'</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>Antonio</td>\n",
       "      <td>'El de Chipiona'</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>José</td>\n",
       "      <td>'El Francés'</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>816728</th>\n",
       "      <td>845461</td>\n",
       "      <td>Herdís</td>\n",
       "      <td>Þorvaldsdóttir</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>816729</th>\n",
       "      <td>845462</td>\n",
       "      <td>Katla Margrét</td>\n",
       "      <td>Þorvaldsdóttir</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>816730</th>\n",
       "      <td>845463</td>\n",
       "      <td>Lilja Nótt</td>\n",
       "      <td>Þórarinsdóttir</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>816731</th>\n",
       "      <td>845464</td>\n",
       "      <td>Hólmfríður</td>\n",
       "      <td>Þórhallsdóttir</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>816732</th>\n",
       "      <td>845465</td>\n",
       "      <td>Theódóra</td>\n",
       "      <td>Þórðardóttir</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>816733 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            id     first_name           last_name gender\n",
       "0            2        Michael  'babeepower' Viera      M\n",
       "1            3           Eloy         'Chincheta'      M\n",
       "2            4       Dieguito         'El Cigala'      M\n",
       "3            5        Antonio    'El de Chipiona'      M\n",
       "4            6           José        'El Francés'      M\n",
       "...        ...            ...                 ...    ...\n",
       "816728  845461         Herdís      Þorvaldsdóttir      F\n",
       "816729  845462  Katla Margrét      Þorvaldsdóttir      F\n",
       "816730  845463     Lilja Nótt      Þórarinsdóttir      F\n",
       "816731  845464     Hólmfríður      Þórhallsdóttir      F\n",
       "816732  845465       Theódóra        Þórðardóttir      F\n",
       "\n",
       "[816733 rows x 4 columns]"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql \n",
    "SELECT * \n",
    "FROM actors \n",
    "WHERE id NOT IN (\n",
    "    SELECT DISTINCT R.actor_id\n",
    "    FROM roles R \n",
    "        INNER JOIN movies_directors MD ON R.movie_id=MD.movie_id\n",
    "        INNER JOIN directors D ON MD.director_id=D.id \n",
    "    WHERE D.first_name = 'Francis Ford' and D.last_name = 'Coppola'\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Find the average number of movies directed by each director "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:14:05 PM UTC; Query executed in 0.01 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>avg(noMovies)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4.5931</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   avg(noMovies)\n",
       "0         4.5931"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "SELECT avg(noMovies)\n",
    "FROM \n",
    "    (\n",
    "    SELECT director_id, count(*) as noMovies \n",
    "    FROM movies_directors dm \n",
    "    GROUP BY director_id\n",
    "    ) r;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Report how many directors have directed one movie, two movies, three movies, etc. (in other words, create a the distribution for the variable \"number of movies directed by a given director\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:14:05 PM UTC; Query executed in 0.00 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NumMovies</th>\n",
       "      <th>NumDirectors</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>47339</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>13678</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>6495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>3982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>2702</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <td>360</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td>370</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td>536</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199</th>\n",
       "      <td>562</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>200</th>\n",
       "      <td>619</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>201 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     NumMovies  NumDirectors\n",
       "0            1         47339\n",
       "1            2         13678\n",
       "2            3          6495\n",
       "3            4          3982\n",
       "4            5          2702\n",
       "..         ...           ...\n",
       "196        360             1\n",
       "197        370             1\n",
       "198        536             1\n",
       "199        562             1\n",
       "200        619             1\n",
       "\n",
       "[201 rows x 2 columns]"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "SELECT NumMovies, COUNT(director_id) as NumDirectors\n",
    "FROM \n",
    "    (\n",
    "    SELECT director_id, count(*) as NumMovies \n",
    "    FROM movies_directors dm \n",
    "    GROUP BY director_id\n",
    "    ) r\n",
    "GROUP BY NumMovies\n",
    "ORDER BY NumMovies;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Find the average number of movies played by each actor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:14:05 PM UTC; Query executed in 2.00 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>avg(noMovies)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4.1964</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   avg(noMovies)\n",
       "0         4.1964"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "SELECT avg(noMovies) \n",
    "FROM\n",
    "(\n",
    "SELECT actor_id, count(distinct movie_id) as noMovies \n",
    "FROM roles \n",
    "GROUP BY actor_id\n",
    ") r;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:16:05 PM UTC; Query executed in 0.16 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NumMovies</th>\n",
       "      <th>NumActors</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>508544</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>103966</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>47369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>28173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>19103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>348</th>\n",
       "      <td>543</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>349</th>\n",
       "      <td>544</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>350</th>\n",
       "      <td>549</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>351</th>\n",
       "      <td>672</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>352</th>\n",
       "      <td>909</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>353 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     NumMovies  NumActors\n",
       "0            1     508544\n",
       "1            2     103966\n",
       "2            3      47369\n",
       "3            4      28173\n",
       "4            5      19103\n",
       "..         ...        ...\n",
       "348        543          1\n",
       "349        544          3\n",
       "350        549          1\n",
       "351        672          1\n",
       "352        909          1\n",
       "\n",
       "[353 rows x 2 columns]"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "SELECT NumMovies, COUNT(actor_id) as NumActors\n",
    "FROM \n",
    "    (\n",
    "    SELECT actor_id, count(*) as NumMovies \n",
    "    FROM roles r \n",
    "    GROUP BY actor_id\n",
    "    ) r\n",
    "GROUP BY NumMovies\n",
    "ORDER BY NumMovies;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Find the maximum number of genres associated with a movie"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:16:15 PM UTC; Query executed in 0.02 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>max(noGenres)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   max(noGenres)\n",
       "0             12"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "SELECT max(noGenres) FROM (\n",
    "SELECT count(*) as noGenres\n",
    "FROM movies_genres\n",
    "GROUP BY movie_id) r;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:16:16 PM UTC; Query executed in 0.01 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>max(noMovies)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>619</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   max(noMovies)\n",
       "0            619"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "SELECT max(noMovies)FROM (\n",
    "SELECT count(*) as noMovies, director_id as director\n",
    "FROM movies_directors\n",
    "GROUP BY director_id\n",
    ") r;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:16:17 PM UTC; Query executed in 0.01 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>noMovies</th>\n",
       "      <th>director</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88599</th>\n",
       "      <td>1</td>\n",
       "      <td>88797</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88600</th>\n",
       "      <td>1</td>\n",
       "      <td>88798</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88601</th>\n",
       "      <td>1</td>\n",
       "      <td>88799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88602</th>\n",
       "      <td>1</td>\n",
       "      <td>88800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88603</th>\n",
       "      <td>1</td>\n",
       "      <td>88801</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>88604 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       noMovies  director\n",
       "0             1         1\n",
       "1             1         2\n",
       "2             2         3\n",
       "3             1         4\n",
       "4             1         5\n",
       "...         ...       ...\n",
       "88599         1     88797\n",
       "88600         1     88798\n",
       "88601         1     88799\n",
       "88602         1     88800\n",
       "88603         1     88801\n",
       "\n",
       "[88604 rows x 2 columns]"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "SELECT count(*) as noMovies, director_id as director\n",
    "FROM movies_directors\n",
    "GROUP BY director_id\n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Compare the favorite books of liberal and conservative students"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query started at 08:16:18 PM UTC; Query executed in 0.01 m"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Book</th>\n",
       "      <th>cons</th>\n",
       "      <th>libs</th>\n",
       "      <th>perc_cons</th>\n",
       "      <th>perc_libs</th>\n",
       "      <th>lift_cons</th>\n",
       "      <th>lift_libs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bible</td>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "      <td>0.0107</td>\n",
       "      <td>0.0011</td>\n",
       "      <td>9.861116</td>\n",
       "      <td>0.101408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Monkey Business</td>\n",
       "      <td>8</td>\n",
       "      <td>7</td>\n",
       "      <td>0.0085</td>\n",
       "      <td>0.0011</td>\n",
       "      <td>7.888893</td>\n",
       "      <td>0.126760</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>The Bible</td>\n",
       "      <td>21</td>\n",
       "      <td>25</td>\n",
       "      <td>0.0224</td>\n",
       "      <td>0.0039</td>\n",
       "      <td>5.798333</td>\n",
       "      <td>0.172463</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Moneyball</td>\n",
       "      <td>9</td>\n",
       "      <td>15</td>\n",
       "      <td>0.0096</td>\n",
       "      <td>0.0023</td>\n",
       "      <td>4.141666</td>\n",
       "      <td>0.241449</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>The Prince</td>\n",
       "      <td>14</td>\n",
       "      <td>25</td>\n",
       "      <td>0.0150</td>\n",
       "      <td>0.0039</td>\n",
       "      <td>3.865555</td>\n",
       "      <td>0.258695</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>The Alchemist</td>\n",
       "      <td>7</td>\n",
       "      <td>91</td>\n",
       "      <td>0.0075</td>\n",
       "      <td>0.0141</td>\n",
       "      <td>0.530983</td>\n",
       "      <td>1.883300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>Catcher In The Rye</td>\n",
       "      <td>32</td>\n",
       "      <td>452</td>\n",
       "      <td>0.0342</td>\n",
       "      <td>0.0700</td>\n",
       "      <td>0.488692</td>\n",
       "      <td>2.046278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>Lolita</td>\n",
       "      <td>10</td>\n",
       "      <td>144</td>\n",
       "      <td>0.0107</td>\n",
       "      <td>0.0223</td>\n",
       "      <td>0.479360</td>\n",
       "      <td>2.086117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>The Lovely Bones</td>\n",
       "      <td>6</td>\n",
       "      <td>93</td>\n",
       "      <td>0.0064</td>\n",
       "      <td>0.0144</td>\n",
       "      <td>0.445340</td>\n",
       "      <td>2.245473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>The Giver</td>\n",
       "      <td>7</td>\n",
       "      <td>110</td>\n",
       "      <td>0.0075</td>\n",
       "      <td>0.0170</td>\n",
       "      <td>0.439268</td>\n",
       "      <td>2.276516</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>74 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Book  cons  libs  perc_cons  perc_libs  lift_cons  lift_libs\n",
       "0                Bible    10     7     0.0107     0.0011   9.861116   0.101408\n",
       "1      Monkey Business     8     7     0.0085     0.0011   7.888893   0.126760\n",
       "2            The Bible    21    25     0.0224     0.0039   5.798333   0.172463\n",
       "3            Moneyball     9    15     0.0096     0.0023   4.141666   0.241449\n",
       "4           The Prince    14    25     0.0150     0.0039   3.865555   0.258695\n",
       "..                 ...   ...   ...        ...        ...        ...        ...\n",
       "69       The Alchemist     7    91     0.0075     0.0141   0.530983   1.883300\n",
       "70  Catcher In The Rye    32   452     0.0342     0.0700   0.488692   2.046278\n",
       "71              Lolita    10   144     0.0107     0.0223   0.479360   2.086117\n",
       "72    The Lovely Bones     6    93     0.0064     0.0144   0.445340   2.245473\n",
       "73           The Giver     7   110     0.0075     0.0170   0.439268   2.276516\n",
       "\n",
       "[74 rows x 7 columns]"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%read_sql\n",
    "\n",
    "USE facebook;\n",
    "\n",
    "SELECT BC.Book, \n",
    "    BC.C AS cons,\n",
    "    BL.L AS libs,\n",
    "    BC.C/C.NC AS perc_cons, \n",
    "    BL.L/L.NL AS perc_libs,\n",
    "    (BC.C/C.NC)/(BL.L/L.NL) AS lift_cons,\n",
    "    (BL.L/L.NL)/(BC.C/C.NC) AS lift_libs\n",
    "FROM \n",
    "    (\n",
    "    SELECT B.Book, COUNT(P.ProfileID) AS C\n",
    "    FROM Profiles AS P \n",
    "        INNER JOIN FavoriteBooks AS B ON P.ProfileID = B.ProfileId\n",
    "    WHERE PoliticalViews = 'Conservative'\n",
    "    GROUP BY B.Book\n",
    "    ) BC \n",
    "JOIN \n",
    "    (\n",
    "    SELECT B.Book, COUNT(P.ProfileID) AS L\n",
    "    FROM Profiles AS P \n",
    "        INNER JOIN FavoriteBooks AS B ON P.ProfileID = B.ProfileId\n",
    "    WHERE PoliticalViews = 'Liberal'\n",
    "    GROUP BY B.Book\n",
    "    ) BL ON BC.Book = BL.Book \n",
    "JOIN\n",
    "    (\n",
    "    SELECT COUNT(*) AS NC \n",
    "    FROM Profiles \n",
    "    WHERE PoliticalViews = 'Conservative'\n",
    "    ) C \n",
    "JOIN \n",
    "    (\n",
    "    SELECT COUNT(*) AS NL\n",
    "    FROM Profiles \n",
    "    WHERE PoliticalViews = 'Liberal'\n",
    "    ) L\n",
    "WHERE BC.C > 5 AND BL.L > 5\n",
    "ORDER BY lift_cons DESC\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}