Transcript Modèle E/A Relation binaire (0,1)

SERIE 2
PCSI
2014 - 2015
Exercices d’application
Questions des élèves :
Modèle E/A
Relation binaire (0,1)-(1,1)
On duplique la clé de la table basée sur l'entité à cardinalité (0,1) dans la table basée sur
l'entité à cardinalité (1,1).
Exemple :
Relation binaire (0,1)-(0,1)
On duplique la clé d'une des tables dans l'autre. Lorsque la relation contient elle-même des
propriétés, celles-ci deviennent également attributs de la table dans laquelle a été ajoutée
la clé étrangère.
L.OUSTOUH
1 / 22
CPGE OMAR IBN LKHATTAB
SERIE 2
PCSI
2014 - 2015
Exercice 1 :
Proposer un domaine approprié pour les attributs représentant :
- Une adresse e-mail
texte
- Une nationalité
texte
- Un âge
entier
- Date
entier
- Mot de passe
texte
- Classement à un concours
entier
- Une note de music
texte
Exercice 2 :
Proposez des schémas relationnels représentant respectivement :
- L’inventaire d’un supermarché
La relation Produit dont le schéma relationnel est :((idProduit, entier), (libellé,
texte),(prix,
réel),(rayon,
texte),
(codebarre,
entier),(dateAchat,entier),(dateExpiration,entier))
- Le parc informatique d’une entreprise
Matériel((nserie, entier),(fabriquant, texte),(dateAchat, entier),(PrixAchat,réel),
(ninventaire,entier),(service,
texte),(Duréegarantie,entier),(fournisseur,texte),(ncommande,entier))
Pour chacun des schémas relationnels proposés, peut-on espérer qu’il existe une clé
primaire ? Si oui, laquelle?
Idproduit pour la relation Produit
Ninventaire pour la relation Matériel
Exercice 3 :
Le camping Les Dunes de l'Océan souhaiterait informatiser la location de ses
emplacements. Il a recensé dans 3 tables les informations nécessaires à la gestion des
locations.
Table CONTRAT.
L.OUSTOUH
2 / 22
CPGE OMAR IBN LKHATTAB
SERIE 2
PCSI
Code
06JUI258
06JUI259
06JUI260
Date Debut Loc
Date Fin Loc
10/07/06
10/07/06
11/07/06
24/07/06
17/07/06
31/07/06
2014 - 2015
N°
1249
146T
1248
N° Emplacement
147MH
1247
152T
Table CLIENT.
N°
1247
1248
1249
Nom
Prenom
Adresse
BERT
SALI
GIRARD
Rene
Mehdi
Veronique
22, av. de Picardie – 69000 LYON
14, rue des Armées – 13000 MARSEILLE
22, bd Pasteur – 75000 PARIS
Table EMPLACEMENT.
N°
Surface
Px Location par Nuité
147MH
45
70
148MH
32
50
151T
19
Entre 15 & 18
152MH
47
65
1. Distinguer dans chaque table,les clés primaires & étrangères.
Clé primaire (PK : Primary Key)
Clé étrangère (FK: Foreign Key)
2. Relever dans les tables les erreurs de saisie.
Exercice 4 :
Un loueur de voitures et d’utilitaires souhaite informatiser la gestion de son entreprise. La
base de données devra notamment stocker des informations sur :
- Les types de véhicules proposés : volume utile, cout d’entretien annuel, tarif de
location
- Les véhicules effectivement possédés par l’entreprise : type, marque,
immatriculation, est il en cours de location ou non …
- Les clients : coordonnées, véhicule loué, dates de location …
Proposer un ensemble de schémas relationnels, de clés primaires et étrangères permettant
de réaliser la gestion de cette entreprise.
L.OUSTOUH
3 / 22
CPGE OMAR IBN LKHATTAB
SERIE 2
PCSI
2014 - 2015
MLD :
client (Idclient, Adresse, tel)
Véhicule (Immatriculation, Type, Marque, Loué, Volume_Utile,
Cout_entretien_annuel, Tarif_location)
Louer (#Idclient, #Immatriculation, Date_location)
Exercice 5 :
Une agence immobilière gère la location d’appartements et de maisons pour le compte de
propriétaires. Cette agence maintient une base de données relationnelles dans laquelle
chaque propriétaire (nom, prénom, adresse, tel.) remet en gestion à l’agence plusieurs
appartements ou maisons (taille, adresse, prix de location) selon un contrat (durée,
pourcentage) propre à chaque bien. Chaque appartement ou maison est donnée en
location par un bail identifié par un numéro et pour une période déterminée, à un locataire
dont on enregistre le nom, prénom, adresse, tél.
Proposer un ensemble de schémas relationnels, de clés primaires et étrangères.
Modèle E/A
L.OUSTOUH
4 / 22
CPGE OMAR IBN LKHATTAB
SERIE 2
PCSI
2014 - 2015
MLD
Propriétaire (Idpropriétaire, Nom, Prénom, Adresse, Tel)
Bien (IdBien, Taille, AdresseB, PrixLocation)
Locataire (Idlocataire, NomL, PrenomL, AdresseL, TEL_L)
Contrat (#Idpropriétaire, #IdBien, duree, Pourcentage)
Bail (#IdBien, #Idlocataire, Numéro, Période)
Exercice 6 :
La société Software réalise des sites Web. Pour la création d’un site Web plusieurs
informaticiens (identifiés par un numéro et un nom) peuvent intervenir en même temps.
Tout client est identifié par un numéro unique, un nom et une adresse. Un client peut
commander plusieurs sites Web. Un site porte un numéro de référence et une désignation
qui décrit son contenu. Enfin chaque informaticien à un taux horaire qui lui est propre.
Proposer un ensemble de schémas relationnels, de clés primaires et étrangères.
L.OUSTOUH
5 / 22
CPGE OMAR IBN LKHATTAB
SERIE 2
PCSI
2014 - 2015
MLD
Informaticien (NumeroI, NomI, TauxHoraire)
Client (NumeroC, NomC, Adresse)
SiteWeb (Numref, Designation, NumCommande,DateCommande,NumeroC)
Créer (#NumeroI, #Numref, NbreHeure, Date)
L.OUSTOUH
6 / 22
CPGE OMAR IBN LKHATTAB
SERIE 2
PCSI
2014 - 2015
CLASSES PREPARATOIRES AUX GRANDES ECOLES : CPGE
Option : Physique Chimie et Sciences de l’Ingénieur (PCSI sup)
Introduction aux bases
de données
Etablissement : CPGE
Enseignante :
Algèbre relationnelle
OMAR IBN LKHATTAB
L.OUSTOUH
SERIE N°2
corrigé
Exercice 1 : Application d’opérations sur des relations.
On considère ici les relations suivantes :
R1
Id Nombre Premier
1
2
1
2
3
1
3
4
0
4
5
1
5
6
0
6
8
0
7
10
0
8
11
1
9
12
0
10
15
0
11
19
1
R2
Diviseur Multiple
2
4
3
6
4
8
5
10
6
12
1. Déterminez les résultats des opérations suivantes :
a.
: sélection de R1 selon la condition premier=1
Les nombres premiers de la relarion R1
b.
La liste des nombres de R1, leurs primalités et leurs multiples
c.
La somme des nombres de R1
d.
Projection de R1 selon premier, résultats : 0,1
2. Ecrire les opérations, éventuellement composées, permettant d’obtenir les
résultats suivants :
a. La somme des nombres premiers (ceux dont l’attribut Premier vaut 1)
b. La somme des nombres présents dans R2 dont l’attribut Diviseur est premier.
c. La vérification qu’un nombre indiqué comme premier n’est pas une valeur de
l’attribut Multiple de R2.
L.OUSTOUH
7 / 22
CPGE OMAR IBN LKHATTAB
SERIE 2
PCSI
2014 - 2015
Un nombre indiqué comme premier n’est pas une valeur de l’attribut
Multiple de R2 si la fonction comptage retourne une valeur nulle.
Exercice 2 :
On considère les relations :
Ici, on est dans le cas classique où id est une clé primaire pour classe et élève
possède une clé étrangère vers classe.
Traduire les requêtes suivantes en opérations de l’algèbre relationnelle.
1. Obtenez la liste des filières proposées dans ce lycée.
2. Obtenez toutes les informations concernant les classes de PCSI
3. Obtenez les prénoms des élèves des classes 1 et 3.
4. Obtenez les noms et les notes des élèves ayant eu une note inférieure à 10.
5. Obtenez les élèves scolarisés en filière MPSI.
6. Calculez la moyenne des élèves de la classe 3.
7. Déterminez la classe ayant la meilleure moyenne.
Proposition 1
Proposition 2
Exercice 3 :
On va considérer ici un ensemble de
relations utiles pour gérer un
complexe hôtelier. Celui-ci est
composé de différents bâtiments,
identifiés par leur nom et leur nombre
d’étoiles. Ils sont représentés dans la
relation bâtiment :
L.OUSTOUH
8 / 22
Bâtiment
Nom Etoiles
Rose
3
Jasmin
2
Lys
3
Les chambres comportent chacune un
numéro, un nom de bâtiment et un
CPGE OMAR IBN LKHATTAB
SERIE 2
PCSI
nombre de fenêtres. Elles sont
représentées dans la relation
chambre :
Chambre
Numéro Bâtiment fenêtres
2014 - 2015
lit
idlit chambre
1
1
2
1
3
2
4
3
5
1
6
2
7
2
8
3
9
3
10
4
11
1
12
2
13
3
1
Rose
2
2
Rose
1
3
Rose
1
1
Jasmin
1
2
Jasmin
0
3
Jasmin
1
4
Jasmin
1
1
Lys
3
2
Lys
2
3
Lys
2
Certaines chambres possèdent deux
lits. On sépare donc les lits dans une
autre relation, où ils comportent
chacun un identifiant numérique
unique au sein du complexe, un
numéro de chambre, ainsi que le
bâtiment correspondant. Ils sont
représentés dans la relation lit :
batlit
Rose
Rose
Rose
Rose
Jasmin
Jasmin
Jasmin
Jasmin
Jasmin
Jasmin
Lys
Lys
Lys
Enfin, les nuitées sont identifiées par
le nom du client, l’identifiant du lit et
par la date. Elles sont représentées
dans la relation nuitée :
Nuitée
client
Lit
date
Lennon
McCartney
Starr
Harrison
Page
Plant
Jones
Bonham
Townshend
1
8
3
2
10
1
11
7
1
15-08-1969
18-08-1969
03-07-1969
01-08-1969
05-08-1969
13-08-1969
05-08-1969
02-08-1969
08-08-1969
Pour chacune des recherches suivantes, on indique une décomposition dans l’algèbre
relationnelle, ainsi que le résultat obtenu :
1. Obtenez le nom des clients ayant séjourné dans le bâtiment Jasmin.
2. Obtenez le nom des clients ayant séjourné dans un bâtiment 3 étoiles.
L.OUSTOUH
9 / 22
CPGE OMAR IBN LKHATTAB
SERIE 2
PCSI
2014 - 2015
3. Obtenez le nom des clients ayant séjourné dans une chambre ayant au moins 2
fenêtres.
Exercice 4 :
On considère la relation suivante :
Relevé
Filière Numéro Elève Note
MPSI
PCSI
MPSI
PCSI
PCS
MPSI
PCSI
PCSI
PCSI
MPSI
PCSI
1
2
1
1
2
1
1
1
2
2
1
Meyer
Martin
Bernard
Robert
Dubois
Lemaire
Albert
Garcia
Richard
Petit
Simon
17,5
7,75
9,25
14,0
11,5
7,25
13,0
16,5
12,5
15,5
10,5
Traduisez les requêtes suivantes en opérations de l’algèbre relationnelle.
1. Calculez la moyenne des PCSI et celle des MPSI.
2. Calculez la moyenne de chaque classe.
3. Calculez la moyenne de la PCSI 2 et celle de la MPSI 2.
4. Sélectionnez les classes dont la moyenne est supérieure ou égale à douze.
Exercice 5 :
On considère les deux relations suivantes où les clés sont en caractères gras :
* LESFILMS (TITRE, PAYS, ANNEE, REALISATEUR, DUREE)
* LESACTEURS (#TITRE, ACTEUR)
et où les attributs ont les significations et les types suivants:
-TITRE : titre d'un film (chaine 50 caractères)
-PAYS : pays d'où un film est originaire (chaine 10 caractères)
Page 10 sur 22
SERIE 2
PCSI
2014 - 2015
-ANNEE : année de sortie du film (entier 4 chiffres)
-REALISATEUR: Nom du réalisateur du film (chaine 20 caractères)
- DUREE : durée du film en mn (entier 3 chiffres)
-ACTEUR : Nom d'acteur (chaine 20 caractères)
La relation LESFILMS donne pour chaque film identifié par son titre, le pays, l'année de sortie,
réalisateur et la durée.
La relation LESACTEURS donne pour chaque film l'ensemble des principaux acteurs.
A l'aide de l'algèbre relationnelle exprimer les requêtes suivantes:
Q1: Liste des films français (titre, année, réalisateur).
Q2: Donnez les années de sortie des films tournés par GABIN.
Q3: Trouvez les acteurs qui ont tourné avec TRUFFAUT comme réalisateur.
Q4: Trouvez tous les acteurs qui ont été partenaires de DENEUVE.
Q5: Liste des films où le réalisateur est aussi acteur
Q6: Réalisateurs ayant joué comme acteurs dans des films qu'ils ne réalisaient pas euxmêmes.
Q7: Réalisateurs qui ne jouent pas dans un de leur propre film.
Cette requete prends des sens différents :
Premier sens : si l’article un est un article indéfini, le sens de la phrase qualifiée d’inachevée
selon les spécialistes de la langue française, deviens : les réalisateurs qui n’ont joué dans
aucun de leur propre films.
La réponse sera la différence entre l’ensemble des réalisateurs et l’ensemble des réalisateurs
qui sont acteurs dans leur propre films
Deuxième sens : si l’article un est un article numéral, le sens de la requete deviens : les
réalisateurs qui ne jouent pas dans un et un seul de leur propre film
L’idée est de compter le nombre de films par réalisateur et le nombre de films où le
réalisateur est acteur, puis faire la différence. Si la différence est égale à un, on sélectionne le
réalisateur.
Page 11 sur 22
SERIE 2
PCSI
2014 - 2015
Troisième sens : il existe un film où le réalisateur n’est pas acteur, l’existence peut signifier un
ou plusieurs films mais pas tous.
La négation sera : quelque soit le film d’un réalisateur, ce dernier est acteur.
La solution est donc :
Sélectionner l’ensemble des realisateurs qui jouent dans au moins un de leurs films et de
retrancher l’ensemble des réalisateurs qui jouent dans tous leurs propres films.
Q8: Donnez les acteurs qui jouent dans tous les films de Truffaut.
La solution est la division
Page 12 sur 22
SERIE N°3
PSI
2014 - 2015
Exercices supplémentaires :
Exercice 1 :
On considère la base de données BD AIRBASE suivante :
- PILOTE (NUMPIL, NOMPIL, ADR, SAL)
- AVION (NUMA, NOMAV, CAP, LOC)
- VOL (NUMVOL, NUMPIL, NUMAV, VILLE_DEP, VILLE_ARR, H_DEP, H_ARR)
Convention :
Les clés primaires sont soulignées et les clés étrangères sont en italique gras.
Q1 : Donnez la liste des avions dont la capacité est supérieure à 350 passagers.
Q2 : Quels sont les numéros et noms des avions localisés à Nice ?
Q3 : Quels sont les numéros des pilotes en service et les villes de départ de
leurs vols ?
Q4 : Donnez toutes les informations sur les pilotes de la compagnie.
Q5 : Quel est le nom des pilotes domiciliés à Paris dont le salaire est supérieur à
15000 F ?
Q6 : Quels sont les avions (numéro et nom) localisés à Nice ou dont la capacité
est inférieure à 350 passagers?
Q7 : Liste des vols au départ de Nice allant à Paris après 18 heures ?
Q8 : Quels sont les numéros des pilotes qui ne sont pas en service ?
Q9 : Quels sont les vols (numéro, ville de départ) effectués par les pilotes de
numéro 100 et 204 ?
Q10 : Donnez le numéro des vols effectués au départ de Nice par des pilotes
Niçois ?
Q11 : Quels sont les vols effectués par un avion qui n'est pas localisé à Nice ?
L.OUSTOUH
13 / 2
CPGE OMAR IBN LKHATTAB
SERIE N°3
PSI
2014 - 2015
Q12 : Quels sont les pilotes (numéro et nom) assurant au moins un vol au
départ de Nice avec un avion de capacité supérieure à 300 places ?
Q13 : Quels sont les noms des pilotes domiciliés à Paris assurant un vol au
départ de Nice avec un Airbus ?
Q14 : Quels sont les numéros des vols effectués par un pilote Niçois au départ
ou à l'arrivée de Nice avec un avion localisé à Paris ?
Q15 : Quels sont les pilotes (numéro et nom) habitant dans la même ville que le
pilote Dupont ?
Q16 : Quels sont les numéros des pilotes en service différents de celui de
Durand ?
Q17 : Quelles sont les villes desservies à partir de la ville d'arrivée d'un vol au
départ de Paris ?
Q18 : Quels sont les appareils (leur numéro) localisés dans la même ville que
l'avion numéro 100 ?
)
Q19 : Quels sont les numéros et noms des pilotes domiciliés dans la même ville
que le pilote Dupont et dont le salaire est supérieur à celui de Dupont ?
Q20 : Quels sont les numéros et noms des pilotes qui effectuent un vol au
départ de leur ville de résidence ?
Q21 : Y a-t-il des homonymes parmi les pilotes ? Si oui, donner leur numéro et
nom.
L.OUSTOUH
14 / 2
CPGE OMAR IBN LKHATTAB
SERIE N°3
PSI
2014 - 2015
Exercice 2 :
On suppose qu'une bibliothèque gère une base de données dont le schéma est
le suivant (les clés primaires des relations sont soulignées) :
Emprunt (Personne, Livre, DateEmprunt, DateRetourPrevue,
DateRetourEffective)
Retard (Personne, Livre, DateEmprunt, PenalitéRetard)
Exprimez, lorsque cela est possible, les requêtes suivantes en algèbre
relationnelle :
1. Quelles sont les personnes ayant emprunté le livre "Recueil Examens
BD" ?
2. Quelles sont les personnes n'ayant jamais rendu de livre en retard ?
3. Quelles sont les personnes ayant emprunté tous les livres (empruntés
au moins une fois) ?
4. Quels sont les livres ayant été empruntés par tout le monde (i.e. tous
les emprunteurs) ?
5. Quelles sont les personnes ayant toujours rendu en retard les livres
qu'elles ont empruntés?
L.OUSTOUH
15 / 2
CPGE OMAR IBN LKHATTAB
SERIE N°3
PSI
2014 - 2015
CLASSES PREPARATOIRES AUX GRANDES ECOLES : CPGE
Option : Physique Chimie et Sciences de l’Ingénieur (PCSI sup)
Introduction aux bases de
données
Etablissement : CPGE OMAR
Langage SQL
Enseignante : L.OUSTOUH
IBN LKHATTAB
SERIE N° 3
CORRIGE
Exercice 1 :
On considère la base carte de France, avec les trois tables Villes, Jumelages et Regions. Les
attributs correspondant à une clé primaire sont soulignés, les dépendances relationnelles
sont symbolisées par une flèche clé étrangère  clé primaire.
I.
LDD
1. Créer les tables qui correspondent au modèle relationnel ci-dessus.
CREATTION DE LA TABLE JUMELAGES :
CREATE TABLE JUMELAGES(
VILLE1 INTEGER REFERENCES VILLES(CLE),
VILLE2 INTEGER REFERENCES VILLES(CLE),
PRIMARY KEY(VILLE1,VILLE2)) ;
CREATION DE LA TABLE VILLES :
CREATE TABLE VILLES(
CLE INTEGER PRIMARY KEY,
NOM VARCHAR(80),
REGION VARCHAR(40) REFERENCES REGIONS(NOM),
L.OUSTOUH
16 / 2
CPGE OMAR IBN LKHATTAB
SERIE N°3
PSI
2014 - 2015
HABITANTS INTEGER,
CODEPOSTAL INTEGER) ;
CREATION DE LA TABLE REGIONS
CREATE TABLE REGIONS(
NOM VARCHAR(40) PRIMARY KEY,
CAPITALE INTEGER REFERENCES VILLES(CLE));
II.
Algèbre relationnelle
2. Quel sens peut-on donner à chacune des relations suivantes ?
1)
Nom des villes dont le nombre d’habitants est inférieur à 2000 ou supérieur à 30000.
2)
Nom de région où la capitale est saintMarcellin.
3. Calculer à l’aide de l’algèbre relationnelle :
3) le nombre d’habitants de Rennes ;
4) les noms de toutes les communes de Bretagne ;
5) les villes jumelées avec Rennes ;
6) les couples des noms de villes jumelées ;
Page 17 sur 22
SERIE N°3
PSI
2014 - 2015
7) le nombre d’habitants de la Bretagne ;
8) le nombre de villes jumelées avec Rennes ;
9) les villes jumelées avec au moins une ville de Bretagne ;
10) les villes jumelées avec toutes les villes de Bretagne ;
11) les noms de communes apparaissant dans toutes les régions.
III.
Requêtes d’interrogation
4. Spécifier en langage courant le résultat de chacune des requêtes suivantes :
12)
SELECT Villes.Habitants
FROM Regions JOIN Villes ON Villes.Cle = Regions.Capitale
WHERE Regions.Nom = ’Franche-Comte’
Page 18 sur 22
SERIE N°3
PSI
2014 - 2015
Le nombre d’habitants de la capitale de la région « franche-comté »
13)
SELECT V1.Nom, V1.Habitants FROM Jumelages
JOIN Villes AS V1 ON Jumelages.Ville1 = V1.Cle
JOIN Villes AS V2 ON Jumelages.Ville2 = V2.Cle
WHERE V2.Habitants > 100000
UNION
SELECT V2.Nom, V2.Habitants FROM Jumelages
JOIN Villes AS V1 ON Jumelages.Ville1 = V1.Cle
JOIN Villes AS V2 ON Jumelages.Ville2 = V2.Cle
WHERE V1.Habitants > 100000
ORDER BY V1.Habitants DESC ;
Le nom et le nombre d’habitants des villes jumelées avec des villes dont le nombre
d’habitants est supérieur à 100000. Les résultats sont ordonnés par ordre décroissant
d’habitants.
SELECT V1.Nom FROM Villes AS V1
WHERE NOT EXISTS( SELECT * FROM (SELECT * FROM Villes WHERE
Region=’Bretagne’) AS V2
WHERE NOT EXISTS(SELECT * FROM Jumelages AS J
WHERE ((J.Ville1=V1.Cle AND J.Ville2=V2.Cle) OR
(J.Ville1=V2.Cle AND J.Ville2=V1.Cle))))
Le nom des villes jumelées avec toutes les villes de la region de Bretagne.
5. Pour chacune des questions suivantes, écrire une requête SQL permettant de
connaître :
14)
les villes de Franche–Comté, en ordonnant les résultats par ordre croissant
d’habitants ;
SELECT *
FROM Villes
Page 19 sur 22
SERIE N°3
PSI
2014 - 2015
WHERE Region=’Franche-comté’
ORDER BY Habitants DESC;
15)
le plus grand code postal des villes de Bretagne ;
SELECT MAX(CodePostal)
FROM Villes
WHERE Region=’Bretagne’ ;
16)
le nombre moyen d’habitants des villes de Franche–Comté ;
PROPOSITION 1
SELECT
AVG(Habitants)
PROPOSITION 2
SELECT
AVG(Habitants)
FROM (SELECT *
FROM VILLE
WHERE
REGION=’FRANCH
E-COMTE')
17)
PROPOSITION 3
SELECT
AVG(Habitants)
FROM Villes
FROM Villes
GROUP BY
Region
WHERE
Region=’Franch
e-comté’
HAVING
Region=’Franch
e-comté’
les villes jumelées avec Rennes.
SELECT V1.*
FROM Villes V1, Jumelages J, Villes V2
WHERE V1.Cle=J.Ville1 AND V2.Cle=J.Ville2 AND V2.Nom=’Rennes’
UNION
SELECT V2.*
FROM Villes V1, Jumelages J, Villes V2
WHERE V1.Cle=J.Ville1 AND V2.Cle=J.Ville2 AND V1.Nom=’Rennes’
18)
les villes jumelées à au moins une ville de Bretagne ;
Page 20 sur 22
SERIE N°3
PSI
2014 - 2015
SELECT V1.*
FROM Ville V1, Jumelages J, Ville V2
WHERE V1.Cle=J.Ville1 AND V2.Cle=J.Ville2 AND V2.Region='BRETAGNE'
UNION
SELECT V2.*
FROM Ville V1, Jumelages J, Ville V2
WHERE V1.Cle=J.Ville1 AND V2.Cle=J.Ville2 AND V1.Region='BRETAGNE'
19)
les villes qui ne sont pas une capitale de région ;
PROPOSITION 1
SELECT *
PROPOSITION 2
SELECT V.*
FROM Villes
FROM Villes V
WHERE Cle NOT IN (SELECT
Capitale
WHERE NOT EXISTS (SELECT R.*
FROM
Regions)
FROM Regions R
WHERE R.Capitale=V.Cle)
Pour chaque ville, on vérifie
dans la sous requete si elle est
capitale grace à la jointure :
WHERE R.Capitale=V.Cle
20)
les noms de communes apparaissant dans toutes les régions ;
SELECT V1.Nom FROM Villes V1
WHERE NOT EXISTS(SELECT *
FROM Regions R
WHERE NOT EXISTS ( SELECT * FROM Villes V2
Page 21 sur 22
SERIE N°3
PSI
2014 - 2015
WHERE V2.Nom=V1.Nom AND
V2.Region=R.Nom))
V2.Nom=V1.Nom: la condition de jointure avec V1 concerne le Nom de la ville et
Nom la clé.
21)
les villes de Bretagne plus peuplées que la moyenne des villes de Bretagne;
SELECT *
FROM Villes
WHERE Region=’Bretagne’ AND Habitants>(SELECT AVG(Habitants) FROM Villes
WHERE Region=’Bretagne’)
22)
les régions ayant le plus de villes.
SELECT R.REGION
FROM(SELECT REGION,COUNT(CLE) AS NBVILLES
FROM VILLE
GROUP BY REGION) AS R
WHERE NBVILLES=(SELECT MAX(R1.NBVILLES)
FROM(SELECT REGION,COUNT(CLE) AS NBVILLES
FROM VILLE
GROUP BY REGION) AS R1);
Page 22 sur 22