Write a SQL query: Write a query that returns list of active airlines (active =
ID: 3774350 • Letter: W
Question
Write a SQL query:
Write a query that returns list of active airlines (active = true) that serve no routes, sorted by the airline name in lexical order. The query must return airline id, airline name, alias, and airline country. This problem has to be implemented using join queries.
Data Architecture: The following diagram shows the relationships between the data files in the dataset.
airport
route
airline
Airport_ID
Airline
Airline_ID
Name
Airline_ID
Name
City
Source_Airport
Alias
Country
Source_Airport_ID
IATA
IATA/FAA
Destination_Airport
ICAO
ICAO
Destination_Airport_ID
Callsign
Latitude
Codeshare
Country
Longitude
Stops
Active
Altitude
Equipment
Timezone
DST
TZDB_Timezone
airport
route
airline
Airport_ID
Airline
Airline_ID
Name
Airline_ID
Name
City
Source_Airport
Alias
Country
Source_Airport_ID
IATA
IATA/FAA
Destination_Airport
ICAO
ICAO
Destination_Airport_ID
Callsign
Latitude
Codeshare
Country
Longitude
Stops
Active
Altitude
Equipment
Timezone
DST
TZDB_Timezone
Explanation / Answer
select al.airline_id,al.name,al.alias,al.country,count(r.airline_id) from airline al inner join route r where al.airline_id=r.airline_id and al.active=true group by r.airine_id order by al.name asc;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.