James Hoffman

Techniques de l'Informatique - Cégep de Shawinigan

Menu

Base de données 1

Base de données 1

Laboratoire 2

5.1- Sous-requêtes

5.2- Exercices

BD de démonstration

sql
copier
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', '1965-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()))),
('420-0SU-SW', 1.33, 'Web: Client 1', null, 2, concat('A', year(curdate())));

Sous-requêtes

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 un mécanisme puissant pour récupérer des données adjacentes dans la même table ou dans une autre table.

sql
copier
-- Liste des cours, en affichant le courriel de contact de l'enseignant du cours
select
code,
name,
(select email from school.teachers where employee_number = teacher) as `courriel`
from school.courses;

-- on peut utiliser le nom complet de la table
-- OU
-- donner des alias aux tables
-- pour rendre explicite la manipulation des colonnes
select
courses.code,
courses.name,
(select t.email from school.teachers as t where t.employee_number = courses.teacher) as `courriel`
from school.courses;
Comment afficher aussi le NOM de l'enseignant?
sql
copier
select
courses.code,
courses.name,
(select t.email from school.teachers as t where t.employee_number = courses.teacher) as `courriel`,
(select t.name from school.teachers as t where t.employee_number = courses.teacher) as `nom`
from school.courses;

📚 Documentation

ℹ️ SQL Run

Scalaire vs Lignes

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

FROM, Table dérivée

Permets d'utilise le résultat d'un select pour alimenter les données, from, de la requête. Particulièrement intéressant pour utiliser le résultat d'un calcul plusieurs fois sans répéter l'expression complète.

sql
copier
--
-- Le nom, l'age des enseignants, leur éligibilité à la retraite et le nombre d'années avant l'éligibilité à la retraite
-- sachant qu'un enseignant est éligible à partir de 55 ans
--
-- +---------+------+-----------+-----------------------------+
-- | name | age | eligible? | nombres d'annee eligibilite |
-- +---------+------+-----------+-----------------------------+
-- | James | 34 | 0 | 21 |
-- | Mathieu | 44 | 0 | 11 |
-- | Stevens | 59 | 1 | -4 |
-- | Marco | 49 | 0 | 6 |
-- | Lyne | 39 | 0 | 16 |
-- | Nicolas | 36 | 0 | 19 |
-- +---------+------+-----------+-----------------------------+

-- age des enseignants
select
name,
timestampdiff(year, birthday, curdate()) as `age`
from
school.teachers;

-- calcul des statistiques
select
name,
timestampdiff(year, birthday, curdate()) as `age`,
(55 - timestampdiff(year, birthday, curdate()) <= 0) as `eligible?`,
(55 - timestampdiff(year, birthday, curdate())) as `nombres d'annee eligibilite`
from
school.teachers;


-- reutilisation du calcul
select
*,
((55 - `age`) <= 0) as `eligible?`,
(55 - `age`) as `nombres d'annee eligibilite`
-- autre reutilisations?
from
(
-- Sous-requete alimente le FROM
select
name,
timestampdiff(year, birthday, curdate()) as `age`
from
school.teachers
) Retirements
;


-- Trouver LE prochain eligible
select
*,
((55 - `age`) <= 0) as `eligible?`,
(55 - `age`) as `nombres d'annee eligibilite`
from
(
select
name,
timestampdiff(year, birthday, curdate()) as `age`
from
school.teachers
having `age` < 55 -- Pre-filtrer les prochains eligible
-- Mais having non-standard SQL :(
) Retirements
-- where `age` < 55 -- ici AGE disponible :)
order by `quel colonne?` -- Age?, Annee eligibilite?, Birthday?
limit 1;

📚 Derived tables

WHERE / HAVING

sql
copier
--
-- Le CODE des cours dont l'enseignant a moins de 40 ans
--

-- age des enseignants
select
*,
timestampdiff(year, birthday, curdate()) as `age`
from
school.teachers
;

-- age de l'enseignant du cours
select
code,
(select timestampdiff(year, birthday, curdate()) from school.teachers where employee_number = teacher) as `teacher age`
from
school.courses
;

-- les cours avec enseignant de moins de 40 ans
select
code,
(select timestampdiff(year, birthday, curdate()) from school.teachers where employee_number = teacher) as `teacher age`
from
school.courses
where
(select timestampdiff(year, birthday, curdate()) from school.teachers where employee_number = teacher) < 40
-- having `teacher age`?
-- Comment eviter le having et rendre la colonne disponible dans le where?
-- >> Sous-requete!
;

-- Conserver seulement les codes differents

select distinct
code
from
school.courses
where
(select timestampdiff(year, birthday, curdate()) from school.teachers where employee_number = teacher) < 40
;
sql
copier
--
-- Les enseignants(*, age) plus vieux que la moyenne
--

-- age des enseignants
select
name, email, birthday, (timestampdiff(year, birthday, curdate())) as `age`
from
school.teachers
;

-- moyenne d'age des enseignants
select
avg(timestampdiff(year, birthday, curdate())) as `average age`
from
school.teachers
;

select
name, email, birthday, age
from (
select *, (timestampdiff(year, birthday, curdate())) as `age`
from school.teachers
) as TeachersSummary
where age > (
-- On doit calculer l'age encore... :(
select avg(timestampdiff(year, birthday, curdate()))
from school.teachers
)
;

WITH

