CREATE TABLE clientes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nome VARCHAR(100) NOT NULL,
    idade INT,
    cidade VARCHAR(100)
);

INSERT INTO clientes (id, nome, idade, cidade) VALUES
(1, 'João Silva', 30, 'São Paulo'),
(2, 'Maria Oliveira', 25, 'Rio de Janeiro'),
(3, 'Carlos Souza', 35, 'São Paulo'),
(4, 'Ana Costa', 28, 'Belo Horizonte'),
(5, 'Pedro Rocha', 40, 'São Paulo');

CREATE TABLE departamentos (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nome_departamento VARCHAR(100) NOT NULL
);

INSERT INTO departamentos (id, nome_departamento) VALUES
(1, 'Vendas'),
(2, 'Marketing'),
(3, 'TI');

CREATE TABLE funcionarios (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nome VARCHAR(100) NOT NULL,
    salario DECIMAL(10, 2),
    departamento_id INT,
    FOREIGN KEY (departamento_id) REFERENCES departamentos(id)
);

INSERT INTO funcionarios (id, nome, salario, departamento_id) VALUES
(1, 'Fernando Lima', 6000, 1),
(2, 'Juliana Santos', 4500, 2),
(3, 'Ricardo Alves', 5500, 1),
(4, 'Camila Freitas', 7000, 3),
(5, 'Bruno Oliveira', 5000, 2);

CREATE TABLE produtos (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nome VARCHAR(100) NOT NULL,
    preco DECIMAL(10, 2),
    estoque INT
);

INSERT INTO produtos (id, nome, preco, estoque) VALUES
(1, 'Notebook', 3000, 10),
(2, 'Smartphone', 1500, 0),
(3, 'Tablet', 1200, 5),
(4, 'Impressora', 800, 2),
(5, 'Monitor', 700, 0);

CREATE TABLE pedidos (
    id INT PRIMARY KEY AUTO_INCREMENT,
    cliente_id INT,
    valor DECIMAL(10, 2),
    data_pedido DATE,
    FOREIGN KEY (cliente_id) REFERENCES clientes(id)
);

INSERT INTO pedidos (id, cliente_id, valor, data_pedido) VALUES
(1, 1, 150, '2023-01-15'),
(2, 2, 300, '2023-02-20'),
(3, 1, 800, '2023-03-10'),
(4, 3, 1200, '2023-04-05'),
(5, 4, 500, '2023-05-12');

CREATE TABLE vendas (
    id INT PRIMARY KEY AUTO_INCREMENT,
    produto_id INT,
    quantidade INT,
    data_venda DATE,
    FOREIGN KEY (produto_id) REFERENCES produtos(id)
);

INSERT INTO vendas (id, produto_id, quantidade, data_venda) VALUES
(1, 1, 2, '2023-01-10'),
(2, 3, 1, '2023-02-15'),
(3, 2, 3, '2023-03-20'),
(4, 4, 1, '2023-04-25'),
(5, 1, 1, '2023-05-30');

-- Seleciona os clientes de São Paulo com mais de 25 anos, ordenados por nome
SELECT *
FROM clientes
WHERE cidade = 'São Paulo' AND idade > 25
ORDER BY nome
LIMIT 10 OFFSET 5;

-- Conta o total de clientes que atendem aos critérios
SELECT COUNT(*) AS total_clientes
FROM clientes
WHERE cidade = 'São Paulo' AND idade > 25;

-- Calcula a média salarial por departamento e filtra os que têm média > 5000
SELECT departamento_id, AVG(salario) AS media_salarial
FROM funcionarios
GROUP BY departamento_id
HAVING media_salarial > 5000
ORDER BY media_salarial DESC;

-- Lista os pedidos feitos por clientes do Rio de Janeiro, com valor entre 100 e 1000 em 2023
SELECT c.nome AS nome_cliente, p.valor, p.data_pedido
FROM pedidos p
INNER JOIN clientes c ON p.cliente_id = c.id
WHERE c.cidade = 'Rio de Janeiro'
  AND p.valor BETWEEN 100 AND 1000
  AND YEAR(p.data_pedido) = 2023;

-- Calcula o valor total dos pedidos listados
SELECT SUM(p.valor) AS valor_total_pedidos
FROM pedidos p
INNER JOIN clientes c ON p.cliente_id = c.id
WHERE c.cidade = 'Rio de Janeiro'
  AND p.valor BETWEEN 100 AND 1000
  AND YEAR(p.data_pedido) = 2023;

-- Lista os produtos que nunca foram vendidos ou estão com estoque zerado
SELECT id, nome, preco, estoque
FROM produtos
WHERE NOT EXISTS (
    SELECT 1
    FROM vendas
    WHERE vendas.produto_id = produtos.id
)
UNION
SELECT id, nome, preco, estoque
FROM produtos
WHERE estoque = 0
LIMIT 5;

-- Conta o total de produtos que atendem aos critérios
SELECT COUNT(*) AS total_produtos
FROM (
    SELECT id
    FROM produtos
    WHERE NOT EXISTS (
        SELECT 1
        FROM vendas
        WHERE vendas.produto_id = produtos.id
    )
    UNION
    SELECT id
    FROM produtos
    WHERE estoque = 0
) AS produtos_filtrados;