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 :

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

   

   Une solution

   SELECT *
   FROM MessagesForum;
   

3. Écrire une requête permettant de consulter uniquement les messages qui traitent du sujet 5.

   Affichage à obtenir

   

   Une solution

   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

   

   Une solution

   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

   

   Une solution

   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

   

   Une solution

   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

   

   Une solution

   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

   

   Une solution

   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 :

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

   

   Une solution

   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

   

   Une solution

   SELECT name
   FROM Country
   WHERE capital IS NULL;
   

4. Déterminez la population mondiale.

   Affichage à obtenir

   

   Une solution

   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

   

   Une solution

   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

   

   Une solution

   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

   

   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

   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

   

   Une solution

   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 :

   Une solution

   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

    

    Une solution

    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 km².

    Affichage à obtenir

    

    Une solution

    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

    

    Une solution

    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;