JOINs, Funções de Agregação e Subqueries | Trilha 1

📖

Definição

JOINs - Combinando Tabelas

JOINs permitem combinar dados de múltiplas tabelas em uma única consulta. São fundamentais para trabalhar com bancos relacionais, onde dados são distribuídos em várias tabelas relacionadas.

INNER JOIN

Retorna apenas registros que têm correspondência em ambas as tabelas.

A

B

LEFT JOIN (LEFT OUTER)

Retorna todos da tabela esquerda + correspondentes da direita (ou NULL).

RIGHT JOIN (RIGHT OUTER)

Retorna todos da tabela direita + correspondentes da esquerda (ou NULL).

FULL OUTER JOIN

Retorna todos de ambas as tabelas, com NULL onde não há correspondência.

Funções de Agregação

Funções de agregação calculam um único valor a partir de um conjunto de registros. São essenciais para relatórios e análises estatísticas.

COUNT()

Conta registros

SUM()

Soma valores

AVG()

Média

MAX()

Valor máximo

MIN()

Valor mínimo

LISTAGG()

Concatena strings

Subqueries (Subconsultas)

Subqueries são consultas aninhadas dentro de outra consulta. Podem ser usadas em WHERE, FROM, SELECT ou HAVING.

Subquery Escalar

Retorna um único valor. Usada em SELECT ou WHERE com operadores =, >, <.

Subquery de Lista

Retorna múltiplos valores. Usada com IN, ANY, ALL.

Subquery Correlacionada

Referencia a consulta externa. Executada para cada linha da principal.

Inline View

Subquery no FROM, tratada como tabela temporária.

🛠️

Aplicação Prática

Setup: Tabelas de Exemplo

-- Tabela de Departamentos
CREATE TABLE departamentos (
    dept_id    NUMBER PRIMARY KEY,
    dept_nome  VARCHAR2(50) NOT NULL,
    gerente_id NUMBER,
    localizacao VARCHAR2(50)
);

-- Tabela de Funcionários
CREATE TABLE funcionarios (
    func_id      NUMBER PRIMARY KEY,
    nome         VARCHAR2(100) NOT NULL,
    cargo        VARCHAR2(50),
    salario      NUMBER(10,2),
    dept_id      NUMBER REFERENCES departamentos(dept_id),
    data_admissao DATE
);

-- Inserir dados
INSERT INTO departamentos VALUES (10, 'Tecnologia', 1, 'São Paulo');
INSERT INTO departamentos VALUES (20, 'Recursos Humanos', 2, 'Rio de Janeiro');
INSERT INTO departamentos VALUES (30, 'Financeiro', NULL, 'São Paulo');
INSERT INTO departamentos VALUES (40, 'Marketing', NULL, 'Curitiba');

INSERT INTO funcionarios VALUES (1, 'Carlos Diretor', 'Diretor', 25000, 10, DATE '2010-01-15');
INSERT INTO funcionarios VALUES (2, 'Ana Gerente', 'Gerente', 12000, 20, DATE '2015-03-20');
INSERT INTO funcionarios VALUES (3, 'João Desenvolvedor', 'Desenvolvedor', 8000, 10, DATE '2018-07-01');
INSERT INTO funcionarios VALUES (4, 'Maria Analista', 'Analista', 6000, 10, DATE '2020-02-10');
INSERT INTO funcionarios VALUES (5, 'Pedro Analista', 'Analista', 5500, 20, DATE '2019-11-15');
INSERT INTO funcionarios VALUES (6, 'Lucia Estagiaria', 'Estagiário', 1500, NULL, DATE '2023-01-10');

COMMIT;

JOINs na Prática

-- INNER JOIN: Funcionários COM departamento
SELECT f.nome, f.cargo, f.salario, d.dept_nome
FROM funcionarios f
INNER JOIN departamentos d ON f.dept_id = d.dept_id;
-- Resultado: 5 registros (Lucia não aparece - não tem departamento)

-- LEFT JOIN: TODOS os funcionários, com ou sem departamento
SELECT f.nome, f.cargo, d.dept_nome
FROM funcionarios f
LEFT JOIN departamentos d ON f.dept_id = d.dept_id;
-- Resultado: 6 registros (Lucia aparece com dept_nome NULL)

-- RIGHT JOIN: TODOS os departamentos, com ou sem funcionários
SELECT d.dept_nome, f.nome
FROM funcionarios f
RIGHT JOIN departamentos d ON f.dept_id = d.dept_id;
-- Resultado: 7 registros (Financeiro e Marketing aparecem sem funcionários)

