CREATE OR REPLACE DATABASE school;
CREATE TABLE school.teachers (
employee_number INT AUTO_INCREMENT KEY,
name VARCHAR(200) NOT NULL,
email VARCHAR(200) UNIQUE NOT NULL,
birthday DATE NOT NULL
);
CREATE TABLE school.courses (
code CHAR(10) NOT NULL,
credits DOUBLE(3,2) NOT NULL DEFAULT 1,
name VARCHAR(200) NOT NULL,
description VARCHAR(1000),
teacher INT NOT NULL,
semester CHAR(5) NOT NULL
);
INSERT INTO school.teachers (employee_number, name, email, birthday)
VALUES (1, 'James', 'jhoffman@cshawi.ca', '1990-01-01'), (2, 'Mathieu', 'mstyves@cshawi.ca', '1980-02-02'), (3, 'Stevens', 'sgagnon@cshawi.ca', '1970-03-03'), (4, 'Marco', 'mguilmette@cshawi.ca', '1975-04-04'), (5, 'Lyne', 'lamyot@cshawi.ca', '1985-05-05'), (6, 'Nicolas', 'nbourre@cshawi.ca', '1988-08-08');
INSERT INTO school.courses(code, credits, name, description, teacher, semester)
VALUES ('420-0Q4-SW', 2, 'Initiation à la profession', NULL, 1, CONCAT('A', YEAR(CURDATE()) - 1)), ('420-0Q4-SW', 2, 'Initiation à la profession', NULL, 1, CONCAT('A', YEAR(CURDATE()))), ('420-0Q4-SW', 2, 'Initiation à la profession', NULL, 1, CONCAT('A', YEAR(CURDATE()) - 3)), ('420-0Q7-SW', 2.33, 'Base de données 1', NULL, 1, CONCAT('A', YEAR(CURDATE()) - 1)), ('420-2SS-SW', 2, 'Développement d''applications mobiles', NULL, 1, CONCAT('A', YEAR(CURDATE()) - 1)), ('420-2SS-SW', 2, 'Développement d''applications mobiles', NULL, 1, CONCAT('A', YEAR(CURDATE()))), ('420-0Q7-SW', 2.33, 'Base de données 1', NULL, 2, CONCAT('A', YEAR(CURDATE()) - 1)), ('420-2SS-SW', 2, 'Développement d''applications mobiles', NULL, 2, CONCAT('A', YEAR(CURDATE()))), ('420-0Q4-SW', 2, 'Initiation à la profession', NULL, 2, CONCAT('A', YEAR(CURDATE()) - 3)), ('420-0SU-SW', 1.33, 'Web: Client 1', NULL, 2, CONCAT('A', YEAR(CURDATE()) - 1)), ('420-2SU-SW', 2.33, 'Web: Serveur 2', NULL, 2, CONCAT('A', YEAR(CURDATE()) - 1)), ('420-1SY-SW', 2.66, 'Analyse objet', NULL, 2, CONCAT('A', YEAR(CURDATE()) - 1)), ('420-0Q4-SW', 2, 'Initiation à la profession', NULL, 3, CONCAT('A', YEAR(CURDATE()) - 1)), ('420-0SV-SW', 1.66, 'Échange de données 1', 'Développement d''applications client-serveur', 3, CONCAT('A', YEAR(CURDATE()) - 1)), ('420-1SX-SW', 1.66, 'Robotique', 'Programmer un robot pour suivree un tracé', 3, CONCAT('A', YEAR(CURDATE()) - 1)), ('420-2SS-SW', 2, 'Développement d''applications mobiles', NULL, 3, CONCAT('A', YEAR(CURDATE()))), ('420-0Q4-SW', 2, 'Initiation à la profession', NULL, 4, CONCAT('A', YEAR(CURDATE()) - 8)), ('420-0Q4-SW', 2, 'Initiation à la profession', NULL, 4, CONCAT('A', YEAR(CURDATE()) - 7)), ('420-2SS-SW', 2, 'Développement d''applications mobiles', NULL, 1, CONCAT('A', YEAR(CURDATE()))), ('420-0Q7-SW', 2.33, 'Base de données 1', NULL, 2, CONCAT('A', YEAR(CURDATE()) - 1)), ('420-2SS-SW', 2, 'Développement d''applications mobiles', NULL, 2, CONCAT('A', YEAR(CURDATE()))), ('420-0Q4-SW', 2, 'Initiation à la profession', NULL, 2, CONCAT('A', YEAR(CURDATE()) - 3)), ('420-0SU-SW', 1.33, 'Web: Client 1', NULL, 2, CONCAT('A', YEAR(CURDATE()) - 1)), ('420-2SU-SW', 2.33, 'Web: Serveur 2', NULL, 2, CONCAT('A', YEAR(CURDATE()) - 1)), ('420-1SY-SW', 2.66, 'Analyse objet', NULL, 2, CONCAT('A', YEAR(CURDATE()) - 1)), ('420-0Q4-SW', 2, 'Initiation à la profession', NULL, 3, CONCAT('A', YEAR(CURDATE()) - 1)), ('420-0SV-SW', 1.66, 'Échange de données 1', 'Développement d''applications client-serveur', 3, CONCAT('A', YEAR(CURDATE()) - 1)), ('420-1SX-SW', 1.66, 'Robotique', 'Programmer un robot pour suivree un tracé', 3, CONCAT('A', YEAR(CURDATE()) - 1)), ('420-2SS-SW', 2, 'Développement d''applications mobiles', NULL, 5, CONCAT('A', YEAR(CURDATE())));
Une sous-requête permet d'injecter un SELECT dans une autre requête(select, insert, update, delete) et d'en exploiter le résultat. C'est mécanisme puissant pour récupérer des données adjacentes dans la même table ou dans une autre table.
-- Liste des cours, en affichant le courriel de contact de l'enseignant du cours
SELECT
code,
name,
(SELECT email FROM teachers WHERE employee_number = teacher) AS `Courriel`
FROM courses;
-- On peut donner des alias aux tables pour rendre explicite la manipulation des colonnes
SELECT
C.code,
C.name,
(SELECT T.email FROM teachers AS T WHERE T.employee_number = C.teacher) AS `Courriel`
FROM courses AS C;
📚 Documentation ℹ️ Subqueries
Scalaire Retourne 1 seule colonne ET 1 seule ligne, donc 1 seule valeur
📚 Scalar
Lignes Retourne un ensemble de plusieurs colonnes ET/OU lignes
📚 Rows
SELECT name, email, birthday, age
FROM (
-- Alternative pour selectionner toutes les colonnes SELECT *, ...
SELECT name, email, birthday, (TIMESTAMPDIFF(YEAR, birthday, CURDATE())) AS `age`
FROM teachers
) AS TeachersSummary
-- On doit OBLIGATOIREMENT affecter un alias à la table dérivée
WHERE TeachersSummary.age > (
SELECT AVG(TIMESTAMPDIFF(YEAR, birthday, CURDATE()))
FROM teachers
);
-- Si aucune ambiguité de nom de colonne,
-- l'identification de la colonne 'age' via l'alias de table est optionnelle
Essayons de réutiliser la table dérivée TeachersSummary
SELECT
name,
email,
birthday,
age
FROM (
SELECT name, email, birthday, (TIMESTAMPDIFF(YEAR, birthday, CURDATE())) AS `age`
FROM teachers
) AS TeachersSummary
WHERE age > ( SELECT AVG(age) FROM TeachersSummary ); -- Retirer la répétition
😢 Table 'TeacherSummary' does not exists
😃 L'opérateur WITH permet de créer une table temporaire accessible dans toute la requête subséquente. On peut préciser plusieurs tables temporaire avec l'opérateur WITH.
WITH TeachersSummary AS (
SELECT name, email, birthday, TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS `age`
FROM teachers
)
SELECT *
FROM TeachersSummary
WHERE age > (SELECT AVG(age) FROM TeachersSummary);
--
-- VS
--
SELECT name, email, birthday, age
FROM (
SELECT name, email, birthday, (TIMESTAMPDIFF(YEAR, birthday, CURDATE())) AS `age`
FROM teachers
) AS TeachersSummary
WHERE age > ( SELECT AVG(TIMESTAMPDIFF(YEAR, birthday, CURDATE())) FROM teachers );
SELECT
name,
email,
birthday,
TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS `Age`
FROM teachers
WHERE TIMESTAMPDIFF(YEAR, birthday, CURDATE()) > (
SELECT AVG(TIMESTAMPDIFF(YEAR, birthday, CURDATE()))
FROM teachers
);
Valide également dans un HAVING
-- SELECT: Scalaire
SELECT code, name,
(SELECT email FROM teachers WHERE employee_number = teacher) AS `Courriel`
-- La colonne teacher est accessible de la table courses dans la sous-requête
FROM courses;
-- SELECT(from)/WITH : Lignes
-- Séparation des calculs
WITH Season AS (
SELECT CASE
WHEN MONTH(CURDATE()) BETWEEN 8 AND 12 THEN 'A'
WHEN MONTH(CURDATE()) BETWEEN 1 AND 5 THEN 'H'
ELSE 'E'
END AS `current`
),
Year AS (
SELECT YEAR(CURDATE()) AS `current`
)
SELECT *
FROM courses
WHERE LEFT(semester, 1) = (SELECT current FROM Season LIMIT 1) -- LIMIT 1 requis???
&& RIGHT(semester, 4) = (SELECT current FROM Year);
-- SELECT(from)/WITH : Lignes
-- Aggrégation d'une aggrégation
SELECT AVG(C.occurences) AS `Moyenne occurences des cours`
FROM (
SELECT COUNT(name) AS `occurences`
FROM courses
GROUP BY code
) AS C;
-- Condition d'une aggrégation sur une aggrégation
WITH C AS (
SELECT *, COUNT(name) AS `occurences`
FROM courses
GROUP BY code
)
SELECT *
FROM C
WHERE occurences >= (SELECT AVG(occurences) FROM C);
-- INSERT: Scalaire
INSERT INTO courses (code, credits, name, semester, teacher)
VALUES ('420-0Q1-SW', 2, 'Système d''exploitation', 'H2022',
(SELECT employee_number FROM teachers ORDER BY birthday DESC LIMIT 1));
-- INSERT: Lignes
INSERT INTO courses (code, credits, name, teacher, semester)
SELECT code, credits, name, teacher,
CONCAT(LEFT(semester, 1), CAST(RIGHT(semester, 4) AS UNSIGNED) + 1)
FROM courses
WHERE RIGHT(semester, 4) = YEAR(CURDATE());
-- UPDATE: Scalaire
UPDATE courses
SET teacher = (SELECT employee_number FROM teachers ORDER BY birthday DESC LIMIT 1)
WHERE teacher = 3;
-- WHERE/HAVING(update, delete, select) : Scalaire
SELECT *
FROM teachers
WHERE employee_number = (SELECT employee_number FROM teachers ORDER BY birthday DESC LIMIT 1)
-- Cours que l'enseignant 1 a donné plus de fois que les autres enseignants
SELECT *, COUNT(code) AS `Nb fois donné`
FROM courses AS ParentCourses
WHERE teacher = 1
GROUP BY code -- -1 pour inclure si lui-même a donné le plus de fois, sinon on utilise le COUNT
HAVING `Nb fois donné` > (SELECT IF (C.teacher = ParentCourses.teacher, -1, COUNT(C.code)) AS nb
FROM courses C
WHERE C.code = ParentCourses.code
GROUP BY C.teacher -- nb fois que chaque ensignant a donné le cours
ORDER BY nb DESC -- Celui ayant donné le PLUS de fois
LIMIT 1);
-- Debug
SELECT code, teacher, COUNT(code) AS `Nb fois donné`
FROM courses
GROUP BY code, teacher;
-- IMBRIQUÉES: Plusieurs niveaux de sous-requetes
SELECT *
FROM teachers
WHERE employee_number = (SELECT teacher
FROM (SELECT teacher, COUNT(name) AS `nb cours`
FROM courses
GROUP BY teacher) AS T
ORDER BY `nb cours` DESC
LIMIT 1);
Les opérateurs IN, ALL, ANY, EXISTS permettent de vérifier l'appartenance ou la correspondance avec une sous-requête. Pratique pour récupérer des données dans une table selon la présence de données dans une autre table.
WHERE test_expr [NOT] IN (subquery)
-- Si la valeur EST PRÉSENTE dans la subquery
WHERE test_expr op ALL (subquery)
-- Si la comparaison op est vrai pour TOUTES les lignes
WHERE test_expr op ANY (subquery)
-- Si la comparaison op est vrai pour AU MOINS UNE ligne
WHERE [NOT] EXISTS (subquery)
-- Si subquery retourne AU MOINS UNE ligne
op est =, <>, <, <=, > ou >=
-- Enseignant qui n'a pas de cours
SELECT *
FROM teachers
WHERE employee_number NOT IN (SELECT DISTINCT teacher FROM courses);
-- Enseignants avec exactement 1 cours
-- = ANY equivaut à IN
SELECT *
FROM teachers
WHERE employee_number = ANY ( SELECT DISTINCT teacher
FROM courses
GROUP BY teacher HAVING COUNT(code) = 1);
-- Enseignants ayant au moins un cours
SELECT *
FROM teachers
WHERE EXISTS (SELECT code FROM courses WHERE teacher = teachers.employee_number);