TD 3 :
Traitement des données – Le
langage de requêtes SQL
La base de données : Vols
Soit la base de données relationnelle des vols
quotidiens d’une compagnie aérienne qui contient les tables:
Avion (NA, Nom, Capacite, Localite)
Pilote (NP, Nom, Adresse)
Vol (NV, #NP, #NA, VD, VA, HD, HA )
1) Créer la base de données Vols
2) Préciser l’ordre de création des tables
3) Créer les tables de la base de données vol en respectant les contraintes suivantes.
- Le nom de l’avion et du pilote sont obligatoires
- La capacité de l’avion est par défaut 200
- L’heure de départ du vol est 12h, 15h, 18h ou 22h.
Création de la base de données
1) Create database Vols ;
2) Ordre : Avion ou Pilote , puis vol
3)
CREATE TABLE Avion
(NA INTEGER ,
CREATE TABLE Vol
Nom VARCHAR(12) NOT NULL,
(NV VARCHAR(6),
capacite INTEGER(6) DEFAULT 200,
NP INTEGER,
localite VARCHAR(10) ,
NA INTEGER,
Constraint Ac_C Default capacite 200,
VD varchar (10) ,
Constraint pk_v PRIMARY KEY(NA) VA varchar (10),
); HD INTEGER check in (12,15,18,22),
HA INTEGER,
CREATE TABLE Pilote
CONSTRAINT fk_vol_P FOREIGN Key (NP) REFERENCES Pilote (NP) ON
(NP INTEGER, DELETE CASCADE,
Nom varchar (25) NOT NULL, CONSTRAINT fk_vol_A REFERENCES Avion (NA),
Adresse varchar (40), Constraint pk_v PRIMARY KEY(NV));
Constraint pk_p PRIMARY KEY(NP));
La base de données : Vols – Manipulation des données
Soit la base de données relationnelle des vols quotidiens d’une compagnie aérienne qui contient les tables Avion, Pilote et Vol.
Avion (NA, Nom, Capacite, Localite)
Pilote (NP, Nom, Adresse)
Vol (NV, #NP, #NA, VD, VA, HD, HA )
1) Insérer les avions suivants dans la table Avion : (100, AIRBUS, 300, RABAT), (101,B737,250,CASA), (101, B737,220,RABAT).
y’a-t-il un problème suite à cette requête ?
2) Insérer le pilote (12, Ben Hamed, RABAT)
3) Insérer le vol (1, 12, 101, RABAT, CASA, 12, 13)
4) Supprimer l’avion numéro 101. y’a-t-il un problème suite à cette requête ?
5) Modifier la capacité de l’avion numéro 100 à 320
6) Modifier la table pilote ajouter une colonne prénom
7) Modifier le pilote d’id 12, remplir son prénom Ali
La base de données : Vols – Manipulation des données
1) Insert into Avion values (100, 'AIRBUS', 300, 'RABAT');
Insert into Avion values (101,'B737',250,'CASA');
Insert into Avion values (101, 'B737',220,'RABAT’);
La dernière ligne ne sera pas insérée car il y a une duplication de clé primaire.
2) Insert into Pilote values (12, « Ben Hamed », « RABAT »);
3) Insert into Vol values(« 1 », 12, 101, « RABAT », « CASA », 12, 13);
4) Delete from Avion where NA=101;
Problème de suppression car la clé est référencée dans vol
Solution: ajouter On delete cascade
alter table vol drop constraint fk_vol;
alter table vol Add constraint fk_vol foreign key (NA) REFERENCES Avion (NA) on delete cascade,
5) Update Avion set capacité= 320 where NA=100 ;
6) Alter table pilote add prenom varchar (20);
7) Update pilote set prenom=‘ali’ where NP=12;
La base de données : Vols – Interrogation de données
Soit la base de données relationnelle des vols quotidiens d’une compagnie aérienne qui contient les tables Avion, Pilote et Vol.
Avion (NA, Nom, Capacite, Localite)
Pilote (NP, Nom, Adresse)
Vol (NV, #NP, #NA, VD, VA, HD, HA )
1) Afficher tous les avions
2) Afficher tous les avions par ordre croissant sur le nom
3) Afficher les noms et les capacités des avions
4) Afficher les localités des avions sans redondance
5) Afficher les avions dans la localité et Rabat ou Casa
6) Modifier la capacité de l’avion numéro 101, la nouvelle capacité est 220
7) Supprimer les avions dans la capacité et inférieure à 200
8) Afficher la capacité maximale, minimale, moyenne des avions
La base de données : Vols – Interrogation de données
1) select * from Avion; .
2) select * from Avion order by Nom asc;
3) select nom, capacite from Avion;
4) select distinct (localite) from Avion;
5) select * from Avion where localite='Rabat' or localite='Casa' ;
select * from Avion where localite IN (‘Rabat’,'Casa’) ;
6) Update Avion set capacite=220 where NA=101;
7) Delete from Avion where capacite <200;
8) Select Max(capacite), Min(capacite), AVG(capacite) from Avion;