DQL + DCL — Consulta & Controlo de Acesso

DQL (Data Query Language) é centrado no SELECT — o comando mais complexo e expressivo do SQL. Desde JOINs simples até window functions e CTEs recursivas, o SELECT permite transformar dados relacionais em qualquer resultado que o negócio precise, sem mover dados para a aplicação. DCL (Data Control Language) — GRANT, REVOKE e roles — define quem pode fazer o quê, e row-level security vai mais longe: define quais linhas cada utilizador pode sequer ver. Os dois andam juntos porque a query mais bem escrita não vale nada se o utilizador errado a conseguir executar.

Anatomia do SELECT

-- Ordem de escrita vs ordem de execução (o motor SQL executa numa ordem diferente):
--
-- Ordem de ESCRITA:          Ordem de EXECUÇÃO (lógica):
-- 1. SELECT                  1. FROM + JOINs     — define as tabelas base
-- 2. FROM                    2. WHERE             — filtra linhas (antes de agrupar)
-- 3. JOIN                    3. GROUP BY          — agrupa linhas
-- 4. WHERE                   4. HAVING            — filtra grupos
-- 5. GROUP BY                5. SELECT            — calcula expressões e aliases
-- 6. HAVING                  6. DISTINCT          — remove duplicados
-- 7. ORDER BY                7. ORDER BY          — ordena o resultado
-- 8. LIMIT / OFFSET          8. LIMIT / OFFSET    — pagina o resultado
--
-- Consequência prática: não podes usar aliases do SELECT no WHERE ou HAVING
-- Porque WHERE é executado ANTES do SELECT (onde o alias é definido)

-- ❌ Erro comum:
SELECT total * 1.23 AS total_iva
FROM encomenda
WHERE total_iva > 100;   -- ERROR: column "total_iva" does not exist

-- ✅ Correcto:
SELECT total * 1.23 AS total_iva
FROM encomenda
WHERE total * 1.23 > 100;   -- repetir a expressão no WHERE
-- ou usar subquery / CTE (ver abaixo)


-- ── SELECT completo com todas as cláusulas: ───────────────────────────────────
SELECT
    c.nome                              AS categoria,
    COUNT(DISTINCT p.id)                AS num_produtos,
    ROUND(AVG(p.preco_base), 2)         AS preco_medio,
    MIN(p.preco_base)                   AS preco_minimo,
    MAX(p.preco_base)                   AS preco_maximo,
    SUM(p.stock)                        AS stock_total
FROM produto p
JOIN categoria c ON c.id = p.categoria_id
WHERE p.activo = TRUE
  AND p.preco_base > 0
GROUP BY c.id, c.nome
HAVING COUNT(DISTINCT p.id) >= 3       -- apenas categorias com 3 ou mais produtos
ORDER BY preco_medio DESC
LIMIT 10
OFFSET 0;

JOINs

-- ── INNER JOIN — apenas linhas com correspondência nos dois lados ─────────────
SELECT e.id, u.nome AS cliente, e.total
FROM encomenda e
INNER JOIN utilizador u ON u.id = e.cliente_id;
-- Encomendas sem cliente válido (cliente_id = NULL) NÃO aparecem
-- Clientes sem encomendas NÃO aparecem


-- ── LEFT JOIN — todas as linhas da esquerda, NULL onde não há correspondência ──
SELECT u.nome, COUNT(e.id) AS num_encomendas
FROM utilizador u
LEFT JOIN encomenda e ON e.cliente_id = u.id
GROUP BY u.id, u.nome
ORDER BY num_encomendas DESC;
-- Clientes sem encomendas aparecem com num_encomendas = 0
-- (COUNT(e.id) = 0 porque e.id é NULL para esses clientes)


-- ── RIGHT JOIN — espelho do LEFT JOIN (raramente necessário — inverter tabelas) ─
-- Preferir sempre LEFT JOIN reordenando as tabelas


