QuizSQL

📎 Modelo de dados de referência

-- Esquema de exemplo
CREATE TABLE clientes (
  id SERIAL PRIMARY KEY,
  nome VARCHAR(100) NOT NULL,
  email VARCHAR(120) UNIQUE,
  nascimento DATE,
  ativo BOOLEAN DEFAULT TRUE
);

CREATE TABLE produtos (
  id SERIAL PRIMARY KEY,
  nome VARCHAR(120) NOT NULL,
  preco NUMERIC(10,2) NOT NULL CHECK (preco >= 0)
);

CREATE TABLE pedidos (
  id SERIAL PRIMARY KEY,
  cliente_id INTEGER NOT NULL REFERENCES clientes(id),
  criado_em TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE itens_pedido (
  pedido_id INTEGER REFERENCES pedidos(id),
  produto_id INTEGER REFERENCES produtos(id),
  quantidade INTEGER NOT NULL CHECK (quantidade > 0),
  preco_unit NUMERIC(10,2) NOT NULL,
  PRIMARY KEY (pedido_id, produto_id)
);

CREATE TABLE clientes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(100) NOT NULL,
  email VARCHAR(120) UNIQUE,
  nascimento DATE,
  ativo TINYINT(1) DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE produtos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(120) NOT NULL,
  preco DECIMAL(10,2) NOT NULL CHECK (preco >= 0)
) ENGINE=InnoDB;

CREATE TABLE pedidos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  cliente_id INT NOT NULL,
  criado_em TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (cliente_id) REFERENCES clientes(id)
) ENGINE=InnoDB;

CREATE TABLE itens_pedido (
  pedido_id INT,
  produto_id INT,
  quantidade INT NOT NULL,
  preco_unit DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (pedido_id, produto_id),
  FOREIGN KEY (pedido_id) REFERENCES pedidos(id),
  FOREIGN KEY (produto_id) REFERENCES produtos(id)
) ENGINE=InnoDB;

📗 Nível Básico – Fundamentos de SQL

1) O que é SQL?

Uso

SQL (Structured Query Language) é usado para definir estruturas (DDL), manipular dados (DML), consultar informações (DQL) e controlar permissões/transações (DCL/TCL) em bancos relacionais.

Estrutura

• Declarações terminadas por ponto e vírgula (;).
• Cláusulas como SELECT, FROM, WHERE, GROUP BY, ORDER BY.
• Identificadores (nomes de tabelas/colunas), literais (números, strings, datas) e funções.
• Dialetos: há diferenças entre PostgreSQL, MySQL, SQL Server, Oracle.

Função

Permitir operações consistentes sobre dados relacionais com garantias ACID, linguagem declarativa (você diz o que quer, o otimizador decide como executar).

-- Estrutura básica de consulta
SELECT colunas
FROM tabela
WHERE condicao
GROUP BY colunas
HAVING condicao_agregada
ORDER BY colunas
LIMIT n OFFSET m; -- suporte a LIMIT varia por SGBD

2) Criação de Banco de Dados

Uso

Criar um novo contêiner lógico para tabelas, views e demais objetos. Útil para separar ambientes (dev, teste, produção) ou domínios de negócio.

Estrutura

CREATE DATABASE <nome> com opções como owner, encoding, collation.

Função

Isolar dados e metadados; permitir políticas de backup e permissões distintas.

-- PostgreSQL
CREATE DATABASE loja TEMPLATE template0 ENCODING 'UTF8' LC_COLLATE 'pt_BR.UTF-8' LC_CTYPE 'pt_BR.UTF-8';
-- MySQL
CREATE DATABASE loja CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

DROP DATABASE IF EXISTS loja; -- remove tudo dentro!

3) Criação de Tabelas (DDL)

Uso

Definir colunas, tipos e constraints que garantem integridade.

Estrutura

• Colunas com tipo e nulabilidade (NOT NULL).
• Chaves (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK).
• Valores padrão (DEFAULT) e identidade/auto incremento (SERIAL / GENERATED AS IDENTITY / AUTO_INCREMENT).

Função

Modelar o domínio e impor regras no nível do banco, evitando dados inválidos.

