-- PILOTE (NUMPIL, NOMPIL, ADR, SAL)
-- AVION	(NUMAV, NOMAV, CAP, LOC)
-- VOL	(NUMVOL, NUMPIL, NUMAV, VILLE_DEP, VILLE_ARR, H_DEP, H_ARR)

drop table if exists pilote;
drop table if exists avion;
drop table if exists vol;

create table pilote (
    numpil integer primary key,
    nompil text,
    adr text,
    sal integer
);

create table avion (
    numav integer primary key,
    nomav text,
    cap integer,
    loc text
);

create table vol (
    numvol integer primary key,
    numpil integer references pilote(numpil),
    numav integer references avion(numav),
    ville_dep text,
    ville_arr text,
    h_dep real,
    h_arr real
);

insert into pilote values (0, 'VIDAL', 'MONTPELLIER', 100000);
insert into pilote values (1, 'MARTINEZ', 'MONTPELLIER', 500000);
insert into pilote values (2, 'ROUCHON', 'MONTPELLIER', 90000);
insert into pilote values (3, 'DELASOUCHE', 'PARIS', 350000);
insert into pilote values (4, 'FOUCHEAU', 'PARIS', 320000);
insert into pilote values (5, 'TOUROND', 'LYON', 250000);

insert into avion values (0, 'AIRBUS', 130, 'MONTPELLIER');
insert into avion values (1, 'BOEING', 250, 'PARIS');
insert into avion values (2, 'AIRBUS', 600, 'PARIS');
insert into avion values (3, 'AIRBUS', 551, 'MONTPELLIER');
insert into avion values (4, 'BOEING', 200, 'LYON');

insert into vol values (0, 1, 2, 'MONTPELLIER', 'LYON', 20.00, 20.45);
insert into vol values (1, 1, 2, 'LYON', 'MONTPELLIER', 21.15, 22.00);
insert into vol values (2, 0, 0, 'MONTPELLIER', 'PARIS', 20.00, 21.00);
insert into vol values (3, 3, 0, 'PARIS', 'NEW YORK', 08.30, 16.00);
insert into vol values (4, 3, 0, 'NEW YORK', 'PARIS', 16.30, 04.00);
insert into vol values (5, 5, 4, 'MONTPELLIER', 'DENVER', 16.30, 04.00);
insert into vol values (6, 0, 0, 'PARIS', 'MONTPELLIER', 22.00, 23.00);

--1
SELECT * FROM AVION WHERE CAP>550;

--2
SELECT NUMPIL, VILLE_DEP FROM VOL ;

--3
SELECT * FROM PILOTE;

--4
SELECT NUMAV, NOMAV FROM AVION WHERE LOC='MONTPELLIER';

--5
SELECT NOMPIL FROM PILOTE WHERE SAL>10000 AND ADR='PARIS';

--6
SELECT * FROM VOL WHERE VILLE_DEP='MONTPELLIER' AND VILLE_ARR='PARIS' AND H_DEP>20.00;

--7
SELECT NUMAV, NOMAV FROM AVION WHERE LOC='MONTPELLIER' OR CAP<550;

--8
SELECT NUMVOL, VILLE_DEP FROM VOL WHERE NUMPIL = 666 OR NUMPIL = 24;

--9
SELECT NUMPIL FROM PILOTE WHERE NUMPIL NOT IN (SELECT NUMPIL FROM VOL);

--10
SELECT NUMVOL, VILLE_DEP, VILLE_ARR FROM VOL, AVION WHERE LOC!='MONTPELLIER';

--11
SELECT DISTINCT P.NUMPIL, P.NOMPIL
FROM AVION AS A, VOL AS V, PILOTE AS P
WHERE P.NUMPIL=V.NUMPIL 
  AND V.NUMAV=A.NUMAV 
  AND V.VILLE_DEP='MONTPELLIER'
  AND A.CAP > 200;

--12
SELECT V.NUMVOL
FROM VOL AS V, PILOTE AS P
WHERE P.NUMPIL=V.NUMPIL
  AND P.ADR='MONTPELLIER'
  AND V.VILLE_DEP='MONTPELLIER';

--13
SELECT V.NUMVOL
FROM VOL AS V, PILOTE AS P, AVION AS A
WHERE V.NUMVOL=V.NUMVOL
  AND V.NUMAV=A.NUMAV
  AND V.NUMPIL=P.NUMPIL
  AND A.LOC='PARIS'
  AND P.ADR='MONTPELLIER'
  AND (V.VILLE_DEP='MONTPELLIER' OR V.VILLE_ARR='MONTPELLIER');

--14
SELECT P.NOMPIL
FROM VOL AS V, PILOTE AS P, AVION AS A
WHERE V.NUMVOL=V.NUMVOL
  AND V.NUMAV=A.NUMAV
  AND V.NUMPIL=P.NUMPIL
  AND A.NOMAV='AIRBUS'
  AND P.ADR='PARIS'
  AND V.VILLE_DEP='MONTPELLIER';

--15
SELECT NUMPIL, NOMPIL
FROM PILOTE
WHERE ADR IN (SELECT ADR FROM PILOTE WHERE NOMPIL='VIDAL');

--16
SELECT DISTINCT A1.NUMAV
FROM AVION AS A1, AVION AS A2
WHERE A1.LOC=A2.LOC AND A2.NUMAV=666;

--17
SELECT DISTINCT NUMPIL
FROM PILOTE
WHERE NUMPIL IN (SELECT NUMPIL
                 FROM VOL
                 WHERE NOMPIL<>'O’CONNORS');

--18
SELECT DISTINCT VILLE_ARR
FROM VOL
WHERE VILLE_DEP IN (SELECT VILLE_ARR AS VILLE_DEP
                   FROM VOL 
                   WHERE VILLE_DEP='PARIS');

--19
SELECT P.NUMPIL, P.NOMPIL
FROM PILOTE AS P, VOL AS V
WHERE P.NUMPIL=V.NUMVOL AND P.ADR=V.VILLE_DEP;

--20
SELECT P2.NUMPIL, P2.NOMPIL
FROM PILOTE AS P1, PILOTE AS P2
WHERE P1.ADR=P2.ADR
  AND P1.NOMPIL='VIDAL'
  AND P1.SAL <= P2.SAL;

--21
SELECT P1.NUMPIL, P1.NOMPIL
FROM PILOTE AS P1, PILOTE AS P2
WHERE P1.NOMPIL=P2.NOMPIL
  AND P1.NUMPIL!=P2.NUMPIL;