exercises-01.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": [
    {
     "ename": "ModuleNotFoundError",
     "evalue": "No module named 'numpy'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mModuleNotFoundError\u001b[0m                       Traceback (most recent call last)",
      "Cell \u001b[0;32mIn [1], line 3\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[38;5;28;01mimport\u001b[39;00m \u001b[38;5;21;01msqlite3\u001b[39;00m\n\u001b[1;32m      2\u001b[0m \u001b[38;5;28;01mimport\u001b[39;00m \u001b[38;5;21;01munittest\u001b[39;00m\n\u001b[0;32m----> 3\u001b[0m \u001b[38;5;28;01mimport\u001b[39;00m \u001b[38;5;21;01mnumpy\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m \u001b[38;5;21;01mnp\u001b[39;00m\n\u001b[1;32m      4\u001b[0m \u001b[38;5;28;01mimport\u001b[39;00m \u001b[38;5;21;01mpandas\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m \u001b[38;5;21;01mpd\u001b[39;00m\n\u001b[1;32m      5\u001b[0m conn \u001b[38;5;241m=\u001b[39m sqlite3\u001b[38;5;241m.\u001b[39mconnect(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mKaggleSurvey2022.db\u001b[39m\u001b[38;5;124m'\u001b[39m)\n",
      "\u001b[0;31mModuleNotFoundError\u001b[0m: No module named 'numpy'"
     ]
    }
   ],
   "source": [
    "import sqlite3\n",
    "import unittest\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "conn = sqlite3.connect('KaggleSurvey2022.db')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "closed-hierarchy",
   "metadata": {
    "deletable": false
   },
   "source": [
    "## 練習題指引\n",
    "\n",
    "- 在 SQL 語法起點與 SQL 語法終點這兩個單行註解之間撰寫能夠得到預期結果的 SQL。\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",
    "- 預期輸出：(10, 4) 的查詢結果。\n",
    "\n",
    "```\n",
    "   id question_index               type  \\\n",
    "0   1             Q2    Multiple Choice   \n",
    "1   2             Q3    Multiple Choice   \n",
    "2   3             Q4    Multiple Choice   \n",
    "3   4             Q5    Multiple Choice   \n",
    "4   5             Q6  Multiple Response   \n",
    "5   6             Q7  Multiple Response   \n",
    "6   7             Q8    Multiple Choice   \n",
    "7   8             Q9    Multiple Choice   \n",
    "8   9            Q10    Multiple Choice   \n",
    "9  10            Q11    Multiple Choice   \n",
    "\n",
    "                                         description  \n",
    "0                        What is your age (# years)?  \n",
    "1                               What is your gender?  \n",
    "2          In which country do you currently reside?  \n",
    "3  Are you currently a student? (high school, uni...  \n",
    "4  On which platforms have you begun or completed...  \n",
    "5  What products or platforms did you find to be ...  \n",
    "6  What is the highest level of formal education ...  \n",
    "7  Have you ever published any academic research ...  \n",
    "8    Did your research make use of machine learning?  \n",
    "9  For how many years have you been writing code ...\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3fa000bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "select_all_from_questions =\\\n",
    "\"\"\"\n",
    "-- SQL 查詢語法起點\n",
    "SELECT *\n",
    "  FROM questions\n",
    " LIMIT 10;\n",
    "-- SQL 查詢語法終點\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b9bc6b1b",
   "metadata": {},
   "source": [
    "## 02. 從 `KaggleSurvey2022` 資料庫的 `responses` 資料表將所有欄位選擇出來，參考下列的預期查詢結果。\n",
    "\n",
    "- 預期輸入：SQL 查詢語法。\n",
    "- 預期輸出：(15, 3) 的查詢結果。\n",
    "\n",
    "```\n",
    "    respondant_id  question_id choice\n",
    "0               1            1  30-34\n",
    "1               2            1  30-34\n",
    "2               3            1  18-21\n",
    "3               4            1  55-59\n",
    "4               5            1  45-49\n",
    "5               6            1  18-21\n",
    "6               7            1  18-21\n",
    "7               8            1  30-34\n",
    "8               9            1    70+\n",
    "9              10            1  22-24\n",
    "10             11            1  55-59\n",
    "11             12            1  55-59\n",
    "12             13            1  35-39\n",
    "13             14            1  40-44\n",
    "14             15            1  35-39\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "20f62737",
   "metadata": {},
   "outputs": [],
   "source": [
    "select_all_from_responses =\\\n",
    "\"\"\"\n",
    "-- SQL 查詢語法起點\n",
    "\n",
    "-- SQL 查詢語法終點\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ac98b41",
   "metadata": {},
   "source": [
    "## 03. 從 `KaggleSurvey2022` 資料庫的 `questions` 資料表將單選題（Multiple Choice）篩選出來，參考下列的預期查詢結果。\n",
    "\n",
    "- 預期輸入：SQL 查詢語法。\n",
    "- 預期輸出：(19, 2) 的查詢結果。\n",
    "\n",
    "```\n",
    "   question_index                                        description\n",
    "0              Q2                        What is your age (# years)?\n",
    "1              Q3                               What is your gender?\n",
    "2              Q4          In which country do you currently reside?\n",
    "3              Q5  Are you currently a student? (high school, uni...\n",
    "4              Q8  What is the highest level of formal education ...\n",
    "5              Q9  Have you ever published any academic research ...\n",
    "6             Q10    Did your research make use of machine learning?\n",
    "7             Q11  For how many years have you been writing code ...\n",
    "8             Q16  For how many years have you used machine learn...\n",
    "9             Q22  Which of the following ML model hubs/repositor...\n",
    "10            Q23  Select the title most similar to your current ...\n",
    "11            Q24  In what industry is your current employer/cont...\n",
    "12            Q25  What is the size of the company where you are ...\n",
    "13            Q26  Approximately how many individuals are respons...\n",
    "14            Q27  Does your current employer incorporate machine...\n",
    "15            Q29  What is your current yearly compensation (appr...\n",
    "16            Q30  Approximately how much money have you spent on...\n",
    "17            Q32  Of the cloud platforms that you are familiar w...\n",
    "18            Q43  Approximately how many times have you used a T...\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "democratic-failing",
   "metadata": {},
   "outputs": [],
   "source": [
    "filter_multiple_choice_from_questions =\\\n",
    "\"\"\"\n",
    "-- SQL 查詢語法起點\n",
    "\n",
    "-- SQL 查詢語法終點\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "growing-belfast",
   "metadata": {},
   "source": [
    "## 04. 從 `KaggleSurvey2022` 資料庫的 `questions` 資料表將多選題（Multiple Response）篩選出來，參考下列的預期查詢結果。\n",
    "\n",
    "- 預期輸入：SQL 查詢語法。\n",
    "- 預期輸出：(24, 2) 的查詢結果。\n",
    "\n",
    "```\n",
    "   question_index                                        description\n",
    "0              Q6  On which platforms have you begun or completed...\n",
    "1              Q7  What products or platforms did you find to be ...\n",
    "2             Q12  What programming languages do you use on a reg...\n",
    "3             Q13  Which of the following integrated development ...\n",
    "4             Q14  Do you use any of the following hosted noteboo...\n",
    "5             Q15  Do you use any of the following data visualiza...\n",
    "6             Q17  Which of the following machine learning framew...\n",
    "7             Q18  Which of the following ML algorithms do you us...\n",
    "8             Q19  Which categories of computer vision methods do...\n",
    "9             Q20  Which of the following natural language proces...\n",
    "10            Q21  Do you download pre-trained model weights from...\n",
    "11            Q28  Select any activities that make up an importan...\n",
    "12            Q31  Which of the following cloud computing platfor...\n",
    "13            Q33  Do you use any of the following cloud computin...\n",
    "14            Q34  Do you use any of the following data storage p...\n",
    "15            Q35  Do you use any of the following data products ...\n",
    "16            Q36  Do you use any of the following business intel...\n",
    "17            Q37  Do you use any of the following managed machin...\n",
    "18            Q38  Do you use any of the following automated mach...\n",
    "19            Q39  Do you use any of the following products to se...\n",
    "20            Q40  Do you use any tools to help monitor your mach...\n",
    "21            Q41  Do you use any of the following responsible or...\n",
    "22            Q42  Do you use any of the following types of speci...\n",
    "23            Q44  Who/what are your favorite media sources that ...\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "relative-failing",
   "metadata": {},
   "outputs": [],
   "source": [
    "filter_multiple_response_from_questions =\\\n",
    "\"\"\"\n",
    "-- SQL 查詢語法起點\n",
    "\n",
    "-- SQL 查詢語法終點\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mobile-israel",
   "metadata": {},
   "source": [
    "## 05. 從 `KaggleSurvey2022` 資料庫的 `responses` 資料表將填答者 4（`respondant_id = 4`）的填答結果篩選出來，參考下列的預期查詢結果。\n",
    "\n",
    "- 預期輸入：SQL 查詢語法。\n",
    "- 預期輸出：(62, 3) 的查詢結果。\n",
    "\n",
    "```\n",
    "    respondant_id  question_id  \\\n",
    "0               4            1   \n",
    "1               4            2   \n",
    "2               4            3   \n",
    "3               4            4   \n",
    "4               4            5   \n",
    "..            ...          ...   \n",
    "57              4           42   \n",
    "58              4           43   \n",
    "59              4           43   \n",
    "60              4           43   \n",
    "61              4           43   \n",
    "\n",
    "                                               choice  \n",
    "0                                               55-59  \n",
    "1                                                 Man  \n",
    "2                                              France  \n",
    "3                                                  No  \n",
    "4                                            Coursera  \n",
    "..                                                ...  \n",
    "57                                          2-5 times  \n",
    "58                 Twitter (data science influencers)  \n",
    "59                    Kaggle (notebooks, forums, etc)  \n",
    "60  Course Forums (forums.fast.ai, Coursera forums...  \n",
    "61  Blogs (Towards Data Science, Analytics Vidhya,...  \n",
    "\n",
    "[62 rows x 3 columns]\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "homeless-vacation",
   "metadata": {},
   "outputs": [],
   "source": [
    "filter_respondant_from_responses =\\\n",
    "\"\"\"\n",
    "-- SQL 查詢語法起點\n",
    "\n",
    "-- SQL 查詢語法終點\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3136ee10",
   "metadata": {},
   "source": [
    "## 06. 從 `KaggleSurvey2022` 資料庫的 `responses` 資料表將獨一的填答者編號選擇出來，暸解有多少人填寫問卷，參考下列的預期查詢結果。\n",
    "\n",
    "- 預期輸入：SQL 查詢語法。\n",
    "- 預期輸出：(23997, 1) 的查詢結果。\n",
    "\n",
    "```\n",
    "       respondant_id\n",
    "0                  1\n",
    "1                  2\n",
    "2                  3\n",
    "3                  4\n",
    "4                  5\n",
    "...              ...\n",
    "23992          23993\n",
    "23993          23994\n",
    "23994          23995\n",
    "23995          23996\n",
    "23996          23997\n",
    "\n",
    "[23997 rows x 1 columns]\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4c4962fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "select_distinct_respondant_from_responses =\\\n",
    "\"\"\"\n",
    "-- SQL 查詢語法起點\n",
    "\n",
    "-- SQL 查詢語法終點\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b498e16",
   "metadata": {},
   "source": [
    "## 07. 從 `KaggleSurvey2022` 資料庫的 `responses` 資料表將 `Q6: On which platforms have you begun or completed data science courses?` 的獨一選項顯示出來，暸解有多少選項，參考下列的預期查詢結果。\n",
    "\n",
    "- 預期輸入：SQL 查詢語法。\n",
    "- 預期輸出：(12, 2) 的查詢結果。\n",
    "\n",
    "```\n",
    "                                               choice  question_id\n",
    "0                                            Coursera            5\n",
    "1                                                 edX            5\n",
    "2                                Kaggle Learn Courses            5\n",
    "3                                            DataCamp            5\n",
    "4                                             Fast.ai            5\n",
    "5                                             Udacity            5\n",
    "6                                               Udemy            5\n",
    "7                                   LinkedIn Learning            5\n",
    "8   Cloud-certification programs (direct from AWS,...            5\n",
    "9   University Courses (resulting in a university ...            5\n",
    "10                                               None            5\n",
    "11                                              Other            5\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "64c2e0ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "select_distinct_q6_choice_from_responses =\\\n",
    "\"\"\"\n",
    "-- SQL 查詢語法起點\n",
    "\n",
    "-- SQL 查詢語法終點\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "80b7102c",
   "metadata": {},
   "source": [
    "## 08. 從 `KaggleSurvey2022` 資料庫的 `responses` 資料表將 `Q12: What programming language do you use on a regular basis?` 的獨一選項顯示出來，暸解有多少選項，參考下列的預期查詢結果。\n",
    "\n",
    "- 預期輸入：SQL 查詢語法。\n",
    "- 預期輸出：(15, 2) 的查詢結果。\n",
    "\n",
    "```\n",
    "        choice  question_id\n",
    "0       Python           11\n",
    "1            R           11\n",
    "2          SQL           11\n",
    "3            C           11\n",
    "4           C#           11\n",
    "5          C++           11\n",
    "6         Java           11\n",
    "7   Javascript           11\n",
    "8         Bash           11\n",
    "9          PHP           11\n",
    "10      MATLAB           11\n",
    "11       Julia           11\n",
    "12          Go           11\n",
    "13        None           11\n",
    "14       Other           11\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0efef186",
   "metadata": {},
   "outputs": [],
   "source": [
    "select_distinct_q12_choice_from_responses =\\\n",
    "\"\"\"\n",
    "-- SQL 查詢語法起點\n",
    "\n",
    "-- SQL 查詢語法終點\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a9caad0f",
   "metadata": {},
   "source": [
    "## 09. 從 `KaggleSurvey2022` 資料庫查詢資料表清單 metadata，參考下列的預期查詢結果。\n",
    "\n",
    "- 預期輸入：SQL 查詢語法。\n",
    "- 預期輸出：(2, 1) 的查詢結果。\n",
    "\n",
    "```\n",
    "  table_names\n",
    "0   questions\n",
    "1   responses\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dd025ae0",
   "metadata": {},
   "outputs": [],
   "source": [
    "query_metadata_of_table_list =\\\n",
    "\"\"\"\n",
    "-- SQL 查詢語法起點\n",
    "\n",
    "-- SQL 查詢語法終點\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fcd0f7b0",
   "metadata": {},
   "source": [
    "## 10. 從 `KaggleSurvey2022` 資料庫查詢 `questions` 資料表的 metadata，參考下列的預期查詢結果。\n",
    "\n",
    "- 預期輸入：SQL 查詢語法。\n",
    "- 預期輸出：(4, 6) 的查詢結果。\n",
    "\n",
    "```\n",
    "   cid            name     type  notnull dflt_value  pk\n",
    "0    0              id  INTEGER        0       None   1\n",
    "1    1  question_index  INTEGER        0       None   0\n",
    "2    2            type     TEXT        0       None   0\n",
    "3    3     description     TEXT        0       None   0\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c3333028",
   "metadata": {},
   "outputs": [],
   "source": [
    "query_metadata_of_questions_table =\\\n",
    "\"\"\"\n",
    "-- SQL 查詢語法起點\n",
    "\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": null,
   "id": "consistent-concentrate",
   "metadata": {
    "deletable": false
   },
   "outputs": [],
   "source": [
    "class TestExercises01(unittest.TestCase):\n",
    "    def test_01_select_all_from_questions(self):\n",
    "        all_from_questions = pd.read_sql(select_all_from_questions, conn)\n",
    "        self.assertEqual(all_from_questions.shape, (10, 4))\n",
    "    def test_02_select_all_from_responses(self):\n",
    "        all_from_responses = pd.read_sql(select_all_from_responses, conn)\n",
    "        self.assertEqual(all_from_responses.shape, (15, 3))\n",
    "        question_id_nunique = all_from_responses[\"question_id\"].nunique()\n",
    "        self.assertEqual(question_id_nunique, 1)\n",
    "    def test_03_filter_multiple_choice_from_questions(self):\n",
    "        multiple_choice_from_questions = pd.read_sql(filter_multiple_choice_from_questions, conn)\n",
    "        self.assertEqual(multiple_choice_from_questions.shape, (19, 2))\n",
    "        question_index_nunique = multiple_choice_from_questions[\"question_index\"].nunique()\n",
    "        description_nunique = multiple_choice_from_questions[\"description\"].nunique()\n",
    "        self.assertEqual(question_index_nunique, 19)\n",
    "        self.assertEqual(description_nunique, 19)\n",
    "    def test_04_filter_multiple_response_from_questions(self):\n",
    "        multiple_response_from_questions = pd.read_sql(filter_multiple_response_from_questions, conn)\n",
    "        self.assertEqual(multiple_response_from_questions.shape, (24, 2))\n",
    "        question_index_nunique = multiple_response_from_questions[\"question_index\"].nunique()\n",
    "        description_nunique = multiple_response_from_questions[\"description\"].nunique()\n",
    "        self.assertEqual(question_index_nunique, 24)\n",
    "        self.assertEqual(description_nunique, 24)\n",
    "    def test_05_filter_respondant_from_responses(self):\n",
    "        respondant_from_responses = pd.read_sql(filter_respondant_from_responses, conn)\n",
    "        self.assertEqual(respondant_from_responses.shape, (62, 3))\n",
    "        respondant_id_unique = respondant_from_responses[\"respondant_id\"].unique()[0]\n",
    "        self.assertEqual(respondant_id_unique, 4)\n",
    "    def test_06_select_distinct_respondant_from_responses(self):\n",
    "        distinct_respondant_from_responses = pd.read_sql(select_distinct_respondant_from_responses, conn)\n",
    "        self.assertEqual(distinct_respondant_from_responses.shape, (23997, 1))\n",
    "        respondant_id_nunique = distinct_respondant_from_responses[\"respondant_id\"].nunique()\n",
    "        self.assertEqual(respondant_id_nunique, 23997)\n",
    "    def test_07_select_distinct_q6_choice_from_responses(self):\n",
    "        distinct_q6_choice_from_responses = pd.read_sql(select_distinct_q6_choice_from_responses, conn)\n",
    "        self.assertEqual(distinct_q6_choice_from_responses.shape, (12, 2))\n",
    "        choice_nunique = distinct_q6_choice_from_responses[\"choice\"].nunique()\n",
    "        self.assertEqual(choice_nunique, 12)\n",
    "    def test_08_select_distinct_q12_choice_from_responses(self):\n",
    "        distinct_q12_choice_from_responses = pd.read_sql(select_distinct_q12_choice_from_responses, conn)\n",
    "        self.assertEqual(distinct_q12_choice_from_responses.shape, (15, 2))\n",
    "        choice_nunique = distinct_q12_choice_from_responses[\"choice\"].nunique()\n",
    "        self.assertEqual(choice_nunique, 15)\n",
    "    def test_09_query_metadata_of_table_list(self):\n",
    "        metadata_of_table_list = pd.read_sql(query_metadata_of_table_list, conn)\n",
    "        self.assertEqual(metadata_of_table_list.shape, (2, 1))\n",
    "    def test_10_query_metadata_of_questions_table(self):\n",
    "        metadata_of_questions_table = pd.read_sql(query_metadata_of_questions_table, conn)\n",
    "        self.assertEqual(metadata_of_questions_table.shape, (4, 6))\n",
    "\n",
    "suite = unittest.TestLoader().loadTestsFromTestCase(TestExercises01)\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": null,
   "id": "younger-beach",
   "metadata": {
    "deletable": false,
    "editable": false
   },
   "outputs": [],
   "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.10.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}