Table des matières

Activité : les Bases de données relationnelles

Présentation

Quand nous avons une grande quantité d'informations numériques à gérer, il est nécessaire de les organiser et d'utiliser un logiciel spécialisé, le Systèmes de Gestion de Bases de Données pour les utiliser.

Actuellement la grande majorité des SGBD utilise le modèle relationnel qui consiste :

Une petite histoire des bases de données :

Dans cette activité je vais interroger les données sur les villes du site http://sql.sh en utilisant le langage de requêtes SQL :

Il existe plusieurs logiciels de gestion de base de données. MySQL (racheté par la société Oracle), continue à être une base de données OpenSource sous le nom de projet MariaDB.

Les requêtes SQL

La projection

La projection consiste à choisir les informations, en utilisant la clause select suivie les champs à afficher :

SELECT *
FROM icn_ville ;

Après la clause select, j'indique :

  • les colonnes à afficher séparées par des virgules ,
  • ou bien je mets le caractère * pour visualiser le contenu de toutes les colonnes.

Puis j'indique avec la clause from la table dans laquelle se trouve les données.

Question 1 : Ecrire la requête SQL pour avoir le nom, le code postal et le numéro de département (dans cet ordre) des villes.

Résultat à obtenir :

nom cp dep
Ozan 1190 1
Cormoranche-sur-Saône 12901

SELECT concat(nom, ' ', cp) AS adresse
FROM icn_ville ;

Résultat :

adresse
nom cp
Ozan 1190
Cormoranche-sur-Saône 1290

Question 2 : Ecrire la requête SQL qui indique pour chaque ville l'augmentation de la population entre 2010 et 2012. Résultat à obtenir :

ville augmentation
Ozan -118
Cormoranche-sur-Saône -58
Plagne -29
Tossiat -6
Pouillat 12

Eviter des résultats en double

Des requêtes peuvent renvoyer des résultats identiques et il est parfois utile d'évier cela avec le mot clé distinct.

SELECT dep AS Département 
FROM icn_ville ;

Résultat :

Département
1
1
1

… soit 36 701 lignes.

SELECT DISTINCT dep AS Département 
FROM icn_ville ;

Résultat :

Département
1
2
3

… soit 103 lignes.

Question 3 : Ecrire la requête SQL qui donne la liste des codes postaux. La requête SQL doit renvoyer uniquement 6 083 lignes.

Trier les résultats obtenus

Les requêtes SQL renvoient en général les données dans l'ordre où elles sont disponibles dans la base de données. Pour obtenir un ordre de tri différent on utilise les mots clés order by suivi des colonnes à trier en ascendant, par défaut (asc) ou en descendant (desc).

SELECT nom AS Ville
FROM icn_ville
ORDER BY nom ASC ;

Résultat :

Ville
Aast
Abainville
Abancourt

… soit 36 701 lignes.

Question 4 : Ecrire la requête SQL qui donne la liste des villes selon le nom d'habitants par ordre décroissant (indiquer la ville la plus peuplée premier)

La sélection

J'utilise la sélection si je ne souhaite avoir des données qui réponde à une condition en utilisant la clause where :

SELECT * 
FROM icn_ville
WHERE nom = 'panazol';

Résultat : 1 ville(s) trouvée(s) !

dep nom cp nb_hab_2010 nb_hab_1999 nb_hab_2012 dens surf longitude latitude alt_min alt_max
87 Panazol 87350 10392 9727 10100 518 20 1.3 45.8333 215 351

Voici les opérateurs utilisables :

Les opérateurs de comparaison et logiques

Opérateur de comparaisonDescriptionOpérateurs logiquesDescription
=égal àandles deux conditions doivent être vérifiées simultanément
<inférieur àorau moins une des deux conditions doit être vérifiée
>supérieur à
inférieur ou égal
>=supérieur ou égal
<>différent de

Question 5 : Ecrire la requête SQL qui donne la liste des villes qui ont plus de 5000 habitants en 2012. La requête SQL doit renvoyer uniquement 2 007 lignes.

Les opérateur d’appartenance (ou non) d’un élément à un intervalle

BETWEENappartenance
NOT BETWEENnon appartenance

* Exemple : connaître les villes dont l'altitude maximale est comprise entre 200 et 300 m :

SELECT nom AS ville, alt_max  
FROM icn_ville
WHERE alt_max  BETWEEN 200 AND 300;

Résultat :

ville alt_max
Ozan 205
Cormoranche-sur-Saône 211
Replonges 207

Question 6 : Ecrire la requête SQL qui donne la liste des villes dont la superficie est comprise entre 100 et 300 kilomètres carré. La requête SQL doit renvoyer uniquement 144 lignes.

Les opérateur de comparaison de chaînes de caracatères

LIKEcomparaison de chaînes (identiques)
NOT LIKEchaîne différente

% permet de remplacer n caractères _ permet de remplacer 1 caractère

* Exemple : connaître les villes dont le nom commence par Limoges :

SELECT nom AS ville
FROM icn_ville
WHERE nom LIKE 'limoges%';

Résultat :

ville
Limoges-Fourches
Limoges

Question 7 : Ecrire la requête SQL qui donne la liste des villes dont le nom contient les caractères paris. La requête SQL doit renvoyer uniquement 10 lignes.

Les activités ...