📚 Toutes les Requêtes SQL pour la Base Aviation

Copier

SELECT a.nom, v.nom AS ville 
FROM aeroport a
JOIN ville v ON a.ville_id = v.id
JOIN pays p ON v.pays_id = p.id
WHERE p.nom = 'France';

Copier

SELECT v.numero, v.date_depart, a_arr.nom AS arrivee
FROM vol v
JOIN aeroport a_dep ON v.aeroport_dep_id = a_dep.id
JOIN ville ville_dep ON a_dep.ville_id = ville_dep.id
WHERE ville_dep.nom = 'Paris';

Copier

SELECT v.numero, 
       CONCAT(p.prenom, ' ', p.nom) AS pilote,
       c.nom AS compagnie,
       ta.libelle AS avion
FROM vol v
JOIN pilote p ON v.pilote_id = p.id
JOIN avion a ON v.avion_id = a.id
JOIN type_avion ta ON a.type_avion_id = ta.id
JOIN compagnie c ON a.compagnie_id = c.id;

Copier

SELECT c.nom, COUNT(v.id) AS nombre_vols
FROM vol v
JOIN avion a ON v.avion_id = a.id
JOIN compagnie c ON a.compagnie_id = c.id
GROUP BY c.nom
ORDER BY nombre_vols DESC;

Copier

SELECT numero, 
       TIMEDIFF(date_arrivee, date_depart) AS duree
FROM vol
WHERE TIMEDIFF(date_arrivee, date_depart) > '05:00:00';

Copier

SELECT CONCAT(prenom, ' ', nom) AS pilote
FROM pilote
WHERE id NOT IN (
    SELECT DISTINCT v.pilote_id
    FROM vol v
    JOIN avion a ON v.avion_id = a.id
    JOIN type_avion ta ON a.type_avion_id = ta.id
    WHERE ta.libelle LIKE 'Airbus%'
);

Copier

SELECT numero, date_depart, date_arrivee
FROM vol
WHERE date_depart BETWEEN '2025-05-01' AND '2025-05-31';

Copier

SELECT CONCAT(prenom, ' ', nom) AS pilote, salaire
FROM pilote
WHERE salaire > (SELECT AVG(salaire) FROM pilote)
ORDER BY salaire DESC;

Copier

SELECT p.nom AS pays, COUNT(a.id) AS nombre_aeroports
FROM aeroport a
JOIN ville v ON a.ville_id = v.id
JOIN pays p ON v.pays_id = p.id
GROUP BY p.nom
HAVING COUNT(a.id) > 5
ORDER BY nombre_aeroports DESC;

Copier

SELECT v.numero, ta.libelle AS type_avion, f.nom AS fabricant
FROM vol v
JOIN avion a ON v.avion_id = a.id
JOIN type_avion ta ON a.type_avion_id = ta.id
JOIN fabriquant f ON ta.fabriquant_id = f.id
WHERE f.nom = 'Airbus';

Copier

SELECT ta.libelle, COUNT(v.id) AS nombre_vols
FROM type_avion ta
JOIN avion a ON ta.id = a.type_avion_id
JOIN vol v ON a.id = v.avion_id
GROUP BY ta.libelle
ORDER BY nombre_vols DESC
LIMIT 5;

Copier

SELECT numero, 
       TIMEDIFF(date_arrivee, date_depart) AS duree,
       aeroport_dep.nom AS depart,
       aeroport_arr.nom AS arrivee
FROM vol
JOIN aeroport aeroport_dep ON vol.aeroport_dep_id = aeroport_dep.id
JOIN aeroport aeroport_arr ON vol.aeroport_arr_id = aeroport_arr.id
ORDER BY duree DESC
LIMIT 1;

Copier

SELECT DISTINCT v_dep.nom AS ville_depart, 
                v_arr.nom AS ville_arrivee
FROM vol
JOIN aeroport a_dep ON vol.aeroport_dep_id = a_dep.id
JOIN aeroport a_arr ON vol.aeroport_arr_id = a_arr.id
JOIN ville v_dep ON a_dep.ville_id = v_dep.id
JOIN ville v_arr ON a_arr.ville_id = v_arr.id
WHERE v_dep.pays_id = v_arr.pays_id
AND v_dep.id != v_arr.id;

Copier

SELECT CONCAT(p.prenom, ' ', p.nom) AS pilote,
       COUNT(DISTINCT a.type_avion_id) AS types_avion
FROM pilote p
JOIN vol v ON p.id = v.pilote_id
JOIN avion a ON v.avion_id = a.id
GROUP BY p.id
HAVING types_avion > 1
ORDER BY types_avion DESC;

Copier

SELECT a.immatriculation, ta.libelle
FROM avion a
LEFT JOIN vol v ON a.id = v.avion_id
JOIN type_avion ta ON a.type_avion_id = ta.id
WHERE v.id IS NULL;

Copier

SELECT p.nom AS pays, 
       ROUND(AVG(pi.salaire), 2) AS salaire_moyen
FROM pilote pi
JOIN compagnie c ON pi.compagnie_id = c.id
JOIN ville v ON c.id = v.pays_id -- À adapter selon le vrai schéma
JOIN pays p ON v.pays_id = p.id
GROUP BY p.nom
ORDER BY salaire_moyen DESC;

Copier

SELECT v.numero,
       p_dep.nom AS pays_depart,
       p_arr.nom AS pays_arrivee
FROM vol
JOIN aeroport a_dep ON vol.aeroport_dep_id = a_dep.id
JOIN aeroport a_arr ON vol.aeroport_arr_id = a_arr.id
JOIN ville v_dep ON a_dep.ville_id = v_dep.id
JOIN ville v_arr ON a_arr.ville_id = v_arr.id
JOIN pays p_dep ON v_dep.pays_id = p_dep.id
JOIN pays p_arr ON v_arr.pays_id = p_arr.id
WHERE p_dep.id != p_arr.id;