Mini mémento de requêtes SQL

Mini mémento de requêtes SQL

Définition en SQL des tables PILOTE, AVION et VOL

PILOTE	PL#	PLNOM	VILLE
	1 2 3 4 5	SERGE JEAN CLAUDE ROBERT MICHEL	NICE PARIS GRENOBLE TOULOUSE PARIS

create table PILOTE
( PLnum number(3) primary key,
  PLNOM char(30),
  VILLE char(30)
);

insert into PILOTE values (1, 'SERGE', 'NICE');
insert into PILOTE values (2, 'JEAN', 'PARIS');
insert into PILOTE values (3, 'CLAUDE', 'GRENOBLE');
insert into PILOTE values (4, 'ROBERT', 'TOULOUSE');
insert into PILOTE values (5, 'MICHEL', 'PARIS');

AVION	AV#	AVNOM	VILLE
	100 101 102 103	A300 B707 A300 B727	NICE PARIS TOULOUSE TOULOUSE

create table AVION
( AVnum number(3) primary key,
  AVNOM char(10),
  VILLE char(30)
);

insert into AVION values (100, 'A300', 'NICE');
insert into AVION values (101, 'B707', 'PARIS');
insert into AVION values (102, 'A300', 'TOULOUSE');
insert into AVION values (103, 'B727', 'TOULOUSE');

VOL	VOL#	PL#	AV#	VD	VA	HD	HA
	IT100 IT101 IT102 IT103 IT104	1 2 1 3 3	100 100 101 102 102	NICE PARIS PARIS GRENOBLE TOULOUSE	PARIS TOULOUSE NICE TOULOUSE GRENOBLE	7 11 12 9 17	8 12 13 11 18

create table VOL
( VOLnum char(10) primary key,
  PLnum  number(3),
  AVnum  number(3),
  VD     char(30),
  VA     char(30),
  HD     number(2),
  HA     number(2)
);

insert into VOL values ('IT100', 1, 100, 'NICE', 'PARIS', 7, 8);
insert into VOL values ('IT101', 2, 100, 'PARIS', 'TOULOUSE', 11, 12);
insert into VOL values ('IT102', 1, 101, 'PARIS', 'NICE', 12, 13);
insert into VOL values ('IT103', 3, 102, 'GRENOBLE', 'TOULOUSE', 9, 11);
insert into VOL values ('IT104', 3, 102, 'TOULOUSE', 'GRENOBLE', 17, 18);

Exemples de formulation de requêtes en SQL

Noms des pilotes qui assurent un vol au départ de Paris

select distinct PLNOM
from PILOTE, VOL
where VD = 'PARIS'
  and VOL.PLnum = PILOTE.PLnum;

Noms des pilotes qui conduisent un A300

select distinct PLNOM
from PILOTE, AVION, VOL
where AVNOM = 'A300'
  and AVION.AVnum = VOL.AVnum
  and PILOTE.PLnum = VOL.PLnum;

select distinct PLNOM
from PILOTE
where PLnum in (select PLnum
                from VOL
                where AVnum in (select AVnum
                                from AVION
                                where AVNOM = 'A300'));

select distinct PLNOM
from PILOTE, VOL
where PILOTE.PLnum = VOL.PLnum
  and AVnum in (select AVnum
                from AVION
                where AVnum = VOL.AVnum
                  and AVNOM = 'A300');

select distinct PLNOM
from PILOTE
where exists (select *
              from VOL, AVION
              where AVNOM = 'A300'
                and AVION.AVnum = VOL.AVnum
                and PILOTE.PLnum = VOL.PLnum);

select distinct PLNOM
from PILOTE, AVION
where AVNOM = 'A300'
  and exists (select *
              from VOL
              where AVION.AVnum = VOL.AVnum
                and PILOTE.PLnum = VOL.PLnum);

Noms des pilotes qui habitent dans une ville de localisation d'un A300

select distinct PLNOM
from PILOTE, AVION
where AVNOM = 'A300'
  and AVION.VILLE = PILOTE.VILLE;

Noms des pilotes vérifiant les deux conditions précédantes

Pilotes conduisant un A300 localisé dans leur ville

select distinct PLNOM
from PILOTE, AVION, VOL
where PILOTE.PLnum = VOL.PLnum
  and AVION.AVnum = VOL.AVnum
  and AVION.VILLE = PILOTE.VILLE
  and AVNOM = 'A300';

Pilotes conduisant un A300, et habitant dans la localisation d'un A300 quelconque

select distinct PLNOM
from PILOTE, AVION AVION1, VOL
where PILOTE.PLnum = VOL.PLnum
  and AVION1.AVnum = VOL.AVnum
  and AVION1.AVNOM = 'A300'
  and exists (select *
              from AVION AVION2
              where AVION2.AVNOM = 'A300'
                and AVION2.VILLE = PILOTE.VILLE);

Trajets assurés par chaque A300 en service l'après-midi et conduit par plus de 2 pilotes

Trajets assurés l'après-midi

select distinct VOL1.VD, VOL1.VA
from AVION, VOL VOL1
where AVNOM = 'A300'
  and AVION.AVnum = VOL1.AVnum
  and VOL1.HD >= 12 and VOL1.HA <= 18
  and 2 <= (select count(distinct VOL2.PLnum)
            from VOL VOL2
            where VOL2.AVnum = VOL1.AVnum);

Trajets assurés sur la journée

select distinct VOL1.VD, VOL1.VA
from AVION, VOL VOL1
where AVNOM = 'A300'
  and AVION.AVnum = VOL1.AVnum
  and AVION.AVnum in (select distinct VOL2.AVnum
                      from VOL VOL2
                      where VOL2.HD >= 12 and VOL2.HA <= 18)
  and 2 <= (select count(distinct VOL3.PLnum)
            from VOL VOL3
            where VOL3.AVnum = VOL1.AVnum);

Paires de villes (avion localisé dans 1^ère conduit par pilote résidant dans 2^nde)

select distinct AVION.VILLE as AVILLE, PILOTE.VILLE as PVILLE
from AVION, PILOTE, VOL
where VOL.AVnum = AVION.AVnum
  and VOL.PLnum = PILOTE.PLnum;

Numéros de pilotes conduisant tous les A300

select distinct PILOTE.PLnum
from PILOTE
where not exists (select *
                  from AVION
                  where AVNOM = 'A300'
                    and not exists (select *
                                    from VOL
                                    where VOL.AVnum = AVION.AVnum
                                      and VOL.PLnum = PILOTE.PLnum));

Numéros des pilotes qui ne terminent pas la journée là où ils résident

select distinct PILOTE.PLnum
from PILOTE, VOL VOL1
where PILOTE.PLnum = VOL1.PLnum
  and VOL1.HA = (select max(VOL2.HA)
                 from VOL VOL2
                 where VOL1.PLnum = VOL2.PLnum)
  and VOL1.VA <> VILLE;

Vérification sous Oracle

Last update 02/01/2001