04-querying-data-with-sql.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Data Visualization with Modern Data Science\n",
    "\n",
    "> Querying Data with SQL\n",
    "\n",
    "Yao-Jen Kuo <yaojenkuo@ntu.edu.tw> from [DATAINPOINT](https://www.datainpoint.com)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "%LOAD sqlite3 db=data/taiwan_election_2024.db timeout=2 shared_cache=true"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## The Elements of a SQL Statement"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## (Recap) What is a SQL statement\n",
    "\n",
    "- A SQL statement is a combination of keywords, object names(e.g. databases/tables/columns/functions), constants, and operators.\n",
    "- SQL keywords are NOT case sensitive.\n",
    "- Semicolon `;` is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call.\n",
    "- Indentations or new lines are OPTIONAL in SQL statements."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## A SQL statement\n",
    "\n",
    "Trying to identify keywords, object names, constants, and operators, respectively."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>district_id</th>\n",
       "<th>polling_place</th>\n",
       "<th>voter_turnout</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>15035</td>\n",
       "<td>1</td>\n",
       "<td>0.59</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>15036</td>\n",
       "<td>2</td>\n",
       "<td>0.6</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>15037</td>\n",
       "<td>3</td>\n",
       "<td>0.56</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>15038</td>\n",
       "<td>4</td>\n",
       "<td>0.53</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>15039</td>\n",
       "<td>5</td>\n",
       "<td>0.54</td>\n",
       "</tr>\n",
       "</table>\n",
       "5 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+-------------+---------------+---------------+\n",
       "| district_id | polling_place | voter_turnout |\n",
       "+-------------+---------------+---------------+\n",
       "| 15035       | 1             | 0.59          |\n",
       "+-------------+---------------+---------------+\n",
       "| 15036       | 2             | 0.6           |\n",
       "+-------------+---------------+---------------+\n",
       "| 15037       | 3             | 0.56          |\n",
       "+-------------+---------------+---------------+\n",
       "| 15038       | 4             | 0.53          |\n",
       "+-------------+---------------+---------------+\n",
       "| 15039       | 5             | 0.54          |\n",
       "+-------------+---------------+---------------+\n",
       "5 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT district_id,\n",
    "       polling_place,\n",
    "       ROUND((effective_votes + wasted_votes)*1.0 / (issued_votes + remained_votes), 2) AS voter_turnout\n",
    "  FROM polling_places\n",
    " LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## The list of SQL keywords\n",
    "\n",
    "- [SQLite Keywords](https://www.sqlite.org/lang_keywords.html)\n",
    "- [Standard SQL Keywords](https://www.w3schools.com/sql/sql_ref_keywords.asp)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## (Recap)Download and install SQLiteStudio\n",
    "\n",
    "- Download links: <https://github.com/pawelsalawa/sqlitestudio/releases>\n",
    "- Download installers: `.exe` for Windows, `.dmg` for macOS.\n",
    "- For macOS users: Override system security by System Preferences > Security & Privacy > Open Anyway."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## (Recap)Downloading an established database [taiwan_election_2024.db](https://taiwan-election-2024.s3.ap-northeast-1.amazonaws.com/taiwan_election_2024.db)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## The list of tables within connected database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>name</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>districts</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>villages</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>parties</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>election_types</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>candidates</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>polling_places</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>presidents</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>regional_legislators</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>aboriginal_legislators</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>party_legislators</td>\n",
       "</tr>\n",
       "</table>\n",
       "10 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+------------------------+\n",
       "| name                   |\n",
       "+------------------------+\n",
       "| districts              |\n",
       "+------------------------+\n",
       "| villages               |\n",
       "+------------------------+\n",
       "| parties                |\n",
       "+------------------------+\n",
       "| election_types         |\n",
       "+------------------------+\n",
       "| candidates             |\n",
       "+------------------------+\n",
       "| polling_places         |\n",
       "+------------------------+\n",
       "| presidents             |\n",
       "+------------------------+\n",
       "| regional_legislators   |\n",
       "+------------------------+\n",
       "| aboriginal_legislators |\n",
       "+------------------------+\n",
       "| party_legislators      |\n",
       "+------------------------+\n",
       "10 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT name\n",
    "  FROM sqlite_master\n",
    " WHERE name NOT LIKE 'sqlite%';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## The list of columns of a specified table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>name</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>id</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>county</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>town</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>polling_place</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>vote_tallied_at</td>\n",
       "</tr>\n",
       "</table>\n",
       "5 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+-----------------+\n",
       "| name            |\n",
       "+-----------------+\n",
       "| id              |\n",
       "+-----------------+\n",
       "| county          |\n",
       "+-----------------+\n",
       "| town            |\n",
       "+-----------------+\n",
       "| polling_place   |\n",
       "+-----------------+\n",
       "| vote_tallied_at |\n",
       "+-----------------+\n",
       "5 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT name\n",
    "  FROM PRAGMA_TABLE_INFO('districts');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>name</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>id</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>district_id</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>polling_place</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>effective_votes</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>wasted_votes</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>issued_votes</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>remained_votes</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>election_type_id</td>\n",
       "</tr>\n",
       "</table>\n",
       "8 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+------------------+\n",
       "| name             |\n",
       "+------------------+\n",
       "| id               |\n",
       "+------------------+\n",
       "| district_id      |\n",
       "+------------------+\n",
       "| polling_place    |\n",
       "+------------------+\n",
       "| effective_votes  |\n",
       "+------------------+\n",
       "| wasted_votes     |\n",
       "+------------------+\n",
       "| issued_votes     |\n",
       "+------------------+\n",
       "| remained_votes   |\n",
       "+------------------+\n",
       "| election_type_id |\n",
       "+------------------+\n",
       "8 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT name\n",
    "  FROM PRAGMA_TABLE_INFO('polling_places');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Calculated fields"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## What is a calculated field\n",
    "\n",
    "A calculated field is generated via the data from existed fields in connected databases."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Several ways to generate a calculated field\n",
    "\n",
    "- Functions.\n",
    "- Constants.\n",
    "- Operators.\n",
    "- `CASE` statement."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## What is a function\n",
    "\n",
    "In the context of programming, a function is a named sequence of statements that performs a desired operation."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Using functions with SQL\n",
    "\n",
    "```sql\n",
    "SELECT FUNCTION_NAME(columns/constants, parameters);\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Functions in SQLite\n",
    "\n",
    "- Scalar functions.\n",
    "    - For type checking.\n",
    "    - For numerics.\n",
    "    - For texts.\n",
    "- Date and time functions.\n",
    "- Aggregate functions."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Useful scalar functions for type checking\n",
    "\n",
    "- `TYPEOF(X)`: The function returns a string that indicates the datatype.\n",
    "- `PRAGMA_TABLE_INFO(table_name)`: The function returns one row for each column in the named table. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## `TYPEOF(X)` function for a certain column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>TYPEOF(id)</th>\n",
       "<th>TYPEOF(county)</th>\n",
       "<th>TYPEOF(vote_tallied_at)</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>integer</td>\n",
       "<td>text</td>\n",
       "<td>text</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+------------+----------------+-------------------------+\n",
       "| TYPEOF(id) | TYPEOF(county) | TYPEOF(vote_tallied_at) |\n",
       "+------------+----------------+-------------------------+\n",
       "| integer    | text           | text                    |\n",
       "+------------+----------------+-------------------------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT TYPEOF(id),\n",
    "       TYPEOF(county),\n",
    "       TYPEOF(vote_tallied_at)\n",
    "  FROM districts\n",
    " LIMIT 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## `PRAGMA_TABLE_INFO(table_name)` for an entire table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>cid</th>\n",
       "<th>name</th>\n",
       "<th>type</th>\n",
       "<th>notnull</th>\n",
       "<th>dflt_value</th>\n",
       "<th>pk</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>0</td>\n",
       "<td>id</td>\n",
       "<td>INTEGER</td>\n",
       "<td>0</td>\n",
       "<td>NULL</td>\n",
       "<td>1</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1</td>\n",
       "<td>county</td>\n",
       "<td>CHAR(3)</td>\n",
       "<td>0</td>\n",
       "<td>NULL</td>\n",
       "<td>0</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2</td>\n",
       "<td>town</td>\n",
       "<td>VARCHAR(200)</td>\n",
       "<td>0</td>\n",
       "<td>NULL</td>\n",
       "<td>0</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>3</td>\n",
       "<td>polling_place</td>\n",
       "<td>INTEGER</td>\n",
       "<td>0</td>\n",
       "<td>NULL</td>\n",
       "<td>0</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>4</td>\n",
       "<td>vote_tallied_at</td>\n",
       "<td>CHAR(19)</td>\n",
       "<td>0</td>\n",
       "<td>NULL</td>\n",
       "<td>0</td>\n",
       "</tr>\n",
       "</table>\n",
       "5 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+-----+-----------------+--------------+---------+------------+----+\n",
       "| cid | name            | type         | notnull | dflt_value | pk |\n",
       "+-----+-----------------+--------------+---------+------------+----+\n",
       "| 0   | id              | INTEGER      | 0       | NULL       | 1  |\n",
       "+-----+-----------------+--------------+---------+------------+----+\n",
       "| 1   | county          | CHAR(3)      | 0       | NULL       | 0  |\n",
       "+-----+-----------------+--------------+---------+------------+----+\n",
       "| 2   | town            | VARCHAR(200) | 0       | NULL       | 0  |\n",
       "+-----+-----------------+--------------+---------+------------+----+\n",
       "| 3   | polling_place   | INTEGER      | 0       | NULL       | 0  |\n",
       "+-----+-----------------+--------------+---------+------------+----+\n",
       "| 4   | vote_tallied_at | CHAR(19)     | 0       | NULL       | 0  |\n",
       "+-----+-----------------+--------------+---------+------------+----+\n",
       "5 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT *\n",
    "  FROM PRAGMA_TABLE_INFO('districts');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Using constants to examine datatype\n",
    "\n",
    "- `TEXT`\n",
    "- `INTEGER`\n",
    "- `REAL`\n",
    "- `NULL`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Using `AS` for alias of calculated fields"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>text_constant</th>\n",
       "<th>integer_constant</th>\n",
       "<th>real_constant</th>\n",
       "<th>null_constant</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>I am a TEXT</td>\n",
       "<td>5566</td>\n",
       "<td>3.14159</td>\n",
       "<td>NULL</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+---------------+------------------+---------------+---------------+\n",
       "| text_constant | integer_constant | real_constant | null_constant |\n",
       "+---------------+------------------+---------------+---------------+\n",
       "| I am a TEXT   | 5566             | 3.14159       | NULL          |\n",
       "+---------------+------------------+---------------+---------------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT 'I am a TEXT' AS text_constant,\n",
    "       5566 AS integer_constant,\n",
    "       3.14159 AS real_constant,\n",
    "       NULL AS null_constant;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>text_constant</th>\n",
       "<th>integer_constant</th>\n",
       "<th>real_constant</th>\n",
       "<th>null_constant</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>text</td>\n",
       "<td>integer</td>\n",
       "<td>real</td>\n",
       "<td>null</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+---------------+------------------+---------------+---------------+\n",
       "| text_constant | integer_constant | real_constant | null_constant |\n",
       "+---------------+------------------+---------------+---------------+\n",
       "| text          | integer          | real          | null          |\n",
       "+---------------+------------------+---------------+---------------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT TYPEOF('I am a TEXT') AS text_constant,\n",
    "       TYPEOF(5566) AS integer_constant,\n",
    "       TYPEOF(3.14159) AS real_constant,\n",
    "       TYPEOF(NULL) AS null_constant;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Using `CAST(X AS datatype)` to convert data type for query results\n",
    "\n",
    "Before casting."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>effective_votes</th>\n",
       "<th>wasted_votes</th>\n",
       "<th>issued_votes</th>\n",
       "<th>remained_votes</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>616</td>\n",
       "<td>1</td>\n",
       "<td>617</td>\n",
       "<td>423</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+-----------------+--------------+--------------+----------------+\n",
       "| effective_votes | wasted_votes | issued_votes | remained_votes |\n",
       "+-----------------+--------------+--------------+----------------+\n",
       "| 616             | 1            | 617          | 423            |\n",
       "+-----------------+--------------+--------------+----------------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT effective_votes,\n",
    "       wasted_votes,\n",
    "       issued_votes,\n",
    "       remained_votes\n",
    "  FROM polling_places\n",
    " LIMIT 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Using `CAST(X AS datatype)` to convert data type for query results\n",
    "\n",
    "After casting."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>CAST(effective_votes AS REAL)</th>\n",
       "<th>CAST(wasted_votes AS REAL)</th>\n",
       "<th>CAST(issued_votes AS REAL)</th>\n",
       "<th>CAST(remained_votes AS REAL)</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>616.0</td>\n",
       "<td>1.0</td>\n",
       "<td>617.0</td>\n",
       "<td>423.0</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+-------------------------------+----------------------------+----------------------------+------------------------------+\n",
       "| CAST(effective_votes AS REAL) | CAST(wasted_votes AS REAL) | CAST(issued_votes AS REAL) | CAST(remained_votes AS REAL) |\n",
       "+-------------------------------+----------------------------+----------------------------+------------------------------+\n",
       "| 616.0                         | 1.0                        | 617.0                      | 423.0                        |\n",
       "+-------------------------------+----------------------------+----------------------------+------------------------------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT CAST(effective_votes AS REAL),\n",
    "       CAST(wasted_votes AS REAL),\n",
    "       CAST(issued_votes AS REAL),\n",
    "       CAST(remained_votes AS REAL)\n",
    "  FROM polling_places\n",
    " LIMIT 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Operators\n",
    "\n",
    "- Numeric operators\n",
    "    - `+`, `-`, `*`, `/`\n",
    "- Text operators\n",
    "    - `||`\n",
    "- Relational operators\n",
    "    - `=`, `!=`, `>`, `>=`, `<`, `<=`, `IN`, `BETWEEN`, `IS NULL`\n",
    "- Logical operators\n",
    "    - `AND`, `OR`, `NOT`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Beware of using `/` dividing integers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>2/5</th>\n",
       "<th>2*1.0/5</th>\n",
       "<th>2/5*1.0</th>\n",
       "<th>2/(5*1.0)</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>0</td>\n",
       "<td>0.4</td>\n",
       "<td>0.0</td>\n",
       "<td>0.4</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+-----+---------+---------+-----------+\n",
       "| 2/5 | 2*1.0/5 | 2/5*1.0 | 2/(5*1.0) |\n",
       "+-----+---------+---------+-----------+\n",
       "| 0   | 0.4     | 0.0     | 0.4       |\n",
       "+-----+---------+---------+-----------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT 2/5,\n",
    "       2*1.0/5,\n",
    "       2/5*1.0,\n",
    "       2/(5*1.0);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Using `||` to concatenate texts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>ironman</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Tony Stark</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+------------+\n",
       "| ironman    |\n",
       "+------------+\n",
       "| Tony Stark |\n",
       "+------------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT 'Tony' || ' ' || 'Stark' AS ironman;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Other scalar functions for numerics and texts\n",
    "\n",
    "<https://www.sqlite.org/lang_corefunc.html>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Common scalar functions for date and time functions\n",
    "\n",
    "- `DATE(X)`\n",
    "- `TIME(X)`\n",
    "- `DATETIME(X)`\n",
    "- `STRFTIME(format, X)`\n",
    "\n",
    "Source: <https://www.sqlite.org/lang_datefunc.html>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "```sql\n",
    "SELECT DATE('now') AS date_of_now;\n",
    "\n",
    "SELECT TIME('now', 'localtime') AS time_of_now;\n",
    "\n",
    "SELECT DATETIME('now', 'localtime') AS datetime_of_now;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Common format strings for date/time/datetime\n",
    "\n",
    "- `%d` day of month: 00.\n",
    "- `%H` hour: 00-24.\n",
    "- `%j` day of year: 001-366.\n",
    "- `%m` month: 01-12.\n",
    "- `%M` minute: 00-59.\n",
    "- `%S` seconds: 00-59.\n",
    "- `%w` day of week 0-6 with Sunday==0.\n",
    "- `%W` week of year: 00-53.\n",
    "- `%Y` year: 0000-9999."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "```sql\n",
    "SELECT DATE('now') AS date_of_now,\n",
    "       TIME('now', 'localtime') AS time_of_now, \n",
    "       STRFTIME('%d', DATE('now')) AS day_part,\n",
    "       STRFTIME('%H', TIME('now', 'localtime')) AS hour_part,\n",
    "       STRFTIME('%j', DATE('now')) AS year_day,\n",
    "       STRFTIME('%m', DATE('now')) AS month_part,\n",
    "       STRFTIME('%M', TIME('now', 'localtime')) AS minute_part,\n",
    "       STRFTIME('%S', TIME('now', 'localtime')) AS second_part,\n",
    "       STRFTIME('%w', DATE('now')) AS weekday,\n",
    "       STRFTIME('%W', DATE('now')) AS nth_week,\n",
    "       STRFTIME('%Y', DATE('now')) AS year_part;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## `CASE` statement is a conditional expression\n",
    "\n",
    "We can add some \"if this, then that...\" logic to a SQL statement.\n",
    "\n",
    "```sql\n",
    "CASE WHEN condition_1 THEN result_1\n",
    "     WHEN condition_2 THEN result_2\n",
    "     ELSE result_else END AS calculated_field_alias\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## `CASE` statements can be used to\n",
    "\n",
    "- Turn numeric values into categories.\n",
    "- Turn categories into new categories."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Turn numeric values into categories"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>number</th>\n",
       "<th>number_text</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1</td>\n",
       "<td>1號候選人</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2</td>\n",
       "<td>2號候選人</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>3</td>\n",
       "<td>3號候選人</td>\n",
       "</tr>\n",
       "</table>\n",
       "3 rows in set (0.01 sec)"
      ],
      "text/plain": [
       "+--------+---------------+\n",
       "| number | number_text   |\n",
       "+--------+---------------+\n",
       "| 1      | 1號候選人 |\n",
       "+--------+---------------+\n",
       "| 2      | 2號候選人 |\n",
       "+--------+---------------+\n",
       "| 3      | 3號候選人 |\n",
       "+--------+---------------+\n",
       "3 rows in set (0.01 sec)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT DISTINCT number,\n",
    "       CASE WHEN number = 1 THEN '1號候選人'\n",
    "            WHEN number = 2 THEN '2號候選人'\n",
    "            ELSE '3號候選人' END AS number_text\n",
    "  FROM presidents;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Turn categories into new categories"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>county</th>\n",
       "<th>county_type</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>南投縣</td>\n",
       "<td>非六都</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>嘉義市</td>\n",
       "<td>非六都</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>嘉義縣</td>\n",
       "<td>非六都</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>基隆市</td>\n",
       "<td>非六都</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>宜蘭縣</td>\n",
       "<td>非六都</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>屏東縣</td>\n",
       "<td>非六都</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>彰化縣</td>\n",
       "<td>非六都</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>新北市</td>\n",
       "<td>六都</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>新竹市</td>\n",
       "<td>非六都</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>新竹縣</td>\n",
       "<td>非六都</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>桃園市</td>\n",
       "<td>六都</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>澎湖縣</td>\n",
       "<td>非六都</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>臺中市</td>\n",
       "<td>六都</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>臺北市</td>\n",
       "<td>六都</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>臺南市</td>\n",
       "<td>六都</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>臺東縣</td>\n",
       "<td>非六都</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>花蓮縣</td>\n",
       "<td>非六都</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>苗栗縣</td>\n",
       "<td>非六都</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>連江縣</td>\n",
       "<td>非六都</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>金門縣</td>\n",
       "<td>非六都</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>雲林縣</td>\n",
       "<td>非六都</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>高雄市</td>\n",
       "<td>六都</td>\n",
       "</tr>\n",
       "</table>\n",
       "22 rows in set (0.01 sec)"
      ],
      "text/plain": [
       "+-----------+-------------+\n",
       "| county    | county_type |\n",
       "+-----------+-------------+\n",
       "| 南投縣 | 非六都   |\n",
       "+-----------+-------------+\n",
       "| 嘉義市 | 非六都   |\n",
       "+-----------+-------------+\n",
       "| 嘉義縣 | 非六都   |\n",
       "+-----------+-------------+\n",
       "| 基隆市 | 非六都   |\n",
       "+-----------+-------------+\n",
       "| 宜蘭縣 | 非六都   |\n",
       "+-----------+-------------+\n",
       "| 屏東縣 | 非六都   |\n",
       "+-----------+-------------+\n",
       "| 彰化縣 | 非六都   |\n",
       "+-----------+-------------+\n",
       "| 新北市 | 六都      |\n",
       "+-----------+-------------+\n",
       "| 新竹市 | 非六都   |\n",
       "+-----------+-------------+\n",
       "| 新竹縣 | 非六都   |\n",
       "+-----------+-------------+\n",
       "| 桃園市 | 六都      |\n",
       "+-----------+-------------+\n",
       "| 澎湖縣 | 非六都   |\n",
       "+-----------+-------------+\n",
       "| 臺中市 | 六都      |\n",
       "+-----------+-------------+\n",
       "| 臺北市 | 六都      |\n",
       "+-----------+-------------+\n",
       "| 臺南市 | 六都      |\n",
       "+-----------+-------------+\n",
       "| 臺東縣 | 非六都   |\n",
       "+-----------+-------------+\n",
       "| 花蓮縣 | 非六都   |\n",
       "+-----------+-------------+\n",
       "| 苗栗縣 | 非六都   |\n",
       "+-----------+-------------+\n",
       "| 連江縣 | 非六都   |\n",
       "+-----------+-------------+\n",
       "| 金門縣 | 非六都   |\n",
       "+-----------+-------------+\n",
       "| 雲林縣 | 非六都   |\n",
       "+-----------+-------------+\n",
       "| 高雄市 | 六都      |\n",
       "+-----------+-------------+\n",
       "22 rows in set (0.01 sec)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT DISTINCT county,\n",
    "       CASE WHEN county IN ('臺北市', '桃園市', '新北市', '臺中市', '臺南市', '高雄市') THEN '六都'\n",
    "            ELSE '非六都' END AS county_type\n",
    "  FROM districts;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## We can also roughly divide the SQL functions into 2 sub categories\n",
    "\n",
    "1. Scalar functions.\n",
    "2. Aggregate functions."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## The difference between these 2 categories\n",
    "\n",
    "The major difference is whether if the rows of output equals to the rows of input."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Rows of input `county` equals to `SUBSTR(county)`\n",
    "\n",
    "`SUBSTR()` is a scalar function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>county</th>\n",
       "<th>SUBSTR(county, 1, 2)</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>南投縣</td>\n",
       "<td>南投</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>嘉義市</td>\n",
       "<td>嘉義</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>嘉義縣</td>\n",
       "<td>嘉義</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>基隆市</td>\n",
       "<td>基隆</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>宜蘭縣</td>\n",
       "<td>宜蘭</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>屏東縣</td>\n",
       "<td>屏東</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>彰化縣</td>\n",
       "<td>彰化</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>新北市</td>\n",
       "<td>新北</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>新竹市</td>\n",
       "<td>新竹</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>新竹縣</td>\n",
       "<td>新竹</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>桃園市</td>\n",
       "<td>桃園</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>澎湖縣</td>\n",
       "<td>澎湖</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>臺中市</td>\n",
       "<td>臺中</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>臺北市</td>\n",
       "<td>臺北</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>臺南市</td>\n",
       "<td>臺南</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>臺東縣</td>\n",
       "<td>臺東</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>花蓮縣</td>\n",
       "<td>花蓮</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>苗栗縣</td>\n",
       "<td>苗栗</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>連江縣</td>\n",
       "<td>連江</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>金門縣</td>\n",
       "<td>金門</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>雲林縣</td>\n",
       "<td>雲林</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>高雄市</td>\n",
       "<td>高雄</td>\n",
       "</tr>\n",
       "</table>\n",
       "22 rows in set (0.01 sec)"
      ],
      "text/plain": [
       "+-----------+----------------------+\n",
       "| county    | SUBSTR(county, 1, 2) |\n",
       "+-----------+----------------------+\n",
       "| 南投縣 | 南投               |\n",
       "+-----------+----------------------+\n",
       "| 嘉義市 | 嘉義               |\n",
       "+-----------+----------------------+\n",
       "| 嘉義縣 | 嘉義               |\n",
       "+-----------+----------------------+\n",
       "| 基隆市 | 基隆               |\n",
       "+-----------+----------------------+\n",
       "| 宜蘭縣 | 宜蘭               |\n",
       "+-----------+----------------------+\n",
       "| 屏東縣 | 屏東               |\n",
       "+-----------+----------------------+\n",
       "| 彰化縣 | 彰化               |\n",
       "+-----------+----------------------+\n",
       "| 新北市 | 新北               |\n",
       "+-----------+----------------------+\n",
       "| 新竹市 | 新竹               |\n",
       "+-----------+----------------------+\n",
       "| 新竹縣 | 新竹               |\n",
       "+-----------+----------------------+\n",
       "| 桃園市 | 桃園               |\n",
       "+-----------+----------------------+\n",
       "| 澎湖縣 | 澎湖               |\n",
       "+-----------+----------------------+\n",
       "| 臺中市 | 臺中               |\n",
       "+-----------+----------------------+\n",
       "| 臺北市 | 臺北               |\n",
       "+-----------+----------------------+\n",
       "| 臺南市 | 臺南               |\n",
       "+-----------+----------------------+\n",
       "| 臺東縣 | 臺東               |\n",
       "+-----------+----------------------+\n",
       "| 花蓮縣 | 花蓮               |\n",
       "+-----------+----------------------+\n",
       "| 苗栗縣 | 苗栗               |\n",
       "+-----------+----------------------+\n",
       "| 連江縣 | 連江               |\n",
       "+-----------+----------------------+\n",
       "| 金門縣 | 金門               |\n",
       "+-----------+----------------------+\n",
       "| 雲林縣 | 雲林               |\n",
       "+-----------+----------------------+\n",
       "| 高雄市 | 高雄               |\n",
       "+-----------+----------------------+\n",
       "22 rows in set (0.01 sec)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT DISTINCT county,\n",
    "       SUBSTR(county, 1, 2)\n",
    "  FROM districts;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Aggregate functions combine values from multiple rows and return a single result based on an operation on those values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>total_president_votes</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>13947506</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.01 sec)"
      ],
      "text/plain": [
       "+-----------------------+\n",
       "| total_president_votes |\n",
       "+-----------------------+\n",
       "| 13947506              |\n",
       "+-----------------------+\n",
       "1 row in set (0.01 sec)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT SUM(votes) AS total_president_votes\n",
    "  FROM presidents;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Common aggregate functions\n",
    "\n",
    "- `AVG(X)`\n",
    "- `COUNT(X)`: the count of the number of times that X is not NULL.\n",
    "- `COUNT(*)`: the total number of rows in the group.\n",
    "- `MAX(X)`\n",
    "- `MIN(X)`\n",
    "- `SUM(X)`\n",
    "\n",
    "Source: <https://www.sqlite.org/lang_aggfunc.html>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## How to count the number of columns of a table\n",
    "\n",
    "By querying metadata!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>number_of_columns_for_districts</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>5</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+---------------------------------+\n",
       "| number_of_columns_for_districts |\n",
       "+---------------------------------+\n",
       "| 5                               |\n",
       "+---------------------------------+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT COUNT(*) AS number_of_columns_for_districts\n",
    "  FROM PRAGMA_TABLE_INFO('districts');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Aggregating Data with `GROUP BY`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## `GROUP BY` keyword\n",
    "\n",
    "`GROUP BY` on its own, eliminates duplicate values from the results, similar to the combination of `DISTINCT` and `ORDER BY`(in some RDBMS)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>town</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>中寮鄉</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>仁愛鄉</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>信義鄉</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>南投市</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>名間鄉</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>國姓鄉</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>埔里鎮</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>水里鄉</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>竹山鎮</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>草屯鎮</td>\n",
       "</tr>\n",
       "</table>\n",
       "10 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+-----------+\n",
       "| town      |\n",
       "+-----------+\n",
       "| 中寮鄉 |\n",
       "+-----------+\n",
       "| 仁愛鄉 |\n",
       "+-----------+\n",
       "| 信義鄉 |\n",
       "+-----------+\n",
       "| 南投市 |\n",
       "+-----------+\n",
       "| 名間鄉 |\n",
       "+-----------+\n",
       "| 國姓鄉 |\n",
       "+-----------+\n",
       "| 埔里鎮 |\n",
       "+-----------+\n",
       "| 水里鄉 |\n",
       "+-----------+\n",
       "| 竹山鎮 |\n",
       "+-----------+\n",
       "| 草屯鎮 |\n",
       "+-----------+\n",
       "10 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT DISTINCT town\n",
    "  FROM districts\n",
    " --ORDER BY town\n",
    " LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## `GROUP BY` keyword(Cont'd)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>town</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>七堵區</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>七美鄉</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>七股區</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>三地門鄉</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>三峽區</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>三星鄉</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>三民區</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>三灣鄉</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>三義鄉</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>三芝區</td>\n",
       "</tr>\n",
       "</table>\n",
       "10 rows in set (0.01 sec)"
      ],
      "text/plain": [
       "+--------------+\n",
       "| town         |\n",
       "+--------------+\n",
       "| 七堵區    |\n",
       "+--------------+\n",
       "| 七美鄉    |\n",
       "+--------------+\n",
       "| 七股區    |\n",
       "+--------------+\n",
       "| 三地門鄉 |\n",
       "+--------------+\n",
       "| 三峽區    |\n",
       "+--------------+\n",
       "| 三星鄉    |\n",
       "+--------------+\n",
       "| 三民區    |\n",
       "+--------------+\n",
       "| 三灣鄉    |\n",
       "+--------------+\n",
       "| 三義鄉    |\n",
       "+--------------+\n",
       "| 三芝區    |\n",
       "+--------------+\n",
       "10 rows in set (0.01 sec)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT town\n",
    "  FROM districts\n",
    " GROUP BY town\n",
    " LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Using `GROUP BY` with `COUNT(*)`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>county</th>\n",
       "<th>number_of_rows</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>新北市</td>\n",
       "<td>2681</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>高雄市</td>\n",
       "<td>2038</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>臺中市</td>\n",
       "<td>1902</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>臺北市</td>\n",
       "<td>1764</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>臺南市</td>\n",
       "<td>1548</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>桃園市</td>\n",
       "<td>1384</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>彰化縣</td>\n",
       "<td>1085</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>屏東縣</td>\n",
       "<td>723</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>雲林縣</td>\n",
       "<td>620</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>嘉義縣</td>\n",
       "<td>535</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>南投縣</td>\n",
       "<td>493</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>苗栗縣</td>\n",
       "<td>484</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>新竹縣</td>\n",
       "<td>470</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>宜蘭縣</td>\n",
       "<td>431</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>新竹市</td>\n",
       "<td>356</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>花蓮縣</td>\n",
       "<td>337</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>基隆市</td>\n",
       "<td>285</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>臺東縣</td>\n",
       "<td>236</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>嘉義市</td>\n",
       "<td>198</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>澎湖縣</td>\n",
       "<td>120</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>金門縣</td>\n",
       "<td>95</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>連江縣</td>\n",
       "<td>10</td>\n",
       "</tr>\n",
       "</table>\n",
       "22 rows in set (0.01 sec)"
      ],
      "text/plain": [
       "+-----------+----------------+\n",
       "| county    | number_of_rows |\n",
       "+-----------+----------------+\n",
       "| 新北市 | 2681           |\n",
       "+-----------+----------------+\n",
       "| 高雄市 | 2038           |\n",
       "+-----------+----------------+\n",
       "| 臺中市 | 1902           |\n",
       "+-----------+----------------+\n",
       "| 臺北市 | 1764           |\n",
       "+-----------+----------------+\n",
       "| 臺南市 | 1548           |\n",
       "+-----------+----------------+\n",
       "| 桃園市 | 1384           |\n",
       "+-----------+----------------+\n",
       "| 彰化縣 | 1085           |\n",
       "+-----------+----------------+\n",
       "| 屏東縣 | 723            |\n",
       "+-----------+----------------+\n",
       "| 雲林縣 | 620            |\n",
       "+-----------+----------------+\n",
       "| 嘉義縣 | 535            |\n",
       "+-----------+----------------+\n",
       "| 南投縣 | 493            |\n",
       "+-----------+----------------+\n",
       "| 苗栗縣 | 484            |\n",
       "+-----------+----------------+\n",
       "| 新竹縣 | 470            |\n",
       "+-----------+----------------+\n",
       "| 宜蘭縣 | 431            |\n",
       "+-----------+----------------+\n",
       "| 新竹市 | 356            |\n",
       "+-----------+----------------+\n",
       "| 花蓮縣 | 337            |\n",
       "+-----------+----------------+\n",
       "| 基隆市 | 285            |\n",
       "+-----------+----------------+\n",
       "| 臺東縣 | 236            |\n",
       "+-----------+----------------+\n",
       "| 嘉義市 | 198            |\n",
       "+-----------+----------------+\n",
       "| 澎湖縣 | 120            |\n",
       "+-----------+----------------+\n",
       "| 金門縣 | 95             |\n",
       "+-----------+----------------+\n",
       "| 連江縣 | 10             |\n",
       "+-----------+----------------+\n",
       "22 rows in set (0.01 sec)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT county,\n",
    "       COUNT(*) AS number_of_rows\n",
    "  FROM districts\n",
    " GROUP BY county\n",
    " ORDER BY number_of_rows DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Using `GROUP BY` with `SUM()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>number</th>\n",
       "<th>candidate_id</th>\n",
       "<th>sum_votes</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1</td>\n",
       "<td>330</td>\n",
       "<td>3690466</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2</td>\n",
       "<td>331</td>\n",
       "<td>5586019</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>3</td>\n",
       "<td>329</td>\n",
       "<td>4671021</td>\n",
       "</tr>\n",
       "</table>\n",
       "3 rows in set (0.03 sec)"
      ],
      "text/plain": [
       "+--------+--------------+-----------+\n",
       "| number | candidate_id | sum_votes |\n",
       "+--------+--------------+-----------+\n",
       "| 1      | 330          | 3690466   |\n",
       "+--------+--------------+-----------+\n",
       "| 2      | 331          | 5586019   |\n",
       "+--------+--------------+-----------+\n",
       "| 3      | 329          | 4671021   |\n",
       "+--------+--------------+-----------+\n",
       "3 rows in set (0.03 sec)"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT number,\n",
    "       candidate_id,\n",
    "       SUM(votes) AS sum_votes\n",
    "  FROM presidents\n",
    " GROUP BY number;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Filtering an aggregate query using `HAVING`\n",
    "\n",
    "We are already familiar with using `WHERE` for filtering, but aggregate functions cannot be used in a `WHERE` statement because they operate at the row level, and aggregate functions work across rows."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Combining `GROUP BY`, `COUNT(*)`, and `HAVING`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>county</th>\n",
       "<th>number_of_rows</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>新北市</td>\n",
       "<td>2681</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>高雄市</td>\n",
       "<td>2038</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>臺中市</td>\n",
       "<td>1902</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>臺北市</td>\n",
       "<td>1764</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>臺南市</td>\n",
       "<td>1548</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>桃園市</td>\n",
       "<td>1384</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>彰化縣</td>\n",
       "<td>1085</td>\n",
       "</tr>\n",
       "</table>\n",
       "7 rows in set (0.01 sec)"
      ],
      "text/plain": [
       "+-----------+----------------+\n",
       "| county    | number_of_rows |\n",
       "+-----------+----------------+\n",
       "| 新北市 | 2681           |\n",
       "+-----------+----------------+\n",
       "| 高雄市 | 2038           |\n",
       "+-----------+----------------+\n",
       "| 臺中市 | 1902           |\n",
       "+-----------+----------------+\n",
       "| 臺北市 | 1764           |\n",
       "+-----------+----------------+\n",
       "| 臺南市 | 1548           |\n",
       "+-----------+----------------+\n",
       "| 桃園市 | 1384           |\n",
       "+-----------+----------------+\n",
       "| 彰化縣 | 1085           |\n",
       "+-----------+----------------+\n",
       "7 rows in set (0.01 sec)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT county,\n",
    "       COUNT(*) AS number_of_rows\n",
    "  FROM districts\n",
    " GROUP BY county\n",
    "HAVING number_of_rows >= 1000\n",
    " ORDER BY number_of_rows DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Combining `GROUP BY`, `SUM()`, and `HAVING`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>number</th>\n",
       "<th>candidate_id</th>\n",
       "<th>sum_votes</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2</td>\n",
       "<td>331</td>\n",
       "<td>5586019</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>3</td>\n",
       "<td>329</td>\n",
       "<td>4671021</td>\n",
       "</tr>\n",
       "</table>\n",
       "2 rows in set (0.02 sec)"
      ],
      "text/plain": [
       "+--------+--------------+-----------+\n",
       "| number | candidate_id | sum_votes |\n",
       "+--------+--------------+-----------+\n",
       "| 2      | 331          | 5586019   |\n",
       "+--------+--------------+-----------+\n",
       "| 3      | 329          | 4671021   |\n",
       "+--------+--------------+-----------+\n",
       "2 rows in set (0.02 sec)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT number,\n",
    "       candidate_id,\n",
    "       SUM(votes) AS sum_votes\n",
    "  FROM presidents\n",
    " GROUP BY number\n",
    "HAVING sum_votes > 4500000;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Sub-queries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## What is a sub-query?\n",
    "\n",
    "A sub-query is nested inside another query, we just enclose the sub-query in parentheses and use it where needed."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Useful sub-query skills\n",
    "\n",
    "- Filtering with sub-queries in a `WHERE` statement.\n",
    "- Creating a calculated field with sub-queries in a `SELECT` statement.\n",
    "- Sub-queries in a `FROM` statement."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Filtering with sub-queries in a `WHERE` statement\n",
    "\n",
    "Which polling place has the most wasted votes in president election?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Which polling place has the most wasted votes in president election?\n",
    "\n",
    "- First query for the most wasted votes.\n",
    "- Second query for the polling place."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>max_wasted_votes</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>29</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.02 sec)"
      ],
      "text/plain": [
       "+------------------+\n",
       "| max_wasted_votes |\n",
       "+------------------+\n",
       "| 29               |\n",
       "+------------------+\n",
       "1 row in set (0.02 sec)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT MAX(wasted_votes) AS max_wasted_votes\n",
    "  FROM polling_places\n",
    " WHERE election_type_id = 1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>id</th>\n",
       "<th>district_id</th>\n",
       "<th>polling_place</th>\n",
       "<th>effective_votes</th>\n",
       "<th>wasted_votes</th>\n",
       "<th>issued_votes</th>\n",
       "<th>remained_votes</th>\n",
       "<th>election_type_id</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1401</td>\n",
       "<td>13020</td>\n",
       "<td>668</td>\n",
       "<td>774</td>\n",
       "<td>29</td>\n",
       "<td>803</td>\n",
       "<td>309</td>\n",
       "<td>1</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.01 sec)"
      ],
      "text/plain": [
       "+------+-------------+---------------+-----------------+--------------+--------------+----------------+------------------+\n",
       "| id   | district_id | polling_place | effective_votes | wasted_votes | issued_votes | remained_votes | election_type_id |\n",
       "+------+-------------+---------------+-----------------+--------------+--------------+----------------+------------------+\n",
       "| 1401 | 13020       | 668           | 774             | 29           | 803          | 309            | 1                |\n",
       "+------+-------------+---------------+-----------------+--------------+--------------+----------------+------------------+\n",
       "1 row in set (0.01 sec)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT *\n",
    "  FROM polling_places\n",
    " WHERE wasted_votes = 29 AND\n",
    "       election_type_id = 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Which polling place has the most wasted votes in president election?(Cont'd)\n",
    "\n",
    "Sub-query: combining 2 queries into one."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>id</th>\n",
       "<th>district_id</th>\n",
       "<th>polling_place</th>\n",
       "<th>effective_votes</th>\n",
       "<th>wasted_votes</th>\n",
       "<th>issued_votes</th>\n",
       "<th>remained_votes</th>\n",
       "<th>election_type_id</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1401</td>\n",
       "<td>13020</td>\n",
       "<td>668</td>\n",
       "<td>774</td>\n",
       "<td>29</td>\n",
       "<td>803</td>\n",
       "<td>309</td>\n",
       "<td>1</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.02 sec)"
      ],
      "text/plain": [
       "+------+-------------+---------------+-----------------+--------------+--------------+----------------+------------------+\n",
       "| id   | district_id | polling_place | effective_votes | wasted_votes | issued_votes | remained_votes | election_type_id |\n",
       "+------+-------------+---------------+-----------------+--------------+--------------+----------------+------------------+\n",
       "| 1401 | 13020       | 668           | 774             | 29           | 803          | 309            | 1                |\n",
       "+------+-------------+---------------+-----------------+--------------+--------------+----------------+------------------+\n",
       "1 row in set (0.02 sec)"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT *\n",
    "  FROM polling_places\n",
    " WHERE wasted_votes = (SELECT MAX(wasted_votes) AS max_wasted_votes\n",
    "                         FROM polling_places\n",
    "                        WHERE election_type_id = 1) AND\n",
    "       election_type_id = 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Creating a calculated field with sub-queries in a `SELECT` statement\n",
    "\n",
    "Calculating vote percentage in president election."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Calculating vote percentage in president election\n",
    "\n",
    "- First query for the number of total votes(denominator).\n",
    "- Second query for the number of votes by candidates(numerator)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>number_of_total_votes</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>13947506</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.01 sec)"
      ],
      "text/plain": [
       "+-----------------------+\n",
       "| number_of_total_votes |\n",
       "+-----------------------+\n",
       "| 13947506              |\n",
       "+-----------------------+\n",
       "1 row in set (0.01 sec)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT SUM(votes) AS number_of_total_votes\n",
    "  FROM presidents;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>number</th>\n",
       "<th>candidate_id</th>\n",
       "<th>vote_percentage</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1</td>\n",
       "<td>330</td>\n",
       "<td>0.2646</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2</td>\n",
       "<td>331</td>\n",
       "<td>0.4005</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>3</td>\n",
       "<td>329</td>\n",
       "<td>0.3349</td>\n",
       "</tr>\n",
       "</table>\n",
       "3 rows in set (0.02 sec)"
      ],
      "text/plain": [
       "+--------+--------------+-----------------+\n",
       "| number | candidate_id | vote_percentage |\n",
       "+--------+--------------+-----------------+\n",
       "| 1      | 330          | 0.2646          |\n",
       "+--------+--------------+-----------------+\n",
       "| 2      | 331          | 0.4005          |\n",
       "+--------+--------------+-----------------+\n",
       "| 3      | 329          | 0.3349          |\n",
       "+--------+--------------+-----------------+\n",
       "3 rows in set (0.02 sec)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT number,\n",
    "       candidate_id,\n",
    "       ROUND(SUM(votes) / 13947506.0, 4) AS vote_percentage\n",
    "  FROM presidents\n",
    " GROUP BY number;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>number</th>\n",
       "<th>candidate_id</th>\n",
       "<th>vote_percentage</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1</td>\n",
       "<td>330</td>\n",
       "<td>0.2646</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2</td>\n",
       "<td>331</td>\n",
       "<td>0.4005</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>3</td>\n",
       "<td>329</td>\n",
       "<td>0.3349</td>\n",
       "</tr>\n",
       "</table>\n",
       "3 rows in set (0.03 sec)"
      ],
      "text/plain": [
       "+--------+--------------+-----------------+\n",
       "| number | candidate_id | vote_percentage |\n",
       "+--------+--------------+-----------------+\n",
       "| 1      | 330          | 0.2646          |\n",
       "+--------+--------------+-----------------+\n",
       "| 2      | 331          | 0.4005          |\n",
       "+--------+--------------+-----------------+\n",
       "| 3      | 329          | 0.3349          |\n",
       "+--------+--------------+-----------------+\n",
       "3 rows in set (0.03 sec)"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT number,\n",
    "       candidate_id,\n",
    "       ROUND(SUM(votes) / (SELECT SUM(votes)*1.0 AS number_of_total_votes FROM presidents), 4) AS vote_percentage\n",
    "  FROM presidents\n",
    " GROUP BY number;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Sub-queries in a `FROM` statement\n",
    "\n",
    "Another way to combine `GROUP BY`, `SUM()`, and not using `HAVING`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>number</th>\n",
       "<th>candidate_id</th>\n",
       "<th>sum_votes</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2</td>\n",
       "<td>331</td>\n",
       "<td>5586019</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>3</td>\n",
       "<td>329</td>\n",
       "<td>4671021</td>\n",
       "</tr>\n",
       "</table>\n",
       "2 rows in set (0.02 sec)"
      ],
      "text/plain": [
       "+--------+--------------+-----------+\n",
       "| number | candidate_id | sum_votes |\n",
       "+--------+--------------+-----------+\n",
       "| 2      | 331          | 5586019   |\n",
       "+--------+--------------+-----------+\n",
       "| 3      | 329          | 4671021   |\n",
       "+--------+--------------+-----------+\n",
       "2 rows in set (0.02 sec)"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT number,\n",
    "       candidate_id,\n",
    "       sum_votes\n",
    "  FROM (SELECT number,\n",
    "               candidate_id,\n",
    "               SUM(votes) AS sum_votes\n",
    "          FROM presidents\n",
    "         GROUP BY number)\n",
    " WHERE sum_votes > 4500000;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Putting what we have so far all together\n",
    "\n",
    "SQL is about the order of keywords, so follow this convention:\n",
    "\n",
    "```sql\n",
    "SELECT column_names,\n",
    "       CASE WHEN conditions THEN result\n",
    "            ...\n",
    "            ELSE result_else END AS alias\n",
    "  FROM table_name\n",
    " WHERE conditions\n",
    " GROUP BY column_names\n",
    "HAVING aggregated_conditions\n",
    " ORDER BY column_names\n",
    " LIMIT m OFFSET m;\n",
    "```"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "kernelspec": {
   "display_name": "xsql",
   "language": "sqlite",
   "name": "xsql"
  },
  "language_info": {
   "codemirror_mode": "sql",
   "file_extension": "",
   "mimetype": "",
   "name": "mysql",
   "version": "0.2.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}