day-two.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "lightweight-fruit",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# SQL 基礎\n",
    "\n",
    "> 第二天\n",
    "\n",
    "[數聚點](https://www.datainpoint.com/) | 郭耀仁 <yaojenkuo@datainpoint.com>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "matched-spyware",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "%LOAD sqlite3 db=imdb.db timeout=2 shared_cache=true"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "supposed-spread",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 函數"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "threaded-police",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Function，中文翻譯為函數或者函式，在資料分析和程式語言中都扮演舉足輕重的角色\n",
    "\n",
    "函數是預先被定義好的運算處理邏輯，透過它的作用，能夠將「輸入」對應為「輸出」，進而完成計算數值與操作文字等任務。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "right-confidence",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 函數的運作有五個組成：\n",
    "\n",
    "1. 函數的名稱。\n",
    "2. 輸入。\n",
    "3. 參數。\n",
    "4. 運算處理邏輯。\n",
    "5. 輸出。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "configured-international",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 以買珍珠奶茶為例\n",
    "\n",
    "![](images/pearl_milk_tea.jpg)\n",
    "\n",
    "來源：[Google Search](https://www.google.com/search?q=%E7%8F%8D%E7%8F%A0%E5%A5%B6%E8%8C%B6&sxsrf=ALeKk03IFHCE14mwz0z5yVStCaGHvED-8g:1617364912723&source=lnms&tbm=isch&sa=X&ved=2ahUKEwjZ9eDowd_vAhVgxosBHdTsC_MQ_AUoAXoECAEQAw&biw=1037&bih=1448&dpr=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "lovely-documentation",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 函數可依照功能分為兩大類：\n",
    "\n",
    "- 通用函數（Universal functions）\n",
    "    - 轉換資料類型。\n",
    "    - 計算數值。\n",
    "    - 操作文字。\n",
    "    - 操作日期時間。\n",
    "- 聚合函數（Aggregate functions）"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "complete-breast",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 通用函數與聚合函數的不同在於其所作用的維度\n",
    "\n",
    "- 通用函數作用在「水平」方向。\n",
    "- 聚合函數作用在「垂直」方向。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "twelve-affair",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 作用在「水平」方向的通用函數\n",
    "\n",
    "效果類似「衍生計算欄位」，差別在於一個是以函數輸出衍生計算欄位，一個則是以運算符生成衍生計算欄位。\n",
    "\n",
    "![mutate](images/mutate.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "informal-marriage",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 作用在「垂直」方向的聚合函數\n",
    "\n",
    "![aggregate](images/aggregate.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "announced-wells",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 通用函數的特徵：每個觀測值對應一個輸出"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "written-helicopter",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>rating</th>\n",
       "<th>rounded_rating</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>9.3</td>\n",
       "<td>9.0</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>9.2</td>\n",
       "<td>9.0</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>9</td>\n",
       "<td>9.0</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>9</td>\n",
       "<td>9.0</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>9</td>\n",
       "<td>9.0</td>\n",
       "</tr>\n",
       "</table>\n",
       "5 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+--------+----------------+\n",
       "| rating | rounded_rating |\n",
       "+--------+----------------+\n",
       "| 9.3    | 9.0            |\n",
       "+--------+----------------+\n",
       "| 9.2    | 9.0            |\n",
       "+--------+----------------+\n",
       "| 9      | 9.0            |\n",
       "+--------+----------------+\n",
       "| 9      | 9.0            |\n",
       "+--------+----------------+\n",
       "| 9      | 9.0            |\n",
       "+--------+----------------+\n",
       "5 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT rating,\n",
    "       ROUND(rating) AS rounded_rating\n",
    "  FROM movies\n",
    " LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "naughty-garlic",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 聚合函數的特徵：整欄變數對應一個輸出"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "superb-leone",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>avg_rating</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>8.30519999999997</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+------------------+\n",
       "| avg_rating       |\n",
       "+------------------+\n",
       "| 8.30519999999997 |\n",
       "+------------------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT AVG(rating) AS avg_rating\n",
    "  FROM movies;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2d24e83f",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 常見的通用函數又可以細分為四種類型\n",
    "\n",
    "- **轉換資料類型**。\n",
    "- 計算數值。\n",
    "- 操作文字。\n",
    "- 操作日期時間。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "friendly-oliver",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 使用 `CAST` 函數可以將查詢結果的資料類型轉換為指定資料類型\n",
    "\n",
    "```sql\n",
    "CAST(data AS data_type)\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "registered-michigan",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>release_year</th>\n",
       "<th>typeof_release_year</th>\n",
       "<th>cast_release_year</th>\n",
       "<th>float_release_year</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1994</td>\n",
       "<td>integer</td>\n",
       "<td>1994.0</td>\n",
       "<td>real</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+--------------+---------------------+-------------------+--------------------+\n",
       "| release_year | typeof_release_year | cast_release_year | float_release_year |\n",
       "+--------------+---------------------+-------------------+--------------------+\n",
       "| 1994         | integer             | 1994.0            | real               |\n",
       "+--------------+---------------------+-------------------+--------------------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT release_year,\n",
    "       TYPEOF(release_year) AS typeof_release_year,\n",
    "       CAST(release_year AS float) AS cast_release_year,\n",
    "       TYPEOF(CAST(release_year AS float)) AS float_release_year\n",
    "  FROM movies\n",
    " LIMIT 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "collect-juvenile",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 使用 `COALESCE` 函數可以將空值（或稱遺漏值）轉換為指定常數\n",
    "\n",
    "```sql\n",
    "COALESCE(NULL, replacement)\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "brazilian-blake",
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>a_null</th>\n",
       "<th>typeof_null</th>\n",
       "<th>text_null</th>\n",
       "<th>typeof_text_null</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>NULL</td>\n",
       "<td>null</td>\n",
       "<td>遺漏值</td>\n",
       "<td>text</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+--------+-------------+-----------+------------------+\n",
       "| a_null | typeof_null | text_null | typeof_text_null |\n",
       "+--------+-------------+-----------+------------------+\n",
       "| NULL   | null        | 遺漏值 | text             |\n",
       "+--------+-------------+-----------+------------------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT NULL AS a_null,\n",
    "       TYPEOF(NULL) AS typeof_null,\n",
    "       COALESCE(NULL, '遺漏值') AS text_null,\n",
    "       TYPEOF(COALESCE(NULL, '遺漏值')) AS typeof_text_null;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a99b48f5",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 常見的通用函數又可以細分為四種類型（續）\n",
    "\n",
    "- 轉換資料類型。\n",
    "- **計算數值**。\n",
    "- 操作文字。\n",
    "- 操作日期時間。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "threaded-trading",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 使用 `ROUND` 函數可以調整查詢結果的小數點位數\n",
    "\n",
    "```sql\n",
    "ROUND(REAL, n_digits)\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "opposite-westminster",
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>average_rating</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>8.31</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+----------------+\n",
       "| average_rating |\n",
       "+----------------+\n",
       "| 8.31           |\n",
       "+----------------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT ROUND(AVG(rating), 2) AS average_rating\n",
    "  FROM movies;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b0304de6",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 常見的通用函數又可以細分為四種類型（續）\n",
    "\n",
    "- 轉換資料類型。\n",
    "- 計算數值。\n",
    "- **操作文字**。\n",
    "- 操作日期時間。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "sporting-algebra",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 使用 `LENGTH` 函數可以計算文字中有幾個字元，包含空格、標點符號\n",
    "\n",
    "```sql\n",
    "LENGTH(TEXT)\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "adverse-facing",
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>title</th>\n",
       "<th>length_of_movie_title</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>24</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Godfather</td>\n",
       "<td>13</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Godfather: Part II</td>\n",
       "<td>22</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Dark Knight</td>\n",
       "<td>15</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>12 Angry Men</td>\n",
       "<td>12</td>\n",
       "</tr>\n",
       "</table>\n",
       "5 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+--------------------------+-----------------------+\n",
       "| title                    | length_of_movie_title |\n",
       "+--------------------------+-----------------------+\n",
       "| The Shawshank Redemption | 24                    |\n",
       "+--------------------------+-----------------------+\n",
       "| The Godfather            | 13                    |\n",
       "+--------------------------+-----------------------+\n",
       "| The Godfather: Part II   | 22                    |\n",
       "+--------------------------+-----------------------+\n",
       "| The Dark Knight          | 15                    |\n",
       "+--------------------------+-----------------------+\n",
       "| 12 Angry Men             | 12                    |\n",
       "+--------------------------+-----------------------+\n",
       "5 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT title,\n",
    "       LENGTH(title) AS length_of_movie_title\n",
    "  FROM movies\n",
    " LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "unnecessary-denmark",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 使用 `INSTR` 函數可回傳文字中指定特徵的開始位置\n",
    "\n",
    "```sql\n",
    "INSTR(TEXT, pattern)\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "christian-polymer",
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>dark_knight_position</th>\n",
       "<th>batman_position</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>0</td>\n",
       "<td>0</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>0</td>\n",
       "<td>0</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>0</td>\n",
       "<td>0</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>5</td>\n",
       "<td>0</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>0</td>\n",
       "<td>0</td>\n",
       "</tr>\n",
       "</table>\n",
       "5 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+----------------------+-----------------+\n",
       "| dark_knight_position | batman_position |\n",
       "+----------------------+-----------------+\n",
       "| 0                    | 0               |\n",
       "+----------------------+-----------------+\n",
       "| 0                    | 0               |\n",
       "+----------------------+-----------------+\n",
       "| 0                    | 0               |\n",
       "+----------------------+-----------------+\n",
       "| 5                    | 0               |\n",
       "+----------------------+-----------------+\n",
       "| 0                    | 0               |\n",
       "+----------------------+-----------------+\n",
       "5 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT INSTR(title, 'Dark Knight') AS dark_knight_position,\n",
    "       INSTR(title, 'Batman') AS batman_position\n",
    "  FROM movies\n",
    " LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "8fb6c5f7",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>id</th>\n",
       "<th>title</th>\n",
       "<th>release_year</th>\n",
       "<th>rating</th>\n",
       "<th>director</th>\n",
       "<th>runtime</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>4</td>\n",
       "<td>The Dark Knight</td>\n",
       "<td>2008</td>\n",
       "<td>9</td>\n",
       "<td>Christopher Nolan</td>\n",
       "<td>152</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>71</td>\n",
       "<td>The Dark Knight Rises</td>\n",
       "<td>2012</td>\n",
       "<td>8.4</td>\n",
       "<td>Christopher Nolan</td>\n",
       "<td>164</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>127</td>\n",
       "<td>Batman Begins</td>\n",
       "<td>2005</td>\n",
       "<td>8.2</td>\n",
       "<td>Christopher Nolan</td>\n",
       "<td>140</td>\n",
       "</tr>\n",
       "</table>\n",
       "3 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+-----+-----------------------+--------------+--------+-------------------+---------+\n",
       "| id  | title                 | release_year | rating | director          | runtime |\n",
       "+-----+-----------------------+--------------+--------+-------------------+---------+\n",
       "| 4   | The Dark Knight       | 2008         | 9      | Christopher Nolan | 152     |\n",
       "+-----+-----------------------+--------------+--------+-------------------+---------+\n",
       "| 71  | The Dark Knight Rises | 2012         | 8.4    | Christopher Nolan | 164     |\n",
       "+-----+-----------------------+--------------+--------+-------------------+---------+\n",
       "| 127 | Batman Begins         | 2005         | 8.2    | Christopher Nolan | 140     |\n",
       "+-----+-----------------------+--------------+--------+-------------------+---------+\n",
       "3 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT *\n",
    "  FROM movies\n",
    " WHERE INSTR(title, 'Dark Knight') > 0 OR\n",
    "       INSTR(title, 'Batman') > 0;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "hawaiian-facial",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 使用 `LOWER` 與 `UPPER` 函數可以調整英文的大小寫\n",
    "\n",
    "```sql\n",
    "LOWER(TEXT)\n",
    "UPPER(TEXT)\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "enhanced-quest",
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>upper_title</th>\n",
       "<th>lower_title</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>THE SHAWSHANK REDEMPTION</td>\n",
       "<td>the shawshank redemption</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>THE GODFATHER</td>\n",
       "<td>the godfather</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>THE GODFATHER: PART II</td>\n",
       "<td>the godfather: part ii</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>THE DARK KNIGHT</td>\n",
       "<td>the dark knight</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>12 ANGRY MEN</td>\n",
       "<td>12 angry men</td>\n",
       "</tr>\n",
       "</table>\n",
       "5 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+--------------------------+--------------------------+\n",
       "| upper_title              | lower_title              |\n",
       "+--------------------------+--------------------------+\n",
       "| THE SHAWSHANK REDEMPTION | the shawshank redemption |\n",
       "+--------------------------+--------------------------+\n",
       "| THE GODFATHER            | the godfather            |\n",
       "+--------------------------+--------------------------+\n",
       "| THE GODFATHER: PART II   | the godfather: part ii   |\n",
       "+--------------------------+--------------------------+\n",
       "| THE DARK KNIGHT          | the dark knight          |\n",
       "+--------------------------+--------------------------+\n",
       "| 12 ANGRY MEN             | 12 angry men             |\n",
       "+--------------------------+--------------------------+\n",
       "5 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT UPPER(title) AS upper_title,\n",
    "       LOWER(title) AS lower_title\n",
    "  FROM movies\n",
    " LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1714e5ce",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 常見的通用函數又可以細分為四種類型（續）\n",
    "\n",
    "- 轉換資料類型。\n",
    "- 計算數值。\n",
    "- 操作文字。\n",
    "- **操作日期時間**。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "biological-associate",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 標準的日期、時間與日期時間格式：ISO8601\n",
    "\n",
    "- 日期 `YYYY-MM-DD`\n",
    "- 時間 `HH:MM:SS`\n",
    "- 日期時間 `YYYY-MM-DD HH:MM:SS`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "norwegian-scenario",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 使用 `DATE()` 函數顯示系統日期"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "76137bbb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>date_of_today</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2022-12-15</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+---------------+\n",
       "| date_of_today |\n",
       "+---------------+\n",
       "| 2022-12-15    |\n",
       "+---------------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT DATE('now') AS date_of_today;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "248fa443",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 使用 `TIME()` 函數顯示系統時間"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "628bf38d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>time_of_now</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>14:22:22</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+-------------+\n",
       "| time_of_now |\n",
       "+-------------+\n",
       "| 14:22:22    |\n",
       "+-------------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT TIME('now', 'localtime') AS time_of_now;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "328b522a",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 使用 `DATETIME()` 函數顯示系統日期時間"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "0dc463e8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>date_time_of_now</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2022-12-15 14:22:22</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+---------------------+\n",
       "| date_time_of_now    |\n",
       "+---------------------+\n",
       "| 2022-12-15 14:22:22 |\n",
       "+---------------------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT DATETIME('now', 'localtime') AS date_time_of_now;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a839c38c",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 使用 `STRFTIME` 函數調整日期、時間或日期時間的顯示格式\n",
    "\n",
    "```sql\n",
    "STRFTIME(format, DATE/TIME/DATETIME)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "demanding-mandate",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 常見的日期與日期時間格式參數\n",
    "\n",
    "- `%d`：二位數的日（01-31）\n",
    "- `%j`：一年中的第幾天（001-366）\n",
    "- `%m`：二位數的月（01-12）\n",
    "- `%w`：一星期中的第幾天（0-6）\n",
    "- `%W`：一年中的第幾週（00-53）\n",
    "- `%Y`：四位數的年（0000-9999）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "planned-shade",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>date_of_today</th>\n",
       "<th>day_part</th>\n",
       "<th>year_day_format</th>\n",
       "<th>month_part</th>\n",
       "<th>weekday</th>\n",
       "<th>nth_week</th>\n",
       "<th>year_part</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2022-12-15</td>\n",
       "<td>15</td>\n",
       "<td>349</td>\n",
       "<td>12</td>\n",
       "<td>4</td>\n",
       "<td>50</td>\n",
       "<td>2022</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+---------------+----------+-----------------+------------+---------+----------+-----------+\n",
       "| date_of_today | day_part | year_day_format | month_part | weekday | nth_week | year_part |\n",
       "+---------------+----------+-----------------+------------+---------+----------+-----------+\n",
       "| 2022-12-15    | 15       | 349             | 12         | 4       | 50       | 2022      |\n",
       "+---------------+----------+-----------------+------------+---------+----------+-----------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT DATE('now') AS date_of_today,\n",
    "       STRFTIME('%d', DATE('now')) AS day_part,\n",
    "       STRFTIME('%j', DATE('now')) AS year_day_format,\n",
    "       STRFTIME('%m', DATE('now')) AS month_part,\n",
    "       STRFTIME('%w', DATE('now')) AS weekday,\n",
    "       STRFTIME('%W', DATE('now')) AS nth_week,\n",
    "       STRFTIME('%Y', DATE('now')) AS year_part;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "under-raleigh",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## SQLite 通用函數與操作日期時間函數的官方文件\n",
    "\n",
    "- [Built-In Scalar SQL Functions](https://www.sqlite.org/lang_corefunc.html)\n",
    "- [Date And Time Functions](https://www.sqlite.org/lang_datefunc.html)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "welcome-wales",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 常見的聚合函數\n",
    "\n",
    "- `AVG(column_name)`：計算變數的平均數\n",
    "- `COUNT(column_name)`：計算變數的「非」遺漏值數\n",
    "- `COUNT(*)`：計算資料表的觀測值數\n",
    "- `MAX(column_name)`：計算變數的最大值\n",
    "- `MIN(column_name)`：計算變數的最小值\n",
    "- `SUM(column_name)`：計算變數的加總"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "announced-masters",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>avg_rating</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>8.30519999999997</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+------------------+\n",
       "| avg_rating       |\n",
       "+------------------+\n",
       "| 8.30519999999997 |\n",
       "+------------------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT AVG(rating) AS avg_rating\n",
    "  FROM movies;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "exposed-paraguay",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>number_of_rows</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>250</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+----------------+\n",
       "| number_of_rows |\n",
       "+----------------+\n",
       "| 250            |\n",
       "+----------------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT COUNT(*) AS number_of_rows\n",
    "  FROM movies;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "becoming-mumbai",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>number_of_rows</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>0</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+----------------+\n",
       "| number_of_rows |\n",
       "+----------------+\n",
       "| 0              |\n",
       "+----------------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT COUNT(NULL) AS number_of_rows;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "correct-canadian",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>max_release_year</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2021</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+------------------+\n",
       "| max_release_year |\n",
       "+------------------+\n",
       "| 2021             |\n",
       "+------------------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT MAX(release_year) AS max_release_year\n",
    "  FROM movies;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "heated-timeline",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>min_release_year</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1921</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+------------------+\n",
       "| min_release_year |\n",
       "+------------------+\n",
       "| 1921             |\n",
       "+------------------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT MIN(release_year) AS min_release_year\n",
    "  FROM movies;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "solid-conditioning",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## SQLite 聚合函數的官方文件\n",
    "\n",
    "[Built-in Aggregate Functions](https://www.sqlite.org/lang_aggfunc.html)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4218ba04",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 隨堂練習：`movies` 資料表有幾位導演\n",
    "\n",
    "寫出能夠得到下列預期結果的 SQL 查詢。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "11f26d1b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>number_of_directors</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>157</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+---------------------+\n",
       "| number_of_directors |\n",
       "+---------------------+\n",
       "| 157                 |\n",
       "+---------------------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "472f91b8",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>number_of_directors</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>157</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+---------------------+\n",
       "| number_of_directors |\n",
       "+---------------------+\n",
       "| 157                 |\n",
       "+---------------------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT COUNT(DISTINCT director) AS number_of_directors\n",
    "  FROM movies;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "encouraging-champion",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 基礎 SQL 語法：分組與聚合結果篩選"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "endless-sodium",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 分組 `GROUP BY` 的功能可以視為 `DISTINCT` 與 `ORDER BY` 兩者同時作用\n",
    "\n",
    "```sql\n",
    "SELECT column_names\n",
    "  FROM table_name\n",
    " GROUP BY column_names;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "unable-paraguay",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## `DISTINCT` 與 `ORDER BY` 兩者同時作用"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "loose-script",
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>distinct_director</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Aamir Khan</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Adam Elliot</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Akira Kurosawa</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Alejandro G. Iñárritu</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Alfred Hitchcock</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Andrei Tarkovsky</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Andrew Stanton</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Anthony Russo</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Anurag Kashyap</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Asghar Farhadi</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Ashutosh Gowariker</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Billy Wilder</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Bob Persichetti</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Bong Joon Ho</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Brian De Palma</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Bryan Singer</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Buster Keaton</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Carl Theodor Dreyer</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Carol Reed</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Chan-wook Park</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Charles Chaplin</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Christopher Nolan</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Clint Eastwood</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Clyde Bruckman</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Curtis Hanson</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Céline Sciamma</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Damien Chazelle</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Damián Szifron</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Danny Boyle</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Darren Aronofsky</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>David Fincher</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>David Lean</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>David Lynch</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>David Yates</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Dean DeBlois</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Denis Villeneuve</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Elem Klimov</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Elia Kazan</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Emir Kusturica</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Ernst Lubitsch</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Ethan Coen</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Fernando Meirelles</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Florian Henckel von Donnersmarck</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Francis Ford Coppola</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Frank Capra</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Frank Darabont</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>François Truffaut</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Fritz Lang</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Gavin O'Connor</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>George Lucas</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>George Miller</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>George Roy Hill</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Giuseppe Tornatore</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Guillermo del Toro</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Gus Van Sant</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Guy Ritchie</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Hayao Miyazaki</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Henri-Georges Clouzot</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Hideaki Anno</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Hrishikesh Mukherjee</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Ingmar Bergman</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Irvin Kershner</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Isao Takahata</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>James Cameron</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>James Mangold</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>James McTeigue</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Jean-Pierre Jeunet</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Jeethu Joseph</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Jim Sheridan</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Joel Coen</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>John Carpenter</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>John G. Avildsen</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>John Huston</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>John Lasseter</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>John McTiernan</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>John Sturges</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Jonathan Demme</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Joseph L. Mankiewicz</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Juan José Campanella</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Jules Dassin</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Kar-Wai Wong</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Lana Wachowski</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Lasse Hallström</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Lee Unkrich</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Lenny Abrahamson</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Luc Besson</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>M. Night Shyamalan</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Majid Majidi</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Makoto Shinkai</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Martin McDonagh</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Martin Scorsese</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Masaki Kobayashi</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Mathieu Kassovitz</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Mehmet Ada Öztekin</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Mel Gibson</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Michael Cimino</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Michael Curtiz</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Michael Mann</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Michel Gondry</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Milos Forman</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Nadine Labaki</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Nishikant Kamat</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Nitesh Tiwari</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Oliver Hirschbiegel</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Oliver Stone</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Olivier Nakache</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Orson Welles</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Paul Thomas Anderson</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Pete Docter</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Peter Farrelly</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Peter Jackson</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Peter Weir</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Quentin Tarantino</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Rajkumar Hirani</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Rakeysh Omprakash Mehra</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Ram Kumar</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Richard Linklater</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Richard Marquand</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Ridley Scott</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Rob Reiner</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Robert Mulligan</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Robert Zemeckis</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Roberto Benigni</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Roger Allers</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Roman Polanski</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Ron Howard</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Sam Mendes</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Sean Penn</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Sergio Leone</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Sergio Pablos</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Sidney Lumet</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Sriram Raghavan</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Stanley Donen</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Stanley Kramer</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Stanley Kubrick</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Steve McQueen</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Steven Spielberg</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Stuart Rosenberg</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Terry George</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Terry Gilliam</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Terry Jones</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Thomas Kail</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Thomas Vinterberg</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Todd Phillips</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Tom McCarthy</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Tony Kaye</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Victor Fleming</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Vittorio De Sica</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Wes Anderson</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>William Wyler</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Wim Wenders</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Wolfgang Petersen</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Yasujirô Ozu</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Yavuz Turgul</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Zack Snyder</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Zaza Urushadze</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Çagan Irmak</td>\n",
       "</tr>\n",
       "</table>\n",
       "157 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+----------------------------------+\n",
       "| distinct_director                |\n",
       "+----------------------------------+\n",
       "| Aamir Khan                       |\n",
       "+----------------------------------+\n",
       "| Adam Elliot                      |\n",
       "+----------------------------------+\n",
       "| Akira Kurosawa                   |\n",
       "+----------------------------------+\n",
       "| Alejandro G. Iñárritu          |\n",
       "+----------------------------------+\n",
       "| Alfred Hitchcock                 |\n",
       "+----------------------------------+\n",
       "| Andrei Tarkovsky                 |\n",
       "+----------------------------------+\n",
       "| Andrew Stanton                   |\n",
       "+----------------------------------+\n",
       "| Anthony Russo                    |\n",
       "+----------------------------------+\n",
       "| Anurag Kashyap                   |\n",
       "+----------------------------------+\n",
       "| Asghar Farhadi                   |\n",
       "+----------------------------------+\n",
       "| Ashutosh Gowariker               |\n",
       "+----------------------------------+\n",
       "| Billy Wilder                     |\n",
       "+----------------------------------+\n",
       "| Bob Persichetti                  |\n",
       "+----------------------------------+\n",
       "| Bong Joon Ho                     |\n",
       "+----------------------------------+\n",
       "| Brian De Palma                   |\n",
       "+----------------------------------+\n",
       "| Bryan Singer                     |\n",
       "+----------------------------------+\n",
       "| Buster Keaton                    |\n",
       "+----------------------------------+\n",
       "| Carl Theodor Dreyer              |\n",
       "+----------------------------------+\n",
       "| Carol Reed                       |\n",
       "+----------------------------------+\n",
       "| Chan-wook Park                   |\n",
       "+----------------------------------+\n",
       "| Charles Chaplin                  |\n",
       "+----------------------------------+\n",
       "| Christopher Nolan                |\n",
       "+----------------------------------+\n",
       "| Clint Eastwood                   |\n",
       "+----------------------------------+\n",
       "| Clyde Bruckman                   |\n",
       "+----------------------------------+\n",
       "| Curtis Hanson                    |\n",
       "+----------------------------------+\n",
       "| Céline Sciamma                  |\n",
       "+----------------------------------+\n",
       "| Damien Chazelle                  |\n",
       "+----------------------------------+\n",
       "| Damián Szifron                  |\n",
       "+----------------------------------+\n",
       "| Danny Boyle                      |\n",
       "+----------------------------------+\n",
       "| Darren Aronofsky                 |\n",
       "+----------------------------------+\n",
       "| David Fincher                    |\n",
       "+----------------------------------+\n",
       "| David Lean                       |\n",
       "+----------------------------------+\n",
       "| David Lynch                      |\n",
       "+----------------------------------+\n",
       "| David Yates                      |\n",
       "+----------------------------------+\n",
       "| Dean DeBlois                     |\n",
       "+----------------------------------+\n",
       "| Denis Villeneuve                 |\n",
       "+----------------------------------+\n",
       "| Elem Klimov                      |\n",
       "+----------------------------------+\n",
       "| Elia Kazan                       |\n",
       "+----------------------------------+\n",
       "| Emir Kusturica                   |\n",
       "+----------------------------------+\n",
       "| Ernst Lubitsch                   |\n",
       "+----------------------------------+\n",
       "| Ethan Coen                       |\n",
       "+----------------------------------+\n",
       "| Fernando Meirelles               |\n",
       "+----------------------------------+\n",
       "| Florian Henckel von Donnersmarck |\n",
       "+----------------------------------+\n",
       "| Francis Ford Coppola             |\n",
       "+----------------------------------+\n",
       "| Frank Capra                      |\n",
       "+----------------------------------+\n",
       "| Frank Darabont                   |\n",
       "+----------------------------------+\n",
       "| François Truffaut               |\n",
       "+----------------------------------+\n",
       "| Fritz Lang                       |\n",
       "+----------------------------------+\n",
       "| Gavin O'Connor                   |\n",
       "+----------------------------------+\n",
       "| George Lucas                     |\n",
       "+----------------------------------+\n",
       "| George Miller                    |\n",
       "+----------------------------------+\n",
       "| George Roy Hill                  |\n",
       "+----------------------------------+\n",
       "| Giuseppe Tornatore               |\n",
       "+----------------------------------+\n",
       "| Guillermo del Toro               |\n",
       "+----------------------------------+\n",
       "| Gus Van Sant                     |\n",
       "+----------------------------------+\n",
       "| Guy Ritchie                      |\n",
       "+----------------------------------+\n",
       "| Hayao Miyazaki                   |\n",
       "+----------------------------------+\n",
       "| Henri-Georges Clouzot            |\n",
       "+----------------------------------+\n",
       "| Hideaki Anno                     |\n",
       "+----------------------------------+\n",
       "| Hrishikesh Mukherjee             |\n",
       "+----------------------------------+\n",
       "| Ingmar Bergman                   |\n",
       "+----------------------------------+\n",
       "| Irvin Kershner                   |\n",
       "+----------------------------------+\n",
       "| Isao Takahata                    |\n",
       "+----------------------------------+\n",
       "| James Cameron                    |\n",
       "+----------------------------------+\n",
       "| James Mangold                    |\n",
       "+----------------------------------+\n",
       "| James McTeigue                   |\n",
       "+----------------------------------+\n",
       "| Jean-Pierre Jeunet               |\n",
       "+----------------------------------+\n",
       "| Jeethu Joseph                    |\n",
       "+----------------------------------+\n",
       "| Jim Sheridan                     |\n",
       "+----------------------------------+\n",
       "| Joel Coen                        |\n",
       "+----------------------------------+\n",
       "| John Carpenter                   |\n",
       "+----------------------------------+\n",
       "| John G. Avildsen                 |\n",
       "+----------------------------------+\n",
       "| John Huston                      |\n",
       "+----------------------------------+\n",
       "| John Lasseter                    |\n",
       "+----------------------------------+\n",
       "| John McTiernan                   |\n",
       "+----------------------------------+\n",
       "| John Sturges                     |\n",
       "+----------------------------------+\n",
       "| Jonathan Demme                   |\n",
       "+----------------------------------+\n",
       "| Joseph L. Mankiewicz             |\n",
       "+----------------------------------+\n",
       "| Juan José Campanella            |\n",
       "+----------------------------------+\n",
       "| Jules Dassin                     |\n",
       "+----------------------------------+\n",
       "| Kar-Wai Wong                     |\n",
       "+----------------------------------+\n",
       "| Lana Wachowski                   |\n",
       "+----------------------------------+\n",
       "| Lasse Hallström                 |\n",
       "+----------------------------------+\n",
       "| Lee Unkrich                      |\n",
       "+----------------------------------+\n",
       "| Lenny Abrahamson                 |\n",
       "+----------------------------------+\n",
       "| Luc Besson                       |\n",
       "+----------------------------------+\n",
       "| M. Night Shyamalan               |\n",
       "+----------------------------------+\n",
       "| Majid Majidi                     |\n",
       "+----------------------------------+\n",
       "| Makoto Shinkai                   |\n",
       "+----------------------------------+\n",
       "| Martin McDonagh                  |\n",
       "+----------------------------------+\n",
       "| Martin Scorsese                  |\n",
       "+----------------------------------+\n",
       "| Masaki Kobayashi                 |\n",
       "+----------------------------------+\n",
       "| Mathieu Kassovitz                |\n",
       "+----------------------------------+\n",
       "| Mehmet Ada Öztekin              |\n",
       "+----------------------------------+\n",
       "| Mel Gibson                       |\n",
       "+----------------------------------+\n",
       "| Michael Cimino                   |\n",
       "+----------------------------------+\n",
       "| Michael Curtiz                   |\n",
       "+----------------------------------+\n",
       "| Michael Mann                     |\n",
       "+----------------------------------+\n",
       "| Michel Gondry                    |\n",
       "+----------------------------------+\n",
       "| Milos Forman                     |\n",
       "+----------------------------------+\n",
       "| Nadine Labaki                    |\n",
       "+----------------------------------+\n",
       "| Nishikant Kamat                  |\n",
       "+----------------------------------+\n",
       "| Nitesh Tiwari                    |\n",
       "+----------------------------------+\n",
       "| Oliver Hirschbiegel              |\n",
       "+----------------------------------+\n",
       "| Oliver Stone                     |\n",
       "+----------------------------------+\n",
       "| Olivier Nakache                  |\n",
       "+----------------------------------+\n",
       "| Orson Welles                     |\n",
       "+----------------------------------+\n",
       "| Paul Thomas Anderson             |\n",
       "+----------------------------------+\n",
       "| Pete Docter                      |\n",
       "+----------------------------------+\n",
       "| Peter Farrelly                   |\n",
       "+----------------------------------+\n",
       "| Peter Jackson                    |\n",
       "+----------------------------------+\n",
       "| Peter Weir                       |\n",
       "+----------------------------------+\n",
       "| Quentin Tarantino                |\n",
       "+----------------------------------+\n",
       "| Rajkumar Hirani                  |\n",
       "+----------------------------------+\n",
       "| Rakeysh Omprakash Mehra          |\n",
       "+----------------------------------+\n",
       "| Ram Kumar                        |\n",
       "+----------------------------------+\n",
       "| Richard Linklater                |\n",
       "+----------------------------------+\n",
       "| Richard Marquand                 |\n",
       "+----------------------------------+\n",
       "| Ridley Scott                     |\n",
       "+----------------------------------+\n",
       "| Rob Reiner                       |\n",
       "+----------------------------------+\n",
       "| Robert Mulligan                  |\n",
       "+----------------------------------+\n",
       "| Robert Zemeckis                  |\n",
       "+----------------------------------+\n",
       "| Roberto Benigni                  |\n",
       "+----------------------------------+\n",
       "| Roger Allers                     |\n",
       "+----------------------------------+\n",
       "| Roman Polanski                   |\n",
       "+----------------------------------+\n",
       "| Ron Howard                       |\n",
       "+----------------------------------+\n",
       "| Sam Mendes                       |\n",
       "+----------------------------------+\n",
       "| Sean Penn                        |\n",
       "+----------------------------------+\n",
       "| Sergio Leone                     |\n",
       "+----------------------------------+\n",
       "| Sergio Pablos                    |\n",
       "+----------------------------------+\n",
       "| Sidney Lumet                     |\n",
       "+----------------------------------+\n",
       "| Sriram Raghavan                  |\n",
       "+----------------------------------+\n",
       "| Stanley Donen                    |\n",
       "+----------------------------------+\n",
       "| Stanley Kramer                   |\n",
       "+----------------------------------+\n",
       "| Stanley Kubrick                  |\n",
       "+----------------------------------+\n",
       "| Steve McQueen                    |\n",
       "+----------------------------------+\n",
       "| Steven Spielberg                 |\n",
       "+----------------------------------+\n",
       "| Stuart Rosenberg                 |\n",
       "+----------------------------------+\n",
       "| Terry George                     |\n",
       "+----------------------------------+\n",
       "| Terry Gilliam                    |\n",
       "+----------------------------------+\n",
       "| Terry Jones                      |\n",
       "+----------------------------------+\n",
       "| Thomas Kail                      |\n",
       "+----------------------------------+\n",
       "| Thomas Vinterberg                |\n",
       "+----------------------------------+\n",
       "| Todd Phillips                    |\n",
       "+----------------------------------+\n",
       "| Tom McCarthy                     |\n",
       "+----------------------------------+\n",
       "| Tony Kaye                        |\n",
       "+----------------------------------+\n",
       "| Victor Fleming                   |\n",
       "+----------------------------------+\n",
       "| Vittorio De Sica                 |\n",
       "+----------------------------------+\n",
       "| Wes Anderson                     |\n",
       "+----------------------------------+\n",
       "| William Wyler                    |\n",
       "+----------------------------------+\n",
       "| Wim Wenders                      |\n",
       "+----------------------------------+\n",
       "| Wolfgang Petersen                |\n",
       "+----------------------------------+\n",
       "| Yasujirô Ozu                    |\n",
       "+----------------------------------+\n",
       "| Yavuz Turgul                     |\n",
       "+----------------------------------+\n",
       "| Zack Snyder                      |\n",
       "+----------------------------------+\n",
       "| Zaza Urushadze                   |\n",
       "+----------------------------------+\n",
       "| Çagan Irmak                     |\n",
       "+----------------------------------+\n",
       "157 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT DISTINCT director AS distinct_director\n",
    "  FROM movies\n",
    " ORDER BY director;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "billion-ratio",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 使用分組 `GROUP BY`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "annoying-milton",
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>distinct_director</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Aamir Khan</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Adam Elliot</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Akira Kurosawa</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Alejandro G. Iñárritu</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Alfred Hitchcock</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Andrei Tarkovsky</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Andrew Stanton</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Anthony Russo</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Anurag Kashyap</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Asghar Farhadi</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Ashutosh Gowariker</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Billy Wilder</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Bob Persichetti</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Bong Joon Ho</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Brian De Palma</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Bryan Singer</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Buster Keaton</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Carl Theodor Dreyer</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Carol Reed</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Chan-wook Park</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Charles Chaplin</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Christopher Nolan</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Clint Eastwood</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Clyde Bruckman</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Curtis Hanson</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Céline Sciamma</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Damien Chazelle</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Damián Szifron</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Danny Boyle</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Darren Aronofsky</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>David Fincher</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>David Lean</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>David Lynch</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>David Yates</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Dean DeBlois</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Denis Villeneuve</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Elem Klimov</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Elia Kazan</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Emir Kusturica</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Ernst Lubitsch</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Ethan Coen</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Fernando Meirelles</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Florian Henckel von Donnersmarck</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Francis Ford Coppola</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Frank Capra</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Frank Darabont</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>François Truffaut</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Fritz Lang</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Gavin O'Connor</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>George Lucas</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>George Miller</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>George Roy Hill</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Giuseppe Tornatore</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Guillermo del Toro</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Gus Van Sant</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Guy Ritchie</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Hayao Miyazaki</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Henri-Georges Clouzot</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Hideaki Anno</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Hrishikesh Mukherjee</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Ingmar Bergman</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Irvin Kershner</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Isao Takahata</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>James Cameron</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>James Mangold</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>James McTeigue</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Jean-Pierre Jeunet</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Jeethu Joseph</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Jim Sheridan</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Joel Coen</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>John Carpenter</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>John G. Avildsen</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>John Huston</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>John Lasseter</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>John McTiernan</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>John Sturges</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Jonathan Demme</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Joseph L. Mankiewicz</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Juan José Campanella</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Jules Dassin</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Kar-Wai Wong</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Lana Wachowski</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Lasse Hallström</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Lee Unkrich</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Lenny Abrahamson</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Luc Besson</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>M. Night Shyamalan</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Majid Majidi</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Makoto Shinkai</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Martin McDonagh</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Martin Scorsese</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Masaki Kobayashi</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Mathieu Kassovitz</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Mehmet Ada Öztekin</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Mel Gibson</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Michael Cimino</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Michael Curtiz</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Michael Mann</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Michel Gondry</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Milos Forman</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Nadine Labaki</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Nishikant Kamat</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Nitesh Tiwari</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Oliver Hirschbiegel</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Oliver Stone</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Olivier Nakache</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Orson Welles</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Paul Thomas Anderson</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Pete Docter</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Peter Farrelly</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Peter Jackson</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Peter Weir</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Quentin Tarantino</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Rajkumar Hirani</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Rakeysh Omprakash Mehra</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Ram Kumar</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Richard Linklater</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Richard Marquand</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Ridley Scott</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Rob Reiner</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Robert Mulligan</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Robert Zemeckis</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Roberto Benigni</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Roger Allers</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Roman Polanski</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Ron Howard</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Sam Mendes</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Sean Penn</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Sergio Leone</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Sergio Pablos</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Sidney Lumet</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Sriram Raghavan</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Stanley Donen</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Stanley Kramer</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Stanley Kubrick</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Steve McQueen</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Steven Spielberg</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Stuart Rosenberg</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Terry George</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Terry Gilliam</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Terry Jones</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Thomas Kail</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Thomas Vinterberg</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Todd Phillips</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Tom McCarthy</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Tony Kaye</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Victor Fleming</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Vittorio De Sica</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Wes Anderson</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>William Wyler</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Wim Wenders</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Wolfgang Petersen</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Yasujirô Ozu</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Yavuz Turgul</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Zack Snyder</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Zaza Urushadze</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Çagan Irmak</td>\n",
       "</tr>\n",
       "</table>\n",
       "157 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+----------------------------------+\n",
       "| distinct_director                |\n",
       "+----------------------------------+\n",
       "| Aamir Khan                       |\n",
       "+----------------------------------+\n",
       "| Adam Elliot                      |\n",
       "+----------------------------------+\n",
       "| Akira Kurosawa                   |\n",
       "+----------------------------------+\n",
       "| Alejandro G. Iñárritu          |\n",
       "+----------------------------------+\n",
       "| Alfred Hitchcock                 |\n",
       "+----------------------------------+\n",
       "| Andrei Tarkovsky                 |\n",
       "+----------------------------------+\n",
       "| Andrew Stanton                   |\n",
       "+----------------------------------+\n",
       "| Anthony Russo                    |\n",
       "+----------------------------------+\n",
       "| Anurag Kashyap                   |\n",
       "+----------------------------------+\n",
       "| Asghar Farhadi                   |\n",
       "+----------------------------------+\n",
       "| Ashutosh Gowariker               |\n",
       "+----------------------------------+\n",
       "| Billy Wilder                     |\n",
       "+----------------------------------+\n",
       "| Bob Persichetti                  |\n",
       "+----------------------------------+\n",
       "| Bong Joon Ho                     |\n",
       "+----------------------------------+\n",
       "| Brian De Palma                   |\n",
       "+----------------------------------+\n",
       "| Bryan Singer                     |\n",
       "+----------------------------------+\n",
       "| Buster Keaton                    |\n",
       "+----------------------------------+\n",
       "| Carl Theodor Dreyer              |\n",
       "+----------------------------------+\n",
       "| Carol Reed                       |\n",
       "+----------------------------------+\n",
       "| Chan-wook Park                   |\n",
       "+----------------------------------+\n",
       "| Charles Chaplin                  |\n",
       "+----------------------------------+\n",
       "| Christopher Nolan                |\n",
       "+----------------------------------+\n",
       "| Clint Eastwood                   |\n",
       "+----------------------------------+\n",
       "| Clyde Bruckman                   |\n",
       "+----------------------------------+\n",
       "| Curtis Hanson                    |\n",
       "+----------------------------------+\n",
       "| Céline Sciamma                  |\n",
       "+----------------------------------+\n",
       "| Damien Chazelle                  |\n",
       "+----------------------------------+\n",
       "| Damián Szifron                  |\n",
       "+----------------------------------+\n",
       "| Danny Boyle                      |\n",
       "+----------------------------------+\n",
       "| Darren Aronofsky                 |\n",
       "+----------------------------------+\n",
       "| David Fincher                    |\n",
       "+----------------------------------+\n",
       "| David Lean                       |\n",
       "+----------------------------------+\n",
       "| David Lynch                      |\n",
       "+----------------------------------+\n",
       "| David Yates                      |\n",
       "+----------------------------------+\n",
       "| Dean DeBlois                     |\n",
       "+----------------------------------+\n",
       "| Denis Villeneuve                 |\n",
       "+----------------------------------+\n",
       "| Elem Klimov                      |\n",
       "+----------------------------------+\n",
       "| Elia Kazan                       |\n",
       "+----------------------------------+\n",
       "| Emir Kusturica                   |\n",
       "+----------------------------------+\n",
       "| Ernst Lubitsch                   |\n",
       "+----------------------------------+\n",
       "| Ethan Coen                       |\n",
       "+----------------------------------+\n",
       "| Fernando Meirelles               |\n",
       "+----------------------------------+\n",
       "| Florian Henckel von Donnersmarck |\n",
       "+----------------------------------+\n",
       "| Francis Ford Coppola             |\n",
       "+----------------------------------+\n",
       "| Frank Capra                      |\n",
       "+----------------------------------+\n",
       "| Frank Darabont                   |\n",
       "+----------------------------------+\n",
       "| François Truffaut               |\n",
       "+----------------------------------+\n",
       "| Fritz Lang                       |\n",
       "+----------------------------------+\n",
       "| Gavin O'Connor                   |\n",
       "+----------------------------------+\n",
       "| George Lucas                     |\n",
       "+----------------------------------+\n",
       "| George Miller                    |\n",
       "+----------------------------------+\n",
       "| George Roy Hill                  |\n",
       "+----------------------------------+\n",
       "| Giuseppe Tornatore               |\n",
       "+----------------------------------+\n",
       "| Guillermo del Toro               |\n",
       "+----------------------------------+\n",
       "| Gus Van Sant                     |\n",
       "+----------------------------------+\n",
       "| Guy Ritchie                      |\n",
       "+----------------------------------+\n",
       "| Hayao Miyazaki                   |\n",
       "+----------------------------------+\n",
       "| Henri-Georges Clouzot            |\n",
       "+----------------------------------+\n",
       "| Hideaki Anno                     |\n",
       "+----------------------------------+\n",
       "| Hrishikesh Mukherjee             |\n",
       "+----------------------------------+\n",
       "| Ingmar Bergman                   |\n",
       "+----------------------------------+\n",
       "| Irvin Kershner                   |\n",
       "+----------------------------------+\n",
       "| Isao Takahata                    |\n",
       "+----------------------------------+\n",
       "| James Cameron                    |\n",
       "+----------------------------------+\n",
       "| James Mangold                    |\n",
       "+----------------------------------+\n",
       "| James McTeigue                   |\n",
       "+----------------------------------+\n",
       "| Jean-Pierre Jeunet               |\n",
       "+----------------------------------+\n",
       "| Jeethu Joseph                    |\n",
       "+----------------------------------+\n",
       "| Jim Sheridan                     |\n",
       "+----------------------------------+\n",
       "| Joel Coen                        |\n",
       "+----------------------------------+\n",
       "| John Carpenter                   |\n",
       "+----------------------------------+\n",
       "| John G. Avildsen                 |\n",
       "+----------------------------------+\n",
       "| John Huston                      |\n",
       "+----------------------------------+\n",
       "| John Lasseter                    |\n",
       "+----------------------------------+\n",
       "| John McTiernan                   |\n",
       "+----------------------------------+\n",
       "| John Sturges                     |\n",
       "+----------------------------------+\n",
       "| Jonathan Demme                   |\n",
       "+----------------------------------+\n",
       "| Joseph L. Mankiewicz             |\n",
       "+----------------------------------+\n",
       "| Juan José Campanella            |\n",
       "+----------------------------------+\n",
       "| Jules Dassin                     |\n",
       "+----------------------------------+\n",
       "| Kar-Wai Wong                     |\n",
       "+----------------------------------+\n",
       "| Lana Wachowski                   |\n",
       "+----------------------------------+\n",
       "| Lasse Hallström                 |\n",
       "+----------------------------------+\n",
       "| Lee Unkrich                      |\n",
       "+----------------------------------+\n",
       "| Lenny Abrahamson                 |\n",
       "+----------------------------------+\n",
       "| Luc Besson                       |\n",
       "+----------------------------------+\n",
       "| M. Night Shyamalan               |\n",
       "+----------------------------------+\n",
       "| Majid Majidi                     |\n",
       "+----------------------------------+\n",
       "| Makoto Shinkai                   |\n",
       "+----------------------------------+\n",
       "| Martin McDonagh                  |\n",
       "+----------------------------------+\n",
       "| Martin Scorsese                  |\n",
       "+----------------------------------+\n",
       "| Masaki Kobayashi                 |\n",
       "+----------------------------------+\n",
       "| Mathieu Kassovitz                |\n",
       "+----------------------------------+\n",
       "| Mehmet Ada Öztekin              |\n",
       "+----------------------------------+\n",
       "| Mel Gibson                       |\n",
       "+----------------------------------+\n",
       "| Michael Cimino                   |\n",
       "+----------------------------------+\n",
       "| Michael Curtiz                   |\n",
       "+----------------------------------+\n",
       "| Michael Mann                     |\n",
       "+----------------------------------+\n",
       "| Michel Gondry                    |\n",
       "+----------------------------------+\n",
       "| Milos Forman                     |\n",
       "+----------------------------------+\n",
       "| Nadine Labaki                    |\n",
       "+----------------------------------+\n",
       "| Nishikant Kamat                  |\n",
       "+----------------------------------+\n",
       "| Nitesh Tiwari                    |\n",
       "+----------------------------------+\n",
       "| Oliver Hirschbiegel              |\n",
       "+----------------------------------+\n",
       "| Oliver Stone                     |\n",
       "+----------------------------------+\n",
       "| Olivier Nakache                  |\n",
       "+----------------------------------+\n",
       "| Orson Welles                     |\n",
       "+----------------------------------+\n",
       "| Paul Thomas Anderson             |\n",
       "+----------------------------------+\n",
       "| Pete Docter                      |\n",
       "+----------------------------------+\n",
       "| Peter Farrelly                   |\n",
       "+----------------------------------+\n",
       "| Peter Jackson                    |\n",
       "+----------------------------------+\n",
       "| Peter Weir                       |\n",
       "+----------------------------------+\n",
       "| Quentin Tarantino                |\n",
       "+----------------------------------+\n",
       "| Rajkumar Hirani                  |\n",
       "+----------------------------------+\n",
       "| Rakeysh Omprakash Mehra          |\n",
       "+----------------------------------+\n",
       "| Ram Kumar                        |\n",
       "+----------------------------------+\n",
       "| Richard Linklater                |\n",
       "+----------------------------------+\n",
       "| Richard Marquand                 |\n",
       "+----------------------------------+\n",
       "| Ridley Scott                     |\n",
       "+----------------------------------+\n",
       "| Rob Reiner                       |\n",
       "+----------------------------------+\n",
       "| Robert Mulligan                  |\n",
       "+----------------------------------+\n",
       "| Robert Zemeckis                  |\n",
       "+----------------------------------+\n",
       "| Roberto Benigni                  |\n",
       "+----------------------------------+\n",
       "| Roger Allers                     |\n",
       "+----------------------------------+\n",
       "| Roman Polanski                   |\n",
       "+----------------------------------+\n",
       "| Ron Howard                       |\n",
       "+----------------------------------+\n",
       "| Sam Mendes                       |\n",
       "+----------------------------------+\n",
       "| Sean Penn                        |\n",
       "+----------------------------------+\n",
       "| Sergio Leone                     |\n",
       "+----------------------------------+\n",
       "| Sergio Pablos                    |\n",
       "+----------------------------------+\n",
       "| Sidney Lumet                     |\n",
       "+----------------------------------+\n",
       "| Sriram Raghavan                  |\n",
       "+----------------------------------+\n",
       "| Stanley Donen                    |\n",
       "+----------------------------------+\n",
       "| Stanley Kramer                   |\n",
       "+----------------------------------+\n",
       "| Stanley Kubrick                  |\n",
       "+----------------------------------+\n",
       "| Steve McQueen                    |\n",
       "+----------------------------------+\n",
       "| Steven Spielberg                 |\n",
       "+----------------------------------+\n",
       "| Stuart Rosenberg                 |\n",
       "+----------------------------------+\n",
       "| Terry George                     |\n",
       "+----------------------------------+\n",
       "| Terry Gilliam                    |\n",
       "+----------------------------------+\n",
       "| Terry Jones                      |\n",
       "+----------------------------------+\n",
       "| Thomas Kail                      |\n",
       "+----------------------------------+\n",
       "| Thomas Vinterberg                |\n",
       "+----------------------------------+\n",
       "| Todd Phillips                    |\n",
       "+----------------------------------+\n",
       "| Tom McCarthy                     |\n",
       "+----------------------------------+\n",
       "| Tony Kaye                        |\n",
       "+----------------------------------+\n",
       "| Victor Fleming                   |\n",
       "+----------------------------------+\n",
       "| Vittorio De Sica                 |\n",
       "+----------------------------------+\n",
       "| Wes Anderson                     |\n",
       "+----------------------------------+\n",
       "| William Wyler                    |\n",
       "+----------------------------------+\n",
       "| Wim Wenders                      |\n",
       "+----------------------------------+\n",
       "| Wolfgang Petersen                |\n",
       "+----------------------------------+\n",
       "| Yasujirô Ozu                    |\n",
       "+----------------------------------+\n",
       "| Yavuz Turgul                     |\n",
       "+----------------------------------+\n",
       "| Zack Snyder                      |\n",
       "+----------------------------------+\n",
       "| Zaza Urushadze                   |\n",
       "+----------------------------------+\n",
       "| Çagan Irmak                     |\n",
       "+----------------------------------+\n",
       "157 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT director AS distinct_director\n",
    "  FROM movies\n",
    " GROUP BY director;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c6df8f74",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 在「函數」的章節，我們介紹過一種「用來彙總資訊」的函數，稱為聚合函數（Aggregate functions）\n",
    "\n",
    "單獨使用聚合函數的時候，是將一整欄變數的資訊彙總後輸出。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "instrumental-dinner",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>avg_rating</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>8.30519999999997</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+------------------+\n",
       "| avg_rating       |\n",
       "+------------------+\n",
       "| 8.30519999999997 |\n",
       "+------------------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT AVG(rating) AS avg_rating  -- 所有電影的平均評等\n",
    "  FROM movies;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "declared-pendant",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 假如現在希望計算不同每一年的電影平均評等，現在我們會怎麼做？\n",
    "\n",
    "- 先知道有哪些上映年份。\n",
    "- 篩選不同上映年份，一一計算平均評等。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "wooden-humanity",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>release_year</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1994</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1972</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1974</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2008</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1957</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1993</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2003</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1966</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2001</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1999</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2010</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2002</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1980</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1990</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1975</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1954</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1995</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1997</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1991</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1946</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1977</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1998</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2014</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2019</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1962</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1985</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1936</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2000</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1960</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2006</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1931</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2011</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1988</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1968</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1942</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1979</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1940</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1981</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2012</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2020</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2018</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1950</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1964</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1984</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2016</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1986</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2017</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1963</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2009</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1983</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2007</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1992</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1958</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2004</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1941</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1952</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1921</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1948</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1987</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1959</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1971</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1976</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1973</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1927</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1965</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1944</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1989</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2005</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1961</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2013</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1925</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2015</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1982</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1996</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2021</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1939</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1953</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1949</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1978</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1924</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1926</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1967</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1928</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1955</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1934</td>\n",
       "</tr>\n",
       "</table>\n",
       "85 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+--------------+\n",
       "| release_year |\n",
       "+--------------+\n",
       "| 1994         |\n",
       "+--------------+\n",
       "| 1972         |\n",
       "+--------------+\n",
       "| 1974         |\n",
       "+--------------+\n",
       "| 2008         |\n",
       "+--------------+\n",
       "| 1957         |\n",
       "+--------------+\n",
       "| 1993         |\n",
       "+--------------+\n",
       "| 2003         |\n",
       "+--------------+\n",
       "| 1966         |\n",
       "+--------------+\n",
       "| 2001         |\n",
       "+--------------+\n",
       "| 1999         |\n",
       "+--------------+\n",
       "| 2010         |\n",
       "+--------------+\n",
       "| 2002         |\n",
       "+--------------+\n",
       "| 1980         |\n",
       "+--------------+\n",
       "| 1990         |\n",
       "+--------------+\n",
       "| 1975         |\n",
       "+--------------+\n",
       "| 1954         |\n",
       "+--------------+\n",
       "| 1995         |\n",
       "+--------------+\n",
       "| 1997         |\n",
       "+--------------+\n",
       "| 1991         |\n",
       "+--------------+\n",
       "| 1946         |\n",
       "+--------------+\n",
       "| 1977         |\n",
       "+--------------+\n",
       "| 1998         |\n",
       "+--------------+\n",
       "| 2014         |\n",
       "+--------------+\n",
       "| 2019         |\n",
       "+--------------+\n",
       "| 1962         |\n",
       "+--------------+\n",
       "| 1985         |\n",
       "+--------------+\n",
       "| 1936         |\n",
       "+--------------+\n",
       "| 2000         |\n",
       "+--------------+\n",
       "| 1960         |\n",
       "+--------------+\n",
       "| 2006         |\n",
       "+--------------+\n",
       "| 1931         |\n",
       "+--------------+\n",
       "| 2011         |\n",
       "+--------------+\n",
       "| 1988         |\n",
       "+--------------+\n",
       "| 1968         |\n",
       "+--------------+\n",
       "| 1942         |\n",
       "+--------------+\n",
       "| 1979         |\n",
       "+--------------+\n",
       "| 1940         |\n",
       "+--------------+\n",
       "| 1981         |\n",
       "+--------------+\n",
       "| 2012         |\n",
       "+--------------+\n",
       "| 2020         |\n",
       "+--------------+\n",
       "| 2018         |\n",
       "+--------------+\n",
       "| 1950         |\n",
       "+--------------+\n",
       "| 1964         |\n",
       "+--------------+\n",
       "| 1984         |\n",
       "+--------------+\n",
       "| 2016         |\n",
       "+--------------+\n",
       "| 1986         |\n",
       "+--------------+\n",
       "| 2017         |\n",
       "+--------------+\n",
       "| 1963         |\n",
       "+--------------+\n",
       "| 2009         |\n",
       "+--------------+\n",
       "| 1983         |\n",
       "+--------------+\n",
       "| 2007         |\n",
       "+--------------+\n",
       "| 1992         |\n",
       "+--------------+\n",
       "| 1958         |\n",
       "+--------------+\n",
       "| 2004         |\n",
       "+--------------+\n",
       "| 1941         |\n",
       "+--------------+\n",
       "| 1952         |\n",
       "+--------------+\n",
       "| 1921         |\n",
       "+--------------+\n",
       "| 1948         |\n",
       "+--------------+\n",
       "| 1987         |\n",
       "+--------------+\n",
       "| 1959         |\n",
       "+--------------+\n",
       "| 1971         |\n",
       "+--------------+\n",
       "| 1976         |\n",
       "+--------------+\n",
       "| 1973         |\n",
       "+--------------+\n",
       "| 1927         |\n",
       "+--------------+\n",
       "| 1965         |\n",
       "+--------------+\n",
       "| 1944         |\n",
       "+--------------+\n",
       "| 1989         |\n",
       "+--------------+\n",
       "| 2005         |\n",
       "+--------------+\n",
       "| 1961         |\n",
       "+--------------+\n",
       "| 2013         |\n",
       "+--------------+\n",
       "| 1925         |\n",
       "+--------------+\n",
       "| 2015         |\n",
       "+--------------+\n",
       "| 1982         |\n",
       "+--------------+\n",
       "| 1996         |\n",
       "+--------------+\n",
       "| 2021         |\n",
       "+--------------+\n",
       "| 1939         |\n",
       "+--------------+\n",
       "| 1953         |\n",
       "+--------------+\n",
       "| 1949         |\n",
       "+--------------+\n",
       "| 1978         |\n",
       "+--------------+\n",
       "| 1924         |\n",
       "+--------------+\n",
       "| 1926         |\n",
       "+--------------+\n",
       "| 1967         |\n",
       "+--------------+\n",
       "| 1928         |\n",
       "+--------------+\n",
       "| 1955         |\n",
       "+--------------+\n",
       "| 1934         |\n",
       "+--------------+\n",
       "85 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT DISTINCT release_year --先知道有哪些上映年份\n",
    "  FROM movies;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "korean-crossing",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 這不是一個聰明的辦法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "apparent-vacation",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>number_of_dist_release_year</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>85</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+-----------------------------+\n",
       "| number_of_dist_release_year |\n",
       "+-----------------------------+\n",
       "| 85                          |\n",
       "+-----------------------------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT COUNT(DISTINCT release_year) AS number_of_dist_release_year\n",
    "  FROM movies;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "weird-houston",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 結合聚合函數與 `GROUP BY` 可以便捷地完成分組聚合\n",
    "\n",
    "```sql\n",
    "SELECT AGGREGATE_FUNCTION(column_names) AS alias\n",
    "  FROM table_name\n",
    " GROUP BY column_names;\n",
    "```\n",
    "\n",
    "![group by](images/group_by.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "fc371cd2",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>release_year</th>\n",
       "<th>avg_rating</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1921</td>\n",
       "<td>8.3</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1924</td>\n",
       "<td>8.2</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1925</td>\n",
       "<td>8.2</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1926</td>\n",
       "<td>8.1</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1927</td>\n",
       "<td>8.3</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1928</td>\n",
       "<td>8.1</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1931</td>\n",
       "<td>8.4</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1934</td>\n",
       "<td>8.1</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1936</td>\n",
       "<td>8.5</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1939</td>\n",
       "<td>8.1</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1940</td>\n",
       "<td>8.25</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1941</td>\n",
       "<td>8.3</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1942</td>\n",
       "<td>8.35</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1944</td>\n",
       "<td>8.3</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1946</td>\n",
       "<td>8.6</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1948</td>\n",
       "<td>8.25</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1949</td>\n",
       "<td>8.1</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1950</td>\n",
       "<td>8.26666666666667</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1952</td>\n",
       "<td>8.3</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1953</td>\n",
       "<td>8.15</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1954</td>\n",
       "<td>8.325</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1955</td>\n",
       "<td>8.1</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1957</td>\n",
       "<td>8.38333333333333</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1958</td>\n",
       "<td>8.3</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1959</td>\n",
       "<td>8.175</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1960</td>\n",
       "<td>8.4</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1961</td>\n",
       "<td>8.2</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1962</td>\n",
       "<td>8.36666666666667</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1963</td>\n",
       "<td>8.3</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1964</td>\n",
       "<td>8.4</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1965</td>\n",
       "<td>8.2</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1966</td>\n",
       "<td>8.33333333333333</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1967</td>\n",
       "<td>8.1</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1968</td>\n",
       "<td>8.4</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1971</td>\n",
       "<td>8.3</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1972</td>\n",
       "<td>9.2</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1973</td>\n",
       "<td>8.3</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1974</td>\n",
       "<td>8.55</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1975</td>\n",
       "<td>8.33333333333333</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1976</td>\n",
       "<td>8.13333333333333</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1977</td>\n",
       "<td>8.6</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1978</td>\n",
       "<td>8.15</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1979</td>\n",
       "<td>8.275</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1980</td>\n",
       "<td>8.35</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1981</td>\n",
       "<td>8.35</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1982</td>\n",
       "<td>8.1</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1983</td>\n",
       "<td>8.3</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1984</td>\n",
       "<td>8.225</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1985</td>\n",
       "<td>8.33333333333333</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1986</td>\n",
       "<td>8.16666666666667</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1987</td>\n",
       "<td>8.3</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1988</td>\n",
       "<td>8.32</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1989</td>\n",
       "<td>8.15</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1990</td>\n",
       "<td>8.7</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1991</td>\n",
       "<td>8.55</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1992</td>\n",
       "<td>8.25</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1993</td>\n",
       "<td>8.36666666666667</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1994</td>\n",
       "<td>8.8</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1995</td>\n",
       "<td>8.2875</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1996</td>\n",
       "<td>8.13333333333333</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1997</td>\n",
       "<td>8.31666666666667</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1998</td>\n",
       "<td>8.3</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1999</td>\n",
       "<td>8.5</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2000</td>\n",
       "<td>8.28333333333333</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2001</td>\n",
       "<td>8.4</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2002</td>\n",
       "<td>8.475</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2003</td>\n",
       "<td>8.32</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2004</td>\n",
       "<td>8.17142857142857</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2005</td>\n",
       "<td>8.23333333333333</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2006</td>\n",
       "<td>8.36</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2007</td>\n",
       "<td>8.2</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2008</td>\n",
       "<td>8.5</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2009</td>\n",
       "<td>8.21666666666667</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2010</td>\n",
       "<td>8.32</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2011</td>\n",
       "<td>8.275</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2012</td>\n",
       "<td>8.325</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2013</td>\n",
       "<td>8.18333333333333</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2014</td>\n",
       "<td>8.28</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2015</td>\n",
       "<td>8.12</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2016</td>\n",
       "<td>8.25</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2017</td>\n",
       "<td>8.2</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2018</td>\n",
       "<td>8.36666666666667</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2019</td>\n",
       "<td>8.2875</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2020</td>\n",
       "<td>8.3</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2021</td>\n",
       "<td>8.3</td>\n",
       "</tr>\n",
       "</table>\n",
       "85 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+--------------+------------------+\n",
       "| release_year | avg_rating       |\n",
       "+--------------+------------------+\n",
       "| 1921         | 8.3              |\n",
       "+--------------+------------------+\n",
       "| 1924         | 8.2              |\n",
       "+--------------+------------------+\n",
       "| 1925         | 8.2              |\n",
       "+--------------+------------------+\n",
       "| 1926         | 8.1              |\n",
       "+--------------+------------------+\n",
       "| 1927         | 8.3              |\n",
       "+--------------+------------------+\n",
       "| 1928         | 8.1              |\n",
       "+--------------+------------------+\n",
       "| 1931         | 8.4              |\n",
       "+--------------+------------------+\n",
       "| 1934         | 8.1              |\n",
       "+--------------+------------------+\n",
       "| 1936         | 8.5              |\n",
       "+--------------+------------------+\n",
       "| 1939         | 8.1              |\n",
       "+--------------+------------------+\n",
       "| 1940         | 8.25             |\n",
       "+--------------+------------------+\n",
       "| 1941         | 8.3              |\n",
       "+--------------+------------------+\n",
       "| 1942         | 8.35             |\n",
       "+--------------+------------------+\n",
       "| 1944         | 8.3              |\n",
       "+--------------+------------------+\n",
       "| 1946         | 8.6              |\n",
       "+--------------+------------------+\n",
       "| 1948         | 8.25             |\n",
       "+--------------+------------------+\n",
       "| 1949         | 8.1              |\n",
       "+--------------+------------------+\n",
       "| 1950         | 8.26666666666667 |\n",
       "+--------------+------------------+\n",
       "| 1952         | 8.3              |\n",
       "+--------------+------------------+\n",
       "| 1953         | 8.15             |\n",
       "+--------------+------------------+\n",
       "| 1954         | 8.325            |\n",
       "+--------------+------------------+\n",
       "| 1955         | 8.1              |\n",
       "+--------------+------------------+\n",
       "| 1957         | 8.38333333333333 |\n",
       "+--------------+------------------+\n",
       "| 1958         | 8.3              |\n",
       "+--------------+------------------+\n",
       "| 1959         | 8.175            |\n",
       "+--------------+------------------+\n",
       "| 1960         | 8.4              |\n",
       "+--------------+------------------+\n",
       "| 1961         | 8.2              |\n",
       "+--------------+------------------+\n",
       "| 1962         | 8.36666666666667 |\n",
       "+--------------+------------------+\n",
       "| 1963         | 8.3              |\n",
       "+--------------+------------------+\n",
       "| 1964         | 8.4              |\n",
       "+--------------+------------------+\n",
       "| 1965         | 8.2              |\n",
       "+--------------+------------------+\n",
       "| 1966         | 8.33333333333333 |\n",
       "+--------------+------------------+\n",
       "| 1967         | 8.1              |\n",
       "+--------------+------------------+\n",
       "| 1968         | 8.4              |\n",
       "+--------------+------------------+\n",
       "| 1971         | 8.3              |\n",
       "+--------------+------------------+\n",
       "| 1972         | 9.2              |\n",
       "+--------------+------------------+\n",
       "| 1973         | 8.3              |\n",
       "+--------------+------------------+\n",
       "| 1974         | 8.55             |\n",
       "+--------------+------------------+\n",
       "| 1975         | 8.33333333333333 |\n",
       "+--------------+------------------+\n",
       "| 1976         | 8.13333333333333 |\n",
       "+--------------+------------------+\n",
       "| 1977         | 8.6              |\n",
       "+--------------+------------------+\n",
       "| 1978         | 8.15             |\n",
       "+--------------+------------------+\n",
       "| 1979         | 8.275            |\n",
       "+--------------+------------------+\n",
       "| 1980         | 8.35             |\n",
       "+--------------+------------------+\n",
       "| 1981         | 8.35             |\n",
       "+--------------+------------------+\n",
       "| 1982         | 8.1              |\n",
       "+--------------+------------------+\n",
       "| 1983         | 8.3              |\n",
       "+--------------+------------------+\n",
       "| 1984         | 8.225            |\n",
       "+--------------+------------------+\n",
       "| 1985         | 8.33333333333333 |\n",
       "+--------------+------------------+\n",
       "| 1986         | 8.16666666666667 |\n",
       "+--------------+------------------+\n",
       "| 1987         | 8.3              |\n",
       "+--------------+------------------+\n",
       "| 1988         | 8.32             |\n",
       "+--------------+------------------+\n",
       "| 1989         | 8.15             |\n",
       "+--------------+------------------+\n",
       "| 1990         | 8.7              |\n",
       "+--------------+------------------+\n",
       "| 1991         | 8.55             |\n",
       "+--------------+------------------+\n",
       "| 1992         | 8.25             |\n",
       "+--------------+------------------+\n",
       "| 1993         | 8.36666666666667 |\n",
       "+--------------+------------------+\n",
       "| 1994         | 8.8              |\n",
       "+--------------+------------------+\n",
       "| 1995         | 8.2875           |\n",
       "+--------------+------------------+\n",
       "| 1996         | 8.13333333333333 |\n",
       "+--------------+------------------+\n",
       "| 1997         | 8.31666666666667 |\n",
       "+--------------+------------------+\n",
       "| 1998         | 8.3              |\n",
       "+--------------+------------------+\n",
       "| 1999         | 8.5              |\n",
       "+--------------+------------------+\n",
       "| 2000         | 8.28333333333333 |\n",
       "+--------------+------------------+\n",
       "| 2001         | 8.4              |\n",
       "+--------------+------------------+\n",
       "| 2002         | 8.475            |\n",
       "+--------------+------------------+\n",
       "| 2003         | 8.32             |\n",
       "+--------------+------------------+\n",
       "| 2004         | 8.17142857142857 |\n",
       "+--------------+------------------+\n",
       "| 2005         | 8.23333333333333 |\n",
       "+--------------+------------------+\n",
       "| 2006         | 8.36             |\n",
       "+--------------+------------------+\n",
       "| 2007         | 8.2              |\n",
       "+--------------+------------------+\n",
       "| 2008         | 8.5              |\n",
       "+--------------+------------------+\n",
       "| 2009         | 8.21666666666667 |\n",
       "+--------------+------------------+\n",
       "| 2010         | 8.32             |\n",
       "+--------------+------------------+\n",
       "| 2011         | 8.275            |\n",
       "+--------------+------------------+\n",
       "| 2012         | 8.325            |\n",
       "+--------------+------------------+\n",
       "| 2013         | 8.18333333333333 |\n",
       "+--------------+------------------+\n",
       "| 2014         | 8.28             |\n",
       "+--------------+------------------+\n",
       "| 2015         | 8.12             |\n",
       "+--------------+------------------+\n",
       "| 2016         | 8.25             |\n",
       "+--------------+------------------+\n",
       "| 2017         | 8.2              |\n",
       "+--------------+------------------+\n",
       "| 2018         | 8.36666666666667 |\n",
       "+--------------+------------------+\n",
       "| 2019         | 8.2875           |\n",
       "+--------------+------------------+\n",
       "| 2020         | 8.3              |\n",
       "+--------------+------------------+\n",
       "| 2021         | 8.3              |\n",
       "+--------------+------------------+\n",
       "85 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT release_year,\n",
    "       AVG(rating) AS avg_rating\n",
    "  FROM movies\n",
    " GROUP BY release_year;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "humanitarian-undergraduate",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 用來篩選資料的兩種方式\n",
    "\n",
    "1. 作用在「觀測值」的 `WHERE`\n",
    "2. 作用在「分組聚合結果」的 `HAVING`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "graphic-collar",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 作用在「分組聚合結果」的 `HAVING`\n",
    "\n",
    "```sql\n",
    "SELECT AGGREGATE_FUNCTION(column_names) AS alias\n",
    "  FROM table_name\n",
    " GROUP BY column_names\n",
    "HAVING conditions;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "defined-beach",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>release_year</th>\n",
       "<th>avg_rating</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1936</td>\n",
       "<td>8.5</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1946</td>\n",
       "<td>8.6</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1972</td>\n",
       "<td>9.2</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1974</td>\n",
       "<td>8.55</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1977</td>\n",
       "<td>8.6</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1990</td>\n",
       "<td>8.7</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1991</td>\n",
       "<td>8.55</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1994</td>\n",
       "<td>8.8</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1999</td>\n",
       "<td>8.5</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2008</td>\n",
       "<td>8.5</td>\n",
       "</tr>\n",
       "</table>\n",
       "10 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+--------------+------------+\n",
       "| release_year | avg_rating |\n",
       "+--------------+------------+\n",
       "| 1936         | 8.5        |\n",
       "+--------------+------------+\n",
       "| 1946         | 8.6        |\n",
       "+--------------+------------+\n",
       "| 1972         | 9.2        |\n",
       "+--------------+------------+\n",
       "| 1974         | 8.55       |\n",
       "+--------------+------------+\n",
       "| 1977         | 8.6        |\n",
       "+--------------+------------+\n",
       "| 1990         | 8.7        |\n",
       "+--------------+------------+\n",
       "| 1991         | 8.55       |\n",
       "+--------------+------------+\n",
       "| 1994         | 8.8        |\n",
       "+--------------+------------+\n",
       "| 1999         | 8.5        |\n",
       "+--------------+------------+\n",
       "| 2008         | 8.5        |\n",
       "+--------------+------------+\n",
       "10 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT release_year,\n",
    "       AVG(rating) AS avg_rating\n",
    "  FROM movies\n",
    " GROUP BY release_year\n",
    "HAVING avg_rating >= 8.5;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c4f8b905",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 隨堂練習：`movies` 資料表每年有幾部電影，只顯示電影數在 5 部以上（`>= 5`）的資料\n",
    "\n",
    "寫出能夠得到下列預期結果的 SQL 查詢。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "9223e0f9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>release_year</th>\n",
       "<th>number_of_movies</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1957</td>\n",
       "<td>6</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1988</td>\n",
       "<td>5</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1994</td>\n",
       "<td>5</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1995</td>\n",
       "<td>8</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1997</td>\n",
       "<td>6</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1998</td>\n",
       "<td>5</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1999</td>\n",
       "<td>5</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2000</td>\n",
       "<td>6</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2001</td>\n",
       "<td>5</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2003</td>\n",
       "<td>5</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2004</td>\n",
       "<td>7</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2006</td>\n",
       "<td>5</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2009</td>\n",
       "<td>6</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2010</td>\n",
       "<td>5</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2013</td>\n",
       "<td>6</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2014</td>\n",
       "<td>5</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2015</td>\n",
       "<td>5</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2018</td>\n",
       "<td>6</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2019</td>\n",
       "<td>8</td>\n",
       "</tr>\n",
       "</table>\n",
       "19 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+--------------+------------------+\n",
       "| release_year | number_of_movies |\n",
       "+--------------+------------------+\n",
       "| 1957         | 6                |\n",
       "+--------------+------------------+\n",
       "| 1988         | 5                |\n",
       "+--------------+------------------+\n",
       "| 1994         | 5                |\n",
       "+--------------+------------------+\n",
       "| 1995         | 8                |\n",
       "+--------------+------------------+\n",
       "| 1997         | 6                |\n",
       "+--------------+------------------+\n",
       "| 1998         | 5                |\n",
       "+--------------+------------------+\n",
       "| 1999         | 5                |\n",
       "+--------------+------------------+\n",
       "| 2000         | 6                |\n",
       "+--------------+------------------+\n",
       "| 2001         | 5                |\n",
       "+--------------+------------------+\n",
       "| 2003         | 5                |\n",
       "+--------------+------------------+\n",
       "| 2004         | 7                |\n",
       "+--------------+------------------+\n",
       "| 2006         | 5                |\n",
       "+--------------+------------------+\n",
       "| 2009         | 6                |\n",
       "+--------------+------------------+\n",
       "| 2010         | 5                |\n",
       "+--------------+------------------+\n",
       "| 2013         | 6                |\n",
       "+--------------+------------------+\n",
       "| 2014         | 5                |\n",
       "+--------------+------------------+\n",
       "| 2015         | 5                |\n",
       "+--------------+------------------+\n",
       "| 2018         | 6                |\n",
       "+--------------+------------------+\n",
       "| 2019         | 8                |\n",
       "+--------------+------------------+\n",
       "19 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "a6ebb7cd",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>release_year</th>\n",
       "<th>number_of_movies</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1957</td>\n",
       "<td>6</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1988</td>\n",
       "<td>5</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1994</td>\n",
       "<td>5</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1995</td>\n",
       "<td>8</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1997</td>\n",
       "<td>6</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1998</td>\n",
       "<td>5</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1999</td>\n",
       "<td>5</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2000</td>\n",
       "<td>6</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2001</td>\n",
       "<td>5</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2003</td>\n",
       "<td>5</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2004</td>\n",
       "<td>7</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2006</td>\n",
       "<td>5</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2009</td>\n",
       "<td>6</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2010</td>\n",
       "<td>5</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2013</td>\n",
       "<td>6</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2014</td>\n",
       "<td>5</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2015</td>\n",
       "<td>5</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2018</td>\n",
       "<td>6</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2019</td>\n",
       "<td>8</td>\n",
       "</tr>\n",
       "</table>\n",
       "19 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+--------------+------------------+\n",
       "| release_year | number_of_movies |\n",
       "+--------------+------------------+\n",
       "| 1957         | 6                |\n",
       "+--------------+------------------+\n",
       "| 1988         | 5                |\n",
       "+--------------+------------------+\n",
       "| 1994         | 5                |\n",
       "+--------------+------------------+\n",
       "| 1995         | 8                |\n",
       "+--------------+------------------+\n",
       "| 1997         | 6                |\n",
       "+--------------+------------------+\n",
       "| 1998         | 5                |\n",
       "+--------------+------------------+\n",
       "| 1999         | 5                |\n",
       "+--------------+------------------+\n",
       "| 2000         | 6                |\n",
       "+--------------+------------------+\n",
       "| 2001         | 5                |\n",
       "+--------------+------------------+\n",
       "| 2003         | 5                |\n",
       "+--------------+------------------+\n",
       "| 2004         | 7                |\n",
       "+--------------+------------------+\n",
       "| 2006         | 5                |\n",
       "+--------------+------------------+\n",
       "| 2009         | 6                |\n",
       "+--------------+------------------+\n",
       "| 2010         | 5                |\n",
       "+--------------+------------------+\n",
       "| 2013         | 6                |\n",
       "+--------------+------------------+\n",
       "| 2014         | 5                |\n",
       "+--------------+------------------+\n",
       "| 2015         | 5                |\n",
       "+--------------+------------------+\n",
       "| 2018         | 6                |\n",
       "+--------------+------------------+\n",
       "| 2019         | 8                |\n",
       "+--------------+------------------+\n",
       "19 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT release_year,\n",
    "       COUNT(*) AS number_of_movies\n",
    "  FROM movies\n",
    " GROUP BY release_year\n",
    "HAVING number_of_movies >= 5"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d0715180",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 隨堂練習：`movies` 資料表重複的電影 `title` 為哪一部？\n",
    "\n",
    "寫出能夠得到下列預期結果的 SQL 查詢。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "2b07330a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>title</th>\n",
       "<th>n_rows</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Drishyam</td>\n",
       "<td>2</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+----------+--------+\n",
       "| title    | n_rows |\n",
       "+----------+--------+\n",
       "| Drishyam | 2      |\n",
       "+----------+--------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "59fc205d",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>title</th>\n",
       "<th>n_rows</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Drishyam</td>\n",
       "<td>2</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+----------+--------+\n",
       "| title    | n_rows |\n",
       "+----------+--------+\n",
       "| Drishyam | 2      |\n",
       "+----------+--------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT title,\n",
    "       COUNT(*) AS n_rows\n",
    "  FROM movies\n",
    " GROUP BY title\n",
    "HAVING n_rows > 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e36bcf1d",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 子查詢"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c52d37bb",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 在「函數」的章節中，我們看過函數包含著其他函數的使用方法\n",
    "\n",
    "前一個函數的輸出，成為下一個函數的輸入。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "superb-principle",
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>avg_rating</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>8.0</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+------------+\n",
       "| avg_rating |\n",
       "+------------+\n",
       "| 8.0        |\n",
       "+------------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT ROUND(AVG(rating)) AS avg_rating \n",
    "  FROM movies;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cordless-relay",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 如果一段 SQL 中包含著另外一段的 SQL，這樣的 SQL 結構就被稱為子查詢（Subquery）"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "impressive-rochester",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 在什麼樣的情境下我們會想使用子查詢呢？\n",
    "\n",
    "1. 查詢的篩選條件必須要先做一個查詢才能得知。\n",
    "2. 查詢的計算內容必須要先做一個查詢才能得知。\n",
    "3. 查詢所需要的資料來自不同資料表。\n",
    "4. 將查詢的結果作為一個資料表使用。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "posted-acrylic",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 情境一：查詢的篩選條件必須要先做一個查詢才能得知\n",
    "\n",
    "我們想知道 `imdb` 資料庫的 `movies` 資料表中片長最短的電影是哪一部。\n",
    "\n",
    "```sql\n",
    "SELECT *\n",
    "  FROM movies\n",
    " WHERE runtime = MIN(runtime); -- 這個查詢會得到錯誤，因為聚合函數不能夠寫在 WHERE 後\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "continuous-nerve",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 我們要分兩次查詢來完成\n",
    "\n",
    "- 先查詢「最短」的片長是幾分鐘。\n",
    "- 再依據前一個查詢結果作為篩選條件。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "provincial-smith",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>minimum_runtime</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>45</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+-----------------+\n",
       "| minimum_runtime |\n",
       "+-----------------+\n",
       "| 45              |\n",
       "+-----------------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT MIN(runtime) AS minimum_runtime  -- 先查詢「最短」的片長是幾分鐘。\n",
    "  FROM movies;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "alternative-march",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>id</th>\n",
       "<th>title</th>\n",
       "<th>release_year</th>\n",
       "<th>rating</th>\n",
       "<th>director</th>\n",
       "<th>runtime</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>195</td>\n",
       "<td>Sherlock Jr.</td>\n",
       "<td>1924</td>\n",
       "<td>8.2</td>\n",
       "<td>Buster Keaton</td>\n",
       "<td>45</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+-----+--------------+--------------+--------+---------------+---------+\n",
       "| id  | title        | release_year | rating | director      | runtime |\n",
       "+-----+--------------+--------------+--------+---------------+---------+\n",
       "| 195 | Sherlock Jr. | 1924         | 8.2    | Buster Keaton | 45      |\n",
       "+-----+--------------+--------------+--------+---------------+---------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT *\n",
    "  FROM movies\n",
    " WHERE runtime = 45;  -- 再依據前一個查詢結果作為篩選條件。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "induced-cherry",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 面對情境一，我們可以應用子查詢將一段 SQL 查詢結果作為條件\n",
    "\n",
    "```sql\n",
    "SELECT column_names\n",
    "  FROM table_name\n",
    " WHERE conditions (Another SELECT statement);\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "mighty-helena",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    ";\n",
    "\n",
    "SELECT *\n",
    "  FROM movies\n",
    " WHERE runtime = (SELECT MIN(runtime) AS minimum_runtime  -- 先查詢「最短」的片長是幾分鐘。\n",
    "  FROM movies);  -- 再依據前一個查詢結果作為篩選條件。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "agricultural-presentation",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 情境二：查詢的計算內容必須要先做一個查詢才能得知\n",
    "\n",
    "我們想知道 `imdb` 資料庫的 `movies` 資料表中，在千禧年之後上映的電影佔比為多少？"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "complimentary-polyester",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 我們要分兩次查詢來完成\n",
    "\n",
    "- 先查詢在千禧年之後上映的電影有幾部。\n",
    "- 再依據前一個查詢結果作為計算內容。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "changing-queen",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>number_of_movies</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>101</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+------------------+\n",
       "| number_of_movies |\n",
       "+------------------+\n",
       "| 101              |\n",
       "+------------------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT COUNT(*) AS number_of_movies\n",
    "  FROM movies\n",
    " WHERE release_year >= 2000;  -- 先查詢在千禧年之後上映的電影有幾部。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "isolated-madrid",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>millennium_percentage</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>0.404</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+-----------------------+\n",
       "| millennium_percentage |\n",
       "+-----------------------+\n",
       "| 0.404                 |\n",
       "+-----------------------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT 101 / CAST(COUNT(*) AS REAL) AS millennium_percentage  -- 再依據前一個查詢結果作為計算內容。\n",
    "  FROM movies;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "warming-place",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 面對情境二，我們可以應用子查詢將一段 SQL 查詢結果作為計算內容\n",
    "\n",
    "```sql\n",
    "SELECT (Another SELECT statement) AS alias\n",
    "  FROM table_name;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "renewable-lithuania",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    ";  -- 先查詢在千禧年之後上映的電影有幾部。\n",
    "\n",
    "SELECT (SELECT COUNT(*) AS number_of_movies\n",
    "  FROM movies\n",
    " WHERE release_year >= 2000) / CAST(COUNT(*) AS REAL) AS millennium_percentage  -- 再依據前一個查詢結果作為計算內容。\n",
    "  FROM movies;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fancy-kazakhstan",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 情境三：查詢所需要的資料來自不同的資料表\n",
    "\n",
    "我們想知道在 `imdb` 資料庫中，哪幾部電影 Tom Hanks 有演出？"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "governing-roberts",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 我們要分三次查詢來完成\n",
    "\n",
    "- 先從 `actors` 資料表查詢 Tom Hanks 的演員編號是多少。\n",
    "- 再依據前一個查詢結果去 `casting` 資料表查詢。\n",
    "- 再依據前一個查詢結果去 `movies` 資料表查詢。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "continuing-potter",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>id</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2865</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+------+\n",
       "| id   |\n",
       "+------+\n",
       "| 2865 |\n",
       "+------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT id\n",
    "  FROM actors\n",
    " WHERE name = 'Tom Hanks';  -- 先從 actors 資料表查詢 Tom Hanks 的演員編號是多少。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "asian-vector",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>movie_id</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>12</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>26</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>27</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>82</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>112</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>189</td>\n",
       "</tr>\n",
       "</table>\n",
       "6 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+----------+\n",
       "| movie_id |\n",
       "+----------+\n",
       "| 12       |\n",
       "+----------+\n",
       "| 26       |\n",
       "+----------+\n",
       "| 27       |\n",
       "+----------+\n",
       "| 82       |\n",
       "+----------+\n",
       "| 112      |\n",
       "+----------+\n",
       "| 189      |\n",
       "+----------+\n",
       "6 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT movie_id\n",
    "  FROM casting\n",
    " WHERE actor_id = 2865;  -- 再依據前一個查詢結果去 casting 資料表查詢。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "cordless-polls",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>id</th>\n",
       "<th>title</th>\n",
       "<th>release_year</th>\n",
       "<th>rating</th>\n",
       "<th>director</th>\n",
       "<th>runtime</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>12</td>\n",
       "<td>Forrest Gump</td>\n",
       "<td>1994</td>\n",
       "<td>8.8</td>\n",
       "<td>Robert Zemeckis</td>\n",
       "<td>142</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>26</td>\n",
       "<td>Saving Private Ryan</td>\n",
       "<td>1998</td>\n",
       "<td>8.6</td>\n",
       "<td>Steven Spielberg</td>\n",
       "<td>169</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>27</td>\n",
       "<td>The Green Mile</td>\n",
       "<td>1999</td>\n",
       "<td>8.6</td>\n",
       "<td>Frank Darabont</td>\n",
       "<td>189</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>82</td>\n",
       "<td>Toy Story</td>\n",
       "<td>1995</td>\n",
       "<td>8.3</td>\n",
       "<td>John Lasseter</td>\n",
       "<td>81</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>112</td>\n",
       "<td>Toy Story 3</td>\n",
       "<td>2010</td>\n",
       "<td>8.2</td>\n",
       "<td>Lee Unkrich</td>\n",
       "<td>103</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>189</td>\n",
       "<td>Catch Me If You Can</td>\n",
       "<td>2002</td>\n",
       "<td>8.1</td>\n",
       "<td>Steven Spielberg</td>\n",
       "<td>141</td>\n",
       "</tr>\n",
       "</table>\n",
       "6 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+-----+---------------------+--------------+--------+------------------+---------+\n",
       "| id  | title               | release_year | rating | director         | runtime |\n",
       "+-----+---------------------+--------------+--------+------------------+---------+\n",
       "| 12  | Forrest Gump        | 1994         | 8.8    | Robert Zemeckis  | 142     |\n",
       "+-----+---------------------+--------------+--------+------------------+---------+\n",
       "| 26  | Saving Private Ryan | 1998         | 8.6    | Steven Spielberg | 169     |\n",
       "+-----+---------------------+--------------+--------+------------------+---------+\n",
       "| 27  | The Green Mile      | 1999         | 8.6    | Frank Darabont   | 189     |\n",
       "+-----+---------------------+--------------+--------+------------------+---------+\n",
       "| 82  | Toy Story           | 1995         | 8.3    | John Lasseter    | 81      |\n",
       "+-----+---------------------+--------------+--------+------------------+---------+\n",
       "| 112 | Toy Story 3         | 2010         | 8.2    | Lee Unkrich      | 103     |\n",
       "+-----+---------------------+--------------+--------+------------------+---------+\n",
       "| 189 | Catch Me If You Can | 2002         | 8.1    | Steven Spielberg | 141     |\n",
       "+-----+---------------------+--------------+--------+------------------+---------+\n",
       "6 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT *\n",
    "  FROM movies\n",
    " WHERE id IN (12, 26, 27, 82, 112, 189);  -- 再依據前一個查詢結果去 movies 資料表查詢。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "educated-calcium",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 面對情境三，我們可以應用子查詢將一段 SQL 查詢結果作為條件\n",
    "\n",
    "```sql\n",
    "SELECT column_names\n",
    "  FROM table_name\n",
    " WHERE conditions (Another SELECT statement);\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "sharp-drinking",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    ";  -- 先從 actors 資料表查詢 Tom Hanks 的演員編號是多少。\n",
    " \n",
    ";  -- 再依據前一個查詢結果去 casting 資料表查詢。\n",
    " \n",
    "SELECT *\n",
    "  FROM movies\n",
    " WHERE id IN (SELECT movie_id\n",
    "  FROM casting\n",
    " WHERE actor_id = (SELECT id\n",
    "  FROM actors\n",
    " WHERE name = 'Tom Hanks'));  -- 再依據前一個查詢結果去 movies 資料表查詢。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "auburn-accused",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 面對情境四，應用子查詢將一段 SQL 查詢結果作為資料表\n",
    "\n",
    "```sql\n",
    "SELECT column_names\n",
    "  FROM (SELECT column_names FROM table_name) AS alias;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "hazardous-mattress",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>release_year</th>\n",
       "<th>max_rating</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1957</td>\n",
       "<td>9.0</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1966</td>\n",
       "<td>8.8</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1972</td>\n",
       "<td>9.2</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1974</td>\n",
       "<td>9.0</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1993</td>\n",
       "<td>8.9</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1994</td>\n",
       "<td>9.3</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1999</td>\n",
       "<td>8.8</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2001</td>\n",
       "<td>8.8</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2003</td>\n",
       "<td>8.9</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2008</td>\n",
       "<td>9.0</td>\n",
       "</tr>\n",
       "</table>\n",
       "10 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+--------------+------------+\n",
       "| release_year | max_rating |\n",
       "+--------------+------------+\n",
       "| 1957         | 9.0        |\n",
       "+--------------+------------+\n",
       "| 1966         | 8.8        |\n",
       "+--------------+------------+\n",
       "| 1972         | 9.2        |\n",
       "+--------------+------------+\n",
       "| 1974         | 9.0        |\n",
       "+--------------+------------+\n",
       "| 1993         | 8.9        |\n",
       "+--------------+------------+\n",
       "| 1994         | 9.3        |\n",
       "+--------------+------------+\n",
       "| 1999         | 8.8        |\n",
       "+--------------+------------+\n",
       "| 2001         | 8.8        |\n",
       "+--------------+------------+\n",
       "| 2003         | 8.9        |\n",
       "+--------------+------------+\n",
       "| 2008         | 9.0        |\n",
       "+--------------+------------+\n",
       "10 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT *\n",
    "  FROM (SELECT release_year,\n",
    "               MAX(rating) AS max_rating\n",
    "          FROM movies\n",
    "         GROUP BY release_year) AS max_rating_each_year\n",
    " WHERE max_rating > 8.7\n",
    " LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1715e23f",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 隨堂練習：`movies` 資料表出現最多次的導演為誰？\n",
    "\n",
    "寫出能夠得到下列預期結果的 SQL 查詢。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "fe5e054f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>director</th>\n",
       "<th>counts</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Christopher Nolan</td>\n",
       "<td>7</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Martin Scorsese</td>\n",
       "<td>7</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Stanley Kubrick</td>\n",
       "<td>7</td>\n",
       "</tr>\n",
       "</table>\n",
       "3 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+-------------------+--------+\n",
       "| director          | counts |\n",
       "+-------------------+--------+\n",
       "| Christopher Nolan | 7      |\n",
       "+-------------------+--------+\n",
       "| Martin Scorsese   | 7      |\n",
       "+-------------------+--------+\n",
       "| Stanley Kubrick   | 7      |\n",
       "+-------------------+--------+\n",
       "3 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "c6b54081",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>director</th>\n",
       "<th>counts</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Christopher Nolan</td>\n",
       "<td>7</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Martin Scorsese</td>\n",
       "<td>7</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Stanley Kubrick</td>\n",
       "<td>7</td>\n",
       "</tr>\n",
       "</table>\n",
       "3 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+-------------------+--------+\n",
       "| director          | counts |\n",
       "+-------------------+--------+\n",
       "| Christopher Nolan | 7      |\n",
       "+-------------------+--------+\n",
       "| Martin Scorsese   | 7      |\n",
       "+-------------------+--------+\n",
       "| Stanley Kubrick   | 7      |\n",
       "+-------------------+--------+\n",
       "3 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT director,\n",
    "       counts\n",
    "  FROM (SELECT director,\n",
    "               COUNT(*) AS counts\n",
    "          FROM movies\n",
    "         GROUP BY director) AS director_counts\n",
    " WHERE director_counts.counts = (SELECT MAX(counts) AS max_counts\n",
    "                                   FROM (SELECT director,\n",
    "                                                COUNT(*) AS counts\n",
    "                                           FROM movies\n",
    "                                          GROUP BY director) AS director_counts);"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "forward-passage",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 關聯資料表"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "guided-credit",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 在「子查詢 」章節中，我們知道子查詢常見的應用情境有：\n",
    "\n",
    "1. 查詢的篩選條件必須要先做一個查詢才能得知。\n",
    "2. 查詢的計算內容必須要先做一個查詢才能得知。\n",
    "3. 查詢所需要的**資料來自不同資料表**。\n",
    "4. 將查詢的結果作為一個資料表使用。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "decimal-newsletter",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 這意味著我們的查詢範圍開始由「單個」資料表擴展至「多個」資料表\n",
    "\n",
    "- 子查詢。\n",
    "- 關聯資料表。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "smaller-transaction",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 關聯式資料庫（Relational Database）的核心精神\n",
    "\n",
    "- 可以設計資料表彼此之間的「關聯」特性。\n",
    "- 讓資料表彼此之間的「重複性」降低。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "previous-gasoline",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 資料表能夠從兩個維度關聯\n",
    "\n",
    "- 垂直：讓資料表以垂直方向對觀測值（列）進行集合運算。\n",
    "- 水平：讓資料表以水平方向對變數（欄）進行集合運算。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "impaired-section",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 以 `UNION` 垂直關聯\n",
    "\n",
    "```sql\n",
    "A SELECT statement\n",
    "UNION\n",
    "Another SELECT statement\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "brief-castle",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 垂直關聯簡單來說，就是結合觀測值\n",
    "\n",
    "![union](images/union.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "subjective-therapist",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>my_favorites</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Christopher Nolan</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Leonardo DiCaprio</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Steven Spielberg</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Tom Hanks</td>\n",
       "</tr>\n",
       "</table>\n",
       "4 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+-------------------+\n",
       "| my_favorites      |\n",
       "+-------------------+\n",
       "| Christopher Nolan |\n",
       "+-------------------+\n",
       "| Leonardo DiCaprio |\n",
       "+-------------------+\n",
       "| Steven Spielberg  |\n",
       "+-------------------+\n",
       "| Tom Hanks         |\n",
       "+-------------------+\n",
       "4 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "    SELECT director AS my_favorites\n",
    "      FROM movies\n",
    "     WHERE director IN ('Christopher Nolan', 'Steven Spielberg')\n",
    " UNION\n",
    "SELECT name\n",
    "  FROM actors\n",
    " WHERE name IN ('Tom Hanks', 'Leonardo DiCaprio');"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "sound-chassis",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 使用 `UNION` 的注意事項\n",
    "\n",
    "- 垂直關聯的欄位數要相同。\n",
    "- 垂直關聯的 SQL 若有使用到 `ORDER BY` 只能放在 `UNION` 之後。\n",
    "- 垂直關聯的重複觀測值會被省略。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "legendary-learning",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 垂直關聯的欄位數要相同\n",
    "\n",
    "```sql\n",
    "SELECT director AS my_favorites\n",
    "  FROM movies\n",
    " WHERE director IN ('Christopher Nolan', 'Steven Spielberg')\n",
    " UNION\n",
    "SELECT name,\n",
    "       id  -- 垂直關聯的欄位數不相同\n",
    "  FROM actors\n",
    " WHERE name IN ('Tom Hanks', 'Leonardo DiCaprio');\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mighty-survival",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 垂直關聯的 SQL 若有使用到 `ORDER BY` 只能放在 `UNION` 之後\n",
    "\n",
    "```sql\n",
    "SELECT director AS my_favorites\n",
    "  FROM movies\n",
    " WHERE director IN ('Christopher Nolan', 'Steven Spielberg')\n",
    " ORDER BY my_favorites\n",
    " UNION\n",
    "SELECT name\n",
    "  FROM actors\n",
    " WHERE name IN ('Tom Hanks', 'Leonardo DiCaprio');\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "regulation-remainder",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 垂直關聯的重複觀測值會被省略"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "comprehensive-classification",
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>my_favorites</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Christopher Nolan</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Steven Spielberg</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Christopher Nolan</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Steven Spielberg</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Christopher Nolan</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Christopher Nolan</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Christopher Nolan</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Steven Spielberg</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Christopher Nolan</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Steven Spielberg</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Christopher Nolan</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Steven Spielberg</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Steven Spielberg</td>\n",
       "</tr>\n",
       "</table>\n",
       "13 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+-------------------+\n",
       "| my_favorites      |\n",
       "+-------------------+\n",
       "| Christopher Nolan |\n",
       "+-------------------+\n",
       "| Steven Spielberg  |\n",
       "+-------------------+\n",
       "| Christopher Nolan |\n",
       "+-------------------+\n",
       "| Steven Spielberg  |\n",
       "+-------------------+\n",
       "| Christopher Nolan |\n",
       "+-------------------+\n",
       "| Christopher Nolan |\n",
       "+-------------------+\n",
       "| Christopher Nolan |\n",
       "+-------------------+\n",
       "| Steven Spielberg  |\n",
       "+-------------------+\n",
       "| Christopher Nolan |\n",
       "+-------------------+\n",
       "| Steven Spielberg  |\n",
       "+-------------------+\n",
       "| Christopher Nolan |\n",
       "+-------------------+\n",
       "| Steven Spielberg  |\n",
       "+-------------------+\n",
       "| Steven Spielberg  |\n",
       "+-------------------+\n",
       "13 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT director AS my_favorites\n",
    "  FROM movies\n",
    " WHERE director IN ('Christopher Nolan', 'Steven Spielberg');\n",
    "SELECT name\n",
    "  FROM actors\n",
    " WHERE name IN ('Tom Hanks', 'Leonardo DiCaprio')\n",
    " ORDER BY my_favorites;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "understood-match",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 若希望保留重複觀測值，改使用 `UNION ALL`\n",
    "\n",
    "```sql\n",
    "A SELECT statement\n",
    "UNION ALL\n",
    "Another SELECT statement\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "technical-package",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>my_favorites</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Christopher Nolan</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Christopher Nolan</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Christopher Nolan</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Christopher Nolan</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Christopher Nolan</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Christopher Nolan</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Christopher Nolan</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Leonardo DiCaprio</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Steven Spielberg</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Steven Spielberg</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Steven Spielberg</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Steven Spielberg</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Steven Spielberg</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Steven Spielberg</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Tom Hanks</td>\n",
       "</tr>\n",
       "</table>\n",
       "15 rows in set (0.01 sec)"
      ],
      "text/plain": [
       "+-------------------+\n",
       "| my_favorites      |\n",
       "+-------------------+\n",
       "| Christopher Nolan |\n",
       "+-------------------+\n",
       "| Christopher Nolan |\n",
       "+-------------------+\n",
       "| Christopher Nolan |\n",
       "+-------------------+\n",
       "| Christopher Nolan |\n",
       "+-------------------+\n",
       "| Christopher Nolan |\n",
       "+-------------------+\n",
       "| Christopher Nolan |\n",
       "+-------------------+\n",
       "| Christopher Nolan |\n",
       "+-------------------+\n",
       "| Leonardo DiCaprio |\n",
       "+-------------------+\n",
       "| Steven Spielberg  |\n",
       "+-------------------+\n",
       "| Steven Spielberg  |\n",
       "+-------------------+\n",
       "| Steven Spielberg  |\n",
       "+-------------------+\n",
       "| Steven Spielberg  |\n",
       "+-------------------+\n",
       "| Steven Spielberg  |\n",
       "+-------------------+\n",
       "| Steven Spielberg  |\n",
       "+-------------------+\n",
       "| Tom Hanks         |\n",
       "+-------------------+\n",
       "15 rows in set (0.01 sec)"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT director AS my_favorites\n",
    "  FROM movies\n",
    " WHERE director IN ('Christopher Nolan', 'Steven Spielberg')\n",
    " UNION ALL\n",
    "SELECT name\n",
    "  FROM actors\n",
    " WHERE name IN ('Tom Hanks', 'Leonardo DiCaprio')\n",
    " ORDER BY my_favorites;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "tough-appreciation",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 學習資料庫的實體關係圖（ER Diagram, Entity Relationship Diagram） \n",
    "\n",
    "- 體會關聯式資料庫的核心精神。\n",
    "- 暸解資料表之間透過如同「橋樑」般存在的**結合鍵**（Join Key）水平關聯。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bigger-interstate",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## `imdb` 資料庫的實體關係圖\n",
    "\n",
    "![erd_imdb](images/imdb_erd.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "superior-dynamics",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 以 `JOIN` 水平關聯\n",
    "\n",
    "因為是「水平」關聯，在 `FROM` 後的資料表被稱為「左表格」、`JOIN` 後的資料表被稱為「右表格」。\n",
    "\n",
    "```sql\n",
    "SELECT left_table.column_names,\n",
    "       right_table.column_names\n",
    "  FROM left_table\n",
    "  JOIN right_table\n",
    "    ON left_table.join_key = right_table.join_key;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "afraid-cuisine",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 水平關聯簡單來說，就是結合變數\n",
    "\n",
    "![join](images/join.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "gentle-watts",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 以 `JOIN` 水平關聯 `movies` 與 `casting`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "raising-cigarette",
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>title</th>\n",
       "<th>actor_id</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>2853</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>2097</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>333</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>3029</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>533</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1042</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1923</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1319</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1370</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1724</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>2151</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>363</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>659</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1530</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1553</td>\n",
       "</tr>\n",
       "</table>\n",
       "15 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+--------------------------+----------+\n",
       "| title                    | actor_id |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 2853     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 2097     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 333      |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 3029     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 533      |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1042     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1923     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1319     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1370     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1724     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 2151     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 363      |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 659      |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1530     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1553     |\n",
       "+--------------------------+----------+\n",
       "15 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT movies.title,\n",
    "       casting.actor_id\n",
    "  FROM movies  -- 左表格\n",
    "  JOIN casting -- 右表格\n",
    "    ON movies.id = casting.movie_id\n",
    " WHERE movies.title = 'The Shawshank Redemption';"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "included-fiction",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 以 `JOIN` 水平關聯 `movies`、`casting` 與 `actors`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "dried-aspect",
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>title</th>\n",
       "<th>ord</th>\n",
       "<th>name</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1</td>\n",
       "<td>Tim Robbins</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>2</td>\n",
       "<td>Morgan Freeman</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>3</td>\n",
       "<td>Bob Gunton</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>4</td>\n",
       "<td>William Sadler</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>5</td>\n",
       "<td>Clancy Brown</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>6</td>\n",
       "<td>Gil Bellows</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>7</td>\n",
       "<td>Mark Rolston</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>8</td>\n",
       "<td>James Whitmore</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>9</td>\n",
       "<td>Jeffrey DeMunn</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>10</td>\n",
       "<td>Larry Brandenburg</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>11</td>\n",
       "<td>Neil Giuntoli</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>12</td>\n",
       "<td>Brian Libby</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>13</td>\n",
       "<td>David Proval</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>14</td>\n",
       "<td>Joseph Ragno</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>15</td>\n",
       "<td>Jude Ciccolella</td>\n",
       "</tr>\n",
       "</table>\n",
       "15 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+--------------------------+-----+-------------------+\n",
       "| title                    | ord | name              |\n",
       "+--------------------------+-----+-------------------+\n",
       "| The Shawshank Redemption | 1   | Tim Robbins       |\n",
       "+--------------------------+-----+-------------------+\n",
       "| The Shawshank Redemption | 2   | Morgan Freeman    |\n",
       "+--------------------------+-----+-------------------+\n",
       "| The Shawshank Redemption | 3   | Bob Gunton        |\n",
       "+--------------------------+-----+-------------------+\n",
       "| The Shawshank Redemption | 4   | William Sadler    |\n",
       "+--------------------------+-----+-------------------+\n",
       "| The Shawshank Redemption | 5   | Clancy Brown      |\n",
       "+--------------------------+-----+-------------------+\n",
       "| The Shawshank Redemption | 6   | Gil Bellows       |\n",
       "+--------------------------+-----+-------------------+\n",
       "| The Shawshank Redemption | 7   | Mark Rolston      |\n",
       "+--------------------------+-----+-------------------+\n",
       "| The Shawshank Redemption | 8   | James Whitmore    |\n",
       "+--------------------------+-----+-------------------+\n",
       "| The Shawshank Redemption | 9   | Jeffrey DeMunn    |\n",
       "+--------------------------+-----+-------------------+\n",
       "| The Shawshank Redemption | 10  | Larry Brandenburg |\n",
       "+--------------------------+-----+-------------------+\n",
       "| The Shawshank Redemption | 11  | Neil Giuntoli     |\n",
       "+--------------------------+-----+-------------------+\n",
       "| The Shawshank Redemption | 12  | Brian Libby       |\n",
       "+--------------------------+-----+-------------------+\n",
       "| The Shawshank Redemption | 13  | David Proval      |\n",
       "+--------------------------+-----+-------------------+\n",
       "| The Shawshank Redemption | 14  | Joseph Ragno      |\n",
       "+--------------------------+-----+-------------------+\n",
       "| The Shawshank Redemption | 15  | Jude Ciccolella   |\n",
       "+--------------------------+-----+-------------------+\n",
       "15 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT movies.title,\n",
    "       casting.ord,\n",
    "       actors.name\n",
    "  FROM movies   -- 左表格\n",
    "  JOIN casting  -- 右一表格\n",
    "    ON movies.id = casting.movie_id\n",
    "  JOIN actors   -- 右二表格\n",
    "    ON casting.actor_id = actors.id\n",
    " WHERE movies.title = 'The Shawshank Redemption';"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "balanced-wound",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 使用 `JOIN` 水平關聯資料表時的注意事項\n",
    "\n",
    "- 養成為水平關聯的資料表取別名並在欄位名稱前註明清楚的好習慣。\n",
    "- 除了實體資料表亦可使用子查詢生成的結果作為關聯來源。\n",
    "- 預設保留左表格與右表格**交集**的觀測值。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "48cad54b",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 養成為水平關聯的資料表取別名並在欄位名稱前註明清楚的好習慣\n",
    "\n",
    "舉例來說，在 `movies` 與 `actors` 兩個資料表中都有 `id` 欄位。\n",
    "\n",
    "- `movies.id` 指的是電影編號。\n",
    "- `actors.id` 指的是演員編號。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "center-block",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 如果沒有註明 id 來自哪個資料表會產生錯誤\n",
    "\n",
    "```sql\n",
    "SELECT title,\n",
    "       ord,\n",
    "       name\n",
    "  FROM movies\n",
    "  JOIN casting\n",
    "    ON id = movie_id\n",
    "  JOIN actors\n",
    "    ON actor_id = id\n",
    " WHERE title = 'The Shawshank Redemption';\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "solved-graduation",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 除了實體資料表亦可使用子查詢生成的結果作為關聯來源"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "average-shopper",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>title</th>\n",
       "<th>ord</th>\n",
       "<th>name</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1</td>\n",
       "<td>Tim Robbins</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Forrest Gump</td>\n",
       "<td>1</td>\n",
       "<td>Tom Hanks</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>2</td>\n",
       "<td>Morgan Freeman</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Forrest Gump</td>\n",
       "<td>2</td>\n",
       "<td>Rebecca Williams</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>3</td>\n",
       "<td>Bob Gunton</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Forrest Gump</td>\n",
       "<td>3</td>\n",
       "<td>Sally Field</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>4</td>\n",
       "<td>William Sadler</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Forrest Gump</td>\n",
       "<td>4</td>\n",
       "<td>Michael Conner Humphreys</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>5</td>\n",
       "<td>Clancy Brown</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Forrest Gump</td>\n",
       "<td>5</td>\n",
       "<td>Harold G. Herthum</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>6</td>\n",
       "<td>Gil Bellows</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Forrest Gump</td>\n",
       "<td>6</td>\n",
       "<td>George Kelly</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>7</td>\n",
       "<td>Mark Rolston</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Forrest Gump</td>\n",
       "<td>7</td>\n",
       "<td>Bob Penny</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>8</td>\n",
       "<td>James Whitmore</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Forrest Gump</td>\n",
       "<td>8</td>\n",
       "<td>John Randall</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>9</td>\n",
       "<td>Jeffrey DeMunn</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Forrest Gump</td>\n",
       "<td>9</td>\n",
       "<td>Sam Anderson</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>10</td>\n",
       "<td>Larry Brandenburg</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Forrest Gump</td>\n",
       "<td>10</td>\n",
       "<td>Margo Moorer</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>11</td>\n",
       "<td>Neil Giuntoli</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Forrest Gump</td>\n",
       "<td>11</td>\n",
       "<td>Ione M. Telech</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>12</td>\n",
       "<td>Brian Libby</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Forrest Gump</td>\n",
       "<td>12</td>\n",
       "<td>Christine Seabrook</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>13</td>\n",
       "<td>David Proval</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Forrest Gump</td>\n",
       "<td>13</td>\n",
       "<td>John Worsham</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>14</td>\n",
       "<td>Joseph Ragno</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Forrest Gump</td>\n",
       "<td>14</td>\n",
       "<td>Peter Dobson</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>15</td>\n",
       "<td>Jude Ciccolella</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Forrest Gump</td>\n",
       "<td>15</td>\n",
       "<td>Siobhan Fallon Hogan</td>\n",
       "</tr>\n",
       "</table>\n",
       "30 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+--------------------------+-----+--------------------------+\n",
       "| title                    | ord | name                     |\n",
       "+--------------------------+-----+--------------------------+\n",
       "| The Shawshank Redemption | 1   | Tim Robbins              |\n",
       "+--------------------------+-----+--------------------------+\n",
       "| Forrest Gump             | 1   | Tom Hanks                |\n",
       "+--------------------------+-----+--------------------------+\n",
       "| The Shawshank Redemption | 2   | Morgan Freeman           |\n",
       "+--------------------------+-----+--------------------------+\n",
       "| Forrest Gump             | 2   | Rebecca Williams         |\n",
       "+--------------------------+-----+--------------------------+\n",
       "| The Shawshank Redemption | 3   | Bob Gunton               |\n",
       "+--------------------------+-----+--------------------------+\n",
       "| Forrest Gump             | 3   | Sally Field              |\n",
       "+--------------------------+-----+--------------------------+\n",
       "| The Shawshank Redemption | 4   | William Sadler           |\n",
       "+--------------------------+-----+--------------------------+\n",
       "| Forrest Gump             | 4   | Michael Conner Humphreys |\n",
       "+--------------------------+-----+--------------------------+\n",
       "| The Shawshank Redemption | 5   | Clancy Brown             |\n",
       "+--------------------------+-----+--------------------------+\n",
       "| Forrest Gump             | 5   | Harold G. Herthum        |\n",
       "+--------------------------+-----+--------------------------+\n",
       "| The Shawshank Redemption | 6   | Gil Bellows              |\n",
       "+--------------------------+-----+--------------------------+\n",
       "| Forrest Gump             | 6   | George Kelly             |\n",
       "+--------------------------+-----+--------------------------+\n",
       "| The Shawshank Redemption | 7   | Mark Rolston             |\n",
       "+--------------------------+-----+--------------------------+\n",
       "| Forrest Gump             | 7   | Bob Penny                |\n",
       "+--------------------------+-----+--------------------------+\n",
       "| The Shawshank Redemption | 8   | James Whitmore           |\n",
       "+--------------------------+-----+--------------------------+\n",
       "| Forrest Gump             | 8   | John Randall             |\n",
       "+--------------------------+-----+--------------------------+\n",
       "| The Shawshank Redemption | 9   | Jeffrey DeMunn           |\n",
       "+--------------------------+-----+--------------------------+\n",
       "| Forrest Gump             | 9   | Sam Anderson             |\n",
       "+--------------------------+-----+--------------------------+\n",
       "| The Shawshank Redemption | 10  | Larry Brandenburg        |\n",
       "+--------------------------+-----+--------------------------+\n",
       "| Forrest Gump             | 10  | Margo Moorer             |\n",
       "+--------------------------+-----+--------------------------+\n",
       "| The Shawshank Redemption | 11  | Neil Giuntoli            |\n",
       "+--------------------------+-----+--------------------------+\n",
       "| Forrest Gump             | 11  | Ione M. Telech           |\n",
       "+--------------------------+-----+--------------------------+\n",
       "| The Shawshank Redemption | 12  | Brian Libby              |\n",
       "+--------------------------+-----+--------------------------+\n",
       "| Forrest Gump             | 12  | Christine Seabrook       |\n",
       "+--------------------------+-----+--------------------------+\n",
       "| The Shawshank Redemption | 13  | David Proval             |\n",
       "+--------------------------+-----+--------------------------+\n",
       "| Forrest Gump             | 13  | John Worsham             |\n",
       "+--------------------------+-----+--------------------------+\n",
       "| The Shawshank Redemption | 14  | Joseph Ragno             |\n",
       "+--------------------------+-----+--------------------------+\n",
       "| Forrest Gump             | 14  | Peter Dobson             |\n",
       "+--------------------------+-----+--------------------------+\n",
       "| The Shawshank Redemption | 15  | Jude Ciccolella          |\n",
       "+--------------------------+-----+--------------------------+\n",
       "| Forrest Gump             | 15  | Siobhan Fallon Hogan     |\n",
       "+--------------------------+-----+--------------------------+\n",
       "30 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT subquery_movies.title,\n",
    "       subquery_casting.ord,\n",
    "       actors.name\n",
    "  FROM (SELECT id,\n",
    "               title,\n",
    "               director\n",
    "          FROM movies\n",
    "         WHERE title IN ('The Shawshank Redemption', 'Forrest Gump')) AS subquery_movies -- 左表格\n",
    "  JOIN (SELECT *\n",
    "          FROM casting\n",
    "         WHERE movie_id IN (1, 12)) AS subquery_casting  -- 右一表格\n",
    "    ON subquery_movies.id = subquery_casting.movie_id\n",
    "  JOIN actors   -- 右二表格\n",
    "    ON subquery_casting.actor_id = actors.id\n",
    " ORDER BY subquery_casting.ord;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "tracked-pregnancy",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 預設保留左表格與右表格交集的觀測值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "leading-fever",
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>title</th>\n",
       "<th>actor_id</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>2853</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>2097</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>333</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>3029</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>533</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1042</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1923</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1319</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1370</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1724</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>2151</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>363</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>659</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1530</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1553</td>\n",
       "</tr>\n",
       "</table>\n",
       "15 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+--------------------------+----------+\n",
       "| title                    | actor_id |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 2853     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 2097     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 333      |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 3029     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 533      |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1042     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1923     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1319     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1370     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1724     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 2151     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 363      |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 659      |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1530     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1553     |\n",
       "+--------------------------+----------+\n",
       "15 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT two_movies.title,\n",
    "       two_castings.actor_id\n",
    "  -- 左表格是刺激1995與阿甘正傳的電影資料\n",
    "  FROM (SELECT *\n",
    "          FROM movies\n",
    "         WHERE title IN ('The Shawshank Redemption', 'Forrest Gump')) AS two_movies\n",
    "  -- 右表格是刺激1995與黑暗騎士的演員名單資料\n",
    "  JOIN (SELECT *\n",
    "          FROM casting\n",
    "         WHERE movie_id IN (1, 4)) AS two_castings\n",
    "    ON two_movies.id = two_castings.movie_id\n",
    " ORDER BY two_movies.title;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "characteristic-telescope",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 若希望保留以左表格為主的觀測值，改使用 `LEFT JOIN`\n",
    "\n",
    "```sql\n",
    "SELECT left_table.column_names,\n",
    "       right_table.column_names\n",
    "  FROM table_name AS left_table\n",
    "  LEFT JOIN table_name AS right_table\n",
    "    ON left_table.join_key = right_table.join_key;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "confidential-airline",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>title</th>\n",
       "<th>actor_id</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Forrest Gump</td>\n",
       "<td>NULL</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>333</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>363</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>533</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>659</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1042</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1319</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1370</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1530</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1553</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1724</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1923</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>2097</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>2151</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>2853</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>3029</td>\n",
       "</tr>\n",
       "</table>\n",
       "16 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+--------------------------+----------+\n",
       "| title                    | actor_id |\n",
       "+--------------------------+----------+\n",
       "| Forrest Gump             | NULL     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 333      |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 363      |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 533      |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 659      |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1042     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1319     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1370     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1530     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1553     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1724     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1923     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 2097     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 2151     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 2853     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 3029     |\n",
       "+--------------------------+----------+\n",
       "16 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT two_movies.title,\n",
    "       two_castings.actor_id\n",
    "  -- 左表格是刺激1995與阿甘正傳的電影資料\n",
    "  FROM (SELECT *\n",
    "          FROM movies\n",
    "         WHERE title IN ('The Shawshank Redemption', 'Forrest Gump')) AS two_movies\n",
    "  -- 右表格是刺激1995與黑暗騎士的名單資料\n",
    "  LEFT JOIN (SELECT *\n",
    "               FROM casting\n",
    "              WHERE movie_id IN (1, 4)) AS two_castings\n",
    "    ON two_movies.id = two_castings.movie_id\n",
    " ORDER BY two_movies.title;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "95ed8309",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 若希望保留以右表格為主的觀測值，互換左右表格\n",
    "\n",
    "或者在使用其他關聯式資料庫管理系統時檢查是否有 `RIGHT JOIN` 功能。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "211cb833",
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>title</th>\n",
       "<th>actor_id</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>NULL</td>\n",
       "<td>502</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>NULL</td>\n",
       "<td>1155</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>NULL</td>\n",
       "<td>2</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>NULL</td>\n",
       "<td>2015</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>NULL</td>\n",
       "<td>1855</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>NULL</td>\n",
       "<td>985</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>NULL</td>\n",
       "<td>2097</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>NULL</td>\n",
       "<td>2095</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>NULL</td>\n",
       "<td>2520</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>NULL</td>\n",
       "<td>528</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>NULL</td>\n",
       "<td>482</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>NULL</td>\n",
       "<td>2157</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>NULL</td>\n",
       "<td>838</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>NULL</td>\n",
       "<td>2458</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>NULL</td>\n",
       "<td>179</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>2853</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>2097</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>333</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>3029</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>533</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1042</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1923</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1319</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1370</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1724</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>2151</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>363</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>659</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1530</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1553</td>\n",
       "</tr>\n",
       "</table>\n",
       "30 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+--------------------------+----------+\n",
       "| title                    | actor_id |\n",
       "+--------------------------+----------+\n",
       "| NULL                     | 502      |\n",
       "+--------------------------+----------+\n",
       "| NULL                     | 1155     |\n",
       "+--------------------------+----------+\n",
       "| NULL                     | 2        |\n",
       "+--------------------------+----------+\n",
       "| NULL                     | 2015     |\n",
       "+--------------------------+----------+\n",
       "| NULL                     | 1855     |\n",
       "+--------------------------+----------+\n",
       "| NULL                     | 985      |\n",
       "+--------------------------+----------+\n",
       "| NULL                     | 2097     |\n",
       "+--------------------------+----------+\n",
       "| NULL                     | 2095     |\n",
       "+--------------------------+----------+\n",
       "| NULL                     | 2520     |\n",
       "+--------------------------+----------+\n",
       "| NULL                     | 528      |\n",
       "+--------------------------+----------+\n",
       "| NULL                     | 482      |\n",
       "+--------------------------+----------+\n",
       "| NULL                     | 2157     |\n",
       "+--------------------------+----------+\n",
       "| NULL                     | 838      |\n",
       "+--------------------------+----------+\n",
       "| NULL                     | 2458     |\n",
       "+--------------------------+----------+\n",
       "| NULL                     | 179      |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 2853     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 2097     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 333      |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 3029     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 533      |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1042     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1923     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1319     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1370     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1724     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 2151     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 363      |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 659      |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1530     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1553     |\n",
       "+--------------------------+----------+\n",
       "30 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT two_movies.title,\n",
    "       two_castings.actor_id\n",
    "  -- 左表格是刺激1995與黑暗騎士的名單資料\n",
    "  FROM (SELECT *\n",
    "               FROM casting\n",
    "              WHERE movie_id IN (1, 4)) AS two_castings\n",
    "  -- 右表格是刺激1995與阿甘正傳的電影資料\n",
    "  LEFT JOIN (SELECT *\n",
    "               FROM movies\n",
    "              WHERE title IN ('The Shawshank Redemption', 'Forrest Gump')) AS two_movies\n",
    "    ON two_castings.movie_id = two_movies.id\n",
    " ORDER BY two_movies.title;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0831da4a",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 若希望保留左右表格的觀測值，加入 `UNION`\n",
    "\n",
    "或者在使用其他關聯式資料庫管理系統時檢查是否有 `FULL JOIN` 功能。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "c2369f13",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>title</th>\n",
       "<th>actor_id</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>NULL</td>\n",
       "<td>2</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>NULL</td>\n",
       "<td>179</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>NULL</td>\n",
       "<td>482</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>NULL</td>\n",
       "<td>502</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>NULL</td>\n",
       "<td>528</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>NULL</td>\n",
       "<td>838</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>NULL</td>\n",
       "<td>985</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>NULL</td>\n",
       "<td>1155</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>NULL</td>\n",
       "<td>1855</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>NULL</td>\n",
       "<td>2015</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>NULL</td>\n",
       "<td>2095</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>NULL</td>\n",
       "<td>2097</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>NULL</td>\n",
       "<td>2157</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>NULL</td>\n",
       "<td>2458</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>NULL</td>\n",
       "<td>2520</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Forrest Gump</td>\n",
       "<td>NULL</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>333</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>363</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>533</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>659</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1042</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1319</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1370</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1530</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1553</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1724</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>1923</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>2097</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>2151</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>2853</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>The Shawshank Redemption</td>\n",
       "<td>3029</td>\n",
       "</tr>\n",
       "</table>\n",
       "31 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+--------------------------+----------+\n",
       "| title                    | actor_id |\n",
       "+--------------------------+----------+\n",
       "| NULL                     | 2        |\n",
       "+--------------------------+----------+\n",
       "| NULL                     | 179      |\n",
       "+--------------------------+----------+\n",
       "| NULL                     | 482      |\n",
       "+--------------------------+----------+\n",
       "| NULL                     | 502      |\n",
       "+--------------------------+----------+\n",
       "| NULL                     | 528      |\n",
       "+--------------------------+----------+\n",
       "| NULL                     | 838      |\n",
       "+--------------------------+----------+\n",
       "| NULL                     | 985      |\n",
       "+--------------------------+----------+\n",
       "| NULL                     | 1155     |\n",
       "+--------------------------+----------+\n",
       "| NULL                     | 1855     |\n",
       "+--------------------------+----------+\n",
       "| NULL                     | 2015     |\n",
       "+--------------------------+----------+\n",
       "| NULL                     | 2095     |\n",
       "+--------------------------+----------+\n",
       "| NULL                     | 2097     |\n",
       "+--------------------------+----------+\n",
       "| NULL                     | 2157     |\n",
       "+--------------------------+----------+\n",
       "| NULL                     | 2458     |\n",
       "+--------------------------+----------+\n",
       "| NULL                     | 2520     |\n",
       "+--------------------------+----------+\n",
       "| Forrest Gump             | NULL     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 333      |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 363      |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 533      |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 659      |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1042     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1319     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1370     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1530     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1553     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1724     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 1923     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 2097     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 2151     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 2853     |\n",
       "+--------------------------+----------+\n",
       "| The Shawshank Redemption | 3029     |\n",
       "+--------------------------+----------+\n",
       "31 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT two_movies.title,\n",
    "       two_castings.actor_id\n",
    "  -- 左表格是刺激1995與阿甘正傳的電影資料\n",
    "  FROM (SELECT *\n",
    "          FROM movies\n",
    "         WHERE title IN ('The Shawshank Redemption', 'Forrest Gump')) AS two_movies\n",
    "  -- 右表格是刺激1995與黑暗騎士的名單資料\n",
    "  LEFT JOIN (SELECT *\n",
    "               FROM casting\n",
    "              WHERE movie_id IN (1, 4)) AS two_castings\n",
    "    ON two_movies.id = two_castings.movie_id\n",
    " UNION\n",
    "SELECT two_movies.title,\n",
    "       two_castings.actor_id\n",
    "  -- 左表格是刺激1995與黑暗騎士的名單資料\n",
    "  FROM (SELECT *\n",
    "               FROM casting\n",
    "              WHERE movie_id IN (1, 4)) AS two_castings\n",
    "  -- 右表格是刺激1995與阿甘正傳的電影資料\n",
    "  LEFT JOIN (SELECT *\n",
    "               FROM movies\n",
    "              WHERE title IN ('The Shawshank Redemption', 'Forrest Gump')) AS two_movies\n",
    "    ON two_castings.movie_id = two_movies.id\n",
    " ORDER BY two_movies.title;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27f5796b",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 隨堂練習：查詢 Tom Hanks 與 Leonardo DiCaprio 演出哪些電影，依據 `casting` 資料表中的 `ord` 衍生計算欄位 `is_lead_actor` 註記是否為第一主角（`ord` 若為 1 表示為第一主角）\n",
    "\n",
    "寫出能夠得到下列預期結果的 SQL 查詢。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "faef95c8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>release_year</th>\n",
       "<th>title</th>\n",
       "<th>name</th>\n",
       "<th>is_lead_actor</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1994</td>\n",
       "<td>Forrest Gump</td>\n",
       "<td>Tom Hanks</td>\n",
       "<td>1</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1995</td>\n",
       "<td>Toy Story</td>\n",
       "<td>Tom Hanks</td>\n",
       "<td>1</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1998</td>\n",
       "<td>Saving Private Ryan</td>\n",
       "<td>Tom Hanks</td>\n",
       "<td>1</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1999</td>\n",
       "<td>The Green Mile</td>\n",
       "<td>Tom Hanks</td>\n",
       "<td>1</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2002</td>\n",
       "<td>Catch Me If You Can</td>\n",
       "<td>Leonardo DiCaprio</td>\n",
       "<td>1</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2002</td>\n",
       "<td>Catch Me If You Can</td>\n",
       "<td>Tom Hanks</td>\n",
       "<td>0</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2006</td>\n",
       "<td>The Departed</td>\n",
       "<td>Leonardo DiCaprio</td>\n",
       "<td>1</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2010</td>\n",
       "<td>Inception</td>\n",
       "<td>Leonardo DiCaprio</td>\n",
       "<td>1</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2010</td>\n",
       "<td>Toy Story 3</td>\n",
       "<td>Tom Hanks</td>\n",
       "<td>1</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2010</td>\n",
       "<td>Shutter Island</td>\n",
       "<td>Leonardo DiCaprio</td>\n",
       "<td>1</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2012</td>\n",
       "<td>Django Unchained</td>\n",
       "<td>Leonardo DiCaprio</td>\n",
       "<td>0</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2013</td>\n",
       "<td>The Wolf of Wall Street</td>\n",
       "<td>Leonardo DiCaprio</td>\n",
       "<td>1</td>\n",
       "</tr>\n",
       "</table>\n",
       "12 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+--------------+-------------------------+-------------------+---------------+\n",
       "| release_year | title                   | name              | is_lead_actor |\n",
       "+--------------+-------------------------+-------------------+---------------+\n",
       "| 1994         | Forrest Gump            | Tom Hanks         | 1             |\n",
       "+--------------+-------------------------+-------------------+---------------+\n",
       "| 1995         | Toy Story               | Tom Hanks         | 1             |\n",
       "+--------------+-------------------------+-------------------+---------------+\n",
       "| 1998         | Saving Private Ryan     | Tom Hanks         | 1             |\n",
       "+--------------+-------------------------+-------------------+---------------+\n",
       "| 1999         | The Green Mile          | Tom Hanks         | 1             |\n",
       "+--------------+-------------------------+-------------------+---------------+\n",
       "| 2002         | Catch Me If You Can     | Leonardo DiCaprio | 1             |\n",
       "+--------------+-------------------------+-------------------+---------------+\n",
       "| 2002         | Catch Me If You Can     | Tom Hanks         | 0             |\n",
       "+--------------+-------------------------+-------------------+---------------+\n",
       "| 2006         | The Departed            | Leonardo DiCaprio | 1             |\n",
       "+--------------+-------------------------+-------------------+---------------+\n",
       "| 2010         | Inception               | Leonardo DiCaprio | 1             |\n",
       "+--------------+-------------------------+-------------------+---------------+\n",
       "| 2010         | Toy Story 3             | Tom Hanks         | 1             |\n",
       "+--------------+-------------------------+-------------------+---------------+\n",
       "| 2010         | Shutter Island          | Leonardo DiCaprio | 1             |\n",
       "+--------------+-------------------------+-------------------+---------------+\n",
       "| 2012         | Django Unchained        | Leonardo DiCaprio | 0             |\n",
       "+--------------+-------------------------+-------------------+---------------+\n",
       "| 2013         | The Wolf of Wall Street | Leonardo DiCaprio | 1             |\n",
       "+--------------+-------------------------+-------------------+---------------+\n",
       "12 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "7de244fe",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>release_year</th>\n",
       "<th>title</th>\n",
       "<th>name</th>\n",
       "<th>is_lead_actor</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1994</td>\n",
       "<td>Forrest Gump</td>\n",
       "<td>Tom Hanks</td>\n",
       "<td>1</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1995</td>\n",
       "<td>Toy Story</td>\n",
       "<td>Tom Hanks</td>\n",
       "<td>1</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1998</td>\n",
       "<td>Saving Private Ryan</td>\n",
       "<td>Tom Hanks</td>\n",
       "<td>1</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1999</td>\n",
       "<td>The Green Mile</td>\n",
       "<td>Tom Hanks</td>\n",
       "<td>1</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2002</td>\n",
       "<td>Catch Me If You Can</td>\n",
       "<td>Leonardo DiCaprio</td>\n",
       "<td>1</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2002</td>\n",
       "<td>Catch Me If You Can</td>\n",
       "<td>Tom Hanks</td>\n",
       "<td>0</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2006</td>\n",
       "<td>The Departed</td>\n",
       "<td>Leonardo DiCaprio</td>\n",
       "<td>1</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2010</td>\n",
       "<td>Inception</td>\n",
       "<td>Leonardo DiCaprio</td>\n",
       "<td>1</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2010</td>\n",
       "<td>Toy Story 3</td>\n",
       "<td>Tom Hanks</td>\n",
       "<td>1</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2010</td>\n",
       "<td>Shutter Island</td>\n",
       "<td>Leonardo DiCaprio</td>\n",
       "<td>1</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2012</td>\n",
       "<td>Django Unchained</td>\n",
       "<td>Leonardo DiCaprio</td>\n",
       "<td>0</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2013</td>\n",
       "<td>The Wolf of Wall Street</td>\n",
       "<td>Leonardo DiCaprio</td>\n",
       "<td>1</td>\n",
       "</tr>\n",
       "</table>\n",
       "12 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+--------------+-------------------------+-------------------+---------------+\n",
       "| release_year | title                   | name              | is_lead_actor |\n",
       "+--------------+-------------------------+-------------------+---------------+\n",
       "| 1994         | Forrest Gump            | Tom Hanks         | 1             |\n",
       "+--------------+-------------------------+-------------------+---------------+\n",
       "| 1995         | Toy Story               | Tom Hanks         | 1             |\n",
       "+--------------+-------------------------+-------------------+---------------+\n",
       "| 1998         | Saving Private Ryan     | Tom Hanks         | 1             |\n",
       "+--------------+-------------------------+-------------------+---------------+\n",
       "| 1999         | The Green Mile          | Tom Hanks         | 1             |\n",
       "+--------------+-------------------------+-------------------+---------------+\n",
       "| 2002         | Catch Me If You Can     | Leonardo DiCaprio | 1             |\n",
       "+--------------+-------------------------+-------------------+---------------+\n",
       "| 2002         | Catch Me If You Can     | Tom Hanks         | 0             |\n",
       "+--------------+-------------------------+-------------------+---------------+\n",
       "| 2006         | The Departed            | Leonardo DiCaprio | 1             |\n",
       "+--------------+-------------------------+-------------------+---------------+\n",
       "| 2010         | Inception               | Leonardo DiCaprio | 1             |\n",
       "+--------------+-------------------------+-------------------+---------------+\n",
       "| 2010         | Toy Story 3             | Tom Hanks         | 1             |\n",
       "+--------------+-------------------------+-------------------+---------------+\n",
       "| 2010         | Shutter Island          | Leonardo DiCaprio | 1             |\n",
       "+--------------+-------------------------+-------------------+---------------+\n",
       "| 2012         | Django Unchained        | Leonardo DiCaprio | 0             |\n",
       "+--------------+-------------------------+-------------------+---------------+\n",
       "| 2013         | The Wolf of Wall Street | Leonardo DiCaprio | 1             |\n",
       "+--------------+-------------------------+-------------------+---------------+\n",
       "12 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT movies.release_year,\n",
    "       movies.title,\n",
    "       actors.name,\n",
    "       CASE WHEN casting.ord = 1 THEN 1\n",
    "            ELSE 0 END AS is_lead_actor\n",
    "  FROM actors\n",
    "  JOIN casting\n",
    "    ON actors.id = casting.actor_id\n",
    "  JOIN movies\n",
    "    ON casting.movie_id = movies.id\n",
    " WHERE actors.name IN ('Tom Hanks', 'Leonardo DiCaprio')\n",
    " ORDER BY movies.release_year;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "pacific-currency",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "/*截至目前學起來的 SQL 有哪些？\n",
    "SQL 寫作順序必須遵從標準 SQL 的規定。*/\n",
    "SELECT column_names     -- 選擇哪些欄位\n",
    "  FROM left_table       -- 從哪個資料庫的資料表\n",
    "  JOIN right_table      -- 與哪個資料表水平關聯\n",
    "    ON left_table.join_key = right_table.join_key\n",
    " WHERE conditions       -- 篩選哪些觀測值\n",
    " GROUP BY column_names  -- 指定依照哪個變數分組\n",
    "HAVING conditions       -- 篩選哪些分組聚合的結果\n",
    " UNION SELECT statement -- 與哪段 SQL 垂直關聯\n",
    " ORDER BY column_names  -- 指定依照哪個變數排序\n",
    " LIMIT m;               -- 查詢結果顯示前 m 列就好"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "kernelspec": {
   "display_name": "xsql",
   "language": "sqlite",
   "name": "xsql"
  },
  "language_info": {
   "codemirror_mode": "sql",
   "file_extension": "",
   "mimetype": "",
   "name": "mysql",
   "version": "0.1.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}