Exercices

Ch. 4 : Bases de données et SQL

Les exercices précédés de sont à résoudre par écrit (sur cahier) ; ceux précédés de sont à faire sur machine, en sauvegardant le fichier si nécessaire. Niveau de difficulté : facile, moyen, difficile.

Exercice 1 (Les services d’un SGBD)

Le ski-club utilise un SGBD pour gérer ses adhérents.

  1. Pour chacune des situations suivantes, indiquer quel service du SGBD est en jeu : persistance des données, gestion des accès concurrents, efficacité de traitement des requêtes ou sécurisation des accès.

    1. Deux conseillers modifient en même temps la fiche d’un même adhérent, sans que l’une des modifications écrase l’autre.
    2. Un stagiaire peut consulter la liste des adhérents, mais n’a pas le droit de la modifier.
    3. Après une coupure de courant générale, toutes les inscriptions de la veille sont retrouvées.
    4. Une recherche parmi les dix millions d’adhérents d’une fédération renvoie sa réponse en une fraction de seconde.

  2. Citer deux difficultés que poserait l’utilisation directe de fichiers CSV, partagés entre plusieurs utilisateurs, à la place d’un SGBD.

Exercice 2 (Films)

Voici un extrait d’une relation référençant des films :

id titre realisateur ann_sortie note_sur_10
1 Alien, le huitième passager Scott 1979 10
2 Dune Lynch 1985 5
3 2001 : l’odyssée de l’espace Kubrick 1968 9
4 Blade Runner Scott 1982 10
  1. Lister les différents attributs de cette relation. Donner le domaine de chaque attribut.
  2. Pour chaque attribut, dire si cet attribut peut jouer le rôle de clef primaire. N’oubliez pas de justifier vos réponses.
  3. Écrire la requête SQL donnant le titre des films réalisés par Scott.
  4. Écrire la requête SQL donnant le titre des films sortis après 1980, du mieux noté au moins bien noté.
  5. Quel est le résultat de la requête SELECT COUNT(*) FROM FILMS WHERE realisateur = 'Scott' ?

Exercice 3 (Anomalies d’un schéma)

Un autre club de ski stocke tout dans une seule relation ADHERENTS_STATIONS :

num_licence nom prenom annee_naissance station altitude_max
12558 Doe John 1988 Flaine 2510
13668 Vect Alice 1974 Avoriaz 2466
1777 Dect Bob 1967 Le grand Bornand 2050
13447 Beau Tristan 1999 La clusaz 2616
1141 Pabeau John 1975 Le grand Bornand 2050
  1. Quel attribut peut jouer le rôle de clef primaire ?
  2. Quelles informations sont enregistrées plusieurs fois (redondance) ?
  3. Le club veut enregistrer la nouvelle station de Morzine (2 460 m), que personne n’a encore choisie. Quel problème rencontre-t-il ? De quelle anomalie s’agit-il ?
  4. Tristan Beau quitte le club et sa ligne est supprimée. Qu’est-ce que le club perd involontairement ? De quelle anomalie s’agit-il ?
  5. L’altitude maximale du Grand Bornand est corrigée à 2 100 m. Combien de lignes faut-il modifier ? Quel risque court-on ? De quelle anomalie s’agit-il ?
  6. Proposer un schéma relationnel à deux relations qui évite ces anomalies : préciser les attributs, les clefs primaires et la clef étrangère.

Exercice 4 (Le ski-club)

Un ski-club utilise une base de données constituée de deux tables : une table ADHERENTS et une table STATIONS. Dans la table ADHERENTS on trouve un attribut ref_station qui permet de connaître la station de ski préférée de chaque adhérent.

Table ADHERENTS

num_licence nom prenom annee_naissance ref_station
12558 Doe John 1988 5
13668 Vect Alice 1974 6
1777 Dect Bob 1967 3
13447 Beau Tristan 1999 4
1141 Pabeau John 1975 3

Table STATIONS

ref nom altitude_max
3 Le grand Bornand 2050
4 La clusaz 2616
5 Flaine 2510
6 Avoriaz 2466

  1. Comment appelle-t-on l’attribut ref_station de la table ADHERENTS ?

  2. Écrire la requête SQL permettant d’obtenir le nom des stations ayant une altitude maxi strictement supérieure à 2 500 m.

  3. Écrire une requête SQL permettant d’obtenir le numéro de licence des adhérents nés après 1980 et ayant pour prénom John.

  4. Donner le résultat de la requête SQL suivante :

    SELECT nom
FROM ADHERENTS
WHERE num_licence > 13500 OR ref_station = 3

  5. Donner le résultat de la requête SQL suivante :

    SELECT STATIONS.nom
FROM STATIONS
INNER JOIN ADHERENTS ON ADHERENTS.ref_station = STATIONS.ref
WHERE annee_naissance > 1975

Exercice 5 (Mises à jour et contraintes d’intégrité)

