CS 383

Lab 2 -- Jan 26

SQL Queries and Postgres

Work on the following for no more and 80 minutes. I do not expect you complete all of queries below; the goal is just to get practice.

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.
For the queries below, create a file (xxx.sql) that holds them all.
  1. How many rows are in each table? (flights=196431, carriers=5621, airports=1948)
  2. List all departure flights, sorted by departure delays
                        flight=# select * from flights order by departuredelay desc limit 3;
                        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
        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.

Send email to gtowell380@cs.brynmawr.edu with the last query you got working.