msb-public
/
Data-Science-For-Beginners
mirror of https://github.com/microsoft/Data-Science-For-Beginners.git
1
0
Code Issues Projects Releases Wiki Activity
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'leestott-patch-13'
${ noResults }
Data-Science-For-Beginners/translations/en/2-Working-With-Data/05-relational-databases/assignment.md

4.2 KiB
Raw Permalink Blame History

Displaying airport data

You have been provided a database built on SQLite which contains information about airports. The schema is shown below. You will use the SQLite extension in Visual Studio Code to display information about airports in various cities.

Instructions

To begin the assignment, you'll need to complete a few steps. This involves installing some tools and downloading the sample database.

Set up your system

You can use Visual Studio Code and the SQLite extension to interact with the database.

  1. Go to code.visualstudio.com and follow the instructions to install Visual Studio Code.
  2. Install the SQLite extension as described on the Marketplace page.

Download and open the database

Next, download and open the database.

  1. Download the database file from GitHub and save it to a folder.
  2. Open Visual Studio Code.
  3. Open the database in the SQLite extension by pressing Ctrl-Shift-P (or Cmd-Shift-P on a Mac) and typing SQLite: Open database.
  4. Select Choose database from file and open the airports.db file you downloaded earlier.
  5. After opening the database (you won't see any visible changes on the screen), create a new query window by pressing Ctrl-Shift-P (or Cmd-Shift-P on a Mac) and typing SQLite: New query.

Once the query window is open, you can use it to execute SQL statements against the database. Use the command Ctrl-Shift-Q (or Cmd-Shift-Q on a Mac) to run queries on the database.

[!NOTE] For more details about the SQLite extension, refer to the documentation.

Database schema

A database's schema defines its table design and structure. The airports database contains two tables: cities, which lists cities in the United Kingdom and Ireland, and airports, which lists all airports. Since some cities may have multiple airports, two separate tables were created to store this information. In this exercise, you will use joins to display data for various cities.

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

Assignment

Write queries to retrieve the following information:

  1. All city names in the Cities table.
  2. All cities in Ireland from the Cities table.
  3. All airport names along with their city and country.
  4. All airports located in London, United Kingdom.

Rubric

Exemplary Adequate Needs Improvement

Disclaimer:
This document has been translated using the AI translation service Co-op Translator. While we strive for accuracy, please note that automated translations may contain errors or inaccuracies. The original document in its native language should be regarded as the authoritative source. For critical information, professional human translation is recommended. We are not responsible for any misunderstandings or misinterpretations resulting from the use of this translation.