Criação de Views no SQLite

Introdução

Neste laboratório, exploraremos a criação de views (visões) no SQLite, focando na construção de views complexas, na realização de queries (consultas) através delas, na atualização de dados por meio de views atualizáveis e na remoção de views obsoletas.

Começaremos entendendo as views como tabelas virtuais baseadas em conjuntos de resultados de instruções SQL, úteis para simplificar queries e controlar o acesso aos dados. O laboratório irá guiá-lo através da criação de views simples a partir de tabelas únicas, como uma tabela employees, e então progredirá para views mais complexas envolvendo joins (junções) entre tabelas, como a junção das tabelas employees e departments. Você aprenderá como consultar essas views como se fossem tabelas regulares e explorará as possibilidades de atualizar dados através de views atualizáveis, bem como como remover corretamente as views quando elas não forem mais necessárias.

Criar Tabelas: funcionários e departamentos

Nesta etapa, você criará duas tabelas, employees e departments, e inserirá alguns dados de exemplo. Essas tabelas serão usadas para criar e consultar views nas etapas seguintes.

Primeiro, abra o shell do SQLite executando o seguinte comando no terminal:

sqlite3 /home/labex/project/employees.db

Este comando abre o shell do SQLite e conecta-se ao banco de dados employees.db. Se o arquivo do banco de dados não existir, o SQLite o criará.

Agora, crie a tabela employees com a seguinte instrução SQL:

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

Esta instrução SQL cria uma tabela chamada employees com cinco colunas: id, first_name, last_name, department e salary. A coluna id é a chave primária da tabela.

Em seguida, insira alguns dados de exemplo na tabela 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);

Esta instrução SQL insere quatro linhas na tabela employees.

Agora, crie a tabela departments com a seguinte instrução SQL:

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

Esta instrução SQL cria uma tabela chamada departments com três colunas: id, name e location. A coluna id é a chave primária da tabela.

Em seguida, insira alguns dados de exemplo na tabela departments:

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

Esta instrução SQL insere três linhas na tabela departments.

Você pode verificar a criação da tabela e a inserção de dados consultando as tabelas:

SELECT * FROM employees;
SELECT * FROM departments;

Esses comandos exibirão o conteúdo das tabelas employees e departments, respectivamente.

Criar uma View Simples

Nesta etapa, você criará uma view simples chamada employee_info que seleciona colunas específicas da tabela employees.

Uma view é uma tabela virtual baseada no conjunto de resultados de uma instrução SQL. Ela simplifica queries complexas e fornece um nível de abstração.

Para criar a view employee_info, execute a seguinte instrução SQL no shell do SQLite:

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

Esta instrução SQL cria uma view chamada employee_info que seleciona as colunas id, first_name, last_name e department da tabela employees.

Agora você pode consultar a view como se fosse uma tabela:

SELECT * FROM employee_info;

Este comando exibirá o conteúdo da view employee_info, que é um subconjunto das colunas na tabela employees.

Criar uma View Complexa com Joins

Nesta etapa, você criará uma view mais complexa chamada employee_department_info que junta as tabelas employees e departments.

Juntar tabelas permite combinar dados de múltiplas tabelas com base em uma coluna relacionada.

Para criar a view employee_department_info, execute a seguinte instrução SQL no shell do SQLite:

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;

Esta instrução SQL cria uma view chamada employee_department_info que junta as tabelas employees e departments na coluna department. Ela seleciona o primeiro nome do funcionário, o sobrenome, o departamento e a localização do departamento.

Agora você pode consultar a view para ver os dados combinados:

SELECT * FROM employee_department_info;

Este comando exibirá o conteúdo da view employee_department_info, mostrando informações do funcionário juntamente com a localização do seu departamento.

Criar uma View com Funções Agregadas

Nesta etapa, você criará uma view chamada department_salary_stats que usa funções agregadas para calcular o salário médio para cada departamento.

Funções agregadas realizam cálculos em um conjunto de valores e retornam um único resultado.

