CREATE TABLE Pays
(
   Ville varchar(15) PRIMARY KEY,
   Nompays varchar(20)
);

CREATE TABLE Avion
(
   NumAv number(4) PRIMARY KEY,
   ModAv varchar(20),
   CapAv number(4),
   VilleAv varchar(15),
   CONSTRAINT Avion_PAYS_VILLE_fk FOREIGN KEY (VilleAv) REFERENCES PAYS (VILLE)
);

CREATE TABLE pilote
(
   NumPil number(4) PRIMARY KEY,
   NomPil varchar(20),
   dateNaissPil date,
   villePil varchar(15),
   CONSTRAINT pilote_PAYS_VILLE_fk FOREIGN KEY (villePil) REFERENCES PAYS (VILLE)
);

CREATE TABLE client
(
   NumCl number(5) PRIMARY KEY,
   NomCl varchar(15),
   NumNomCl number(3),
   NomRueCl varchar(50),
   CodePosteCl number(5),
   VilleCl varchar(15),
   email varchar(50),
   CONSTRAINT client_PAYS_VILLE_fk FOREIGN KEY (VilleCl) REFERENCES PAYS (VILLE)
);

CREATE TABLE VOL
(
   NumVol varchar(5) PRIMARY KEY,
   VilleD varchar(15),
   VilleA varchar(15),
   DateD date,
   DateA date,
   NumPil number(4),
   NumAv number(4),
   CoutVol number(4),
   CONSTRAINT VOL_PAYS_VILLE_fk FOREIGN KEY (VilleD) REFERENCES PAYS (VILLE),
   CONSTRAINT VOL_PILOTE_NUMPIL_fk FOREIGN KEY (NumPil) REFERENCES PILOTE (NUMPIL),
   CONSTRAINT VOL_AVION_NUMAV_fk FOREIGN KEY (NumAv) REFERENCES AVION (NUMAV),
   CONSTRAINT VOL_PAYS_VILLE_fk_2 FOREIGN KEY (VilleA) REFERENCES PAYS (VILLE)
);

CREATE TABLE classe
(
   NumVol varchar(5),
   NomClasse varchar(15),
   CoeffPlace number(4,3),
   CoeffPrix number(2,1),
   CONSTRAINT classe_pk PRIMARY KEY (NumVol, NomClasse),
   CONSTRAINT classe_VOL_NUMVOL_fk FOREIGN KEY (NumVol) REFERENCES VOL (NUMVOL)
);

CREATE TABLE reservation
(
   NumCl number(5),
   NumVol varchar(5),
   NomClasse varchar(15),
   nbPlaces number(3),
   CONSTRAINT reservation_pk PRIMARY KEY (NumCl, NumVol, NomClasse),
   CONSTRAINT reservation_CLIENT_NUMCL_fk FOREIGN KEY (NumCl) REFERENCES CLIENT (NUMCL),
   CONSTRAINT reservation_VOL_NUMVOL_fk FOREIGN KEY (NumVol) REFERENCES VOL (NUMVOL)
);

INSERT INTO louppe7u.PAYS (VILLE, NOMPAYS) VALUES ('Paris', 'France');
INSERT INTO louppe7u.PAYS (VILLE, NOMPAYS) VALUES ('Bordeaux', 'France');
INSERT INTO louppe7u.PAYS (VILLE, NOMPAYS) VALUES ('Grenoble', 'France');
INSERT INTO louppe7u.PAYS (VILLE, NOMPAYS) VALUES ('Marseille', 'France');
INSERT INTO louppe7u.PAYS (VILLE, NOMPAYS) VALUES ('Lyon', 'France');
INSERT INTO louppe7u.PAYS (VILLE, NOMPAYS) VALUES ('Aix', 'France');
INSERT INTO louppe7u.PAYS (VILLE, NOMPAYS) VALUES ('La Ciotat', 'France');
INSERT INTO louppe7u.PAYS (VILLE, NOMPAYS) VALUES ('Trets', 'France');
INSERT INTO louppe7u.PAYS (VILLE, NOMPAYS) VALUES ('Amsterdam', 'Pays-Bas');
INSERT INTO louppe7u.PAYS (VILLE, NOMPAYS) VALUES ('Madrid', 'Espagne');
INSERT INTO louppe7u.PAYS (VILLE, NOMPAYS) VALUES ('Rome', 'Italie');
INSERT INTO louppe7u.PAYS (VILLE, NOMPAYS) VALUES ('Milan', 'Italie');
INSERT INTO louppe7u.PAYS (VILLE, NOMPAYS) VALUES ('Londres', 'Angleterre');
INSERT INTO louppe7u.PAYS (VILLE, NOMPAYS) VALUES ('Lille', 'France');