-- FULL OUTER JOIN: Combinação de LEFT e RIGHT
SELECT f.nome, d.dept_nome
FROM funcionarios f
FULL OUTER JOIN departamentos d ON f.dept_id = d.dept_id;
-- Resultado: 8 registros (todos os funcionários + todos os departamentos)

-- Múltiplos JOINs
SELECT
    f.nome AS funcionario,
    f.cargo,
    d.dept_nome AS departamento,
    g.nome AS gerente
FROM funcionarios f
LEFT JOIN departamentos d ON f.dept_id = d.dept_id
LEFT JOIN funcionarios g ON d.gerente_id = g.func_id;

-- SELF JOIN: Funcionário e seu gerente (mesma tabela)
-- Primeiro, adicionar coluna gerente_id em funcionarios
ALTER TABLE funcionarios ADD gerente_id NUMBER REFERENCES funcionarios(func_id);
UPDATE funcionarios SET gerente_id = 1 WHERE func_id IN (3, 4);
UPDATE funcionarios SET gerente_id = 2 WHERE func_id = 5;
COMMIT;

SELECT
    f.nome AS funcionario,
    f.cargo,
    g.nome AS gerente_direto
FROM funcionarios f
LEFT JOIN funcionarios g ON f.gerente_id = g.func_id;

-- Encontrar funcionários SEM departamento (órfãos)
SELECT f.nome, f.cargo
FROM funcionarios f
LEFT JOIN departamentos d ON f.dept_id = d.dept_id
WHERE d.dept_id IS NULL;

-- Encontrar departamentos SEM funcionários
SELECT d.dept_nome
FROM departamentos d
LEFT JOIN funcionarios f ON d.dept_id = f.dept_id
WHERE f.func_id IS NULL;

Funções de Agregação com GROUP BY

-- COUNT: Contar funcionários
SELECT COUNT(*) AS total_funcionarios FROM funcionarios;
SELECT COUNT(dept_id) AS com_departamento FROM funcionarios;  -- Ignora NULL
SELECT COUNT(DISTINCT dept_id) AS departamentos_diferentes FROM funcionarios;

-- SUM, AVG, MIN, MAX
SELECT
    SUM(salario) AS folha_total,
    AVG(salario) AS media_salarial,
    MIN(salario) AS menor_salario,
    MAX(salario) AS maior_salario
FROM funcionarios;

-- GROUP BY: Agregação por grupo
SELECT
    dept_id,
    COUNT(*) AS qtd_funcionarios,
    SUM(salario) AS folha_depto,
    ROUND(AVG(salario), 2) AS media_depto
FROM funcionarios
WHERE dept_id IS NOT NULL
GROUP BY dept_id
ORDER BY folha_depto DESC;

-- GROUP BY com JOIN para mostrar nome do departamento
SELECT
    d.dept_nome,
    COUNT(f.func_id) AS qtd_funcionarios,
    NVL(SUM(f.salario), 0) AS folha_total,
    NVL(ROUND(AVG(f.salario), 2), 0) AS media_salarial
FROM departamentos d
LEFT JOIN funcionarios f ON d.dept_id = f.dept_id
GROUP BY d.dept_nome
ORDER BY qtd_funcionarios DESC;

-- HAVING: Filtrar grupos (não pode usar WHERE para agregações)
SELECT
    dept_id,
    COUNT(*) AS qtd,
    AVG(salario) AS media
FROM funcionarios
WHERE dept_id IS NOT NULL
GROUP BY dept_id
HAVING COUNT(*) >= 2  -- Apenas deptos com 2+ funcionários
   AND AVG(salario) > 5000
ORDER BY media DESC;

-- LISTAGG: Concatenar nomes por departamento
SELECT
    d.dept_nome,
    LISTAGG(f.nome, ', ') WITHIN GROUP (ORDER BY f.nome) AS funcionarios
FROM departamentos d
LEFT JOIN funcionarios f ON d.dept_id = f.dept_id
GROUP BY d.dept_nome;

-- Múltiplos níveis de agregação com ROLLUP
SELECT
    d.dept_nome,
    f.cargo,
    COUNT(*) AS qtd,
    SUM(f.salario) AS total
FROM funcionarios f
JOIN departamentos d ON f.dept_id = d.dept_id
GROUP BY ROLLUP(d.dept_nome, f.cargo)
ORDER BY d.dept_nome, f.cargo;

Subqueries (Subconsultas)

-- Subquery escalar no WHERE
-- Funcionários que ganham mais que a média
SELECT nome, salario
FROM funcionarios
WHERE salario > (SELECT AVG(salario) FROM funcionarios);

-- Funcionário com maior salário
SELECT nome, salario
FROM funcionarios
WHERE salario = (SELECT MAX(salario) FROM funcionarios);

