SQL - Joindre plusieurs tables

Contenu

PRAGMA foreign_keys = ON;

CREATE TABLE Livre (
    titre TEXT,
    auteur TEXT,
    date_pub DATE,
    numero_isbn INTEGER,
    prix REAL,
    PRIMARY KEY(numero_isbn)
);

CREATE TABLE Utilisateur (
    nom TEXT,
    prenom TEXT,
    role TEXT,
    id_utilisateur INTEGER,
    PRIMARY KEY(id_utilisateur AUTOINCREMENT)
);

CREATE TABLE Emprunt (
    livre INTEGER,
    utilisateur INTEGER,
    date_emprunt DATE,
    id_emprunt INTEGER,
    PRIMARY KEY(id_emprunt AUTOINCREMENT),
    FOREIGN KEY(utilisateur) REFERENCES Utilisateur(id_utilisateur),
    FOREIGN KEY(livre) REFERENCES Livre(numero_isbn)
);

INSERT INTO Livre(titre, auteur, date_pub, numero_isbn, prix) VALUES
('1984', 'George Orwell', '1949-06-08', 9780451524935, 9.99);

INSERT INTO Livre(titre, auteur, date_pub, numero_isbn, prix) VALUES
('Le Petit Prince', 'Antoine de Saint-Exupéry', '1943-04-06', 9782070612758, 7.50);

INSERT INTO Livre(titre, auteur, date_pub, numero_isbn, prix) VALUES
('Les Misérables', 'Victor Hugo', '1862-03-30', 9782253004220, 12.90);

INSERT INTO Livre(titre, auteur, date_pub, numero_isbn, prix) VALUES
('L`Étranger', 'Albert Camus', '1942-05-19', 9782070360024, 8.70);

INSERT INTO Livre(titre, auteur, date_pub, numero_isbn, prix) VALUES
('Le Comte de Monte-Cristo', 'Alexandre Dumas', '1844-08-28', 9782070105618, 14.99);

INSERT INTO Livre(titre, auteur, date_pub, numero_isbn, prix) VALUES
('Les Trois Mousquetaires', 'Alexandre Dumas', '1846-03-15', 9782070405732, 12.99);

INSERT INTO Livre(titre, auteur, date_pub, numero_isbn, prix) VALUES
('Madame Bovary', 'Gustave Flaubert', '1857-04-01', 9782070360604, 10.50);

INSERT INTO Livre(titre, auteur, date_pub, numero_isbn, prix) VALUES
('Don Quichotte', 'Miguel de Cervantes', '1605-01-16', 9782070117153, 15.80);

INSERT INTO Livre(titre, auteur, date_pub, numero_isbn, prix) VALUES
('Crime et Châtiment', 'Fiodor Dostoïevski', '1866-11-01', 9782070360405, 11.90);

INSERT INTO Livre(titre, auteur, date_pub, numero_isbn, prix) VALUES
('Orgueil et Préjugés', 'Jane Austen', '1813-01-28', 9782070318746, 9.50);

INSERT INTO Livre(titre, auteur, date_pub, numero_isbn, prix) VALUES
('Germinal', 'Émile Zola', '1885-03-01', 9782070443943, 10.99);

INSERT INTO Livre(titre, auteur, date_pub, numero_isbn, prix) VALUES
('Les Fleurs du mal', 'Charles Baudelaire', '1857-06-25', 9782070413113, 8.20);

INSERT INTO Livre(titre, auteur, date_pub, numero_isbn, prix) VALUES
('L`Odyssée', 'Homère', '0800-01-01', 9782080700241, 13.50);

INSERT INTO Livre(titre, auteur, date_pub, numero_isbn, prix) VALUES
('La Divine Comédie', 'Dante Alighieri', '1320-09-14', 9782253084079, 16.40);

INSERT INTO Livre(titre, auteur, date_pub, numero_isbn, prix) VALUES
('Ulysse', 'James Joyce', '1922-02-02', 9782253943635, 17.99);

INSERT INTO Livre(titre, auteur, date_pub, numero_isbn, prix) VALUES
('Moby Dick', 'Herman Melville', '1851-10-18', 9782070408485, 12.00);