Insert into Avion Values (1234,'AIRBUS A300',30,'Paris') ;
Insert into Avion Values (1512,'AIRBUS A600',60,'Grenoble') ;
Insert into Avion Values (7932,'BOEING 707',20,'Marseille') ;
Insert into Avion Values (8121,'Cesna',4,'Marseille') ;
Insert into Avion Values (9867, 'Cesna',4,'Paris') ;
Insert into Avion Values (1259,'AIRBUS A300',30,'Lyon') ;
Insert into Avion Values (7713,'BOEING 737',50,'Bordeaux') ;
Insert into Avion Values (1599,'BOEING 737',50,'Lyon') ;
Insert into Avion Values (3384,'AIRBUS A200',20,'Grenoble') ;
Insert into Avion Values (6691, 'BOEING 747',60,'Paris') ;

Insert into Pilote Values (7802, 'DURAND','01/10/1950','Bordeaux') ;
Insert into Pilote Values (6991, 'DAVIR','25/04/1954','Paris') ;
Insert into Pilote Values (6789, 'DUPUIS','20/05/1970','Marseille') ;
Insert into Pilote Values (4003, 'LAMBERT','12/11/1965','Grenoble') ;
Insert into Pilote Values (5002, 'DURAND','14/08/1951','Paris') ;
Insert into Pilote Values (8837, 'SMITH','23/09/1971','Bordeaux') ;
Insert into Pilote Values (7654, 'ROBERT','11/02/1954','Marseille') ;
Insert into Pilote Values (3947, 'SCHMIDT','03/01/1963','Paris') ;
Insert into Pilote Values (3240, 'ALONSO','30/07/1968', 'Paris');
Insert into Pilote Values (5407, 'DELTAN','08/05/1968','Lyon') ;


Insert into Client Values (7003,'DUTIER',7,'Gambetta',13100,'Aix', 'alec.dutier@gmail.com');
Insert into Client Values (9012,'RIBEIRA',34,'Mistral',13600,'La Ciotat', 'paul.ribiera@yahoo.fr');
Insert into Client Values (8347,'VALOIS',62,'Mitterand',13600,'La Ciotat','clement.valois@hotmail.fr');
Insert into Client Values (6692,'GARI',3,'Mirabeau',13530,'Trets','gino.gari@gmail.com');
Insert into Client Values (7387,'CARRO',122,'Serpolet',13008,'Marseille','vivak.carro@yahoo.fr');
Insert into Client Values (5407,'FAVER',15,'Hugo',13530,'Trets', 'david.faver@me.com');
Insert into Client Values (7631,'BOSS',17,'Papere',13001,'Marseille', 'florian.boss@orange.fr');
Insert into Client Values (2781,'MARI',9,'Montigny',13100,'Aix', 'stephane.mari@free.fr');
Insert into Client Values (1107,'GIROLLE',53,'St Anne',13100,'Aix','anne.girolle@gmail.com');
Insert into Client Values (1752,'RALLINO',84,'Italie',13002,'Marseille',null );

