Voici quelques exercices de requêtes SQL, avec leurs solutions

Exercice 1

On considère dans ce sujet la base de données prenoms.
Cette base ne comporte qu’une table, naissances, qui contient des informations décrivant des prénoms des enfants nés en France au XX-ième siècle.

Les attributs de cette table sont :

  • id : l’identifiant de l’entrée (clé primaire);
  • sexe : le sexe de l’enfant au format texte : Fille ou Garçon ;
  • prenom : le prénom de l’enfant en lettres majuscules ;
  • annee_naissance : l’année de naissance (nombre entier) ;
  • nombre : le nombre d’enfants nés durant l’année en question et portant ce prénom.

On rappelle que la structure générale d’une requête SQL est :
SELECT attribut_1, attribut_2, …
FROM table
WHERE condition;

Toutefois :

  • il est possible d’obtenir tous les attributs en faisant SELECT * ;
  • la condition WHERE condition n’est pas indispensable si l’on souhaite obtenir toutes les entrées de la table. On peut alors se contenter de SELECT attributs FROM table ;
  • il est possible de rajouter d’autres arguments qui seront présentés au fil de ce document.

Voici les requêtes à réaliser :

  • ex 1 : Afficher les dix premières lignes de la table. Astuce : Utiliser LIMIT 10 à la fin de la requête afin de n’afficher que les 10 premiers résultats. Il y a beaucoup de lignes dans cette table !

  • ex 2 : Afficher les lignes correspondant à l’année 1923. Là encore, mieux vaut ne demander que les 10 premières lignes…

  • ex 3 : Afficher les prénoms des filles nées en 1978.

  • ex 4 : Combien de fois le prénom Nicolas a-t-il été donné en 1907 ? Attention aux majuscules.

  • ex 5 : Afficher les 10 prénoms de fille les plus donnés en 1978 rangés dans l’ordre décroissant du nombre de fois où ils ont été donnés.

  • ex 6 : Afficher le nombre de prénoms différents de garçons donnés en 1938.

  • ex 7 : Afficher le nombre de naissances de garçons observées en 1938.

  • ex 8 : Afficher le nombre de filles et le nombre de garçons apparaissant dans la table.

  • ex 9 : Quel est le prénom, pour un certain sexe, en distinguant par exemple “Camille (fille)” et “Camille (garçon)”, qui a été le plus donné durant une année donnée ? En quelle année ?

  • ex 10 : En quelle année y-a-t-il eu le plus de naissances ?

  • ex 11 : Quels sont les 10 prénoms les plus donnés en France au cours du XX-ème siècle ?

  • ex 12 : Afficher les lignes des prénoms de garçons donnés entre 1960 et 1969 (inclus l’un et l’autre). Utiliser un AND pour tester l’encadrement des années.

Pour réaliser l’exercice, téléchargez le fichier prenoms.db (32 116 ko), installez Sqlite3 et éventuellement DBBrowser.

Le fichier à télécharger : prenoms.db

