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
Exercices corrigés Bases de Données Avancées Une compagnie d'assurance veut utiliser un SGBD pour stocker ses contrats d'assurances de voiture. Une police d'assurance est souscrite par une seule personne
3.3 Algèbre relationnelle - Irif Correction : 1. Les clefs primaires : Auteur : CODE_AUTEUR.
Bases de Données (BD3) Corrigé de l'examen Sujet: Nous proposons la relation universelle suivante pour stocker dans une base de données l'ensemble des résultats de notre plateforme.
LIF4 - TD9 Requêtes SQL Correction - CNRS 2.3.3 Exercices corrigé . Requêtes sur une base de données existante . Supposons que le serveur soit celui d'IMDb qui gère une base de données 
Travaux Dirigés de Base de données Correction. Exercice 1: Donner un schéma de base de données correspondant a ce diagramme On considere le Schéma de la base de données CINEMA
Exercices de conception de bases de données - Stéphane Crozat Exercice 1 : Consultation et sélection simple de données. La base de données est utilisée pour la gestion simplifiée des ventes
Initiation pratique a la macroeconomie, 2e edition. Etudes de cas Auto-manuel de macro-économie, cours, exercices et corrigés, Édition . Initiation à la macro Cas, Exercices Corriges Et Qcm. comment5 ITB - 2e année d'études Étudiant Les marchés de capitaux Examen. QCM Énoncé Durée 
COMPACT Physique : Cours et 250 exercices corriges de PCSI PCSI, MPSI, PT : le cours et 250 exercices corrigés / Chérif Zananiri,. 9 mars 2017 auto-adjoints avec exercices et solutions. 100 QCM COMPACT Physique : Cours Et 250 Exercices Corriges De PCSI - MPSI - PT
Sujets corriges du bac 99, sciences de la vie et de la terre terminale Sujets Corriges Du Bac 99, Sciences De La Vie Et De La Terre .s preuve et m thodes du bac exercice corrige tle s physique chimie . re simple Outil de traduction semi-automatique depuis un sujet de bac ou du code Python, .
Read eBook » automatique - systèmes linéaires, non linéaires événements discrets - cours et exercices corrigés (3e édition) CORRIGE (3EMEDITION) - To read automatique - systèmes linéaires
Pratique des normes IAS/IFRS : 40 cas d'application avec corrigés ment les travaux de l'I.R.Ma. sur le sujet les personnes Le P.C.S. apparaît plus que jamais comme l'instrument de gestion de toutes Cette deuxième édition intitulée « Plan Communal de Sauvegarde - S'organiser pour être prêt Parallèlement
à télécharger 1 - Faculté des Sciences Economiques, Commerciales meilleure maîtrise des techniques formulées par les normes IAS/IFRS. Elle subvention d'investissement virée au résultat de l'exercice. 6 000. Totaux Le swap est un instrument de couverture de l'emprunt et des intérêts à payer.