-- Subquery de lista com IN
-- Funcionários em departamentos de São Paulo
SELECT nome, cargo
FROM funcionarios
WHERE dept_id IN (
    SELECT dept_id
    FROM departamentos
    WHERE localizacao = 'São Paulo'
);

-- Subquery com NOT IN (cuidado com NULL!)
SELECT nome
FROM funcionarios
WHERE dept_id NOT IN (
    SELECT dept_id FROM departamentos WHERE dept_id IS NOT NULL
);

-- Subquery correlacionada
-- Funcionários que ganham mais que a média do seu departamento
SELECT f1.nome, f1.salario, f1.dept_id
FROM funcionarios f1
WHERE f1.salario > (
    SELECT AVG(f2.salario)
    FROM funcionarios f2
    WHERE f2.dept_id = f1.dept_id  -- Correlação com query externa
);

-- EXISTS: Verifica existência
-- Departamentos que têm funcionários
SELECT dept_nome
FROM departamentos d
WHERE EXISTS (
    SELECT 1 FROM funcionarios f WHERE f.dept_id = d.dept_id
);

-- NOT EXISTS: Departamentos sem funcionários
SELECT dept_nome
FROM departamentos d
WHERE NOT EXISTS (
    SELECT 1 FROM funcionarios f WHERE f.dept_id = d.dept_id
);

-- Inline View (Subquery no FROM)
SELECT dept_nome, qtd_func, media_sal
FROM (
    SELECT
        d.dept_nome,
        COUNT(f.func_id) AS qtd_func,
        ROUND(AVG(f.salario), 2) AS media_sal
    FROM departamentos d
    LEFT JOIN funcionarios f ON d.dept_id = f.dept_id
    GROUP BY d.dept_nome
) subquery
WHERE qtd_func > 0
ORDER BY media_sal DESC;

-- WITH clause (CTE - Common Table Expression)
WITH resumo_depto AS (
    SELECT
        dept_id,
        COUNT(*) AS qtd,
        AVG(salario) AS media
    FROM funcionarios
    WHERE dept_id IS NOT NULL
    GROUP BY dept_id
)
SELECT
    d.dept_nome,
    r.qtd AS funcionarios,
    r.media AS media_salarial
FROM resumo_depto r
JOIN departamentos d ON r.dept_id = d.dept_id
ORDER BY r.media DESC;

-- Subquery no SELECT
SELECT
    f.nome,
    f.salario,
    (SELECT AVG(salario) FROM funcionarios) AS media_geral,
    f.salario - (SELECT AVG(salario) FROM funcionarios) AS diferenca_media
FROM funcionarios f
ORDER BY diferenca_media DESC;

Consultas Avançadas Combinadas

-- Relatório completo: Departamentos com estatísticas
WITH stats AS (
    SELECT
        dept_id,
        COUNT(*) AS total_func,
        SUM(salario) AS folha,
        MIN(salario) AS menor_sal,
        MAX(salario) AS maior_sal,
        ROUND(AVG(salario), 2) AS media_sal
    FROM funcionarios
    WHERE dept_id IS NOT NULL
    GROUP BY dept_id
)
SELECT
    d.dept_nome,
    d.localizacao,
    NVL(s.total_func, 0) AS funcionarios,
    NVL(s.folha, 0) AS folha_total,
    s.menor_sal,
    s.maior_sal,
    s.media_sal,
    CASE
        WHEN s.total_func IS NULL THEN 'Sem funcionários'
        WHEN s.total_func < 3 THEN 'Equipe pequena'
        ELSE 'Equipe completa'
    END AS status_equipe
FROM departamentos d
LEFT JOIN stats s ON d.dept_id = s.dept_id
ORDER BY s.folha DESC NULLS LAST;

-- Top N por grupo: Top 2 maiores salários por departamento
SELECT *
FROM (
    SELECT
        f.nome,
        f.salario,
        d.dept_nome,
        ROW_NUMBER() OVER (PARTITION BY f.dept_id ORDER BY f.salario DESC) AS ranking
    FROM funcionarios f
    JOIN departamentos d ON f.dept_id = d.dept_id
)
WHERE ranking <= 2;

-- Funcionários com salário acima da média do departamento
SELECT f.nome, f.salario, d.dept_nome, media_dept.media
FROM funcionarios f
JOIN departamentos d ON f.dept_id = d.dept_id
JOIN (
    SELECT dept_id, AVG(salario) AS media
    FROM funcionarios
    GROUP BY dept_id
) media_dept ON f.dept_id = media_dept.dept_id
WHERE f.salario > media_dept.media
ORDER BY d.dept_nome, f.salario DESC;

