CS 383

Lab 2

SQL Queries and Postgres

Answers, or at least partial answers, are included for the first few queries. Also, correct queries are in a separate link from the class web site. I encourage you to not look at the correct queries except to check those you wrote yourself.

PostgreSQL

1. Connect to the flight database
2. What tables are in this database?
3. What is the structure of each table. Hereis my UML-ish sketch for the structure

1. How many rows are in each table? (correct answers are: flights=196431, carriers=5621, airports=1948)
2. List all departure flights, sorted by departure delays. The first 3 rows should be

                       date    | departuretime | arrivaltime | carrier | flightnum | arrivaldelay | departuredelay | origin | destination | distance | cancelled 
                   ------------+---------------+-------------+---------+-----------+--------------+----------------+--------+-------------+----------+-----------
                    2008-10-16 | 08:03:00      | 10:20:00    | NW      |      1765 |         1392 |           1369 | PHL    | DTW         |      453 | f
                    2008-07-20 | 02:36:00      | 04:15:00    | FL      |       927 |         1175 |           1206 | PHL    | ATL         |      665 | f
                    2008-06-13 | 07:25:00      | 09:11:00    | NW      |       689 |         1009 |           1020 | PHL    | MSP         |      980 | f                
                   

3. List all flights on Jan 3, 2008 The first three rows should be:

       date    | departuretime | arrivaltime | carrier | flightnum | arrivaldelay | departuredelay | origin | destination | distance | cancelled 
   ------------+---------------+-------------+---------+-----------+--------------+----------------+--------+-------------+----------+-----------
    2008-01-03 | 17:34:00      | 19:41:00    | WN      |        23 |           36 |             44 | JAX    | PHL         |      742 | f
    2008-01-03 | 07:12:00      | 09:26:00    | WN      |      1232 |           11 |             12 | JAX    | PHL         |      742 | f
    2008-01-03 | 11:27:00      | 18:56:00    | WN      |      1285 |           21 |             42 | LAS    | PHL         |     2176 | f
   

4. The number of arrivals into PHL in March. (You will need to look up how Postgres does dates) (8457)
5. The number of departures from PHL on the 5 day of a month. (You will need to look up how Postgres does dates) (3199)
6. The number of departures from PHL on the 5 day of a month between 10am and 10:59am (inclusive). (You will need to look up how Postgres does times.) (186)
7. List all different departure delays (without duplication)
8. How many different departure delays are there? (486).
9. What was the largest departure delay? The query should have a single response. Write this query in two ways
10. What flight number(s) had the largest delay? Write this query by just putting the largest delay into the query.
11. What is the smallest arrival delay? (actual delay, not early or on-time)
12. How main times did the smallest delay happen?
13. On which flight numbers did this arrival delay happen? for each of those flight numbers, how many times did this arrival delay happen?
14. Which is greater, the number of destinations or the number of origins? Use two queries
15. How many carriers are in this database. How many actually fly into PHL? (Out of PHL?)
16. How many carriers fly from each location from which you can fly directly to PHL?
17. How many flights go past midnight?
18. How many flights from each origin landed in PHL on the day after they took off?

What to turn in.