CPGE PSI* 2025/2026
Requêtes SQL- Travail avec la base de données mondialNP

Vous avez à votre disposition la base de données mondialNP, qui est composée de 3 tables :
Schéma de la table:
country (Name,Code,Capital,Province,Area,Population)
city (Name,Country,Province,Population,Longitude, Latitude)   	
borders (Country1,Country2,Length)
La clé primaire de chaque table est soulignée.
Dans la table city, l'attribut Country est une clé étrangère faisant référence à la clé primaire Code de la table country.
Dans la table borders, les attributs Country1 et Country2 sont deux clés étrangères faisant référence à la clé primaire Code de la table country.

Dans ce TP, vous allez écrire des requêtes en SQL suivantes et les tester au fur et à mesure.
Commencez toujours par vous poser la question suivante : 
De quelle(s) table(s) ai-je besoin ?
Vous commencerez ainsi par remplir la partie FROM ….

I) Requêtes sans jointure (i.e. portant sur une seule table)
Travail dans la table city.
1) Faire afficher toutes les informations contenues dans cette table.

2) Combien de villes sont répertoriées dans cette table ?

3) Faire afficher sans doublons (i.e. on ne répète pas des villes qui auraient le même nom) le nom de toutes les villes répertoriées dans cette table. 

4) Ordonner cette liste par ordre alphabétique.

5) Combien y a-t-il de noms de villes distincts ?

Pour les questions suivantes, on suppose que l'on sait que le code utilisé pour la France est 'F'.

6) Faire afficher le nom de toutes les villes françaises répertoriées dans cette table.

7) Faire afficher le nom de toutes les villes françaises répertoriées dans cette table et leur population, par ordre décroissant de population.

8) Combien de villes françaises sont répertoriées dans cette table ?

9)Quelle est la population totale des villes françaises répertoriées dans cette table ?

10) Donner le nom de toutes les villes dont le nom commence par ‘C’ et finit par ‘d’.

11) Quelle est la ville la plus peuplée répertoriée dans cette base ?

12) Faire afficher le nom de toutes les villes françaises répertoriées dans cette table, qui sont situées au sud du 45ème parallèle.

13) Faire afficher la population totale des villes regroupées par pays, en faisant afficher le code du pays à côté du total de la population. 

14) Ordonner cette liste par population totale décroissante.

15) Faire afficher la même chose qu'en question a), mais on ne veut que deux lignes : celle de la France et celle de la Belgique (code 'B')

16) Faire afficher la même chose qu'en question a), mais vous ne gardez que les pays où la population totale des villes est supérieure à 10 millions.

17)(plus difficile, possibilité d'utiliser une sous-requête) Combien de pays sont affichés lors de cette dernière requête (pas de comptage à la main bien sûr:)

Travail dans la table borders.
Remarque sur la table borders: chaque frontière n'apparaît qu'une seule fois dans la table, mais par exemple quand on cherche la frontière France/Italie , on ne sait pas si la France sera Country1 ou Country2...
18) Faire afficher toutes les lignes correspondant aux frontières de la Suisse (code ‘CH’).
19)Ecrire une requête permettant de compter combien la Suisse a de frontières.

II) Requêtes avec jointures (i.e. portant sur deux tables ou plus)

20)Faire afficher le nom du pays où se trouve la ville qui s'appelle Ulm.

21) Dans quel(s) pays se trouve la ville de Cordoba ?

22) Faire afficher le nom de toutes les villes de Chine répertoriées dans cette base (on ne connaît pas le code utilisé pour la Chine, mais on sait qu’en anglais Chine se dit ‘China’…)

23) Faire afficher la population totale des villes regroupées par pays, en faisant afficher le nom du pays à côté du total de la population des villes. Vous ordonnerez cette liste par ordre décroissant de population.

24)Donner la liste de tous les couples de noms de pays correspondant à une frontière avec la France ? (par exemple dans votre liste doit apparaître (France,Italy) )

III) Requêtes diverses
25) Donner le nom des villes possédant au moins une ville homonyme (donner une liste qui si possible ne contient pas de doublons)

26)(difficile) Quel est le nom du pays possédant le plus de frontières ?