TP 1 Base de données : Langage SQL
Enseignant : Mehdi Achour
Année universitaire : 2023/2024
Filière : 2émé SI Groupe 01
Etudiante : Ele Akacha
Compte Rendu :
-- Création des tables
CREATE TABLE Avion (
NA INT PRIMARY KEY,
Nom VARCHAR(12),
Capacité INT,
Localité VARCHAR(10)
);
CREATE TABLE Pilote (
NP INT PRIMARY KEY,
Nom VARCHAR(25),
Adresse VARCHAR(40)
);
CREATE TABLE Vol (
NV INT PRIMARY KEY,
NP INT,
NA INT,
VD VARCHAR(10),
VA VARCHAR(10),
HD INT,
HA INT,
FOREIGN KEY (NP) REFERENCES Pilote(NP),
FOREIGN KEY (NA) REFERENCES Avion(NA)
);
-- Insertion des données
INSERT INTO Avion VALUES (100, 'AIRBUS', 300, 'RABAT');
INSERT INTO Avion VALUES (101, 'B737', 250, 'CASA');
INSERT INTO Avion VALUES (102, 'B737', 220, 'RABAT');
INSERT INTO Pilote VALUES (1, 'Pilote1', 'Adresse1');
INSERT INTO Pilote VALUES (2, 'Pilote2', 'Adresse2');
INSERT INTO Pilote VALUES (3, 'Pilote3', 'Adresse3');
INSERT INTO Vol VALUES (1, 1, 100, 'RABAT', 'CASA', 1200, 1400);
INSERT INTO Vol VALUES (2, 2, 101, 'CASA', 'RABAT', 1500, 1700);
INSERT INTO Vol VALUES (3, 3, 102, 'RABAT', 'CASA', 1100, 1300);
INSERT INTO Vol VALUES (4, 1, 101, 'CASA', 'RABAT', 1800, 2000);
INSERT INTO Vol VALUES (5, 2, 100, 'RABAT', 'CASA', 1400, 1600);
-- Requêtes demandées
-- 2) Afficher tous les avions
SELECT * FROM Avion;
-- 3) Afficher tous les avions par ordre croissant sur le nom
SELECT * FROM Avion ORDER BY Nom;
-- 4) Afficher les noms et les capacités des avions
SELECT Nom, Capacité FROM Avion;
-- 5) Afficher les localités des avions sans redondance
SELECT DISTINCT Localité FROM Avion;
-- 6) Afficher les avions dans la localité est Rabat ou Casa
SELECT * FROM Avion WHERE Localité IN ('RABAT', 'CASA');
-- 7) Modifier la capacité de l’avion numéro 101, la nouvelle capacité et 220
UPDATE Avion SET Capacité = 220 WHERE NA = 101;
-- 8) Supprimer les avions dans la capacité et inférieure à 200
DELETE FROM Avion WHERE Capacité < 200;
-- 9) Afficher la capacité maximale, minimale, moyenne des avions
SELECT MAX(Capacité) AS Capacité_Max, MIN(Capacité) AS Capacité_Min, AVG(Capacité)
AS Capacité_Moyenne FROM Avion;
-- 10) Afficher les données des avions dont la capacité est la plus basse
SELECT * FROM Avion WHERE Capacité = (SELECT MIN(Capacité) FROM Avion);
-- 11) Afficher les données des avions dont la capacité est supérieure à la capacité moyenne
SELECT * FROM Avion WHERE Capacité > (SELECT AVG(Capacité) FROM Avion);
-- 12) Afficher le nom et l’adresse des pilotes assurant les vols IT100 et IT104 (jointure entre
table pilote et vol)
SELECT [Link], [Link] FROM Pilote INNER JOIN Vol ON [Link] = [Link]
WHERE [Link] IN (1, 4);
-- 13) Afficher les numéros des pilotes qui sont en service
SELECT DISTINCT NP FROM Vol;
-- 14) Afficher les numéros des pilotes qui ne sont pas en service
SELECT NP FROM Pilote WHERE NP NOT IN (SELECT DISTINCT NP FROM Vol);
-- 15) Afficher les noms des pilotes qui conduisent un AIRBUS
Method 1 :
SELECT DISTINCT [Link] FROM Pilote INNER JOIN Vol ON [Link] = [Link] INNER
JOIN Avion ON [Link] = [Link] WHERE [Link] = 'AIRBUS';
Method 2:
SELECT DISTINCT Nom
FROM Pilote
WHERE NP IN (
SELECT DISTINCT [Link]
FROM Vol, Avion
WHERE [Link] = [Link] AND [Link] = 'AIRBUS'
);
