TD 4 --- SQL ---
Exprimer les requêtes suivantes en SQL sur la base « Immeuble » de l’exo 3 du TD 3.
Pour chaque requête, donnez le résultat sur la base “Immeubles”
Division
19. Quels sont les immeubles où tout le monde a emménagé en 1994 ?
Π nomIm, Annee-Arv (Occupant) ≑ Π Annee-Arv (σ Annee-Arv = 1994 (Occupant))
SELECT DISTINCT nomIm FROM Occupant WHERE nomIm
NOT IN (SELECT nomIm FROM Occupant WHERE Anneearv <> 1994 );
20. Quels sont les immeubles où tous leurs appartements ont une superficie supérieure à 200 ?
SELECT DISTINCT nomIm FROM Appart WHERE nomIm
NOT IN ( select nomIm FROM Appart WHERE Superficie < 200 );
Jointure Externe
21. Donner les appartements de l’immeuble 1 et leurs habitants (extern gauche)
SELECT Appart.noApp, Occupant.nomC
FROM Appart LEFT JOIN Occupant ON (Appart.noApp = Occupant.noAppart)
AND (Appart.nomIm = Occupant.nomIm)WHERE (((Appart.nomIm)="Imb1"));
22. Donner les immeubles et les occupants en précisant pour chacun immeuble son gérant
(extern droit)
SELECT Immeuble.nomIm, Occupant.nomC
FROM Immeuble RIGHT JOIN Occupant ON (Immeuble.nomGérant = Occupant.nomC)
AND (Immeuble.nomIm = Occupant.nomIm);
Semi Jointure
23. Donner la liste des occupants et leur année d’arrivé qui ont moins de 50 ans.
Π nomC, Annee-Arv (Occupant) ⋉ (σ age < 50 (Personne))
SELECT O.nomC, O.Anneearv FROM Occupant O WHERE O.nomC IN
(SELECT P.nom FROM Personne P WHERE age<50);
Any
24. Donner les appartements de l’immeuble 2 qui ont une superficie supérieure aux appartements de
l’immeuble 1
SELECT noApp FROM Appart WHERE nomIm="Imb2" AND Superficie > ANY
(SELECT Superficie FROM Appart WHERE nomIm="Imb1");
All
25. Donner les appartements de l’immeuble 2 qui ont une superficie supérieure à tous les
appartements de l’immeuble 1
SELECT noApp FROM Appart WHERE nomIm="Imb2" AND Superficie > ALL
(SELECT Superficie FROM Appart WHERE nomIm="Imb1");
Agrégation
26. Donner pour chaque Immeuble la moyenne de superficie de ses appartements
SELECT nomIm, AVG (Superficie) FROM Appart GROUP BY nomIm
27. Donner la moyenne d’âge des occupants pour chaque immeuble (Jointure+ agregat)
SELECT nomIm, AVG(age) FROM Occupant, Personne
WHERE nomC= nom GROUP BY nomIm
28. Donner les immeubles qui ont plus que 2 habitants
SELECT nomIm, COUNT (nomC) FROM Occupant GROUP BY nomIm
HAVING COUNT (nomC)>2;
29. Donner les occupants et leur appartements ordonné par la superfinie des appartements
SELECT O.nomC, O.noAppart, A.Superficie FROM Occupant O, Appart A
WHERE O.nomIm=A.nomIm AND O.noAppart=A.noApp
ORDER BY A.superficie DESC, O.nomC DESC;
Vues
On veut créer un ensemble de vue sur la base « Immeuble » de l’exo 3 du TD 3, qui ne donne des
informations que sur l’immeuble 1.
30. Définir la vue OccupIm1 qui est identique à Occupants sauf qu’elle ne contient que les
occupants de l’immeuble 1.
CREATE VIEW OccupIm1 AS
SELECT * FROM Occupant WHERE nomIm= Imb1
31. Définir la vue PersFc qui contient les noms des personnes et leur fonction qui occupent
l’immeuble 1
CREAT VIEW PersFc (NomP, Fonct) AS SELECT nom, fonction FROM Personne
WHERE nom = (SELECT nomC FROM Occupant WHERE nomIm = “Imb1”)
