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

create table if not exists pilote(
       pilote_id int not null,
       pilote_name varchar(12),
       pilote_birthdate	date,
       pilote_address varchar(20),
       pilote_telephone	varchar(12),       
       pilote_salary float,
       primary key (pilote_id)
);

create table if not exists avion(
       avion_id int not null,
       avion_type varchar(10),
       avion_cap int,
       avion_loc varchar(20),
       avion_status varchar(20), 
       primary key (avion_id)
);

create table if not exists vol(
       vol_id int not null,
       vol_pilote_id int not null,
       vol_avion_id int not null,
       vol_villeDepart varchar(20),
       vol_villeArrive varchar(20),
       vol_heureDepart int,
       vol_heureArrive int,
       vol_date date,
       primary key (vol_id),
       foreign key (vol_pilote_id) references pilote (pilote_id),
       foreign key (vol_avion_id) references avion (avion_id)
);

insert into  pilote values(1, 'Miranda', '1952-10-16','Sophia-Antipolis', '93548254', 18009.0);
insert into  pilote values(2, 'St-exupery', '1932-10-16', 'Lyon', '91548254', 12300.0);
insert into  pilote values(3, 'Armstrong ', '1930-10-11', 'Wapakoneta','96548254', 24500.0);
insert into  pilote values(4, 'Tintin', '1929-10-01', 'Bruxelles','93548254', 21100.0);
insert into  pilote values(5, 'Gagarine', '1934-10-12', 'Klouchino','93548454', 22100.0);
insert into  pilote values(6, 'Baudry', '1959-10-31', 'Toulouse','93548444', 21000.0);
insert into  pilote values(8, 'Bush', '1924-10-28', 'Milton','44556254', 22000.0);
insert into  pilote values(9, 'Ruskoi', '1930-10-16', 'Moscou','73548254', 22000.0);
insert into  pilote values(10, 'Math', '1938-10-12', 'Paris', '23548254', 15000.0);
insert into  pilote values(11, 'Yen', '1942-10-19', 'Munich','13548254', 29000.0);
insert into  pilote values(12, 'Icare', '1962-10-17', 'Ithaques','73548211', 17000.6);
insert into  pilote values(13, 'Mopolo', '1955-10-04', 'Nice','93958211', 17000.6);
insert into  pilote values(14, 'Chretien', '1945-10-04', '','73223322', 15000.6);
insert into  pilote values(15, 'Vernes', '1935-10-04', 'Paris', '',17000.6);
insert into  pilote values(16, 'Tournesol', '1929-10-04', 'Bruxelles','', 15000.6);
insert into  pilote values(17, 'Concorde', '1966-10-04', 'Paris', '',21000.6);
insert into  pilote values(18, 'Foudil', '1966-10-04', 'Paris', '',21000.6);
insert into  pilote values(19, 'Foudelle', '1966-10-04', 'Paris', '',21000.6);
insert into  pilote values(20, 'Zembla', '1966-10-04', 'Paris', '',21000.6);

insert into  avion values(1, 'A300', 300, 'Nice', 'En service');   
insert into  avion values(2, 'A300', 300, 'Nice', 'En service');   
insert into  avion values(3, 'A320', 320, 'Paris', 'En service');  
insert into  avion values(4, 'A300', 300, 'Paris', 'En service');  
insert into  avion values(5, 'CONCORDE', 300, 'Nice', 'En service');
insert into  avion values(6, 'B707', 400, 'Paris', 'En panne');    
insert into  avion values(7, 'CARAVELLE', 300, 'Paris', 'En service');
insert into  avion values(8, 'B727', 250, 'Toulouse', 'En service');
insert into  avion values(9, 'CONCORDE', 350, 'Toulouse', 'En service');   
insert into  avion values(10, 'A300', 400, 'Paris', 'En service');
insert into  avion values(11, 'A300', 400, 'Paris', 'En service');
insert into  avion values(12, 'A300', 400, 'Paris', 'En service');

insert into  vol values(100, 1,1,'Nice', 'Paris', '1345', '1500','1989-10-3' );  
insert into  vol values(110, 3,6,'Nice', 'Toulouse', '1230', '1325','1989-10-6' );       
insert into  vol values(120,4,3,'Nice', 'Paris', '0745', '0900','1989-10-21' );    
insert into  vol values(125, 12,6,'Paris', 'Nice', '1330', '1845','1989-10-10' );        
insert into  vol values(130, 4,8,'Toulouse','Beauvais', '0630','0750', '1989-10-27' );     
insert into  vol values(111, 5,3,'Nice', 'Paris', '0800', '0920','89-10-4' );    
insert into  vol values(135, 8,5,'Paris', 'Toulouse', '1200','1320','1989-10-22' );     
insert into  vol values(140, 14,9,'Lyon', 'Nice', '0700', '0750','1989-10-4' );     
insert into  vol values(150, 1,1,'Paris', 'Nantes','1630', '1725','1989-10-28' );        
insert into  vol values(153, 2,3,'Lyon', 'Nice', '1210', '1300','1989-10-6' );   
insert into  vol values(156, 9,2,'Paris', 'Lyon', '0230', '0320','1989-10-14' );   
insert into  vol values(200, 5,3,'Nice', 'Toulouse', '2030', '2125','1989-10-17' );      
insert into  vol values(210, 14,7,'Nice', 'Nantes', '1430', '1525','1989-10-14' );        
insert into  vol values(236, 8,4,'Lyon', 'Toulouse', '2130', '2250','1989-10-15' );     
insert into  vol values(240, 13,10, 'Nice', 'Paris', '2300', '2355','1989-10-19' );       
insert into  vol values(250, 13,4,'Bordeaux', 'Paris', '2300','2355', '89-10-25' );   
insert into  vol values(260, 13,5,'Bordeaux', 'Paris', '2300','2355', '1989-10-30' );    
insert into  vol values(270, 13,9,'Paris', 'New york', '1400','2300', '1989-10-3' );    
insert into  vol values(280, 8,9,'Nice', 'Mulhouse', '1200','1320','1989-10-21' );     
insert into  vol values(290, 3,8,'Beauvais', 'Marseille', '1230','1425', '1989-10-9' );       
insert into  vol values(310, 19,8,'Beauvais', 'Marseille', '1230','1425', '1989-10-9' );       

select * from pilote;
select * from vol where vol_villeDepart = "Nice";
select distinct avion_type from  avion;
select * from pilote where pilote_salary > 20000;
select pilote_name from pilote, vol where vol_pilote_id = pilote_id and vol_id = 100;
select max(pilote_salary) from pilote;
select count(*) from vol where vol_date like "____-10-__";
select pilote_name from vol, pilote where vol_pilote_id = pilote_id group by pilote_id having count(*) > 1;
select distinct avion_type from avion, vol where avion_id = vol_avion_id and vol_villeDepart != "Paris" and vol_villeDepart != "Toulouse";
select pilote_name, pilote_salary from pilote where pilote_salary > 10000 and pilote_salary < 20000;