Techniques de sous-requêtes SQLite

Introduction

Dans ce laboratoire, vous explorerez les techniques de sous-requêtes SQLite pour améliorer vos capacités d'extraction et de filtrage de données. Vous apprendrez à utiliser les sous-requêtes dans la clause WHERE, à les intégrer dans l'instruction SELECT et à construire des sous-requêtes corrélées (correlated subqueries).

Créer des tables et insérer des données

Dans cette première étape, vous allez créer deux tables, departments et employees, et y insérer des exemples de données. Cela fournira les données dont vous avez besoin pour vous exercer à utiliser les sous-requêtes dans les étapes suivantes.

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

Pour commencer, connectez-vous à une base de données SQLite nommée company.db. Si la base de données n'existe pas, SQLite la créera pour vous. Exécutez la commande suivante :

sqlite3 company.db

Cette commande ouvre l'outil de ligne de commande SQLite et se connecte à la base de données company.db. Vous verrez l'invite sqlite>.

Maintenant, créez la table departments avec la commande SQL suivante :

CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    department_name TEXT,
    location TEXT
);

Cette commande crée une table nommée departments avec trois colonnes : department_id, department_name et location. La colonne department_id est la clé primaire (primary key) de cette table.

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

INSERT INTO departments (department_name, location) VALUES
('Sales', 'New York'),
('Marketing', 'Los Angeles'),
('Engineering', 'San Francisco');

Cette commande insère trois lignes dans la table departments, représentant trois départements différents et leurs emplacements.

Maintenant, créez la table employees avec la commande SQL suivante :

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    employee_name TEXT,
    department_id INTEGER,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Cette commande crée une table nommée employees avec trois colonnes : employee_id, employee_name et department_id. La colonne department_id est une clé étrangère (foreign key) qui fait référence à la colonne department_id de la table departments.

Enfin, insérez des exemples de données dans la table employees :

INSERT INTO employees (employee_name, department_id) VALUES
('Alice', 1),
('Bob', 2),
('Charlie', 1),
('David', 3);

Cette commande insère quatre lignes dans la table employees, représentant quatre employés différents et leurs ID de département.

Utiliser des sous-requêtes dans la clause WHERE

Dans cette étape, vous apprendrez à utiliser des sous-requêtes dans la clause WHERE pour filtrer les résultats en fonction de la sortie d'une autre requête.

Une sous-requête (subquery) est une instruction SELECT imbriquée dans une autre instruction SQL. Dans ce cas, vous utiliserez une sous-requête pour sélectionner les valeurs department_id de la table departments, puis vous utiliserez ces valeurs pour filtrer les résultats d'une requête sur la table employees.

Trouvons tous les employés qui travaillent dans des départements situés à 'New York'. Pour ce faire, vous devrez d'abord trouver le department_id des départements situés à 'New York', puis trouver tous les employés avec ce department_id.

Entrez la commande SQL suivante dans l'invite sqlite> :

SELECT employee_name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

Cette commande sélectionne le employee_name de la table employees où le department_id se trouve dans la liste des valeurs department_id renvoyées par la sous-requête. La sous-requête sélectionne le department_id de la table departments où la location est 'New York'.

Après avoir exécuté la commande, vous devriez voir la sortie suivante :

Alice
Charlie

Cette sortie affiche les noms des employés qui travaillent dans le département des ventes (Sales), qui est situé à New York.

Intégrer des sous-requêtes dans la clause SELECT

Dans cette étape, vous apprendrez à intégrer des sous-requêtes dans la clause SELECT d'une instruction SQL pour récupérer des données associées.

L'intégration d'une sous-requête dans la clause SELECT vous permet de récupérer une seule valeur pour chaque ligne de la requête externe. Cette valeur est souvent une valeur calculée ou une valeur associée provenant d'une autre table.

Récupérons le nom de chaque employé ainsi que le nom de son département. Entrez la commande SQL suivante dans l'invite sqlite> :

SELECT employee_name, (SELECT department_name FROM departments WHERE department_id = employees.department_id) AS department_name FROM employees;

Cette commande sélectionne le employee_name de la table employees et inclut également une sous-requête qui récupère le department_name de la table departments. La sous-requête utilise le department_id de la table employees pour faire correspondre le département correct. Le résultat de la sous-requête est renommé (aliased) en tant que department_name.

Après avoir exécuté la commande, vous devriez voir la sortie suivante :

