Criar e Gerenciar Bancos de Dados

-- Criar banco de dados
CREATE DATABASE ecommerce;

-- Usar banco de dados (MySQL/SQL Server)
USE ecommerce;

-- Conectar ao banco (PostgreSQL via psql)
-- \c ecommerce

-- Listar bancos de dados
SHOW DATABASES;              -- MySQL
-- \l                         -- PostgreSQL

-- Deletar banco de dados
DROP DATABASE IF EXISTS ecommerce;

Criar Tabelas

CREATE TABLE usuarios (
  id          SERIAL PRIMARY KEY,
  nome        VARCHAR(100) NOT NULL,
  email       VARCHAR(255) UNIQUE NOT NULL,
  senha       VARCHAR(255) NOT NULL,
  role        VARCHAR(20) DEFAULT 'user',
  ativo       BOOLEAN DEFAULT TRUE,
  criado_em   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  atualizado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE categorias (
  id   SERIAL PRIMARY KEY,
  nome VARCHAR(100) NOT NULL,
  slug VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE produtos (
  id            SERIAL PRIMARY KEY,
  nome          VARCHAR(200) NOT NULL,
  descricao     TEXT,
  preco         DECIMAL(10, 2) NOT NULL,
  estoque       INT DEFAULT 0,
  categoria_id  INT REFERENCES categorias(id) ON DELETE SET NULL,
  criado_em     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE pedidos (
  id          SERIAL PRIMARY KEY,
  usuario_id  INT NOT NULL REFERENCES usuarios(id),
  status      VARCHAR(30) DEFAULT 'pendente',
  total       DECIMAL(10, 2) NOT NULL,
  criado_em   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE itens_pedido (
  id          SERIAL PRIMARY KEY,
  pedido_id   INT NOT NULL REFERENCES pedidos(id) ON DELETE CASCADE,
  produto_id  INT NOT NULL REFERENCES produtos(id),
  quantidade  INT NOT NULL,
  preco_unit  DECIMAL(10, 2) NOT NULL
);

Alterar Tabelas

-- Adicionar coluna
ALTER TABLE usuarios ADD COLUMN telefone VARCHAR(20);

-- Remover coluna
ALTER TABLE usuarios DROP COLUMN telefone;

-- Renomear coluna
ALTER TABLE usuarios RENAME COLUMN nome TO nome_completo;

-- Alterar tipo de coluna
ALTER TABLE produtos ALTER COLUMN preco TYPE NUMERIC(12, 2);

-- Adicionar constraint
ALTER TABLE produtos ADD CONSTRAINT preco_positivo CHECK (preco > 0);

-- Adicionar índice
CREATE INDEX idx_produtos_categoria ON produtos(categoria_id);
CREATE INDEX idx_usuarios_email ON usuarios(email);

-- Adicionar índice único
CREATE UNIQUE INDEX idx_categorias_slug ON categorias(slug);

INSERT — Inserir Dados

-- Inserir um registro
INSERT INTO categorias (nome, slug) VALUES ('Eletrônicos', 'eletronicos');

-- Inserir múltiplos registros
INSERT INTO categorias (nome, slug) VALUES
  ('Roupas', 'roupas'),
  ('Livros', 'livros'),
  ('Games', 'games'),
  ('Casa e Jardim', 'casa-jardim');

-- Inserir com retorno do ID (PostgreSQL)
INSERT INTO usuarios (nome, email, senha)
VALUES ('Ivan Reis', 'ivan@dev.com', '$2b$12$hash...')
RETURNING id, nome, email;

-- Inserir ignorando duplicatas (MySQL)
INSERT IGNORE INTO categorias (nome, slug)
VALUES ('Eletrônicos', 'eletronicos');

-- Upsert (PostgreSQL)
INSERT INTO usuarios (email, nome, senha)
VALUES ('ivan@dev.com', 'Ivan Reis', '$2b$12$hash...')
ON CONFLICT (email) DO UPDATE SET nome = EXCLUDED.nome;

SELECT — Consultar Dados

-- Selecionar tudo
SELECT * FROM produtos;

-- Selecionar colunas específicas
SELECT nome, preco, estoque FROM produtos;

-- Alias de colunas
SELECT nome AS produto, preco AS valor FROM produtos;

-- Filtrar com WHERE
SELECT * FROM produtos WHERE preco > 100 AND estoque > 0;

-- Operadores de comparação
SELECT * FROM produtos WHERE preco BETWEEN 50 AND 200;
SELECT * FROM usuarios WHERE role IN ('admin', 'editor');
SELECT * FROM produtos WHERE nome LIKE '%notebook%';
SELECT * FROM produtos WHERE descricao IS NOT NULL;

-- Ordenar resultados
SELECT * FROM produtos ORDER BY preco DESC, nome ASC;

-- Limitar resultados
SELECT * FROM produtos ORDER BY criado_em DESC LIMIT 10;
SELECT * FROM produtos ORDER BY criado_em DESC LIMIT 10 OFFSET 20;

-- Valores distintos
SELECT DISTINCT categoria_id FROM produtos;

UPDATE — Atualizar Dados

-- Atualizar registro
UPDATE produtos SET preco = 299.90, estoque = 50 WHERE id = 1;

-- Atualizar com expressão
UPDATE produtos SET preco = preco * 0.9 WHERE categoria_id = 1;

-- Atualizar com subquery
UPDATE produtos SET estoque = 0
WHERE id IN (SELECT produto_id FROM itens_pedido WHERE quantidade > 100);

-- Atualizar com retorno (PostgreSQL)
UPDATE usuarios SET ativo = FALSE WHERE id = 5
RETURNING id, nome, ativo;

DELETE — Remover Dados

-- Deletar registro
DELETE FROM produtos WHERE id = 1;

-- Deletar com condição
DELETE FROM usuarios WHERE ativo = FALSE AND criado_em < '2025-01-01';

-- Deletar todos os registros (mantém a tabela)
DELETE FROM itens_pedido;

-- Truncar tabela (mais rápido, reseta auto-increment)
TRUNCATE TABLE itens_pedido RESTART IDENTITY CASCADE;

-- Deletar tabela completamente
DROP TABLE IF EXISTS itens_pedido;

JOINs

-- INNER JOIN (registros que existem em ambas tabelas)
SELECT p.nome, p.preco, c.nome AS categoria
FROM produtos p
INNER JOIN categorias c ON p.categoria_id = c.id;

-- LEFT JOIN (todos da esquerda + matches da direita)
SELECT u.nome, COUNT(pe.id) AS total_pedidos
FROM usuarios u
LEFT JOIN pedidos pe ON u.id = pe.usuario_id
GROUP BY u.id, u.nome;

-- RIGHT JOIN (todos da direita + matches da esquerda)
SELECT p.nome, c.nome AS categoria
FROM produtos p
RIGHT JOIN categorias c ON p.categoria_id = c.id;

-- FULL OUTER JOIN (todos de ambas tabelas)
SELECT u.nome, pe.id AS pedido_id
FROM usuarios u
FULL OUTER JOIN pedidos pe ON u.id = pe.usuario_id;

-- JOIN múltiplo
SELECT
  pe.id AS pedido,
  u.nome AS cliente,
  pr.nome AS produto,
  ip.quantidade,
  ip.preco_unit,
  (ip.quantidade * ip.preco_unit) AS subtotal
FROM pedidos pe
JOIN usuarios u ON pe.usuario_id = u.id
JOIN itens_pedido ip ON pe.id = ip.pedido_id
JOIN produtos pr ON ip.produto_id = pr.id
ORDER BY pe.criado_em DESC;

-- Self JOIN
SELECT a.nome AS funcionario, b.nome AS gerente
FROM usuarios a
LEFT JOIN usuarios b ON a.gerente_id = b.id;

Funções de Agregação

-- COUNT, SUM, AVG, MIN, MAX
SELECT
  COUNT(*) AS total_produtos,
  SUM(estoque) AS estoque_total,
  AVG(preco) AS preco_medio,
  MIN(preco) AS mais_barato,
  MAX(preco) AS mais_caro
FROM produtos;

-- GROUP BY
SELECT
  c.nome AS categoria,
  COUNT(p.id) AS qtd_produtos,
  AVG(p.preco) AS preco_medio
FROM produtos p
JOIN categorias c ON p.categoria_id = c.id
GROUP BY c.id, c.nome
ORDER BY qtd_produtos DESC;

-- HAVING (filtrar após agregação)
SELECT categoria_id, COUNT(*) AS total
FROM produtos
GROUP BY categoria_id
HAVING COUNT(*) > 5;

-- Agregação com CASE
SELECT
  COUNT(CASE WHEN status = 'concluido' THEN 1 END) AS concluidos,
  COUNT(CASE WHEN status = 'pendente' THEN 1 END) AS pendentes,
  COUNT(CASE WHEN status = 'cancelado' THEN 1 END) AS cancelados
FROM pedidos;

Subqueries

-- Subquery no WHERE
SELECT * FROM produtos
WHERE preco > (SELECT AVG(preco) FROM produtos);

-- Subquery no FROM
SELECT categoria, preco_medio
FROM (
  SELECT c.nome AS categoria, AVG(p.preco) AS preco_medio
  FROM produtos p
  JOIN categorias c ON p.categoria_id = c.id
  GROUP BY c.id, c.nome
) AS resumo
WHERE preco_medio > 100;

-- EXISTS
SELECT * FROM categorias c
WHERE EXISTS (
  SELECT 1 FROM produtos p WHERE p.categoria_id = c.id
);

-- NOT IN
SELECT * FROM usuarios
WHERE id NOT IN (SELECT DISTINCT usuario_id FROM pedidos);

Window Functions

-- ROW_NUMBER
SELECT
  nome, preco, categoria_id,
  ROW_NUMBER() OVER (PARTITION BY categoria_id ORDER BY preco DESC) AS ranking
FROM produtos;

-- RANK e DENSE_RANK
SELECT
  nome, preco,
  RANK() OVER (ORDER BY preco DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY preco DESC) AS dense_rank
FROM produtos;

-- Acumulado com SUM
SELECT
  criado_em::DATE AS dia,
  total,
  SUM(total) OVER (ORDER BY criado_em) AS acumulado
FROM pedidos;

-- LAG e LEAD (valor anterior/próximo)
SELECT
  criado_em::DATE AS dia,
  total,
  LAG(total) OVER (ORDER BY criado_em) AS pedido_anterior,
  LEAD(total) OVER (ORDER BY criado_em) AS proximo_pedido
FROM pedidos;

-- Média móvel
SELECT
  criado_em::DATE AS dia,
  total,
  AVG(total) OVER (ORDER BY criado_em ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS media_movel_3d
FROM pedidos;

CTEs (Common Table Expressions)

-- CTE básica
WITH clientes_vip AS (
  SELECT u.id, u.nome, SUM(pe.total) AS gasto_total
  FROM usuarios u
  JOIN pedidos pe ON u.id = pe.usuario_id
  GROUP BY u.id, u.nome
  HAVING SUM(pe.total) > 1000
)
SELECT * FROM clientes_vip ORDER BY gasto_total DESC;

-- CTE recursiva (hierarquia)
WITH RECURSIVE subordinados AS (
  SELECT id, nome, gerente_id, 1 AS nivel
  FROM usuarios
  WHERE gerente_id IS NULL

  UNION ALL

  SELECT u.id, u.nome, u.gerente_id, s.nivel + 1
  FROM usuarios u
  JOIN subordinados s ON u.gerente_id = s.id
)
SELECT * FROM subordinados ORDER BY nivel, nome;

-- Múltiplas CTEs
WITH
  vendas_mes AS (
    SELECT
      DATE_TRUNC('month', criado_em) AS mes,
      SUM(total) AS receita
    FROM pedidos
    WHERE status = 'concluido'
    GROUP BY DATE_TRUNC('month', criado_em)
  ),
  media_vendas AS (
    SELECT AVG(receita) AS media FROM vendas_mes
  )
SELECT
  vm.mes,
  vm.receita,
  mv.media,
  CASE WHEN vm.receita > mv.media THEN 'Acima' ELSE 'Abaixo' END AS performance
FROM vendas_mes vm
CROSS JOIN media_vendas mv
ORDER BY vm.mes;

Views

-- Criar view
CREATE VIEW vw_produtos_disponiveis AS
SELECT p.id, p.nome, p.preco, c.nome AS categoria
FROM produtos p
JOIN categorias c ON p.categoria_id = c.id
WHERE p.estoque > 0;

-- Usar view como tabela
SELECT * FROM vw_produtos_disponiveis WHERE preco < 500;

-- View materializada (PostgreSQL)
CREATE MATERIALIZED VIEW mv_relatorio_vendas AS
SELECT
  DATE_TRUNC('month', pe.criado_em) AS mes,
  c.nome AS categoria,
  SUM(ip.quantidade * ip.preco_unit) AS receita,
  SUM(ip.quantidade) AS unidades_vendidas
FROM pedidos pe
JOIN itens_pedido ip ON pe.id = ip.pedido_id
JOIN produtos pr ON ip.produto_id = pr.id
JOIN categorias c ON pr.categoria_id = c.id
WHERE pe.status = 'concluido'
GROUP BY DATE_TRUNC('month', pe.criado_em), c.id, c.nome;

-- Atualizar view materializada
REFRESH MATERIALIZED VIEW mv_relatorio_vendas;

-- Deletar view
DROP VIEW IF EXISTS vw_produtos_disponiveis;

Índices e Performance

-- Índice simples
CREATE INDEX idx_pedidos_status ON pedidos(status);

-- Índice composto
CREATE INDEX idx_pedidos_usuario_status ON pedidos(usuario_id, status);

-- Índice parcial (PostgreSQL)
CREATE INDEX idx_pedidos_pendentes ON pedidos(criado_em)
WHERE status = 'pendente';

-- Índice GIN para busca full-text (PostgreSQL)
CREATE INDEX idx_produtos_busca ON produtos USING GIN(to_tsvector('portuguese', nome || ' ' || descricao));

-- Busca full-text
SELECT * FROM produtos
WHERE to_tsvector('portuguese', nome || ' ' || descricao) @@ to_tsquery('portuguese', 'notebook & gamer');

-- Analisar query (EXPLAIN)
EXPLAIN ANALYZE
SELECT * FROM produtos WHERE categoria_id = 1 AND preco > 100;

-- Deletar índice
DROP INDEX IF EXISTS idx_pedidos_status;

Transações

-- Transação básica
BEGIN;

INSERT INTO pedidos (usuario_id, status, total)
VALUES (1, 'pendente', 599.90);

INSERT INTO itens_pedido (pedido_id, produto_id, quantidade, preco_unit)
VALUES (LASTVAL(), 42, 1, 599.90);

UPDATE produtos SET estoque = estoque - 1 WHERE id = 42;

COMMIT;

-- Rollback em caso de erro
BEGIN;

UPDATE produtos SET estoque = estoque - 5 WHERE id = 10;

-- Verificar se ficou negativo
-- Se sim:
ROLLBACK;

-- Savepoint
BEGIN;
SAVEPOINT antes_desconto;

UPDATE produtos SET preco = preco * 0.8 WHERE categoria_id = 3;

-- Se algo deu errado, voltar ao savepoint
ROLLBACK TO antes_desconto;

COMMIT;

Controle de Acesso

-- Criar usuário
CREATE USER api_user WITH PASSWORD 'senha_segura';

-- Conceder permissões
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO api_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO api_user;

-- Permissões específicas
GRANT SELECT ON produtos, categorias TO api_user;
GRANT INSERT, UPDATE ON pedidos, itens_pedido TO api_user;

-- Revogar permissões
REVOKE DELETE ON ALL TABLES IN SCHEMA public FROM api_user;

-- Criar role
CREATE ROLE readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT readonly TO api_user;

-- Deletar usuário
DROP USER IF EXISTS api_user;

Funções Úteis

-- Strings
SELECT
  UPPER('texto') AS maiusculo,            -- TEXTO
  LOWER('TEXTO') AS minusculo,            -- texto
  TRIM('  espaços  ') AS sem_espacos,     -- espaços
  LENGTH('hello') AS tamanho,             -- 5
  CONCAT(nome, ' - ', email) AS completo,
  SUBSTRING(email FROM 1 FOR 5) AS inicio,
  REPLACE(nome, ' ', '_') AS slug
FROM usuarios;

-- Datas
SELECT
  NOW() AS agora,
  CURRENT_DATE AS hoje,
  CURRENT_TIMESTAMP AS timestamp_atual,
  DATE_TRUNC('month', NOW()) AS inicio_mes,
  EXTRACT(YEAR FROM criado_em) AS ano,
  EXTRACT(MONTH FROM criado_em) AS mes,
  AGE(NOW(), criado_em) AS tempo_desde_criacao,
  criado_em + INTERVAL '30 days' AS vence_em
FROM pedidos;

-- Condicionais
SELECT
  nome,
  preco,
  CASE
    WHEN preco < 50 THEN 'Barato'
    WHEN preco < 200 THEN 'Intermediário'
    ELSE 'Premium'
  END AS faixa,
  COALESCE(descricao, 'Sem descrição') AS descricao,
  NULLIF(estoque, 0) AS estoque_ou_null,
  GREATEST(preco, 99.90) AS preco_minimo,
  LEAST(preco, 999.90) AS preco_maximo
FROM produtos;

-- Conversão de tipos
SELECT
  CAST('42' AS INTEGER) AS numero,
  '2026-01-01'::DATE AS data,
  preco::TEXT AS preco_texto
FROM produtos;