INSERT INTO Livre(titre, auteur, date_pub, numero_isbn, prix) VALUES
('Le Nom de la Rose', 'Umberto Eco', '1980-09-01', 9782070388824, 11.50);

INSERT INTO Livre(titre, auteur, date_pub, numero_isbn, prix) VALUES
('À la recherche du temps perdu', 'Marcel Proust', '1913-11-14', 9782070107586, 22.90);

INSERT INTO Livre(titre, auteur, date_pub, numero_isbn, prix) VALUES
('Le Seigneur des Anneaux', 'J.R.R. Tolkien', '1954-07-29', 9782266154115, 29.90);

INSERT INTO Livre(titre, auteur, date_pub, numero_isbn, prix) VALUES
('Harry Potter à l`école des sorciers', 'J.K. Rowling', '1997-06-26', 9782070643022, 8.90);

INSERT INTO Livre(titre, auteur, date_pub, numero_isbn, prix) VALUES
('Le Meilleur des mondes', 'Aldous Huxley', '1932-01-01', 9782070368222, 9.20);

INSERT INTO Livre(titre, auteur, date_pub, numero_isbn, prix) VALUES
('Harry Potter et la Chambre des Secrets', 'J.K. Rowling', '1998-07-02', 9782070643039, 8.90);

INSERT INTO Livre(titre, auteur, date_pub, numero_isbn, prix) VALUES
('Harry Potter et le Prisonnier d`Azkaban', 'J.K. Rowling', '1999-07-08', 9782070643046, 8.90);

INSERT INTO Livre(titre, auteur, date_pub, numero_isbn, prix) VALUES
('Harry Potter et la Coupe de Feu', 'J.K. Rowling', '2000-07-08', 9782070643053, 9.90);

INSERT INTO Livre(titre, auteur, date_pub, numero_isbn, prix) VALUES
('Harry Potter et l`Ordre du Phénix', 'J.K. Rowling', '2003-06-21', 9782070643060, 10.90);

INSERT INTO Livre(titre, auteur, date_pub, numero_isbn, prix) VALUES
('Harry Potter et le Prince de Sang-Mêlé', 'J.K. Rowling', '2005-07-16', 9782070643077, 10.90);

INSERT INTO Livre(titre, auteur, date_pub, numero_isbn, prix) VALUES
('Harry Potter et les Reliques de la Mort', 'J.K. Rowling', '2007-07-21', 9782070643084, 11.90);


INSERT INTO Utilisateur(nom, prenom, role) VALUES
('Dupont', 'Alice', 'enseignant'),
('Martin', 'Benoît', 'bibliothécaire'),
('Leroy', 'Catherine', 'enseignant'),
('Moreau', 'David', 'enseignant'),
('Bernard', 'Elise', 'élève'),
('Petit', 'François', 'élève'),
('Robert', 'Gabrielle', 'élève'),
('Richard', 'Hélène', 'élève'),
('Durand', 'Isabelle', 'bibliothécaire'),
('Dubois', 'Jules', 'élève');

INSERT INTO Emprunt(livre, utilisateur, date_emprunt) VALUES
(9780451524935, 6, '2025-02-01'),
(9782070612758, 3, '2025-02-03'),
(9782253004220, 3, '2025-02-05'),
(9782070360024, 2, '2025-02-07'),
(9782070105618, 8, '2025-02-10'),
(9782070405732, 6, '2025-02-12'),
(9782070360604, 2, '2025-02-15'),
(9782070117153, 1, '2025-02-18'),
(9782070360405, 7, '2025-02-20'),
(9782070318746, 3, '2025-02-22');

SQL - Joindre plusieurs tables#

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).

SELECT *
FROM Utilisateur
JOIN 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é.

SELECT *
FROM Utilisateur
JOIN Emprunt ON Emprunt.utilisateur = Utilisateur.id_utilisateur
JOIN 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.

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

Exercices#

Exercice 21#

Avant d'écrire vos propres JOIN, il faut bien comprendre ce qu'un JOIN produit. Exécutez la requête ci-dessous, observez attentivement le résultat, puis répondez aux questions.

SELECT *
FROM Utilisateur
JOIN Emprunt ON Emprunt.utilisateur = Utilisateur.id_utilisateur