-- ── FULL OUTER JOIN — todas as linhas de ambos os lados ──────────────────────
SELECT u.nome, e.id AS encomenda_id
FROM utilizador u
FULL OUTER JOIN encomenda e ON e.cliente_id = u.id;
-- Clientes sem encomendas: encomenda_id = NULL
-- Encomendas sem cliente:  nome = NULL (dados corrompidos — útil para auditoria)


-- ── CROSS JOIN — produto cartesiano (todas as combinações possíveis) ──────────
SELECT t1.cor, t2.tamanho
FROM cor t1
CROSS JOIN tamanho t2;
-- Se cor tem 5 linhas e tamanho tem 3 → 15 combinações
-- Útil para gerar combinações de variantes de produto


-- ── SELF JOIN — tabela junta com ela própria ──────────────────────────────────
-- Exemplo: estrutura hierárquica de empregados (cada empregado tem um manager)
SELECT
    e.nome      AS empregado,
    m.nome      AS manager
FROM empregado e
LEFT JOIN empregado m ON m.id = e.manager_id;
-- e e m são dois aliases para a mesma tabela


-- ── Múltiplos JOINs ───────────────────────────────────────────────────────────
SELECT
    e.id        AS encomenda_id,
    u.email     AS cliente,
    p.nome      AS produto,
    l.quantidade,
    l.preco_unit,
    c.nome      AS categoria
FROM encomenda e
JOIN utilizador   u ON u.id = e.cliente_id
JOIN linha_encomenda l ON l.encomenda_id = e.id
JOIN produto      p ON p.id = l.produto_id
JOIN categoria    c ON c.id = p.categoria_id
WHERE e.estado = 'entregue'
ORDER BY e.id, p.nome;

Subqueries e CTEs

-- ── Subquery no WHERE ─────────────────────────────────────────────────────────
-- Clientes que fizeram pelo menos uma encomenda acima de 500€:
SELECT nome, email
FROM utilizador
WHERE id IN (
    SELECT DISTINCT cliente_id
    FROM encomenda
    WHERE total > 500
);

-- EXISTS vs IN — EXISTS pára na primeira correspondência (mais eficiente):
SELECT nome, email
FROM utilizador u
WHERE EXISTS (
    SELECT 1
    FROM encomenda e
    WHERE e.cliente_id = u.id AND e.total > 500
);
-- SELECT 1: não importa o valor retornado — só interessa a existência de linhas


-- ── Subquery no FROM (derived table) ─────────────────────────────────────────
SELECT categoria, preco_medio
FROM (
    SELECT c.nome AS categoria, AVG(p.preco_base) AS preco_medio
    FROM produto p
    JOIN categoria c ON c.id = p.categoria_id
    GROUP BY c.nome
) AS medias_por_categoria
WHERE preco_medio > 50;
-- O alias AS medias_por_categoria é obrigatório no PostgreSQL


-- ── CTE — Common Table Expression (WITH) ─────────────────────────────────────
-- Equivalente à derived table mas mais legível, reutilizável e debuggável

WITH clientes_vip AS (
    SELECT cliente_id, SUM(total) AS total_gasto
    FROM encomenda
    WHERE estado = 'entregue'
    GROUP BY cliente_id
    HAVING SUM(total) > 1000
),
produtos_populares AS (
    SELECT produto_id, COUNT(*) AS num_vendas
    FROM linha_encomenda l
    JOIN encomenda e ON e.id = l.encomenda_id
    WHERE e.estado = 'entregue'
    GROUP BY produto_id
    ORDER BY num_vendas DESC
    LIMIT 10
)
SELECT
    u.nome,
    u.email,
    cv.total_gasto,
    pp.num_vendas AS vendas_produto_favorito
FROM clientes_vip cv
JOIN utilizador u ON u.id = cv.cliente_id
LEFT JOIN linha_encomenda l ON l.encomenda_id IN (
    SELECT id FROM encomenda WHERE cliente_id = cv.cliente_id
)
JOIN produtos_populares pp ON pp.produto_id = l.produto_id
ORDER BY cv.total_gasto DESC;


