Base de données 1

5.1- Sous-requêtes

BD de démonstration

classDiagram class teachers { employee_number name email birthday } class courses { code credits name description teacher semester }

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())));

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 donner des alias aux tables pour rendre explicite la manipulation des colonnes
select
    c.code,
    c.name,
    (select t.email from school.teachers as t where t.employee_number = c.teacher) as `courriel`
from school.courses as c;

-- Comment afficher aussi le NOM de l'enseignant?

📚 Documentation

ℹ️ SQL Run

Scalaire vs Lignes

Scalaire Retourne 1 seule colonne ET 1 seule ligne, donc 1 seule valeur

Expressions de colonne et conditions

📚 Scalar

Lignes Retourne un ensemble de plusieurs colonnes ET/OU lignes

Tables dérivées et conditions avec opérateurs spécialisés

📚 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
    ( 
        select 
            name, 
            timestampdiff(year, birthday, curdate()) as `age`
        from 
            school.teachers 
    ) Retirements
;


-- 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
    ) Retirements
-- VS la condition ici?
-- where `age` < 55
-- having `eligible?` = false
order by `nombres d'annee eligibilite` -- ?
limit 1; -- ?

📚 Derived tables

WHERE / HAVING

sql

copier

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

-- age de l'enseignant du cours
select
    code,
    timestampdiff(year, birthday, curdate()) as `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
having `teacher age` < 40
;

-- Conserver seulement le code

select
    -- distinct  -- ?
    code
from
    school.courses
where
    (select timestampdiff(year, birthday, curdate()) from school.teachers where employee_number = teacher) < 40
;


--
-- 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 ); -- retirer la répétition... :(

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.

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

sql

copier

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 >=

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
-- = ANY equivaut à IN

select *
from school.teachers
where employee_number = any ( 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);