Solution de l’exercice 1

  • ex 1 :
    SELECT *
    FROM naissances
    LIMIT 10;

  • ex 2 :
    SELECT *
    FROM naissances
    WHERE annee_naissance = 1923
    LIMIT 10;

  • ex 3 :
    SELECT prenom
    FROM naissances
    WHERE sexe = ‘Fille’ AND annee_naissance = 1978;

  • ex 4 :
    SELECT sexe, nombre
    FROM naissances
    WHERE prenom = ‘NICOLAS’ AND annee_naissance = 1907;

  • ex 5 :
    SELECT prenom
    FROM naissances
    WHERE sexe = ‘Fille’ AND annee_naissance = 1978
    ORDER BY nombre DESC
    LIMIT 10;

  • ex 6 :
    SELECT COUNT(*) as differents
    FROM naissances
    WHERE sexe = ‘Garçon’ AND annee_naissance = 1938;
     Le as differents est un alias, il permet de renommer un attribut ou un résultat (ici le décompte total).

  • ex 7 :
    SELECT SUM(nombre)
    FROM naissances
    WHERE sexe = ‘Garçon’ AND annee_naissance = 1938;

  • ex 8 :
    SELECT sexe, SUM(nombre)
    FROM naissances
    GROUP BY sexe;

  • ex 9 :
    SELECT sexe, prenom, annee_naissance, nombre
    FROM naissances
    ORDER BY nombre DESC
    LIMIT 1;

  • ex 10 :
    SELECT annee_naissance, SUM(nombre) as somme
    FROM naissances
    GROUP BY annee_naissance
    ORDER BY somme DESC
    LIMIT 1;

  • ex 11 :
    SELECT prenom, SUM(nombre) as nb
    FROM naissances
    GROUP BY prenom
    ORDER BY nb DESC
    LIMIT 10;

  • ex 12 :
    SELECT *
    FROM naissances
    WHERE sexe = ‘Garçon’ AND annee_naissance >= 1960 AND annee_naissance <= 1969;

Exercice 2

Voici une base données (artistes.sqlite) qui contient plusieurs tables.
Pour vous aider, vous pouvez dessiner sur papier le schéma relationnel.
Vous pouvez réaliser ces requêtes en mode console ou en mode graphique avec DBBrowser.
Voici le fichier à télécharger : artistes.sqlite

Écrivez et testez les commandes SQL permettant d’afficher :

  1. la table complète des artistes (25 réponses) ;

  2. le nom et l’origine des artistes (25 réponses) ;

  3. le nom et l’époque des artistes italiens (4 réponses) ;

  4. la liste des œuvres estimées à plus de 1000 dollars (15 réponses) ;

  5. le nombre d’œuvres estimées à moins de 1000 dollars (la réponse est 15) ;

  6. le prix moyen des œuvres d’art (la réponse est 1565.6667 dollars) ;

  7. le nombre d’artistes dans chaque pays d’origine (9 réponses) ;

  8. le nombre d’artistes qui ne sont pas du XXième siècle (17)

  9. le nom des musées qui sont ouverts après 17 h (3 réponses)

  10. le nom des sculptures qui sont estimées plus de 2000 dollars (4)

Solution de l’exercice 2

  1. la table complète des artistes (25 réponses) ;
    SELECT *
    FROM Artiste ;

  2. le nom et l’origine des artistes (25 réponses) ;
    SELECT Nom,Origine
    FROM Artiste ;

  3. le nom et l’époque des artistes italiens (4 réponses) ;
    SELECT Nom,Epoque
    FROM Artiste
    WHERE Origine = ‘Italie’ :

  4. la liste des œuvres estimées à plus de 1000 dollars (15 réponses) ;
    SELECT Titre,Estimation
    FROM Œuvre
    WHERE Estimation > 1000 ;

  5. le nombre d’œuvres estimées à moins de 1000 dollars (la réponse est 15) ;
    SELECT COUNT(*)
    FROM Œuvre
    WHERE Estimation<1000 ;

  6. le prix moyen des œuvres d’art (la réponse est 1565.6667 dollars) ;
    SELECT AVG(Estimation)
    FROM Œuvre ;

  7. le nombre d’artistes dans chaque pays d’origine (9 réponses) ;
    SELECT Origine, COUNT(*)
    FROM Artiste
    GROUP BY Origine ;

  8. le nombre d’artistes qui ne sont pas du XXième siècle (17)
    SELECT COUNT(*)
    FROM Artiste
    WHERE Epoque <>20 ;

  9. le nom des musées qui sont ouverts après 17 h (3 réponses)
    SELECT Nom
    FROM Musee
    WHERE Fermeture > 17 ;

  10. le nom des sculptures qui sont estimées plus de 2000 dollars (4)
    SELECT titre
    FROM Œuvre
    WHERE Genre = ‘sculpture’ AND Estimation > 2000 ;