-- ── CTE Recursiva — hierarquias e grafos ─────────────────────────────────────
-- Navegar uma árvore de categorias (categoria pai → subcategorias):
WITH RECURSIVE arvore_categorias AS (
    -- Âncora: ponto de partida (categoria raiz)
    SELECT id, nome, parent_id, 0 AS nivel, nome::TEXT AS caminho
    FROM categoria
    WHERE parent_id IS NULL

    UNION ALL

    -- Passo recursivo: cada iteração vai um nível mais fundo
    SELECT c.id, c.nome, c.parent_id,
           ac.nivel + 1,
           ac.caminho || ' > ' || c.nome
    FROM categoria c
    JOIN arvore_categorias ac ON ac.id = c.parent_id
)
SELECT nivel, caminho, id
FROM arvore_categorias
ORDER BY caminho;
-- Resultado:
-- 0 | Electrónica                          | 1
-- 1 | Electrónica > Computadores           | 4
-- 2 | Electrónica > Computadores > Portáteis | 9
-- 1 | Electrónica > Televisões             | 5

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

-- ── Agregações básicas ───────────────────────────────────────────────────────
SELECT
    DATE_TRUNC('month', criada_em)  AS mes,
    COUNT(*)                         AS num_encomendas,
    COUNT(DISTINCT cliente_id)       AS clientes_unicos,
    SUM(total)                       AS receita,
    AVG(total)                       AS ticket_medio,
    MIN(total)                       AS encomenda_minima,
    MAX(total)                       AS encomenda_maxima,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total) AS mediana
FROM encomenda
WHERE estado = 'entregue'
GROUP BY DATE_TRUNC('month', criada_em)
ORDER BY mes;


-- ── GROUPING SETS, ROLLUP, CUBE — subtotais automáticos ──────────────────────

-- ROLLUP: subtotais hierárquicos (ano → mês → dia)
SELECT
    EXTRACT(YEAR  FROM criada_em) AS ano,
    EXTRACT(MONTH FROM criada_em) AS mes,
    SUM(total)                    AS receita
FROM encomenda
WHERE estado = 'entregue'
GROUP BY ROLLUP (
    EXTRACT(YEAR  FROM criada_em),
    EXTRACT(MONTH FROM criada_em)
)
ORDER BY ano NULLS LAST, mes NULLS LAST;
-- Gera:
-- 2024 | 1  | 12500  ← total de Jan 2024
-- 2024 | 2  |  9800  ← total de Fev 2024
-- 2024 | NULL| 22300  ← total de 2024
-- NULL | NULL| 45600  ← total geral


-- FILTER: agregação condicional (evita múltiplos SELECTs ou CASE)
SELECT
    COUNT(*) FILTER (WHERE estado = 'pendente')   AS pendentes,
    COUNT(*) FILTER (WHERE estado = 'enviado')    AS enviadas,
    COUNT(*) FILTER (WHERE estado = 'entregue')   AS entregues,
    COUNT(*) FILTER (WHERE estado = 'cancelado')  AS canceladas,
    COUNT(*)                                       AS total
FROM encomenda;

Window Functions

Window functions calculam sobre um conjunto de linhas relacionadas com a linha actual — sem colapsar as linhas num grupo como o GROUP BY. São o mecanismo mais poderoso do SQL para análise e relatórios.

-- Sintaxe: FUNÇÃO() OVER (PARTITION BY ... ORDER BY ... ROWS/RANGE ...)

-- ── ROW_NUMBER, RANK, DENSE_RANK ─────────────────────────────────────────────
SELECT
    p.nome,
    c.nome                                          AS categoria,
    p.preco_base,
    ROW_NUMBER() OVER (
        PARTITION BY p.categoria_id
        ORDER BY p.preco_base DESC
    )                                               AS posicao_na_categoria,
    RANK() OVER (
        ORDER BY p.preco_base DESC
    )                                               AS rank_global,
    -- RANK: produtos com o mesmo preço têm o mesmo rank, e o rank seguinte salta
    -- (1, 2, 2, 4) — há lacuna
    DENSE_RANK() OVER (
        ORDER BY p.preco_base DESC
    )                                               AS dense_rank_global
    -- DENSE_RANK: sem lacunas (1, 2, 2, 3)
