Write a SQL query that finds all airlines that had more than 0.5 percent of thei

Question

Write a SQL query that finds 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. [6 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

First of all i take canceled column as boolean.
===================================

select
fid,
count_canceled,
sum(cancel_true),
sum(cancel_false),
from
select
(
fid fid,
count(count_canceled) count_canceled,
decode(canceled,'T',1,0) cancel_true,
decode(canceled,'F',1,0) cancel_false
from
Flights
where (sum(cancel_false)/count_canceled)*100>0.5
)
order by fid asc;

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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