7.2.5 Additional AirportDB Queries

This topic provides additional airportdb queries that you can run to test the HeatWave Cluster.

• Query 1: Number of Tickets > $500.00, Grouped By Price

  mysql> SELECT
            booking.price,
            count(*)
        FROM
            booking
        WHERE
            booking.price > 500
        GROUP BY
            booking.price
        ORDER BY
            booking.price
        LIMIT
            10;

• Query 2: Average Age of Passengers By Country, Per Airline

  mysql> SELECT
            airline.airlinename,
            AVG(datediff(departure,birthdate)/365.25) as avg_age,
            count(*) as nb_people
        FROM
            booking, flight, airline, passengerdetails
        WHERE
            booking.flight_id=flight.flight_id AND
            airline.airline_id=flight.airline_id AND
            booking.passenger_id=passengerdetails.passenger_id AND
            country IN ("SWITZERLAND", "FRANCE", "ITALY")
        GROUP BY
            airline.airlinename
        ORDER BY
            airline.airlinename, avg_age
        LIMIT 10;

• Query 3: Most Tickets Sales by Airline for Departures from US Airports

  mysql> SELECT
            airline.airlinename,
            SUM(booking.price) as price_tickets,
            count(*) as nb_tickets
        FROM
            booking, flight, airline, airport_geo
        WHERE
            booking.flight_id=flight.flight_id AND
            airline.airline_id=flight.airline_id AND
            flight.from=airport_geo.airport_id AND
            airport_geo.country = "UNITED STATES"
        GROUP BY
            airline.airlinename
        ORDER BY
            nb_tickets desc, airline.airlinename
        LIMIT 10;