Calculos.sql

USE bd_16_byciclon

--- Tabelas do Banco de dados
SELECT name FROM SyS.tables ORDER BY name;

--- campo da tabela pedidos
SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ='tb_pedidos' 

--- total de clientes por estado
SELECT e.uf, COUNT(*) AS total_clientes 
FROM tb_cliente AS c 
JOIN tb_enderecos e ON c.idEndereco = e.idEndereco
GROUP BY e.uf 
ORDER BY total_clientes DESC;

--- nome dos clientes e valor total que cada um ja gastou 
SELECT 
	c.nome AS Cliente, 
	SUM(p.valorTotal) AS totalGasto
FROM tb_cliente c
JOIN tb_pedidos p ON C.idCliente = P.idCliente
GROUP BY c.nome
ORDER BY totalGasto DESC;

--- valor total de pedidos por cliente e estatisticas de calculo 
SELECT 
	c.nome AS Cliente,
	SUM(p.valorTotal) AS ValorGasto,
	AVG (p.valorTotal) AS MediaPorPedido,
	MIN (p.valorTotal) AS PedidoMaisBarato,
	MAX (p.valorTotal) AS PedidoMaisCaro
FROM tb_cliente c 
JOIN tb_pedidos p ON c.idCliente = p.idCliente
GROUP BY c.nome 

--- Pedidos por mês com media de valor
--- para ano uso year() e para o dia uso day()
SELECT 
	MONTH (dataPedido) AS 'MÊS',
	COUNT(*) AS TotaldePedidos,
	AVG (valorTotal) AS MediaPedido
FROM tb_pedidos 
GROUP BY MONTH(dataPedido)
ORDER BY 'MÊS';


SELECT TOP 3
	c.nome AS Cliente,
	SUM (p.valorTotal) AS 'Total Gasto'
FROM tb_cliente c 
JOIN tb_pedidos p ON c.idCliente = p.idCliente
GROUP BY c.nome 
ORDER BY 'Total Gasto' DESC; 

/*
	Qual cliente comprou o maior número de produtos no mês passado
	e quanto ele gastou no total.
*/

SELECT TOP 3
	c.nome AS Cliente,
	SUM (i.quantidade ) AS TotalProdutosComprados, 
	FORMAT(SUM((i.precoUnitario * i.quantidade )- i.desconto), 'C', 'pt-br' )
	AS TotalGasto
FROM tb_pedidos p
JOIN tb_cliente c ON p.idCliente = c.idCliente
JOIN tb_itens_pedidos i ON p.idPedido = i.idPedido
WHERE 
	p.dataPedido BETWEEN '2025-07-01' AND '2025-07-30'
GROUP BY c.nome 
ORDER BY totalProdutosComprados DESC; 
	
--- Lista todos as Fks de tabela (modo pratico)
EXEC sp_fkeys 'tb_enderecos';
EXEC sp_fkeys 'tb_cliente';
EXEC sp_fkeys 'tb_pedidos';

---- ver o nome das constraints no banco de dados inteiro 
SELECT 
	f.name AS 'Nome Constraint',
	OBJECT_NAME (f.parent_object_id) AS Tabela,
	COL_NAME(fc.parent_object_id, fc.parent_column_id) AS Coluna
FROM sys.foreign_keys  AS f
INNER JOIN sys.foreign_key_columns AS fc
	ON f.object_id = fc.constraint_object_id
ORDER BY Tabela

--- excluir a constraint (chave estrangeira incorreta ) 
---ALTER TABLE ''tb_cliente'' substitui pela tabela com a constraint errada
--- DROP CONSTRAINT '' fk_cliente_endereco''  substituia pela constraint errada

SELECT * FROM tb_cliente
SELECT * FROM tb_vendedores

EXEC sp_fkeys @fktable_name =  'tb_cliente';
EXEC sp_fkeys @fktable_name =  'tb_vendedor';


--- consultar bugs e mostra os dados conflitantes 
SELECT * FROM tb_cliente WHERE idEndereco NOT IN 
(SELECT idEndereco FROM tb_enderecos);

SELECT * FROM tb_vendedores WHERE idEndereco NOT IN 
(SELECT idEndereco FROM tb_enderecos);

--- purga a tabela dos dados conlitantes 
DELETE FROM tb_cliente WHERE idEndereco NOT IN  
(SELECT idEndereco FROM tb_enderecos);