Création de vues SQLite

Introduction

Dans ce laboratoire, nous explorerons la création de vues SQLite, en nous concentrant sur la construction de vues complexes, l'interrogation (querying) à travers celles-ci, la mise à jour via des vues modifiables (updatable views), et la suppression des vues obsolètes.

Nous commencerons par comprendre les vues comme des tables virtuelles basées sur les ensembles de résultats d'instructions SQL, utiles pour simplifier les requêtes et contrôler l'accès aux données. Le laboratoire vous guidera à travers la création de vues simples à partir de tables uniques, comme une table employees, puis progressera vers des vues plus complexes impliquant des jointures (joins) entre les tables, telles que la jointure des tables employees et departments. Vous apprendrez à interroger ces vues comme s'il s'agissait de tables régulières et explorerez les possibilités de mise à jour des données via des vues modifiables, ainsi que la manière de supprimer correctement les vues lorsqu'elles ne sont plus nécessaires.

Skills Graph

Créer des tables : employees et departments

Dans cette étape, vous allez créer deux tables, employees et departments, et insérer des exemples de données. Ces tables seront utilisées pour créer et interroger (query) des vues dans les étapes suivantes.

Tout d'abord, ouvrez l'interpréteur de commandes (shell) SQLite en exécutant la commande suivante dans le terminal :

sqlite3 /home/labex/project/employees.db

Cette commande ouvre l'interpréteur de commandes SQLite et se connecte à la base de données employees.db. Si le fichier de base de données n'existe pas, SQLite le créera.

Maintenant, créez la table employees avec l'instruction SQL suivante :

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    department TEXT,
    salary REAL
);

Cette instruction SQL crée une table nommée employees avec cinq colonnes : id, first_name, last_name, department et salary. La colonne id est la clé primaire (primary key) de la table.

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

INSERT INTO employees (first_name, last_name, department, salary) VALUES
('John', 'Doe', 'Sales', 50000.00),
('Jane', 'Smith', 'Marketing', 60000.00),
('Peter', 'Jones', 'Sales', 55000.00),
('Mary', 'Brown', 'IT', 70000.00);

Cette instruction SQL insère quatre lignes dans la table employees.

Maintenant, créez la table departments avec l'instruction SQL suivante :

CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT,
    location TEXT
);

Cette instruction SQL crée une table nommée departments avec trois colonnes : id, name et location. La colonne id est la clé primaire de la table.

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

INSERT INTO departments (name, location) VALUES
('Sales', 'New York'),
('Marketing', 'London'),
('IT', 'San Francisco');

Cette instruction SQL insère trois lignes dans la table departments.

Vous pouvez vérifier la création de la table et l'insertion des données en interrogeant les tables :

SELECT * FROM employees;
SELECT * FROM departments;

Ces commandes afficheront le contenu des tables employees et departments, respectivement.

Créer une vue simple

Dans cette étape, vous allez créer une vue simple appelée employee_info qui sélectionne des colonnes spécifiques de la table employees.

Une vue est une table virtuelle basée sur l'ensemble de résultats d'une instruction SQL. Elle simplifie les requêtes complexes et fournit un niveau d'abstraction.

Pour créer la vue employee_info, exécutez l'instruction SQL suivante dans l'interpréteur de commandes SQLite (SQLite shell) :

CREATE VIEW employee_info AS
SELECT id, first_name, last_name, department
FROM employees;

Cette instruction SQL crée une vue nommée employee_info qui sélectionne les colonnes id, first_name, last_name et department de la table employees.

Vous pouvez maintenant interroger (query) la vue comme s'il s'agissait d'une table :

SELECT * FROM employee_info;

Cette commande affichera le contenu de la vue employee_info, qui est un sous-ensemble des colonnes de la table employees.

Créer une vue complexe avec des jointures (Joins)

Dans cette étape, vous allez créer une vue plus complexe appelée employee_department_info qui joint (join) les tables employees et departments.

Joindre des tables vous permet de combiner des données provenant de plusieurs tables en fonction d'une colonne liée.

Pour créer la vue employee_department_info, exécutez l'instruction SQL suivante dans l'interpréteur de commandes SQLite (SQLite shell) :

CREATE VIEW employee_department_info AS
SELECT
    e.first_name,
    e.last_name,
    e.department,
    d.location
FROM
    employees e
JOIN
    departments d ON e.department = d.name;

Cette instruction SQL crée une vue nommée employee_department_info qui joint les tables employees et departments sur la colonne department. Elle sélectionne le prénom (first name) de l'employé, son nom (last name), son département et l'emplacement (location) du département.

