Hands On Labs

SQL Select Basics

These ungraded hands-on-lab will help you build your skills working with combining information from multiple tables. Solutions to all problems are immediately available, but you’ll learn the material more durably if you attempt each problem before consulting its solution.

All of the queries use tables in the flights database. You may find the data descriptions here helpful: NYC Flights13.

SQL select question 1

Write a SELECT statement that returns all of the rows and columns in the planes table.

    SELECT  * 
      FROM  planes;

SQL select question 2

Using the weather table, concatenate the year, month, and day columns to display a date in the form “3/17/2013”. Consider using the CONCAT() function.

   SELECT  CONCAT(Month,'/',Day,'/',Year) AS Date 
     FROM  weather
     WHERE CONCAT(Month,'/',Day,'/',Year) LIKE '%3/17/2013%';

SQL select question 3

Order by planes table by number of seats, in descending order.

   SELECT     * 
     FROM     planes 
     ORDER BY seats DESC;

SQL select question 4

List only those planes that have an engine that is ‘Reciprocating’.

   SELECT  * 
     FROM  planes 
     WHERE engine = 'Reciprocating';

SQL select question 5

List only the first 5 rows in the flights table.

   SELECT  * 
     FROM  flights LIMIT 5;

SQL select question 6

What was the longest (non-blank) air time?

   SELECT  air_time 
     FROM  flights 
     WHERE air_time > 0 ORDER BY air_time DESC LIMIT 1;

SQL select question 7

What was the shortest (non-blank) air time for Delta?

   SELECT     air_time 
     FROM     flights 
     WHERE    air_time > 0 AND carrier = 'DL' 
     ORDER BY air_time LIMIT 1;

SQL select question 8

Show all of the Alaska Airlines flights between June 1st, 2013 and June 3rd, 2013. Is the way the data is stored in the database helpful to you in making your query?

   SELECT  * 
     FROM  flights 
     WHERE carrier = 'AS' 
           AND year = 2013 AND month = 6 
           AND day BETWEEN 1 AND 3;

SQL select question 9

Show all of the airlines whose names contain ‘America’.

   SELECT  * 
     FROM  airlines 
     WHERE name LIKE '%America%';

SQL select question 10

How many flights went to Miami?

   SELECT  COUNT(*) AS 'Total flights'
     FROM  flights 
     WHERE dest = 'MIA';

SQL select question 11

Were there more flights to Miami in January 2013 or July 2013? (Multiple queries are OK)

   SELECT  'January' as 'Month', COUNT(*) as 'Total flights' 
     FROM  flights 
     WHERE dest = 'MIA' AND year = 2013 AND month = 1
   UNION
   SELECT  'July', COUNT(*) 
     FROM  flights 
     WHERE dest = 'MIA' AND year = 2013 AND month = 7;

SQL select question 12

What is the average altitude of airports?

   SELECT FORMAT(AVG(alt),2) as 'Avg. altitude' 
     FROM airports;

Please email to: kleber.perez@live.com for any suggestion.

    ** For simplicity some images do not reflect total number of columns or rows.

SQL Aggregation

These ungraded hands-on-labs will help you build your skills working with aggregating data in a SQL SELECT statement. Solutions to all problems are immediately available, but you’ll learn the material more durably if you attempt each problem before consulting its solution.

All of the queries use tables in the flights database. You may find the data descriptions here helpful: NYC Flights13.

SQL aggregate question 1

What is the average altitude of the three major New York airports?

   SELECT  FORMAT(AVG(alt),2) as 'Avg. altitude' 
     FROM  airports 
     WHERE faa IN ('EWR','LGA','JFK');

SQL aggregate question 2

What is the average altitude for airports grouped by timezone. Which timezone has the highest altitude? Why?

   SELECT     tz, 
              FORMAT(AVG(alt),2) as 'Avg. altitude', 
              FORMAT(MAX(alt),2) as 'MAX altitude'
     FROM     airports
     GROUP BY tz
     ORDER BY tz DESC;

SQL aggregate question 3

Which of these four airplanes made the most flights out of New York City airports in 2013? Plane tailnums: ‘N125UW’,‘N848MQ’,‘N328AA’,‘N247JB’.

   SELECT     tailnum, COUNT(*) AS 'Total flights'
     FROM     flights WHERE tailnum IN ('N125UW','N848MQ','N328AA','N247JB')
     GROUP BY tailnum
     ORDER BY COUNT(*) DESC;

SQL aggregate question 4

For each of these four planes, show the corresponding meta-data (model, manufacturer, engines, etc.) about each plane? What is surprising about the information returned? How do you think this could happen?

   SELECT  * 
     FROM  planes
     WHERE tailnum IN ('N125UW','N848MQ','N328AA','N247JB');

SQL aggregate question 5

Write a SELECT statement that shows for all of the flights during the period February 14th to February 17th for each of the four planes above: ‘N125UW’,‘N848MQ’,‘N328AA’,‘N247JB’. Your select statement should return the following information: tailnum, flight date information, departure delay, arrival delay, 3 digit destination code.

   SELECT  * 
     FROM  planes
     WHERE tailnum IN ('N125UW','N848MQ','N328AA','N247JB');