Université de Kairouan A.
U : 2019-2020
Institut Supérieur d’Informatique Filière : 1SI
et de Gestion de Kairouan Module : Environnement de
développement de BD
Enseignant responsable : SAGAR Samya
CORRECTION : TP n°1 – LANGAGE SQL
1. CREATION D’UNE BASE DE DONNEES
-- Création de la base
create table pilote (
plnum number(2),
plnom varchar(30),
plprenom varchar(30),
ville varchar(30),
salaire number(8,2),
constraint pilote_cle_pri primary key(plnum));
insert into pilote values(1, 'Zighed', 'Djamel', 'Paris', 21000);
insert into pilote values(2, 'Boussaid', 'Omar', 'Toulouse', 21000);
insert into pilote values(3, 'Viallaneix', 'Jacques', 'Nice', 18000);
insert into pilote values(4, 'Nicolloyannis', 'Nicolas', 'Paris', 17000);
insert into pilote values(5, 'Darmont', 'Jerome', 'Toulouse', 19000);
insert into pilote values(6, 'Lallich', 'Stephane', 'Paris', 18000);
insert into pilote values(7, 'Rakotomalala', 'Ricco', 'Nice', 17000);
insert into pilote values(8, 'Chauchat', 'Jean-Hughes', 'Lyon', 15000);
insert into pilote values(9, 'Jalam', 'Radwan', 'Nice', 18000);
insert into pilote values(10, 'Muhlenbach', 'Fabrice', 'Paris', 20000);
create table avion (
avnum number(2),
avnom varchar(30),
capacite number(3),
localisation varchar(30),
constraint avion_cle_pri primary key(avnum));
insert into avion values(1, 'A300', 300, 'Nice');
insert into avion values(2, 'A310', 300, 'Nice');
insert into avion values(3, 'B707', 250, 'Paris');
insert into avion values(4, 'A300', 280, 'Lyon');
insert into avion values(5, 'Concorde', 160, 'Nice');
insert into avion values(6, 'B747', 460, 'Paris');
insert into avion values(7, 'B707', 250, 'Paris');
insert into avion values(8, 'A310', 300, 'Toulouse');
insert into avion values(9, 'Mercure', 180, 'Lyon');
insert into avion values(10, 'Concorde', 160, 'Paris');
De même pour la table VOL, avec les contraintes
constraint vol_cle_pri primary key(volnum);
onstraint vol_cle_etr_pilote foreign key(plnum) references pilote(plnum);
constraint vol_cle_etr_avion foreign key(avnum) references avion(avnum);
1
2. MISE A JOUR DE LA BASE DE DONNEES
insert into vol values(17, 5, 8, 'Bordeaux', 'Clermont-Fd', 12, 13);
insert into vol values(18, 12, 7, 'Paris', 'Lille', 11, 12); -- Ne marche
pas, contrainte clé étrangère
update vol set villedep='Lille', heurearr=17 where volnum=14;
delete from vol where volnum=17;
3. INTERROGATION DE LA BASE DE DONNEES
1. select plnom from pilote order by nom;
2. select plnom from pilote order by sal desc, nom;
3. select ville, avg(salaire) from pilote group by ville;
4. select distinct avnom from avion order by avnom;
5. select count(*) from avion where localisation = ‘Lyon’;
6. select count(distinct avnum) from avion where localisation = ‘Lyon’;
7. select max(avg(salaire)) from pilote group by ville;
8. select ville from pilote group by ville having avg(salaire) = (select
max(avg(salaire)) from pilote group by ville);
9. select * from vol;
10. select plnom, plprenom, salaire from pilote where salaire > 20000;
11. select plnom, plprenom, volnum from pilote p, vol v
where [Link] = [Link];
12. select count(*) from vol;
13. select sum(capacite) from avion;
14. select avg(heurearr - heuredep), stddev(heurearr - heuredep) from
vol;
15. select min(capacite), max(capacite) from avion;
16. select plnum, count(volnum) from vol
group by plnum;
17. select plnom, sum(heurearr - heuredep)
from pilote p, vol v
where [Link] = [Link]
group by plnom;
18. select avnum, avnom from avion
minus
2
select [Link], avnom from avion a, vol v where [Link] = [Link];
19. select plnom, plprenom from pilote p
where not exists (
select * from avion a
where not exists (
select * from vol v
where [Link] = [Link]
and [Link] = [Link] ) );
