Système d’Information
et Gestion en Réseau
SQL-3
Université Paris 2
L3 Economie-Gestion option Sciences du Management
Dominique Tachat
id_Pilote Nom_Pilote Prenom_Pilote date_emb Salaire_Mensuel
Nom des pilotes gagnant le même salaire que le pilote nommé Dupuis
Supposons que le salaire du pilote Dupuis soit 12000 €
SELECT p.Nom_Pilote Exclusion du pilote Dupuis car on
FROM pilote AS p souhaite connaitre les pilotes
autres que le pilote Dupuis qui ont
WHERE p.Salaire_Mensuel = 12000 le même salaire que le pilote
AND p.Nom_Pilote<>'Dupuis'; Dupuis
La 1ère méthode va donc consister à calculer dans une sous-
requête le salaire du pilote Dupuis
Nom des pilotes gagnant le même salaire que le pilote nommé Dupuis
SELECT p.Nom_Pilote
FROM pilote AS p
WHERE p.Salaire_Mensuel =
(
SELECT p.Salaire_Mensuel Salaire mensuel
FROM pilote AS p du pilote Dupuis
WHERE p.Nom_Pilote='Dupuis'
) Nom_Pilote
AND p.Nom_Pilote<>'Dupuis'; Arthur
2ème méthode : utilisation du principe du double alias
pilote p1 (p1 : 1er alias)
id_Pilote Nom_Pilote Prenom_Pilote date_emb Salaire_Mensuel
pilote p2 (p2 : 2ème alias)
id_Pilote Nom_Pilote Prenom_Pilote date_emb Salaire_Mensuel
Avec restriction à la seule occurrence du pilote nommé
Dupuis
pilote p1
pilote p2 après restriction à la seule occurrence du pilote
Dupuis
Jointure sur le salaire
Nom des pilotes gagnant le même salaire que le pilote nommé Dupuis :
utilisation du principe du double alias
SELECT p1.Nom_Pilote
FROM pilote AS p1
INNER JOIN
pilote AS p2
ON p1.Salaire_Mensuel =p2.Salaire_Mensuel
WHERE p2.Nom_Pilote='Dupuis'
AND p1.Nom_Pilote<>'Dupuis';
Tables et liens
vol
Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet
pilote
id_Pilote Nom_Pilote Prenom_Pilote date_emb Salaire_Mensuel
Nom du premier pilote embauché
avion
id_Avion Type_Avion Nbre_Passagers
Nom du premier pilote embauché
Supposons que la plus petite date d’embauche soit le
01-01-1999
SELECT p.Nom_Pilote
FROM pilote AS p
WHERE p.Date_Emb=#1999-01-01#
Calcul de la plus petite date d’embauche dans une
sous-requête
Nom du premier pilote embauché
SELECT p.Nom_Pilote
FROM pilote AS p
WHERE p.Date_Emb=
(
SELECT MIN(p.Date_Emb) Calcul de la plus
FROM pilote AS p petite date
d’embauche
);
Tables et liens
vol
Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet
pilote
id_Pilote Nom_Pilote Prenom_Pilote date_emb Salaire_Mensuel
Nombre de pilotes
ayant volé sur AirBus
avion
id_Avion Type_Avion Nbre_Passagers
Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet
id_Avion Type_Avion Nbre_Passagers
Nombre de pilotes ayant volé sur AirBus
SELECT COUNT(*)
FROM vol AS v INNER JOIN avion AS a
ON v.Code_Avion=a.id_Avion
WHERE a.Type_Avion LIKE 'AirBus%';
Expr1000
7
vol
Vol
Date_ Heure_
id_Vol Ville_Depart Ville_Arrivee Code_Avion Code_Pilote Duree_Vol Prix_Billet
Decollage Decollage
V01 Paris San Francisco A01 P02 25/09/2019 10:00 690 900
V02 Londres Moscou A01 P01 25/09/2019 10:30 240 600
V03 Berlin Madrid A02 P03 25/09/2019 11:15 180 250
V04 Londres Madrid A04 P06 26/09/2019 06:20 150 200
V05 Bruxelles Rome A06 P05 26/09/2019 10:00 120 300
V06 Berlin Amsterdam A05 P01 26/09/2019 14:30 85 275
V07 Paris Bruxelles A03 P02 26/09/2019 18:00 55 190
V08 New York Paris A01 P03 27/09/2019 03:00 480 750
V09 Paris San Francisco A01 P05 28/09/2019 09:30 650 850
Attention, un même pilote est compté plusieurs fois
s’il effectue plusieurs vols sur AirBus.
Exemple P0002
Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet
id_Avion Type_Avion Nbre_Passagers
Nombre de pilotes ayant volé sur AirBus
SELECT COUNT(*) DISTINCT permet d’enlever les
FROM pilote AS p doublons des codes des
pilotes qui ont piloté plusieurs
WHERE p.id_Pilote IN
fois des AirBus
(
SELECT DISTINCT v.Code_Pilote Recherche des codes
FROM vol AS v INNER JOIN avion AS a différents des pilotes
ON v.Code_Avion=a.id_Avion qui pilotent des
Airbus
WHERE a.Type_Avion LIKE 'AirBus%'
);
Expr1000
4
Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet
id_Avion Type_Avion Nbre_Passagers
Nombre de pilotes ayant volé sur AirBus
SELECT COUNT(*)
FROM
(
SELECT DISTINCT v.Code_Pilote
FROM vol AS v INNER JOIN avion AS a
ON v.Code_Avion=a.id_Avion
WHERE a.Type_Avion LIKE 'AirBus%'
)
AS liste;
Création d’une nouvelle relation LISTE qui a pour seul attribut,
l’attribut Code_Pilote et pour occurrences les codes des pilotes qui
ont piloté des AirBus (doublons retirés)
Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet
Pour chaque pilote (code), nombre de vols réalisés
P0001 2
SELECT v.Code_Pilote, COUNT(*) P0002 2
FROM vol AS v P0003 2
GROUP BY v.Code_Pilote; P0004 0
P0005 2
P0006 1
Code_Pilote Expr1001
P01 2
P02 2
P03 2
P05 2
P06 1
Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet
Pour chaque pilote (code), nombre de vols réalisés
(
SELECT v.Code_Pilote, COUNT(*) Pour chaque pilote ayant
FROM vol AS v réalisé des vols, nombre de
vols
GROUP BY v.Code_Pilote
)
UNION
(
SELECT p.id_Pilote,'0' Pour chaque pilote n’ayant
FROM pilote AS p pas réalisé de vols,
renvoyer la valeur 0
WHERE p.id_Pilote NOT IN
(SELECT DISTINCT v.Code_Pilote Code distinct des pilotes
FROM vol AS v) ayant réalisé des vols
)
Ville_ Ville_ Code_ Code_ Date_ Heure_ Duree_ Prix_
id_Vol
Depart Arrivee Avion Pilote Decollage Decollage Vol Billet
Code de l’avion qui a le plus volé
Recherche dans la table VOL
SELECT v.Code_Avion du code de l’avion ayant volé
FROM vol AS v un nombre de fois égal au
GROUP BY v.Code_Avion nombre de fois maximum
HAVING COUNT(*) = Recherche dans la table
( LISTE du nombre maximum
SELECT MAX([Link]) de vols
FROM
(SELECT COUNT(*) AS NBRE
FROM vol AS v Création d’une relation
GROUP BY v.Code_Avion) LISTE qui contient le nombre
de vols effectués par code
AS LISTE avion
)