On reprend les tables ADHERENTS et STATIONS de l’exercice précédent.

  1. Écrire la requête SQL qui ajoute l’adhérent n° 14002, Rey Léa, née en 2001, dont la station préférée est Avoriaz.

  2. Alice Vect préfère désormais Flaine. Écrire la requête SQL qui met à jour la table.

  3. Bob Dect quitte le club. Écrire la requête SQL qui supprime son enregistrement.

  4. Que se passerait-il si l’on oubliait la clause WHERE dans la requête de la question 2 ? Et dans celle de la question 3 ?

  5. Pour chacune des requêtes suivantes (indépendantes les unes des autres), dire si le SGBD l’accepte. Sinon, préciser quelle contrainte d’intégrité (domaine, relation ou référence) est violée.

    1. INSERT INTO ADHERENTS VALUES (12558, 'Moi', 'Sam', 2000, 4)
    2. INSERT INTO ADHERENTS VALUES (15000, 'Roc', 'Eva', 1990, 9)
    3. DELETE FROM STATIONS WHERE ref = 3
    4. INSERT INTO STATIONS VALUES (7, 'Morzine', 2460)
    5. INSERT INTO ADHERENTS VALUES (15001, 'Lin', 'Max', 'deux mille', 4)

Exercice 6 (Fonctions d’agrégation, tri et doublons)

On reprend les tables ADHERENTS et STATIONS. Écrire les requêtes SQL qui permettent d’obtenir :

  1. l’altitude maxi moyenne des stations ;
  2. l’altitude maxi la plus élevée parmi les stations ;
  3. le nombre d’adhérents nés avant 1980 ;
  4. le nombre d’adhérents dont la station préférée est Le grand Bornand ;
  5. la liste des références des stations préférées d’au moins un adhérent, sans doublon et dans l’ordre croissant ;
  6. le nom de la station dont l’altitude maxi est la plus élevée (on pourra utiliser une requête imbriquée).

Donner aussi le résultat de chaque requête.

Exercice 7 (Lire une requête)

Pour chacune des requêtes suivantes, sur les tables ADHERENTS et STATIONS : repérer les clauses (SELECT, FROM, JOIN … ON, WHERE, ORDER BY) et dire à quelle question, en français, répond la requête ; puis donner son résultat.

  1. SELECT COUNT(*)
FROM ADHERENTS INNER JOIN STATIONS ON ADHERENTS.ref_station = STATIONS.ref
WHERE STATIONS.nom = 'Flaine'
  2. SELECT DISTINCT prenom
FROM ADHERENTS
ORDER BY prenom DESC
  3. SELECT nom
FROM STATIONS
WHERE altitude_max = (SELECT MAX(altitude_max) FROM STATIONS)

Exercice 8 (Exercices tirés des annales)

  1. Métropole 2022 Jour 1 : Exercice 2.
  2. Métropole 2022 Jour 2 : Exercice 4.
  3. Amérique du Nord 2022 Jour 1 : Exercice 1.
  4. Amérique du Nord 2022 Jour 2 : Exercice 3.
  5. Centres étrangers 2022 Jour 1 : Exercice 4.
  6. Centres étrangers 2022 Jour 2 : Exercice 3.

Exercice 9 (Manipuler Cantal.db)

Ouvrir la base Cantal.db avec DB Browser (SQLite). Travailler sur une copie du fichier. Écrire et exécuter les requêtes SQL permettant d’obtenir :

  1. le nom des communes de plus de 1 000 habitants et de moins de 2 000 habitants, de la plus peuplée à la moins peuplée ;
  2. le nombre de lycées (établissements dont le nom commence par « Lycée ») du Cantal ;
  3. le nom des établissements privés avec le nom de leur commune, classés dans l’ordre alphabétique des communes ;
  4. la population totale des communes de code postal 15130 ;
  5. la liste des codes postaux du Cantal, sans doublon.

Puis tester les mises à jour, toujours sur la copie :

  1. ajouter un établissement fictif dans la commune de votre choix, modifier son statut, puis le supprimer ;
  2. essayer d’ajouter un établissement dont le code existe déjà, puis un établissement dont id_commune ne correspond à aucune commune. Que répond le SGBD ? Quelles contraintes d’intégrité sont en jeu ?

Exercice 10 (Le CNAM)

Le CNAM (Conservatoire National des Arts et Métiers) propose en ligne des travaux pratiques sur une base de données concernant les films de cinéma. À titre d’entraînement, ouvrir cet exerciseur (cliquer sur l’image ci-dessous) et essayer de formuler les requêtes correspondant aux suggestions de la colonne de droite. Les réponses sont disponibles sur le site, mais prenez le temps de chercher, d’essayer, et de vous corriger.

Problème

Serious game : meurtre à SQL City …

Pour les plus rapides

Serious game : SQL Island …

Et encore un autre :