Requêtes CTE SQLite

Introduction

Dans ce laboratoire, vous explorerez la puissance des expressions de table communes (CTE, Common Table Expressions) dans SQLite. Vous apprendrez à définir et à utiliser les CTE pour améliorer la lisibilité et la maintenabilité des requêtes. Vous commencerez par des CTE simples, puis vous passerez aux CTE récursives. À la fin de ce laboratoire, vous serez en mesure d'utiliser les CTE pour écrire du code SQL plus propre, plus efficace et plus facile à comprendre.

Skills Graph

Créer une base de données et une table

Dans cette étape, vous allez créer une base de données SQLite et une table employees (employés). Cette table stockera les informations des employés, que vous utiliserez dans les étapes ultérieures pour pratiquer les requêtes CTE.

Tout d'abord, ouvrez votre terminal dans la VM LabEx. Votre chemin d'accès par défaut est /home/labex/project.

Maintenant, créons une base de données SQLite nommée company.db. Exécutez la commande suivante pour créer le fichier de base de données et ouvrir l'outil de ligne de commande SQLite :

sqlite3 company.db

Vous verrez une invite indiquant que vous êtes maintenant dans l'interpréteur (shell) SQLite :

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

Ensuite, créez une table nommée employees pour stocker les informations de base des employés. Cette table aura quatre colonnes : id, name (nom), department (département) et salary (salaire). Entrez la commande SQL suivante à l'invite sqlite> et appuyez sur Entrée :

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department TEXT,
    salary INTEGER
);

Cette commande configure la table employees où :

• id est un entier qui sert de clé primaire pour chaque employé.
• name est un champ texte pour le nom de l'employé.
• department est un champ texte pour le département de l'employé.
• salary est un champ entier pour le salaire de l'employé.

Vous ne verrez aucune sortie si la commande s'exécute correctement.

Insérer des données dans la table

Maintenant que vous avez créé la table employees, ajoutons-y des données. Nous allons insérer cinq enregistrements d'employés dans la table.

Insérez cinq enregistrements d'employés dans la table employees en exécutant ces commandes une par une à l'invite sqlite> :

INSERT INTO employees (name, department, salary) VALUES ('Alice', 'Sales', 50000);
INSERT INTO employees (name, department, salary) VALUES ('Bob', 'Marketing', 60000);
INSERT INTO employees (name, department, salary) VALUES ('Charlie', 'Sales', 55000);
INSERT INTO employees (name, department, salary) VALUES ('David', 'Engineering', 70000);
INSERT INTO employees (name, department, salary) VALUES ('Eve', 'Marketing', 62000);

Ces commandes ajoutent cinq lignes à la table employees.

• INSERT INTO employees (name, department, salary) spécifie que vous insérez des données dans les colonnes name, department et salary de la table employees.
• VALUES ('Alice', 'Sales', 50000) fournit les valeurs à insérer pour chaque enregistrement.

Pour confirmer que les données ont été ajoutées correctement, exécutez cette commande pour afficher tous les enregistrements de la table :

SELECT * FROM employees;

Résultat attendu :

1|Alice|Sales|50000
2|Bob|Marketing|60000
3|Charlie|Sales|55000
4|David|Engineering|70000
5|Eve|Marketing|62000

Ce résultat affiche l'id, le name, le department et le salary pour chaque enregistrement. La commande SELECT * récupère toutes les colonnes de la table spécifiée.

Définir une CTE simple

Dans cette étape, vous apprendrez à définir et à utiliser une expression de table commune (Common Table Expression - CTE) simple dans SQLite. Les CTE sont des ensembles de résultats temporaires et nommés que vous pouvez référencer dans une seule instruction SQL. Elles sont utiles pour décomposer des requêtes complexes en parties plus petites et plus faciles à gérer, améliorant ainsi la lisibilité et la maintenabilité.

Une CTE est essentiellement une sous-requête nommée qui n'existe que pendant la durée d'une seule requête. Vous définissez une CTE à l'aide de la clause WITH, en lui donnant un nom et en spécifiant la requête qui génère l'ensemble de résultats. Vous pouvez ensuite référencer le nom de la CTE dans la requête principale comme s'il s'agissait d'une table ordinaire.

Syntaxe de base :

WITH
    cte_name AS (
        SELECT column1, column2
        FROM table1
        WHERE condition
    )
SELECT column1, column2
FROM cte_name
WHERE another_condition;

Définissons une CTE pour sélectionner les employés du département 'Sales' (Ventes). Exécutez l'instruction SQL suivante à l'invite sqlite> :

WITH
    SalesEmployees AS (
        SELECT id, name, salary
        FROM employees
        WHERE department = 'Sales'
    )
SELECT id, name, salary
FROM SalesEmployees
WHERE salary > 52000;

Cette requête définit d'abord une CTE nommée SalesEmployees qui sélectionne l'id, le name et le salary de tous les employés du département 'Sales'. Ensuite, la requête principale sélectionne l'id, le name et le salary de la CTE SalesEmployees où le salaire est supérieur à 52000.

Résultat attendu :

3|Charlie|55000

Cela montre que Charlie est le seul employé du département des ventes dont le salaire est supérieur à 52000.

Construire une CTE récursive

Dans cette étape, vous apprendrez à construire et à utiliser une expression de table commune (Common Table Expression - CTE) récursive dans SQLite. Les CTE récursives sont utilisées pour interroger des données hiérarchiques ou structurées en arborescence. Elles vous permettent de parcourir les relations au sein d'une table et de récupérer des données à différents niveaux de la hiérarchie.

