SELECT Avançado

O que é a tabela DUAL?

A DUAL é uma tabela especial do Oracle que contém apenas uma linha e uma coluna (chamada DUMMY com valor 'X'). Ela é útil quando você precisa executar funções ou expressões sem consultar uma tabela real.

-- Exemplos básicos com DUAL
SELECT SYSDATE FROM DUAL;                    -- Data/hora atual
SELECT USER FROM DUAL;                       -- Usuário conectado
SELECT 10 + 5 AS resultado FROM DUAL;        -- Cálculo simples
SELECT 'Hello, Oracle!' AS mensagem FROM DUAL;  -- Literal
SELECT UPPER('oracle database') FROM DUAL;   -- Função de string

Aliases de Colunas

Aliases permitem renomear colunas no resultado da consulta, tornando-o mais legível. Você pode usar AS (opcional) ou aspas duplas para nomes com espaços.

-- Aliases de colunas
SELECT
    id_funcionario AS codigo,                -- AS é opcional
    nome funcionario,                        -- Sem AS
    salario AS "Salário Mensal",            -- Com espaço (requer aspas)
    salario * 12 AS salario_anual,          -- Expressão calculada
    UPPER(email) email_maiusculo
FROM funcionarios;

-- Resultado será exibido com os nomes dos aliases

Aliases de Tabelas e Prefixação

Aliases de tabelas simplificam queries complexas com múltiplas tabelas. A prefixação (tabela.coluna) evita ambiguidade quando colunas têm o mesmo nome em tabelas diferentes.

-- Aliases de tabelas (muito útil em JOINs)
SELECT
    f.id_funcionario,          -- Prefixo 'f' identifica a tabela
    f.nome AS funcionario,
    f.salario,
    d.nome AS departamento,    -- Prefixo 'd' identifica a tabela
    d.localizacao
FROM funcionarios f            -- Alias 'f' para funcionarios
INNER JOIN departamentos d     -- Alias 'd' para departamentos
    ON f.departamento_id = d.id_departamento
WHERE d.localizacao = 'São Paulo'
ORDER BY f.nome;

-- Sem aliases seria mais verboso:
-- funcionarios.nome, departamentos.nome, etc.

Ordenação Ascendente e Descendente

ORDER BY ordena o resultado da consulta. Use ASC (ascendente, padrão) ou DESC (descendente).

-- Ordem ascendente (padrão)
SELECT nome, salario
FROM funcionarios
ORDER BY salario;              -- Menor para maior (ASC implícito)

-- Ordem descendente
SELECT nome, salario
FROM funcionarios
ORDER BY salario DESC;         -- Maior para menor

-- Múltiplas colunas
SELECT nome, departamento_id, salario
FROM funcionarios
ORDER BY departamento_id ASC, salario DESC;  -- Dept crescente, salário decrescente

-- Ordenar por posição da coluna no SELECT
SELECT nome, salario, data_admissao
FROM funcionarios
ORDER BY 3 DESC, 2 DESC;       -- Posição 3 (data_admissao), depois posição 2 (salario)

-- Ordenar por alias
SELECT nome AS funcionario, salario * 12 AS salario_anual
FROM funcionarios
ORDER BY salario_anual DESC;   -- Usa o alias definido no SELECT

-- Controlar posição dos NULLs
SELECT nome, data_demissao
FROM funcionarios
ORDER BY data_demissao NULLS LAST;  -- NULLs no final

Removendo Linhas Duplicadas

DISTINCT remove linhas duplicadas do resultado, considerando todas as colunas do SELECT.

-- Sem DISTINCT - pode retornar valores repetidos
SELECT departamento_id
FROM funcionarios;
-- Resultado: 10, 20, 10, 30, 20, 10 (duplicatas)

-- Com DISTINCT - valores únicos
SELECT DISTINCT departamento_id
FROM funcionarios;
-- Resultado: 10, 20, 30 (sem duplicatas)

-- DISTINCT em múltiplas colunas (combinação única)
SELECT DISTINCT departamento_id, cargo
FROM funcionarios;
-- Retorna combinações únicas de departamento + cargo

-- DISTINCT com COUNT
SELECT COUNT(DISTINCT departamento_id) AS total_departamentos
FROM funcionarios;
-- Conta quantos departamentos diferentes existem

O que é ROWNUM?

ROWNUM é uma pseudocoluna que atribui um número sequencial para cada linha no momento em que é retornada pela query.

