Aller au contenu

Exercices pour s'entraîner

Les exercices ci-dessous ont pour but de vous familiariser avec les requêtes SQL, que ce soit des requêtes d'écriture ou de lecture.

Prenez l'habitude de revenir vous entraîner régulièrement avec ces exercices tout au long de l'année.

Exercice B03.21

Cet exercice a été adapté d'un travail réalisé par C. BLANCHER et M. CHARDET, (futurs) enseignants en informatique.

On considère une base de données pour un forum. Cette base de données est représentée par le diagramme suivant :

Forum

  1. Enregistrez cette base de données après l'avoir téléchargée en cliquant sur ce lien. Ouvrez ensuite cette base à l'aide du logiciel DB Browser.
    Les requêtes suivantes devront être écrites dans l'éditeur SQL de DB browser.

  2. Écrire une requête permettant de consulter l'intégralité de la table des messages.

    Affichage à obtenir

    Forum

    Une solution
    1
    2
    SELECT *
    FROM MessagesForum;
    
  3. Écrire une requête permettant de consulter uniquement les messages qui traitent du sujet 5.

    Affichage à obtenir

    Forum

    Une solution
    1
    2
    3
    SELECT *
    FROM MessagesForum
    WHERE id_sujet = 5;
    
  4. Écrire une requête permettant d'afficher le contenu, les date et heure de publication des messages de linkdu69

    Affichage à obtenir

    Forum

    Une solution
    1
    2
    3
    SELECT contenu, date_publication, heure_publication
    FROM MessagesForum
    WHERE nom_utilisateur = "linkdu69";
    
  5. Écrire une requête permettant d'afficher le contenu, les date et heure de publication des messages de linkdu69 qui traitent du sujet 5.

    Affichage à obtenir

    Forum

    Une solution
    1
    2
    3
    4
    SELECT contenu, date_publication, heure_publication
    FROM MessagesForum
    WHERE nom_utilisateur = "linkdu69"
          AND id_sujet = 5;
    
  6. Écrire une requête permettant d'afficher les messages qui traitent du sujet 5 par ordre décroissant d'heure.
    Ces messages sont-ils publiés du plus récent au plus ancien ?

    Affichage à obtenir

    Forum

    Une solution
    1
    2
    3
    4
    SELECT *
    FROM MessagesForum
    WHERE id_sujet = 5
    ORDER BY heure_publication DESC;
    
  7. Écrire une requête permettant d'afficher les messages qui traitent du sujet 5 du plus récent au plus ancien.

    Affichage à obtenir

    Forum

    Une solution
    1
    2
    3
    4
    SELECT *
    FROM MessagesForum
    WHERE id_sujet = 5
    ORDER BY date_publication DESC, heure_publication DESC;
    
  8. Renvoyer la liste précédente, chaque message étant complété par les informations de l'auteur de ce message.

    Affichage à obtenir

    Forum

    Une solution
    1
    2
    3
    4
    5
    SELECT *
    FROM MessagesForum JOIN Utilisateurs
        ON MessagesForum.nom_utilisateur = Utilisateurs.nom_utilisateur
    WHERE id_sujet = 5
    ORDER BY date_publication DESC, heure_publication DESC;
    

Exercice B03.22

On considère un extrait de la base de données géographiques disponible sur le site http://www.semwebtech.org.

Cette base de données contient de nombreuses tables de données géographiques.
Nous allons nous limiter aux trois tables suivantes :

