TD Bases de Données et Ingénierie des Systèmes d’Information TD 3 − Requêtes SQL

Ce document présente un TD consacré aux requêtes SQL dans le cadre de la gestion informatisée des emplois du temps pour une école d’ingénieurs. Il s’adresse aux étudiants ayant des connaissances de base en modèle conceptuel de données, modèle relationnel et langage SQL. Le TD propose un exemple concret avec un schéma relationnel, des requêtes simples et imbriquées, ainsi que la syntaxe SQL associée.

Description du système d’informations

La direction des études des Mines de Nancy souhaite informatiser la gestion des emplois du temps. Chaque étudiant est défini par un numéro, un nom, un prénom et un âge. Chaque cours est identifié par un sigle unique (ex. SI033, MD021) et possède un intitulé (bases de données, mathématiques discrètes, etc.) ainsi qu’un enseignant responsable. Les enseignants sont caractérisés par un identifiant alphanumérique, un nom et un prénom.

Chaque séance est identifiée par le cours et un numéro de séance (ex. séance 3 du cours SI033), le type d’intervention (CM, TD, TP), la date, l’heure de début et de fin, la salle et l’enseignant qui dispense la séance. Les étudiants s’inscrivent aux cours qu’ils souhaitent suivre.

Schéma relationnel retenu

Les clés primaires sont soulignées et les clés étrangères sont en italique :

• Etudiant ( <u>numero</u>, nom, prenom, age )
• Enseignant ( <u>id</u>, nom, prenom )
• Cours ( <u>sigle</u>, intitule, responsable, nombreSeances )
• Séance ( <u>cours</u>, <u>numero</u>, type, date, salle, heureDebut, heureFin, enseignant )
• Inscription ( etudiant, cours )

Requêtes simples

Création des tables « Etudiant » et « Séance »

CREATE TABLE Etudiant (
  numero VARCHAR(20) PRIMARY KEY,
  nom VARCHAR(50) NOT NULL,
  prenom VARCHAR(50) NOT NULL,
  age INT NOT NULL CHECK(age > 0)
);

CREATE TABLE Séance (
  cours VARCHAR(20) NOT NULL,
  numero VARCHAR(50) NOT NULL,
  type VARCHAR(2) NOT NULL CHECK(type IN ('CM', 'TD', 'TP')),
  date DATE NOT NULL,
  salle VARCHAR(10) NOT NULL,
  heureDebut TIME NOT NULL,
  heureFin TIME NOT NULL CHECK(heureFin > heureDebut),
  enseignant VARCHAR(20) NOT NULL,
  PRIMARY KEY (cours, numero),
  FOREIGN KEY (cours) REFERENCES Cours(sigle),
  FOREIGN KEY (enseignant) REFERENCES Enseignant(id)
);

Insertion d’un étudiant inscrit à un cours

Exemple : inscrire l’étudiant ('l0372', 'Léponge', 'Bob', 20) au cours ('LOG015', 'Logique', 'jh1908').

INSERT INTO Inscription VALUES ('l0372', 'LOG015');

Requêtes de sélection

• Nom et prénom de tous les étudiants de moins de 20 ans :

SELECT nom, prenom FROM Etudiant WHERE age < 20;

• Nom et prénom de l’enseignant responsable du cours de Statistiques :

SELECT nom, prenom
FROM Enseignant, Cours
WHERE responsable = id
  AND intitule LIKE 'Statistiques';

• Nom et prénom de tous les étudiants inscrits au cours de Probabilités :

SELECT e.nom, e.prenom
FROM etudiant e, inscription i, cours c
WHERE e.numero = i.etudiant
  AND i.cours = c.sigle
  AND c.intitule LIKE 'Probabilites';

• Nombre d’enseignants intervenant dans le cours de Modélisation Stochastique :

SELECT COUNT(DISTINCT enseignant)
FROM Seance, Cours
WHERE sigle = cours
  AND intitule LIKE '%Modelisation%';

• Lieu et date du premier cours d’Algèbre linéaire :

SELECT date, salle, heureDebut, heureFin
FROM Seance, Cours
WHERE sigle = cours
  AND numero = 1
  AND intitule LIKE 'Algebre lineaire';

• Emploi du temps du cours de Logique :

SELECT numero, date, salle, heureDebut, heureFin, e.nom, e.prenom
FROM Seance, Cours, Enseignant e
WHERE sigle = cours
  AND enseignant = id
  AND intitule LIKE 'Logique'
