This SQL exercise is a part of a section dedicated for data analysts , providing data analysis tasks that require both business understanding and technical skills.

 

About this Exercise

The Bureau of Transportation Statistics (BTS), part of the United States Department of Transportation, collects information on domestic flights operated by different air carriers. The following database simulates some of the data it has.

 

Diagram



 

Download

The database creation script can be downloaded using the following link

 

Exercises

The following exercises are constructed in ascending difficulty order. First ones are pretty easy, made for warm-up. The advanced exercises, on the other hand, can be quite challenging.

 
Basic Analysis

  1. How many airlines are listed in this database ?
  2. Which state has the highest amount of airports ?
  3. What was the most common reason for flight cancellation ?

 
Advanced Analysis

  1. How many flights, carried out by “American Airlines Inc.”, flew out of “Los Angeles International Airport” to “Miami International Airport”
  2. How many flights have been carried out by each airline
  3. Which company had the highest amount of delays (in minutes)
  4. A customer is planning to take a flight from LAX to SFO, based on the data you have regarding delays, which airline will you recommend him to take?
  5. Based on the data you have, if a customer is planing to take a flight, what are the odds it’ll be cancelled?
  6. Which aireline company has the highest cancellation rate?
  7. Which airport had the highest number of cancellations due to security issues?
  8. Display the number of flights per month

 

Solutions