Mondial

  1. Enregistrez cette base de données après l'avoir télécharger en cliquant sur ce lien.
    Ouvrez ensuite cette base à l'aide du logiciel DB Browser.
    Les requêtes suivantes devront être écrites dans l'éditeur SQL de DB browser.

  2. Déterminez le nombre de pays recensés dans la base.

    Affichage à obtenir

    Forum

    Une solution
    1
    2
    SELECT count(*) AS nb_pays
    FROM Country;
    
  3. Existe-t-il des pays n'ayant pas de capitale ? Si oui, donnez leur nom.

    Affichage à obtenir

    Forum

    Une solution
    4
    5
    6
    SELECT name
    FROM Country
    WHERE capital IS NULL;
    
  4. Déterminez la population mondiale.

    Affichage à obtenir

    Forum

    Une solution
    8
    9
    SELECT SUM(Population) AS pop_mondiale
    FROM Country;
    
  5. Déterminez la liste alphabétique des pays dont la population excède 60 000 000 d’habitants.

    Affichage à obtenir

    Forum

    Une solution
    11
    12
    13
    14
    SELECT name
    FROM Country
    WHERE Population > 60000000
    ORDER BY Name;
    
  6. Donnez la surface moyenne des pays obtenus avec la liste précédente.

    Affichage à obtenir

    Forum

    Une solution
    16
    17
    18
    SELECT AVG(Area) AS "Surface moyenne"
    FROM Country
    WHERE Population > 60000000;
    
  7. Donnez la liste complète des pays, triée par ordre décroissant de population, dont la surface est supérieure à la moyenne précédente.

    Affichage à obtenir

    Forum

    Une piste

    Vous pouvez copier/coller la requête précédente (entourée de parenthèses) à l'emplacement adéquat de votre requête actuelle.

    Une solution
    21
    22
    23
    24
    25
    26
    27
    SELECT *
    FROM Country
    WHERE Area > (
        SELECT AVG(Area) AS "Surface moyenne"
        FROM Country
        WHERE Population > 60000000)
    ORDER BY Population DESC;
    
  8. Déterminez les noms, surfaces et populations des 10 pays ayant la surface la plus petite.
    Utilisez le mot-clef LIMIT suivi du nombre de résultats désirés.

    Affichage à obtenir

    Forum

    Une solution
    22
    23
    24
    SELECT Name, Area, Population
    FROM Country
    ORDER BY Area ASC LIMIT 10;
    
  9. Dans cette base de donnée, les attributs de la table encompasses sont :

    • Country : le code du pays (de une à quatre lettres) ;
    • Continent : nom d'un continent ;
    • Percentage : la proportion de la surface du pays sur ce continent.

    Listez les noms des pays dont une partie de la surface est située en Europe, donnez leur proportion de surface sur le contient européen, les résultats étant triés par proportion croissante puis par ordre alphabétique inverse de nom.

    Affichage à obtenir

    Voici la liste des dix premiers : Forum

    Une solution
    33
    34
    35
    36
    37
    SELECT Country.Name, encompasses.Percentage
    FROM Country JOIN encompasses
        ON Country.code = encompasses.Country
    WHERE encompasses.Continent = 'Europe'
    ORDER BY encompasses.Percentage ASC, Country.Name DESC;
    
  10. Listez les noms des pays qui sont à cheval sur plusieurs continents.

    Affichage à obtenir

    Forum

    Une solution
    39
    40
    41
    42
    SELECT DISTINCT C.Name
    FROM Country AS C JOIN encompasses AS e
        ON C.Code = e.Country
    WHERE e.Percentage < 100;
    
  11. Listez les noms des pays du continent américain qui comptent moins de 10 habitants par km2.

    Affichage à obtenir

    Forum

    Une solution
    44
    45
    46
    47
    SELECT C.Name
    FROM Country AS C JOIN encompasses AS e
        ON C.Code = e.Country
    WHERE (e.Continent LIKE '%America%' AND C.Population/C.Area < 10);
    
  12. Déterminer les noms des capitales européennes, ainsi que les noms de leur pays, situées à une latitude supérieure à 60°.

    Affichage à obtenir

    Forum

    Une solution
    49
    50
    51
    52
    53
    54
    SELECT V.Name, C.name
    FROM City AS V JOIN Country AS C
        ON C.Code = V.Country
    JOIN encompasses AS E
        ON V.Country = e.Country
    WHERE e.Continent = 'Europe' AND V.Name = C.Capital AND Latitude > 60;