ORDER BY date, heureDebut;

• Nombre de cours par enseignant intervenant dans au moins deux cours :

SELECT e.nom, e.prenom, COUNT(DISTINCT cours)
FROM Seance, Cours, Enseignant e
WHERE sigle = cours
  AND enseignant = e.id
GROUP BY e.id
HAVING COUNT(DISTINCT cours) > 1;

Requêtes imbriquées

Ajout d’un cours magistral de Logique le 14 décembre avec Jacques Herbrand

Le cours aura lieu en salle S250 de 14h à 18h.

INSERT INTO Séance VALUES (
  (SELECT sigle FROM Cours WHERE intitule LIKE 'Logique'),
  (SELECT nombreSeances + 1 FROM Cours WHERE intitule LIKE 'Logique'),
  'CM',
  '2008-12-14',
  'S250',
  '14:00',
  '18:00',
  (SELECT id FROM Enseignant WHERE nom LIKE 'Herbrand' AND prenom = 'Jacques')
);

UPDATE Cours SET nombreSeances = nombreSeances + 1 WHERE intitule LIKE 'Logique';

Liste des étudiants inscrits à aucun cours

SELECT e.nom, e.prenom
FROM Etudiant e
WHERE NOT EXISTS (
  SELECT * FROM Inscription i WHERE i.etudiant = e.numero
);

Nombre d’étudiants différents ayant assisté à au moins une séance animée par Leonhard Euler

SELECT COUNT(DISTINCT e.numero)
FROM Etudiant e, Inscription i
WHERE i.etudiant = e.numero
  AND EXISTS (
    SELECT s.cours
    FROM Enseignant en, Seance s
    WHERE en.id = s.enseignant
      AND s.cours = i.cours
      AND en.nom LIKE 'Euler'
      AND en.prenom LIKE 'Leonhard'
  );

Syntaxe SQL essentielle

La structure générale d’une requête SELECT est :

SELECT liste_d’attributs
FROM noms_de_tables
[WHERE liste_de_critères]
[GROUP BY liste_d’attributs]
[HAVING liste_de_critères]
[ORDER BY liste_d’attributs];

Quelques mots-clés et fonctions courants :

• DISTINCT : élimine les doublons
• AS : alias pour renommer une colonne ou table
• Opérateurs arithmétiques : +, -, *, %
• Fonctions d’agrégation : AVG, MAX, MIN, SUM, COUNT
• Comparateurs : <, <=, >, >=, =, <>
• Conditions : BETWEEN, IN, IS NULL
• Filtres : LIKE, AND, OR, NOT

Glossaire des termes clés

• Clé primaire : attribut ou ensemble d’attributs qui identifie de façon unique une ligne dans une table.
• Clé étrangère : attribut qui référence une clé primaire dans une autre table, assurant l’intégrité référentielle.
• Requête imbriquée (sous-requête) : requête SELECT placée à l’intérieur d’une autre requête, souvent entre parenthèses.
• INSERT INTO : commande SQL pour insérer des données dans une table.
• CREATE TABLE : commande SQL pour créer une nouvelle table avec ses attributs et contraintes.
• DELETE : commande SQL pour supprimer des données (non présentée ici).
• SELECT : commande SQL pour interroger des données dans une ou plusieurs tables.
• JOIN implicite : jointure réalisée en listant plusieurs tables dans FROM et en précisant la condition dans WHERE.
• GROUP BY : clause pour regrouper les résultats selon un ou plusieurs attributs.
• HAVING : clause pour filtrer les groupes créés par GROUP BY.
• EXISTS / NOT EXISTS : test d’existence ou de non-existence de résultats dans une sous-requête.

Points clés à retenir

• Le schéma relationnel doit clairement définir les clés primaires et étrangères pour assurer l’intégrité des données.
• Les requêtes SQL peuvent être simples (sélection, insertion) ou imbriquées (sous-requêtes) pour des besoins plus complexes.
• Les contraintes sur les attributs (NOT NULL, CHECK) permettent de garantir la validité des données.
• Les jointures entre tables s’effectuent via les clés étrangères, souvent dans la clause WHERE.
• Les fonctions d’agrégation et les clauses GROUP BY / HAVING sont essentielles pour les calculs statistiques.
• Les sous-requêtes permettent de filtrer ou de calculer des données en fonction de résultats intermédiaires.
• La gestion des emplois du temps nécessite de manipuler plusieurs tables liées (étudiants, enseignants, cours, séances, inscriptions).