Requêtes SELECT avancées avec MySQL SQL03

Connectez-vous à votre base de données.

mysql -h localhost -u root -p

Si vous avez les droits, créez une nouvelle base de données intitulées france, sinon sélectionnez votre base de données.

CREATE DATABASE france CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

USE france

Créez la table france_villes en utilisant la requête suivante :

CREATE TABLE IF NOT EXISTS `france_villes` (
  `ville_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `ville_departement` varchar(3) DEFAULT NULL,
  `ville_slug` varchar(255) DEFAULT NULL,
  `ville_nom` varchar(45) DEFAULT NULL,
  `ville_nom_simple` varchar(45) DEFAULT NULL,
  `ville_nom_reel` varchar(45) DEFAULT NULL,
  `ville_nom_soundex` varchar(20) DEFAULT NULL,
  `ville_nom_metaphone` varchar(22) DEFAULT NULL,
  `ville_code_postal` varchar(255) DEFAULT NULL,
  `ville_commune` varchar(3) DEFAULT NULL,
  `ville_code_commune` varchar(5) NOT NULL,
  `ville_arrondissement` smallint(3) unsigned DEFAULT NULL,
  `ville_canton` varchar(4) DEFAULT NULL,
  `ville_amdi` smallint(5) unsigned DEFAULT NULL,
  `ville_population_2010` mediumint(11) unsigned DEFAULT NULL,
  `ville_population_1999` mediumint(11) unsigned DEFAULT NULL,
  `ville_population_2012` mediumint(10) unsigned DEFAULT NULL COMMENT 'approximatif',
  `ville_densite_2010` int(11) DEFAULT NULL,
  `ville_surface` float DEFAULT NULL,
  `ville_longitude_deg` float DEFAULT NULL,
  `ville_latitude_deg` float DEFAULT NULL,
  `ville_longitude_grd` varchar(9) DEFAULT NULL,
  `ville_latitude_grd` varchar(8) DEFAULT NULL,
  `ville_longitude_dms` varchar(9) DEFAULT NULL,
  `ville_latitude_dms` varchar(8) DEFAULT NULL,
  `ville_zmin` mediumint(4) DEFAULT NULL,
  `ville_zmax` mediumint(4) DEFAULT NULL,
  PRIMARY KEY (`ville_id`),
  UNIQUE KEY `ville_code_commune_2` (`ville_code_commune`),
  UNIQUE KEY `ville_slug` (`ville_slug`),
  KEY `ville_departement` (`ville_departement`),
  KEY `ville_nom` (`ville_nom`),
  KEY `ville_nom_reel` (`ville_nom_reel`),
  KEY `ville_code_commune` (`ville_code_commune`),
  KEY `ville_code_postal` (`ville_code_postal`),
  KEY `ville_longitude_latitude_deg` (`ville_longitude_deg`,`ville_latitude_deg`),
  KEY `ville_nom_soundex` (`ville_nom_soundex`),
  KEY `ville_nom_metaphone` (`ville_nom_metaphone`),
  KEY `ville_population_2010` (`ville_population_2010`),
  KEY `ville_nom_simple` (`ville_nom_simple`)
) ENGINE=INNODB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=36831 ;

Détails des différents champs de la table

• Département : numéro du département.
• Slug : identifiant unique en minuscule, sans accent et sans espace. Peut servir pour faire les URLs d’un site web.
• Nom : nom en majuscule et sans accent.
• Nom simple : nom en minuscule, sans accent et avec les tirets remplacés par des espaces. Peut être utilisé pour faire une recherche lorsqu’on ne sait pas si le nom de ville possède un tiret ou des espaces (ex : “Saint-Étienne” possède un tiret comme séparateur, tandis que “Le Havre” possède un espace comme séparateur)
• Nom reel : nom correct avec les accents
• Nom soundex : soundex du nom de la ville (permet de trouver des villes qui se prononcent presque pareil) [ajouté le 31/10/2013]
• Nom metaphone : metaphone du nom de la ville (même utilité que le soundex) [ajouté le 31/10/2013]
• Code postal : code postal de la ville. Si la ville possède plusieurs code postaux, ils sont tous listés et séparés par un tiret [ajouté le 31/10/2013]
• Numéro de commune : numéro de la commune dans le département. Combiné avec le numéro de département, il permet de créer le code INSEE sous 5 caractères.
• Code commune (ou code INSEE) : identifiant unique sous 5 caractères
• Arrondissement : arrondissement de la ville
• Canton : canton de la ville
• Population en 2010 : nombre d’habitants lors du recensement de 2010
• Population en 1999 : nombre d’habitants lors du recensement de 1999
• Population en 2012 (approximatif) : valeur exprimée en centaine
• Densité en 2010 : nombre d’habitants au kilomètre carré arrondie à l’entier. Calculé à partir du nombre d’habitant et de la surface de la ville [corrigé le 02/07/2014]
• Surface / superficie : surface de la ville en kilomètre carrée [corrigé le 02/07/2014]
• Longitude/latitude en degré : géolocalisation du centre de la ville. Permet de localiser la ville sur une carte (exemple : carte Google Maps) [ajouté le 31/10/2013, corrigé le 07/11/2013]
• Longitude/latitude en GRD : géolocalisation exprimée en GRD
• Longitude/latitude en DMS (Degré Minute Seconde) : géolocalisation exprimée en Degré Minute Seconde
• Altitude minimale/maximale : hauteur minimum et maximum de la ville par rapport au niveau de l’eau

Source et licence des données :

La table et les enregistrement utilisés pour cet exercice sont tirés du site https://sql.sh et mis à disposition selon les termes de la licence Creative Commons Attribution – Partage dans les Mêmes Conditions 4.0 International.

Vous êtes libre de partager, distribuer ou utiliser ces données, pour des utilisations commerciales ou non, à condition de conserver cette licence et d’attribuer un lien vers le site sql.sh.

Importez les données dans la table en utilisant les fichiers disponibles ci-dessous.

Pour télécharger un fichier, faites un clic droit sur le fichier désiré et choisissez l’option Télécharger.
Pour télécharger plusieurs fichiers, sélectionnez les fichiers désirés puis cliquez sur Télécharger les sélectionnés. Les fichiers seront téléchargés sous la forme d’une archive .zip. Veillez à bien extraire le contenu de l’archive avant de commencer à travailler.

Rédigez et exécutez la requête permettant d’obtenir liste des villes sous la forme nom_reeldépartement.

SELECT CONCAT(ville_nom_reel, ville_departement) 
FROM france_villes

SELECT CONCAT(ville_nom_reel, ville_departement) AS ville 
FROM france_villes

Corrigez la requête précédente afin d’obtenir la liste des villes sous la forme nom_reel (département).

SELECT CONCAT(ville_nom_reel, ' (', ville_departement,')') AS ville 
FROM france_villes

Rédigez et exécutez la requête permettant d’obtenir le nom réel des villes ainsi que la longueur (en nombre de caractères du nom en question).

SELECT ville_nom_reel , LENGTH(ville_nom_reel) AS nb_car 
FROM france_villes

Rédigez et exécutez la requête permettant d’obtenir la liste des 10 villes de France dont le nom est le plus long.

SELECT ville_nom_reel , LENGTH(ville_nom_reel) AS nb_car 
FROM france_villes
ORDER BY LENGTH(ville_nom_reel) DESC
LIMIT 10

Rédigez et exécutez la requête permettant d’obtenir la liste des noms réels des villes ainsi que leur version complétement en minuscule et complétement en majuscule. Vous ne devez utiliser que la colonne ville_nom_reel pour votre requête.

SELECT ville_nom_reel , LOWER(ville_nom_reel) AS minuscule, UPPER(ville_nom_reel) AS majuscule 
FROM france_villes

Rédigez et exécutez la requête permettant d’obtenir le nombre d’habitants de la ville la plus peuplée de France en 2010.

SELECT MAX(ville_population_2010)
FROM france_villes

Rédigez et exécutez la requête permettant d’obtenir la liste des numéros de départements sans doublons.

SELECT DISTINCT ville_departement
FROM france_villes

Rédigez et exécutez la requête permettant d’obtenir la liste des départements accompagnés du nombre de villes par département.

SELECT ville_departement, COUNT(ville_id) AS villes
FROM france_villes
GROUP BY ville_departement

Complétez la requête précédente pour ajouter le nombre d’habitants total, le nom d’habitants moyen par ville, le nombre d’habitants maximal et le nombre d’habitants minimal.

SELECT ville_departement, 
       COUNT(ville_id) AS villes, 
       SUM(ville_population_2010) AS total, 
       AVG(ville_population_2010) AS moy, 
       MAX(ville_population_2010) AS max,
       MIN(ville_population_2010) AS min
FROM france_villes
GROUP BY ville_departement

Rédigez et exécutez la requête permettant d’obtenir la liste de toutes départements dont la ville la plus peuplée a un nombre d’habitants supérieur à 200000. Les résultats devront être classé par population décroissante.

SELECT ville_departement, MAX(ville_population_2010) AS max
FROM france_villes
GROUP BY ville_departement
HAVING MAX(ville_population_2010) > 200000
ORDER BY MAX(ville_population_2010) DESC