Para criar a view department_salary_stats, execute a seguinte instrução SQL no shell do SQLite:

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

Esta instrução SQL cria uma view chamada department_salary_stats que calcula o salário médio para cada departamento usando a função AVG e agrupa os resultados por departamento usando a cláusula GROUP BY.

Agora você pode consultar a view para ver o salário médio para cada departamento:

SELECT * FROM department_salary_stats;

Este comando exibirá o conteúdo da view department_salary_stats, mostrando o salário médio para cada departamento.

Atualizar Dados Através de uma Visão Atualizável no SQLite

Neste passo, você aprenderá sobre as limitações das visões (views) do SQLite e como criar visões verdadeiramente atualizáveis usando gatilhos (triggers) INSTEAD OF.

Importante: Por padrão, as visões do SQLite são somente leitura. Você não pode diretamente usar UPDATE, INSERT ou DELETE em dados através de uma visão. No entanto, você pode criar visões atualizáveis usando gatilhos INSTEAD OF.

Primeiro, vamos entender a limitação tentando uma atualização direta em nossa visão existente:

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

Isso falhará com um erro porque as visões do SQLite são somente leitura por padrão.

Para criar uma visão verdadeiramente atualizável, precisamos usar gatilhos INSTEAD OF. Vamos recriar a visão employee_info e adicionar um gatilho INSTEAD OF para atualizações:

DROP VIEW IF EXISTS employee_info;

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

Agora, crie um gatilho INSTEAD OF para lidar com operações UPDATE:

CREATE TRIGGER update_employee_info
INSTEAD OF UPDATE ON employee_info
BEGIN
    UPDATE employees
    SET first_name = NEW.first_name,
        last_name = NEW.last_name,
        department = NEW.department
    WHERE id = OLD.id;
END;

Agora você pode atualizar dados através da visão:

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

Esta instrução UPDATE agora funcionará porque o gatilho INSTEAD OF redireciona a atualização para a tabela employees subjacente.

Você pode verificar a atualização consultando a tabela employees diretamente:

SELECT * FROM employees WHERE id = 1;

Este comando exibirá a linha na tabela employees com id = 1, e você deverá ver que a coluna department foi atualizada para 'HR'.

Remover Views e Triggers Obsoletos

Nesta etapa, você removerá as views e triggers que criou nas etapas anteriores.

À medida que seu banco de dados evolui, algumas views e triggers podem se tornar desatualizadas ou desnecessárias. É importante remover esses objetos para manter um esquema de banco de dados limpo e eficiente.

Primeiro, remova a trigger INSTEAD OF:

DROP TRIGGER IF EXISTS update_employee_info;

Em seguida, remova as views. Para remover uma view, use a instrução DROP VIEW. Por exemplo, para remover a view employee_info, execute o seguinte comando:

DROP VIEW IF EXISTS employee_info;

A cláusula IF EXISTS impede um erro caso a view não exista.

Remova as outras views também:

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

Você pode verificar se as views foram removidas consultando a tabela sqlite_master:

SELECT name FROM sqlite_master WHERE type='view';

Este comando deve retornar um resultado vazio, indicando que não há views no banco de dados.

Finalmente, saia do shell SQLite:

.exit

Este comando fecha a conexão com o banco de dados employees.db e retorna você ao terminal Linux.

Resumo

Neste laboratório, você aprendeu como criar, consultar (query), atualizar e remover views no SQLite. Começou criando views simples baseadas em uma única tabela e, em seguida, progrediu para views mais complexas envolvendo joins e funções agregadas. Descobriu que as views do SQLite são somente leitura (read-only) por padrão, mas aprendeu como criar views verdadeiramente atualizáveis usando triggers INSTEAD OF. Também aprendeu como remover corretamente views e triggers obsoletos para manter um esquema de banco de dados limpo. Essas habilidades são essenciais para simplificar consultas complexas, controlar o acesso a dados e gerenciar seus bancos de dados SQLite de forma eficaz.