Combien de lignes le résultat contient-il ?
Combien de fois Catherine Leroy apparaît-elle dans le résultat ?
La table Utilisateur contient 10 utilisateurs. Combien d'entre eux apparaissent au moins une fois dans le résultat ?

Solution

Un JOIN ne colle pas les tables l'une à côté de l'autre : il produit une ligne par couple de lignes qui vérifient la condition du ON.

La table Emprunt contient 10 lignes, et chacune correspond à exactement un utilisateur. Le résultat contient donc 10 lignes, soit autant que la table Emprunt.
Catherine Leroy a emprunté 3 livres. Ses informations sont donc recopiées 3 fois, une fois par emprunt. C'est normal et attendu.
Seuls 6 utilisateurs ont emprunté au moins un livre. Les 4 autres n'apparaissent pas du tout dans le résultat : sans emprunt, aucun couple ne vérifie la condition du ON.

Retenez ces deux effets : un JOIN peut répéter des lignes, et il peut aussi en faire disparaître.

Exercice 22#

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

Pour relier la table Emprunt à la table Livre de la bibliothèque :
SELECT * FROM Emprunt JOIN Livre ON
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 ______.

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

SELECT Livre.titre
FROM Utilisateur
JOIN Emprunt ON ______ = ______
JOIN Livre ON ______ = ______
WHERE 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'

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

SELECT Utilisateur.nom, Utilisateur.prenom
FROM Livre
JOIN Emprunt ON ______ = ______
JOIN Utilisateur ON ______ = ______
WHERE 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.

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

SELECT ______, ______
FROM Utilisateur
JOIN Emprunt ON ______ = ______
JOIN Livre ON ______ = ______
WHERE ______

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

-- Copyright 2025 Maxime Jan <maxime.jan@edufr.ch>
-- SPDX-License-Identifier: CC-BY-NC-SA-4.0
-- Base de données d'un cinéma : films, salles et séances.

PRAGMA foreign_keys = ON;

CREATE TABLE Film (
    titre TEXT,
    realisateur TEXT,
    duree_min INTEGER,
    genre TEXT,
    id_film INTEGER,
    PRIMARY KEY(id_film AUTOINCREMENT)
);

CREATE TABLE Salle (
    id_salle INTEGER,
    nom TEXT,
    nb_places INTEGER,
    PRIMARY KEY(id_salle AUTOINCREMENT)
);

CREATE TABLE Seance (
    id_seance INTEGER,
    film INTEGER,
    salle INTEGER,
    heure INTEGER,
    prix REAL,
    PRIMARY KEY(id_seance AUTOINCREMENT),
    FOREIGN KEY(film) REFERENCES Film(id_film),
    FOREIGN KEY(salle) REFERENCES Salle(id_salle)
);

INSERT INTO Film(titre, realisateur, duree_min, genre) VALUES
  ('Inception', 'Christopher Nolan', 148, 'Science-fiction'),
  ('Le Fabuleux Destin d''Amélie Poulain', 'Jean-Pierre Jeunet', 122, 'Comédie'),
  ('Dune', 'Denis Villeneuve', 155, 'Science-fiction'),
  ('Parasite', 'Bong Joon-ho', 132, 'Thriller'),
  ('Les Triplettes de Belleville', 'Sylvain Chomet', 80, 'Animation'),
  ('Interstellar', 'Christopher Nolan', 169, 'Science-fiction');

INSERT INTO Salle(nom, nb_places) VALUES
  ('Rouge', 220),
  ('Bleue', 120),
  ('Verte', 60),
  ('Studio', 35);

INSERT INTO Seance(film, salle, heure, prix) VALUES
  (1, 1, 2030, 19.50),
  (1, 3, 1400, 12.00),
  (2, 2, 1800, 16.00),
  (3, 1, 1730, 18.50),
  (3, 1, 2115, 18.00),
  (4, 2, 2100, 16.50),
  (5, 4, 1530, 11.00),
  (5, 3, 1030, 8.50),
  (6, 2, 2000, 17.00),
  (4, 4, 1900, 13.00),
  (2, 3, 2200, 14.00),
  (6, 1, 1100, 10.00);

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.