-- Limitar número de linhas retornadas
SELECT ROWNUM, nome, salario
FROM funcionarios
WHERE ROWNUM <= 5;             -- Retorna apenas as 5 primeiras linhas

-- Top 3 maiores salários (requer subquery)
SELECT *
FROM (
    SELECT nome, salario
    FROM funcionarios
    ORDER BY salario DESC      -- Ordenar ANTES de aplicar ROWNUM
)
WHERE ROWNUM <= 3;             -- Pegar os 3 primeiros da ordenação

-- INCORRETO - ORDER BY é aplicado DEPOIS do ROWNUM
SELECT ROWNUM, nome, salario
FROM funcionarios
WHERE ROWNUM <= 3
ORDER BY salario DESC;         -- Ordena DEPOIS de pegar as 3 primeiras

-- CORRETO - Subquery para ordenar ANTES do ROWNUM
SELECT *
FROM (
    SELECT nome, salario
    FROM funcionarios
    ORDER BY salario DESC      -- Ordena primeiro
)
WHERE ROWNUM <= 3;             -- Depois pega as 3 primeiras

Paginação com ROWNUM

Para paginar (pular linhas e pegar um intervalo), use subquery dupla:

-- Página 1: linhas 1-10
SELECT *
FROM (
    SELECT nome, salario
    FROM funcionarios
    ORDER BY nome
)
WHERE ROWNUM <= 10;

-- Página 2: linhas 11-20 (requer subquery dupla)
SELECT *
FROM (
    SELECT ROWNUM AS rn, a.*
    FROM (
        SELECT nome, salario
        FROM funcionarios
        ORDER BY nome          -- Ordenação desejada
    ) a
    WHERE ROWNUM <= 20         -- Limite superior
)
WHERE rn > 10;                 -- Limite inferior (pular primeiras 10)

-- Alternativa moderna: ROW_NUMBER() (Oracle 12c+)
SELECT nome, salario
FROM (
    SELECT nome, salario,
           ROW_NUMBER() OVER (ORDER BY nome) AS rn
    FROM funcionarios
)
WHERE rn BETWEEN 11 AND 20;    -- Página 2 (linhas 11-20)

-- Sintaxe moderna (Oracle 12c+)
SELECT nome, salario
FROM funcionarios
ORDER BY nome
OFFSET 10 ROWS                 -- Pular 10 linhas
FETCH NEXT 10 ROWS ONLY;       -- Pegar as próximas 10

Combinando Técnicas

-- Exemplo 1: Top 5 departamentos com mais funcionários
SELECT departamento, total_funcionarios
FROM (
    SELECT
        d.nome AS departamento,
        COUNT(f.id_funcionario) AS total_funcionarios
    FROM departamentos d
    LEFT JOIN funcionarios f ON d.id_departamento = f.departamento_id
    GROUP BY d.nome
    ORDER BY total_funcionarios DESC
)
WHERE ROWNUM <= 5;

-- Exemplo 2: Listar funcionários únicos por cargo (sem duplicatas)
SELECT DISTINCT
    f.cargo,
    d.nome AS departamento
FROM funcionarios f
INNER JOIN departamentos d ON f.departamento_id = d.id_departamento
ORDER BY f.cargo, d.nome;

-- Exemplo 3: Relatório com múltiplas ordenações e aliases
SELECT
    ROWNUM AS posicao,
    f.nome AS "Nome do Funcionário",
    f.salario AS "Salário Atual",
    f.salario * 12 AS "Salário Anual",
    d.nome AS "Departamento"
FROM (
    SELECT f.*, d.nome AS dept_nome
    FROM funcionarios f
    INNER JOIN departamentos d ON f.departamento_id = d.id_departamento
    ORDER BY d.nome, f.salario DESC
) f, departamentos d
WHERE f.departamento_id = d.id_departamento
  AND ROWNUM <= 20;

-- Exemplo 4: Contagem de funcionários por cidade (distintos)
SELECT
    d.localizacao AS cidade,
    COUNT(DISTINCT f.id_funcionario) AS total_funcionarios,
    COUNT(DISTINCT f.cargo) AS cargos_diferentes
FROM departamentos d
LEFT JOIN funcionarios f ON d.id_departamento = f.departamento_id
GROUP BY d.localizacao
ORDER BY total_funcionarios DESC NULLS LAST;