03-getting-started-with-sql.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Data Visualization with Modern Data Science\n",
    "\n",
    "> Getting Started 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": [
    "## What is SQL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## The definition\n",
    "\n",
    "SQL(pronounced ess-que-ell or sequel) is a language specifically designed with **relational databases**. SQL enables people to create databases, add new data to them, maintain the data in them, and retrieve selected parts of the data via **database management system**. Developed in the 1970s at IBM, SQL has grown and advanced over the years to become the industry standard."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Subcategories of SQL\n",
    "\n",
    "- DML, short for **Data Manipulation Language**.\n",
    "- DDL, short for **Data Definition Language**.\n",
    "- DCL, short for **Data Control Language**.\n",
    "- TCL, short for **Transaction Control Language**."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Subcategories of SQL(Cont'd)\n",
    "\n",
    "- DML: statements begin with `SELECT` `INSERT`, `UPDATE`, `DELETE`...etc.\n",
    "- DDL: statements begin with `CREATE`, `DROP`, `ALTER`...etc.\n",
    "- DCL: statements begin with `GRANT`, `REVOKE`...etc.\n",
    "- TCL: statements begin with `COMMIT`, `ROLLBACK`...etc."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## What will be covered in this course\n",
    "\n",
    "- DML.\n",
    "- A bit of DDL.\n",
    "- ~~DCL.~~\n",
    "- ~~TCL.~~"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Why SQL\n",
    "\n",
    "- SQL gives us improved programmatic control over the structure of data, leading to efficiency, speed, and accuracy.\n",
    "- SQL is also an excellent adjunct to programming languages used in the data sciences, such as R and Python.\n",
    "- For people with no background in programming languages, SQL often serves as an easy-to-understand introduction into concepts related to data structures and programming logic."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## What is a relational database\n",
    "\n",
    "> A relational database is a digital database based on the relational model of data.\n",
    "\n",
    "Source: <https://en.wikipedia.org/wiki/Relational_database>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## What is a database\n",
    "\n",
    "> A database is like a collection of data items (comic books, product orders and player profile).If we define the term more strictly, a database is a **self-describing** collection of **integrated records**. A record is a representation of some physical or conceptual object.\n",
    "\n",
    "Source: <https://www.amazon.com/SQL-Dummies-Computer-Tech/dp/1119527074>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Characteristics of a database\n",
    "\n",
    "- Integrated records: the observations in a database is accompanied by attributes.\n",
    "- Self-describing: a database contains metadata."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## What does integrated records mean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>id</th>\n",
       "<th>county</th>\n",
       "<th>town</th>\n",
       "<th>polling_place</th>\n",
       "<th>vote_tallied_at</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1</td>\n",
       "<td>南投縣</td>\n",
       "<td>中寮鄉</td>\n",
       "<td>191</td>\n",
       "<td>2024-01-13 17:36:44</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2</td>\n",
       "<td>南投縣</td>\n",
       "<td>中寮鄉</td>\n",
       "<td>192</td>\n",
       "<td>2024-01-13 17:18:56</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>3</td>\n",
       "<td>南投縣</td>\n",
       "<td>中寮鄉</td>\n",
       "<td>193</td>\n",
       "<td>2024-01-13 17:04:33</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>4</td>\n",
       "<td>南投縣</td>\n",
       "<td>中寮鄉</td>\n",
       "<td>194</td>\n",
       "<td>2024-01-13 17:44:08</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>5</td>\n",
       "<td>南投縣</td>\n",
       "<td>中寮鄉</td>\n",
       "<td>195</td>\n",
       "<td>2024-01-13 17:57:21</td>\n",
       "</tr>\n",
       "</table>\n",
       "5 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+----+-----------+-----------+---------------+---------------------+\n",
       "| id | county    | town      | polling_place | vote_tallied_at     |\n",
       "+----+-----------+-----------+---------------+---------------------+\n",
       "| 1  | 南投縣 | 中寮鄉 | 191           | 2024-01-13 17:36:44 |\n",
       "+----+-----------+-----------+---------------+---------------------+\n",
       "| 2  | 南投縣 | 中寮鄉 | 192           | 2024-01-13 17:18:56 |\n",
       "+----+-----------+-----------+---------------+---------------------+\n",
       "| 3  | 南投縣 | 中寮鄉 | 193           | 2024-01-13 17:04:33 |\n",
       "+----+-----------+-----------+---------------+---------------------+\n",
       "| 4  | 南投縣 | 中寮鄉 | 194           | 2024-01-13 17:44:08 |\n",
       "+----+-----------+-----------+---------------+---------------------+\n",
       "| 5  | 南投縣 | 中寮鄉 | 195           | 2024-01-13 17:57:21 |\n",
       "+----+-----------+-----------+---------------+---------------------+\n",
       "5 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT * \n",
    "  FROM districts\n",
    " LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## What does self-describing mean\n",
    "\n",
    "> Metadata is \"data that provides information about other data\". In other words, it is \"data about data\".\n",
    "\n",
    "Source: <https://en.wikipedia.org/wiki/Metadata>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Seriously, what the f*** is metadata?\n",
    "\n",
    "![](https://media.giphy.com/media/xT0xeif517lOYwnH2g/giphy.gif)\n",
    "\n",
    "Source: <https://media.giphy.com/media/xT0xeif517lOYwnH2g/giphy.gif>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Metadata of `taiwan_election_2024`"
   ]
  },
  {
   "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": [
    "## Metadata of `taiwan_election_2024.districts`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT * \n",
    "  FROM PRAGMA_TABLE_INFO('districts');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Change table name to view different table's metadata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "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>name</td>\n",
       "<td>VARCHAR(100)</td>\n",
       "<td>0</td>\n",
       "<td>NULL</td>\n",
       "<td>0</td>\n",
       "</tr>\n",
       "</table>\n",
       "2 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   | name | VARCHAR(100) | 0       | NULL       | 0  |\n",
       "+-----+------+--------------+---------+------------+----+\n",
       "2 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT * \n",
    "  FROM PRAGMA_TABLE_INFO('parties');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## What is a database management system\n",
    "\n",
    "> The database management system (DBMS) is the software that interacts with end users, applications, and the database itself to capture and analyze the data.\n",
    "\n",
    "Source: <https://en.wikipedia.org/wiki/Database>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## What is a database management system, the less technical version\n",
    "\n",
    "A database management system (DBMS) bridges the application program(could be a web application, programming environment, or just a client interface) and the database."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## There are a lot of relational database management systems\n",
    "\n",
    "- SQL Server by Microsoft.\n",
    "- MySQL by Oracle.\n",
    "- DB2 by IBM.\n",
    "- PostgreSQL.\n",
    "- **SQLite**.\n",
    "- ...etc."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## What is SQLite\n",
    "\n",
    "> SQLite is a relational database management system (RDBMS). In contrast to many other database management systems, SQLite is not a client–server database engine. Rather, it is embedded into the end program.\n",
    "\n",
    "Source: <https://en.wikipedia.org/wiki/SQLite>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Why SQLite\n",
    "\n",
    "- It is open-sourced.\n",
    "- It is self-contained.\n",
    "- It is light and portable.\n",
    "- I PERSONALLY think it is the best way for newbies to learn SQL on local machine."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Getting SQLiteStudio"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## What is SQLiteStudio\n",
    "\n",
    "> SQLiteStudio is desktop application for browsing and editing SQLite database files. It is aimed for people, who know what SQLite is, or what relational databases are in general.\n",
    "\n",
    "Source: <https://sqlitestudio.pl/about>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 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": [
    "## 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": [
    "## Now we have a SQLite database client that is able to connect to an established database in our own local machine\n",
    "\n",
    "- Database > Add a database.\n",
    "- Browse for existing database on local computer > OK.\n",
    "- Database > Connect to the database."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## A few \"Hello, World!\" like SQL statements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>id</th>\n",
       "<th>county</th>\n",
       "<th>town</th>\n",
       "<th>polling_place</th>\n",
       "<th>vote_tallied_at</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1</td>\n",
       "<td>南投縣</td>\n",
       "<td>中寮鄉</td>\n",
       "<td>191</td>\n",
       "<td>2024-01-13 17:36:44</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2</td>\n",
       "<td>南投縣</td>\n",
       "<td>中寮鄉</td>\n",
       "<td>192</td>\n",
       "<td>2024-01-13 17:18:56</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>3</td>\n",
       "<td>南投縣</td>\n",
       "<td>中寮鄉</td>\n",
       "<td>193</td>\n",
       "<td>2024-01-13 17:04:33</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>4</td>\n",
       "<td>南投縣</td>\n",
       "<td>中寮鄉</td>\n",
       "<td>194</td>\n",
       "<td>2024-01-13 17:44:08</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>5</td>\n",
       "<td>南投縣</td>\n",
       "<td>中寮鄉</td>\n",
       "<td>195</td>\n",
       "<td>2024-01-13 17:57:21</td>\n",
       "</tr>\n",
       "</table>\n",
       "5 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+----+-----------+-----------+---------------+---------------------+\n",
       "| id | county    | town      | polling_place | vote_tallied_at     |\n",
       "+----+-----------+-----------+---------------+---------------------+\n",
       "| 1  | 南投縣 | 中寮鄉 | 191           | 2024-01-13 17:36:44 |\n",
       "+----+-----------+-----------+---------------+---------------------+\n",
       "| 2  | 南投縣 | 中寮鄉 | 192           | 2024-01-13 17:18:56 |\n",
       "+----+-----------+-----------+---------------+---------------------+\n",
       "| 3  | 南投縣 | 中寮鄉 | 193           | 2024-01-13 17:04:33 |\n",
       "+----+-----------+-----------+---------------+---------------------+\n",
       "| 4  | 南投縣 | 中寮鄉 | 194           | 2024-01-13 17:44:08 |\n",
       "+----+-----------+-----------+---------------+---------------------+\n",
       "| 5  | 南投縣 | 中寮鄉 | 195           | 2024-01-13 17:57:21 |\n",
       "+----+-----------+-----------+---------------+---------------------+\n",
       "5 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT *\n",
    "  FROM districts\n",
    " LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>id</th>\n",
       "<th>name</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1</td>\n",
       "<td>中國國民黨</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2</td>\n",
       "<td>中華婦女黨</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>3</td>\n",
       "<td>中華愛國同心黨</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>4</td>\n",
       "<td>中華文化共和黨</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>5</td>\n",
       "<td>中華統一促進黨</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>6</td>\n",
       "<td>中華聯合黨</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>7</td>\n",
       "<td>人民最大黨</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>8</td>\n",
       "<td>人民民主黨</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>9</td>\n",
       "<td>制度救世島</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>10</td>\n",
       "<td>勞動黨</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>11</td>\n",
       "<td>台灣國民黨</td>\n",
       "</tr>\n",
       "</table>\n",
       "11 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+----+-----------------------+\n",
       "| id | name                  |\n",
       "+----+-----------------------+\n",
       "| 1  | 中國國民黨       |\n",
       "+----+-----------------------+\n",
       "| 2  | 中華婦女黨       |\n",
       "+----+-----------------------+\n",
       "| 3  | 中華愛國同心黨 |\n",
       "+----+-----------------------+\n",
       "| 4  | 中華文化共和黨 |\n",
       "+----+-----------------------+\n",
       "| 5  | 中華統一促進黨 |\n",
       "+----+-----------------------+\n",
       "| 6  | 中華聯合黨       |\n",
       "+----+-----------------------+\n",
       "| 7  | 人民最大黨       |\n",
       "+----+-----------------------+\n",
       "| 8  | 人民民主黨       |\n",
       "+----+-----------------------+\n",
       "| 9  | 制度救世島       |\n",
       "+----+-----------------------+\n",
       "| 10 | 勞動黨             |\n",
       "+----+-----------------------+\n",
       "| 11 | 台灣國民黨       |\n",
       "+----+-----------------------+\n",
       "11 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT *\n",
    "  FROM parties\n",
    " LIMIT 11;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 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": [
    "## SQL keywords are NOT case sensitive"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>id</th>\n",
       "<th>county</th>\n",
       "<th>town</th>\n",
       "<th>polling_place</th>\n",
       "<th>vote_tallied_at</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1</td>\n",
       "<td>南投縣</td>\n",
       "<td>中寮鄉</td>\n",
       "<td>191</td>\n",
       "<td>2024-01-13 17:36:44</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2</td>\n",
       "<td>南投縣</td>\n",
       "<td>中寮鄉</td>\n",
       "<td>192</td>\n",
       "<td>2024-01-13 17:18:56</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>3</td>\n",
       "<td>南投縣</td>\n",
       "<td>中寮鄉</td>\n",
       "<td>193</td>\n",
       "<td>2024-01-13 17:04:33</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>4</td>\n",
       "<td>南投縣</td>\n",
       "<td>中寮鄉</td>\n",
       "<td>194</td>\n",
       "<td>2024-01-13 17:44:08</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>5</td>\n",
       "<td>南投縣</td>\n",
       "<td>中寮鄉</td>\n",
       "<td>195</td>\n",
       "<td>2024-01-13 17:57:21</td>\n",
       "</tr>\n",
       "</table>\n",
       "5 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+----+-----------+-----------+---------------+---------------------+\n",
       "| id | county    | town      | polling_place | vote_tallied_at     |\n",
       "+----+-----------+-----------+---------------+---------------------+\n",
       "| 1  | 南投縣 | 中寮鄉 | 191           | 2024-01-13 17:36:44 |\n",
       "+----+-----------+-----------+---------------+---------------------+\n",
       "| 2  | 南投縣 | 中寮鄉 | 192           | 2024-01-13 17:18:56 |\n",
       "+----+-----------+-----------+---------------+---------------------+\n",
       "| 3  | 南投縣 | 中寮鄉 | 193           | 2024-01-13 17:04:33 |\n",
       "+----+-----------+-----------+---------------+---------------------+\n",
       "| 4  | 南投縣 | 中寮鄉 | 194           | 2024-01-13 17:44:08 |\n",
       "+----+-----------+-----------+---------------+---------------------+\n",
       "| 5  | 南投縣 | 中寮鄉 | 195           | 2024-01-13 17:57:21 |\n",
       "+----+-----------+-----------+---------------+---------------------+\n",
       "5 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select *\n",
    "  from districts\n",
    " limit 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Indentations or new lines are OPTIONAL in SQL statements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>id</th>\n",
       "<th>county</th>\n",
       "<th>town</th>\n",
       "<th>polling_place</th>\n",
       "<th>vote_tallied_at</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1</td>\n",
       "<td>南投縣</td>\n",
       "<td>中寮鄉</td>\n",
       "<td>191</td>\n",
       "<td>2024-01-13 17:36:44</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2</td>\n",
       "<td>南投縣</td>\n",
       "<td>中寮鄉</td>\n",
       "<td>192</td>\n",
       "<td>2024-01-13 17:18:56</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>3</td>\n",
       "<td>南投縣</td>\n",
       "<td>中寮鄉</td>\n",
       "<td>193</td>\n",
       "<td>2024-01-13 17:04:33</td>\n",
       "</tr>\n",
       "</table>\n",
       "3 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+----+-----------+-----------+---------------+---------------------+\n",
       "| id | county    | town      | polling_place | vote_tallied_at     |\n",
       "+----+-----------+-----------+---------------+---------------------+\n",
       "| 1  | 南投縣 | 中寮鄉 | 191           | 2024-01-13 17:36:44 |\n",
       "+----+-----------+-----------+---------------+---------------------+\n",
       "| 2  | 南投縣 | 中寮鄉 | 192           | 2024-01-13 17:18:56 |\n",
       "+----+-----------+-----------+---------------+---------------------+\n",
       "| 3  | 南投縣 | 中寮鄉 | 193           | 2024-01-13 17:04:33 |\n",
       "+----+-----------+-----------+---------------+---------------------+\n",
       "3 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT * \n",
    "FROM districts\n",
    "LIMIT 3;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>id</th>\n",
       "<th>county</th>\n",
       "<th>town</th>\n",
       "<th>polling_place</th>\n",
       "<th>vote_tallied_at</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1</td>\n",
       "<td>南投縣</td>\n",
       "<td>中寮鄉</td>\n",
       "<td>191</td>\n",
       "<td>2024-01-13 17:36:44</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2</td>\n",
       "<td>南投縣</td>\n",
       "<td>中寮鄉</td>\n",
       "<td>192</td>\n",
       "<td>2024-01-13 17:18:56</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>3</td>\n",
       "<td>南投縣</td>\n",
       "<td>中寮鄉</td>\n",
       "<td>193</td>\n",
       "<td>2024-01-13 17:04:33</td>\n",
       "</tr>\n",
       "</table>\n",
       "3 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+----+-----------+-----------+---------------+---------------------+\n",
       "| id | county    | town      | polling_place | vote_tallied_at     |\n",
       "+----+-----------+-----------+---------------+---------------------+\n",
       "| 1  | 南投縣 | 中寮鄉 | 191           | 2024-01-13 17:36:44 |\n",
       "+----+-----------+-----------+---------------+---------------------+\n",
       "| 2  | 南投縣 | 中寮鄉 | 192           | 2024-01-13 17:18:56 |\n",
       "+----+-----------+-----------+---------------+---------------------+\n",
       "| 3  | 南投縣 | 中寮鄉 | 193           | 2024-01-13 17:04:33 |\n",
       "+----+-----------+-----------+---------------+---------------------+\n",
       "3 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT * FROM districts LIMIT 3;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Adopt a style guide before writing your own SQL statements\n",
    "\n",
    "- **[SQL style guide by Simon Holywell](https://www.sqlstyle.guide/)**\n",
    "- [SQL Style Guide | GitLab](https://about.gitlab.com/handbook/business-technology/data-team/platform/sql-style-guide/)\n",
    "- [SQL Style Guide - Mozilla Data Documentation](https://docs.telemetry.mozilla.org/concepts/sql_style.html)\n",
    "- ...etc."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Format SQL in SQLiteStudio\n",
    "\n",
    "- Write down your SQL statements.\n",
    "- Highlight statements and right click for drop-down list -> Format SQL."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Beginning with `SELECT` and `FROM`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Basic `SELECT` syntax\n",
    "\n",
    "A SELECT statement that fetches every row and column in a table.\n",
    "\n",
    "```sql\n",
    "SELECT *\n",
    "  FROM table_name;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Selecting a subset of rows and columns\n",
    "\n",
    "It is more practical to limit the rows and columns the query retrieves, especially with large tables.\n",
    "\n",
    "```sql\n",
    "SELECT column_names\n",
    "  FROM table_name\n",
    " LIMIT m OFFSET m;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>id</th>\n",
       "<th>county</th>\n",
       "<th>town</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1</td>\n",
       "<td>南投縣</td>\n",
       "<td>中寮鄉</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2</td>\n",
       "<td>南投縣</td>\n",
       "<td>中寮鄉</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>3</td>\n",
       "<td>南投縣</td>\n",
       "<td>中寮鄉</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>4</td>\n",
       "<td>南投縣</td>\n",
       "<td>中寮鄉</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>5</td>\n",
       "<td>南投縣</td>\n",
       "<td>中寮鄉</td>\n",
       "</tr>\n",
       "</table>\n",
       "5 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+----+-----------+-----------+\n",
       "| id | county    | town      |\n",
       "+----+-----------+-----------+\n",
       "| 1  | 南投縣 | 中寮鄉 |\n",
       "+----+-----------+-----------+\n",
       "| 2  | 南投縣 | 中寮鄉 |\n",
       "+----+-----------+-----------+\n",
       "| 3  | 南投縣 | 中寮鄉 |\n",
       "+----+-----------+-----------+\n",
       "| 4  | 南投縣 | 中寮鄉 |\n",
       "+----+-----------+-----------+\n",
       "| 5  | 南投縣 | 中寮鄉 |\n",
       "+----+-----------+-----------+\n",
       "5 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT id,\n",
    "       county,\n",
    "       town\n",
    "  FROM districts\n",
    " LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>id</th>\n",
       "<th>county</th>\n",
       "<th>town</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>3</td>\n",
       "<td>南投縣</td>\n",
       "<td>中寮鄉</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>4</td>\n",
       "<td>南投縣</td>\n",
       "<td>中寮鄉</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>5</td>\n",
       "<td>南投縣</td>\n",
       "<td>中寮鄉</td>\n",
       "</tr>\n",
       "</table>\n",
       "3 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+----+-----------+-----------+\n",
       "| id | county    | town      |\n",
       "+----+-----------+-----------+\n",
       "| 3  | 南投縣 | 中寮鄉 |\n",
       "+----+-----------+-----------+\n",
       "| 4  | 南投縣 | 中寮鄉 |\n",
       "+----+-----------+-----------+\n",
       "| 5  | 南投縣 | 中寮鄉 |\n",
       "+----+-----------+-----------+\n",
       "3 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT id,\n",
    "       county,\n",
    "       town\n",
    "  FROM districts\n",
    " LIMIT 3 OFFSET 2;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Using `DISTINCT` to find unique values\n",
    "\n",
    "- It is common for a column to contain rows with duplicates.\n",
    "- To understand the range of values in a column, we can use the `DISTINCT` keyword.\n",
    "\n",
    "```sql\n",
    "SELECT DISTINCT column_names\n",
    "  FROM table_name;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>county</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",
       "<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",
       "<tr>\n",
       "<td>雲林縣</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>高雄市</td>\n",
       "</tr>\n",
       "</table>\n",
       "22 rows in set (0.01 sec)"
      ],
      "text/plain": [
       "+-----------+\n",
       "| county    |\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": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT DISTINCT county\n",
    "  FROM districts;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>county</th>\n",
       "<th>town</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",
       "</table>\n",
       "13 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+-----------+-----------+\n",
       "| county    | town      |\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",
       "13 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT DISTINCT county,\n",
    "       town\n",
    "  FROM districts\n",
    " LIMIT 13;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Sorting data with `ORDER BY`\n",
    "\n",
    "We order the results of a query using the keywords `ORDER BY` followed by the name of the column(s) to sort.\n",
    "\n",
    "```sql\n",
    "SELECT column_names\n",
    "  FROM table_name\n",
    " ORDER BY column_names;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>village_id</th>\n",
       "<th>candidate_id</th>\n",
       "<th>votes</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>3551</td>\n",
       "<td>331</td>\n",
       "<td>1</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>3770</td>\n",
       "<td>331</td>\n",
       "<td>2</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2706</td>\n",
       "<td>331</td>\n",
       "<td>3</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>3551</td>\n",
       "<td>330</td>\n",
       "<td>3</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2706</td>\n",
       "<td>330</td>\n",
       "<td>4</td>\n",
       "</tr>\n",
       "</table>\n",
       "5 rows in set (0.04 sec)"
      ],
      "text/plain": [
       "+------------+--------------+-------+\n",
       "| village_id | candidate_id | votes |\n",
       "+------------+--------------+-------+\n",
       "| 3551       | 331          | 1     |\n",
       "+------------+--------------+-------+\n",
       "| 3770       | 331          | 2     |\n",
       "+------------+--------------+-------+\n",
       "| 2706       | 331          | 3     |\n",
       "+------------+--------------+-------+\n",
       "| 3551       | 330          | 3     |\n",
       "+------------+--------------+-------+\n",
       "| 2706       | 330          | 4     |\n",
       "+------------+--------------+-------+\n",
       "5 rows in set (0.04 sec)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT village_id,\n",
    "       candidate_id,\n",
    "       votes\n",
    "  FROM presidents\n",
    " ORDER BY votes\n",
    " LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## By default, `ORDER BY` sorts values in ascending order\n",
    "\n",
    "If we want to sort in descending order, add the `DESC` keyword.\n",
    "\n",
    "```sql\n",
    "SELECT column_names\n",
    "  FROM table_name\n",
    " ORDER BY column_names DESC;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>village_id</th>\n",
       "<th>candidate_id</th>\n",
       "<th>votes</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>4930</td>\n",
       "<td>329</td>\n",
       "<td>890</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>4930</td>\n",
       "<td>329</td>\n",
       "<td>868</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>4534</td>\n",
       "<td>331</td>\n",
       "<td>831</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>965</td>\n",
       "<td>329</td>\n",
       "<td>800</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>74</td>\n",
       "<td>331</td>\n",
       "<td>783</td>\n",
       "</tr>\n",
       "</table>\n",
       "5 rows in set (0.02 sec)"
      ],
      "text/plain": [
       "+------------+--------------+-------+\n",
       "| village_id | candidate_id | votes |\n",
       "+------------+--------------+-------+\n",
       "| 4930       | 329          | 890   |\n",
       "+------------+--------------+-------+\n",
       "| 4930       | 329          | 868   |\n",
       "+------------+--------------+-------+\n",
       "| 4534       | 331          | 831   |\n",
       "+------------+--------------+-------+\n",
       "| 965        | 329          | 800   |\n",
       "+------------+--------------+-------+\n",
       "| 74         | 331          | 783   |\n",
       "+------------+--------------+-------+\n",
       "5 rows in set (0.02 sec)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT village_id,\n",
    "       candidate_id,\n",
    "       votes\n",
    "  FROM presidents\n",
    " ORDER BY votes DESC\n",
    " LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## We are not limited to sorting on just one column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>village_id</th>\n",
       "<th>candidate_id</th>\n",
       "<th>votes</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1</td>\n",
       "<td>329</td>\n",
       "<td>414</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1</td>\n",
       "<td>329</td>\n",
       "<td>341</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1</td>\n",
       "<td>330</td>\n",
       "<td>146</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1</td>\n",
       "<td>330</td>\n",
       "<td>128</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1</td>\n",
       "<td>331</td>\n",
       "<td>67</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1</td>\n",
       "<td>331</td>\n",
       "<td>56</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2</td>\n",
       "<td>329</td>\n",
       "<td>612</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2</td>\n",
       "<td>330</td>\n",
       "<td>239</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2</td>\n",
       "<td>331</td>\n",
       "<td>103</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>3</td>\n",
       "<td>329</td>\n",
       "<td>398</td>\n",
       "</tr>\n",
       "</table>\n",
       "10 rows in set (0.02 sec)"
      ],
      "text/plain": [
       "+------------+--------------+-------+\n",
       "| village_id | candidate_id | votes |\n",
       "+------------+--------------+-------+\n",
       "| 1          | 329          | 414   |\n",
       "+------------+--------------+-------+\n",
       "| 1          | 329          | 341   |\n",
       "+------------+--------------+-------+\n",
       "| 1          | 330          | 146   |\n",
       "+------------+--------------+-------+\n",
       "| 1          | 330          | 128   |\n",
       "+------------+--------------+-------+\n",
       "| 1          | 331          | 67    |\n",
       "+------------+--------------+-------+\n",
       "| 1          | 331          | 56    |\n",
       "+------------+--------------+-------+\n",
       "| 2          | 329          | 612   |\n",
       "+------------+--------------+-------+\n",
       "| 2          | 330          | 239   |\n",
       "+------------+--------------+-------+\n",
       "| 2          | 331          | 103   |\n",
       "+------------+--------------+-------+\n",
       "| 3          | 329          | 398   |\n",
       "+------------+--------------+-------+\n",
       "10 rows in set (0.02 sec)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT village_id,\n",
    "       candidate_id,\n",
    "       votes\n",
    "  FROM presidents\n",
    " ORDER BY village_id,\n",
    "          candidate_id,\n",
    "          votes DESC\n",
    " LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Filtering rows with `WHERE`\n",
    "\n",
    "The `WHERE` keyword allows us to find rows that match a specific value, a range of values, or multiple values.\n",
    "\n",
    "```sql\n",
    "SELECT column_names\n",
    "  FROM table_name\n",
    " WHERE condition;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>county</th>\n",
       "<th>town</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",
       "</table>\n",
       "12 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+-----------+-----------+\n",
       "| county    | town      |\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",
       "12 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT DISTINCT county,\n",
    "       town\n",
    "  FROM districts\n",
    " WHERE county = '臺北市';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## The above query uses equals `=` to find rows that exactly match, but we can use other operators"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Relational and logical operators\n",
    "\n",
    "- `=`: Equal to.\n",
    "- `!=`: Not equal to.\n",
    "- `>`, `>=`: Greater than; Greater than or equal to.\n",
    "- `<`, `<=`: Less than; Less than or equal to.\n",
    "- `BETWEEN`: Within a range.\n",
    "- `IN`: Match one of a set of values.\n",
    "- `LIKE`: Match a pattern.\n",
    "- `NOT`: Negates a condition.\n",
    "- `AND`: Intersects conditions.\n",
    "- `OR`: Union conditions."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Using the `BETWEEN` operator"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>county</th>\n",
       "<th>town</th>\n",
       "<th>polling_place</th>\n",
       "<th>vote_tallied_at</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>宜蘭縣</td>\n",
       "<td>大同鄉</td>\n",
       "<td>421</td>\n",
       "<td>2024-01-13 16:30:49</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>澎湖縣</td>\n",
       "<td>湖西鄉</td>\n",
       "<td>63</td>\n",
       "<td>2024-01-13 16:39:59</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>澎湖縣</td>\n",
       "<td>西嶼鄉</td>\n",
       "<td>98</td>\n",
       "<td>2024-01-13 16:40:11</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>嘉義縣</td>\n",
       "<td>布袋鎮</td>\n",
       "<td>159</td>\n",
       "<td>2024-01-13 16:41:58</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>澎湖縣</td>\n",
       "<td>馬公市</td>\n",
       "<td>53</td>\n",
       "<td>2024-01-13 16:43:04</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>澎湖縣</td>\n",
       "<td>湖西鄉</td>\n",
       "<td>70</td>\n",
       "<td>2024-01-13 16:43:09</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>高雄市</td>\n",
       "<td>燕巢區</td>\n",
       "<td>181</td>\n",
       "<td>2024-01-13 16:43:25</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>臺南市</td>\n",
       "<td>龍崎區</td>\n",
       "<td>1543</td>\n",
       "<td>2024-01-13 16:44:44</td>\n",
       "</tr>\n",
       "</table>\n",
       "8 rows in set (0.01 sec)"
      ],
      "text/plain": [
       "+-----------+-----------+---------------+---------------------+\n",
       "| county    | town      | polling_place | vote_tallied_at     |\n",
       "+-----------+-----------+---------------+---------------------+\n",
       "| 宜蘭縣 | 大同鄉 | 421           | 2024-01-13 16:30:49 |\n",
       "+-----------+-----------+---------------+---------------------+\n",
       "| 澎湖縣 | 湖西鄉 | 63            | 2024-01-13 16:39:59 |\n",
       "+-----------+-----------+---------------+---------------------+\n",
       "| 澎湖縣 | 西嶼鄉 | 98            | 2024-01-13 16:40:11 |\n",
       "+-----------+-----------+---------------+---------------------+\n",
       "| 嘉義縣 | 布袋鎮 | 159           | 2024-01-13 16:41:58 |\n",
       "+-----------+-----------+---------------+---------------------+\n",
       "| 澎湖縣 | 馬公市 | 53            | 2024-01-13 16:43:04 |\n",
       "+-----------+-----------+---------------+---------------------+\n",
       "| 澎湖縣 | 湖西鄉 | 70            | 2024-01-13 16:43:09 |\n",
       "+-----------+-----------+---------------+---------------------+\n",
       "| 高雄市 | 燕巢區 | 181           | 2024-01-13 16:43:25 |\n",
       "+-----------+-----------+---------------+---------------------+\n",
       "| 臺南市 | 龍崎區 | 1543          | 2024-01-13 16:44:44 |\n",
       "+-----------+-----------+---------------+---------------------+\n",
       "8 rows in set (0.01 sec)"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT county,\n",
    "       town,\n",
    "       polling_place,\n",
    "       vote_tallied_at\n",
    "  FROM districts\n",
    " WHERE vote_tallied_at BETWEEN '2024-01-13 16:30:00' AND '2024-01-13 16:45:00'\n",
    " ORDER BY vote_tallied_at;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Using the `IN` operator"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>county</th>\n",
       "<th>town</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",
       "</table>\n",
       "6 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+-----------+-----------+\n",
       "| county    | town      |\n",
       "+-----------+-----------+\n",
       "| 基隆市 | 中山區 |\n",
       "+-----------+-----------+\n",
       "| 基隆市 | 中正區 |\n",
       "+-----------+-----------+\n",
       "| 臺中市 | 大安區 |\n",
       "+-----------+-----------+\n",
       "| 臺北市 | 中山區 |\n",
       "+-----------+-----------+\n",
       "| 臺北市 | 中正區 |\n",
       "+-----------+-----------+\n",
       "| 臺北市 | 大安區 |\n",
       "+-----------+-----------+\n",
       "6 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT DISTINCT county,\n",
    "       town\n",
    "  FROM districts\n",
    " WHERE town IN ('大安區', '中正區', '中山區');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Using `LIKE` with `WHERE`\n",
    "\n",
    "Relational operators are fairly straightforward, but `LIKE` deserves additional explanations."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## `LIKE` lets us search for patterns in strings by using two wildcard characters\n",
    "\n",
    "- Percent sign `%`: A wildcard matching one or more characters.\n",
    "- Underscore `_`: A wildcard matching just one character."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Using `LIKE` with `WHERE` and `%`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>county</th>\n",
       "<th>town</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",
       "</table>\n",
       "10 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+-----------+-----------+\n",
       "| county    | 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": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT DISTINCT county,\n",
    "       town\n",
    "  FROM districts\n",
    " WHERE town LIKE '中%';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Using `LIKE` with `WHERE` and `_`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>county</th>\n",
       "<th>town</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",
       "</table>\n",
       "13 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+-----------+-----------+\n",
       "| county    | town      |\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",
       "13 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT DISTINCT county,\n",
    "       town\n",
    "  FROM districts\n",
    " WHERE town LIKE '大_區';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Combining conditions with `AND` and `OR`\n",
    "\n",
    "We can combine conditions using `AND` and `OR`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Combining conditions with `AND`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>county</th>\n",
       "<th>town</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",
       "</table>\n",
       "8 rows in set (0.00 sec)"
      ],
      "text/plain": [
       "+-----------+-----------+\n",
       "| county    | town      |\n",
       "+-----------+-----------+\n",
       "| 基隆市 | 中山區 |\n",
       "+-----------+-----------+\n",
       "| 基隆市 | 中正區 |\n",
       "+-----------+-----------+\n",
       "| 新北市 | 中和區 |\n",
       "+-----------+-----------+\n",
       "| 桃園市 | 中壢區 |\n",
       "+-----------+-----------+\n",
       "| 臺中市 | 中區    |\n",
       "+-----------+-----------+\n",
       "| 臺北市 | 中山區 |\n",
       "+-----------+-----------+\n",
       "| 臺北市 | 中正區 |\n",
       "+-----------+-----------+\n",
       "| 臺南市 | 中西區 |\n",
       "+-----------+-----------+\n",
       "8 rows in set (0.00 sec)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT DISTINCT county,\n",
    "       town\n",
    "  FROM districts\n",
    " WHERE town LIKE '中%' AND\n",
    "       town LIKE '%區';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Combining conditions with `OR`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>county</th>\n",
       "<th>town</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",
       "</table>\n",
       "20 rows in set (0.01 sec)"
      ],
      "text/plain": [
       "+-----------+-----------+\n",
       "| county    | town      |\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",
       "20 rows in set (0.01 sec)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT DISTINCT county,\n",
    "       town\n",
    "  FROM districts\n",
    " WHERE town LIKE '中_區' OR\n",
    "       town LIKE '大_區';"
   ]
  },
  {
   "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",
    "  FROM table_name\n",
    " WHERE 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
}