exercises-02-solutions.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "afraid-costa",
   "metadata": {
    "deletable": false
   },
   "source": [
    "# SQL 基礎\n",
    "\n",
    "> 練習題（二）\n",
    "\n",
    "[數聚點](https://www.datainpoint.com/) | 郭耀仁 <yaojenkuo@datainpoint.com>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "standard-basin",
   "metadata": {
    "deletable": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x111f1e240>"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sqlite3\n",
    "import unittest\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "conn = sqlite3.connect('KaggleSurvey2022.db')\n",
    "conn.execute(\"\"\"ATTACH 'imdb.db' AS imdb\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "closed-hierarchy",
   "metadata": {
    "deletable": false
   },
   "source": [
    "## 練習題指引\n",
    "\n",
    "- 在 SQL 語法起點與 SQL 語法終點這兩個單行註解之間撰寫能夠得到預期結果的 SQL。\n",
    "- 學習資料庫已經於第一個儲存格載入，查詢可以從兩個資料庫的選擇資料。\n",
    "- 執行測試的方式為點選上方選單的 Kernel -> Restart & Run All -> Restart and Run All Cells。\n",
    "- 可以每寫一題就執行測試，也可以全部寫完再執行測試。\n",
    "- 練習題閒置超過 10 分鐘會自動斷線，這時只要重新點選練習題連結即可重新啟動。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "following-playlist",
   "metadata": {},
   "source": [
    "## 01. 從 `KaggleSurvey2022` 資料庫 `questions` 資料表分析問題種類，參考下列的預期查詢結果。\n",
    "\n",
    "- 預期輸入：SQL 查詢語法。\n",
    "- 預期輸出：(2, 2) 的查詢結果。\n",
    "\n",
    "```\n",
    "                type  number_of_questions\n",
    "0    Multiple Choice                   19\n",
    "1  Multiple Response                   24\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "3fa000bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "summarize_question_types =\\\n",
    "\"\"\"\n",
    "-- SQL 查詢語法起點\n",
    "SELECT type,\n",
    "       COUNT(*) AS number_of_questions\n",
    "  FROM questions\n",
    " GROUP BY type;\n",
    "-- SQL 查詢語法終點\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b44f0602",
   "metadata": {},
   "source": [
    "## 02. 從 `KaggleSurvey2022` 資料庫分析受訪者日常使用的程式語言 Q12: What programming languages do you use on a regular basis? (Select all that apply)，參考下列的預期查詢結果。\n",
    "\n",
    "- 預期輸入：SQL 查詢語法。\n",
    "- 預期輸出：(15, 2) 的查詢結果。\n",
    "\n",
    "```\n",
    "        choice  number_of_respondants\n",
    "0       Python                  18653\n",
    "1          SQL                   9620\n",
    "2            R                   4571\n",
    "3          C++                   4549\n",
    "4         Java                   3862\n",
    "5            C                   3801\n",
    "6   Javascript                   3489\n",
    "7       MATLAB                   2441\n",
    "8         Bash                   1674\n",
    "9           C#                   1473\n",
    "10         PHP                   1443\n",
    "11       Other                   1342\n",
    "12          Go                    322\n",
    "13       Julia                    296\n",
    "14        None                    256\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "2c38d073",
   "metadata": {},
   "outputs": [],
   "source": [
    "summarize_programming_languages =\\\n",
    "\"\"\"\n",
    "-- SQL 查詢語法起點\n",
    "SELECT responses.choice,\n",
    "       COUNT(*) AS number_of_respondants\n",
    "  FROM responses\n",
    "  JOIN questions\n",
    "    ON responses.question_id = questions.id\n",
    " WHERE questions.id = 11\n",
    " GROUP BY responses.choice\n",
    " ORDER BY number_of_respondants DESC;\n",
    "-- SQL 查詢語法終點\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0972063a",
   "metadata": {},
   "source": [
    "## 03. 從 `KaggleSurvey2022` 資料庫分析受訪者日常使用的關聯式資料庫管理系統 Q35: Do you use any of the following data products (relational databases, data warehouses, data lakes, or similar)? (Select all that apply)，參考下列的預期查詢結果。\n",
    "\n",
    "- 預期輸入：SQL 查詢語法。\n",
    "- 預期輸出：(16, 2) 的查詢結果。\n",
    "\n",
    "```\n",
    "                           choice  number_of_respondants\n",
    "0                          MySQL                    2233\n",
    "1                     PostgreSQL                    1516\n",
    "2           Microsoft SQL Server                    1203\n",
    "3                         SQLite                    1159\n",
    "4                        MongoDB                    1031\n",
    "5                            None                    955\n",
    "6          Google Cloud BigQuery                     690\n",
    "7                Oracle Database                     688\n",
    "8   Microsoft Azure SQL Database                     520\n",
    "9                     Amazon RDS                     505\n",
    "10              Google Cloud SQL                     439\n",
    "11                     Snowflake                     399\n",
    "12               Amazon Redshift                     380\n",
    "13               Amazon DynamoDB                     356\n",
    "14                          Other                    217\n",
    "15                       IBM Db2                     192\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "da9352cc",
   "metadata": {},
   "outputs": [],
   "source": [
    "summarize_rdbms =\\\n",
    "\"\"\"\n",
    "-- SQL 查詢語法起點\n",
    "SELECT responses.choice,\n",
    "       COUNT(*) AS number_of_respondants\n",
    "  FROM responses\n",
    "  JOIN questions\n",
    "    ON responses.question_id = questions.id\n",
    " WHERE questions.id = 34\n",
    " GROUP BY responses.choice\n",
    " ORDER BY number_of_respondants DESC;\n",
    "-- SQL 查詢語法終點\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b9bc6b1b",
   "metadata": {},
   "source": [
    "## 04. 從 `KaggleSurvey2022` 資料庫分析受訪者職稱 Q23: Select the title most similar to your current role (or most recent title if retired)，參考下列的預期查詢結果。\n",
    "\n",
    "- 預期輸入：SQL 查詢語法。\n",
    "- 預期輸出：(15, 2) 的查詢結果。\n",
    "\n",
    "```\n",
    "                                               choice  number_of_respondants\n",
    "0                                      Data Scientist                   1929\n",
    "1   Data Analyst (Business, Marketing, Financial, ...                   1538\n",
    "2                              Currently not employed                   1432\n",
    "3                                   Software Engineer                    980\n",
    "4                                 Teacher / professor                    833\n",
    "5   Manager (Program, Project, Operations, Executi...                    832\n",
    "6                                               Other                    754\n",
    "7                                  Research Scientist                    593\n",
    "8                    Machine Learning/ MLops Engineer                    571\n",
    "9                             Engineer (non-software)                    465\n",
    "10                                      Data Engineer                    352\n",
    "11                                       Statistician                    125\n",
    "12                                     Data Architect                     95\n",
    "13                                 Data Administrator                     70\n",
    "14                                 Developer Advocate                     61\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "20f62737",
   "metadata": {},
   "outputs": [],
   "source": [
    "summarize_respondant_titles =\\\n",
    "\"\"\"\n",
    "-- SQL 查詢語法起點\n",
    "SELECT responses.choice,\n",
    "       COUNT(*) AS number_of_respondants\n",
    "  FROM responses\n",
    "  JOIN questions\n",
    "    ON responses.question_id = questions.id\n",
    " WHERE questions.id = 22\n",
    " GROUP BY responses.choice\n",
    " ORDER BY number_of_respondants DESC;\n",
    "-- SQL 查詢語法終點\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0f410b63",
   "metadata": {},
   "source": [
    "## 05. 接續上題，從 `KaggleSurvey2022` 資料庫分析受訪者職稱 Q23: Select the title most similar to your current role (or most recent title if retired)，改以百分比顯示，參考下列的預期查詢結果。\n",
    "\n",
    "- 預期輸入：SQL 查詢語法。\n",
    "- 預期輸出：(15, 2) 的查詢結果。\n",
    "\n",
    "```\n",
    "                                               choice percentage\n",
    "0                                      Data Scientist     18.15%\n",
    "1   Data Analyst (Business, Marketing, Financial, ...     14.47%\n",
    "2                              Currently not employed     13.47%\n",
    "3                                   Software Engineer      9.22%\n",
    "4                                 Teacher / professor      7.84%\n",
    "5   Manager (Program, Project, Operations, Executi...      7.83%\n",
    "6                                               Other      7.09%\n",
    "7                                  Research Scientist      5.58%\n",
    "8                    Machine Learning/ MLops Engineer      5.37%\n",
    "9                             Engineer (non-software)      4.37%\n",
    "10                                      Data Engineer      3.31%\n",
    "11                                       Statistician      1.18%\n",
    "12                                     Data Architect      0.89%\n",
    "13                                 Data Administrator      0.66%\n",
    "14                                 Developer Advocate      0.57%\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "8ac06106",
   "metadata": {},
   "outputs": [],
   "source": [
    "summarize_respondant_titles_in_percentage =\\\n",
    "\"\"\"\n",
    "-- SQL 查詢語法起點\n",
    "SELECT subquery_table.choice,\n",
    "       subquery_table.percentage || '%' AS percentage\n",
    "  FROM (\n",
    "           SELECT responses.choice,\n",
    "                  ROUND( (COUNT( * ) * 1.0 / (\n",
    "                                                 SELECT COUNT( * ) \n",
    "                                                   FROM responses\n",
    "                                                  WHERE responses.question_id = 22\n",
    "                                             )\n",
    "                         ) * 100, 2) AS percentage\n",
    "             FROM responses\n",
    "                  JOIN\n",
    "                  questions ON responses.question_id = questions.id\n",
    "            WHERE questions.id = 22\n",
    "            GROUP BY responses.choice\n",
    "            ORDER BY percentage DESC\n",
    "       )\n",
    "       AS subquery_table;\n",
    "-- SQL 查詢語法終點\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mobile-israel",
   "metadata": {},
   "source": [
    "## 06. 從 `imdb` 資料庫查詢「魔戒三部曲」與「蝙蝠俠三部曲」的電影資訊與演員名單，三部曲電影系列中演員重複出演的情況是正常的，這時顯示獨一值即可，參考下列的預期查詢結果。\n",
    "\n",
    "- 預期輸入：SQL 查詢語法。\n",
    "- 預期輸出：(66, 2) 的查詢結果。\n",
    "\n",
    "```\n",
    "                          trilogy                  name\n",
    "0                  Batman Trilogy         Aaron Eckhart\n",
    "1                  Batman Trilogy          Aidan Gillen\n",
    "2                  Batman Trilogy        Alon Aboutboul\n",
    "3                  Batman Trilogy         Anne Hathaway\n",
    "4                  Batman Trilogy  Anthony Michael Hall\n",
    "..                            ...                   ...\n",
    "61  The Lord of the Rings Trilogy         Sadwyn Brophy\n",
    "62  The Lord of the Rings Trilogy            Sala Baker\n",
    "63  The Lord of the Rings Trilogy            Sam Comery\n",
    "64  The Lord of the Rings Trilogy            Sean Astin\n",
    "65  The Lord of the Rings Trilogy             Sean Bean\n",
    "\n",
    "[66 rows x 2 columns]\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "homeless-vacation",
   "metadata": {},
   "outputs": [],
   "source": [
    "find_two_trilogy_casting_list =\\\n",
    "\"\"\"\n",
    "-- SQL 查詢語法起點\n",
    "SELECT CASE WHEN movies.title LIKE '%Lord of the Rings%' THEN 'The Lord of the Rings Trilogy'\n",
    "            ELSE 'Batman Trilogy' END AS trilogy,\n",
    "       actors.name\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 movies.title LIKE '%Lord of the Rings%' OR\n",
    "       movies.title LIKE '%Batman%' OR\n",
    "       movies.title LIKE '%The Dark Knight%'\n",
    " GROUP BY trilogy, actors.name;\n",
    "-- SQL 查詢語法終點\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b498e16",
   "metadata": {},
   "source": [
    "## 07. 從 `imdb` 資料庫查詢出現最多次的演員為誰，參考下列的預期查詢結果。\n",
    "\n",
    "- 預期輸入：SQL 查詢語法。\n",
    "- 預期輸出：(3, 2) 的查詢結果。\n",
    "\n",
    "```\n",
    "   actor_id            name  counts\n",
    "0      2472  Robert De Niro       9\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "64c2e0ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "find_most_frequent_actors_from_imdb =\\\n",
    "\"\"\"\n",
    "-- SQL 查詢語法起點\n",
    "SELECT actor_id,\n",
    "       actors.name,\n",
    "       counts\n",
    "  FROM (SELECT actor_id,\n",
    "               COUNT(*) AS counts\n",
    "          FROM casting\n",
    "         GROUP BY actor_id) AS actor_counts\n",
    "  JOIN actors\n",
    "    ON actor_counts.actor_id = actors.id\n",
    " WHERE actor_counts.counts = (SELECT MAX(counts) AS max_counts\n",
    "                                FROM (SELECT actor_id,\n",
    "                                             COUNT(*) AS counts\n",
    "                                        FROM casting\n",
    "                                       GROUP BY actor_id) AS actor_counts);\n",
    "-- SQL 查詢語法終點\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "facial-camcorder",
   "metadata": {
    "deletable": false,
    "editable": false
   },
   "source": [
    "## 執行測試！\n",
    "\n",
    "Kernel -> Restart & Run All -> Restart and Run All Cells."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "consistent-concentrate",
   "metadata": {
    "deletable": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "test_01_select_all_from_questions (__main__.TestExercises02) ... ok\n",
      "test_02_summarize_programming_languages (__main__.TestExercises02) ... ok\n",
      "test_03_summarize_rdbms (__main__.TestExercises02) ... ok\n",
      "test_04_summarize_rdbms (__main__.TestExercises02) ... ok\n",
      "test_05_summarize_respondant_titles_in_percentage (__main__.TestExercises02) ... ok\n",
      "test_06_find_two_trilogy_casting_list (__main__.TestExercises02) ... ok\n",
      "test_07_find_most_frequent_actors_from_imdb (__main__.TestExercises02) ... ok\n",
      "\n",
      "----------------------------------------------------------------------\n",
      "Ran 7 tests in 0.453s\n",
      "\n",
      "OK\n"
     ]
    }
   ],
   "source": [
    "class TestExercises02(unittest.TestCase):\n",
    "    def test_01_summarize_question_types(self):\n",
    "        question_types = pd.read_sql(summarize_question_types, conn)\n",
    "        self.assertEqual(question_types.shape, (2, 2))\n",
    "        column_values = set(question_types.iloc[:, 0].values)\n",
    "        self.assertTrue('Multiple Choice' in column_values)\n",
    "        self.assertTrue('Multiple Response' in column_values)\n",
    "    def test_02_summarize_programming_languages(self):\n",
    "        programming_languages = pd.read_sql(summarize_programming_languages, conn)\n",
    "        self.assertEqual(programming_languages.shape, (15, 2))\n",
    "        column_values = programming_languages.iloc[:, 0].values\n",
    "        self.assertEqual(column_values[0], \"Python\")\n",
    "        self.assertEqual(column_values[1], \"SQL\")\n",
    "    def test_03_summarize_rdbms(self):\n",
    "        rdbms = pd.read_sql(summarize_rdbms, conn)\n",
    "        self.assertEqual(rdbms.shape, (16, 2))\n",
    "        column_values = rdbms.iloc[:, 0].values\n",
    "        self.assertTrue(\"MySQL\" in column_values[0])\n",
    "        self.assertTrue(\"PostgreSQL\" in column_values[1])\n",
    "        self.assertTrue(\"Microsoft SQL Server\" in column_values[2])\n",
    "        self.assertTrue(\"SQLite\" in column_values[3])\n",
    "    def test_04_summarize_respondant_titles(self):\n",
    "        respondant_titles = pd.read_sql(summarize_respondant_titles, conn)\n",
    "        self.assertEqual(respondant_titles.shape, (15, 2))\n",
    "        column_values = respondant_titles.iloc[:, 0].values\n",
    "        self.assertEqual(column_values[0], \"Data Scientist\")\n",
    "        self.assertTrue(\"Data Analyst\" in column_values[1])\n",
    "    def test_05_summarize_respondant_titles_in_percentage(self):\n",
    "        respondant_titles_in_percentage = pd.read_sql(summarize_respondant_titles_in_percentage, conn)\n",
    "        self.assertEqual(respondant_titles_in_percentage.shape, (15, 2))\n",
    "        column_values = respondant_titles_in_percentage.iloc[:, 0].values\n",
    "        self.assertEqual(column_values[0], \"Data Scientist\")\n",
    "        self.assertTrue(\"Data Analyst\" in column_values[1])\n",
    "        column_values = respondant_titles_in_percentage.iloc[:, 1].values\n",
    "        self.assertTrue(\"%\" in column_values[0])\n",
    "        self.assertTrue(\"%\" in column_values[1])\n",
    "    def test_06_find_two_trilogy_casting_list(self):\n",
    "        two_trilogy_casting_list = pd.read_sql(find_two_trilogy_casting_list, conn)\n",
    "        self.assertEqual(two_trilogy_casting_list.shape, (66, 2))\n",
    "        column_values = set(two_trilogy_casting_list.iloc[:, 1].values)\n",
    "        self.assertTrue('Christian Bale' in column_values)\n",
    "        self.assertTrue('Heath Ledger' in column_values)\n",
    "        self.assertTrue('Anne Hathaway' in column_values)\n",
    "        self.assertTrue('Sean Astin' in column_values)\n",
    "        self.assertTrue('Cate Blanchett' in column_values)\n",
    "        self.assertTrue('Orlando Bloom' in column_values)\n",
    "    def test_07_find_most_frequent_actors_from_imdb(self):\n",
    "        most_frequent_actors_from_imdb = pd.read_sql(find_most_frequent_actors_from_imdb, conn)\n",
    "        self.assertEqual(most_frequent_actors_from_imdb.shape, (1, 3))\n",
    "        column_values = set(most_frequent_actors_from_imdb.iloc[:, 1].values)\n",
    "        self.assertTrue('Robert De Niro' in column_values)\n",
    "        column_values = set(most_frequent_actors_from_imdb.iloc[:, 2].values)\n",
    "        self.assertTrue(9 in column_values)\n",
    "\n",
    "suite = unittest.TestLoader().loadTestsFromTestCase(TestExercises02)\n",
    "runner = unittest.TextTestRunner(verbosity=2)\n",
    "test_results = runner.run(suite)\n",
    "number_of_failures = len(test_results.failures)\n",
    "number_of_errors = len(test_results.errors)\n",
    "number_of_test_runs = test_results.testsRun\n",
    "number_of_successes = number_of_test_runs - (number_of_failures + number_of_errors)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "younger-beach",
   "metadata": {
    "deletable": false,
    "editable": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "您在 7 道 SQL 練習答對了 7 題。\n"
     ]
    }
   ],
   "source": [
    "print(\"您在 {} 道 SQL 練習答對了 {} 題。\".format(number_of_test_runs, number_of_successes))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}