Técnicas de Subconsulta SQLite

Introdução

Neste laboratório, você explorará as técnicas de subconsulta do SQLite para aprimorar suas capacidades de recuperação e filtragem de dados. Você aprenderá como usar subconsultas dentro da cláusula WHERE, incorporá-las na instrução SELECT e construir subconsultas correlacionadas.

Criar Tabelas e Inserir Dados

Neste primeiro passo, você criará duas tabelas, departments e employees, e inserirá alguns dados de exemplo nelas. Isso fornecerá os dados necessários para praticar o uso de subconsultas nos passos seguintes.

Primeiro, abra seu terminal na VM do LabEx. Seu caminho padrão é /home/labex/project.

Para começar, conecte-se a um banco de dados SQLite chamado company.db. Se o banco de dados não existir, o SQLite o criará para você. Execute o seguinte comando:

sqlite3 company.db

Este comando abre a ferramenta de linha de comando do SQLite e se conecta ao banco de dados company.db. Você verá o prompt sqlite>.

Agora, crie a tabela departments com o seguinte comando SQL:

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

Este comando cria uma tabela chamada departments com três colunas: department_id, department_name e location. A coluna department_id é a chave primária para esta tabela.

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

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

Este comando insere três linhas na tabela departments, representando três departamentos diferentes e suas localizações.

Agora, crie a tabela employees com o seguinte comando SQL:

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

Este comando cria uma tabela chamada employees com três colunas: employee_id, employee_name e department_id. A coluna department_id é uma chave estrangeira (foreign key) que referencia a coluna department_id na tabela departments.

Finalmente, insira alguns dados de exemplo na tabela employees:

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

Este comando insere quatro linhas na tabela employees, representando quatro funcionários diferentes e seus IDs de departamento.

Usar Subconsultas na Cláusula WHERE

Neste passo, você aprenderá como usar subconsultas na cláusula WHERE para filtrar resultados com base na saída de outra consulta.

Uma subconsulta é uma instrução SELECT aninhada dentro de outra instrução SQL. Neste caso, você usará uma subconsulta para selecionar os valores de department_id da tabela departments e, em seguida, usará esses valores para filtrar os resultados de uma consulta na tabela employees.

Vamos encontrar todos os funcionários que trabalham em departamentos localizados em 'New York'. Para fazer isso, você precisará primeiro encontrar o department_id para os departamentos em 'New York' e, em seguida, encontrar todos os funcionários com esse department_id.

Digite o seguinte comando SQL no prompt sqlite>:

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

Este comando seleciona o employee_name da tabela employees onde o department_id está na lista de valores de department_id retornados pela subconsulta. A subconsulta seleciona o department_id da tabela departments onde a location é 'New York'.

Após executar o comando, você deverá ver a seguinte saída:

Alice
Charlie

Esta saída mostra os nomes dos funcionários que trabalham no departamento de Vendas, que está localizado em Nova York.

Incorporar Subconsultas na Cláusula SELECT

Neste passo, você aprenderá como incorporar subconsultas dentro da cláusula SELECT de uma instrução SQL para recuperar dados relacionados.

Incorporar uma subconsulta na cláusula SELECT permite que você recupere um único valor para cada linha na consulta externa. Este valor é frequentemente um valor calculado ou um valor relacionado de outra tabela.

Vamos recuperar o nome de cada funcionário junto com o nome de seu departamento. Digite o seguinte comando SQL no prompt sqlite>:

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

Este comando seleciona o employee_name da tabela employees e também inclui uma subconsulta que recupera o department_name da tabela departments. A subconsulta usa o department_id da tabela employees para corresponder ao departamento correto. O resultado da subconsulta é apelidado (aliased) como department_name.

Após executar o comando, você deverá ver a seguinte saída:

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

Esta saída mostra o nome de cada funcionário e o nome de seu departamento correspondente.

Construir Subconsultas Correlacionadas

Neste passo, você aprenderá como construir subconsultas correlacionadas. Subconsultas correlacionadas são subconsultas que se referem a uma coluna da consulta externa. Isso significa que a subconsulta é executada uma vez para cada linha da consulta externa.

Ao contrário das subconsultas simples que são executadas uma vez e seu resultado é usado pela consulta externa, as subconsultas correlacionadas dependem da consulta externa para seus valores. Elas são usadas quando você precisa comparar valores dentro da subconsulta com valores na linha atual da consulta externa.

Vamos encontrar todos os funcionários que trabalham em um departamento localizado na mesma cidade que o nome do funcionário. Para que isso funcione, primeiro atualizaremos os nomes dos funcionários para serem nomes de cidades.

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';

Esses comandos atualizam a coluna employee_name na tabela employees para nomes de cidades.

Agora, vamos escrever a subconsulta correlacionada:

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);

Este comando seleciona o employee_name da tabela employees (apelidada como e) onde o department_id está na lista de valores de department_id retornados pela subconsulta. A subconsulta seleciona o department_id da tabela departments (apelidada como d) onde a location corresponde ao employee_name da consulta externa.

Após executar o comando, você deverá ver a seguinte saída:

New York
Los Angeles
San Francisco

Esta saída mostra os nomes dos funcionários (agora nomes de cidades) que trabalham em departamentos localizados na mesma cidade.

Avaliar a Eficiência de Subconsultas com JOIN

Neste passo, você aprenderá como avaliar a eficiência de subconsultas e explorar abordagens alternativas usando operações JOIN para otimização.

Embora as subconsultas sejam poderosas, elas podem, às vezes, levar a gargalos de desempenho, especialmente com grandes conjuntos de dados. Em muitos casos, você pode reescrever subconsultas usando operações JOIN, que podem ser mais eficientes.

Vamos reescrever a subconsulta correlacionada do passo anterior usando um JOIN:

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

Este comando seleciona o employee_name da tabela employees (apelidada como e) e a junta com a tabela departments (apelidada como d) na coluna department_id. A cláusula WHERE então filtra os resultados para incluir apenas as linhas onde a location na tabela departments corresponde ao employee_name na tabela employees.

Para verificar o resultado, execute o comando. Você deverá ver a mesma saída do passo anterior:

New York
Los Angeles
San Francisco

Para avaliar a eficiência, você normalmente usaria EXPLAIN QUERY PLAN antes e depois da alteração. No entanto, devido a limitações no ambiente LabEx, não podemos demonstrar totalmente o comando EXPLAIN QUERY PLAN. A principal conclusão é que as operações JOIN são frequentemente mais eficientes do que as subconsultas correlacionadas, especialmente para conjuntos de dados maiores.

Finalmente, saia do shell sqlite3:

.exit

Isso o retornará ao prompt bash.

Resumo

Neste laboratório, você aprendeu como usar subconsultas SQLite para aprimorar suas capacidades de recuperação e filtragem de dados. Você praticou o uso de subconsultas dentro da cláusula WHERE, incorporando-as na instrução SELECT e construindo subconsultas correlacionadas. Você também aprendeu como reescrever subconsultas usando operações JOIN para melhor eficiência. Essas técnicas fornecem ferramentas poderosas para trabalhar com dados em SQLite.