Vous pouvez maintenant interroger (query) la vue pour voir les données combinées :

SELECT * FROM employee_department_info;

Cette commande affichera le contenu de la vue employee_department_info, montrant les informations sur les employés ainsi que l'emplacement de leur département.

Créer une vue avec des fonctions d'agrégation (Aggregate Functions)

Dans cette étape, vous allez créer une vue appelée department_salary_stats qui utilise des fonctions d'agrégation pour calculer le salaire moyen (average salary) pour chaque département.

Les fonctions d'agrégation effectuent des calculs sur un ensemble de valeurs et renvoient un seul résultat.

Pour créer la vue department_salary_stats, exécutez l'instruction SQL suivante dans l'interpréteur de commandes SQLite (SQLite shell) :

CREATE VIEW department_salary_stats AS
SELECT
    department,
    AVG(salary) AS average_salary
FROM
    employees
GROUP BY
    department;

Cette instruction SQL crée une vue nommée department_salary_stats qui calcule le salaire moyen pour chaque département en utilisant la fonction AVG et regroupe les résultats par département en utilisant la clause GROUP BY.

Vous pouvez maintenant interroger (query) la vue pour voir le salaire moyen pour chaque département :

SELECT * FROM department_salary_stats;

Cette commande affichera le contenu de la vue department_salary_stats, montrant le salaire moyen pour chaque département.

Mettre à jour des données via une vue modifiable (Updatable View)

Dans cette étape, vous allez mettre à jour des données dans la table employees via la vue employee_info.

Toutes les vues ne sont pas modifiables (updatable). Une vue est généralement modifiable si elle effectue une sélection (select) à partir d'une seule table et inclut la clé primaire (primary key) de cette table.

Tout d'abord, supprimons et recréons la vue employee_info pour nous assurer qu'elle inclut la colonne id, qui est la clé primaire de la table employees :

DROP VIEW IF EXISTS employee_info;

CREATE VIEW employee_info AS
SELECT id, first_name, last_name, department
FROM employees;

Maintenant, mettez à jour le département d'un employé avec id = 1 à 'HR' :

UPDATE employee_info
SET department = 'HR'
WHERE id = 1;

Cette instruction SQL met à jour la colonne department dans la table employees pour l'employé avec id = 1 via la vue employee_info.

Vous pouvez vérifier la mise à jour en interrogeant (query) directement la table employees :

SELECT * FROM employees WHERE id = 1;

Cette commande affichera la ligne dans la table employees avec id = 1, et vous devriez voir que la colonne department a été mise à jour à 'HR'.

Supprimer les vues obsolètes (Obsolete Views)

Dans cette étape, vous allez supprimer les vues que vous avez créées dans les étapes précédentes.

Au fur et à mesure que votre base de données évolue, certaines vues peuvent devenir obsolètes ou inutiles. Il est important de supprimer ces vues pour maintenir un schéma de base de données propre et efficace.

Pour supprimer une vue, utilisez l'instruction DROP VIEW. Par exemple, pour supprimer la vue employee_info, exécutez la commande suivante :

DROP VIEW IF EXISTS employee_info;

La clause IF EXISTS empêche une erreur si la vue n'existe pas.

Supprimez également les autres vues :

DROP VIEW IF EXISTS employee_department_info;
DROP VIEW IF EXISTS department_salary_stats;

Vous pouvez vérifier que les vues ont été supprimées en interrogeant (querying) la table sqlite_master :

SELECT name FROM sqlite_master WHERE type='view';

Cette commande devrait renvoyer un résultat vide, indiquant qu'il n'y a aucune vue dans la base de données.

Enfin, quittez l'interpréteur de commandes SQLite (SQLite shell) :

.exit

Cette commande ferme la connexion à la base de données employees.db et vous ramène au terminal Linux.

Résumé

Dans ce labo, vous avez appris à créer, interroger (query), mettre à jour et supprimer des vues (views) dans SQLite. Vous avez commencé par créer des vues simples basées sur une seule table, puis vous êtes passé à des vues plus complexes impliquant des jointures (joins) et des fonctions d'agrégation (aggregate functions). Vous avez également appris à mettre à jour des données via des vues modifiables (updatable views) et à supprimer les vues obsolètes pour maintenir un schéma de base de données propre. Ces compétences sont essentielles pour simplifier les requêtes complexes, contrôler l'accès aux données et gérer efficacement vos bases de données SQLite.