Quelles séances coûtent plus de 16 CHF ?
Quel film est projeté à 10h30 ?
Combien de places compte la salle Rouge ?
Dans quelle salle Parasite est-il projeté à 21h00 ?
Quel réalisateur est projeté à 22h00 ?
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.

Quel film est projeté à 10h30 ?

Solution

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

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

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

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

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

Quel réalisateur est projeté à 22h00 ?

Solution

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

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

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

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

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

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

Solution

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

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

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

PRAGMA foreign_keys = ON;

-- Schema
CREATE TABLE Enseignant (
  id_enseignant  INTEGER PRIMARY KEY AUTOINCREMENT,
  nom            TEXT NOT NULL,
  prenom         TEXT NOT NULL,
  taux_travail   REAL NOT NULL
);

CREATE TABLE Classe (
  id_classe       INTEGER PRIMARY KEY AUTOINCREMENT,
  section         TEXT NOT NULL,        -- 'Francophone' | 'Germanophone' | 'Bilingue'
  nom             TEXT NOT NULL,        -- ex: 1F1, 2D2, 3B1
  mcl             INTEGER NOT NULL,     -- maître·sse de classe
  annee_scolaire  INTEGER NOT NULL,
  FOREIGN KEY (mcl) REFERENCES Enseignant(id_enseignant)
);

CREATE TABLE Cours (
  id_cours            INTEGER PRIMARY KEY AUTOINCREMENT,
  classe        INTEGER NOT NULL,
  enseignant    INTEGER NOT NULL,
  branche       TEXT NOT NULL,
  salle         TEXT NOT NULL,          -- ex: P101, E205
  heure_debut   INTEGER NOT NULL,       -- ex: 815, 1630
  heure_fin     INTEGER NOT NULL,
  FOREIGN KEY (classe)     REFERENCES Classe(id_classe),
  FOREIGN KEY (enseignant) REFERENCES Enseignant(id_enseignant)
);

CREATE TABLE Eleve (
  id_eleve   INTEGER PRIMARY KEY AUTOINCREMENT,
  nom        TEXT NOT NULL,
  prenom     TEXT NOT NULL,
  classe     INTEGER NOT NULL,
  FOREIGN KEY (classe) REFERENCES Classe(id_classe)
);

CREATE INDEX idx_cours_classe      ON Cours(classe);
CREATE INDEX idx_cours_enseignant  ON Cours(enseignant);
CREATE INDEX idx_eleve_classe      ON Eleve(classe);

-- ===================================================================
-- ENSEIGNANTS (18)
-- ===================================================================
INSERT INTO Enseignant (nom, prenom, taux_travail) VALUES
  ('Martin',  'Claire', 0.80),  -- 1  (Français / Philosophie)
  ('Dupont',  'Louis',  1.00),  -- 2  (Mathématiques / Informatique)
  ('Nguyen',  'Sophie', 0.90),  -- 3  (Biologie)
  ('Rossi',   'Marco',  0.60),  -- 4  (Informatique)
  ('Schmid',  'Anna',   1.00),  -- 5  (Allemand / Histoire)
  ('Garcia',  'Elena',  0.70),  -- 6  (Anglais)
  ('Favre',   'Paul',   0.80),  -- 7  (Géographie)
  ('Morel',   'Julie',  0.50),  -- 8  (Sport / Musique)
  ('Weber',   'Thomas', 0.90),  -- 9  (Physique)
  ('Keller',  'Sarah',  0.70),  -- 10 (Français / Anglais)
  ('Blanc',   'Antoine',1.00),  -- 11 (Philosophie)
  ('Silva',   'Marta',  0.70),  -- 12 (Musique)
  ('Meier',   'Kevin',  0.60),  -- 13 (Chimie)
  ('Conti',   'Laura',  0.85),  -- 14 (Histoire)
  ('Frei',    'Jonas',  0.75),  -- 15 (Arts visuels / Histoire)
  ('Renaud',  'Alex',   1.00),  -- 16 (Économie et droit)
  ('Schaller','Nina',   0.10),  -- 17 (Français)
  ('Da Silva','Inès',   0.90);  -- 18 (Langues)

