Exercices sur des villes de France

Nous allons réaliser des recherches sur les villes françaises.

Pour cela nous allons créer une table pour enregistrer les villes et une table pour enregistrer les départements.

Création des tables

En utilisant SQL, vous allez créer les tables suivantes en respectant à la lettre les instructions qui suivent.

Table Villes

Créer une table nommée 'villes_france_free' avec les champs suivants :

`ville_id` de type INT, ne peut pas être vide. Ce champ sera la clé primaire
`ville_departement` de type varchar avec 3 caractères maxi, peut être vide
`ville_slug` de type varchar avec 255 caractères maxi, peut être vide
`ville_nom` de type varchar avec 45 caractères maxi, peut être vide
`ville_nom_simple` de type varchar avec 45 caractères maxi, peut être vide
`ville_nom_reel` de type varchar avec 45 caractères maxi, peut être vide
`ville_nom_soundex` de type varchar avec 20 caractères maxi, peut être vide
`ville_nom_metaphone` de type varchar avec 22 caractères maxi, peut être vide
`ville_code_postal` de type varchar avec 255 caractères maxi, peut être vide
`ville_commune` de type varchar avec 3 caractères maxi, peut être vide
`ville_code_commune` de type varchar avec 5 caractères maxi, peut être vide
`ville_arrondissement` de type INT, peut être vide
`ville_canton` de type varchar avec 4 caractères maxi, peut être vide
`ville_amdi` , de type INT, peut être vide
`ville_population_2010` de type INT, peut être vide
`ville_population_1999` de type INT, peut être vide
`ville_population_2012` de type INT, peut être vide
`ville_densite_2010` de type INT, peut être vide
`ville_surface` de type float peut être vide
`ville_longitude_deg` de type float peut être vide
`ville_latitude_deg` de type float peut être vide
`ville_longitude_grd` de type varchar avec 9 caractères maxi, peut être vide
`ville_latitude_grd` de type varchar avec 8 caractères maxi, peut être vide
`ville_longitude_dms` de type varchar avec 9 caractères maxi, peut être vide
`ville_latitude_dms` de type varchar avec 8 caractères maxi, peut être vide
`ville_zmin` de type INT, peut être vide
`ville_zmax` de type INT, peut être vide

Table départements

Créer une table nommée 'departement' avec les champs suivants :

`departement_id` de type INT, ne peut pas être vide. Ce champ sera la clé primaire
`departement_code` de type varchar avec 3 caractères maxi, peut être vide
`departement_nom` de type varchar avec 255 caractères maxi, peut être vide
`departement_nom_uppercase` de type varchar avec 255 caractères maxi, peut être vide
`departement_slug` de type varchar avec 255 caractères maxi, peut être vide
`departement_nom_soundex` de type varchar avec 20 caractères maxi, peut être vide

Remplir les tables avec des données

Pour injecter toutes les villes de France dans votre table et les départements, télécharger les fichiers suivant.

• Villes de France
• Départements de France

Ouvrez les fichers et récupérer le code SQL pour le coller dans votre invite de commande SQLite de Visual Studio Code.

Exercices du jour

1. Obtenir la liste des 10 villes les plus peuplées en 2012
2. Obtenir la liste des 50 villes ayant la plus faible superficie
3. Obtenir la liste des départements d’outres-mer, c’est-à-dire ceux dont le numéro de département commencent par “97”
4. Obtenir le nom des 10 villes les plus peuplées en 2012, ainsi que le nom du département associé
5. Obtenir la liste du nom de chaque département, associé à son code et du nombre de commune au sein de ces département, en triant afin d’obtenir en priorité les départements qui possèdent le plus de communes
6. Obtenir la liste des 10 plus grands départements, en terme de superficie
7. Compter le nombre de villes dont le nom commence par “Saint”
8. Obtenir la liste des villes qui ont un nom existants plusieurs fois, et trier afin d’obtenir en premier celles dont le nom est le plus souvent utilisé par plusieurs communes
9. Obtenir en une seule requête SQL la liste des villes dont la superficie est supérieur à la superficie moyenne
10. Obtenir la liste des départements qui possèdent plus de 2 millions d’habitants
11. Remplacez les tirets par un espace vide, pour toutes les villes commençant par “SAINT-” (dans la colonne qui contient les noms en majuscule)

Correction

Pour vous aider à réviser et à mieux comprendre la conception des requêtes, je vous invite à consulter le fichier de correction des exercices après avoir étudier les questions.

• Correction Villes de France