CNED - exploitation des données Devoir 1

Requête 1 (0,5 point) : Donnez la liste des biens (adresse, nombre de pièces, superficie et prix) en commençant par le plus cher.

SELECT rue, cp, ville, nbPieces, superficie, prix

FROM bien

ORDER BY prix DESC

Requête 2 (1,5 point) : Combien de biens sont à vendre sur le secteur «Toulon Ouest» ?

SELECT COUNT(*) AS "Nombre de biens a vendre sur le secteur Toulon Ouest"

FROM bien JOIN secteur USING (idSecteur)

WHERE libelleSecteur = ‘Toulon Ouest’ ;

• Requête 3 (2 points) : Quel est le prix de la maison la plus chère ?

SELECT MAX(prix) AS "La maison la plus chère"

FROM bien JOIN type USING (idType)

WHERE libelleType = ‘Maison’ ;

• Requête 4 (2 points) : Donnez la liste des biens (adresse, nombre de pièces, superficie et prix) proches à la fois d’un lycée et d’un arrêt de bus.

SELECT rue, cp, ville, nbPieces, superficie, prix

FROM bien

JOIN concerner USING (idBien)

JOIN commodite USING (idCommodite)

JOIN ( SELECT idBien FROM concerner

JOIN commodite USING (idCommodite)

WHERE libelleCommodite = ‘Arrêt de bus’ ) AS sReq USING (idBien)

WHERE libelleCommodite = ‘Lycée’ ;

• Requête 5 (3 points) : Donnez la liste des secteurs ayant moins de 5 biens actuellement en vente (le secteur ayant le plus de bien sera affiché en premier).

SELECT libelleSecteur, COUNT(*) AS "Nombre de biens"

FROM bien JOIN secteur USING (idSecteur)

GROUP BY libelleSecteur HAVING "Nombre de biens" < 5

ORDER BY "Nombre de biens" DESC ;

• Requête 6 (1 point) : Donnez la liste des propriétaires vendant leur résidence principale (c’est-à-dire dont l’adresse du bien vendu est la même que celle du propriétaire).

SELECT prenom, nom, pr.cp, pr.rue, pr.ville, tel, email

FROM bien AS b

JOIN proprietaire AS pr USING (idProprietaire)

WHERE b.rue = pr.rue AND b.cp = pr.cp AND b.ville = pr.ville ;

Requête 7 (1 point) : Ajoutez la nouvelle commodité «Borne de recharge véhicule électrique» (id : BRVE) à notre base de données.

INSERT INTO commodite (libelleCommodite, idCommodite)

VALUES (‘Borne de recharge véhicule électrique’, ‘BRVE’) ;

• Requête 8 (1 point) : La liste des commodités suivantes est à ajouter au logement 8367 (qui n’avait pas encore de commodités déclarées) :

[pic 1]

INSERT INTO concerner (numero, idCommodite, distance, idBien)

SELECT num, commo, distance, 8367

FROM lstCommods8367 ;

• Requête 9 (3 points) : Une borne de recharge de véhicule électrique vient d’être posée à 20 m du logement 8367. Prenez en compte cette information pour mettre à jour la base de données.

UPDATE concerner

SET numero = numero + 1

WHERE idBien = 8367 ;

INSERT INTO concerner (idBien, numero, idCommodite, distance)

VALUES (8367,1, ‘BRVE’, 20) ;

• Requête 10 (2 points) : Le propriétaire du bien 8367 nous prévient que son email a changé, c’est maintenant «a.dupont@orange.fr».

...