Insert into Vol Values ('V12A1','Marseille','Paris',to_date('08/01/2019-15:17','DD/MM/YYYY-HH24:MI'),to_date('08/01/2019-16:35','DD/MM/YYYY-HH24:MI'),7654,7713, 35);
Insert into Vol Values ('V9000','Marseille','Lyon', to_date('08/01/2019-20:30','DD/MM/YYYY-HH24:MI'),to_date('08/01/2019-21:10','DD/MM/YYYY-HH24:MI'), 5407,6691, 40);
Insert into Vol Values ('V7B90','Marseille','Bordeaux',to_date('17/02/2019-07:10','DD/MM/YYYY-HH24:MI'), to_date('17/02/2019-08:25','DD/MM/YYYY-HH24:MI'),8837,8121,32);
Insert into Vol Values ('V66AA','Marseille','Grenoble',to_date('17/02/2019-07:05','DD/MM/YYYY-HH24:MI'),to_date('17/02/2019-08:15','DD/MM/YYYY-HH24:MI'),5002,1512,50);
Insert into Vol Values ('V0702','Paris', 'Lyon',to_date('30/11/2019-15:00','DD/MM/YYYY-HH24:MI'),to_date('30/11/2019-16:05','DD/MM/YYYY-HH24:MI'),4003,6691,43);
Insert into Vol Values ('V8D90','Paris','Marseille',to_date('18/02/2019-16:10','DD/MM/YYYY-HH24:MI'),to_date('18/02/2019-17:40','DD/MM/YYYY-HH24:MI'),5002,1234,35);
Insert into Vol Values ('V8E90','Paris','Bordeaux',to_date('04/02/2019-19:30','DD/MM/YYYY-HH24:MI'),to_date('04/02/2019-20:35','DD/MM/YYYY-HH24:MI'),7654,7713,38);
Insert into Vol Values ('V8000', 'Lyon','Paris',to_date('14/12/2019-18:10','DD/MM/YYYY-HH24:MI'),to_date('14/12/2019-19:20','DD/MM/YYYY-HH24:MI'),3947,3384,39);
Insert into Vol Values ('V74FG','Bordeaux','Paris',to_date('05/02/2019-12:20','DD/MM/YYYY-HH24:MI'),to_date('05/02/2019-13:30','DD/MM/YYYY-HH24:MI'),3240,1599,41);
Insert into Vol Values ('V327B', 'Lyon','Marseille',to_date('06/02/2019-20:00','DD/MM/YYYY-HH24:MI'),to_date('06/02/2019-21:30','DD/MM/YYYY-HH24:MI'),7802,9867,70);
Insert into Vol Values('V9200', 'Lyon', 'Amsterdam', to_date('02/06/2019-20:00','DD/MM/YYYY-HH24:MI'), to_date('02/06/2019-21:50','DD/MM/YYYY-HH24:MI'), 6991, 1234, 74);
Insert into Vol Values('V9201', 'Lyon', 'Madrid', to_date('02/05/2019-08:00','DD/MM/YYYY-HH24:MI'), to_date('02/05/2019-09:50','DD/MM/YYYY-HH24:MI'), 6991, 1512, 63);
Insert into Vol Values('V9202', 'Lyon', 'Rome', to_date('02/02/2019 10:00','DD/MM/YYYY-HH24:MI'), to_date('02/02/2019 10:50','DD/MM/YYYY-HH24:MI'), 6991, 7713, 42);
Insert into Vol Values('V9203', 'Paris', 'Bordeaux', to_date('02/04/2019-13:00','DD/MM/YYYY-HH24:MI'), to_date('02/04/2019-14:50','DD/MM/YYYY-HH24:MI'), 6991, 8121, 84);
Insert into Vol Values('V9204', 'Lille', 'Marseille',to_date( '02/06/2019-15:00','DD/MM/YYYY-HH24:MI'), to_date('02/06/2019-16:50','DD/MM/YYYY-HH24:MI'), 6991, 3384, 95);
Insert into Vol Values('V9205', 'Lyon', 'Milan', to_date('02/05/2019-18:00','DD/MM/YYYY-HH24:MI'), to_date('02/05/2019-19:50','DD/MM/YYYY-HH24:MI'), 6991, 6691, 74);
Insert into Vol Values('V9206', 'Lyon', 'Londres', to_date('02/01/2019-17:00','DD/MM/YYYY-HH24:MI'), to_date('02/01/2019-18:50','DD/MM/YYYY-HH24:MI'), 6991, 7932, 84);


