Devoir 1 exploitation de données

1 CNED BTS SERVICES INFORMATIQUES AUX ORGANISATIONS – 1re année 8 2943 CT WB 01 15

BTS Services informatiques aux organisations 1re année

EXPLOITATION DES DONNÉES

CORRIGÉ TYPE 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 ;

Évaluation (sur 0,5 point) :

– 0,5 point pour le tri dans le bon ordre

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

SELECT COUNT(*) AS "Nb biens sur Toulon Ouest"

FROM bien JOIN secteur USING (idSecteur)

WHERE libelleSecteur = ‘Toulon Ouest’ ;

Évaluation (sur 1,5 point) :

– 0,5 point pour la fonction COUNT() et son alias ;

– 0,5 point pour la jointure ;

– 0,5 pour la restriction sur le libelleSecteur.

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

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

FROM bien JOIN type USING (idType)

WHERE libelleType = ‘Maison’ ;

Évaluation (sur 2 points) :

– 0,5 point pour la fonction MAX() et son alias ;

– 1 point pour la jointure ;

– 0,5 pour la restriction au bon type.

• 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)

WHERE libelleCommodite = ‘Lycée’

AND idBien IN ( SELECT idBien

FROM concerner JOIN commodite USING (idCommodite)

WHERE libelleCommodite = ‘Arrêt de bus’ ) ;

Si vous avez tenté de combiner simplement les restrictions sur libelleCommodite de la façon suivante, c’est une

grave erreur de compréhension du SQL :

libelleCommodite = ‘Lycée’ AND libelleCommodite = ‘Arrêt de bus’

Il faut vous rappeler que dans chaque ligne retournée, chaque cellule ne peut contenir qu’une seule valeur et

que par conséquent, il apparaît impossible d’avoir 2 valeurs différentes de libelleCommodite sur la même ligne.

Dis autrement, un libelleCommodite ne peut pas contenir à la fois l’information «Lycée» et l’information «Arrêt

de bus».

D’autres solutions existent. Comme ici il fallait «croiser» des résultats, on pouvait tout à fait faire une jointure

supplémentaire avec notre sous-requête plutôt que de l’utiliser dans la restriction :

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’ ;

Évaluation (sur 2 points) :

– 0,5 point par bonne jointure (dans la limite de 1 point) ;

– 1 point pour une restriction globale cohérente (uniquement les biens satisfaisants les 2 critères).

• 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 ;

Évaluation (sur 3 points) :

– 0,5 point pour la fonction COUNT() et son alias ;

– 0,5 point pour la jointure ;

– 0,5 points pour le regroupement

– 1 point pour la condition de regroupement HAVING ;

– 0,5 points pour le bon critère de tri (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).

Une bonne occasion d’utiliser à bon escient la jointure naturelle :

SELECT prenom, nom, proprietaire.rue, proprietaire.cp,

proprietaire.ville, tel, email

FROM bien NATURAL JOIN proprietaire;

Une bonne occasion également d’utiliser les alias de table :

SELECT prenom,

...