intro-to-sql.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "7e3f47c1-a5e8-4c6b-8002-9f1f8159a052",
   "metadata": {},
   "source": [
    "# SSC Data Science and Analytics Workshop 2022\n",
    "\n",
    "### [Intro to Databases in Industry: Data Cleaning, Querying, and Modeling at Scale](https://ssc.ca/en/meetings/annual/2022-annual-meeting/workshops/data-science-and-analytics)\n",
    "\n",
    "Speakers:\n",
    "- **Rodolfo Lourenzutti**, University of British Columbia\n",
    "- **Arman Seyed-Ahmadi**, University of British Columbia\n",
    "- **Diego Ardila**, Shopify\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea599d02-42a9-470d-b841-55b1aa69c1d9",
   "metadata": {},
   "source": [
    "# 1. Introduction"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4d8ac27d-b848-4578-b0d5-b126368e6651",
   "metadata": {},
   "source": [
    "### 1.1 Workshop structure\n",
    "\n",
    "The workshop will be split in two parts. \n",
    "\n",
    "- In the first part, Arman and I will discuss\n",
    "    - what it is and why to use a Database Management Systems (DBMS);\n",
    "    - the basics of SQL queries;\n",
    "    - accessing a database from R;\n",
    "    \n",
    "    \n",
    "    \n",
    "- In the second part, Diego will:\n",
    "   - discuss the concept of a data warehouse;\n",
    "   - introduce the ETL (Extract, Transform, Load) and ELT (Extract, Load, Transform) processes;\n",
    "   - present dimensional modelling as methodology to guide the design of data warehouses at scale.\n",
    "   \n",
    "The workshop will be focused on the user aspect of databases, not on design and implementation. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2cce8242-a9d0-4159-8780-261d22da0ab8",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 1.2 Why use a Database management system (DBMS)?\n",
    "\n",
    "To analyze data, we need to first collect and store the data. In fact, depending on the scale of the dataset, we need much more than that. Curating a large dataset is not just about storing it, we also need to:\n",
    "\n",
    "- keep track of the relationship between the data entities;\n",
    "- avoid redundancies; \n",
    "- manage security issues to protect private and sensitive information;\n",
    "- have an error recovery system in place;\n",
    "- be able to retrieve data in an efficient manner;\n",
    "- have well controlled concurrent access;\n",
    "- keep the data in a consistent state;\n",
    "- enforce data integrity!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bbe62947-26ba-4865-8cca-47d352d7e5ee",
   "metadata": {},
   "source": [
    "### 1.3 Database vs DBMS \n",
    "\n",
    "- A **database is a collection of data about different but related objects** (also called entities in database jargon), each entity with a common set of attributes. For example, a taxi company stores information about:\n",
    "  - Drivers (name, licence number, licence expiration);\n",
    "  - Cars (model, year);\n",
    "  - Clients (name, address, phone, e-mail);\n",
    "\n",
    "\n",
    "- Note that entities also interact among themselves (e.g., a driver drives a car).\n",
    "\n",
    "- A **DBMS is a software** responsible to assist the maintenance and usage of databases (yes, plural!)\n",
    "  - There are many different vendors of **relational** DBMS out there (e.g., PostgreSQL, MS Access, MS SQL Server, MySQL, etc…)\n",
    "  <img src=\"img/flavours_sql.png\" width=\"700\"/>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "909766b6-31bf-46f5-85cd-73961d82ca55",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "**Remember:**\n",
    "    \n",
    "database $\\ne$ database management system\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fabf7a57-8dd7-4b4f-b2dd-1260a8973748",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 1.4 PostgreSQL\n",
    "\n",
    "- In this workshop we will be using PostgreSQL as our RDBMS;\n",
    "\n",
    "\n",
    "- PostgreSQL is an open-source multi-platform (Windows, Linux, macOS) RDBMS with a high level of compliance with ANSI SQL (the langugae that practically all RDBMS use to create, modify and query databases);"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c3857409-00d6-45a6-a854-56813e0dcb70",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 1.5 The client-server model"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee4354f1-58bc-465f-8edf-b88a2cd9630b",
   "metadata": {},
   "source": [
    "Similar to most other DBMSs, Postgres works based on a **client-sever** model. In this model\n",
    "\n",
    "- The DBMS along with its databases and data are all stored on a host computer where the database server resides. This is typically a powerful machine with high processing power and large storage\n",
    "- Client hosts are usually personal computers with GUIs that can connect to a database server to access the data.\n",
    "\n",
    "In this model, the clients and the server are connected over a network. The heavy-lifting of processing, managing and storing large amounts of data is done by the server host, and clients only retrieve the data that they need.\n",
    "\n",
    "<img src=\"img/client-server.png\" width=\"500\"/>\n",
    "\n",
    "> Although sometimes used interchangeably, there is a difference between a **client/server** and a **client/server host**. A host is a device, whereas a client/server is a piece of software. For example, you can simultaneously have multiple client programs connected to a remote database. Similarly, a remote host is a device (i.e. a computer) that might have several server programs running concurrently.\n",
    "\n",
    "The idea of client-server models for databases has become the standard of computation and storage today, known as **cloud computing**:\n",
    "\n",
    "- Today we rarely store movie or music files on our computers. This is why most of us have laptops with only 256/512 GB of space, because most of that takes up space is already provided as a cloud service (e.g. Netflix, Spotify, Youtube), or is stored on cloud storage spaces (e.g. One Drive, Dropbox, Google Drive).\n",
    "- We rarely run production-stage computation-intensive jobs on our own computers. All such computations are done on cloud-computing services (e.g. Google Cloud Platform, Amazon Web Services, Microsoft Azure). I personally haven't run a single simulation code on my own computer, neither ever stored any raw data locally. I use my computer mainly as an interface to access the services that I want.\n",
    "\n",
    "> Note that there are certain situations where one might want to **locally** benefit from the advantages of storing data in a database. A relational database engine that works only with local databases is SQLite. If you're curious to find out the use cases for **SQLite**, take a look [here](https://www.sqlite.org/whentouse.html).\n",
    "\n",
    "Whenever we use Postgres (or any other client-server DBMS), the first step before anything else is to **connect** to the database server. This is why we will talk about _host address_, _port_, _username_, and _password_ when we try to use a database."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "02432046-5af6-4a64-a292-213556a58c61",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 1.5 What is SQL?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "884bd651-7610-4a0b-a0b1-f7b473744eb7",
   "metadata": {},
   "source": [
    "Well, it's finally time to learn about SQL!\n",
    "\n",
    "- SQL stands for Structured Query Language ([or... does it?](https://en.wikipedia.org/wiki/SQL#History)).\n",
    "\n",
    "- It is a language that we use to talk to a relational DBMS.\n",
    "\n",
    "- Originally developed by IBM in 1970s to manipulate and retrieve data stored in their DBMS, System R.\n",
    "\n",
    "- Keep in mind that SQL is not set in stone and the SQL standard is updated from time to time (e.g., 1986, 1992, 1999, 2011, 2016);\n",
    "\n",
    "- There’s no DBMS that fully complies with the standard;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0fe8eeb6-095e-43fe-92d0-b7d9acd5103b",
   "metadata": {
    "tags": []
   },
   "source": [
    "## 2. SQL"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1cc31fcb-a8c2-469a-b14c-4e8f07ce89ca",
   "metadata": {
    "tags": []
   },
   "source": [
    "Suppose that we have the following table in our database, and \n",
    "\n",
    "> we want to retrieve the names and GPAs of students older than 25.\n",
    "\n",
    "|  sid  | name      | login      | age | gpa |\n",
    "|-------|-----------|------------|-----|-----|\n",
    "| 23792 | Arman     | arman@mds  | 28  | 2.5 |\n",
    "| 82347 | Varada    | varada@mds | 29  | 2.9 |\n",
    "| 11238 | Tiffany   | tiff@mds   | 23  | 2.8 |\n",
    "| 87263 | Mike      | mike@mds   | 19  | 3.8 |\n",
    "| 13298 | Joel      | joel@mds   | 25  | 3.2 |\n",
    "| 91287 | Florencia | flor@mds   | 20  | 3.3 |\n",
    "\n",
    "We can write this as the following SQL query:\n",
    "\n",
    "```sql\n",
    "SELECT\n",
    "    name, age, gpa\n",
    "FROM\n",
    "    Students\n",
    "WHERE\n",
    "    age > 25;\n",
    "```\n",
    "\n",
    "Running the above query should return this relation:\n",
    "\n",
    "| name   | age | gpa |\n",
    "|--------|-----|-----|\n",
    "| Arman  | 28  | 2.5 |\n",
    "| Varada | 29  | 2.9 |"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a810ee48-6663-455b-82b8-de7e479d78bf",
   "metadata": {},
   "source": [
    "Let's dissect the different parts of our SQL query here."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "03c2e7db-6967-4e06-9895-3d2e87bb6418",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 2.1 The `SELECT` statement\n",
    "\n",
    "```sql\n",
    "-- A basic SELECT statement\n",
    "-- ===========================\n",
    "SELECT\n",
    "    name, age, gpa  -- column names\n",
    "FROM\n",
    "    Students        -- table name\n",
    "WHERE\n",
    "    age > 25;       -- condition\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "20926548-7854-436d-9e3c-2ce97ab1b838",
   "metadata": {},
   "source": [
    "- A SQL statement usually starts with a verb that describes what the statement is doing, e.g., `SELECT`, `UPDATE`, `INSERT`, `CREATE`, …\n",
    "\n",
    "- SQL is NOT case sensitive: “SELECT”, “select”, and “SeLeCt” are all the same;\n",
    "\n",
    "- A SQL command can use multiple lines;\n",
    "\n",
    "- Don't forget that every SQL statement needs to be terminated with a `;`.\n",
    "\n",
    "- SQL keywords are traditionally written in upper case letters, but that is not a requirement."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8d10b0c1-843a-4c93-b051-43585305e826",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 2.1 How to run SQL in Postgres?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f6abf68f-9d11-4a16-acf9-2223b3c93694",
   "metadata": {
    "tags": []
   },
   "source": [
    "Well, we have a variety of options to run our SQL statements in PostgreSQL:\n",
    "\n",
    "- pgAdmin is the official web-based GUI for interacting with PostgreSQL databases\n",
    "- `psql` is PostgreSQL's interactive command-line interface\n",
    "- `%sql` and `%%sql` magic commands in Jupyter notebooks, which are provided by the `ipython-sql` package\n",
    "- `DBI` and `RPostgres` packages in R;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bf6f4e14-27b0-4cb5-8f66-7482ae331e52",
   "metadata": {
    "tags": []
   },
   "source": [
    "---------------------------\n",
    "### 2.1.1 `psql`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "86017410-2916-4bdf-8c0f-9580e6587f65",
   "metadata": {
    "tags": []
   },
   "source": [
    "This is PostgreSQL's command-line tool that allows us to interactively run SQL statements as well as \"meta\" commands. I introduce a couple of useful `psql` meta commands here, but you can find all the other ones in Postgres documentations [here](https://www.postgresql.org/docs/current/app-psql.html) or a shorter version in this [cheatsheet](http://www.postgresonline.com/downloads/special_feature/postgresql83_psql_cheatsheet.pdf).\n",
    "\n",
    "| Command | Usage                                         |\n",
    "|---------|-----------------------------------------------|\n",
    "| `\\l`    | list all databases                            |\n",
    "| `\\c`    | connect to a database                         |\n",
    "| `\\cd`   | change directory                              |\n",
    "| `\\!`    | execute shell commands                        |\n",
    "| `\\i`    | execute commands from file                    |\n",
    "| `\\d`    | list tables and views                         |\n",
    "| `\\d+`   | list tables and views with additional info    |\n",
    "| `\\dt`   | list tables                                   |\n",
    "| `\\dt+`  | list tables with additional info              |\n",
    "| `\\h`    | view help on SQL commands                     |\n",
    "| `\\?`    | view help on psql meta commands               |\n",
    "| `\\q`    | quit interactive shell                        |"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "856d613c-be3f-4350-8e0c-e46c923f9261",
   "metadata": {},
   "source": [
    "> Note that you don't need to terminate meta commands with `;`."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a960b138-d5df-4cd3-b63a-3d95d8dad914",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 2.1.2 `ipython-sql` (`%sql` and `%%sql`)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b39a962-28ff-4989-857f-d29be35e9da7",
   "metadata": {
    "tags": []
   },
   "source": [
    "`ipython-sql` is a package that enables us to run SQL statements right from a Jupyter notebook. In order to use it, we should load it first:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e4cd94ea-59f0-4a53-b4e2-288b149c566a",
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql\n",
    "%config SqlMagic.displaylimit = 30"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f885ae4-e3a2-468a-a526-e057457c6fbd",
   "metadata": {
    "tags": []
   },
   "source": [
    "Now we need the host address of where the database is stored, along with a username and a password.\n",
    "\n",
    "It is always a bad idea to store login information directly in a notebook or code file because of security reasons. For example, you don't want to commit your sensitive login information to a Git repo.\n",
    "\n",
    "In order to avoid that, it's better to store that kind of information in a separate file (e.g. `credentials.json`) and read the username and password into our IPython session. We will not do this here in this workshop as the DB server will be taken down afterwards.\n",
    "\n",
    "Here are the necessary information for you to log in on the workshop's DB server:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bf624010-6ed1-4de9-a4cb-9fa3e6fad8cc",
   "metadata": {},
   "outputs": [],
   "source": [
    "username = 'ssc_workshop'\n",
    "password = 'sql_for_ds'\n",
    "host = \"ssc-2022-workshop.ct6ghoz7smhy.us-east-1.rds.amazonaws.com\"\n",
    "port = '5432'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "672c0ac9-680a-44f3-9f78-5938d6601183",
   "metadata": {},
   "source": [
    "Now we can establish the connection to the `world` database using the following code:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a9fdad2a-a3d4-46e1-a54b-2dad34efce48",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql postgresql://{username}:{password}@{host}:{port}/world"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8847916e-daf2-4735-bc7c-4d4eb1ba5a7e",
   "metadata": {},
   "source": [
    "Note that we have used the `%sql` line magic to interpret the line in front of it as a magic command. \n",
    "\n",
    "We can also use `%%sql` cell magic to apply the magic to an entire notebook cell."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "30a5a327-680b-43e3-8dda-eda5fe365f85",
   "metadata": {},
   "source": [
    "A limited number of `psql` meta commands (e.g. `\\l`, `d`) can also be executed here. This is made possible through the `pgspecial` package. For example, let's list all databases that exist on our PostgreSQL server:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9ae4f58b-c89d-49af-8492-50144c67c934",
   "metadata": {},
   "outputs": [],
   "source": [
    "# %sql \\l"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee1c87cf-0fd4-4a2a-92c1-f11458dbdae3",
   "metadata": {},
   "source": [
    "Or list the relations (i.e. tables) in the current database:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8db38f49-a336-41b3-b39d-1aa876fb1f9c",
   "metadata": {},
   "outputs": [],
   "source": [
    "#%sql \\d"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0409626d-a858-47cd-b729-acb685e90a2a",
   "metadata": {},
   "source": [
    "---------------------------------------\n",
    "#### Wait, isn't this a workshop? Let's get to work then!!\n",
    "To learn you need to practice! "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c185baa-a650-452d-b014-7961ca0ff9cb",
   "metadata": {},
   "source": [
    "**Exercise 1:**\n",
    "\n",
    "Retrieve the `name` and `population` columns from the `country` table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ad1aa818-700c-4a5b-b0bd-f01ec3b39e60",
   "metadata": {
    "scrolled": true,
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%sql \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bdfe4f49-c3fc-49cf-ba1a-43349aa49cdb",
   "metadata": {},
   "source": [
    "**Exercise 2: `SELECT *`?**\n",
    "\n",
    "What happens if you replace your column list with * in the SELECT statement?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "988cb0e2-66d7-403b-803c-5028b6856b39",
   "metadata": {
    "scrolled": true,
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%sql \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b4c614a9-3d1f-49a8-9074-4865e2ab039b",
   "metadata": {},
   "source": [
    "Use `*` with care, since this may cause an unnecessary burden to your network. \n",
    "Besides, it make your SQL code less readable. If someone is not highly familiar with the database, they might not know what is being returned. \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a5161334-eb6e-40b5-b1c7-a3f2dfa2f7e3",
   "metadata": {
    "tags": []
   },
   "source": [
    "**Exercise 3: `LIMIT` keyword**\n",
    "\n",
    "You can use the `LIMIT` keyword to limit the number of rows returned by the `SELECT` statement. \n",
    "Fill in the SQL query below to retrieve the `name` and `lifeexpectancy` columns from the `country` table, while limiting the number of rows to 10. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6bff2d4c-40e3-46b0-8761-b1e2b47e5239",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "\n",
    "... name, ... \n",
    "   ... country\n",
    "   LIMIT ...;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e8862dd-7347-4daf-91fb-4a43e0c867aa",
   "metadata": {},
   "source": [
    "**Exercise 4: Aliases**\n",
    "\n",
    "As you can see in the previous exercise, `lifeexpectancy` is hard to read. \n",
    "We can rename the columns in the SELECT statement's output by using the `AS` keyword. \n",
    "This will not change the `country` table, only the output of the SELECT statement. \n",
    "\n",
    "Fill in the SQL query below to retrieve the `name` and `lifeexpectancy` columns from the country table. Rename `lifeexpectancy` to `life_expectancy`. Again, limit the output to 15 rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6cc04d43-6433-4b38-930e-aca3f297f920",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "...\n",
    "    name,\n",
    "    ... ... life_expectancy\n",
    "  FROM ...\n",
    "  ...;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "20b4c0f1-c1bb-49eb-bc59-f168f7aefe78",
   "metadata": {},
   "source": [
    "**Exercise 5: `DISTINCT`**\n",
    "\n",
    "What are the continents in the present in the `country` table? We can add the `DISTINCT` modifier to the `SELECT` clause to return only distinct results.  \n",
    "\n",
    "> Note: `DISTINCT` is applied to **all columns** that we list in front of `SELECT`, and returns all distinct combinations of values stored in those columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8b4b9c7b-b8ca-48a8-8924-f4555b47a8f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "... DISTINCT ...\n",
    "  FROM ...;\n",
    "  \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "11b9cce7-1572-46c1-9d6d-42fd60f42f76",
   "metadata": {},
   "source": [
    "**Exercise 6: Filtering with `WHERE`**\n",
    "\n",
    "- The `WHERE` clause allows us to filter the rows we want;\n",
    "- We can specify a condition (a Boolean expression) in the `WHERE` clause to retrieve only the rows for which the condition is true;\n",
    "- Remember the filter verb in dplyr? It is the same idea!!\n",
    "\n",
    "Retrieve the countries with a population higher than 150 million people. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "76321659-c17e-4e5e-8018-8fc30f638d76",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "... ...\n",
    "  FROM ...\n",
    "  ... population > 150000000;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d76c51f7-2f6c-4290-a197-f989df08fed5",
   "metadata": {},
   "source": [
    "- Some comparisons operator to use in `WHERE` clause\n",
    "\n",
    "Operator | Condition |\n",
    "---------|-----------|\n",
    "`<`, `<=`, `=`, `>=`, `>` | ordinal comparisons | \n",
    "`LIKE`, `ILIKE`, `SIMILAR TO` | Pattern Matching | \n",
    "`BETWEEN` | Range filtering | \n",
    "`IN` | check belonginess (similar to `%in% `in R) |\n",
    "`IS NULL` | check if is NULL |"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a4df8d72-1aa5-4025-ae70-85d0ccc622e1",
   "metadata": {},
   "source": [
    "**Exercise 7: derived columns**\n",
    "\n",
    "SQL is able to make operations using columns. \n",
    "\n",
    "Retrieve the `name` column and `gnp_per_capita` by dividing the `gnp` column by the `population` column. \n",
    "Make sure to only calculate this for rows with population higher than 0. Remember to rename the column as `gnp_per_capita`. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ba03f2e8-f745-4589-8610-805ea994a607",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "\n",
    "...\n",
    "    name,\n",
    "    ... ... gnp_per_capita\n",
    "  FROM ...\n",
    "  ... ...\n",
    "  LIMIT 15;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f2b4384-5cd6-4295-afb7-56e1f20de1af",
   "metadata": {},
   "source": [
    "> Remember:  the `SELECT` statement is powerful, but not dangerous. Derived columns returned by Postgres are not saved anywhere, nor do they change existing columns."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "431994d3-af2b-4bd8-9d49-51b686185c4c",
   "metadata": {},
   "source": [
    "**Exercise 8: Arranging with `ORDER BY`**\n",
    "\n",
    "What are the countries with the highest `gnp_per_capita`? \n",
    "You can use the `ORDER BY` clause to rearrange the rows that is returned. By default, it will arrange the rows in ascending order. \n",
    "If you want in descend order you can add the `DESC` keyword. \n",
    "\n",
    "Retrieve the `name` column and `gnp_per_capita` by dividing the `gnp` column by the `population` column. \n",
    "1. Make sure to only calculate this for rows with population higher than 0. \n",
    "2. Remember to rename the column as `gnp_per_capita`. \n",
    "3. Order the rows in descending order. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ab89055a-dd47-401a-afa6-95a09c64e024",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "\n",
    "...\n",
    "    name,\n",
    "    ... ... gnp_per_capita\n",
    "  FROM ...\n",
    "  ... population > 0\n",
    "  ORDER BY ... DESC\n",
    "  LIMIT 15;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f060bf5c-a2fb-4e3d-bc39-570ad5919dbb",
   "metadata": {},
   "source": [
    "> Note: if there’s a draw in `ORDER BY`, the rows are presented in arbitrary order;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "efb7d60a-30ad-4a69-9071-61d059d9c954",
   "metadata": {},
   "source": [
    "---  \n",
    "**Remember:**\n",
    "\n",
    "The order of SQL keywords does matter: `SELECT`, `FROM`, `WHERE`, `ORDER BY`, `LIMIT`\n",
    "    \n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f2ae4d4-e073-4629-bd39-08d6a75a07af",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Data Types\n",
    "\n",
    "We will not cover data types in detail. You can find all data types supported by PostgreSQL [here](https://www.postgresql.org/docs/current/datatype.html).\n",
    "\n",
    "One thing that is worth noting is the `CAST` function, to convert from one data type to another. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5cda444f-0ff2-48fa-ac59-fab3c7c83e5c",
   "metadata": {},
   "source": [
    "**Example**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "75e86031-f9b9-4c16-9f89-7bd602fedbfc",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql \n",
    "\n",
    "SELECT CAST('1.2587465416874' AS NUMERIC(5,2));"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8eb96e95-d1e8-4139-88fe-b86ccf3f93f8",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Nulls"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b480b153-e4e9-4e31-9a64-64affc828ce9",
   "metadata": {},
   "source": [
    "- A null is marker to indicate that the value for a column is unknown, or not entered yet. A null is not equal to 0, or an empty string. In fact, **a null is not even equal to another null**!\n",
    "\n",
    "- In `ORDER BY`, `NULL` values are considered either: the highest or the smallest possible value, depending on the DBMS. PostgreSQL treats `NULL` the highest possible value.\n",
    "\n",
    "- Use `IS NULL` to match `NULL`. Do not try using equality. A `NULL` is not equal to another `NULL`.\n",
    "\n",
    "- Although `NULL`s are not equal to each other, `DISTINC` treats them as if they were!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "af68fae0-28e6-487a-9d48-b5eb93c6e49c",
   "metadata": {},
   "source": [
    "- How different environments show nulls:\n",
    "  - `ipython-sql` -> `None`\n",
    "  - psql -> blank space\n",
    "  - pgAdmin -> `[null]`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d318a662-d2bb-4bea-9da3-2bc2212eaab5",
   "metadata": {},
   "source": [
    "------------------------"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f09b9141-bb4a-4d73-8c9d-102eff6394dd",
   "metadata": {},
   "source": [
    "## `imdb` database \n",
    "Let's continue exploring the `WHERE` clause, but to keep things interesting, let's use another database. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7f66513e-3f87-487a-be3d-e8f1556fb241",
   "metadata": {},
   "outputs": [],
   "source": [
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1e41a374-c059-4d94-9abb-a36373c02b33",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%sql postgresql://{username}:{password}@{host}:{port}/imdb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "58caca8d-5758-4e0f-865f-0067db599a8c",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "/* Lets take a look at the movies table */\n",
    "\n",
    "SELECT *\n",
    "  FROM movies;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60f2f315-0105-43d2-938b-2b648af14bd6",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Pattern matching"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c4394868-9b65-42f5-88d5-5f844f4b1c13",
   "metadata": {},
   "source": [
    "It is a quite common situation that we want to find rows for which the values of one or more columns match a particular pattern. In SQL, this can be done either using `LIKE` or by using regular expressions. The syntax is as follows:\n",
    "\n",
    "```sql\n",
    "SELECT\n",
    "    column1, column2\n",
    "FROM\n",
    "    table1\n",
    "WHERE\n",
    "    column1 [NOT] LIKE '<pattern>'\n",
    ";\n",
    "```\n",
    "\n",
    "Postgres provides us with two wild-cards that we can use with `LIKE`:\n",
    "- `%` matches any string of characters\n",
    "- `_` matches a single character.\n",
    "\n",
    "Pattern matching with `LIKE` is case sensitive; however, Postgres also provides the `ILIKE` keyword that has the same functionality as `LIKE` but is case-insensitive (not part of standard SQL).\n",
    "\n",
    "> **Note:** With `LIKE` or `ILIKE`, the entire string should match the pattern."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f7a3c652-ad09-416a-bd71-ce5c8b6c7408",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "**Example:** Retrieve those movies from the `movie` table whose title contains the word `'violin'` (note that `LIKE` is picky about letter cases in strings!)\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dba64f09-19ff-45cb-b039-12efaff56f79",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    *\n",
    "FROM\n",
    "    movies\n",
    "WHERE\n",
    "    title ILIKE '%Violin%'\n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c27fae4-3f7c-492b-b7c3-ef02f7f9a533",
   "metadata": {},
   "source": [
    "\n",
    "**Exercise 9:** \n",
    "\n",
    "Retrieve those movies from the `movie` table whose title starts with the word `'Zero'`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "289fbea2-e681-40db-8883-f8423943a17a",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%sql\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "258171b4-1551-4877-bbf2-3b4eac0721ba",
   "metadata": {},
   "source": [
    "**Exercise 10:** \n",
    "\n",
    "Retrieve those movies from the `movie` table whose title is 4 letters long and ends with the letter `'e'`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4d82a2aa-d449-43f8-ac0f-55f0b0623b05",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%sql\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "293a8535-53fa-4649-9443-2f37339a4c0c",
   "metadata": {
    "tags": []
   },
   "source": [
    "## The relational model"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1a73c8df-4b30-47d4-a482-bc440f0b1c8e",
   "metadata": {},
   "source": [
    "SQL databases are based on the so-called relational model, it is based on the set theory in mathematics was introduced by by Edgar Codd (IBM) in 1970 ([more details here](https://en.wikipedia.org/wiki/Relational_model)). \n",
    "It's foundations in **set theory** is the reason you will here words like \"tuples\", \"domain\", \"union\", \"cross product\", etc.\n",
    "\n",
    "We don't have time to cover the relational model in detail. But there are three things that you should keep in mind:\n",
    "\n",
    "\n",
    "- **All operations** your perform in SQL result in a table (it could be an empty table).\n",
    "\n",
    "\n",
    "- Primary keys: is one or more columns which uniquely identify each row (i.e., they are different for all rows); \n",
    "  - Examples: id, driver's license, SIN, license plate\n",
    "\n",
    "\n",
    "- Foreign key: is one or more columns that uniquely identify each row of another table. For example\n",
    "  - `id` in the `movies` table uniquely identify the movies;\n",
    "  - `id` in the `names` table uniquely identify people; \n",
    "  - the `acting_roles` table relates people to movies using these primary keys to guarantee that there is no people working on a movie that doesn't exist, nor movies with people that don't exist (referential integrity);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f5a51f80-a21a-43c6-a9d4-e4404cae3108",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql select * from names LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "51383541-3184-44e4-95c2-671a0209b5db",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql select * from movies LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dfd02aa5-7089-4a1a-937c-af65ef8d7922",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql select * from acting_roles LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b077ca7c-0e8a-420c-9d4e-989d9fbb8e86",
   "metadata": {},
   "source": [
    "There are also other useful models, which are less structured, which are known as `NoSQL` Databases. But this is outside the scope of this workshop."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "934001c0-63a1-4912-8278-664d367b751b",
   "metadata": {},
   "source": [
    "## Aggregations"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ab54bf67-15b0-444e-a573-d636836defa5",
   "metadata": {},
   "source": [
    "So far, we have seen many functions for various purposes. For example, `ROUND()` and `SQRT()` for math operations or `CHAR_LENGTH` and `SUBSTR()` for manipulating strings. As you may have noticed, these functions produce an individual output for each and every row of a column in an element-wise manner.\n",
    "\n",
    "There is also a small class of useful functions in SQL called **aggregation** functions, which operate on groups of rows and summarize the data stored in those rows in the form of a single value. Here is a list of standard aggregation functions in SQL:\n",
    "\n",
    "| Function   | What it computes                    |\n",
    "|------------|-------------------------------------|\n",
    "| `COUNT(*)` | Count of all rows in a table        |\n",
    "| `COUNT()`  | Count of non-null rows of a column  |\n",
    "| `MIN()`    | Minimum value in a column           |\n",
    "| `MAX()`    | Maximum value in a column           |\n",
    "| `AVG()`    | Average of values in a column       |\n",
    "| `SUM()`    | Total sum of values in a column     |\n",
    "\n",
    "A couple of points to remember:\n",
    "\n",
    "- Except for `COUNT(*)`, all aggregation functions ignore `NULL`s\n",
    "- In addition to numbers, `MIN()` and `MAX()` also work with strings."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ee68b48-f8bb-4c35-ab91-ae7090fcc992",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "**Example:** Find the population of the world according to the `country` table in the `world` database.\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7f01b9fb-83e6-4b6a-8850-2456155c4d84",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql postgresql://{username}:{password}@{host}:{port}/world"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8f1de486-9606-4681-9491-329c74775129",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    SUM(population)\n",
    "FROM\n",
    "    country\n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "04371563-03f1-4228-a9fc-c6d354c66288",
   "metadata": {},
   "source": [
    "There are a few things that we need to remember when using aggregation functions:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9e9dadd0-e36e-4e04-b73f-fe4a00058ec2",
   "metadata": {},
   "source": [
    "- It is valid to have multiple aggregations in a SQL query, but it is NOT possible have both aggregations and regular columns in a single query:\n",
    "\n",
    "```sql\n",
    "-- This is CORRECT:\n",
    "SELECT\n",
    "    AVG(lifeexpectancy), SUM(population)\n",
    "FROM\n",
    "    country\n",
    "WHERE\n",
    "    continent = 'North America'\n",
    ";\n",
    "\n",
    "-- This is WRONG:\n",
    "SELECT\n",
    "    AVG(lifeexpectancy), name\n",
    "FROM\n",
    "    country\n",
    "WHERE\n",
    "    continent = 'North America'\n",
    ";\n",
    "```\n",
    "\n",
    "There is only one exception to the latter rule, and that's when we have a `GROUP BY` clause (we'll learn about that in a bit)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "853d8140-488f-49ff-928c-7d75a68aa0e2",
   "metadata": {},
   "source": [
    "- An aggregation function CANNOT be used in the `WHERE` clause, because in SQL `WHERE` processes rows before aggregations. For example, we can't find the name of countries with above-average populations using the following query:\n",
    "\n",
    "```sql\n",
    "-- This is WRONG:\n",
    "SELECT\n",
    "    name\n",
    "FROM\n",
    "    country\n",
    "WHERE\n",
    "    population > AVG(population)\n",
    ";\n",
    "```\n",
    "\n",
    "It is, of course, possible to write a query to answer the above question, but we have to wait until we learn about subqueries!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cfac63f0-c4f8-4072-8c88-b0ae5e97a8b8",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Grouping\n",
    "\n",
    "If we divide a table into groups of rows based on values of one or more columns, that is called **grouping**. For example, in the `country` table of the `world` database, we find several countries located in the same continent. In this situation, we can group the rows in our `country` table based on the values in the `continent` column. In this way, we would end up with bunch of \"sub-tables\": A sub-table for all rows where `continent = 'Asia'`, another sub-table for all rows where `continent = 'Europe'`, and so on.\n",
    "\n",
    "The formal syntax of the grouping operation in SQL looks like this:\n",
    "\n",
    "```sql\n",
    "SELECT\n",
    "    grouping_columns, aggregated_columns\n",
    "FROM\n",
    "    table1\n",
    "WHERE\n",
    "    condition\n",
    "GROUP BY\n",
    "    grouping_columns\n",
    "ORDER BY\n",
    "    grouping_columns\n",
    "```\n",
    "\n",
    "Typically, it is not the sub-tables themselves that we're interested in, but some sort of summary statistics:\n",
    "For example, we might want to know the average population for each continent, i.e. for each sub-table or group. In order to do this, we can use aggregation functions that learned about them in the previous section. The question of **\"what is the average population of countries in each continent\"** can be asked in SQL terms as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0cc50c0d-f719-43b2-a962-28859ce281f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    continent, AVG(population)\n",
    "FROM\n",
    "    country\n",
    "GROUP BY\n",
    "    continent\n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc6e10db-c0d4-419d-8041-aefe4c15bb9f",
   "metadata": {},
   "source": [
    "**Important points:**\n",
    "\n",
    "1. If your SQL query involves\n",
    "  - grouping,\n",
    "  - filtering with `WHERE`, and\n",
    "  - sorting with `ORDER BY`,\n",
    "  \n",
    "then the `GROUP BY` clause MUST appear between `WHERE` and `ORDER BY`.\n",
    "\n",
    "2. There can't be any non-aggregated column in a grouping query, except for the columns which are used for grouping (remember the exception I talked about with aggregation functions?). In other words, a non-aggregated column in the `SELECT` clause MUST appear in the `GROUP BY` clause as well.\n",
    "\n",
    "3. If there are null values in the grouping column, there will be a separate group for null values in the results."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a95b86a-ce2b-47be-aa9c-0689c250084d",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-info\" style=\"color:black\">\n",
    "\n",
    "<font size=\"+2\">**Activity (3 min)**</font>\n",
    "\n",
    "Write a query to return the average and maximum population of cities in the `city` table for China, India, Canada, US, Australia, and Russia. The corresponding country codes are `CHN, IND, CAN, USA, AUS` and `RUS`.\n",
    "\n",
    "Show the results for each country using the corresponding country code."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "492dc984-5a31-41e0-a420-473d9af07d1d",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    countrycode,\n",
    "    AVG(population),\n",
    "    MAX(population)\n",
    "FROM\n",
    "    city\n",
    "WHERE\n",
    "    countrycode IN ('CHN', 'IND', 'CAN', 'USA', 'AUS', 'RUS')\n",
    "GROUP BY\n",
    "    countrycode\n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f8b43a7-98ea-4611-a523-b57553390ccc",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Filtering revisited: the `HAVING` clause\n",
    "\n",
    "So far, we have used the `WHERE` clause to filter rows. However, I mentioned before that aggregation functions cannot be used inside a `WHERE` clause.\n",
    "\n",
    "There is another reserved keyword, `HAVING`, for when we need to do filtering using aggregated values. The syntax is as follows (order is important!):\n",
    "\n",
    "```sql\n",
    "SELECT\n",
    "    grouping_columns, aggregated_columns\n",
    "FROM\n",
    "    table1\n",
    "[WHERE\n",
    "    condition]\n",
    "GROUP BY\n",
    "    grouping_columns\n",
    "HAVING\n",
    "    group_condition\n",
    "[ORDER BY\n",
    "    grouping_columns]\n",
    "```\n",
    "\n",
    "To summarize:\n",
    "\n",
    "- `WHERE` filters rows **before** grouping (or any other operation)\n",
    "- `HAVING` filters groups **after** grouping"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d96cf75e-05ca-4ada-a765-59f8d2e08c7e",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "**Example:**\n",
    "\n",
    "Write a query to return the average and maximum population of cities for countries that have at least 60 cities listed in the `city` table.\n",
    "\n",
    "Show the results for each country using the corresponding country code. Order groups by the number of cities of each country in descending order. Also, convert the returned values to integer type.\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4a6c1126-850b-4cc3-a5c0-7ad837af5f1d",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    countrycode,\n",
    "    AVG(population)::INT,\n",
    "    MAX(population)::INT,\n",
    "    COUNT(population) AS count\n",
    "FROM\n",
    "    city\n",
    "GROUP BY\n",
    "    countrycode\n",
    "HAVING\n",
    "    COUNT(*) > 60\n",
    "ORDER BY\n",
    "    count DESC\n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d83ef538-e93a-4434-acd5-1624ea467a6c",
   "metadata": {},
   "source": [
    "Note that just like with the `WHERE` clause, the expression used for filtering with `HAVING` does not necessarily need to appear in the `SELECT` clause. For instance, the `HAVING` clause will still do its job even if `COUNT(population)` is omitted from the `SELECT` clause:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "34cb9c2b-8f73-4c8a-bb1b-90302d0d1e19",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    countrycode,\n",
    "    AVG(population)::int,\n",
    "    MAX(population)::int\n",
    "FROM\n",
    "    city\n",
    "GROUP BY\n",
    "    countrycode\n",
    "HAVING\n",
    "    COUNT(*) > 60\n",
    "ORDER BY\n",
    "    COUNT(*) DESC\n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b0150a5-c8b1-42b7-abb0-42ec5f6a5a70",
   "metadata": {},
   "source": [
    "**Note:** A `GROUP BY` clause can be considered as equivalent to using `DISTINCT` if no aggregate functions are used:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "946bfe54-25e6-4a19-97be-d2822997cc36",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    continent\n",
    "FROM\n",
    "    country\n",
    "GROUP BY\n",
    "    continent\n",
    ";"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "30461e98-1d83-444d-96b9-eac75e49d1c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    DISTINCT continent\n",
    "FROM\n",
    "    country\n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4672b68b-c818-4da2-912c-ad25d9cded03",
   "metadata": {},
   "source": [
    "**Note:** Neither `GROUP BY` nor `DISTINCT` ignore null values."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e974b22-00e1-4265-a881-b56253a0b3a9",
   "metadata": {},
   "source": [
    "As long as they are aggregated, columns appearing in the `HAVING` clause don't necessarily need to be present in the `SELECT` clause. For example, here we're retrieving the name of continents having at least 40 countries:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0cc645c4-476f-4a96-a8fd-4f809128b9ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    continent\n",
    "FROM\n",
    "    country\n",
    "GROUP BY\n",
    "    continent\n",
    "HAVING\n",
    "    COUNT(*) >= 40\n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2c9bc26b-a795-44c3-b133-7a3ea9bf0b23",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Joins\n",
    "\n",
    "Joins are probably the most fundamentally important operation in relational databases.\n",
    "\n",
    "The reason is that the whole idea of such databases is that data can be broken down into various tables that are related to each other, and can be joined together whenever related information from multiple tables is required. Consider the following query as an example:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c59c949f-766b-4eee-93c7-660ca2d7cc08",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "**Example:** Write a query that returns the name of all countries along with their corresponding continents and their cities.\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0d9dd1ec-ff96-4a20-8a83-a5f0e5b991e4",
   "metadata": {},
   "source": [
    "As we've been working with the `world` database, we immediately notice that information about countries and cities are stored in two different tables, so we should somehow **combine** or **_join_** the data from the two tables. \n",
    "\n",
    "The syntax for a joining tables in SQL is as follows:\n",
    "\n",
    "```sql\n",
    "SELECT\n",
    "    columns\n",
    "FROM\n",
    "    left_table\n",
    "join_type\n",
    "    right_table\n",
    "ON\n",
    "    join_condition\n",
    "WHERE\n",
    "    row_filter\n",
    "GROUP BY\n",
    "    columns\n",
    "HAVING\n",
    "    group_filter\n",
    "ORDER BY\n",
    "    columns\n",
    ";\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c133a993-55fe-4785-88f4-e3a9d9f4d2e8",
   "metadata": {},
   "source": [
    "In this section, we'll learn how to do a **join** to answer the question we posed for the `world`, but I prefer to use a smaller database called `faculty` to demonstrate various joining methods first, and then use our larger databases."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6ef19b3f-0b3e-48e5-a40d-eb2f2f22b937",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql postgresql://{username}:{password}@{host}:{port}/faculty"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "22b5911b-7a4d-4b4a-b1d4-249e1061bec5",
   "metadata": {},
   "source": [
    "Let's take a look at some tables in this database:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "72792589-238b-4873-b2f4-d5a275e10eb7",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT * FROM instructor;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ed635bef-ae47-454e-a15f-b9d930647b77",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT * FROM instructor_course;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d5cb9c9-ddfe-437c-9ef1-c057df63e31e",
   "metadata": {},
   "source": [
    "### Cross join\n",
    "\n",
    "A cross join is the simplest way to join two tables:\n",
    "\n",
    "By cross-joining tables A and B, we match each every row from table A with every row from table B.\n",
    "\n",
    "In other words, a cross-join returns all combinations of rows from table A and table B. This type of join is also sometimes called _the Cartesian product_ of two relations or tables:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "761148c5-aeef-45f8-9e23-ddc15d02e550",
   "metadata": {},
   "outputs": [],
   "source": [
    "%config SqlMagic.displaylimit = 200"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1ef704a7-0105-4c2a-b350-2b6e8d92705e",
   "metadata": {
    "scrolled": true,
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    *\n",
    "FROM\n",
    "    instructor\n",
    "CROSS JOIN\n",
    "    instructor_course\n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "84d23feb-e48b-45ad-8982-743901af9ed1",
   "metadata": {},
   "source": [
    "**How to deal with ambiguous column names**\n",
    "\n",
    "Now suppose that we want to return only the names of the instructors and their IDs from the `instructor` table, and names of courses and their IDs from the `course` table. Since there is a column named `id` in both tables, we cannot use `id` in the `SELECT` clause, because it is ambiguous.\n",
    "\n",
    "In this situation, we should either prepend the column name by the full name of its parent table (e.g. `instructor.id`), or we can create table aliases using the keyword `AS` (just like we did before with columns) and prepend the column name with the parent table alias. A table name followed by a dot and the name of a column is called a _qualified name_. Here is an example of using qualified names for ambiguous column names:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4a8c0dc3-b86f-4793-9b73-cec5d03b3f11",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    name, i.id, course, ic.id\n",
    "FROM\n",
    "    instructor AS i\n",
    "CROSS JOIN\n",
    "    instructor_course AS ic\n",
    "LIMIT 10\n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f8799902-5d68-4c22-8a6c-0aec6c6fd000",
   "metadata": {},
   "source": [
    "- The keyword `AS` can be dropped\n",
    "- Table aliases only exist during the execution of a statement\n",
    "- Using table aliases is a great way to reduce clutter in SQL join statements\n",
    "- Once you create an alias for a table, you should only use the alias to refer to that table in the statement. For example, the following query would throw an error:\n",
    "\n",
    "```sql\n",
    "-- This is WRONG\n",
    "SELECT\n",
    "    instructor.name, instructor.id, course, ic.id\n",
    "FROM\n",
    "    instructor AS i\n",
    "CROSS JOIN\n",
    "    instructor_course AS ic\n",
    ";\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "358fc639-cfc6-4bfb-8a6d-b5e2c7b280d4",
   "metadata": {},
   "source": [
    "### Inner join\n",
    "\n",
    "Except for a cross join, all other types of joins use a condition using the `ON` keyword to figure out which rows from the two tables to pair up. An inner join is a type of join that only returns the matching rows from the left and right tables. The image below ([source](https://www.postgresqltutorial.com/postgresql-joins/)) shows Venn diagram of an inner join:\n",
    "\n",
    "<img src=\"img/inner_join.png\" width=\"250\"/>\n",
    "\n",
    "For example, in our `instructor` table there are some instructors who are assigned one or more courses in the `instructor_course` table, some who are not. Similarly, there are courses in the `instructor_course` table that have an instructor, and some that don't have an instructor yet. With an inner join based on `instructor.id` and `instructor_course.id` columns, we would retrieve matching rows, meaning that only instructors are retrieved that have one or more assigned courses, and vice versa:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "16c25455-1885-4da9-918e-af002584877c",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    name, i.id, ic.instructor_id, course\n",
    "FROM\n",
    "    instructor AS i\n",
    "INNER JOIN\n",
    "    instructor_course AS ic\n",
    "ON\n",
    "    i.id = ic.instructor_id\n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad3b7ffa-77f1-410a-be63-2ec1ea3f00d1",
   "metadata": {},
   "source": [
    "In the above returned table, \"Quan\" and \"Varada\" are missing as instructors since they are not yet assigned any courses. Also, the courses \"Web & Cloud Computing\", \"Parallel Computing\", and \"Introduction to Optimization\" are missing, since there not yet any instructors assigned for these courses.\n",
    "\n",
    "> **Note:** The `INNER` keyword is optional."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9469c970-4f81-4e1e-865d-48dcb66155a6",
   "metadata": {},
   "source": [
    "### Natural join\n",
    "\n",
    "For joins involving a join condition, e.g. inner or self joins, we have so far explicitly specified the matching condition. In a situation that columns in different tables have the same name and we want to simply match rows with similar values **in all similarly named columns**, we can do a **natural join** using the keywords `NATURAL JOIN`. For example, the `id` column in the `course_cohort` refers to the `id` column let's find which courses are offered for which cohorts using a natural join:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6faa5df1-baee-40b1-a29c-be987267ab6e",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT * FROM course_cohort;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3a9ac1a7-d727-4b72-b83b-6bceb24d0cd3",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    ic.course, cc.cohort\n",
    "FROM\n",
    "    instructor_course ic\n",
    "NATURAL JOIN\n",
    "    course_cohort cc\n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b8fe960e-efe6-4bff-a4cd-bc603f124ab8",
   "metadata": {},
   "source": [
    "If there are no matching columns in the two tables, `NATURAL JOIN` acts like `JOIN ... ON TRUE` and results in a cross-product join between the participating tables."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f465c9b-aaea-4785-ba7f-70b01a134d56",
   "metadata": {},
   "source": [
    "### Outer joins"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ba66cddd-2f18-4570-8af0-4cca0b76e261",
   "metadata": {},
   "source": [
    "An outer join is a type of join that returns all the rows from one or both of the tables that takes part in the joining.\n",
    "\n",
    "**Outer joins** are useful in questions that involve **missing values**."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "22e676dd-bc52-40c9-9e33-44073bee1b22",
   "metadata": {},
   "source": [
    "#### Left outer join"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "440c6849-bb11-4472-9b71-083a55d1c343",
   "metadata": {},
   "source": [
    "In the joining process, the first table from which data is retrieved using `SELECT` is called the **left** table, and the table that is joined onto that is called the **right** table. In other words, the first table that appears in the query is the left table (table on the left of the query), and the one appearing later is the right table (table on the right of the query).\n",
    "\n",
    "A left outer join is a type of join that returns all rows from the left table (matching or not), in addition to the matching rows from both tables. The non-matching rows from the left table are assigned null values in the columns that belong to the \n",
    "right table. This is schematically shown in the diagram below ([source](https://www.postgresqltutorial.com/postgresql-joins/)):\n",
    "\n",
    "<img src=\"img/left_join.png\" width=\"250\"/>\n",
    "\n",
    "For example, in the [inner join](#Inner-join) example, instructors who don't teach any course are not returned by the join operation. Let's say we want to retrieve a list of all instructors and the courses they teach, as well as those who don't teach any courses:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "70ae52fc-110d-4f1e-b0df-90ffa0dcac5d",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    name, i.id, ic.instructor_id, course\n",
    "FROM\n",
    "    instructor AS i\n",
    "LEFT OUTER JOIN\n",
    "    instructor_course AS ic\n",
    "ON\n",
    "    i.id = ic.instructor_id\n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ff4e9b26-ec31-49af-9fa6-c95336282716",
   "metadata": {},
   "source": [
    "> **Note:** The keyword `OUTER` is optional."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4d12cae4-01c4-494a-b7dd-e7fac56d27dc",
   "metadata": {},
   "source": [
    "How can this be helpful? As an example, we can return the name of instructors who don't teach any courses with the following query:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d88b2768-e3a7-4601-8e6e-c009d25ec8f4",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    name\n",
    "FROM\n",
    "    instructor AS i\n",
    "LEFT JOIN\n",
    "    instructor_course AS ic\n",
    "ON\n",
    "    i.id = ic.instructor_id\n",
    "WHERE\n",
    "    ic.course IS NULL\n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4afd5842-56a1-4bc3-a7ae-0f33c0861ee3",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### Right outer join"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c900e8d-4ca8-4876-b76d-4b0dea54f3bb",
   "metadata": {},
   "source": [
    "A right join acts exactly in the same way as a left join, except that it keeps all rows from the right table and only the matching ones from the left table. The diagram below demonstrates a right join schematically ([source](https://www.postgresqltutorial.com/postgresql-joins/)):\n",
    "\n",
    "<img src=\"img/right_join.png\" width=\"250\"/>\n",
    "\n",
    "Let's retrieve a list of all courses and their appointed instructors, as well as those courses without an instructor:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8bd5e69e-3a8c-4705-992d-e2270ca35a3a",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    name, i.id, ic.instructor_id, course\n",
    "FROM\n",
    "    instructor AS i\n",
    "RIGHT OUTER JOIN\n",
    "    instructor_course AS ic\n",
    "ON\n",
    "    i.id = ic.instructor_id\n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2d3fc7e2-d9e7-4ac8-a37f-d299ac6f8b4e",
   "metadata": {},
   "source": [
    "Now, let's find out which courses do not have an appointed instructor yet:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "166210fd-47b3-4769-9af5-f41545cd0ef8",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    course\n",
    "FROM\n",
    "    instructor AS i\n",
    "RIGHT OUTER JOIN\n",
    "    instructor_course AS ic\n",
    "ON\n",
    "    i.id = ic.instructor_id\n",
    "WHERE\n",
    "    i.id IS NULL\n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "82a02559-b6d2-4c4d-9658-7b4704942281",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### Full outer join\n",
    "\n",
    "A full outer join is the combination of a left and right join: it retrieves **matching and non-matching** rows from **both** tables. Take a look at the schematic diagram of a full outer join ([source](https://www.postgresqltutorial.com/postgresql-joins/)):\n",
    "\n",
    "<img src=\"img/full_outer_join.png\" width=\"250\"/>\n",
    "\n",
    "Let's do a full outer join between the `instructor` and `instructor_course` tables to retrieve all instructors and courses:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d5453628-5628-4f07-aabf-1fd21456fa2a",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    name, i.id, ic.instructor_id, course\n",
    "FROM\n",
    "    instructor AS i\n",
    "FULL OUTER JOIN\n",
    "    instructor_course AS ic\n",
    "ON\n",
    "    i.id = ic.instructor_id\n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f67f5c0-8b38-42f4-a08a-6b4ddc456ddd",
   "metadata": {},
   "source": [
    "We can now write a query to find instructors who are free to teach a course, and courses that need an instructor:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "551a7c11-aac2-4148-b790-560d1a34b8f6",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    name, course\n",
    "FROM\n",
    "    instructor AS i\n",
    "FULL OUTER JOIN\n",
    "    instructor_course AS ic\n",
    "ON\n",
    "    i.id = ic.instructor_id\n",
    "WHERE\n",
    "    i.name IS NULL\n",
    "    OR\n",
    "    ic.course IS NULL\n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37ce8b44-8adb-4bf8-ba5a-e66b47763916",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "**Question:** What's the difference between a cross join and a full outer join?\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bbcdfc7b-f05a-4ce8-80a9-16e1b3a317ca",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-info\" style=\"color:black\">\n",
    "\n",
    "<font size=\"+2\">**Activity (3 min)**</font>\n",
    "    \n",
    "Write a query that returns the name of countries and the number of their listed cities, sorted by the latter in descending order. Your query should only return those countries with more than 80 listed cities."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d860c672-b3a2-4a16-a3d3-8d1eb692beb4",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql postgresql://{username}:{password}@{host}:{port}/world"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e336cd86-606d-4bce-bb34-f8dfc8a0dc93",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    c.name, COUNT(*)\n",
    "FROM\n",
    "    country c\n",
    "JOIN\n",
    "    city ci\n",
    "ON\n",
    "    c.code = ci.countrycode\n",
    "GROUP BY\n",
    "    c.name\n",
    "HAVING\n",
    "    COUNT(*) > 80\n",
    "ORDER BY\n",
    "    COUNT(*) DESC\n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8897a165-8b03-41e1-b30b-3e0368b0f428",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-info\" style=\"color:black\">\n",
    "\n",
    "<font size=\"+2\">**Activity (3 min)**</font>\n",
    "    \n",
    "Write a query to find out if there are any countries for which no cities are listed?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "41d1e2a1-0f06-49ed-af97-95fa3a3f68d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    c.name, ci.name\n",
    "FROM\n",
    "    country c\n",
    "LEFT JOIN\n",
    "    city ci\n",
    "ON\n",
    "    c.code = ci.countrycode\n",
    "WHERE\n",
    "    ci.name IS NULL\n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "254736b4-062b-4fa4-bb89-359ce37d2b06",
   "metadata": {},
   "source": [
    "## Subqueries"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "281b3b28-80ac-4048-a61b-13e88d48447f",
   "metadata": {},
   "source": [
    "There is a particular type of question that we have avoided so far for our SQL queries, and that is one for which we need the result of a second query to be able to run the first query. Take the following question as an example:\n",
    "\n",
    "---\n",
    "\n",
    "**Example:** Using the `world` database, find the countries with surface area above the average value of all countries in the world.\n",
    "\n",
    "---\n",
    "\n",
    "This query looks simple. You might be tempted to try\n",
    "\n",
    "```sql\n",
    "-- This will NOT work\n",
    "SELECT\n",
    "    name\n",
    "FROM\n",
    "    country\n",
    "WHERE\n",
    "    surfacearea > AVG(surfacearea)\n",
    ";\n",
    "```\n",
    "\n",
    "but we've learned before that aggregate functions **cannot** be used within a `WHERE` clause.\n",
    "\n",
    "To answer this question, we need to query the database twice: once to obtain the average surface area, and once to actually retrieve the rows that satisfy the condition.\n",
    "\n",
    "However, we don't need to do that in two separate queries and manually take the data coming from the first query and use it in the second. We can use a **subquery** to do that for us.\n",
    "\n",
    "A subquery is a `SELECT` statement that is incorporated into another SQL statement. For example, the query that computes the average surface area is:\n",
    "\n",
    "```sql\n",
    "SELECT\n",
    "    AVG(surfacearea)\n",
    "FROM\n",
    "    country\n",
    ";\n",
    "```\n",
    "\n",
    "We can use this intermediate information in our original query by embedding the above query in the `WHERE` clause of the original query:\n",
    "\n",
    "```sql\n",
    "SELECT\n",
    "    name\n",
    "FROM\n",
    "    country\n",
    "WHERE\n",
    "    surfacearea > (\n",
    "        SELECT AVG(surfacearea) FROM country\n",
    "    )\n",
    ";\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8863a5e1-1483-48b5-b078-2df4d424e58e",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql postgresql://{username}:{password}@{host}:{port}/world"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e45e9c16-3e6b-4611-9dc1-6ea96d07d64c",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    name\n",
    "FROM\n",
    "    country\n",
    "WHERE\n",
    "    surfacearea > (\n",
    "        SELECT\n",
    "            AVG(surfacearea)\n",
    "        FROM\n",
    "            country\n",
    "    )\n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "81fb6b0c-9ba5-45ae-bfa0-1366310cf568",
   "metadata": {},
   "source": [
    "Note that:\n",
    "\n",
    "- A subquery should always be enclosed in parentheses, e.g. `(SELECT ...)`\n",
    "\n",
    "- Subqueries should **not** be terminated by a semi-colon, as opposed to regular queries\n",
    "\n",
    "- Sometimes the main SQL statement is called the **outer query** and the subquery is called the **inner query**\n",
    "\n",
    "- A subquery can be used in the `SELECT`, `FROM`, `WHERE`, and `HAVING` clauses, but most commonly in the `WHERE`.\n",
    "\n",
    "- A subquery in the `SELECT` clause should always return a single value, not a column or rows of values."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d4e8eee7-65d4-4cc3-9fad-7aea8d6b9c8b",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-info\" style=\"color:black\">\n",
    "\n",
    "<font size=\"+2\">**Activity (3 min)**</font>\n",
    "    \n",
    "Use a subquery in the `SELECT` clause to **double-check** if the returned rows do satisfy the condition of `surfacearea > AVG(surfacearea)`?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0ff0411a-625c-4921-bc36-07a08139da12",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    name,\n",
    "    surfacearea / (SELECT AVG(surfacearea) FROM country)\n",
    "        AS ratio\n",
    "FROM\n",
    "    country\n",
    "WHERE\n",
    "    surfacearea > (\n",
    "        SELECT AVG(surfacearea) FROM country\n",
    "    )\n",
    "ORDER BY\n",
    "    ratio\n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be77bc24-60fd-49e4-ba44-60cec4283222",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-info\" style=\"color:black\">\n",
    "\n",
    "<font size=\"+2\">**Activity (3 min)**</font>\n",
    "    \n",
    "Retrieve the name of countries whose capital cities have a population larger than 5 million.\n",
    "    \n",
    "(You can do this using a SQL join as well as we'll do after this activity, but here I want you to use a subquery for practice)\n",
    "    \n",
    "**Hint:** The id of the capital of each country is stored in the `capital` column of the `country` table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d205bfac-852f-4536-bb10-088c3f50e132",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    name\n",
    "FROM\n",
    "    country\n",
    "WHERE\n",
    "    capital IN (\n",
    "        SELECT id\n",
    "        FROM city\n",
    "        WHERE population > 5000000\n",
    "    )\n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f34a8a1-9f69-49a4-8791-382c4323238d",
   "metadata": {
    "tags": []
   },
   "source": [
    "As mentioned earlier, we can rewrite this query using a **join**:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a4424140-5acc-4af9-8530-7e87a9dda440",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    co.name\n",
    "FROM\n",
    "    country co\n",
    "JOIN\n",
    "    city ci\n",
    "ON\n",
    "    co.capital = ci.id\n",
    "WHERE\n",
    "    ci.population > 5000000\n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b2ad1fdf-7494-4884-a43f-8d8d8652ff61",
   "metadata": {},
   "source": [
    "Using a subquery is actually another way to gain access to data stored in other tables.\n",
    "\n",
    "Typically, joins can be rewritten as a subquery and vice-versa, so what's the difference?\n",
    "\n",
    "- Subqueries tend to be more readable and more intuitive\n",
    "\n",
    "However:\n",
    "\n",
    "- Subqueries cannot be used if you need to include columns from the inner query in your results"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:ssc2022]",
   "language": "python",
   "name": "conda-env-ssc2022-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}