-- Exemplo mínimo
CREATE TABLE clientes (
  id SERIAL PRIMARY KEY,
  nome VARCHAR(100) NOT NULL,
  email VARCHAR(120) UNIQUE,
  nascimento DATE,
  ativo BOOLEAN NOT NULL DEFAULT TRUE,
  CHECK (nome <> '')
);

-- ALTER para evoluir o esquema
ALTER TABLE clientes ADD COLUMN criado_em TIMESTAMP DEFAULT NOW();
ALTER TABLE clientes RENAME COLUMN nome TO nome_completo;
DROP TABLE IF EXISTS clientes;  -- remove a tabela

4) Inserção e Consulta de Dados (DML/DQL)

Uso

Inserir novos registros, ler dados com filtros, projetar colunas e ordenar/paginar resultados.

Estrutura

• INSERT INTO tabela (cols) VALUES (...) ou INSERT ... SELECT.
• SELECT com WHERE, ORDER BY, LIMIT/OFFSET.

Função

Persistir e recuperar informações para telas, relatórios e APIs.

-- Inserir
INSERT INTO clientes (nome, email, nascimento)
VALUES ('Maria', 'maria@exemplo.com', '1990-05-10');

-- Inserir em lote
INSERT INTO produtos (nome, preco) VALUES
  ('Teclado', 99.90),
  ('Mouse', 59.90),
  ('Monitor', 1299.00);

-- Consultar
SELECT id, nome, email
FROM clientes
WHERE ativo = TRUE AND nascimento >= DATE '1990-01-01'
ORDER BY nome
LIMIT 20 OFFSET 0;

-- Projeção e alias
SELECT p.id, p.nome AS produto, (p.preco * 1.10) AS preco_com_imposto
FROM produtos p;

5) Atualização e Exclusão

Uso

Modificar ou remover linhas existentes.

Estrutura

UPDATE tabela SET coluna = expr WHERE condição e DELETE FROM tabela WHERE condição.

Função

Manter dados consistentes e atualizados.

UPDATE produtos SET preco = preco * 0.95 WHERE id IN (1,2);
DELETE FROM clientes WHERE ativo = FALSE AND email IS NULL;

-- Postgres: retornar linhas afetadas
UPDATE produtos SET preco = preco * 1.05 WHERE id = 3 RETURNING id, preco;

6) Operadores

Uso

Comparar, combinar e calcular valores em filtros e projeções.

Estrutura

• Aritméticos: +, -, *, /, %
• Relacionais: =, <>/!=, >, >=, <, <=, BETWEEN, IN, LIKE
• Lógicos: AND, OR, NOT
• Nulos: IS NULL, IS NOT NULL (atenção: NULL não é igual a nada)

Função

Construir expressões e condições para seleção e transformação de dados.

SELECT nome
FROM clientes
WHERE (nome ILIKE 'm%')  -- Postgres: ILIKE ignora maiúsc./minúsc.
   OR (email LIKE '%@empresa.com');

SELECT * FROM produtos WHERE preco BETWEEN 50 AND 200;

📘 Nível Intermediário – Manipulação e Análise

1) Cláusulas avançadas do SELECT

Uso

Refinar resultados, tratar nulos, criar colunas derivadas e aplicar funções.

Estrutura

Ordem lógica de avaliação: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

Função

Controlar o conjunto de linhas, agregações e ordenação final.

-- DISTINCT remove duplicatas
SELECT DISTINCT cliente_id FROM pedidos;

-- Filtros
SELECT * FROM clientes WHERE id IN (1,2,3);
SELECT * FROM clientes WHERE nascimento BETWEEN '1990-01-01' AND '2000-12-31';
SELECT * FROM clientes WHERE nome LIKE '% Silva%';
SELECT * FROM clientes WHERE email IS NOT NULL;

-- Funções úteis
SELECT COALESCE(email,'sem email') AS contato, UPPER(nome), ROUND(AVG(preco),2)
FROM clientes c
JOIN pedidos p ON p.cliente_id = c.id
JOIN itens_pedido i ON i.pedido_id = p.id
JOIN produtos pr ON pr.id = i.produto_id
GROUP BY c.id;

-- CASE para lógica condicional
SELECT nome,
  CASE WHEN ativo THEN 'Ativo' ELSE 'Inativo' END AS status
FROM clientes;

2) Funções Agregadas

Uso

Obter métricas sobre conjuntos de linhas.

