Bases de données Relations et requête SELECT Corrigé Exercice 1

Exercice 1
´Ecrire `a chaque question une requˆete permettant de connaˆıtre :
1) les villes de Bretagne, en ordonnant les r´esultats par ordre croissant d"habitants ;
SELECT Nom, Habitants FROM Villes
WHERE Region = "Bretagne"
ORDER BY Habitants
2) le plus grand code postal des villes de Bretagne ;
SELECT MAX('Code postal') FROM Villes
WHERE Region="Bretagne"
Remarque : parfois la fonctionMAXne fonctionne pas, je ne comprends pas pourquoi, quand
je retape la mˆeme requˆete la deuxi`eme (ou troisi`eme) fois ¸camarche.
3) les villes qui ne sont pas une capitale de r´egion ;
SELECT * FROM Villes
WHERE Cle NOT IN (SELECT Capitale FROM Regions)
On pourrait aussi chercher les codes postaux non divisibles par 1000.
4) les villes de Bretagne plus peupl´ees que la moyenne des villes de Bretagne ;
SELECT * FROM Villes
WHERE Region = "Bretagne"
AND Habitants > (SELECT AVG(Habitants) FROM Villes
WHERE Region="Bretagne")
5) les r´egions ayant le plus de villes (dans la base).
SELECT Region FROM Villes
GROUP BY Region
HAVING COUNT(Cle) = (SELECT MAX(NB_Villes) FROM
(SELECT COUNT(Cle) AS NB_Villes FROM Villes
GROUP BY Region) AS VV)
Exercice 2
Jumelages
1)
´Ecrire une requˆete permettant de connaˆıtre les villes jumel´eesavec Rennes.
SELECT Nom FROM Villes JOIN Jumelages On Cle=Ville1
WHERE Ville2 IN (SELECT Cle FROM Villes WHERE Nom="Rennes")
UNION
SELECT Nom FROM Villes JOIN Jumelages On Cle=Ville2
WHERE Ville1 IN (SELECT Cle FROM Villes WHERE Nom="Rennes")
Cl´ement PicardLyc´ee Chateaubriand 1/4
Bases de donn´eesRelations et requˆeteSELECTCorrig´e
2)´Ecrire une requˆete permettant de connaˆıtre les villes jumel´ees`a au moins une ville de Bre-
tagne.
SELECT Nom FROM Villes JOIN Jumelages On Cle=Ville1
WHERE Ville2 IN (SELECT Cle FROM Villes WHERE Region="Bretagne")
UNION
SELECT Nom FROM Villes JOIN Jumelages On Cle=Ville2
WHERE Ville1 IN (SELECT Cle FROM Villes WHERE Region="Bretagne")
3)
´Ecrire une requˆete permettant de connaˆıtre les villes jumel´ees`a toutes les villes de Bretagne.
Une villevv´erifie cette condition si et seulement s"il n"existe pas de ville de Bretagnewqui
ne soit pas jumel´ee avecv.
SELECT Nom FROM Villes AS V
WHERE NOT EXISTS (SELECT * FROM Villes AS W
WHERE Region = "Bretagne"
AND NOT EXISTS (SELECT * FROM Jumelages
WHERE (Ville1 = W.Cle AND Ville2=V.Cle)
OR (Ville2=W.Cle AND Ville1=V.Cle)))
4)
´Ecrire une requˆete qui permet de v´erifier que la condition sur l"unicit´e des jumelages est bien
respect´ee dans la table.
La condition est v´erifi´ee si et seulement si la requˆete ci-dessous renvoie 0.
SELECT COUNT(*) FROM Jumelages AS J1, Jumelages AS J2
WHERE J1.Ville1=J2.Ville2 AND J1.Ville2=J2.Ville1
Exercice 3
Une pizzeria
1) Quel est le prix de la pizza Calzone ?
SELECT Prix FROM Pizzas
WHERE Nom = "Calzone"
2) Quel est le prix moyen des pizzas command´ees par M. Athanase Johnovitch ? (on suppose
qu"un seul client porte ce nom).
SELECT AVG(Pizzas.Prix)
FROM Clients
JOIN Commandes ON Clients.Cle = Commandes.Client
JOIN Pizzas ON Commandes.Pizza = Pizzas.Cle
WHERE Clients.Prenom = "Athanase"
AND Clients.Nom = "Johnovitch"
Cl´ement PicardLyc´ee Chateaubriand 2/4
Bases de donn´eesRelations et requˆeteSELECTCorrig´e
3) Retrouver les coordonn´ees d"un client pr´enomm´e Alfred qui n"a toujours pas pay´e sa com-
mande du mois de novembre.
SELECT Prenom, Nom, Adresse, Ville
FROM Clients
JOIN Commandes ON Clients.Cle = Client
WHERE Prenom = "Alfred"
AND MONTH(Date) = 11
AND Paye = 0
4) Quelle est la pizza qui a le plus de succ`es ?
On trie les pizzas par nombre d´ecroissant de commandes et on ne garde que la premi`ere
ligne grˆace `aLIMIT 0,1.
SELECT Pizzas.Nom, COUNT(Commandes.Cle) AS NbCommandes
FROM Pizzas
JOIN Commandes ON Pizzas.Cle = Commandes.Pizza
GROUP BY Pizzas.Nom
ORDER BY NbCommandes DESC
LIMIT 0, 1
5) Quelle est la recette du mois d"avril (en ne comptant que les commandes effectivement
pay´ees).
SELECT SUM(Prix) FROM Commandes
JOIN Pizzas ON Commandes.Pizza = Pizzas.Cle
WHERE MONTH (Commandes.Date) = 4
AND Commandes.Paye = 1
6) Combien de pizzas chaque client a-t-il command´e en moyenne ? Comment savoir si les
habitants de Vitr´e commandent plus de pizzas que la moyenne ?
Premi`ere solution pour compter le nombre de pizzas que chaque client a command´e en
moyenne :
SELECT AVG( C.NbCommandes )
FROM (SELECT COUNT(*) AS NbCommandes
FROM Commandes
GROUP BY Client) AS C
Deuxi`eme solution : on pourrait compter le nombre de commandes et diviser par le nombre
de clients...
Pour calculer le nombre moyen de pizzas command´ees par les habitants de Vitr´e :
Cl´ement PicardLyc´ee Chateaubriand 3/4
Bases de donn´eesRelations et requˆeteSELECTCorrig´e
SELECT AVG(C.NbCommandes)
FROM (SELECT COUNT(*) AS NbCommandes
FROM Commandes
JOIN Clients ON Client = Clients.Cle
WHERE Ville = "Vitre"
GROUP BY Client) AS C
7) Les pizzas Calzone et Regina sont les seules qui contiennent de la viande. Donner la liste
des clients potentiellement v´eg´etariens.
SELECT Prenom, Nom FROM Clients AS C
WHERE NOT EXISTS (SELECT * FROM Commandes
JOIN Pizzas ON Pizza = Pizzas.Cle
WHERE C.Cle = Client
AND (Pizzas.Nom = "Calzone" OR Pizzas.Nom = "Regina"))
8) Quelles sont les clients qui ont test´e au moins une fois chaque pizza ? Formaliser cela sous
forme d"une division sans ´ecrire la requˆete, puis traduire la requˆete en SQL.
SoitRla relation (abstraite) dont les deux attributs sont : identification du client, pizza
command´ee par ce client. SoitSla relation donnant toutes les pizzas. Il s"agit de faire la
division deRparS.
On traduit l"´enonc´e avec "il n"existe pas" : il s"agit de trouver les clients pour lesquels il
n"existe aucune pizza qu"ils n"ont pas command´ee.
SELECT Prenom, Clients.Nom FROM Clients
WHERE NOT EXISTS (SELECT Cle FROM Pizzas
WHERE NOT EXISTS (SELECT * FROM Commandes
WHERE Client = Clients.Cle
AND Pizza = Pizzas.Cle))
9)SELECT SUM (Nombre * Prix)FROM CommandesJOIN CompositionCommande ON Commandes.Cle = CompositionCommande.Commande
JOIN Pizzas ON CompositionCommande.Pizza = Pizzas.Cle
WHERE Commandes.Cle = 1830
10) Il semble logique de d´esigner la liste d"attributs(Commande,Pizza)comme cl´e primaire de
cette relation car pour une commandecdonn´ee et une pizzapdonn´ee, il y a logiquement au
plus un seul uplet dans la relationsCompositionCommandecorrespondant `a cette commande
et `a cette pizza ; par contre il peut y avoir plusieurs uplets correspondant `a la commandec,
et plusieurs uplets correspondant `a la pizzap.
11) On d´esire maintenant tarifer la livraison en fonction de la ville de livraison (qui correspond
peu ou prou `a la longueur du trajet).
Proposer une nouveau sch´ema relationnel. Il faudra pouvoir r´epondre `a la question : "quel
est la prix de la commande num´erondont le client habite `a Betton" ? (´ecrire effectivement
cette requˆete dans votre sch´ema).
Cl´ement PicardLyc´ee Chateaubriand 4/4