Dans cette section, vous trouverez des ressources sur les cours que j'enseigne à savoir de la documentation, du code, des vidéos etc. Dès que je trouve quelque chose d'intéressant à partager, je le mettrai à disposition ici pour mes étudiants.
Travaux dirigés 8
##Auteur : Enseignant
##École : Collège Marie Victorin
##Session : Hiver 2025
##Titre du cours : Introduction aux bases de données
##Titre du programme : Travaux dirigés 8 -
##Version : 1.0
##Date : 00/00/2025
##Langage et version : SQL
##Moteur : SQL Server Express
- Base de données : School
-- En utilisant la base de données School, créer les requêtes qui correspondent aux énoncés suivants.
-- Requête 1
-- Écrire une requête qui affiche tous les étudiants (Person) ayant une date d'inscription (EnrollmentDate) non nulle..
-- On affichera le nom, le prénom et la date d'inscription
-- La liste sera triée par la date d'inscription
-- La colonne "Date d'inscription" sera du type Date et non du type DateTime
-- Votre code ici
SELECT
FirstName,
LastName,
CAST([EnrollmentDate] AS date) DateInscription
FROM Person
WHERE EnrollmentDate IS NOT NULL;
-- Requête 2
-- Écrire une requête qui affiche tous les enseignants (Person) embauchés avant l'an 2000 et dont le nom commence par "S".
-- On affichera les colonnes :
- Le nom
- Le prénom
- L'année d'embauche
-- Votre code ici
SELECT
LastName,
FirstName,
[HireDate]
FROM Person
--WHERE CAST(HireDate AS Date) < '2000-01-01' AND LastName LIKE 'S%';
WHERE HireDate < '2000-01-01' AND LastName LIKE 'S%';
-- Requête 8
---
-- Écrire une requête qui affiche les listes de classe par département des étudiants ayant obtenu une note.
-- On affichera les colonnes :
-- Le département
-- Le nom de l'enseignant
-- Le prénom de l'enseignant
-- Le titre du cours
-- Le nom de l'étudiant
-- Le prénom de l'étudiant
-- La note de l'étudiant
-- La liste sera triée par département et par enseignant
-- Votre code ici
SELECT
P.LastName NomEtudiant,
P.FirstName PrenomEtudiant,
P.Discriminator Statut,
SG.Grade Note,
C.Title Cours,
D.Name Departement,
I.LastName NomInstructeur,
I.FirstName PrenomInstructeur
FROM
Person P,
StudentGrade SG,
Course C,
Department D,
CourseInstructor CI,
Person I
WHERE
P.PersonID = SG.StudentID AND
SG.CourseID = C.CourseID AND
C.DepartmentID = D.DepartmentID AND
CI.CourseID = C.CourseID AND
I.PersonID = CI.PersonID AND
SG.Grade IS NOT NULL;
-- Requête 8-1
--------------
-- Écrire une requête qui crée une table tblListeClasse avec le résultat de la requête 8.
-- Ajouter la clé primaire ClasseID qui contient un identificateur unique.
-- La structure de la table tblListeClasse est :
-- ClasseID identificateur unique, clé primaire
-- Le département
-- Le nom et le prénom de l'enseignant
-- Le titre du cours
-- Le nom et le prénom de l'étudiant
-- La note de l'étudiant
-- Votre code ici
DROP TABLE IF EXISTS tblListeClasse;
SELECT *
INTO tblListeClasse
FROM
(
SELECT
NEWID() ClasseID,
D.Name AS DepartmentName,
CONCAT(I.LastName, ' ', I.FirstName) AS Instructor,
C.Title AS CourseTitle,
CONCAT(ST.LastName, ' ', ST.FirstName) AS Student,
SG.Grade
FROM
Person ST
JOIN
StudentGrade SG ON ST.PersonID = SG.StudentID
JOIN
Course C ON SG.CourseID = C.CourseID
JOIN
Department D ON C.DepartmentID = D.DepartmentID
JOIN
CourseInstructor CI ON C.CourseID = CI.CourseID
JOIN
Person I ON CI.PersonID = I.PersonID
WHERE
SG.Grade IS NOT NULL
) temp;
-- Vérification de la création de la table
SELECT * FROM tblListeClasse;
-- Requête 11
-- Écrire une requête qui affiche les emplacements des cours en présentiel ainsi que les enseignants de ces cours.
-- On affichera les colonnes :
- Le nom et le prénom de l'enseignant
- Le titre du cours enseigné
- L'emplacement ou le cours est donné
-- Votre code ici
SELECT
P.LastName
,P.FirstName
,C.Title
,OS.Location
FROM
Course C,
OnsiteCourse OS,
CourseInstructor CI,
Person P
WHERE
C.CourseID = OS.CourseID AND
C.CourseID = CI.CourseID AND
P.PersonID = CI.PersonID;
-- Requête 12
-- Écrire une requête qui affiche les départements ayant au moins 2 cours de 3 crédits minimum chacun.
-- On affichera les colonnes :
- Le nom du département
- Le nombre de cours dans le département
-- Votre code ici
SELECT
D.Name Department
,COUNT(*) AS TotalCourses
FROM
Course C,
Department D
WHERE
C.DepartmentID = D.DepartmentID AND
Credits >= 3
GROUP BY D.Name
HAVING COUNT(*) >= 2;
-- Requête 13
-- Écrire une requête qui affiche les étudiants ayant obtenu une moyenne supérieure à 3.5 dans au moins 2 cours.
-- On affichera les colonnes :
-- Le nom et le prenom de l'étudiant
-- La moyenne des notes de l'étudiant
-- Le nombre de cours pour lesquels il a obtenu plus de 3.5 de moyenne
-- Votre code ici
SELECT
CONCAT(P.LastName, ' ', P.FirstName) NomPrenom
,AVG(Grade) AS AverageGrade
,COUNT(*) AS TotalCourses
FROM
StudentGrade SG,
Person P
WHERE
P.PersonID = SG.StudentID AND
Grade IS NOT NULL AND
P.Discriminator = 'student'
GROUP BY CONCAT(P.LastName, ' ', P.FirstName)
HAVING AVG(Grade) > 3.5 AND COUNT(*) >= 2;
-- Requête 14
-- Écrire une requête qui calcule le nombre total de crédits obtenus par chaque étudiant qui a réussi le cours. La note de passage étant 2.0
-- On s'intéresse aux étudiants ayant pris plus d'un cours.
-- On afichera les colonnes :
- Le nom complet de l'étudiant
- Le nombre total de crédit
- Le nombre de cours
- La moyenne des cours
-- Votre code ici
SELECT
CONCAT(P.LastName, ' ', P.FirstName) Etudiant
,COUNT(C.CourseID) NbCours
,SUM(C.Credits) TotalCredit
,AVG(SG.Grade) MoyenneEtudiant
FROM
Person P,
StudentGrade SG,
Course C
WHERE
P.PersonID = SG.StudentID AND
C.CourseID = SG.CourseID AND
SG.Grade > 2.0
GROUP BY
CONCAT(P.LastName, ' ', P.FirstName)
HAVING COUNT(C.CourseID) > 1
ORDER BY 1;
-- Requête 15
-- Écrire une requete qui crée une table tblBulletinNotes qui affiche les notes des étudiants issues de la requête 14
-- La structure de la table tblBulletinNotes :
- BulletinID Clé primaire, identificateur unique
- Le nom et le prénom de l'étudiant
- Le nombre de cours de l'étudiant
- Le nombre de total de crédits
- La moyenne générale de l'étudiant
-- Votre code ici
-- Vérification de l'existence de la table
DROP TABLE IF EXISTS tblBulletinNotes;
-- Creation de la table
SELECT *
INTO tblBulletinNotes
FROM (
SELECT
NEWID() ClasseID
,CONCAT(P.LastName, ' ', P.FirstName) Etudiant
,COUNT(C.CourseID) NbCours
,SUM(C.Credits) TotalCredit
,AVG(SG.Grade) MoyenneEtudiant
FROM
Person P,
StudentGrade SG,
Course C
WHERE
P.PersonID = SG.StudentID AND
C.CourseID = SG.CourseID AND
SG.Grade > 2.0
GROUP BY
CONCAT(P.LastName, ' ', P.FirstName)
HAVING COUNT(C.CourseID) > 1
) temp;
-- Vérification de la création de la table
SELECT * FROM tblBulletinNotes;
-- Rendre la colonne ClasseID non nulle
ALTER TABLE tblBulletinNotes
ALTER COLUMN ClasseID UNIQUEIDENTIFIER NOT NULL;
-- Modification de la table et ajout de la clé primaire
ALTER TABLE tblBulletinNotes
ADD CONSTRAINT PK_ClasseID PRIMARY KEY(ClasseID);
-- Vérification de la clé primaire
EXEC sp_help tblBulletinNotes;
W3schools
Python.org