-- ===================================================================
-- CLASSES (15) — sections & noms selon consigne
-- id_classe = ordre d'insertion (1..15)
-- ===================================================================
INSERT INTO Classe (section, nom, mcl, annee_scolaire) VALUES
  ('Francophone', '1F1',  1, 2025),   -- 1
  ('Francophone', '1F2',  2, 2025),   -- 2
  ('Francophone', '2F1',  3, 2025),   -- 3
  ('Francophone', '2F2',  4, 2025),   -- 4
  ('Francophone', '3F1',  5, 2025),   -- 5
  ('Germanophone','1D1',  6, 2025),   -- 6
  ('Germanophone','2D1',  7, 2025),   -- 7
  ('Germanophone','2D2',  8, 2025),   -- 8
  ('Germanophone','3D1',  9, 2025),   -- 9
  ('Germanophone','3D2', 10, 2025),   --10
  ('Bilingue',    '1BP1', 11, 2025),   --11
  ('Bilingue',    '2BS1', 12, 2025),   --12
  ('Bilingue',    '2BS2', 13, 2025),   --13
  ('Bilingue',    '2BP1', 14, 2025),   --14
  ('Bilingue',    '3BP1', 15, 2025);   --15

-- ===================================================================
-- ÉLÈVES (10 par classe -> 150)
-- ===================================================================
INSERT INTO Eleve (nom, prenom, classe) VALUES
  -- 1F1 (10)
  ('Durand','Emma',1), ('Bernard','Lucas',1), ('Petit','Lina',1), ('Robert','Yanis',1),
  ('Favre','Zoé',1), ('Monod','Aline',1), ('Jaccard','Tim',1),
  ('Leresche','Hugo',1), ('Aubry','Maëlle',1), ('Piguet','Enzo',1),

  -- 1F2 (10)
  ('Richard','Noa',2), ('Moreau','Léo',2), ('Keller','Mia',2), ('Bovet','Nils',2),
  ('Lambert','Léna',2), ('Bajrami','Maxime',2), ('Rochat','Chloé',2),
  ('Perroud','Ethan',2), ('Grandjean','Sarah',2), ('Godinat','Jonas',2),

  -- 2F1 (10)
  ('Aebi','Nora',3), ('Girard','Ava',3), ('Monney','Eliot',3), ('Bosson','Nadia',3),
  ('Bourquin','Maya',3), ('Ayer','Noam',3), ('Fragnière','Mélina',3),
  ('Michaud','Alex',3), ('Caille','Sasha',3), ('Ammann','Zoe',3),

  -- 2F2 (10)
  ('Broillet','Marc',4), ('Gruber','Zoé',4), ('Perler','Tom',4), ('Rey','Milan',4),
  ('Remy','Nolan',4), ('Egger','Maud',4), ('Berset','Loris',4),
  ('Gillard','Naya',4), ('Loup','Evan',4), ('Hertig','Camille',4),

  -- 3F1 (10)
  ('Savoy','Aya',5), ('Cuennet','Jade',5), ('Berset','Luca',5), ('Cottet','Nina',5),
  ('Aellen','Yuna',5), ('Glauser','Kilian',5), ('Cuenat','Livia',5),
  ('Maradan','Théo',5), ('Pache','Inès',5), ('Vuillaume','Nico',5),

  -- 1D1 (10)
  ('Diop','Amir',6), ('Cottier','Sami',6), ('Rodriguez','Lina',6), ('Yerly','Noé',6),
  ('Zimmermann','Lea',6), ('Hofmann','Luca',6), ('Kunz','Mara',6),
  ('Schneider','Finn',6), ('Fischer','Mila',6), ('Huber','Noel',6),

  -- 2D1 (10)
  ('Baumann','Iris',7), ('Brügger','Liam',7), ('Schütz','Luna',7), ('Meyer','Alexis',7),
  ('Baumann','Noemi',7), ('Vogel','Alan',7), ('Graf','Lenn',7),
  ('Studer','Amelie',7), ('Rufener','Timo',7), ('Zbinden','Elina',7),

  -- 2D2 (10)
  ('Bapst','Nora',8), ('Ammann','Maya',8), ('Zen','Théo',8), ('Savary','Eva',8),
  ('Gerber','Nora',8), ('Keller','Yann',8), ('Bieri','Mia',8),
  ('Schmid','Julian',8), ('Frei','Lina',8), ('Steiner','Ben',8),

  -- 3D1 (10)
  ('Jordan','Ilan',9), ('Buchs','Mathis',9), ('Piller','Maël',9), ('Schenker','Ella',9),
  ('Imhof','Loris',9), ('Schmutz','Naomi',9), ('Gurtner','Elio',9),
  ('Bachmann','Nia',9), ('Sutter','Marco',9), ('Fankhauser','Marek',9),

  -- 3D2 (10)
  ('Fontana','Kimi',10), ('Maret','Jules',10), ('Ngoy','Sara',10), ('Suter','Dario',10),
  ('Moser','Giulia',10), ('Tanner','Robin',10), ('Wyss','Nadia',10),
  ('Roth','Ian',10), ('Kohler','Elena',10), ('Arnold','Luca',10),

  -- 1B1 (10)
  ('Pittet','Elsa',11), ('Burri','Noah',11), ('Vuadens','Inaya',11), ('Pittier','Yanis',11),
  ('Bersier','Maé',11), ('Marti','Isaac',11), ('Schafer','Ambre',11),
  ('Rey','Téo',11), ('Chollet','Leyla',11), ('Delgado','Rayan',11),

  -- 1B2 (10)
  ('Riedo','Celia',12), ('Zaugg','Clara',12), ('Carrel','Nolan',12), ('Ndaye','Liam',12),
  ('Dias','Livia',12), ('Pilloud','Noé',12), ('Gonçalves','Elisa',12),
  ('Berthoud','Yannick',12), ('Carvalho','Nina',12), ('Savary','Eden',12),

  -- 2B1 (10)
  ('Thalmann','Roxane',13), ('Chappuis','Luis',13), ('Aubert','Maya',13), ('Roth','Iris',13),
  ('Perrenoud','Alya',13), ('Grobety','Maël',13), ('Nussbaum','Iris',13),
  ('Chassot','Evan',13), ('Boscardin','Maia',13), ('Clerc','Nils',13),

  -- 2B2 (10)
  ('Monnin','Sami',14), ('Gilliéron','Ely',14), ('Gumy','Léa',14), ('Raemy','Loris',14),
  ('Progin','Léonie',14), ('Amherd','Timéo',14), ('Gabioud','Naya',14),
  ('Vuichard','Léo',14), ('Dousse','Jade',14), ('Gauthier','Arno',14),

  -- 3B1 (10)
  ('Camarasa','Noa',15), ('Broennimann','Léon',15), ('Angehrn','Lara',15), ('Lachat','Elio',15),
  ('Gumy','Nino',15), ('Bapst','Lena',15), ('Aubert','Evan',15),
  ('Hayoz','Alina',15), ('Savioz','Lyam',15), ('Cortes','Maya',15);

