42.20. Displaying airport data

You have been provided a database built on SQLite which contains information about airports.The schema is displayed below.

42.20.1. Database schema

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.

Cities
id (PK, integer)
city (text)
country (text)

Airports
id (PK, integer)
name (text)
code (text)
city_id (FK to id in Cities)

42.20.2. Loading data

# install the necessary dependencies
import sys
!{sys.executable} -m pip install --quiet ipython-sql

%load_ext sql

# download data file
!curl https://raw.githubusercontent.com/ocademy-ai/machine-learning/main/open-machine-learning-jupyter-book/assets/data/airports.db -o ./airports.db

# loading from disk
%sql sqlite:///airports.db 

# clear tmp data file
!rm -rf airports.db 

42.20.3. Assignment

Create queries to return the following information:

1. all city names in the Cities table

%%sql

SELECT City FROM Cities

1. all cities in Ireland in the Cities table

%%sql

SELECT ______ FROM Cities ______ Country = ______

1. all airport names with their city and country

%%sql

SELECT Airports.name, Cities.____, Cities.____
FROM Airports
______ JOIN Cities 
WHERE Airports.city_id = ____

1. all airports in London, United Kingdom

%%sql

SELECT Airports.name, Cities.city, Cities.country
FROM Airports
______ Cities 
WHERE Airports.city_id = Cities.id ______ Cities.____ = 'London' ______ Cities.____ = 'United Kingdom'

42.20.4. Acknowledgments

Thanks to Microsoft for creating the open-source course Data Science for Beginners and author Pikachú. They inspires the majority of the content in this chapter.