Estrutura

SUM, AVG, MAX, MIN, COUNT; opcionalmente com GROUP BY e filtros com HAVING.

Função

Produzir KPIs, totais e estatísticas.

-- Total de pedidos e valor por cliente
SELECT c.id, c.nome,
       COUNT(DISTINCT p.id) AS qnt_pedidos,
       SUM(i.quantidade * i.preco_unit) AS valor_total
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.id
LEFT JOIN itens_pedido i ON i.pedido_id = p.id
GROUP BY c.id, c.nome
HAVING SUM(i.quantidade * i.preco_unit) > 500; -- filtra agregados

3) Agrupamentos

Uso

Consolidar linhas por chaves (ex.: por mês, cliente, produto).

Estrutura

GROUP BY com expressões; em alguns SGBDs há ROLLUP, CUBE e GROUPING SETS.

Função

Construir relatórios e painéis.

-- Faturamento por mês
SELECT DATE_TRUNC('month', p.criado_em) AS mes,
       SUM(i.quantidade * i.preco_unit) AS faturamento
FROM pedidos p
JOIN itens_pedido i ON i.pedido_id = p.id
GROUP BY 1
ORDER BY 1;

4) Joins (Junções)

Uso

Combinar dados de múltiplas tabelas relacionadas.

Estrutura

• INNER JOIN: somente correspondências.
• LEFT/RIGHT JOIN: preserva todas as linhas da tabela à esquerda/direita.
• FULL OUTER JOIN: todas as linhas de ambas, quando suportado.
• CROSS JOIN: produto cartesiano.
• Anti/Semi-join: NOT EXISTS / EXISTS.

Função

Recompor o modelo relacional em consultas.

-- Pedidos com nome do cliente
SELECT p.id, c.nome, p.criado_em
FROM pedidos p
JOIN clientes c ON c.id = p.cliente_id;

-- Produtos nunca vendidos (anti-join)
SELECT pr.*
FROM produtos pr
WHERE NOT EXISTS (
  SELECT 1 FROM itens_pedido i WHERE i.produto_id = pr.id
);

5) Subconsultas

Uso

Reutilizar resultados intermediários ou expressar lógica dependente.

Estrutura

• Inline (retorna escalar/linha única) ou tabelada no FROM.
• Correlacionada: depende da linha externa.
• WITH (CTE): nomeia subconsulta; recursive para hierarquias.

Função

Organizar a query e facilitar manutenção; algumas vezes melhora o plano.

-- CTE para clareza
WITH vendas AS (
  SELECT p.id, SUM(i.quantidade * i.preco_unit) AS total
  FROM pedidos p JOIN itens_pedido i ON i.pedido_id = p.id
  GROUP BY p.id
)
SELECT * FROM vendas WHERE total > 1000;

-- Correlacionada: último pedido de cada cliente
SELECT c.id, c.nome,
  (
    SELECT MAX(criado_em) FROM pedidos p WHERE p.cliente_id = c.id
  ) AS ultimo_pedido
FROM clientes c;

6) Ordenação e Paginação

Uso

Controlar a ordenação final e limitar o volume retornado.

Estrutura

ORDER BY com múltiplas colunas; LIMIT/OFFSET ou FETCH FIRST. Opções NULLS FIRST/LAST (Postgres).

Função

Paginar listas em aplicações; melhorar tempo de resposta.

-- Paginação tradicional
SELECT id, nome FROM clientes ORDER BY id FETCH FIRST 20 ROWS ONLY;
-- Keyset pagination (melhor performance em grandes volumes)
SELECT id, nome FROM clientes WHERE id > :ultimo_id ORDER BY id LIMIT 20;

7) Views (Visões)

Uso

Encapsular consultas complexas, padronizar métricas e restringir colunas.

Estrutura

CREATE VIEW nome AS SELECT ...; algumas visões são atualizáveis. Existem materialized views (Postgres) que armazenam o resultado.

Função

Reuso e segurança (limitar acesso a colunas/linhas).

CREATE VIEW vw_receita_por_cliente AS
SELECT c.id, c.nome, SUM(i.quantidade * i.preco_unit) AS receita
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.id
LEFT JOIN itens_pedido i ON i.pedido_id = p.id
GROUP BY c.id, c.nome;

