[solution]PC-SQL-Workshop-Notebook.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "I-nQtshgBe6E"
   },
   "source": [
    "# Peer Consultant SQL Workshop SOLUTION"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "zs78hYJ3Be6H"
   },
   "source": [
    "Welcome to the hands-on portion of this workshop! In this notebook, you will walk through writing basic queries using the **movies** dataset.\n",
    "\n",
    "If you're not familiar with Jupyter Notebooks, press **shift + enter** to run cells. If you accidentally click on one of the text cells (Markdown cells), just run it and it will look normal again. When you re-open the notebook, you need to re-run all the cells (go to Cell tab at the top to do this).\n",
    "\n",
    "First, run the cells below to set up the notebook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "Mm-JJ6cMBe6K",
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "LIqRBQaPBe6T"
   },
   "outputs": [],
   "source": [
    "engine = create_engine('sqlite://', echo=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "c1aHZ_jkBe6Z"
   },
   "source": [
    "## Movies Dataset\n",
    "First we need to read in our Movies dataset .csv file into a pandas dataframe. This way, it is easier to create a sqlite engine to run SQL statements on our dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 381
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 388,
     "status": "error",
     "timestamp": 1586841669557,
     "user": {
      "displayName": "Vincent Lao",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AOh14GiUfkiIhJMGNgIbmCjDIfVNeOvp76970t4KrzXY8Q=s64",
      "userId": "08804274340742922532"
     },
     "user_tz": 420
    },
    "id": "F8v_ElEbBe6a",
    "outputId": "1b7d693a-6090-4403-e952-e24b63ba0fea"
   },
   "outputs": [],
   "source": [
    "movies = pd.read_csv('movie_metadata.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 166
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 197,
     "status": "error",
     "timestamp": 1586841669712,
     "user": {
      "displayName": "Vincent Lao",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AOh14GiUfkiIhJMGNgIbmCjDIfVNeOvp76970t4KrzXY8Q=s64",
      "userId": "08804274340742922532"
     },
     "user_tz": 420
    },
    "id": "N8cFWQ-2Be6i",
    "outputId": "88c22b7d-aa7f-4bd8-fefa-6163557ebb85",
    "scrolled": true
   },
   "outputs": [
    {
     "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>color</th>\n",
       "      <th>director_name</th>\n",
       "      <th>num_critic_for_reviews</th>\n",
       "      <th>duration</th>\n",
       "      <th>director_facebook_likes</th>\n",
       "      <th>actor_3_facebook_likes</th>\n",
       "      <th>actor_2_name</th>\n",
       "      <th>actor_1_facebook_likes</th>\n",
       "      <th>gross</th>\n",
       "      <th>genres</th>\n",
       "      <th>...</th>\n",
       "      <th>num_user_for_reviews</th>\n",
       "      <th>language</th>\n",
       "      <th>country</th>\n",
       "      <th>content_rating</th>\n",
       "      <th>budget</th>\n",
       "      <th>title_year</th>\n",
       "      <th>actor_2_facebook_likes</th>\n",
       "      <th>imdb_score</th>\n",
       "      <th>aspect_ratio</th>\n",
       "      <th>movie_facebook_likes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Color</td>\n",
       "      <td>James Cameron</td>\n",
       "      <td>723.0</td>\n",
       "      <td>178.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>855.0</td>\n",
       "      <td>Joel David Moore</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>760505847.0</td>\n",
       "      <td>Action|Adventure|Fantasy|Sci-Fi</td>\n",
       "      <td>...</td>\n",
       "      <td>3054.0</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>237000000.0</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>936.0</td>\n",
       "      <td>7.9</td>\n",
       "      <td>1.78</td>\n",
       "      <td>33000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Color</td>\n",
       "      <td>Gore Verbinski</td>\n",
       "      <td>302.0</td>\n",
       "      <td>169.0</td>\n",
       "      <td>563.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>Orlando Bloom</td>\n",
       "      <td>40000.0</td>\n",
       "      <td>309404152.0</td>\n",
       "      <td>Action|Adventure|Fantasy</td>\n",
       "      <td>...</td>\n",
       "      <td>1238.0</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>300000000.0</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>7.1</td>\n",
       "      <td>2.35</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Color</td>\n",
       "      <td>Sam Mendes</td>\n",
       "      <td>602.0</td>\n",
       "      <td>148.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>161.0</td>\n",
       "      <td>Rory Kinnear</td>\n",
       "      <td>11000.0</td>\n",
       "      <td>200074175.0</td>\n",
       "      <td>Action|Adventure|Thriller</td>\n",
       "      <td>...</td>\n",
       "      <td>994.0</td>\n",
       "      <td>English</td>\n",
       "      <td>UK</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>245000000.0</td>\n",
       "      <td>2015.0</td>\n",
       "      <td>393.0</td>\n",
       "      <td>6.8</td>\n",
       "      <td>2.35</td>\n",
       "      <td>85000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Color</td>\n",
       "      <td>Christopher Nolan</td>\n",
       "      <td>813.0</td>\n",
       "      <td>164.0</td>\n",
       "      <td>22000.0</td>\n",
       "      <td>23000.0</td>\n",
       "      <td>Christian Bale</td>\n",
       "      <td>27000.0</td>\n",
       "      <td>448130642.0</td>\n",
       "      <td>Action|Thriller</td>\n",
       "      <td>...</td>\n",
       "      <td>2701.0</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>250000000.0</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>23000.0</td>\n",
       "      <td>8.5</td>\n",
       "      <td>2.35</td>\n",
       "      <td>164000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Doug Walker</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>131.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Rob Walker</td>\n",
       "      <td>131.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Documentary</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.0</td>\n",
       "      <td>7.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   color      director_name  num_critic_for_reviews  duration  \\\n",
       "0  Color      James Cameron                   723.0     178.0   \n",
       "1  Color     Gore Verbinski                   302.0     169.0   \n",
       "2  Color         Sam Mendes                   602.0     148.0   \n",
       "3  Color  Christopher Nolan                   813.0     164.0   \n",
       "4    NaN        Doug Walker                     NaN       NaN   \n",
       "\n",
       "   director_facebook_likes  actor_3_facebook_likes      actor_2_name  \\\n",
       "0                      0.0                   855.0  Joel David Moore   \n",
       "1                    563.0                  1000.0     Orlando Bloom   \n",
       "2                      0.0                   161.0      Rory Kinnear   \n",
       "3                  22000.0                 23000.0    Christian Bale   \n",
       "4                    131.0                     NaN        Rob Walker   \n",
       "\n",
       "   actor_1_facebook_likes        gross                           genres  ...  \\\n",
       "0                  1000.0  760505847.0  Action|Adventure|Fantasy|Sci-Fi  ...   \n",
       "1                 40000.0  309404152.0         Action|Adventure|Fantasy  ...   \n",
       "2                 11000.0  200074175.0        Action|Adventure|Thriller  ...   \n",
       "3                 27000.0  448130642.0                  Action|Thriller  ...   \n",
       "4                   131.0          NaN                      Documentary  ...   \n",
       "\n",
       "  num_user_for_reviews language  country  content_rating       budget  \\\n",
       "0               3054.0  English      USA           PG-13  237000000.0   \n",
       "1               1238.0  English      USA           PG-13  300000000.0   \n",
       "2                994.0  English       UK           PG-13  245000000.0   \n",
       "3               2701.0  English      USA           PG-13  250000000.0   \n",
       "4                  NaN      NaN      NaN             NaN          NaN   \n",
       "\n",
       "   title_year actor_2_facebook_likes imdb_score  aspect_ratio  \\\n",
       "0      2009.0                  936.0        7.9          1.78   \n",
       "1      2007.0                 5000.0        7.1          2.35   \n",
       "2      2015.0                  393.0        6.8          2.35   \n",
       "3      2012.0                23000.0        8.5          2.35   \n",
       "4         NaN                   12.0        7.1           NaN   \n",
       "\n",
       "  movie_facebook_likes  \n",
       "0                33000  \n",
       "1                    0  \n",
       "2                85000  \n",
       "3               164000  \n",
       "4                    0  \n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "cBgoEpMgBe6w"
   },
   "source": [
    "To find all the column names, we can run `movies.columns`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "ApehbXoqBe6y",
    "outputId": "516fb238-438b-4ced-8e93-c9cb3d67f602"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['color', 'director_name', 'num_critic_for_reviews', 'duration',\n",
       "       'director_facebook_likes', 'actor_3_facebook_likes', 'actor_2_name',\n",
       "       'actor_1_facebook_likes', 'gross', 'genres', 'actor_1_name',\n",
       "       'movie_title', 'num_voted_users', 'cast_total_facebook_likes',\n",
       "       'actor_3_name', 'facenumber_in_poster', 'plot_keywords',\n",
       "       'movie_imdb_link', 'num_user_for_reviews', 'language', 'country',\n",
       "       'content_rating', 'budget', 'title_year', 'actor_2_facebook_likes',\n",
       "       'imdb_score', 'aspect_ratio', 'movie_facebook_likes'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "cezWyYidBe66"
   },
   "source": [
    "The `to_sql` method writes records stored in a dataframe to a SQL database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "CGCVuPQ1Be67"
   },
   "outputs": [],
   "source": [
    "movies.to_sql('movies', con=engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "oDyysQwoBe7H"
   },
   "source": [
    "## 1. Examples\n",
    "What do you think these example SQL statements will output? Note that when we run SQL queries in this notebook, they must be written in the format `engine.execute(\"...\").fetchall()` in order to run correctly. Additionally, the result will be a list.\n",
    "\n",
    "**Question 1.1** This query uses `DISTINCT` and `WHERE`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "sG6kU-nJBe7I",
    "outputId": "ac807de9-c423-450f-e6ed-b688256bea83",
    "scrolled": true
   },
   "outputs": [
    {
     "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>actor_2_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Heath Ledger</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Anne Hathaway</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Tom Hardy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Orlando Bloom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Billy Boyd</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    actor_2_name\n",
       "0   Heath Ledger\n",
       "1  Anne Hathaway\n",
       "2      Tom Hardy\n",
       "3  Orlando Bloom\n",
       "4     Billy Boyd"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "        SELECT distinct(actor_2_name)\n",
    "        FROM movies\n",
    "        WHERE imdb_score > 8.5\n",
    "        \"\"\"\n",
    "\n",
    "pd.read_sql(query, engine).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "_15ums1kBe7N"
   },
   "source": [
    "**Question 1.2** This query uses `LIKE`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "C8kOfmxxBe7O",
    "outputId": "6d2510a9-1e5a-4fe3-e935-837de145e680"
   },
   "outputs": [
    {
     "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_title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Spectre</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>The Dark Knight Rises</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Battleship</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Jurassic World</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Skyfall</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              movie_title\n",
       "0                Spectre \n",
       "1  The Dark Knight Rises \n",
       "2             Battleship \n",
       "3         Jurassic World \n",
       "4                Skyfall "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "        SELECT movie_title \n",
    "        FROM movies \n",
    "        WHERE genres LIKE '%Thriller%'\n",
    "        \"\"\"\n",
    "pd.read_sql(query, engine).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "pt0UGsqHBe7U"
   },
   "source": [
    "**Question 1.3** This query uses `GROUP BY`, `ORDER BY`, and `LIMIT`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "eNJKLVL0Be7V",
    "outputId": "1ec96f18-ba4c-4a20-cbed-14759793967e"
   },
   "outputs": [
    {
     "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>country</th>\n",
       "      <th>avg(duration)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Iceland</td>\n",
       "      <td>230.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Libya</td>\n",
       "      <td>156.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>West Germany</td>\n",
       "      <td>155.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Thailand</td>\n",
       "      <td>142.800000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Slovakia</td>\n",
       "      <td>141.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        country  avg(duration)\n",
       "0       Iceland     230.333333\n",
       "1         Libya     156.000000\n",
       "2  West Germany     155.666667\n",
       "3      Thailand     142.800000\n",
       "4      Slovakia     141.000000"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "        SELECT country, avg(duration) \n",
    "        FROM movies \n",
    "        GROUP BY country \n",
    "        ORDER BY avg(duration) \n",
    "        DESC LIMIT 10\n",
    "        \"\"\"\n",
    "pd.read_sql(query, engine).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Rp1dbkWwBe7b"
   },
   "source": [
    "## 2. Your turn!\n",
    "**Question 2.1** Show the titles of all the movies that have a content rating of R.\n",
    "\n",
    "*Note:* Remember that you can call `movies.columns` to refer back to all the column names in the dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "AOMTSasgBe7c",
    "outputId": "1f831236-510a-4702-8c19-6a52ef1f40d8",
    "scrolled": true
   },
   "outputs": [
    {
     "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_title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>The Lovers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Terminator 3: Rise of the Machines</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Alexander</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>The Matrix Revolutions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>The Matrix Reloaded</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           movie_title\n",
       "0                          The Lovers \n",
       "1  Terminator 3: Rise of the Machines \n",
       "2                           Alexander \n",
       "3              The Matrix Revolutions \n",
       "4                 The Matrix Reloaded "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# engine.execute(\"SELECT ... FROM ... WHERE ...\").fetchall()\n",
    "query = \"\"\"\n",
    "        SELECT movie_title \n",
    "        FROM movies \n",
    "        WHERE content_rating = 'R'\n",
    "        \"\"\"\n",
    "pd.read_sql(query, engine).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "e6FzmwAjBe7k"
   },
   "source": [
    "**Question 2.2** Show the titles and the duration of all the movies that have Sci-Fi as a genre. \n",
    "\n",
    "*Hint:* Note that a movie can have multiple genres, so you might want to check what the `genres` column looks like first."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "KzObP4-nBe7l",
    "outputId": "d9fcba83-c34b-4bd1-9b0a-4690276d7f35"
   },
   "outputs": [
    {
     "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_title</th>\n",
       "      <th>duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Avatar</td>\n",
       "      <td>178.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>John Carter</td>\n",
       "      <td>132.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Avengers: Age of Ultron</td>\n",
       "      <td>141.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Batman v Superman: Dawn of Justice</td>\n",
       "      <td>183.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Superman Returns</td>\n",
       "      <td>169.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           movie_title  duration\n",
       "0                              Avatar      178.0\n",
       "1                         John Carter      132.0\n",
       "2             Avengers: Age of Ultron      141.0\n",
       "3  Batman v Superman: Dawn of Justice      183.0\n",
       "4                    Superman Returns      169.0"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# engine.execute(\"SELECT ... FROM movies WHERE ... LIKE ...\").fetchall()\n",
    "query = \"\"\"\n",
    "        SELECT movie_title, duration \n",
    "        FROM movies \n",
    "        WHERE genres LIKE '%Sci-Fi%'\n",
    "  \"\"\"\n",
    "pd.read_sql(query, engine).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "GhwdlhZvBe7t"
   },
   "source": [
    "**Question 2.3** Show the directors that earned the top 10 largest total gross sum."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "r-XZVXBFBe7u",
    "outputId": "330facf8-c607-481c-edb6-087f6e3f3de1"
   },
   "outputs": [
    {
     "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_name</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Steven Spielberg</td>\n",
       "      <td>4.114233e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Peter Jackson</td>\n",
       "      <td>2.592969e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Michael Bay</td>\n",
       "      <td>2.231243e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Tim Burton</td>\n",
       "      <td>2.071275e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Sam Raimi</td>\n",
       "      <td>2.049549e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      director_name           sum\n",
       "0  Steven Spielberg  4.114233e+09\n",
       "1     Peter Jackson  2.592969e+09\n",
       "2       Michael Bay  2.231243e+09\n",
       "3        Tim Burton  2.071275e+09\n",
       "4         Sam Raimi  2.049549e+09"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# engine.execute(\"SELECT director_name, ... as sum FROM movies GROUP BY ... ORDER BY ... LIMIT ...\").fetchall()\n",
    "query = \"\"\"\n",
    "        SELECT director_name, SUM(gross) as sum \n",
    "        FROM movies \n",
    "        GROUP BY director_name \n",
    "        ORDER BY sum DESC LIMIT 10\n",
    "        \"\"\"\n",
    "pd.read_sql(query, engine).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "ENMxw40oBe71"
   },
   "source": [
    "**Question 2.4** Show the 10 countries that have the lowest average IMDB scores."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "BQb25vixBe72",
    "outputId": "ae4483dd-ed77-4ce8-917b-4d88e8d7bf9c"
   },
   "outputs": [
    {
     "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>country</th>\n",
       "      <th>avg_imdb</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Bahamas</td>\n",
       "      <td>4.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>New Line</td>\n",
       "      <td>4.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Aruba</td>\n",
       "      <td>4.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Peru</td>\n",
       "      <td>5.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Belgium</td>\n",
       "      <td>5.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    country  avg_imdb\n",
       "0   Bahamas       4.4\n",
       "1  New Line       4.4\n",
       "2     Aruba       4.8\n",
       "3      Peru       5.4\n",
       "4   Belgium       5.6"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# engine.execute(\"SELECT ... FROM movies GROUP BY ... ORDER BY ... LIMIT ...\").fetchall()\n",
    "query = \"\"\"\n",
    "        SELECT country, AVG(imdb_score) as avg_imdb \n",
    "        FROM movies \n",
    "        GROUP BY country \n",
    "        ORDER BY avg_imdb ASC \n",
    "        LIMIT 10\n",
    "        \"\"\"\n",
    "pd.read_sql(query, engine).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "xzHRGGn_Be77"
   },
   "source": [
    "## 3. Harder SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "F-iZWOLCBe79",
    "outputId": "773d79c9-c579-4460-a880-06b295e2d6f7",
    "scrolled": false
   },
   "outputs": [
    {
     "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>color</th>\n",
       "      <th>director_name</th>\n",
       "      <th>num_critic_for_reviews</th>\n",
       "      <th>duration</th>\n",
       "      <th>director_facebook_likes</th>\n",
       "      <th>actor_3_facebook_likes</th>\n",
       "      <th>actor_2_name</th>\n",
       "      <th>actor_1_facebook_likes</th>\n",
       "      <th>gross</th>\n",
       "      <th>genres</th>\n",
       "      <th>...</th>\n",
       "      <th>num_user_for_reviews</th>\n",
       "      <th>language</th>\n",
       "      <th>country</th>\n",
       "      <th>content_rating</th>\n",
       "      <th>budget</th>\n",
       "      <th>title_year</th>\n",
       "      <th>actor_2_facebook_likes</th>\n",
       "      <th>imdb_score</th>\n",
       "      <th>aspect_ratio</th>\n",
       "      <th>movie_facebook_likes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Color</td>\n",
       "      <td>James Cameron</td>\n",
       "      <td>723.0</td>\n",
       "      <td>178.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>855.0</td>\n",
       "      <td>Joel David Moore</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>760505847.0</td>\n",
       "      <td>Action|Adventure|Fantasy|Sci-Fi</td>\n",
       "      <td>...</td>\n",
       "      <td>3054.0</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>237000000.0</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>936.0</td>\n",
       "      <td>7.9</td>\n",
       "      <td>1.78</td>\n",
       "      <td>33000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Color</td>\n",
       "      <td>Gore Verbinski</td>\n",
       "      <td>302.0</td>\n",
       "      <td>169.0</td>\n",
       "      <td>563.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>Orlando Bloom</td>\n",
       "      <td>40000.0</td>\n",
       "      <td>309404152.0</td>\n",
       "      <td>Action|Adventure|Fantasy</td>\n",
       "      <td>...</td>\n",
       "      <td>1238.0</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>300000000.0</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>7.1</td>\n",
       "      <td>2.35</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Color</td>\n",
       "      <td>Sam Mendes</td>\n",
       "      <td>602.0</td>\n",
       "      <td>148.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>161.0</td>\n",
       "      <td>Rory Kinnear</td>\n",
       "      <td>11000.0</td>\n",
       "      <td>200074175.0</td>\n",
       "      <td>Action|Adventure|Thriller</td>\n",
       "      <td>...</td>\n",
       "      <td>994.0</td>\n",
       "      <td>English</td>\n",
       "      <td>UK</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>245000000.0</td>\n",
       "      <td>2015.0</td>\n",
       "      <td>393.0</td>\n",
       "      <td>6.8</td>\n",
       "      <td>2.35</td>\n",
       "      <td>85000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Color</td>\n",
       "      <td>Christopher Nolan</td>\n",
       "      <td>813.0</td>\n",
       "      <td>164.0</td>\n",
       "      <td>22000.0</td>\n",
       "      <td>23000.0</td>\n",
       "      <td>Christian Bale</td>\n",
       "      <td>27000.0</td>\n",
       "      <td>448130642.0</td>\n",
       "      <td>Action|Thriller</td>\n",
       "      <td>...</td>\n",
       "      <td>2701.0</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>250000000.0</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>23000.0</td>\n",
       "      <td>8.5</td>\n",
       "      <td>2.35</td>\n",
       "      <td>164000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Doug Walker</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>131.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Rob Walker</td>\n",
       "      <td>131.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Documentary</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.0</td>\n",
       "      <td>7.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   color      director_name  num_critic_for_reviews  duration  \\\n",
       "0  Color      James Cameron                   723.0     178.0   \n",
       "1  Color     Gore Verbinski                   302.0     169.0   \n",
       "2  Color         Sam Mendes                   602.0     148.0   \n",
       "3  Color  Christopher Nolan                   813.0     164.0   \n",
       "4    NaN        Doug Walker                     NaN       NaN   \n",
       "\n",
       "   director_facebook_likes  actor_3_facebook_likes      actor_2_name  \\\n",
       "0                      0.0                   855.0  Joel David Moore   \n",
       "1                    563.0                  1000.0     Orlando Bloom   \n",
       "2                      0.0                   161.0      Rory Kinnear   \n",
       "3                  22000.0                 23000.0    Christian Bale   \n",
       "4                    131.0                     NaN        Rob Walker   \n",
       "\n",
       "   actor_1_facebook_likes        gross                           genres  ...  \\\n",
       "0                  1000.0  760505847.0  Action|Adventure|Fantasy|Sci-Fi  ...   \n",
       "1                 40000.0  309404152.0         Action|Adventure|Fantasy  ...   \n",
       "2                 11000.0  200074175.0        Action|Adventure|Thriller  ...   \n",
       "3                 27000.0  448130642.0                  Action|Thriller  ...   \n",
       "4                   131.0          NaN                      Documentary  ...   \n",
       "\n",
       "  num_user_for_reviews language  country  content_rating       budget  \\\n",
       "0               3054.0  English      USA           PG-13  237000000.0   \n",
       "1               1238.0  English      USA           PG-13  300000000.0   \n",
       "2                994.0  English       UK           PG-13  245000000.0   \n",
       "3               2701.0  English      USA           PG-13  250000000.0   \n",
       "4                  NaN      NaN      NaN             NaN          NaN   \n",
       "\n",
       "   title_year actor_2_facebook_likes imdb_score  aspect_ratio  \\\n",
       "0      2009.0                  936.0        7.9          1.78   \n",
       "1      2007.0                 5000.0        7.1          2.35   \n",
       "2      2015.0                  393.0        6.8          2.35   \n",
       "3      2012.0                23000.0        8.5          2.35   \n",
       "4         NaN                   12.0        7.1           NaN   \n",
       "\n",
       "  movie_facebook_likes  \n",
       "0                33000  \n",
       "1                    0  \n",
       "2                85000  \n",
       "3               164000  \n",
       "4                    0  \n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# run to look at as a reference\n",
    "movies.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "eAikgw2MBe78"
   },
   "source": [
    "---\n",
    "**Question 3.1** Of the movies that have an `imdb_score` higher than 8, find the number of `English` movies `made in the U.S`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "o3iT6lx5Be8E",
    "outputId": "6f2c06c4-c5bc-4976-e4db-1243f292775d"
   },
   "outputs": [
    {
     "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>COUNT(movie_title)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>169</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   COUNT(movie_title)\n",
       "0                 169"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# engine.execute(\"\"\" SELECT ... FROM ... WHERE ...\"\"\").fetchall()\n",
    "\n",
    "# Using a subquery:\n",
    "# engine.execute(\"\"\" SELECT COUNT(*) FROM (SELECT * FROM movies WHERE imdb_score > 8) WHERE language = 'English' AND country='USA'\n",
    "#                 \"\"\").fetchall()\n",
    "\n",
    "# Without a subquery:\n",
    "query = \"\"\"\n",
    "        SELECT COUNT(movie_title) \n",
    "        FROM movies \n",
    "        WHERE imdb_score > 8 AND language = 'English' AND (country = 'USA' OR country = 'Canada')\n",
    "        \"\"\"\n",
    "pd.read_sql(query, engine).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "**3.2** Find all the directors with an average `imdb_score` of more than 8 across all their movies, with the best director on top."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "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_name</th>\n",
       "      <th>AVG(imdb_score)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>John Blanchard</td>\n",
       "      <td>9.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Sadyk Sher-Niyaz</td>\n",
       "      <td>8.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Mitchell Altieri</td>\n",
       "      <td>8.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Cary Bell</td>\n",
       "      <td>8.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Mike Mayhall</td>\n",
       "      <td>8.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      director_name  AVG(imdb_score)\n",
       "0    John Blanchard              9.5\n",
       "1  Sadyk Sher-Niyaz              8.7\n",
       "2  Mitchell Altieri              8.7\n",
       "3         Cary Bell              8.7\n",
       "4      Mike Mayhall              8.6"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "        SELECT director_name, AVG(imdb_score) \n",
    "        FROM movies \n",
    "        GROUP BY director_name\n",
    "        HAVING AVG(imdb_score) > 8\n",
    "        ORDER BY AVG(imdb_score) DESC\n",
    "        \"\"\"\n",
    "pd.read_sql(query, engine).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "**3.3** Find the `content_rating`, the `COUNT`, and the `AVG` movie Facebook likes of the number of movies of that type `ORDER BY` most popular on average based on Facebook likes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "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>content_rating</th>\n",
       "      <th>COUNT(movie_title)</th>\n",
       "      <th>AVG(movie_facebook_likes)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>TV-MA</td>\n",
       "      <td>20</td>\n",
       "      <td>13095.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>1461</td>\n",
       "      <td>11051.508556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>TV-14</td>\n",
       "      <td>30</td>\n",
       "      <td>9462.733333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>R</td>\n",
       "      <td>2118</td>\n",
       "      <td>7476.420208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>PG</td>\n",
       "      <td>701</td>\n",
       "      <td>5993.680456</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  content_rating  COUNT(movie_title)  AVG(movie_facebook_likes)\n",
       "0          TV-MA                  20               13095.000000\n",
       "1          PG-13                1461               11051.508556\n",
       "2          TV-14                  30                9462.733333\n",
       "3              R                2118                7476.420208\n",
       "4             PG                 701                5993.680456"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "        SELECT content_rating, COUNT(movie_title), AVG(movie_facebook_likes)\n",
    "        FROM movies\n",
    "        GROUP BY content_rating\n",
    "        ORDER BY AVG(movie_facebook_likes) DESC\n",
    "        \"\"\"\n",
    "pd.read_sql(query, engine).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "**3.4** Find two different ways to get the name and length of the longest movie in the dataset. The length of the moving must not be missing (not none).\n",
    "\n",
    "*Hint*: One potential solution involves using [GROUP_CONCAT](https://www.sqlitetutorial.net/sqlite-group_concat/)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "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_title</th>\n",
       "      <th>duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Avatar</td>\n",
       "      <td>178.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Avatar</td>\n",
       "      <td>178.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Avatar</td>\n",
       "      <td>178.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Avatar</td>\n",
       "      <td>178.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Avatar</td>\n",
       "      <td>178.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  movie_title  duration\n",
       "0     Avatar      178.0\n",
       "1     Avatar      178.0\n",
       "2     Avatar      178.0\n",
       "3     Avatar      178.0\n",
       "4     Avatar      178.0"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# a few possible solutions:\n",
    "\n",
    "query = \"\"\"\n",
    "        SELECT movie_title, duration\n",
    "        FROM movies\n",
    "        WHERE duration >= 0\n",
    "        ORDER BY duration\n",
    "        LIMIT 1\n",
    "        \"\"\"\n",
    "\n",
    "# query = \"\"\"\n",
    "#         SELECT GROUP_CONCAT(movie_title, ', ') AS movie_title, duration\n",
    "#         FROM movies\n",
    "#         GROUP BY duration\n",
    "#         HAVING duration = MIN(duration)\n",
    "#         \"\"\"\n",
    "\n",
    "# advanced; uses a subquery!\n",
    "# query = \"\"\"\n",
    "#         SELECT movie_title, duration\n",
    "#         FROM movies\n",
    "#         WHERE duration = (SELECT MIN(duration)\n",
    "#                           FROM movies)\n",
    "#         \"\"\"\n",
    "\n",
    "pd.read_sql(query, engine).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Thoughtful Question: Why might we have an issue with using ORDER BY and LIMIT 1 to find the maximum or minimum?  \n",
    "\n",
    "Answer: In the case that we have more than one maximum or minimum, LIMIT 1 would prevent us from seeing it. As you write more SQL, you'll see these edge cases more clearly, so be very clear what the question is asking, and what your query is returning!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**CHALLENGE:** Open-ended questions to try to find the answer to!\n",
    "\n",
    "1. How can we rate the movies in terms of popularity? One way to approach this is to assume that IMDB's score is not a holistic view of a given movie, and that we can use Facebook likes as a good measurement of popularity. Try weighting each column, ex:  \n",
    "\n",
    "| Column         | Weight |\n",
    "|----------------|--------|\n",
    "| IMDB Score     | 40%    |\n",
    "| Actor 1 Likes  | 10%    |\n",
    "| Actor 2 Likes  | 10%    |\n",
    "| Actor 3 Likes  | 10%    |\n",
    "| Director Likes | 30%    |\n",
    "\n",
    "2. Which country is the most popular in terms of movies they produced? Try using the table you created from question 1's query to answer this question. Set a variable equal to the output after calling `pd.read_sql()`, and then use that variable name in your FROM statement."
   ]
  }
 ],
 "metadata": {
  "colab": {
   "name": "updated_SQL_Workshop_Solutions.ipynb",
   "provenance": []
  },
  "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}