Write a SQL query that gives the names of all airlines that ever flew more than

Question

Write a SQL query that gives the names of all airlines that ever flew more than 1000 flights in one day. Return only the names. Do not return any duplicates. [Should be 11 rows]

I have the following table schema

CREATE TABLE Flights (
fid INT PRIMARY KEY,
  
year INT, --or VARCHAR(4)
  
month_id INT,
  
day_of_month INT,
  
day_of_week_id INT,
  
carrier_id VARCHAR(10),
  
flight_num INT,
  
origin_city VARCHAR(50),
origin_state VARCHAR(50),
  
dest_city VARCHAR(50),
dest_state VARCHAR(50),
  
departure_delay INT,
  
taxi_out INT,
  
arrival_delay INT,

canceled INT, --boolean
  
actual_time INT,

distance INT,

FOREIGN KEY(month_id) REFERENCES Months(mid),
FOREIGN KEY(day_of_week_id) REFERENCES Weekdays(did),
FOREIGN KEY(carrier_id) REFERENCES Carriers(cid)

);

CREATE TABLE Carriers (
cid INT PRIMARY KEY,
name VARCHAR(100) --length?
);

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

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

Explanation / Answer

The SQL query that gives the names of all airlines that ever flew more than 1000 flights in one day is as follows,

SELECT name

FROM Carriers

WHERE cid= (SELECT carrier_id

                      FROM Flights

                      WHERE count(flight_num)-count(canceled)>=1000

                      AND Flights.carrier_id = Carriers.cid);

-Here we have to use the nested sql query, since we are accessing two separate tables with only one field i.e. cid in common.

-We are not using Months and Weekdays tables in this query because the table flight contain records of single days only.

-We have used count( ) function to count the number of flights and the canceled flights.

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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