Requêtes SELECT avancées avec MySQL SQL03

Cet exercice consiste à effectuer des requêtes de sélection en utilisant des fonctions SQL afin de découvrir ou de tester les différentes possibilités offertes par le langage. Il s’inscrit dans la continuité de l’exercice Requêtes SELECT simples avec MySQL [SQL02]

Vous devez avoir une base de données (Maria DB ou MySQL) à votre disposition pour effectuer cet exercice. Vous pouvez traiter les différentes questions en ligne de commande ou via un gestionnaire de base de données comme phpMyAdmin, Adminer, MySQL Workbench, Sequel Pro, etc.

Si vous avez effectué l’exercice Requêtes SELECT simples avec MySQL [SQL02] avant de commencer celui-ci, vous pouvez sauter les étapes de création de remplissage de la base de données.

Connectez-vous à votre base de données.

Afficher la solution

Solution :

Vous pouvez vous connecter à votre base de données en utilisant un logiciel de gestion comme phpMyAdmin. qui vous permettra de saisir toutes les commandes demandées dans l’exercice.

Si vous souhaitez utiliser les lignes de commande, vous pouvez utiliser la commande suivante pour vous connecter en remplaçant localhost par l’adresse de votre base (si nécessaire) et root par votre identifiant.

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.

Afficher la solution

Solution :

Si vous a travaillez avec phpMyAdmin, il suffit d’utiliser les options d’interface pour créer la base et/ou l’ouvrir.

Voici la commande pour créer une base de données en ligne de commande :

CREATE DATABASE france CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

La commande suivante permet de sélectionner une base de données existante.

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.

Attention :

Si votre serveur le permet, utilisez le fichier france_cities.sql. Mais si les paramètres de votre serveur limite la taille d’import pour les gros fichiers (comme sur MAMP qui limite par défaut à 2Mo), utilisez alors les six fichiers france_cities_x.sql.

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

Requête 1
Aperçu partiel du résultat
Afficher un indice

Indice :

La fonction CONCAT() permet de réunir (de concaténer) les valeur de plusieurs colonnes pour créer une seule chaîne de caractères.

Afficher la solution

Solution :

SELECT CONCAT(ville_nom_reel, ville_departement) 
FROM france_villes

La commande facultative AS permet de définir un alias pour la chaîne de caractères obtenue par concaténation. Ce qui donne un libellé de colonne plus facilement exploitable par la suite.

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).

Requête 2
Aperçu partiel du résultat
Afficher un indice

Indice :

La fonction CONCAT() supporte la possibilité d’ajouter des chaînes de caractères entre guillemets.

Afficher la solution

Solution :

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).

Requête 3
Aperçu partiel du résultat
Afficher un indice

Indice :

La fonction LENGTH() permet de déterminer la longueur d’une chaîne de caractères.

Afficher la solution

Solution :

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.

Requête 4
Aperçu partiel du résultat
Afficher un indice

Indice :

La fonction LENGTH() peut également être utilisée pour ordonner les résultats.

Afficher la solution

Solution :

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.

Requête 5
Aperçu partiel du résultat
Afficher un indice

Indice :

Les fonctions LOWER et UPPER permettent de manipuler la casse d’une chaîne de caractères.

Afficher la solution

Solution :

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.

Requête 6
Aperçu partiel du résultat
Afficher un indice

Indice :

La fonction MAX() permet de déterminer la valeur maximale d’une colonne.

Afficher la solution

Solution :

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.

Requête 7
Aperçu partiel du résultat
Afficher un indice

Indice :

La commande DISTINCT permet d’éviter les doublons dans les résultats d’une requête.

Afficher la solution

Solution :

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.

Requête 8
Aperçu partiel du résultat
Afficher un indice

Indice :

La commande GROUP BY est utilisée en SQL pour réunir plusieurs résultats lorsque l’on utilise une fonction d’agrégation comme COUNT(), MAX(), AVG(), etc.

Afficher la solution

Solution :

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.

Requête 9
Aperçu partiel du résultat
Afficher la solution

Solution :

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.

Requête 10
Aperçu partiel du résultat
Afficher un indice

Indice :

La commande HAVING permet de créer une condition. Elle est identique à une clause WHERE mais elle s’applique aux groupes dans leur ensemble quand une clause WHERE s’applique aux lignes individuelles.

Afficher la solution

Solution :

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

Sauf mention contraire*, l'article Requêtes SELECT avancées avec MySQL [SQL03] et son contenu par Julien Crego sont mis à disposition selon les termes de la licence Creative Commons

Licence Creative Commons

Attribution - Pas d’Utilisation Commerciale - Partage dans les Mêmes Conditions 4.0 International

Cette licence vous permet de remixer, arranger, et adapter cette œuvre à des fins non commerciales tant que vous créditez la source en citant le nom des auteurs et que les nouvelles œuvres sont diffusées selon les mêmes conditions.

* Cette notice est intégrée automatiquement à la fin de chaque article de ce site.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.