TP 1 : SGBD
1) SELECT VilleD FROM VOLS;
2) SELECT NumVol FROM VOLS WHERE (NumAv = 240 )AND (VilleD IN
('Marseille', 'Paris'));
3)SELECT NumPil
FROM PILOTES
WHERE VillePil NOT IN ('Marseille', 'Nice');
4)SELECT DISTINCT NumPil
FROM VOLS
WHERE VilleD NOT IN ('Marseille', 'Paris');
5)SELECT DISTINCT NumPil
FROM VOLS
WHERE NOT (VilleD = 'Paris' AND VilleA = 'Marseille');
6)SELECT NumVol
FROM VOLS
WHERE NumAv NOT IN (101, 401) OR NumAv IS NULL;
7)SELECT NomAv, CapAv
FROM AVIONS
ORDER BY NomAv ASC, CapAv DESC;
8)SELECT NumAv, NomAv
FROM AVIONS
WHERE NomAv NOT LIKE '%a%' OR NomAv NOT LIKE '%A%';
OU BIEN
SELECT NumAv, NomAv
FROM AVIONS
WHERE LOWER(NomAv) NOT LIKE '%a%';
OU BIEN
SELECT NumAv, NomAv
FROM AVIONS
WHERE UPPER(NomAv) NOT LIKE '%A%';
9)SELECT NomPil
FROM PILOTES
WHERE NomPil LIKE '_a%';
10) SELECT NumVol
FROM VOLS
WHERE TIMESTAMPDIFF(HOUR, DateD, DateA) BETWEEN 2 AND 4;
11) SELECT DISTINCT NumPil
FROM VOLS
WHERE VilleD = 'Marseille'
AND TIMESTAMPDIFF(HOUR, DateD, DateA) > 1;
12)SELECT NumVol
FROM VOLS
ORDER BY TIMESTAMPDIFF(HOUR, DateD, DateA) DESC
LIMIT 3;
13)SELECT V.NumVol
FROM VOLS V, VOLS V101
WHERE V101.NumVol = 'V101'
AND V.VilleD = V101.VilleA
AND V.VilleA = V101.VilleD
AND DATE(V.DateD) = DATE(V101.DateD);
14)SELECT DISTINCT C.NumCl, C.NomCl
FROM CLIENTS C
JOIN RESERVATIONS R ON C.NumCl = R.NumCl
JOIN VOLS V ON R.NumVol = V.NumVol
JOIN PILOTES P ON V.NumPil = P.NumPil
WHERE P.NomPil = 'Lorentz';
15)SELECT NumCl
FROM RESERVATIONS
GROUP BY NumCl
HAVING COUNT(*) > 3;
16)SELECT NumPil
FROM VOLS
GROUP BY NumPil
ORDER BY COUNT(*) DESC
LIMIT 1;
17)SELECT NumCl
FROM RESERVATIONS
GROUP BY NumVol, NumCl
HAVING MAX(NbPlaces);
18)SELECT
COUNT(*) AS AvCapInconnue,
MIN(CapAv) AS PlusPetiteCap,
FROM AVIONS
WHERE CapAv IS NULL OR CapAv IS NOT NULL;
19)SELECT NumPil, COUNT(DISTINCT NumAv) AS NbAvions
FROM VOLS
GROUP BY NumPil;
20)SELECT V.NumVol, A.NomAv
FROM VOLS V
JOIN AVIONS A ON V.NumAv = A.NumAv
WHERE V.VilleA = 'Marseille';
21)SELECT NumPil, NVL(VillePil,"ville inconnue") as Ville FROM Pilotes;
22)SELECT SUM(NbPlaces) AS NbPlacesTotal FROM RESERVATIONS WHERE
NumVol = 'V101';
23)SELECT NumVol, VilleD, DateD, TIME(DateD) AS HeureDepart
FROM VOLS
WHERE VilleA = 'Marseille'
ORDER BY DATE(DateD) ASC, TIME(DateD) DESC;
24)SELECT NomPil,
YEAR(CURDATE()) - YEAR(NaisPil) AS Age
FROM PILOTES
WHERE VillePil = 'Nice'
AND YEAR(CURDATE()) - YEAR(NaisPil) > 35;
25)SELECT SUBSTRING_INDEX(NomAv, ' ', 1) AS NomAvion
FROM AVIONS;
26)SELECT DATE(DateD) AS Jour, COUNT(*) AS NbVols
FROM VOLS
WHERE VilleD = 'Paris'
AND MONTH(DateD) = 4
GROUP BY DATE(DateD);
27)SELECT (COUNT(*) * 100.0 /
(SELECT COUNT(*) FROM VOLS WHERE MONTH(DateD) = 4)
) AS PourcentageVolsParis
FROM VOLS
WHERE VilleD = 'Paris'
AND MONTH(DateD) = 4;
28)