Insert into Reservation Values(7003,'V9000','AFF',3);
Insert into Reservation Values(1752,'V9000','ECO',4);
Insert into Reservation Values(1107,'V8000','TOUR',2);
Insert into Reservation Values(1107,'V8D90','TOUR',2);
Insert into Reservation Values(2781,'V327B','AFF',1);
Insert into Reservation Values(6692,'V8E90','ECO',3);
Insert into Reservation Values(6692,'V327B','TOUR',2);
Insert into Reservation Values(9012,'V74FG','ECO',1);
Insert into Reservation Values(5407,'V9000','AFF',2);
Insert into Reservation Values(5407,'V0702','TOUR',4);


Insert into classe values ('V9200',	'ECO',0.2,1.0);
Insert into classe values ('V9201',	'ECO',0.2,1.0);
Insert into classe values ('V9202',	'ECO',0.2,1.0);
Insert into classe values ('V9203',	'ECO',0.2,1.0);
Insert into classe values ('V9204',	'ECO',0.2,1.0);
Insert into classe values ('V9205',	'ECO',0.2,1.0);
Insert into classe values ('V9206',	'ECO',0.2,1.0);
Insert into classe values ('V9000', 'AFF', 0.4,2) ;
Insert into classe values ('V9000', 'TOUR', 0.4,1.5) ;
Insert into classe values ('V9000', 'ECO', 0.2,1) ;
insert into classe values ('V0702', 'ECO', 0.2, 1);
insert into classe values ('V12A1', 'ECO', 0.2, 1);
insert into classe values ('V66AA', 'ECO', 0.2, 1);
insert into classe values ('V74FG', 'ECO', 0.2, 1);
insert into classe values ('V7B90', 'ECO', 0.2, 1);
insert into classe values ('V8000', 'ECO', 0.2, 1);
insert into classe values ('V8D90', 'ECO', 0.2, 1);
insert into classe values ('V8E90', 'ECO', 0.2, 1);
insert into classe values ('V327B', 'ECO', 0.2, 1);
insert into classe values ('V327B', 'AFF', 0.2, 2);
insert into classe values ('V8000', 'TOUR', 0.6, 1.5);
insert into classe values ('V8D90', 'TOUR', 0.6, 1.5);
insert into classe values ('V327B', 'TOUR', 0.6, 1.5);
insert into classe values ('V0702', 'TOUR', 0.6, 1.5);

--question 5
select avg(CoutVol) from VOL
inner join pays on VOL.VilleD = Pays.Ville
group by Ville;

--question 6
select count(DISTINCT VilleA, VilleD) from VOL

;
--question 7
select sum(nbPlaces) from reservation
where NumVol ='V9000';

--question 8
select count(*) as nombreservation, client.NomCl from reservation
inner join client on reservation.NumCl = client.NumCl
group by client.NomCl;

--question 9 ENLEVER LA COLONNE NBRES
select count(*) as nbreservation,  client.NomCl, client.NumCl from reservation
inner join client on reservation.NumCl = client.NumCl
group by client.NomCl, client.NumCl
having count(*)>1;

--question 10
select NomPil, NumPil from pilote
where NumPil not in (select distinct NumPil from VOL);

--question 11
select sum((DateA - DateD)*24) as tempsvol, pilote.NumPil from VOL
inner join pilote on VOL.NumPil = pilote.NumPil
group by pilote.NumPil;

--question 12 TO DO
select max(tempsvol)from (
                         select sum((DateA - DateD)*24) as tempsvol, pilote.NumPil from VOL
inner join pilote on VOL.NumPil = pilote.NumPil
group by pilote.NumPil
                            )
;


--question 13
select count(distinct avion.ModAv) as nbavionpilotes, pilote.NomPil from Avion
inner join VOL on Avion.NumAv = VOL.NumAv
inner join pilote on VOL.NumPil = pilote.NumPil
group by pilote.NomPil
having count(distinct avion.ModAv) = (select count(distinct avion.ModAv) as nbavionpilotes from Avion)
;

--question 14