Flights and airports¶

SQL exercises · Part I

Problem¶

Three exercises on a database of airports, scheduled flights (number, departure and arrival airport), the actual flights operated on each date, and the frequent flyer programme of registered users:

translate a given relational algebra expression into SQL;
find the registered users with the most frequent-flyer points;
count the flights leaving each airport on a given date;
find the airports with more than one flight on another date.

Schema and test data¶

schema.sqltest_data.sql

-- The three flight exercises use compatible tables, merged here into one schema.
-- Exercises 3 and 4 give these definitions; exercise 1 only names the relevant
-- attributes: VOLO(numero_volo, aeroporto_partenza, aeroporto_arrivo) and
-- AEROPORTO(codice_aeroporto, nome, citta).
CREATE TABLE IF NOT EXISTS aeroporto (
    codice_aeroporto character(3) PRIMARY KEY NOT NULL,
    nome_aeroporto text  NOT NULL,
    citta text  NOT NULL,
    codice_continente text ,
    codice_paese text ,
    internazionale boolean NOT NULL);

CREATE TABLE IF NOT EXISTS volo (
    numero_volo text PRIMARY KEY NOT NULL,
    aeroporto_partenza character(3) REFERENCES aeroporto(codice_aeroporto),
    aeroporto_arrivo character(3) REFERENCES aeroporto(codice_aeroporto),
    orario_partenza_previsto time ,
    orario_arrivo_previsto time);

CREATE TABLE IF NOT EXISTS volo_reale (
    id_volo_reale integer PRIMARY KEY NOT NULL,
    data_partenza_programmata date,
    numero_volo text REFERENCES volo(numero_volo),
    codice_tipo_aeromobile character(3),
    data_partenza_reale date,
    data_arrivo_reale date,
    orario_arrivo_reale time ,
    orario_partenza_reale time ,
    UNIQUE (numero_volo, data_partenza_programmata));

-- exercise 2
CREATE TABLE IF NOT EXISTS frequent_flyer (
    id_frequent_flyer integer PRIMARY KEY  NOT NULL,
    numero_carta text  NOT NULL,
    livello integer NOT NULL,
    punti integer NOT NULL);

CREATE TABLE IF NOT EXISTS utente_registrato (
    id_utente integer PRIMARY KEY NOT NULL,
    email text  NOT NULL,
    nome text  NOT NULL,
    cognome text  NOT NULL,
    id_frequent_flyer integer REFERENCES frequent_flyer (id_frequent_flyer));

-- Test data (not part of the exercise): two users tied on points, an airport with
-- a single flight on 2023-07-06, and a flight that leaves on both dates.
INSERT INTO aeroporto VALUES ('FCO', 'Fiumicino', 'Roma', 'EU', 'IT', 1),
                             ('LIN', 'Linate', 'Milano', 'EU', 'IT', 0),
                             ('CDG', 'Charles de Gaulle', 'Parigi', 'EU', 'FR', 1),
                             ('JFK', 'John F. Kennedy', 'New York', 'NA', 'US', 1);
INSERT INTO volo VALUES ('AZ100', 'FCO', 'LIN', '08:00', '09:10'),
                        ('AZ101', 'LIN', 'FCO', '10:00', '11:10'),
                        ('AF200', 'CDG', 'FCO', '07:30', '09:30'),
                        ('AZ600', 'FCO', 'JFK', '11:00', '15:00');
INSERT INTO volo_reale (id_volo_reale, data_partenza_programmata, numero_volo) VALUES
    (1, '2023-07-04', 'AZ100'), (2, '2023-07-04', 'AZ600'), (3, '2023-07-04', 'AF200'),
    (4, '2023-07-06', 'AZ100'), (5, '2023-07-06', 'AZ600'), (6, '2023-07-06', 'AZ101');
INSERT INTO frequent_flyer VALUES (1, 'C-001', 2, 1200), (2, 'C-002', 3, 5400),
                                  (3, 'C-003', 3, 5400), (4, 'C-004', 1, 300);
INSERT INTO utente_registrato VALUES (1, 'a@x.it', 'Luca', 'Neri', 1), (2, 'b@x.it', 'Marta', 'Gialli', 2),
                                     (3, 'c@x.it', 'Aldo', 'Blu', 3), (4, 'd@x.it', 'Sofia', 'Rosa', 4);

The test data are mine.

Queries¶

Query 1 · From relational algebra to SQL¶