FROM produto p
JOIN categoria c ON c.id = p.categoria_id
WHERE p.activo = TRUE;


-- ── LAG e LEAD — aceder a linhas anteriores/seguintes ────────────────────────
-- Crescimento mês a mês:
WITH receita_mensal AS (
    SELECT
        DATE_TRUNC('month', criada_em)  AS mes,
        SUM(total)                       AS receita
    FROM encomenda
    WHERE estado = 'entregue'
    GROUP BY DATE_TRUNC('month', criada_em)
)
SELECT
    mes,
    receita,
    LAG(receita, 1) OVER (ORDER BY mes)   AS receita_mes_anterior,
    ROUND(
        (receita - LAG(receita, 1) OVER (ORDER BY mes))
        / NULLIF(LAG(receita, 1) OVER (ORDER BY mes), 0) * 100
    , 2)                                   AS crescimento_pct
    -- NULLIF evita divisão por zero se receita_mes_anterior = 0
FROM receita_mensal
ORDER BY mes;


-- ── Funções de agregação como window functions ────────────────────────────────
SELECT
    e.id,
    e.cliente_id,
    e.total,
    SUM(e.total)   OVER (PARTITION BY e.cliente_id)              AS total_cliente,
    AVG(e.total)   OVER (PARTITION BY e.cliente_id)              AS media_cliente,
    -- % que esta encomenda representa no total do cliente:
    ROUND(e.total / SUM(e.total) OVER (PARTITION BY e.cliente_id) * 100, 1) AS pct_do_cliente,
    -- Total acumulado por data (running total):
    SUM(e.total)   OVER (
        PARTITION BY e.cliente_id
        ORDER BY e.criada_em
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )                                                             AS total_acumulado
FROM encomenda e
WHERE e.estado = 'entregue'
ORDER BY e.cliente_id, e.criada_em;


-- ── NTILE — dividir em percentis ─────────────────────────────────────────────
SELECT
    u.nome,
    SUM(e.total) AS total_gasto,
    NTILE(4) OVER (ORDER BY SUM(e.total))  AS quartil
    -- 1 = clientes com menos gastos, 4 = clientes com mais gastos
FROM encomenda e
JOIN utilizador u ON u.id = e.cliente_id
WHERE e.estado = 'entregue'
GROUP BY u.id, u.nome;

DCL — GRANT, REVOKE e Roles

-- ── Roles (papéis) — grupos de permissões reutilizáveis ─────────────────────
-- Uma role pode ser um utilizador (tem login) ou um grupo (sem login)

-- Criar roles funcionais (sem login — grupos de permissões):
CREATE ROLE role_leitura;
CREATE ROLE role_escrita;
CREATE ROLE role_admin_loja;

-- Criar utilizadores (roles com login):
CREATE ROLE app_user    LOGIN PASSWORD 'password_segura_aqui';
CREATE ROLE app_admin   LOGIN PASSWORD 'outra_password_segura';
CREATE ROLE app_readonly LOGIN PASSWORD 'read_only_password';

-- Atribuir roles a utilizadores:
GRANT role_leitura TO app_readonly;
GRANT role_leitura, role_escrita TO app_user;
GRANT role_admin_loja TO app_admin;


-- ── GRANT — conceder permissões ───────────────────────────────────────────────

-- Permissões sobre tabelas:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO role_leitura;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE encomenda, linha_encomenda TO role_escrita;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO role_admin_loja;

-- Permissões sobre sequências (necessário para INSERT com SERIAL/BIGSERIAL):
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO role_escrita;

-- Permissões sobre schemas:
GRANT USAGE ON SCHEMA public TO role_leitura;
GRANT USAGE ON SCHEMA public TO role_escrita;
GRANT CREATE ON SCHEMA public TO role_admin_loja;

-- DEFAULT PRIVILEGES — aplica permissões a tabelas criadas no futuro:
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO role_leitura;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO role_escrita;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT USAGE, SELECT ON SEQUENCES TO role_escrita;
-- Sem isto, novas tabelas criadas não herdam as permissões das roles existentes