-- Materializada (Postgres)
CREATE MATERIALIZED VIEW mv_top_produtos AS
SELECT pr.id, pr.nome, SUM(i.quantidade) AS vendidos
FROM produtos pr JOIN itens_pedido i ON i.produto_id = pr.id
GROUP BY 1,2;
REFRESH MATERIALIZED VIEW mv_top_produtos;

📕 Nível Avançado – Performance, Segurança e Design

1) Procedures e Functions

Uso

Executar lógica no servidor: rotinas reutilizáveis, validações, integrações e jobs.

Estrutura

• Function: retorna valor/tabela; pode ser usada em SELECT.
• Procedure: chamada com CALL; pode ter efeitos colaterais (DML).
• Parâmetros IN/OUT; atributos como LANGUAGE SQL/PLpgSQL (Postgres) ou DETERMINISTIC (MySQL).

Função

Centralizar regras e melhorar ergonomia; atenção a portabilidade e controle de versões.

-- PostgreSQL function
CREATE OR REPLACE FUNCTION total_pedido(p_pedido INT)
RETURNS NUMERIC AS $$
  SELECT COALESCE(SUM(quantidade * preco_unit),0)
  FROM itens_pedido WHERE pedido_id = p_pedido;
$$ LANGUAGE SQL STABLE;

SELECT total_pedido(10);

-- MySQL procedure
DELIMITER //
CREATE PROCEDURE reajustar_preco(IN p_pct DECIMAL(5,2))
BEGIN
  UPDATE produtos SET preco = preco * (1 + p_pct/100);
END //
DELIMITER ;
CALL reajustar_preco(3.5);

2) Triggers (Gatilhos)

Uso

Executar ações automáticas antes/depois de INSERT, UPDATE ou DELETE.

Estrutura

Definição de evento (BEFORE/AFTER + operação), granularidade (ROW/STATEMENT) e corpo que usa NEW/OLD.

Função

Auditoria, preenchimento automático, regras complexas de integridade.

-- Auditoria de inserts (Postgres)
CREATE TABLE log_auditoria(
  id bigserial PRIMARY KEY,
  tabela TEXT, acao TEXT, payload JSONB, criado_em TIMESTAMPTZ DEFAULT now()
);

CREATE OR REPLACE FUNCTION trg_log_clientes()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO log_auditoria(tabela, acao, payload)
  VALUES ('clientes','INSERT', to_jsonb(NEW));
  RETURN NEW; -- BEFORE/AFTER INSERT em nível de linha
END;$$ LANGUAGE plpgsql;

CREATE TRIGGER clientes_audit
AFTER INSERT ON clientes
FOR EACH ROW EXECUTE FUNCTION trg_log_clientes();

3) Transações e Concorrência

Uso

Garantir atomicidade de operações múltiplas e consistência em ambientes concorrentes.

Estrutura

• BEGIN/COMMIT/ROLLBACK, SAVEPOINT.
• Níveis de isolamento: Read Uncommitted, Read Committed, Repeatable Read, Serializable.
• Bloqueios: compartilhado/exclusivo; deadlocks podem ocorrer.

Função

Proteger integridade (ACID) e definir trade-offs entre simultaneidade e visibilidade de dados.

BEGIN;
  UPDATE produtos SET preco = preco - 10 WHERE id = 1;
  INSERT INTO pedidos (cliente_id) VALUES (1);
  -- se algo falhar:
  -- ROLLBACK;  -- desfaz tudo
COMMIT;          -- confirma

-- Savepoint para ponto de retorno parcial
BEGIN;
  SAVEPOINT etapa1;
  UPDATE produtos SET preco = -1 WHERE id = 2; -- viola CHECK
  ROLLBACK TO etapa1; -- desfaz somente esta parte
COMMIT;

4) Índices

Uso

Acelerar leituras (lookups, ordenação e junções) ao custo de armazenamento e escrita.

Estrutura

• Padrão: B-tree (igualdade e faixa).
• Específicos: Hash (igualdade), GIN/GiST (arrays, texto, geoespacial) no Postgres.
• Compostos (ordem das colunas importa), covering (INCLUDE no Postgres), únicos.

Função

Reduzir páginas lidas e tempo de varredura. Devem alinhar-se às condições de WHERE, JOIN, ORDER BY.

