Schema of Tables: CREATE TABLE FLIGHTS(fid INT PRIMARY KEY, year INT, month_id I

Question

Schema of Tables:

CREATE TABLE FLIGHTS(fid INT PRIMARY KEY, year INT, month_id INT, day_of_month INT, day_of_week_id INT, carrier_id VARCHAR(3), flight_num INT, origin_city VARCHAR(34), origin_state VARCHAR(47), dest_city VARCHAR(34), dest_state VARCHAR(46), departure_delay DOUBLE, taxi_out DOUBLE, arrival_delay DOUBLE, canceled INT, actual_time DOUBLE, distance DOUBLE, capacity INT,price double, FOREIGN KEY (carrier_id) REFERENCES Carriers(cid),FOREIGN KEY (month_id) REFERENCES Months(mid),FOREIGN KEY (day_of_week_id) REFERENCES Weekdays(did));

CREATE TABLE CARRIERS(cid VARCHAR(7) PRIMARY KEY, name VARCHAR(83));

CREATE TABLE MONTHS(mid INT PRIMARY KEY, month VARCHAR(9));

CREATE TABLE WEEKDAYS(did INT PRIMARY KEY, day_of_week VARCHAR(9));

Note: For the questions below, you should be able to write queries that do not contain any subqueries. Please use joins where appropriate

1. (10 points) List the distinct flight numbers of all flights from Seattle to Boston by Alaska Airlines Inc. on Mondays. Also notice that, in the database, the city names include the state. So Seattle appears as Seattle WA. Name the output column flight_num [Hint: Output relation cardinality: 3 rows] 2. (10 points) Find all flights from Seattle to Boston on July 15th 2015. Search only for itineraries that have one stop. Both legs of the flight must have occurred on the same day (same day here means the date of the flight). It's fine if the landing date is different from the flight date, say in the case of an overnight flight and must be with the same carrier. The total flight time (actual_time) of the entire itinerary should be fewer than 7 hours (but notice that actual_time is in minutes). For each itinerary, the query should return the name of the carrier, the first flight number, the origin and destination of that first flight, the flight time, the second flight number, the origin and destination of the second flight, the second flight time, and finally the total flight time. Only count flight times here; do not include any layover time Name the output columns name as the name of the carrier, f1_flight_num, f1_origin_city, f1_dest_city, f1_actual time, f2_flight_num, f2 origin_city, f2_dest_city, f2_actual_time, and actual time as the total flight time. List the output columns in this order. Put the first 20 rows of your result right after your query as a comment. Output relation cardinality: 488 rows] 3. (10 points) Find the day of the week with the longest average arrival delay. Return the name of the day and the average delay. Name the output columns day_of_week and delay, in that order. (Hint: consider using LIMIT. Look up what it does!) Output relation cardinality: 1 row] 4. (10 points) Find the names of all airlines that ever flew more than 1000 flights in one day (i.e., a specific day/month/year, but not any 24-hour period). Return only the names of the airlines. Do not return any duplicates i.e., airlines with the exact same name) Name the output column name Output relation cardinality: 11 rows] 5. (10 points) Find all airlines that had more than 0.5 percent of their flights out of Seattle be canceled. Return the name of the airline and the percentage of canceled flight out of Seattle. Order the results by the percentage of canceled flights in ascending order Name the output columns name and percent, in that order. Output relation cardinality: 6 rows] 6. (10 points) Find the average price of tickets between Seattle and New York, NY in the entire month. Show the average price for each airline separately Name the output columns carrier and average price, in that order Output relation cardinality: 3] 7. (10 points) Find the total capacity of all direct flights that fly between Seattle and San Francisco, CA on July 10th, 2015. Name the output column capacity Output relation cardinality: 1] 8. (10 points) Compute the total departure delay of each airline across all flights in the entire month. Name the output columns name and delay, in that order. [Output relation cardinality: 22]

Explanation / Answer

1.

Select Distinct a.flight_number
From FLIGHTS a, CARRIERS b
where a.carrier_id = b.cid and a.origin_state ='Seattle WA' and a.dest_city ='Boston' and b.name = 'Alaska Airlines'

2.

Select Distinct a.flight_number
From FLIGHTS a, CARRIERS b
where a.carrier_id = b.cid and a.origin_state ='Seattle WA' and a.dest_city ='Boston' and a.actual_time>420

3.

Select b.day_of_week ,Avg(a.departure_delay)
From FLIGHTS a ,WEEKDAYS b
GROUP BY b.day_of_week
ORDER BY b.day_of_week
LIMIT 1
where a.day_of_week_id = b.did ;

4.

Select Distinct CARRIERS.name
from CARRIERS, FLIGHTS
where CARRIERS.cid =FLIGHTS.carrier_id and FLIGHTS.flight_num>1000

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.