Une CTE récursive est une CTE qui se réfère à elle-même. Elle se compose de deux parties :

• Membre d'ancrage (Anchor Member) : L'instruction SELECT initiale qui définit le cas de base ou le point de départ de la récursion.
• Membre récursif (Recursive Member) : Une instruction SELECT qui fait référence à la CTE elle-même. Cette partie effectue l'étape récursive, en s'appuyant sur les résultats de l'itération précédente.

Le membre d'ancrage et le membre récursif sont combinés à l'aide de l'opérateur UNION ALL. La récursion se poursuit jusqu'à ce que le membre récursif renvoie un ensemble de résultats vide.

Tout d'abord, créons une table nommée employees_hierarchy avec la structure suivante :

CREATE TABLE employees_hierarchy (
    id INTEGER PRIMARY KEY,
    name TEXT,
    manager_id INTEGER,
    title TEXT
);

Cette table représente la hiérarchie des employés, où manager_id fait référence à l'id du responsable de l'employé. Exécutez la commande ci-dessus à l'invite sqlite>.

Ensuite, insérez des exemples de données dans la table employees_hierarchy :

INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Alice', NULL, 'CEO');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Bob', 1, 'VP of Marketing');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Charlie', 1, 'VP of Sales');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('David', 2, 'Marketing Manager');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Eve', 3, 'Sales Manager');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Frank', 4, 'Marketing Specialist');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Grace', 5, 'Sales Representative');

Ici, Alice est la PDG (pas de responsable), Bob et Charlie rendent compte à Alice, David rend compte à Bob, Eve rend compte à Charlie, Frank rend compte à David et Grace rend compte à Eve. Exécutez les commandes ci-dessus à l'invite sqlite>.

Maintenant, construisons une CTE récursive pour récupérer toute la hiérarchie sous Alice (la PDG). Exécutez l'instruction SQL suivante :

WITH RECURSIVE
    EmployeeHierarchy(id, name, manager_id, title, level) AS (
        -- Anchor member: Select the CEO (Membre d'ancrage : Sélectionner le PDG)
        SELECT id, name, manager_id, title, 0 AS level
        FROM employees_hierarchy
        WHERE manager_id IS NULL

        UNION ALL

        -- Recursive member: Select employees reporting to the current level (Membre récursif : Sélectionner les employés qui rendent compte au niveau actuel)
        SELECT e.id, e.name, e.manager_id, e.title, eh.level + 1
        FROM employees_hierarchy e
        JOIN EmployeeHierarchy eh ON e.manager_id = eh.id
    )
SELECT id, name, title, level
FROM EmployeeHierarchy;

Cette requête définit une CTE récursive nommée EmployeeHierarchy. Le membre d'ancrage sélectionne le PDG (où manager_id est NULL). Le membre récursif joint la table employees_hierarchy à la CTE EmployeeHierarchy pour trouver les employés qui rendent compte aux employés sélectionnés au niveau précédent. La colonne level suit la profondeur dans la hiérarchie.

Résultat attendu :

1|Alice|CEO|0
2|Bob|VP of Marketing|1
3|Charlie|VP of Sales|1
4|David|Marketing Manager|2
5|Eve|Sales Manager|2
6|Frank|Marketing Specialist|3
7|Grace|Sales Representative|3

Cela montre toute la hiérarchie des employés, avec le level indiquant le niveau hiérarchique.

Intégrer des CTE dans des requêtes complexes

Dans cette étape, vous apprendrez à intégrer des CTE dans des requêtes plus complexes dans SQLite. Vous verrez comment utiliser plusieurs CTE dans une seule requête.

Vous pouvez définir plusieurs CTE dans une seule requête en les séparant par des virgules. Cela vous permet de décomposer une requête complexe en plusieurs étapes logiques, chacune représentée par une CTE.

Créons une nouvelle table nommée department_salaries avec la structure suivante :

CREATE TABLE department_salaries (
    department TEXT,
    total_salary INTEGER
);

Cette table stockera le salaire total pour chaque département. Exécutez la commande ci-dessus à l'invite sqlite>.

Maintenant, utilisons des CTE pour calculer le salaire total pour chaque département et insérer les résultats dans la table department_salaries. Exécutez l'instruction SQL suivante :

WITH
    DepartmentTotalSalaries AS (
        SELECT department, SUM(salary) AS total_salary
        FROM employees
        GROUP BY department
    )
INSERT INTO department_salaries (department, total_salary)
SELECT department, total_salary
FROM DepartmentTotalSalaries;

SELECT * FROM department_salaries;

Cette requête définit d'abord une CTE nommée DepartmentTotalSalaries qui calcule le salaire total pour chaque département à l'aide de la table employees. Ensuite, elle insère les résultats de la CTE DepartmentTotalSalaries dans la table department_salaries. Enfin, elle sélectionne toutes les données de la table department_salaries pour afficher les résultats.

Résultat attendu :

Sales|105000
Marketing|122000
Engineering|70000

Cela montre le salaire total pour chaque département.

Résumé

Dans ce labo, vous avez appris à définir et à utiliser les expressions de table communes (Common Table Expressions - CTE) dans SQLite. Vous avez commencé avec des CTE simples pour sélectionner des données dans une table, puis vous êtes passé aux CTE récursives pour interroger des données hiérarchiques. Enfin, vous avez appris à intégrer les CTE dans des requêtes plus complexes. Les CTE sont un outil puissant pour écrire du code SQL plus propre, plus efficace et plus facile à comprendre.