Airplanes SQL Solutions

1. What are the names and locations of all airports in Portugal? (name, city)

   SELECT name, city
   FROM Airport
   WHERE country = 'Portugal';
   

2. What are the names of all planes with version DC-10? (name)

   SELECT name
   FROM Plane
     JOIN Model ON Plane.modelcod = Model.modelcod
   WHERE version = 'DC-10';
   

   or

   SELECT name
   FROM Plane
     JOIN Model USING(modelcod)
   WHERE version = 'DC-10';
   

   or

   SELECT name
   FROM Plane, Model
   WHERE Plane.modelcod = Model.modelcod AND version = 'DC-10';
   

3. How many engines does each plane have? (plane_name, number)

   SELECT name, engines
   FROM plane
     JOIN model ON plane.modelcod = Model.modelcod;
   

4. How many flights with a 2 or 3 hour duration are there in the database? (number)

   SELECT COUNT(*)
   FROM Flight
   WHERE duration = 2 OR duration = 3;
   

5. What plane models have a version starting with A3? (modelcod, version)

   SELECT modelcod, version
   FROM Model
   WHERE version LIKE 'A3%';
   

6. What is the code and duration of all flights. Sort the answer from longest to shortest flight. If two flights have the same duration, sort them by flight code from smallest to largest (flightcod, duration).

   SELECT flightcod, duration
   FROM Flight
   ORDER BY duration DESC, flightcod;
   

7. Knowing that there are no direct flights from airport 1 (Porto) to airport 12 (London), which 2 legged flights can we use to travel between those airports? (flightcod1, flightcod2, intermediate_airport_code)
   Note: Use the airports codes (1 and 12) instead of the airport names in your query.

   SELECT F1.flightcod AS flightcod1, F2.flightcod AS flightcod2, F1.toairportcod AS intermediate_airport_code
   FROM Flight AS F1 
     JOIN Flight AS F2 ON F1.toairportcod = F2.fromairportcod
   WHERE F1.fromairportcod = 1 AND F2.toairportcod = 12;
   

8. How many airports are there in each country? Sort the answer in ascending order. (country, number)?

   SELECT country, COUNT(*) AS n_airports
   FROM Airport
   GROUP BY country
   ORDER BY n_airports;
   

9. What is the flight code, origin city and destination city of all flights in the database? Sort the answer by flight code. (flightcod, origin, destination)

   SELECT flightcod, A1.city AS origin, A2.city AS destination
   FROM Flight
     JOIN Airport AS A1 ON Flight.fromairportcod = A1.airportcod
     JOIN Airport AS A2 ON Flight.toairportcod = A2.airportcod
   ORDER BY flightcod;
   

10. What are the flight codes of all flights from Porto to Lisboa. (flightcod)? Note: Your query should use the city names, not the airport codes.

    SELECT flightcod
    FROM Flight
      JOIN Airport AS A1 ON Flight.fromairportcod = A1.airportcod
      JOIN Airport AS A2 ON Flight.toairportcod = A2.airportcod
    WHERE A1.city = 'Porto' AND A2.city = 'Lisboa';
    

11. How many airports are there in each country? (country, number);
    Show only countries with more than 2 airports.

    SELECT country, COUNT(*) AS n_airports
    FROM Airport
    GROUP BY country
    HAVING n_airports > 2;
    

12. What country, or countries, has more airports and how many? (country, number)

    SELECT country, COUNT(*) AS n_airports
    FROM Airport
    GROUP BY country
    HAVING n_airports = (
      SELECT COUNT(*) AS n_airports
      FROM Airport
      GROUP BY country
      ORDER BY n_airports DESC
      LIMIT 1
    );
    

    or

    SELECT country, COUNT(*) AS n_airports
    FROM Airport
    GROUP BY country
    HAVING n_airports = (
      SELECT MAX(n_airports)
      FROM (
        SELECT COUNT(*) AS n_airports
        FROM Airport
        GROUP BY country
      )
    );
    

13. How many actual planes are there for each plane model. Sort the result so that least frequent models appear last (make, version, number).
    Note: You do not need to show models that do not have planes.

    SELECT make, version, COUNT(*) as n_planes
    FROM Model
      JOIN Plane ON Model.modelcod = Plane.modelcod
    GROUP BY Model.modelcod
    ORDER BY n_planes DESC;
    

14. How many actual planes are there for each plane model. Sort the result so that least frequent models appear last (make, version, number).
    Note: Also show models that do not have planes.

    SELECT make, version, COUNT(planecod) as n_planes
    FROM Model
      LEFT JOIN Plane ON Model.modelcod = Plane.modelcod
    GROUP BY Model.modelcod
    ORDER BY n_planes DESC;
    

    (Credits: André Restivo https://web.fe.up.pt/~arestivo)