Question

Plane E Flight\ Airport\ E Query! \ Qu Type ▼Nump irbus A320 Airbus A330 ATR 42 Boeing 737 180 320 70 200 300 280 60 150 0 100 130 50 75 150 150 30 150 0 +Boeing 747 DC-10 Saab 340 + Tu-204

Please do the following query for me on these tables

(a) Cities with direct flights from Seattle, sorted lexicographically

(b) The number of international flights departing from Portland on Wednesday

(c) The number of international flights departing every week from each US city

(d) Canadian cities having at least 2 direct flights to US

(e). Cities served by the largest plane (as num. of passengers)

Answer 1

--(a) Cities with direct flights from Seattle, sorted lexicographically
SELECT ArrivalCity FROM Flight WHERE DepartureCity='Seattle' ORDER BY ArrivalCity ASC;
--(b) The number of international flights departing from Portland on Wednesday
SELECT COUNT(FlightID) FROM Flight WHERE DepartureCity='Portland' AND DayOfWeek='Wednesday';
--(c) The number of international flights departing every week from each US city
SELECT COUNT(a.FlightID) FROM Flight a INNER JOIN Airport p ON a.DepartureCity=p.City
WHERE p.Nation='USA'
--(d) Canadian cities having at least 2 direct flights to US
SELECT DepartureCity FROM Flight
WHERE DepartureCity IN (SELECT City FROM Airpot WHERE Nation='USA') AND
ArrivalCity IN (SELECT City FROM Airpot WHERE Nation='CA')
GROUP BY DepartureCity HAVING COUNT(DepartureCity) >1;
--(e). Cities served by the largest plane (as num. of passengers)
SELECT a.ArrivalCity FROM Flight a INNER JOIN Plane p ON a.PlaneType=p.Type
GROUP BY a.ArrivalCity,p.Type HAVING MAX(p.NumOfPassenger);