MINI PROJETS SQL – Administration ORACLE
PROJET 2
1-En utilisant SQL, créons cette base de données et les tables qui la compose
-la base de donnée
CREATE DATABASE compagnie_aerienne;
-les tables
CREATE TABLE PILOTE (
num_pil INT PRIMARY KEY,
nom_pil VARCHAR(50),
prenom_pil VARCHAR(50),
adresse VARCHAR(100)
);
CREATE TABLE AVION (
code_av VARCHAR(10) PRIMARY KEY,
nom_av VARCHAR(50),
capacite INT,
localisation VARCHAR(100)
);
CREATE TABLE VOL (
code_vol VARCHAR(10) PRIMARY KEY,
dep_vol VARCHAR(100),
arr_vol VARCHAR(100),
heure_dep TIME,
heure_arr TIME,
num_pil INT,
code_av VARCHAR(10),
FOREIGN KEY (num_pil) REFERENCES PILOTE(num_pil),
FOREIGN KEY (code_av) REFERENCES AVION(code_av)
);
2- les avions de type AIRBUS
SELECT * FROM AVION WHERE nom_av LIKE 'Airbus%';
3-Les noms d’avion avec leurs numéros et leurs localisations (autre que Nice),
ayant une capacité supérieure à 200 avec un tri décroissant sur le numéro :
SELECT code_av, nom_av, capacite, localisation
FROM AVION
WHERE localisation <> 'Nice' AND capacite > 200
ORDER BY code_av DESC;
4- Les noms et prénoms des pilotes qui assurent un vol au départ de Lomé́ :
SELECT DISTINCT nom_pil, prenom_pil
FROM PILOTE
JOIN VOL ON PILOTE.num_pil = VOL.num_pil
WHERE VOL.dep_vol = 'Lomé';
5-Les noms et prénoms des pilotes qui conduisent un AIRBUS :
SELECT DISTINCT nom_pil, prenom_pil
FROM PILOTE
JOIN VOL ON PILOTE.num_pil = VOL.num_pil
JOIN AVION ON VOL.code_av = AVION.code_av
WHERE AVION.nom_av LIKE 'Airbus%';
6- Les noms et prénoms des pilotes qui habitent dans la ville de localisation
d’un AIRBUS :
SELECT DISTINCT nom_pil, prenom_pil
FROM PILOTE
WHERE adresse IN (
SELECT DISTINCT localisation
FROM AVION
WHERE nom_av LIKE 'Airbus%'
);
7-Les les pilotes qui habitent la ville de localisation d’un AIRBUS et qui sont
en service au départ d’une ville desservie (arr_vol) par KODJO :
SELECT DISTINCT p.nom_pil, p.prenom_pil
FROM PILOTE p
JOIN VOL v ON p.num_pil = v.num_pil
JOIN AVION a ON v.code_av = a.code_av
WHERE [Link] IN (
SELECT DISTINCT localisation
FROM AVION
WHERE nom_av LIKE 'Airbus%'
)
AND v.dep_vol IN (
SELECT DISTINCT arr_vol
FROM VOL
WHERE num_pil = (
SELECT num_pil
FROM PILOTE
WHERE nom_pil = 'KODJO'
)
);
8- Les avions dont la capacité est supérieure à toutes les capacités des avions
localisés à Paris :
SELECT *
FROM AVION
WHERE capacite > ALL (
SELECT capacite
FROM AVION
WHERE localisation = 'Paris'
);
9- Les avions dont la capacité est supérieure à au moins celle d’un avion
localisé à Lomé :
SELECT *
FROM AVION
WHERE capacite > ANY (
SELECT capacite
FROM AVION
WHERE localisation = 'Lomé'
);
10- Pour chaque pilote en service, donnons le nombre de vols assurés :
SELECT nom_pil, prenom_pil, COUNT(*) AS nombre_de_vols
FROM PILOTE
JOIN VOL ON PILOTE.num_pil = VOL.num_pil
GROUP BY nom_pil, prenom_pil;
11-Pour chaque AIRBUS en service l’après-midi et conduit par plus de 2 pilotes,
les trajets (dep_vol, arr_vol) assurés :
SELECT DISTINCT a.code_av, a.nom_av, v.dep_vol, v.arr_vol
FROM AVION a
JOIN VOL v ON a.code_av = v.code_av
JOIN PILOTE p ON v.num_pil = p.num_pil
WHERE a.nom_av LIKE 'Airbus%'
AND v.heure_dep > '12:00' AND v.heure_dep < '18:00'
GROUP BY a.code_av, v.dep_vol, v.arr_vol
HAVING COUNT(DISTINCT v.num_pil) > 2;
12- Le nombre de trajets assurés après 14h :
SELECT COUNT(*)
FROM VOL
WHERE heure_dep > '14:00';
13- la capacité́ maximum des avions par ville de localisation :
SELECT localisation, MAX(capacite) AS capacite_max
FROM AVION
GROUP BY localisation;
14- Le nombre de pilotes différents en service :
SELECT COUNT(DISTINCT num_pil) AS nombre_de_pilotes
FROM VOL;
15- les numéros d’avion dont la capacité est supérieure ou égale à la moyenne des
avions localisés dans la même ville :
SELECT code_av
FROM AVION a
WHERE capacite >= (
SELECT AVG(capacite)
FROM AVION
WHERE localisation = [Link]
);
16- chaque ville de localisation sauf « Abidjan » et sauf « Paris » de la
compagnie BOEING, donner les capacités minimum et maximum d’avions
qui s’y trouvent :
SELECT localisation, MIN(capacite) AS capacite_min, MAX(capacite) AS
capacite_max
FROM AVION
WHERE localisation NOT IN ('Abidjan', 'Paris')
GROUP BY localisation;
17- Les avions (numéros et noms) dont la capacité est comprise entre 150 et
300 et dont la localisation correspond à la ville de départ du vol « AF306/339
»:
SELECT a.code_av, a.nom_av
FROM AVION a
JOIN VOL v ON a.code_av = v.code_av
WHERE [Link] BETWEEN 150 AND 300
AND v.dep_vol = (
SELECT dep_vol
FROM VOL
WHERE code_vol = 'AF306/339'
);
18- Les avions (code_av, nom_av) qui avec une augmentation de 5% de leur
capacité ont une capacité supérieure à 350 :
SELECT code_av, nom_av
FROM AVION
WHERE capacite * 1.05 > 350;
19- La capacité moyenne des avions pour chaque ville ayant plus de 2 avions
(localisés) :
SELECT localisation, AVG(capacite) AS capacite_moyenne
FROM AVION
GROUP BY localisation
HAVING COUNT(*) > 2;
20- Les pilotes (avec leur nombre de vols) qui ont pour numéro 1, 4, 6, 8, 10,
12 ou 14 et qui assurent plus de 5 vols :
SELECT p.nom_pil, p.prenom_pil, COUNT(*) AS nombre_de_vols
FROM PILOTE p
JOIN VOL v ON p.num_pil = v.num_pil
WHERE p.num_pil IN (1, 4, 6, 8, 10, 12, 14)
GROUP BY p.num_pil, p.nom_pil, p.prenom_pil
HAVING COUNT(*) > 5;
21- Les avions dont la capacité est supérieure à toutes les moyennes de
capacités d’avion par ville :
SELECT *
FROM AVION a
WHERE capacite > ALL (
SELECT AVG(capacite)
FROM AVION
WHERE localisation = [Link]
GROUP BY localisation
);
22- Les vols au départ de Lomé entre 13h et 16H :
SELECT *
FROM VOL
WHERE dep_vol = 'Lomé'
AND heure_dep BETWEEN '13:00' AND '16:00';
23- Les pilotes dont le nom comprend un « U » en 2ème position :
SELECT *
FROM PILOTE
WHERE nom_pil LIKE '_U%';