Please answer in SQL: List all airports from which airline Southwest operates more flights than Northwest. Include only airports that have at least one outgoing flight on Southwest and at least one on Northwest. List airport code along with counts for each airline as two separate columns. Order by source airport code. airlines dataset Id : unique identifier of the airline Name : full name of the airline Abbr : abbreviated name of the airline Country : country of the airline airports dataset City : location of the airport Code : unique three-letter airport code Name : name of the airport Country : country of the airport CAbbr : abbreviation of the country flights dataset Airline : airline that manages the flight FlightNo : flight number (unique for each airline, may repeat for different airlines) Source : Code for the airport from which the flight originates Destination : Code for the airport at which the flight terminates Attempted (but incorrect) code: SELECT f.Source, COUNT(CASE WHEN a.Name = 'Southwest' THEN f.FlightNo ELSE NULL END) AS 'SWflights', COUNT(CASE WHEN a.Name = 'Northwest' THEN f.FlightNo ELSE NULL END) AS 'NWflights' FROM flights f INNER JOIN airlines a ON f.Airline = a.Id GROUP BY f.Source HAVING SWflights >= 1 AND NWflights >= 1 AND SWflights > NWflights ORDER BY f.Source;

Computer Networking: A Top-Down Approach (7th Edition)

7th Edition

ISBN:9780133594140

Author:James Kurose, Keith Ross

Publisher:James Kurose, Keith Ross

Chapter1: Computer Networks And The Internet

Section: Chapter Questions

Problem R1RQ: What is the difference between a host and an end system? List several different types of end...

See similar textbooks

Related questions

Question

Please answer in SQL:

List all airports from which airline Southwest operates more flights than Northwest. Include only airports that have at least one outgoing flight on Southwest and at least one on Northwest. List airport code along with counts for each airline as two separate columns. Order by source airport code.

airlines dataset

Id : unique identifier of the airline
Name : full name of the airline
Abbr : abbreviated name of the airline
Country : country of the airline

airports dataset

City : location of the airport
Code : unique three-letter airport code
Name : name of the airport
Country : country of the airport
CAbbr : abbreviation of the country

flights dataset

Airline : airline that manages the flight
FlightNo : flight number (unique for each airline, may repeat
for different airlines)
Source : Code for the airport from which the flight originates
Destination : Code for the airport at which the flight terminates

Attempted (but incorrect) code:

SELECT f.Source,
COUNT(CASE WHEN a.Name = 'Southwest' THEN f.FlightNo ELSE NULL END) AS 'SWflights',
COUNT(CASE WHEN a.Name = 'Northwest' THEN f.FlightNo ELSE NULL END) AS 'NWflights'
FROM flights f
INNER JOIN airlines a ON f.Airline = a.Id
GROUP BY f.Source
HAVING SWflights >= 1 AND
NWflights >= 1 AND
SWflights > NWflights
ORDER BY f.Source;

Expert Solution

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-engineering and related others by exploring similar questions and additional content below.

Recommended textbooks for you

Computer Networking: A Top-Down Approach (7th Edi…

Computer Engineering

ISBN:

9780133594140

Author:

James Kurose, Keith Ross

Publisher:

PEARSON