Polytech Marseille.
Informatique 3e année
Bases de données
TP 2
Correction
1. Afficher le nombre de villes de départ.
SELECT COUNT(DISTINCT VilleD) FROM Vols;
2. Afficher les vols qui utilisent l’avion 240 et partent soit de Marseille soit de Paris.
SELECT NumVol FROM Vols WHERE NumAv=240 AND (VilleD='Marseille' OR VilleD='Paris');
3. Afficher les pilotes qui n’habitent ni à Marseille ni à Nice.
SELECT NumPil FROM Pilotes WHERE VillePil NOT IN ('Marseille', 'Nice');
4. Afficher les pilotes qui assurent un vol qui ne part ni de Marseille ni de Paris.
SELECT DISTINCT NumPil FROM Vols WHERE NOT (VilleD='Marseille' OR VilleD='Paris');
5. Numéros des pilotes qui assurent au moins un trajet autre que Paris-Marseille.
SELECT DISTINCT NumPil FROM Vols WHERE NOT (VilleD='Paris' AND VilleA='Marseille');
6. Numéros des vols auxquels ni l’avion 101 ni l’avion 401 n’ont été affectés. (Les vols auxquels
aucun avion n’a été affecté doivent être sélectionnés.)
SELECT NumVol, NumAv FROM Vols
WHERE NumAv NOT IN (101,401) OR NumAv IS NULL;
7. Afficher les avions par ordre alphabétique et par capacité décroissante.
SELECT DISTINCT NomAv, CapAv FROM Avions ORDER BY nomAv, CapAv DESC;
8. Numéros et noms des avions dont le nom ne contient pas la lettre ”a”.
SELECT NumAv, NomAv FROM Avions WHERE lower(NomAv) NOT LIKE '%a%';
9. Afficher les pilotes dont le nom contient lettre ”a” en deuxième position.
SELECT NumPil, NomPil FROM Pilotes WHERE NomPil LIKE '_a%';
10. Numéros des vols dont la durée est comprise entre 2h et 4h.
SELECT NumVol FROM Vols
WHERE 24*(DateA-DateD) BETWEEN 2 AND 4;
11. Afficher tous les pilotes qui effectuent des vols au départ de Marseille et qui durent plus d’une
heure.
SELECT DISTINCT NumPil FROM Vols
WHERE VilleD='Marseille' AND (24*(DateA-DateD))>1;
12. Afficher les vols les plus longs.
SELECT NumVol FROM Vols
WHERE DateA-DateD = (SELECT max(DateA-DateD) FROM Vols);
13. Numéros des vols qui assurent le même jour le trajet inverse du vol V101.
SELECT NumVol FROM Vols
WHERE (VilleD, VilleA, trunc(DateD)) = (SELECT VilleA, VilleD, trunc(DateD)
FROM Vols WHERE NumVol='V101');
1
Polytech Marseille. Informatique 3e année
Bases de données
14. Numéros et noms des clients qui ont réservés sur un vol transportant Mr Lorentz.
SELECT DISTINCT [Link], NomCl FROM Reservations R1, Clients C1
WHERE [Link]=[Link]
AND NumVol IN (SELECT NumVol FROM Reservations R2, Clients C2
WHERE [Link]=[Link] AND [Link]='Lorentz');
15. Numéros des clients ayant effectués plus de 3 réservations.
SELECT NumCl FROM Reservations
GROUP BY NumCl
HAVING COUNT(*) > 3;
16. Numéros des pilotes qui effectuent le plus de vols.
SELECT NumPil FROM Vols
WHERE NumPil IS NOT NULL
GROUP BY NumPil
HAVING COUNT(*) >= all (SELECT COUNT(*) FROM Vols WHERE NumPil IS NOT NULL
GROUP BY NumPil);
17. Pour chaque vol réservé, donner le numéro des clients qui ont réservés le plus grand nombre de
places.
SELECT NumVol, NumCl FROM reservations R1
GROUP BY NumCl,NumVol
HAVING SUM(nbplaces) >= ALL (SELECT SUM(nbplaces) FROM reservations R2
WHERE [Link]=[Link] GROUP BY numCl);
18. Nombre d’avions qui ont une capacité inconnue, et aussi la plus petite des capacités connues.
SELECT COUNT(*) - COUNT(CapAv) AS NbreCapInconnues, MIN(CapAv) AS CapMin
FROM Avions;
19. Numéro de chaque pilote et nombre d’avions différents qu’il pilote.
SELECT [Link], COUNT(DISTINCT NumAv) AS NbreAv
FROM Pilotes P LEFT JOIN Vols V ON [Link] = [Link]
GROUP BY [Link];
20. Pour chaque vol à destination de Marseille, numéro du vol et nom de l’avion qui lui est affecté.
SELECT NumVol, NomAv
FROM Vols V LEFT JOIN Avions A ON [Link] = [Link]
WHERE VilleA='Marseille';
Requêtes avancées utilisant des fonctions disponibles dans ORACLE
21. Numéro et ville de résidence de chaque pilote. Lorsque la ville est Inconnue, afficher ”Ville
Inconnue” comme valeur.
SELECT NumPil, nvl(VillePil,'Ville Inconnue !') FROM Pilotes;
22. Nombre de places réservées sur le vol V101.
SELECT SUM(nvl(NbPlaces,0)) AS Places_Reserv
FROM Reservations WHERE NumVol = 'V101';
2
Polytech Marseille. Informatique 3e année
Bases de données
23. Trier les vols à destination de Marseille, par ordre croissant sur les dates de départ et par ordre
décroissant sur les heures de départ. Afficher le numéro de vol, la ville, la date et l’heure de départ.
SELECT NumVol, VilleD, DateD FROM vols
WHERE VilleA = 'Marseille'
ORDER BY trunc(DateD), DateD DESC;
24. Noms et âges des pilotes habitant Nice et âgés de plus de 35 ans.
SELECT NomPil, to_number(to_char(Sysdate,'YYYY'))-NaisPil AS age
FROM Pilotes
WHERE to_number(to_char(Sysdate,'YYYY'))-NaisPil> 35 AND VillePil='Nice' ;
25. Afficher les noms d’avions en supprimant la référence du modèle. Par exemple, ”Boeing
747” deviendra ”Boeing”, de même ”Airbus A310” sera transformé en ”Airbus”.
SELECT DISTINCT rtrim(nomav,' A1234567890') AS Nom FROM Avions;
ou plus général :
SELECT DISTINCT rtrim(nvl(substr(nomav,1,instr(nomav,' ')-1),nomav),'1234567890') AS Nom
FROM Avions;
26. Nombre de vols assurés au départ de Paris pour chaque jour de vol du mois d’avril.
SELECT to_char(trunc(DateD),'dd/mm/yyyy') AS jour, COUNT(*) AS NbreVols FROM Vols
WHERE to_char(DateD,'mm') = '04' AND VilleD = 'Paris'
GROUP BY trunc(DateD);
27. Pourcentage de vols assurés au départ de Paris pour le mois d’avril.
SELECT round(100*COUNT(decode(VilleD,'Paris',1,null))/COUNT(*)) AS "%" FROM Vols
WHERE to_char(DateD,'mm') = '04';
28. Y a-t-il au moins un vol au départ de Marseille vers Amsterdam tous les jours du mois de mai ?
(Répondre par ”oui” ou par ”non”).
SELECT decode(COUNT(*),0,'non','oui') AS Reponse FROM dual
WHERE exists (SELECT 1 FROM Vols
WHERE villed='Marseille' AND villea='Amsterdam' AND to_char(DateD,'mm') = '05'
HAVING COUNT(DISTINCT trunc(DateD)) = to_char(last_day(to_date('05','mm')),'dd'));
29. Numéros des pilotes qui assurent en avril 2007 un nombre d’heures de vol supérieur à 170.
SELECT numpil FROM Vols
WHERE numpil IS NOT NULL AND (to_char(DateD,'mm') = '04' OR to_char(DateA,'mm') = '04')
GROUP BY numpil
HAVING SUM(least(DateA,add_months(to_date('04/2007','MM/YYYY'),1))
– greatest(DateD,to_date('04/2007','MM/YYYY')))*24 > 170;
Requêtes avec des vues
30. Nombre de places existant dans chaque classe du vol V240 (idem avec V590 et V650).
create or replace view PlacesEx_par_vol_et_classe as
select NumVol, Classe, Trunc(CapAv*CoeffPlace) NbEx
from Vols V, Avions A, Defclasses D
where [Link] = [Link] and [Link] = [Link](+);
ou
3
Polytech Marseille. Informatique 3e année
Bases de données
create or replace view PlacesEx_par_vol_et_classe as
select NumVol, Classe, Trunc(CapAv*CoeffPlace) NbEx
from (Vols V left join Avions A on [Link] = [Link]) inner join
Defclasses D on [Link] = [Link];
select Classe, NbEx from PlacesEx_par_vol_et_classe where NumVol = 'V240';
select Classe, NbEx from PlacesEx_par_vol_et_classe where NumVol = 'V590';
select Classe, NbEx from PlacesEx_par_vol_et_classe where NumVol = 'V650';
31. Nombre de places réservées dans chaque classe du vol V101 (idem avec V240 et V590).
create or replace view VolsClasses
as select [Link], Classe from Vols V, Defclasses D where [Link] = [Link];
create or replace view PlacesRes_par_vol_et_classe as
select [Link], [Link], nvl(sum(NbPlaces),0) NbRes
from VolsClasses V, Reservations R
where [Link] = [Link] (+) and [Link] = [Link] (+)
group by [Link], [Link];
ou
create or replace view PlacesRes_par_vol_et_classe as
select [Link], [Link], nvl(sum(NbPlaces),0) NbRes
from VolsClasses V left join Reservations R
on [Link] = [Link] and [Link] = [Link]
group by [Link], [Link];
select Classe,Nbres from PlacesRes_par_vol_et_classe where NumVol='V101';
select Classe,Nbres from PlacesRes_par_vol_et_classe where NumVol='V240';
select Classe,Nbres from PlacesRes_par_vol_et_classe where NumVol='V590';
32. Nombre de places disponibles (non réservées) dans chaque classe du vol V101 (idem avec V240
et V590).
create view PlacesDispo_par_vol_et_classe as
select [Link], [Link], NbEx - NVL(sum(NbPlaces),0) NbDispo
from PlacesEx_par_vol_et_classe P, Reservations R
where [Link] = [Link] (+) and [Link]=[Link] (+)
group by [Link], [Link], NbEx;
ou
create view PlacesDispo_par_vol_et_classe as
select [Link], [Link], NbEx - NVL(sum(NbPlaces),0) NbDispo
from PlacesEx_par_vol_et_classe P left join Reservations R
on [Link] = [Link] and [Link]=[Link]
group by [Link], [Link], NbEx;
select Classe, NbDispo from PlacesDispo_par_vol_et_classe where NumVol='V101';
select Classe, NbDispo from PlacesDispo_par_vol_et_classe where NumVol='V240';
select Classe, NbDispo from PlacesDispo_par_vol_et_classe where NumVol='V590';
4
Polytech Marseille. Informatique 3e année
Bases de données
En bonus, voici les requêtes SQL pour créer les tables que nous avons utilisées :
CREATE TABLE AVIONS(
NumAv NUMBER(4) CONSTRAINT pk_avion PRIMARY KEY,
NomAv VARCHAR2(20),
CapAv NUMBER(4) CONSTRAINT dom_capav_avion CHECK (CapAv>0),
VilleAv VARCHAR2(15)
);
CREATE TABLE PILOTES(
NumPil NUMBER(4) CONSTRAINT pk_pilote PRIMARY KEY,
NomPil VARCHAR2(20) CONSTRAINT nn_nom_pilote NOT NULL,
NaisPil NUMBER(4) CONSTRAINT dom_nais_pilote CHECK (NaisPil>1900),
VillePil VARCHAR2(15)
);
CREATE TABLE CLIENTS(
NumCl NUMBER(5) CONSTRAINT pk_client PRIMARY KEY,
NomCl VARCHAR2(20) ,
NumRueCl NUMBER(3) CONSTRAINT dom_numrue_client CHECK (NumRueCl > 0),
NomRueCl VARCHAR2(50),
CodePosteCl NUMBER(5) CONSTRAINT dom_codepostal_client CHECK (CodePosteCl > 0),
VilleCl VARCHAR2(15)
);
CREATE TABLE VOLS(
NumVol VARCHAR2(5) CONSTRAINT pk_vol PRIMARY KEY,
VilleD VARCHAR2(15) CONSTRAINT nn_villed_vol NOT NULL,
VilleA VARCHAR2(15) CONSTRAINT nn_villea_vol NOT NULL,
DateD Date,
DateA Date,
NumPil NUMBER(4) CONSTRAINT pil_ref_vol references PILOTES(NumPil),
NumAv NUMBER(4) CONSTRAINT avion_ref_vol references AVIONS(NumAv),
CoutVol NUMBER(10) CONSTRAINT dom_coutvol_vol CHECK (CoutVol>0),
CONSTRAINT dom_numvol_vol CHECK(NumVol like 'V%'),
CONSTRAINT dates_vol CHECK (DateD < DateA),
CONSTRAINT villes_vol CHECK (VilleD != VilleA)
);
CREATE TABLE DEFCLASSES(
NumVol VarChar2(5) CONSTRAINT vol_ref_defclasse references VOLS(NumVol),
Classe VARCHAR2(12),
CoeffPlace NUMBER(3,2) CONSTRAINT nn_coeffplace_defclasse NOT NULL,
CoeffPrix NUMBER(3,2) CONSTRAINT dom_coeffprix_defclasse CHECK (CoeffPrix >= 1),
CONSTRAINT pk_defclasse PRIMARY KEY (NumVol,Classe),
CONSTRAINT quota CHECK ((CoeffPrix <= 2 and CoeffPlace <= 0.5) or CoeffPrix > 2),
CONSTRAINT dom_coeffplace_defclasse CHECK (CoeffPlace between 0 and 1),
);
CREATE TABLE RESERVATIONS(
NumCl NUMBER(5) CONSTRAINT client_ref_reserv references CLIENTS(NumCl),
NumVol VARCHAR2(5),
Classe VARCHAR2(12),
NbPlaces NUMBER(4) CONSTRAINT nn_nbplaces_reserv NOT NULL,
CONSTRAINT pk_reserv PRIMARY KEY (NumCl, NumVol, Classe),
CONSTRAINT classe_ref_reserv foreign key (NumVol,Classe) references DEFCLASSES(NumVol,Classe),
CONSTRAINT dom_nbplaces_reserv CHECK (NbPlaces >0)
);
