Correction des questions sur MySQL

Première série

1) Trouver le disque de 2005 intitulé "souvenir de florence".

Il n'y a aucun disque correspondant :

SELECT * FROM Disques
  WHERE titre = 'souvenir de florence'
    AND date >= '2005-01-01' AND date < '2006-01-01'

Des variantes possibles pour la partie date :

  ... AND date LIKE '2005-%'
  ... AND YEAR(date) = 2005

2) Lister les titres des disques, triés par date. Les trier par label, et par date pour un même label.

SELECT titre FROM Disques ORDER BY date DESC
SELECT titre FROM Disques ORDER BY label, date DESC

3) Quelle différence entre `SELECT intitule, id_genre FROM Genres` et `SELECT * FROM Genres` ?

Dans ce cas l'ordre des colonnes reçues est différent.

Si la base évolue par la suite (ajout de nouvelles colonnes, etc) la première requête ne changera pas, au contraire de la seconde.

4) Afficher tous les titres de 2006. Afficher les 3 titres les plus récents.

SELECT titre, date FROM Disques WHERE date LIKE '2006-%' -- cf syntaxes de 1)
SELECT titre, date FROM Disques ORDER BY date DESC LIMIT 3

5) Que donne `SELECT count(*) FROM Disques` ? Quelle différence avec `SELECT count(Disques.id_disque) FROM Disques` ?

count(*) compte le combre de lignes.

Pas de différence dans ce cas. Si certaines valeurs de id_disque étaient vides (NULL), alors leurs lignes ne seraient pas comptées.

6) Quelles sont les différentes relations entre artistes et disques ?

SELECT DISTINCT typeRelation FROM Disques_Artistes

7) Combien de compositeurs y a-t-il ?

SELECT count(DISTINCT id_artiste)
  FROM Disques_Artistes
  WHERE typeRelation = 'compositeur'

Sans le DISTINCT, on compterait toutes les compositions (si un artiste avait composé 10 disques, il serait comptabilisé 10 fois).

8) Quels disques ont été publiés par Harmonia Mundi ?

Ce qui a été vu ne nous permet pas de répondre en une seule requête.

SELECT id_label FROM Label WHERE nom = 'Harmonia Mundi'
-- on reçoit 2
SELECT * FROM Disques WHERE id_label = 2

Dans ce cas, le plus simple serait d'utiliser une sous-requête :

SELECT * FROM Disques
  WHERE id_label = (SELECT id_label FROM Label WHERE nom = 'Harmonia Mundi')

Mais le plus souvent en MySQL, on préfère les jointures aux sous-requêtes.

Seconde série (jointures)

1) Afficher tous les titres avec leur label associé.

SELECT titre, nom
  FROM Disques JOIN Labels ON Disques.id_label = Labels.id_label

Ou la même chose en plus lisible :

SELECT Disques.titre, Labels.nom
  FROM Disques JOIN Labels USING (id_label)

2) Y a-t-il un disque de EMI intitulé "... Figaro" ?

Les tables nécessaires sont Labels (pour "EMI") et Disques pour le titre. Il suffit de reprendre la requête précédente en ajoutant un filtre par WHERE.

SELECT Disques.titre, Labels.nom
  FROM Disques JOIN Labels USING (id_label)
  WHERE Labels.nom = 'EMI' AND Disques.titre LIKE '% Figaro'

Les alias permettent d'abréger la requête pour ne pas taper les noms complets des tables :

SELECT d.titre, l.nom
  FROM Disques AS d JOIN Labels AS l USING (id_label)
  WHERE l.nom = 'EMI' AND d.titre LIKE '% Figaro'

3) Quels sont les disques de genre "Baroque" ?

Il nous faut donc réunir les tables Disques et Genres. Mais la relation est "n:m", donc elle utilise une table de liaison. Comme toujours, il faut procéder par étapes :

-- Première jointure
SELECT *
  FROM Disques AS d
    JOIN Disques_Genres AS dg USING (id_disque)
-- On adjoint une nouvelle table au résultat précédent
SELECT *
  FROM Disques AS d
    JOIN Disques_Genres AS dg USING (id_disque)
    JOIN Genres AS g USING (id_genre)
-- On filtre les lignes
SELECT *
  FROM Disques AS d
    JOIN Disques_Genres AS dg USING (id_disque)
    JOIN Genres AS g USING (id_genre)
  WHERE g.intitule = 'Baroque'
-- On conserve uniquement les colonnes intéressantes
SELECT d.titre, d.date
  FROM Disques AS d
    JOIN Disques_Genres AS dg USING (id_disque)
    JOIN Genres AS g USING (id_genre)
  WHERE g.intitule = 'Baroque'

4) Lister les interprètes ayant contribué à au moins un disque.

Un interprète sera décrit dans la table Artistes, et la notion de "compositeur" est dans la table Disques_Artistes. Il faudra donc joindre ces 2 tables.

-- On commence par une jointure simple
SELECT *
  FROM Artistes AS a
    JOIN Disques_Artistes AS da USING (id_artiste)
-- On filtre les lignes (WHERE) puis les colonnes
SELECT DISTINCT a.nom, a.prenom
  FROM Artistes AS a
    JOIN Disques_Artistes AS da USING (id_artiste)
  WHERE typeRelation = 'interprete'
  ORDER BY nom ASC, prenom ASC

5) Est-ce que Phillips a déja produit de l'opéra ?

Comme toujours, on utilise le schéma de la base de données pour construire la requête SQL pas à pas.

SELECT d.titre, d.date, l.nom
  FROM Labels
    JOIN Disques USING (id_label)
    JOIN Disques_Genres AS dg USING (id_disque)
    JOIN Genres AS g USING (id_genre)
  WHERE g.intitule = 'opéra' AND l.nom = 'Phillips'

6) Afficher tous les titres avec leur compositeur associé. Avec leurs artistes associés ?

SELECT d.titre, a.nom, a.prenom
  FROM Disques AS d
    JOIN Disques_Artistes AS da USING (id_disque)
    JOIN Artistes AS a USING (id_artiste)
  WHERE typeRelation = 'compositeur'
  ORDER BY d.titre ASC

Pour les artistes, il faut aussi afficher la relation :

SELECT d.titre, da.typeRelation, a.nom, a.prenom
  FROM Disques AS d
    JOIN Disques_Artistes AS da USING (id_disque)
    JOIN Artistes AS a USING (id_artiste)
  ORDER BY d.titre ASC

Si un disque a plusieurs artistes, alors il sera dans plusieurs lignes du résultat. Pour éviter cela, il faudrait utiliser la syntaxe GROUP BY.

Exemple d'utilisation de GROUP BY pour compter le nombre d'artistes par disque :

SELECT d.titre, count(DISTINCT id_artiste)
  FROM Disques AS d
    JOIN Disques_Artistes AS da USING (id_disque)

Conclusion

Pour construire une requête SQL de type SELECT, le mode opératoire est souvent celui-ci :

Repérer sur le schéma les champs nécessaires et leurs tables.
Construire un SELECT * sur la jointure de ces tables en utilisant les liaisons indiquées sur le schéma.
Ne garder que les lignes intéressantes, grâce à un WHERE.
Ne garder que les colonnes intéressantes au lieu de *.
Compléter par un ORDER BY et éventuellement un LIMIT.