The expression joins each flight with the airport of departure, renames its city to citta_partenza, joins the result with the airport of arrival, renames that city to citta_arrivo, and projects number, departure city and arrival city; order by flight number.

\[ \pi_{\text{numero\_volo},\, \text{citta\_partenza},\, \text{citta\_arrivo}} \Big( \rho_{\text{citta\_arrivo} \leftarrow \text{citta}} \big( \pi_{\ldots}( \rho_{\text{citta\_partenza} \leftarrow \text{citta}}(\text{Volo} \bowtie_{\text{aeroporto\_partenza} = \text{codice\_aeroporto}} \text{Aeroporto})) \bowtie_{\text{aeroporto\_arrivo} = \text{codice\_aeroporto}} \text{Aeroporto} \big) \Big) \]

CREATE VIEW primo AS
SELECT C1.numero_volo, C2.citta AS citta_partenza, C1.aeroporto_arrivo
FROM VOLO AS C1
JOIN AEROPORTO AS C2
ON C1.aeroporto_partenza = C2.codice_aeroporto;

SELECT D1.numero_volo, D1.citta_partenza, D2.citta AS citta_arrivo
FROM primo AS D1
JOIN AEROPORTO AS D2
ON D1.aeroporto_arrivo = D2.codice_aeroporto
ORDER BY numero_volo

numero_volo	citta_partenza	citta_arrivo
AF200	Parigi	Roma
AZ100	Roma	Milano
AZ101	Milano	Roma
AZ600	Roma	New York

The two steps of the expression become a view and a query; the renamings \(\rho\) become the aliases AS citta_partenza and AS citta_arrivo. The airport table is used twice, with two different roles.

Query 2 · Users with the most points¶

Name, surname and points of the registered users with the most frequent-flyer points (there may be more than one), ordered by name, surname and points.

CREATE VIEW sum_punti AS
SELECT C1.nome, C1.cognome, SUM(C2.punti) AS punti

FROM utente_registrato AS C1
JOIN frequent_flyer AS C2
ON C1.id_frequent_flyer = C2.id_frequent_flyer

GROUP BY C1.nome, C1.cognome
ORDER BY C1.nome, C1.cognome, C2.punti;

SELECT nome, cognome, punti
FROM sum_punti
WHERE punti = (SELECT MAX(punti) FROM sum_punti)

nome	cognome	punti
Aldo	Blu	5400
Marta	Gialli	5400

Query 3 · Flights per airport on 4 July 2023¶

For each airport, the number of flights with scheduled departure on 2023-07-04: code and city of the departure airport and number of flights, ordered by airport code.

SELECT C1.aeroporto_partenza, C3.citta, COUNT(C1.numero_volo) AS num_voli

FROM volo AS C1
JOIN volo_reale AS C2
ON C1.numero_volo = C2.numero_volo
JOIN aeroporto AS C3
ON C1.aeroporto_partenza = C3.codice_aeroporto

WHERE C2.data_partenza_programmata = '2023-07-04'
GROUP BY C1.aeroporto_partenza, C3.citta

aeroporto_partenza	citta	num_voli
CDG	Parigi	1
FCO	Roma	2

Query 4 · Airports with more than one flight on 6 July 2023¶

Code of the airports with more than one flight scheduled to leave on 2023-07-06, with the number of flights, ordered by airport code.

CREATE VIEW numero_voli_view AS
SELECT C1.aeroporto_partenza, COUNT(C2.numero_volo) AS numero_voli

FROM volo AS C1
JOIN volo_reale AS C2
ON C1.numero_volo = C2.numero_volo

WHERE data_partenza_programmata = '2023-07-06'
GROUP BY C1.aeroporto_partenza;

SELECT aeroporto_partenza, numero_voli
FROM numero_voli_view
WHERE numero_voli > 1

aeroporto_partenza	numero_voli
FCO	2

Review notes

All four queries return the right rows. The requested ordering is missing in Query 3 and Query 4 (ORDER BY on the airport code). In Query 2 the ORDER BY is inside the view, where it does not order the final result, and it uses C2.punti, which is neither grouped nor aggregated: SQLite accepts it, standard SQL does not. The ordering belongs to the final SELECT. Query 4 could also be written without the view, as GROUP BY … HAVING COUNT(*) > 1.

Takeaways¶

Using the same table twice in a query (departure and arrival airport) requires two aliases.
Relational algebra and SQL map closely: selection is WHERE, projection the SELECT list, renaming AS, join JOIN … ON.
Filtering on a count can be done with a view and WHERE, or directly with HAVING.