-- ── REVOKE — remover permissões ───────────────────────────────────────────────
REVOKE DELETE ON TABLE utilizador FROM role_escrita;
REVOKE ALL PRIVILEGES ON TABLE dados_sensíveis FROM role_leitura;

-- Revogar role de um utilizador:
REVOKE role_escrita FROM app_user;

-- Ver permissões actuais:
\dp utilizador           -- psql: mostra permissões da tabela
\du                      -- psql: lista roles e atributos
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'utilizador';

Row-Level Security

-- RLS — Row-Level Security: cada utilizador só vê as linhas que lhe pertencem
-- Implementado no SGBD — não na aplicação (mais seguro, não pode ser esquecido)

-- 1. Activar RLS na tabela:
ALTER TABLE encomenda ENABLE ROW LEVEL SECURITY;
-- Após activar: por default NENHUMA linha é visível (nem para o dono da tabela)
-- (excepto para superusers e roles com BYPASSRLS)

-- 2. Criar políticas (policies):

-- Política de leitura: cada cliente só vê as suas próprias encomendas
CREATE POLICY pol_encomenda_select
ON encomenda
FOR SELECT
TO app_user                                -- aplicada à role app_user
USING (cliente_id = current_setting('app.cliente_id')::INTEGER);
-- current_setting: lê uma variável de sessão definida pela aplicação

-- Política de inserção: só pode inserir encomendas para si próprio
CREATE POLICY pol_encomenda_insert
ON encomenda
FOR INSERT
TO app_user
WITH CHECK (cliente_id = current_setting('app.cliente_id')::INTEGER);
-- USING: filtra linhas em SELECT/UPDATE/DELETE
-- WITH CHECK: valida linhas em INSERT/UPDATE

-- Política para admins: vêem tudo
CREATE POLICY pol_encomenda_admin
ON encomenda
FOR ALL
TO role_admin_loja
USING (TRUE);   -- sem restrição — vêem todas as linhas

-- 3. A aplicação define a variável de sessão ao autenticar:
-- (em JDBC/SQL, executar após obter a conexão):
-- SET app.cliente_id = '42';
-- Ou numa transacção:
BEGIN;
    SET LOCAL app.cliente_id = '42';   -- SET LOCAL: válido apenas nesta transacção
    SELECT * FROM encomenda;           -- retorna apenas encomendas do cliente 42
COMMIT;

-- Ver políticas activas:
SELECT polname, polcmd, polroles, pg_get_expr(polqual, polrelid) AS using_expr
FROM pg_policy
WHERE polrelid = 'encomenda'::regclass;

-- Desactivar RLS (para manutenção/migração):
ALTER TABLE encomenda DISABLE ROW LEVEL SECURITY;

-- FORCE RLS: aplica RLS mesmo ao dono da tabela:
ALTER TABLE encomenda FORCE ROW LEVEL SECURITY;

Checklist DQL + DCL

O SELECT é executado na ordem FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY — aliases do SELECT não são visíveis no WHERE.
Preferir EXISTS a IN com subqueries — EXISTS pára na primeira correspondência.
CTEs (WITH) para queries complexas em múltiplos passos — mais legíveis e debuggáveis que subqueries aninhadas.
CTEs recursivas (WITH RECURSIVE) para hierarquias e grafos — sem recursão na aplicação.
Window functions para análise sem colapsar linhas: ROW_NUMBER, LAG, SUM OVER, NTILE.
COUNT(*) FILTER (WHERE condição) para múltiplas contagens condicionais num único SELECT.
ROLLUP para subtotais hierárquicos automáticos (ano → mês → total geral).
Criar roles funcionais (role_leitura, role_escrita, role_admin) em vez de conceder permissões directamente a utilizadores.
ALTER DEFAULT PRIVILEGES para que novas tabelas herdem automaticamente as permissões das roles.
Row-Level Security (ENABLE ROW LEVEL SECURITY + CREATE POLICY) para isolar dados por utilizador ao nível do SGBD — não depender apenas da aplicação.
SET LOCAL app.variavel para passar contexto da aplicação para políticas RLS dentro de uma transacção.

DML + DTL — Manipulação & Transacções Índices & Performance