Write a SQL query Write a query that returns list of active

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

Solution

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;