-- ===================================================================
-- COURS (7 par classe, horaires non chevauchants par classe)
-- ===================================================================

-- 1) 1F1 (template A)
INSERT INTO Cours (classe, enseignant, branche, salle, heure_debut, heure_fin) VALUES
  (1, 1, 'Français',          'P101', 800, 900),
  (1, 2, 'Mathématiques',     'P102', 910, 1010),
  (1, 5, 'Allemand',          'P110', 1020, 1120),
  (1, 7, 'Géographie',        'E121', 1130, 1230),
  (1, 3, 'Biologie',          'E220', 1330, 1500),
  (1, 8, 'Sport',             'P111', 1520, 1620),
  (1, 4, 'Informatique',      'E205', 1630, 1730);

-- 2) 1F2 (template B)
INSERT INTO Cours (classe, enseignant, branche, salle, heure_debut, heure_fin) VALUES
  (2,10, 'Français',          'E101', 815, 915),
  (2, 2, 'Mathématiques',     'P103', 925, 1025),
  (2, 6, 'Anglais',           'P110', 1035, 1135),
  (2,14, 'Histoire',          'E110', 1145, 1245),
  (2, 3, 'Biologie',          'E220', 1345, 1515),
  (2, 8, 'Sport',             'P111', 1525, 1625),
  (2, 4, 'Informatique',      'P112', 1635, 1735);

