SQL - Joindre plusieurs tables

SQL - Joindre plusieurs tables#

Schéma relationnel

Utilisation de JOIN ... ON ...#

Les requêtes SELECT vues jusqu'ici ont permis de rechercher des informations dans une seule table à la fois. Toutefois, certaines requêtes demandent des informations s'étalant sur plusieurs tables à la fois. Par exemple, si on souhaite rechercher tous les titres de livres qu'un certain utilisateur a emprunté, les trois tables devront être mises à contribution.

Pour joindre deux tables entre elles, on commence par sélectionner la 1ère avec le FROM comme d'habitude. Puis, juste après ce FROM, on ajoute le mot-clef JOIN suivi du nom de la seconde table nécessaire. Ce JOIN doit ensuite être complété par le mot-clef ON et une égalité indiquant la logique reliant ces deux tables. Dans l'exemple de la bibliothèque, un emprunt est lié à un utilisateur grâce au id_utilisateur référencé dans les emprunts. Ainsi, la requête ci-dessous permet de coller chaque utilisateur à ses emprunts.

Lorsqu'on utilise plusieurs tables en même temps dans une requête, il est parfois nécessaire de faire précéder le nom d'une colonne par sa table pour éviter les ambiguités (par exemple Utilisateur.id_utilisateur plutôt que juste id_utilisateur).

1SELECT *
2FROM Utilisateur
3JOIN Emprunt ON Emprunt.utilisateur = Utilisateur.id_utilisateur

On peut utiliser autant de JOIN que souhaité pour coller plusieurs tables ensemble. La requête suivante nous permet de lier chaque livre à chaque utilisateur l'ayant emprunté.

1SELECT *
2FROM Utilisateur
3JOIN Emprunt ON Emprunt.utilisateur = Utilisateur.id_utilisateur
4JOIN Livre ON Emprunt.livre = Livre.numero_isbn

Cette requête peut être simplement complétée par un WHERE et affinée après le SELECT pour trouver tous les noms de livres empruntés par l'utilisateur dont le prénom est Catherine.

1SELECT Livre.titre
2FROM Utilisateur
3JOIN Emprunt ON Emprunt.utilisateur = Utilisateur.id_utilisateur
4JOIN Livre ON Emprunt.livre = Livre.numero_isbn
5WHERE Utilisateur.prenom = 'Catherine'

Exercices#

Exercice 22#

Pour chacune des jointures ci-dessous, choisissez la condition ON correcte.

  1. Pour relier la table Emprunt à la table Livre de la bibliothèque :
    SELECT * FROM Emprunt JOIN Livre ON

  2. Pour relier la table Seance à la table Salle d'un cinéma :
    SELECT * FROM Seance JOIN Salle ON

Solution

Une condition de JOIN met toujours en relation la clef étrangère d'une table avec la clef primaire de l'autre. C'est exactement ce que représente la flèche du schéma relationnel.

Les autres propositions échouent car elles comparent deux clefs primaires entre elles, comparent des colonnes qui n'ont aucun rapport, ou comparent une clef étrangère à une colonne qui n'est pas celle qui est référencée.

Exercice 23#

Les requêtes ci-dessous portent sur la base de données de la bibliothèque, mais il leur manque les conditions des JOIN. Complétez les ______.

  1. Afficher les titres des livres empruntés par l'utilisateur dont le nom est Petit.

1SELECT Livre.titre
2FROM Utilisateur
3JOIN Emprunt ON ______ = ______
4JOIN Livre ON ______ = ______
5WHERE Utilisateur.nom = 'Petit'

Solution

SELECT Livre.titre
FROM Utilisateur
JOIN Emprunt ON Emprunt.utilisateur = Utilisateur.id_utilisateur
JOIN Livre ON Emprunt.livre = Livre.numero_isbn
WHERE Utilisateur.nom = 'Petit'

  1. Afficher le nom et le prénom de l'utilisateur qui a emprunté 1984.

1SELECT Utilisateur.nom, Utilisateur.prenom
2FROM Livre
3JOIN Emprunt ON ______ = ______
4JOIN Utilisateur ON ______ = ______
5WHERE Livre.titre = '1984'

Solution

SELECT Utilisateur.nom, Utilisateur.prenom
FROM Livre
JOIN Emprunt ON Emprunt.livre = Livre.numero_isbn
JOIN Utilisateur ON Emprunt.utilisateur = Utilisateur.id_utilisateur
WHERE Livre.titre = '1984'

Remarquez que les deux conditions sont exactement les mêmes qu'à la question 1 : peu importe par quelle table on commence, ce sont toujours les mêmes flèches du schéma que l'on suit.

  1. Afficher la date et le titre de tous les emprunts faits par des enseignants.

