Devoir SQL

Durée: 55 minutes

Dans ce devoir, nous utiliserons la base de données constituée des 5 tables suivantes :

Relation Immeuble

idImmeuble	nbEtages	dateConstruction
Z1	9	2017
Z2	7	2015
A1	5	2012

Relation Propriétaire

idAppart	idPersonne	quotePart
801	001	0.22
704	002	0.12
101	007	0.1
203	009	0.5
409	005	0.18

Relation Personne

idPersonne	prénom	nom
001	Catherine	Sauvage
002	Hamid	Belkacem
003	Stéphane	Magry
004	Fatoumata	Diawara
005	Frédéric	Peloux
006	Ben	Oït
007	Touraya	Housni
008	Lucia	Guerrero
009	Cheb	Mami
010	Henri	Dumont

Relation Appart

idAppart	surface	idImmeuble
801	78	Z1
704	53	Z2
101	78	A1
203	78	A1
409	70	Z1

Relation Occupant

idPersonne	idAppart	Datedebut
001	801	01/01/2018
008	704	12/01/2020
004	203	12/04/2014
010	409	01/02/2018
003	101	31/03/2015

\newpage

QUESTIONS

1. Donner le schéma relationnel qui correspond aux relations précédentes.

   Corrigé

   Immeuble(idImmeuble,nbEtages,dateConstruction)

   Propriétaire(#idAppart,#idPersonne,quotePart)

   Personne(idPersonne, prénom, nom)

   Appart(idAppart, surface, #idImmeuble)

   Occupant(#idPersonne,#idAppart, Datedebut)

2. Que retourne la requête suivante ?

   SELECT AVG(nbEtages)
   FROM Immeuble;
   

   Corrigé

   Cette requête retourne la moyenne des étages des immeubles de la table Immeuble. Donc ici \(\dfrac{9+7+5}{3}=7\)

3. Que retourne la requête suivante ?

   SELECT nom
   FROM Propriétaire
   JOIN Personne ON Propriétaire.idPersonne=Personne.idPersonne
   WHERE Personne.prénom="C%";
   

   Corrigé

   Cette requête retourne les noms des propriétaires dont le prénom commence par un C. C'est à dire

   nom
   Sauvage
   Mami

4. Requêtes Donner une requête en SQL qui retourne:

   1. La liste des occupants (Noms et Prénoms) des appartements de l'immeuble Z1.

      Corrigé

      SELECT nom,prénom
      FROM Occupant
      JOIN Personne ON Occupant.idPersonne=Personne.idPersonne
      JOIN Appart ON Appart.idAppart=Occupant.idAppart
      WHERE Appart.idImmeuble="Z1";
      

   2. Le nombre d'appartements de plus de \(70 m^2\).

      Corrigé

      SELECT count(*)
      FROM Appart
      WHERE Appart.surface>=70;
      

   3. La surface moyenne des appartements occupés par des personnes dont le prénom se termine par un 'a'.

      Corrigé

      SELECT AVG(surface)
      FROM Appart
      JOIN Occupant ON Appart.idAppart=Occupant.idAppart
      JOIN Personne ON Occupant.idPersonne=Personne.idPersonne
      WHERE Personne.prénom="%a";
      

   4. Le nombre d'appartement par Immeuble, en donnant l'id de l'immeuble ainsi qu'un champs "nombre d'appartements" qui contient la valeur.

      Corrigé

      SELECT idImmeuble,count(*) AS "Nombre d'appartements"
      FROM Appart
      GROUP BY idImmeuble;
      

   5. La liste des propriétaires (nom,prénom) qui occupent leurs appartements.

      Corrigé

      SELECT nom,prénom
      FROM Personne
      JOIN Propriétaire ON Propriétaire.idPersonne=Personne.idPersonne
      JOIN Occupant ON Occupant.idAppart=Propriétaire.idAppart;
      

5. Suppression

   Que faut-il faire pour supprimer de la base de données, sans erreurs, toute mention à Cheb Mami et à ses biens ?

   Corrigé

   On commence par supprimer la ligne qui correspond à son appartement dans la relation Propriétaire (idAppart = 203).

   DELETE FROM Propriétaire
   WHERE idAppart=203;
   

   Puis on supprime son appartement dans Occupant.

   DELETE FROM Occupant
   WHERE idAppart=203;
   

   Puis on supprime Cheb Mami dans Personne.

   DELETE FROM Personne
   WHERE prénom="Cheb" AND nom="Mami";
   

   Et enfin on supprime l'appartement 203 dans Appart.

   DELETE FROM Appart
   WHERE idAppart=203;
   

6. Mise à jour

   David Benamou emménage aujourd'hui dans l'appartement 409. Quelles reqûetes doit-on écrire pour que, sans erreurs, la base de données soit mise à jour ?

   Corrigé

   On ajoute David Benamou dans la table Personne (idPersonne=11).

   INSERT INTO Personne 
   VALUES 
   (11, David, Benamou);
   

   On supprime la ligne qui correspond à l'appartement 409 dans la relation Occupant.

   DELETE FROM Occupant
   WHERE idAppart=409;
   

   On ajoute une ligne dans Occupant qui indique que David Benamou est le nouvel occupant de l'appartement 409 à partir d'aujourd'hui.

   INSERT INTO Occupant 
   VALUES 
   (11, 409, 17/10/2023);