Neste laboratório, você explorará o poder das Expressões de Tabela Comum (CTEs - Common Table Expressions) em SQLite. Aprenderá como definir e usar CTEs para aprimorar a legibilidade e a manutenibilidade das consultas. Começará com CTEs simples e, em seguida, passará para CTEs recursivas. Ao final deste laboratório, você será capaz de usar CTEs para escrever código SQL mais limpo, eficiente e fácil de entender.
Nesta etapa, você criará um banco de dados SQLite e uma tabela employees. Esta tabela armazenará informações dos funcionários, que você usará nas etapas posteriores para praticar consultas CTE.
Primeiro, abra seu terminal na VM do LabEx. Seu caminho padrão é /home/labex/project.
Agora, vamos criar um banco de dados SQLite chamado company.db. Execute o seguinte comando para criar o arquivo do banco de dados e abrir a ferramenta de linha de comando SQLite:
sqlite3 company.dbVocê verá um prompt indicando que agora está dentro do shell SQLite:
SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>Em seguida, crie uma tabela chamada employees para armazenar informações básicas dos funcionários. Esta tabela terá quatro colunas: id, name, department e salary. Digite o seguinte comando SQL no prompt sqlite> e pressione Enter:
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT,
department TEXT,
salary INTEGER
);Este comando configura a tabela employees onde:
id é um inteiro que serve como chave primária para cada funcionário.name é um campo de texto para o nome do funcionário.department é um campo de texto para o departamento do funcionário.salary é um campo inteiro para o salário do funcionário.Você não verá nenhuma saída se o comando for executado com sucesso.
Agora que você criou a tabela employees, vamos adicionar alguns dados a ela. Inseriremos cinco registros de funcionários na tabela.
Insira cinco registros de funcionários na tabela employees executando estes comandos um por um no prompt 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);Esses comandos adicionam cinco linhas à tabela employees.
INSERT INTO employees (name, department, salary) especifica que você está inserindo dados nas colunas name, department e salary da tabela employees.VALUES ('Alice', 'Sales', 50000) fornece os valores a serem inseridos para cada registro.Para confirmar se os dados foram adicionados corretamente, execute este comando para visualizar todos os registros na tabela:
SELECT * FROM employees;Saída Esperada:
1|Alice|Sales|50000
2|Bob|Marketing|60000
3|Charlie|Sales|55000
4|David|Engineering|70000
5|Eve|Marketing|62000Esta saída mostra o id, name, department e salary para cada registro. O comando SELECT * recupera todas as colunas da tabela especificada.
Nesta etapa, você aprenderá como definir e usar uma Expressão de Tabela Comum (CTE - Common Table Expression) simples em SQLite. CTEs são conjuntos de resultados temporários e nomeados que você pode referenciar dentro de uma única instrução SQL. Eles são úteis para dividir consultas complexas em partes menores e mais gerenciáveis, melhorando a legibilidade e a capacidade de manutenção.
Um CTE é essencialmente uma subconsulta nomeada que existe apenas durante a duração de uma única consulta. Você define um CTE usando a cláusula WITH, dando-lhe um nome e especificando a consulta que gera o conjunto de resultados. Você pode então referenciar o nome do CTE na consulta principal como se fosse uma tabela regular.
Sintaxe Básica:
WITH
cte_name AS (
SELECT column1, column2
FROM table1
WHERE condition
)
SELECT column1, column2
FROM cte_name
WHERE another_condition;Vamos definir um CTE para selecionar funcionários do departamento de 'Vendas'. Execute a seguinte instrução SQL no prompt sqlite>:
WITH
SalesEmployees AS (
SELECT id, name, salary
FROM employees
WHERE department = 'Sales'
)
SELECT id, name, salary
FROM SalesEmployees
WHERE salary > 52000;Esta consulta primeiro define um CTE chamado SalesEmployees que seleciona o id, name e salary de todos os funcionários do departamento de 'Vendas'. Em seguida, a consulta principal seleciona o id, name e salary do CTE SalesEmployees onde o salário é maior que 52000.
Saída Esperada:
3|Charlie|55000Isso mostra que Charlie é o único funcionário no departamento de Vendas com um salário maior que 52000.
Nesta etapa, você aprenderá como construir e usar uma Expressão de Tabela Comum (CTE - Common Table Expression) recursiva em SQLite. CTEs recursivos são usados para consultar dados hierárquicos ou em estrutura de árvore. Eles permitem que você percorra relacionamentos dentro de uma tabela e recupere dados em diferentes níveis da hierarquia.
Um CTE recursivo é um CTE que se refere a si mesmo. Ele consiste em duas partes:
SELECT inicial que define o caso base ou ponto de partida da recursão.SELECT que se refere ao próprio CTE. Esta parte executa a etapa recursiva, construindo sobre os resultados da iteração anterior.O membro âncora e o membro recursivo são combinados usando o operador UNION ALL. A recursão continua até que o membro recursivo retorne um conjunto de resultados vazio.
Primeiro, vamos criar uma tabela chamada employees_hierarchy com a seguinte estrutura:
CREATE TABLE employees_hierarchy (
id INTEGER PRIMARY KEY,
name TEXT,
manager_id INTEGER,
title TEXT
);Esta tabela representa a hierarquia de funcionários, onde manager_id se refere ao id do gerente do funcionário. Execute o comando acima no prompt sqlite>.
Em seguida, insira alguns dados de exemplo na tabela 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');Aqui, Alice é a CEO (sem gerente), Bob e Charlie reportam-se a Alice, David reporta-se a Bob, Eve reporta-se a Charlie, Frank reporta-se a David e Grace reporta-se a Eve. Execute os comandos acima no prompt sqlite>.
Agora, vamos construir um CTE recursivo para recuperar toda a hierarquia sob Alice (a CEO). Execute a seguinte instrução SQL:
WITH RECURSIVE
EmployeeHierarchy(id, name, manager_id, title, level) AS (
-- Anchor member: Select the CEO
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
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;Esta consulta define um CTE recursivo chamado EmployeeHierarchy. O membro âncora seleciona a CEO (onde manager_id é NULL). O membro recursivo junta a tabela employees_hierarchy com o CTE EmployeeHierarchy para encontrar funcionários que se reportam aos funcionários selecionados no nível anterior. A coluna level acompanha a profundidade na hierarquia.
Saída Esperada:
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|3Isso mostra toda a hierarquia de funcionários, com o level indicando o nível de reporte.
Nesta etapa, você aprenderá como integrar CTEs em consultas mais complexas em SQLite. Você verá como usar múltiplos CTEs dentro de uma única consulta.
Você pode definir múltiplos CTEs em uma única consulta separando-os por vírgulas. Isso permite que você divida uma consulta complexa em várias etapas lógicas, cada uma representada por um CTE.
Vamos criar uma nova tabela chamada department_salaries com a seguinte estrutura:
CREATE TABLE department_salaries (
department TEXT,
total_salary INTEGER
);Esta tabela armazenará o salário total para cada departamento. Execute o comando acima no prompt sqlite>.
Agora, vamos usar CTEs para calcular o salário total para cada departamento e inserir os resultados na tabela department_salaries. Execute a seguinte instrução SQL:
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;Esta consulta primeiro define um CTE chamado DepartmentTotalSalaries que calcula o salário total para cada departamento usando a tabela employees. Em seguida, ela insere os resultados do CTE DepartmentTotalSalaries na tabela department_salaries. Finalmente, ela seleciona todos os dados da tabela department_salaries para exibir os resultados.
Saída Esperada:
Sales|105000
Marketing|122000
Engineering|70000Isso mostra o salário total para cada departamento.
Neste laboratório, você aprendeu como definir e usar Expressões de Tabela Comum (CTEs - Common Table Expressions) em SQLite. Você começou com CTEs simples para selecionar dados de uma tabela, depois passou para CTEs recursivos para consultar dados hierárquicos. Finalmente, você aprendeu como integrar CTEs em consultas mais complexas. CTEs são uma ferramenta poderosa para escrever código SQL mais limpo, mais eficiente e mais fácil de entender.