-- Índice simples e composto
CREATE INDEX idx_produtos_nome ON produtos(nome);
CREATE INDEX idx_itens_pedido_pedido_produto ON itens_pedido(pedido_id, produto_id);

-- Postgres: covering index
CREATE INDEX idx_pedidos_cliente_inclui_data ON pedidos(cliente_id) INCLUDE (criado_em);

-- Ver plano de execução
EXPLAIN ANALYZE SELECT * FROM produtos WHERE nome ILIKE 'mo%';

5) Constraints (Restrições)

Uso

Impor regras de integridade referencial e de domínio.

Estrutura

• PRIMARY KEY (única e não nula).
• FOREIGN KEY com ações ON DELETE/UPDATE (CASCADE, RESTRICT, SET NULL).
• UNIQUE, NOT NULL, CHECK (expressões).
• Em Postgres, algumas podem ser DEFERRABLE (avaliadas no COMMIT).

Função

Prevenir estados inválidos sem depender apenas da aplicação.

ALTER TABLE itens_pedido
  ADD CONSTRAINT fk_item_produto FOREIGN KEY (produto_id)
  REFERENCES produtos(id) ON DELETE RESTRICT;

ALTER TABLE pedidos
  ADD CONSTRAINT fk_pedido_cliente FOREIGN KEY (cliente_id)
  REFERENCES clientes(id) ON DELETE CASCADE; -- remove itens ao excluir o pedido

6) Modelagem de Dados Relacional

Uso

Representar entidades e relacionamentos com mínima redundância e alta consistência.

Estrutura

• Normalização: 1FN (atomicidade), 2FN (dependência total da chave), 3FN (sem dependência transitiva), BCNF.
• Chaves naturais vs surrogates (ex.: SERIAL/IDENTITY).
• Relacionamentos 1:1, 1:N, N:N (tabela de junção).

Função

Evitar anomalias de inserção/atualização/exclusão; facilitar consultas e integridade.

-- Relação N:N: pedidos <-> produtos via itens_pedido
-- Já demonstrada no modelo de referência.

7) Técnicas de Otimização de Consultas

Uso

Melhorar latência e custo em grandes volumes.

Estrutura

• Use EXPLAIN/EXPLAIN ANALYZE para entender o plano.
• Evite SELECT *; projete apenas o necessário.
• Garanta sargabilidade: condições que permitem uso de índice.
• Prefira keyset pagination a OFFSET altos.
• Mantenha estatísticas atualizadas; VACUUM/ANALYZE (Postgres).
• Particionamento para tabelas gigantes; caching na aplicação.

Função

Reduzir leituras, CPU e contenção, melhorando throughput geral.

-- Exemplo de reescrita sargável
-- Não sargável (função na coluna):
SELECT * FROM clientes WHERE LOWER(email) = 'maria@exemplo.com';
-- Melhor: índice funcional OU coluna normalizada + comparação direta
CREATE INDEX idx_clientes_email_lower ON clientes ((LOWER(email)));
SELECT * FROM clientes WHERE LOWER(email) = 'maria@exemplo.com';

8) Segurança e Permissões

Uso

Aplicar least privilege, proteger contra acesso indevido e injeção SQL.

Estrutura

• Usuários e papéis (roles), GRANT/REVOKE.
• Row-Level Security (Postgres), views para mascarar dados.
• Parâmetros preparados/ORM para prevenir SQL Injection.
• Auditoria e backups regulares.

Função

Manter confidencialidade, integridade e disponibilidade.

-- PostgreSQL: papéis e concessões
CREATE ROLE app_user LOGIN PASSWORD '***';
GRANT CONNECT ON DATABASE loja TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE ON TABLES TO app_user;

-- Exemplo de consulta parametrizada (pseudocódigo)
-- SELECT * FROM clientes WHERE email = $1;  -- nunca concatene strings do usuário!

🔤 Tipos de dados comuns e quando usar

📚 Glossário rápido

• ACID: Atomicidade, Consistência, Isolamento, Durabilidade.
• Sargabilidade: possibilidade de usar índice na condição.
• Cardinalidade: quantidade de valores distintos em uma coluna.
• CTE: Common Table Expression (cláusula WITH).
• RLS: Row-Level Security, filtro por usuário/condição.