TP n 16 : Bases de données (III) Requêtes imbriquées I

Lycée Victor Hugo
MPSI-PCSI 2016-2017
TP no 16 : Bases de données (III)
Requêtes imbriquées
I
Introduction
Dans ce TP nous allons effectuer, en plus de tout ce que nous avons étudié, des requêtes
imbriquées.
Nous allons continuer à travailler sur la base de données world. Pour mémoire elle
contient la table Country, la table City et la table CountryLanguage. Les descriptions
de ces tables ne sont pas rappelées, mais vous pouvez y accéder par la commande SQL :
DESCRIBE.
De même certaines évidences de manipulations ne seront pas rappelées, elles doivent
être acquises.
II
Chargement de la base de données
Action H
1. Télécharger le fichier CreationBaseWorld.sql à partir du site du lycée (il sera
copié dans le répertoire Téléchargements)
2. Ouvrir un terminal (explications au tableau, mais a priori CTRL+ALT+T devrait
fonctionner) ;
3. Changer de répertoire pour vous placer dans le répertoire Téléchargements en tapant la ligne suivante dans le terminal :
cd Téléchargements
puis appuyer sur Entrée ;
4. Charger la base de données en tapant dans le terminal fidèlement la commande
suivante, puis en appuyant sur Entrée (attention aux espaces, aux tirets !) ;
mysql -u eleves -p < CreationBaseWorld.sql
5. Saisir le mot de passe habituel pour l’informatique commune puis appuyer sur
Entrée ; Il n’apparaît pas à l’écran, ce qui est normal pour un mot de passe...
6. La base de données, tables, attributs et enregistrements est alors créée en quelques
secondes.
7. Lancez l’application MySQL Workbench en cliquant sur l’icône correspondante
(icône comportant un dauphin).
8. Cliquez sur le rectangle gris Connexion élèves.
Un mot de passe vous sera demandé : il s’agit de infocommune.
9. Vérifiez dans le panneau latéral gauche que la base world est présente et contient
les trois tables.
1
Lycée Victor Hugo
III
MPSI-PCSI 2016-2017
Rappels rapides sur les requêtes imbriquées
Pour augmenter le pouvoir d’expression des requêtes SQL il est autorisé d’utiliser une
sous-requête dans les situations suivantes :
• dans un prédicat d’une clause WHERE prenant la forme d’une comparaison, quand la
sous-requête ne renvoie qu’une seule valeur.
• dans un prédicat d’une clause WHERE utilisant IN ou EXISTS (éventuellement précédé
de NOT) quand la sous-requête renvoie une seule colonne avec éventuellement
plusieurs valeurs.
• après un FROM ou JOIN quand la sous-requête renvoie une table plus riche, qui
joue le rôle de table intermédiaire.
Dans tous les cas la sous-requête doit être entourée par deux parenthèses :
(SELECT....).
Si la sous-requête est utilisée après un FROM ou JOIN elle doit en plus être suivie d’un
nom, qui pourra vour servir à faire référence aux colonnes de la table intermédiaire :
FROM (SELECT....)nom_table_intermédiaire ....
IV
Au travail...
Script 1 H
Faire afficher le nom et la population de toutes les villes, par ordre de population décroissante. En déduire la ville la plus peuplée.
Retrouver plus efficacement ce résultat en utilisant une seule requête ne donnant que
la ville concernée (et sa population).
Dans toute la suite, sauf indication contraire, l’objectif est de réponse
à la question posée en n’utilisant qu’une seule requête (avec bien sûr
une sous-requête !)
Script 2 H
Faire afficher le nom de tous les pays dont la densité de population (définie comme le
nombre d’habitants moyen par unité de surface) est supérieure à la densité moyenne
mondiale.
Par lecture des résultats, la France en fait-elle partie ?
Script 3 H
Faire afficher toutes les villes dans le même pays que Houston.
Script 4 H
Même question que précédemment, mais en faisant afficher en plus le nom du pays.
Attention il ne s’agit pas d’une modification mineure de la requête précédente...
Script 5 H
Faire afficher les noms des villes des districts qui possèdent une ville de plus d’un million
d’habitants
2
Lycée Victor Hugo
MPSI-PCSI 2016-2017
Script 6 H
Faire afficher les pays dont aucune ville n’a plus de 10000 habitants.
Script 7 H
On cherche à déterminer les pays dans lesquels on ne parle pas l’anglais. On propose la
requête suivante :
SELECT CountryCode, Language FROM CountryLanguage WHERE Language <> ’
English’;
Tester si cette requête convient (indication : regarder si le Royaume-Uni de code GBR
fait partie des résultats rendus...). Expliquer.
Script 8 H
Proposer une requête correcte avec utilisation d’un opérateur NOT EXISTS. On pourra
se contenter comme dans la requête précédente de ne faire afficher que le code du pays.
Pour ce faire il faudra renommer sous deux noms différents la table CountryLanguage
qui va apparaître dans la requête principale et dans la sous-requete, pour lever l’ambiguïté
sur le champ CountryCode dont vous devriez avoir besoin.
Script 9 H
On recherche les pays avec au moins deux langues officielles. Que fait la requête suivante :
SELECT CountryCode, COUNT(*)FROM CountryLanguage WHERE IsOfficial = ’
T’GROUP BY CountryCode;
Essayer de modifier très simplement cette requête pour répondre à la question posée.
Que constate-t-on ?
On se rabat donc sur une solution avec requête imbriquée après une clause FROM. Écrire
la requête correspondante.
Script 10 H
Faire afficher pour chaque pays le nom du pays (la colonne sera renommée Pays), le code
du pays (renommé Code) et la population maximale d’une ville de ce pays (renommée
Habitants).
Réutiliser (par un copier-coller) la requête précédente pour faire afficher pour chaque
pays le nom du pays, le nom de la ville de ce pays ayant le plus grand nombre d’habitants,
et ce nombre maximum d’habitants.
3