-- 3) 2F1 (template C)
INSERT INTO Cours (classe, enseignant, branche, salle, heure_debut, heure_fin) VALUES
  (3, 1, 'Français',          'P121', 830, 930),
  (3, 2, 'Mathématiques',     'E101', 940, 1040),
  (3, 6, 'Anglais',           'E121', 1050, 1150),
  (3,11, 'Philosophie',       'P220', 1300, 1400),
  (3, 7, 'Géographie',        'P102', 1410, 1510),
  (3, 3, 'Biologie',          'E205', 1520, 1650),
  (3, 4, 'Informatique',      'P110', 1700, 1800);

-- 4) 2F2 (template A)
INSERT INTO Cours (classe, enseignant, branche, salle, heure_debut, heure_fin) VALUES
  (4,17, 'Français',          'P103', 800, 900),
  (4, 2, 'Mathématiques',     'P121', 910, 1010),
  (4, 5, 'Allemand',          'E101', 1020, 1120),
  (4,16, 'Économie et droit', 'E110', 1130, 1230),
  (4,13, 'Chimie',            'E220', 1330, 1430),
  (4,14, 'Histoire',          'P111', 1440, 1540),
  (4, 8, 'Sport',             'P112', 1550, 1650);

-- 5) 3F1 (template B)
INSERT INTO Cours (classe, enseignant, branche, salle, heure_debut, heure_fin) VALUES
  (5, 1, 'Français',          'P110', 815, 915),
  (5, 2, 'Mathématiques',     'P121', 925, 1025),
  (5, 9, 'Physique',          'E121', 1035, 1135),
  (5,11, 'Philosophie',       'E110', 1145, 1245),
  (5, 4, 'Informatique',      'P112', 1345, 1445),
  (5, 3, 'Biologie',          'E220', 1455, 1625),
  (5,12, 'Musique',           'P103', 1635, 1735);

-- 6) 1D1 (template A)
INSERT INTO Cours (classe, enseignant, branche, salle, heure_debut, heure_fin) VALUES
  (6, 5, 'Allemand',          'P101', 800, 900),
  (6, 2, 'Mathématiques',     'E101', 910, 1010),
  (6,10, 'Français',          'E121', 1020, 1120),
  (6, 4, 'Informatique',      'P102', 1130, 1230),
  (6, 7, 'Géographie',        'P110', 1330, 1430),
  (6, 9, 'Physique',          'E220', 1440, 1540),
  (6, 8, 'Sport',             'P111', 1550, 1650);

-- 7) 2D1 (template B)
INSERT INTO Cours (classe, enseignant, branche, salle, heure_debut, heure_fin) VALUES
  (7, 5, 'Allemand',          'E101', 815, 915),
  (7, 2, 'Mathématiques',     'P103', 925, 1025),
  (7, 6, 'Anglais',           'P110', 1035, 1135),
  (7,14, 'Histoire',          'E110', 1145, 1245),
  (7,13, 'Chimie',            'E220', 1345, 1445),
  (7, 4, 'Informatique',      'P111', 1455, 1555),
  (7, 8, 'Sport',             'P112', 1605, 1705);

-- 8) 2D2 (template C)
INSERT INTO Cours (classe, enseignant, branche, salle, heure_debut, heure_fin) VALUES
  (8, 5, 'Allemand',          'P121', 830, 930),
  (8, 2, 'Mathématiques',     'P102', 940, 1040),
  (8, 1, 'Français',          'P101', 1050, 1150),
  (8, 9, 'Physique',          'E205', 1300, 1400),
  (8, 7, 'Géographie',        'P121', 1410, 1510),
  (8, 3, 'Biologie',          'E101', 1520, 1650),
  (8, 4, 'Informatique',      'P112', 1700, 1800);

-- 9) 3D1 (template A)
INSERT INTO Cours (classe, enseignant, branche, salle, heure_debut, heure_fin) VALUES
  (9, 5, 'Allemand',          'P110', 800, 900),
  (9, 2, 'Mathématiques',     'P121', 910, 1010),
  (9,11, 'Philosophie',       'E110', 1020, 1120),
  (9,14, 'Histoire',          'P102', 1130, 1230),
  (9, 9, 'Physique',          'E205', 1330, 1430),
  (9, 4, 'Informatique',      'P112', 1440, 1540),
  (9, 8, 'Sport',             'P220', 1550, 1650);