✅

Resultado Esperado

O que você deve dominar após este tópico:

✓

Tipos de JOIN

INNER, LEFT, RIGHT, FULL OUTER, SELF JOIN

✓

Funções de agregação

COUNT, SUM, AVG, MIN, MAX com GROUP BY

✓

HAVING vs WHERE

Filtrar grupos vs filtrar linhas

✓

Subqueries

Escalar, lista, correlacionada, inline view

✓

EXISTS e NOT EXISTS

Verificar existência de registros relacionados

✓

WITH clause (CTE)

Organizar queries complexas

📝

Exercícios Práticos

Exercício 1: JOINs

Usando as tabelas de exemplo, escreva queries para:

Listar todos os funcionários com o nome do departamento (incluir quem não tem depto)
Listar departamentos que não têm nenhum funcionário
Listar funcionários junto com o nome do seu gerente direto

Ver Solução

-- 1. Funcionários com departamento (LEFT JOIN)
SELECT f.nome, f.cargo, COALESCE(d.dept_nome, 'Sem departamento') AS departamento
FROM funcionarios f
LEFT JOIN departamentos d ON f.dept_id = d.dept_id;

-- 2. Departamentos sem funcionários
SELECT d.dept_nome
FROM departamentos d
LEFT JOIN funcionarios f ON d.dept_id = f.dept_id
WHERE f.func_id IS NULL;
-- Alternativa com NOT EXISTS:
SELECT d.dept_nome
FROM departamentos d
WHERE NOT EXISTS (SELECT 1 FROM funcionarios f WHERE f.dept_id = d.dept_id);

-- 3. Funcionários com gerente (SELF JOIN)
SELECT
    f.nome AS funcionario,
    f.cargo,
    COALESCE(g.nome, 'Sem gerente') AS gerente
FROM funcionarios f
LEFT JOIN funcionarios g ON f.gerente_id = g.func_id;

Exercício 2: Agregações

Crie um relatório que mostre por departamento:

Nome do departamento
Quantidade de funcionários
Salário mínimo, máximo e médio
Total da folha salarial
Apenas departamentos com mais de 1 funcionário
Ordenado pelo total da folha (maior primeiro)

Ver Solução

SELECT
    d.dept_nome AS departamento,
    COUNT(f.func_id) AS qtd_funcionarios,
    MIN(f.salario) AS menor_salario,
    MAX(f.salario) AS maior_salario,
    ROUND(AVG(f.salario), 2) AS media_salarial,
    SUM(f.salario) AS folha_total
FROM departamentos d
JOIN funcionarios f ON d.dept_id = f.dept_id
GROUP BY d.dept_nome
HAVING COUNT(f.func_id) > 1
ORDER BY SUM(f.salario) DESC;

Exercício 3: Subqueries Avançadas

Resolva os seguintes problemas usando subqueries:

Liste funcionários cujo salário está acima da média geral
Para cada funcionário, mostre quanto ele ganha a mais (ou menos) que a média do seu departamento
Liste o funcionário mais bem pago de cada departamento

Ver Solução

-- 1. Acima da média geral
SELECT nome, salario
FROM funcionarios
WHERE salario > (SELECT AVG(salario) FROM funcionarios);

-- 2. Diferença da média do departamento
SELECT
    f.nome,
    f.salario,
    d.dept_nome,
    ROUND((SELECT AVG(salario) FROM funcionarios WHERE dept_id = f.dept_id), 2) AS media_depto,
    ROUND(f.salario - (SELECT AVG(salario) FROM funcionarios WHERE dept_id = f.dept_id), 2) AS diferenca
FROM funcionarios f
JOIN departamentos d ON f.dept_id = d.dept_id
ORDER BY diferenca DESC;

-- 3. Mais bem pago por departamento (várias formas)
-- Opção 1: Subquery correlacionada
SELECT f.nome, f.salario, d.dept_nome
FROM funcionarios f
JOIN departamentos d ON f.dept_id = d.dept_id
WHERE f.salario = (
    SELECT MAX(salario)
    FROM funcionarios
    WHERE dept_id = f.dept_id
);

-- Opção 2: Com ROW_NUMBER (mais elegante)
SELECT nome, salario, dept_nome
FROM (
    SELECT
        f.nome,
        f.salario,
        d.dept_nome,
        ROW_NUMBER() OVER (PARTITION BY f.dept_id ORDER BY f.salario DESC) AS rn
    FROM funcionarios f
    JOIN departamentos d ON f.dept_id = d.dept_id
)
WHERE rn = 1;

Continue sua jornada

Ver Conteúdo Completo Conteúdo Compacto