sql
copier
-- 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
school.teachers
) as TeachersSummary
where age > ( select avg(age) from TeachersSummary ); -- ... :(

L'opérateur WITH permet de créer une expression de table, Common Table Expression CTE, accessible dans toute la requête subséquente. On peut préciser plusieurs tables temporaire avec l'opérateur WITH.

sql
copier

-- :D
with TeachersSummary as (
select
name, email, birthday, timestampdiff(year, birthday, curdate()) as `age`
from school.teachers
)
select
*
from
TeachersSummary
where
age > (select avg(age) from TeachersSummary)
;

Exemples

sql
copier
-- 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 school.courses;
sql
copier
-- 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 school.courses
where left(semester, 1) = (select current from season limit 1) -- limit 1 requis???
&& right(semester, 4) = (select current from year)
;
sql
copier
-- 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 school.courses
group by code
) as c
;

--
-- Condition d'une aggrégation sur une aggrégation
--

with c as (
select *, count(name) as `occurences`
from school.courses
group by code
)
select *
from c
where occurences >= (select avg(occurences) from c);
sql
copier
-- INSERT: Scalaire

insert into school.courses (code, credits, name, semester, teacher)
values ('420-0Q1-SW', 2, 'Système d''exploitation', 'H2022',
(select employee_number from school.teachers order by birthday desc limit 1));
sql
copier
-- INSERT: Lignes

insert into school.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());
sql
copier
-- UPDATE: Scalaire

update school.courses
set teacher = (select employee_number from teachers order by birthday desc limit 1)
where teacher = 3;
sql
copier
-- WHERE/HAVING(update, delete, select) : Scalaire

select *
from school.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 school.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 school.courses
group by code, teacher;
sql
copier
-- IMBRIQUÉES: Plusieurs niveaux de sous-requetes

select *
from school.teachers
where employee_number = (select teacher
from (select teacher, count(name) as `nb cours`
from school.courses
group by teacher) as t
order by `nb cours` desc
limit 1);

Opérateurs

Les opérateurs IN, EXISTS, ALL, ANY 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.

sql
copier
WHERE test_expr [NOT] IN (subquery)
-- Si la valeur EST PRÉSENTE dans la subquery

WHERE [NOT] EXISTS (subquery)
-- Si subquery retourne AU MOINS UNE ligne

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

op est =, <>, <, <=, > ou >=
sql
copier
--
-- Enseignant qui n'a pas de cours

select *
from school.teachers
where employee_number not in (select distinct school.teacher from courses);


--
-- Enseignants avec exactement 1 cours

select *
from school.teachers
where employee_number in ( select distinct teacher
from school.courses
group by teacher having count(code) = 1);

--
-- Enseignants ayant au moins un cours
--

select *
from school.teachers
where exists (select code from school.courses where teacher = teachers.employee_number);

Équivalences

ANY

  • X = ANY

    • IN

  • A > ANY

    • > MIN

  • A < ANY

    • < MAX

  • A <> ANY(B)

    • Au moins une valeur différente
    • A <> ANY(B, B, B)
    • A <> ANY(B, C, D) toujours VRAI

ALL

  • A <> ALL

    • NOT IN

  • A > ALL

    • > MAX

  • A < ALL

    • < MIN

  • A = ALL(B)

    • Toutes les valeurs correspondent
    • A = ALL(B, B, B)
    • A = ALL(B, C, D) toujours FAUX
sql
copier
--
-- Les enseignants et leurs sessions moins chargees que la moyenne
-- Charge d'une session est la somme des credits
-- Donc, moyenne des sommes, puis on identifies les "petites" sessions

-- Charge de chaque session, pour chaque ensignant
select
sum(credits) as `charge session`,
teacher,
semester
from
courses
group by teacher, semester
;

-- Moyenne de la charge des sessions de chaque enseignant
select
teacher,
avg(`charge session`) as `charge moyenne`
from (
select
sum(credits) as `charge session`,
teacher
from
courses
group by teacher, semester
) TeachersCredits
group by teacher
;


-- La plus petit moyenne

select
teacher,
min(avg(`charge session`)) as `charge moyenne` -- :(
from (
select
sum(credits) as `charge session`,
teacher
from
courses
group by teacher, semester
) TeachersCredits
group by teacher
;

-- Premiere option, une autre sous-requete

select
min(`charge moyenne`) as `charge minimale`
from (
select
teacher,
avg(`charge session`) as `charge moyenne`
from (
select
sum(credits) as `charge session`,
teacher
from
courses
group by teacher, semester
) TeachersCredits
group by teacher
) MinimumSemester
;

-- et l'integrer au having
select
teacher,
sum(credits) as `charge session`
from
courses
group by teacher, semester
having `charge session` <= (
select
min(`charge moyenne`) as `charge minimale`
from (
select
teacher,
avg(`charge session`) as `charge moyenne`
from (
select
sum(credits) as `charge session`,
teacher
from
courses
group by teacher, semester
) TeachersCredits
group by teacher
) MinimumSemester
)

-- Extraire avec with?

-- OU, deuxieme option

select
teacher,
sum(credits) as `charge session`
from
courses
group by teacher, semester
having `charge session` <= all ( -- <= MIN
select
avg(`charge session`) as `charge moyenne`
from (
select
sum(credits) as `charge session`,
teacher
from
courses
group by teacher, semester
) TeachersCredits
group by teacher
)

-- Et un peu d'extraction

with CreditsSums as (
select
teacher,
sum(credits) as `charge session`
from
courses
group by teacher, semester
)
select
*
from
CreditsSums
having `charge session` <= all ( -- <= MIN
select
avg(`charge session`) as `charge moyenne`
from (
select
*
from
CreditsSums
) CreditsAvg
group by teacher
)