Alice|Sales
Bob|Marketing
Charlie|Sales
David|Engineering

Cette sortie affiche le nom de chaque employé et le nom de son département correspondant.

Construire des sous-requêtes corrélées

Dans cette étape, vous apprendrez à construire des sous-requêtes corrélées (correlated subqueries). Les sous-requêtes corrélées sont des sous-requêtes qui font référence à une colonne de la requête externe. Cela signifie que la sous-requête est exécutée une fois pour chaque ligne de la requête externe.

Contrairement aux sous-requêtes simples qui sont exécutées une seule fois et dont le résultat est utilisé par la requête externe, les sous-requêtes corrélées dépendent de la requête externe pour leurs valeurs. Elles sont utilisées lorsque vous devez comparer des valeurs dans la sous-requête à des valeurs dans la ligne actuelle de la requête externe.

Trouvons tous les employés qui travaillent dans un département situé dans la même ville que le nom de l'employé. Pour que cela fonctionne, nous allons d'abord mettre à jour les noms des employés pour qu'ils soient des noms de villes.

UPDATE employees SET employee_name = 'New York' WHERE employee_name = 'Alice';
UPDATE employees SET employee_name = 'Los Angeles' WHERE employee_name = 'Bob';
UPDATE employees SET employee_name = 'New York' WHERE employee_name = 'Charlie';
UPDATE employees SET employee_name = 'San Francisco' WHERE employee_name = 'David';

Ces commandes mettent à jour la colonne employee_name dans la table employees avec des noms de villes.

Maintenant, écrivons la sous-requête corrélée :

SELECT e.employee_name FROM employees e WHERE e.department_id IN (SELECT d.department_id FROM departments d WHERE d.location = e.employee_name);

Cette commande sélectionne le employee_name de la table employees (renommée e) où le department_id se trouve dans la liste des valeurs department_id renvoyées par la sous-requête. La sous-requête sélectionne le department_id de la table departments (renommée d) où la location correspond au employee_name de la requête externe.

Après avoir exécuté la commande, vous devriez voir la sortie suivante :

New York
Los Angeles
San Francisco

Cette sortie affiche les noms des employés (maintenant des noms de villes) qui travaillent dans des départements situés dans la même ville.

Évaluer l'efficacité des sous-requêtes avec JOIN

Dans cette étape, vous apprendrez à évaluer l'efficacité des sous-requêtes et à explorer des approches alternatives utilisant des opérations JOIN pour l'optimisation.

Bien que les sous-requêtes soient puissantes, elles peuvent parfois entraîner des goulots d'étranglement (performance bottlenecks), en particulier avec de grands ensembles de données (datasets). Dans de nombreux cas, vous pouvez réécrire les sous-requêtes en utilisant des opérations JOIN, qui peuvent être plus efficaces.

Réécrivons la sous-requête corrélée de l'étape précédente en utilisant une JOIN :

SELECT e.employee_name FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE d.location = e.employee_name;

Cette commande sélectionne le employee_name de la table employees (renommée e) et la joint à la table departments (renommée d) sur la colonne department_id. La clause WHERE filtre ensuite les résultats pour n'inclure que les lignes où la location dans la table departments correspond au employee_name dans la table employees.

Pour vérifier le résultat, exécutez la commande. Vous devriez voir la même sortie que dans l'étape précédente :

New York
Los Angeles
San Francisco

Pour évaluer l'efficacité, vous utiliseriez généralement EXPLAIN QUERY PLAN avant et après la modification. Cependant, en raison des limitations de l'environnement LabEx, nous ne pouvons pas démontrer pleinement la commande EXPLAIN QUERY PLAN. L'essentiel à retenir est que les opérations JOIN sont souvent plus efficaces que les sous-requêtes corrélées, en particulier pour les ensembles de données plus volumineux.

Enfin, quittez l'interpréteur (shell) sqlite3 :

.exit

Cela vous ramènera à l'invite bash.

Résumé

Dans ce labo, vous avez appris à utiliser les sous-requêtes SQLite pour améliorer vos capacités de récupération et de filtrage de données. Vous vous êtes entraîné à utiliser des sous-requêtes dans la clause WHERE, à les intégrer dans l'instruction SELECT et à construire des sous-requêtes corrélées. Vous avez également appris à réécrire des sous-requêtes en utilisant des opérations JOIN pour une meilleure efficacité. Ces techniques vous fournissent des outils puissants pour travailler avec des données dans SQLite.