1SELECT ______, ______
2FROM Utilisateur
3JOIN Emprunt ON ______ = ______
4JOIN Livre ON ______ = ______
5WHERE ______

Solution

SELECT Emprunt.date_emprunt, Livre.titre
FROM Utilisateur
JOIN Emprunt ON Emprunt.utilisateur = Utilisateur.id_utilisateur
JOIN Livre ON Emprunt.livre = Livre.numero_isbn
WHERE Utilisateur.role = 'enseignant'

Cette requête retourne 4 emprunts.

Exercice 24#

Les deux exercices suivants portent sur la base de données d'un cinéma, dont voici le schéma relationnel.

Schéma relationnel de la base de données du cinéma

Voici son contenu.

Sans écrire aucune requête, indiquez combien de JOIN seraient nécessaires pour répondre à chacune des questions ci-dessous. Aidez-vous du schéma : comptez le nombre de flèches à parcourir pour aller de la table de départ à la table d'arrivée.

  1. Quelles séances coûtent plus de 16 CHF ?

  2. Quel film est projeté à 10h30 ?

  3. Combien de places compte la salle Rouge ?

  4. Dans quelle salle Parasite est-il projeté à 21h00 ?

  5. Quel réalisateur est projeté à 22h00 ?

  6. Quel est le genre du film projeté dans la salle Verte à 14h00 ?

Solution

Les questions 1 et 3 ne concernent qu'une seule table (Seance pour l'une, Salle pour l'autre) : aucun JOIN n'est nécessaire.

Les questions 2 et 5 partent d'une séance et vont chercher une information dans Film : une seule flèche est parcourue, donc un seul JOIN.

Les questions 4 et 6 relient Film et Salle, qui ne sont pas reliées directement. Il faut passer par la table Seance au milieu, donc parcourir deux flèches et écrire deux JOIN.

Exercice 25#

Écrivez maintenant les requêtes répondant aux questions ci-dessous, en n'utilisant qu'une seule requête par question. Les heures sont enregistrées comme des nombres : 10h30 s'écrit 1030 et 21h00 s'écrit 2100.

Note

Pour les questions qui demandent le plus ou le moins de quelque chose, triez le résultat avec un ORDER BY et lisez la première ligne affichée.

  1. Quel film est projeté à 10h30 ? 

1

Solution

SELECT Film.titre FROM Film
JOIN Seance ON Seance.film = Film.id_film
WHERE Seance.heure = 1030

  1. Dans quelle salle Parasite est-il projeté à 21h00 ? 

1

Solution

SELECT Salle.nom FROM Salle
JOIN Seance ON Seance.salle = Salle.id_salle
JOIN Film ON Seance.film = Film.id_film
WHERE Film.titre = 'Parasite' AND Seance.heure = 2100

  1. Combien de places compte la salle où Interstellar est projeté à 20h00 ? 

1

Solution

SELECT Salle.nb_places FROM Salle
JOIN Seance ON Seance.salle = Salle.id_salle
JOIN Film ON Seance.film = Film.id_film
WHERE Film.titre = 'Interstellar' AND Seance.heure = 2000

  1. Quel réalisateur est projeté à 22h00 ? 

1

Solution

SELECT Film.realisateur FROM Film
JOIN Seance ON Seance.film = Film.id_film
WHERE Seance.heure = 2200

  1. Dans quelle salle peut-on voir un film de Christopher Nolan à 11h00 ? 

1

Solution

SELECT Salle.nom FROM Salle
JOIN Seance ON Seance.salle = Salle.id_salle
JOIN Film ON Seance.film = Film.id_film
WHERE Film.realisateur = 'Christopher Nolan' AND Seance.heure = 1100

  1. Quel est le genre du film projeté dans la salle Verte à 14h00 ? 

1

Solution

SELECT Film.genre FROM Film
JOIN Seance ON Seance.film = Film.id_film
JOIN Salle ON Seance.salle = Salle.id_salle
WHERE Salle.nom = 'Verte' AND Seance.heure = 1400

  1. Quel est le nom de la salle qui accueille la séance la moins chère ? 

1

Solution

SELECT Salle.nom FROM Salle
JOIN Seance ON Seance.salle = Salle.id_salle
ORDER BY Seance.prix ASC

  1. Quel est le film le plus long projeté dans la salle Rouge ? 

1

Solution

SELECT Film.titre FROM Film
JOIN Seance ON Seance.film = Film.id_film
JOIN Salle ON Seance.salle = Salle.id_salle
WHERE Salle.nom = 'Rouge'
ORDER BY Film.duree_min DESC

Exercice 26#

Dans cet exercice, nous considérons la base de données d'ISA avec le schéma relationnel ci-dessous.

