Sous-requêtes | Base de données

class: main-title

<h1>Base de données</h1>

<h2>Sous-requêtes</h2>

---

<ul>
<li>Sous-requêtes</li>
</ul>

---
class: section-title

<h1>BD de démonstration</h1>

---

<h1 class="slide-only">BD de démonstration</h1>

<pre><code class="language-sql">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())));

</code></pre>

---
class: section-title

<h1>Sous-requêtes</h1>

---

<h1 class="menu">Sous-requêtes</h1>

<p>Une sous-requête permet d'injecter un <code>SELECT</code> dans une autre requête(<em>select</em>, <em>insert</em>, <em>update</em>, <em>delete</em>) 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.</p>

<pre><code class="language-sql">-- 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;
</code></pre>

<p>📚 <a href="https://mariadb.com/kb/en/subqueries/" target="_blank" rel="noopener noreferrer">Documentation</a>
        
ℹ️ <a href="http://sqlrun.com/sql_chap08.html" target="_blank" rel="noopener noreferrer">Subqueries</a></p>

---

<h1 class="slide-only">Sous-requête <i>scalaire</i> vs <i>lignes</i></h1>

<p><strong>Scalaire</strong> Retourne 1 seule colonne ET 1 seule ligne, donc <strong>1 seule valeur</strong></p>

<ul>
<li>Expressions de colonne et conditions</li>
</ul>

<p>📚 <a href="https://mariadb.com/kb/en/subqueries-scalar-subqueries/" target="_blank" rel="noopener noreferrer">Scalar</a></p>

<p><strong>Lignes</strong> Retourne un ensemble de <strong>plusieurs colonnes ET/OU lignes</strong></p>

<ul>
<li>Tables dérivées et conditions avec opérateurs spécialisés</li>
</ul>

---

<h1 class="menu"><span class="slide-only">Sous-requête<br></span> FROM, Table dérivée</h1>

<pre><code class="language-sql">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 &gt; ( 
        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
</code></pre>

<p>📚 <a href="https://mariadb.com/kb/en/subqueries-in-a-from-clause/" target="_blank" rel="noopener noreferrer">Derived tables</a></p>

---

<h1 class="slide-only">Sous-requête<br>FROM, suite</h1>

<p>Essayons de réutiliser la <em>table dérivée</em> TeachersSummary</p>

<pre><code class="language-sql">SELECT  
    name,
    email,
    birthday,
    age
FROM  (
        SELECT name, email, birthday, (TIMESTAMPDIFF(YEAR, birthday, CURDATE())) AS `age`
        FROM teachers
      ) AS TeachersSummary
WHERE age &gt; ( SELECT AVG(age) FROM TeachersSummary ); -- Retirer la répétition
</code></pre>

<p>😢 <code>Table 'TeacherSummary' does not exists</code></p>

---

<h1 class="menu"><span class="slide-only">Sous-requête - </span>WITH</h1>

<p>😃 L'opérateur <a markdown="1" href="https://mariadb.com/kb/en/with/" target="_blank" rel="noopener noreferrer"><code>WITH</code></a> permet de créer une table temporaire accessible dans <strong>toute</strong> la requête subséquente. On peut préciser plusieurs tables temporaire avec l'opérateur <code>WITH</code>.</p>

<pre><code class="language-sql">WITH TeachersSummary AS (
    SELECT name, email, birthday, TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS `age`
    FROM teachers
)
SELECT *
FROM  TeachersSummary
WHERE age &gt; (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 &gt; (  SELECT AVG(TIMESTAMPDIFF(YEAR, birthday, CURDATE())) FROM teachers );
</code></pre>

---

<h1 class="menu"><span class="slide-only">Sous-requête - </span>WHERE</h1>

<pre><code class="language-sql">SELECT  
    name,
    email,
    birthday,
    TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS `Age`
FROM teachers
WHERE TIMESTAMPDIFF(YEAR, birthday, CURDATE()) &gt; (
        SELECT AVG(TIMESTAMPDIFF(YEAR, birthday, CURDATE()))
        FROM teachers
      );
</code></pre>

<p>Valide également dans un <code>HAVING</code></p>

---

<h1><span class="slide-only">Sous-requête - </span>Exemples</h1>

<pre><code class="language-sql">-- 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???
      &amp;&amp; RIGHT(semester, 4) = (SELECT current FROM Year);
</code></pre>

---

<h1 class="slide-only">Sous-requête - Exemples</h1>

<pre><code class="language-sql">-- 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 &gt;= (SELECT AVG(occurences) FROM C);
</code></pre>

---

<h1 class="slide-only">Sous-requête - Exemples</h1>

<pre><code class="language-sql">-- 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;
</code></pre>

---

<h1 class="slide-only">Sous-requête - Exemples</h1>

<pre><code class="language-sql">-- 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é` &gt; (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;
</code></pre>

---

<h1 class="slide-only">Sous-requête - Exemples</h1>

<pre><code class="language-sql">-- 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);
</code></pre>

---

<h1 class="menu"><span class="slide-only">Sous-requête - </span>Opérateurs</h1>

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

<pre><code class="language-sql">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 =, &lt;&gt;, &lt;, &lt;=, &gt; ou &gt;=
</code></pre>

---

<h1 class="slide-only">Sous-requête - Exemples</h1>

<pre><code class="language-sql">-- 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);
</code></pre>