{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Displaying airport data\n",
    "\n",
    "You have been provided a <a href=\"../../assets/data/airports.db\">database</a> built on [SQLite](https://sqlite.org/index.html) which contains information about airports.The schema is displayed below."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Database schema\n",
    "\n",
    "A database's schema is its table design and structure. The **airports** database has two tables, `cities`, which contain a list of cities in the United Kingdom and Ireland, and `airports`, which contains the list of all airports. Because some cities may have multiple airports, two tables were created to store the information. In this exercise, you will use joins to display information for different cities.\n",
    "\n",
    "| Cities           |\n",
    "| ---------------- |\n",
    "| id (PK, integer) |\n",
    "| city (text)      |\n",
    "| country (text)   |\n",
    "\n",
    "| Airports                         |\n",
    "| -------------------------------- |\n",
    "| id (PK, integer)                 |\n",
    "| name (text)                      |\n",
    "| code (text)                      |\n",
    "| city_id (FK to id in **Cities**) |\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Loading data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# install the necessary dependencies\n",
    "import sys\n",
    "!{sys.executable} -m pip install --quiet ipython-sql\n",
    "\n",
    "%load_ext sql\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# download data file\n",
    "!curl https://raw.githubusercontent.com/ocademy-ai/machine-learning/main/open-machine-learning-jupyter-book/assets/data/airports.db -o ./airports.db\n",
    "\n",
    "# loading from disk\n",
    "%sql sqlite:///airports.db \n",
    "\n",
    "# clear tmp data file\n",
    "!rm -rf airports.db \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Assignment\n",
    "\n",
    "Create queries to return the following information:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. all city names in the Cities table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT City FROM Cities"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2. all cities in Ireland in the Cities table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT ______ FROM Cities ______ Country = ______"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3. all airport names with their city and country"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT Airports.name, Cities.____, Cities.____\n",
    "FROM Airports\n",
    "______ JOIN Cities \n",
    "WHERE Airports.city_id = ____"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "4. all airports in London, United Kingdom"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT Airports.name, Cities.city, Cities.country\n",
    "FROM Airports\n",
    "______ Cities \n",
    "WHERE Airports.city_id = Cities.id ______ Cities.____ = 'London' ______ Cities.____ = 'United Kingdom'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Acknowledgments\n",
    "\n",
    "Thanks to Microsoft for creating the open-source course [Data Science for Beginners](https://github.com/microsoft/Data-Science-For-Beginners) and author [Pikachú](https://github.com/BethanyJep). They inspires the majority of the content in this chapter."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}