Schéma relationnel d'ISA

Répondez aux questions ci-dessous en n'utilisant qu'une seule requête SQLite par question. Vérifiez à chaque fois votre réponse en l'entrant dans l'encadré à côté.

Note

Pour les questions qui demandent le plus ou le moins de quelque chose, triez le résultat avec un ORDER BY et lisez la première ligne affichée.

  1. Pour s'échauffer sans utiliser encore de JOIN, trouvez quelle branche est donnée en salle P110 à 13h30. (Attention le format d'heure est tel que 16h30 = 1630, 9h00 = 900) 

1

Solution

SELECT branche FROM Cours WHERE salle = 'P110' AND heure_debut = 1330

  1. Quel est le nom de famille du MCL (Maître De Classe) de la 1F2 ? Pour cette requête et les suivantes, vous aurez besoin d'un JOIN. 

1

Solution

SELECT Enseignant.nom FROM Enseignant JOIN Classe ON id_enseignant = mcl
WHERE Classe.nom = '1F2'

  1. Quel est le nom de la classe ayant comme MCL un enseignant dont le prénom est Antoine. 

1

Solution

SELECT Classe.nom FROM Classe
JOIN Enseignant ON Enseignant.id_enseignant = Classe.mcl
WHERE Enseignant.prenom = 'Antoine'

  1. Quelle branche donne Laura Conti ? 

1

Solution

SELECT branche FROM Cours 
JOIN Enseignant ON Enseignant.id_enseignant = Cours.enseignant
WHERE Enseignant.nom = 'Conti' AND Enseignant.prenom = 'Laura'

  1. Dans quelle classe se trouve Eva Savary ? 

1

Solution

SELECT Classe.nom
FROM Classe
JOIN Eleve ON Eleve.classe = Classe.id_classe
WHERE Eleve.nom = 'Savary' AND Eleve.prenom = 'Eva'

  1. Quel est le nom de famille de l'enseignant.e de Français travaillant avec le plus petit taux ?

1

Solution

SELECT Enseignant.nom
FROM Enseignant
JOIN Cours ON Cours.enseignant = Enseignant.id_enseignant
WHERE branche = 'Français'
ORDER BY taux_travail ASC

  1. Quelle classe a cours de Physique en salle E220 ?

1

Solution

SELECT Classe.nom
FROM Classe
JOIN Cours ON Classe.id_classe = Cours.classe
WHERE branche = 'Physique' AND salle = 'E220'

  1. Quelle est la branche qui finit le plus tard pour la classe 2F2 ?

1

Solution

SELECT branche
FROM Cours
JOIN Classe ON Classe.id_classe = Cours.Classe
WHERE Classe.nom = '2F2'
ORDER BY heure_fin DESC

Exercice 27#

Cet exercice utilise à nouveau la base de données d'ISA

Schéma relationnel d'ISA

Répondez aux questions ci-dessous en n'utilisant qu'une seule requête SQLite par question. Dans cet exercice, chaque question requiert entre 2 et 3 JOIN par requête.

  1. Quel est le nom de famille du MCL de Sara Ngoy ? 

1

Solution

SELECT Enseignant.nom
FROM Enseignant
JOIN Classe ON Enseignant.id_enseignant = Classe.mcl
JOIN Eleve ON Eleve.classe = Classe.id_classe
WHERE Eleve.prenom = 'Sara' AND Eleve.nom = 'Ngoy'

  1. Dans quelle salle à lieu le cours de Français de la classe de l'élève Emma Durand? 

1

Solution

select salle
from cours
join classe on cours.classe=id_classe
join eleve on eleve.classe=id_classe
where eleve.nom='Durand' and eleve.prenom='Emma' and branche='Français'

  1. Quelle branche que suit l’élève Clara Zaugg finit le plus tard ? 

1

Solution

SELECT Cours.branche FROM Eleve
JOIN Classe ON Eleve.classe = Classe.id_classe
JOIN Cours ON Cours.classe = Classe.id_classe
WHERE Eleve.nom = 'Zaugg' AND Eleve.prenom = 'Clara'
ORDER BY Cours.heure_fin DESC

  1. Quel enseignant donne quel cours à Elio de la 3BP1 dans la Salle E205 ? Pour contrôler votre réponse, écrivez le prénom et nom de l'enseignant suivi directement de la branche 

1

Solution

Select Enseignant.prenom, Enseignant.nom, Cours.branche from Eleve
Join Cours on Eleve.classe = Cours.classe
Join Enseignant on Cours.enseignant = id_enseignant
Join Classe on Eleve.classe = id_classe
Where Eleve.prenom = 'Elio' and Cours.salle = 'E205' and Classe.nom = '3BP1'