-- 10) 3D2 (template B)
INSERT INTO Cours (classe, enseignant, branche, salle, heure_debut, heure_fin) VALUES
  (10, 5, 'Allemand',         'P101', 815, 915),
  (10, 2, 'Mathématiques',    'E101', 925, 1025),
  (10,10, 'Français',         'P121', 1035, 1135),
  (10,16, 'Économie et droit','E110', 1145, 1245),
  (10,13, 'Chimie',           'E220', 1345, 1445),
  (10, 6, 'Anglais',          'P111', 1455, 1555),
  (10,12, 'Musique',          'P103', 1605, 1705);

-- 11) 1B1 (template A)
INSERT INTO Cours (classe, enseignant, branche, salle, heure_debut, heure_fin) VALUES
  (11, 1, 'Français',         'P110', 800, 900),
  (11, 5, 'Allemand',         'P102', 910, 1010),
  (11, 6, 'Anglais',          'E121', 1020, 1120),
  (11, 2, 'Mathématiques',    'P121', 1130, 1230),
  (11, 4, 'Informatique',     'E205', 1330, 1430),
  (11, 7, 'Géographie',       'P111', 1440, 1540),
  (11, 8, 'Sport',            'P112', 1550, 1650);

-- 12) 1B2 (template B)
INSERT INTO Cours (classe, enseignant, branche, salle, heure_debut, heure_fin) VALUES
  (12,10, 'Français',         'E101', 815, 915),
  (12, 5, 'Allemand',         'P103', 925, 1025),
  (12, 6, 'Anglais',          'P110', 1035, 1135),
  (12, 2, 'Mathématiques',    'E205', 1145, 1245),
  (12,14, 'Histoire',         'E110', 1345, 1445),
  (12, 3, 'Biologie',         'E220', 1455, 1625),
  (12,12, 'Musique',          'P220', 1635, 1735);

-- 13) 2B1 (template C)
INSERT INTO Cours (classe, enseignant, branche, salle, heure_debut, heure_fin) VALUES
  (13, 1, 'Français',         'P101', 830, 930),
  (13, 5, 'Allemand',         'P121', 940, 1040),
  (13, 6, 'Anglais',          'E121', 1050, 1150),
  (13, 2, 'Mathématiques',    'P102', 1300, 1400),
  (13, 9, 'Physique',         'E205', 1410, 1510),
  (13, 4, 'Informatique',     'P112', 1520, 1620),
  (13,16, 'Économie et droit','E110', 1630, 1730);

-- 14) 2B2 (template A)
INSERT INTO Cours (classe, enseignant, branche, salle, heure_debut, heure_fin) VALUES
  (14, 1, 'Français',         'E101', 800, 900),
  (14, 5, 'Allemand',         'P103', 910, 1010),
  (14, 6, 'Anglais',          'P110', 1020, 1120),
  (14, 2, 'Mathématiques',    'P121', 1130, 1230),
  (14,13, 'Chimie',           'E220', 1330, 1430),
  (14,14, 'Histoire',         'P111', 1440, 1540),
  (14, 8, 'Sport',            'P112', 1550, 1650);

-- 15) 3B1 (template B)
INSERT INTO Cours (classe, enseignant, branche, salle, heure_debut, heure_fin) VALUES
  (15, 1, 'Français',         'P110', 815, 915),
  (15, 5, 'Allemand',         'P102', 925, 1025),
  (15, 6, 'Anglais',          'E121', 1035, 1135),
  (15, 2, 'Mathématiques',    'P121', 1145, 1245),
  (15,11, 'Philosophie',      'E110', 1345, 1445),
  (15, 4, 'Informatique',     'E205', 1455, 1555),
  (15, 9, 'Physique',         'P112', 1605, 1705);

-- End

Exercice 26#

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

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.

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)

Solution

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

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.

Solution

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

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

Solution

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

Quelle branche donne Laura Conti ?

Solution

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

Dans quelle classe se trouve Eva Savary ?

Solution

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

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

Solution

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

Quelle classe a cours de Physique en salle E220 ?

Solution

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

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

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

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.

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

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'

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

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'

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

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

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

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'