Consultas SQL: Agregações, Joins, Subconsultas e Funções

Exercícios de Consultas SQL

Tema: agregações, junções, subconsultas, views e funções em SQL Server.

1) Consultas com GROUP BY e HAVING (tabelas Midias, Locacao e ItensLocacao)

1) Exiba a quantidade de mídias que cada gênero possui.
SELECT COUNT(*) AS qtd, codGenero FROM Midias GROUP BY codGenero

2) Exiba a quantidade de fitas que cada gênero possui.
SELECT codGenero, SUM(quantidade) AS Soma FROM Midias GROUP BY codGenero

3) Exiba a quantidade de mídias que cada gênero possui, somente para os gêneros que possuem mais de 3 mídias cada.
SELECT COUNT(*) AS qtd, codGenero FROM Midias GROUP BY codGenero HAVING COUNT(*) > 3

4) Exiba quantas locações cada cliente fez.
SELECT COUNT(*) AS qtd, CodCli FROM Locacao GROUP BY CodCli

5) Exiba o total gasto por cliente em locações.
SELECT CodCli, SUM(ValorTotalLoc) AS Soma FROM Locacao GROUP BY CodCli

6) Exiba somente os códigos dos clientes que gastaram mais de 27 reais.
SELECT CodCli, SUM(ValorTotalLoc) AS Soma FROM Locacao GROUP BY CodCli HAVING SUM(ValorTotalLoc) > 27

7) Exiba a quantidade de locações que cada mês possui.
SELECT COUNT(*) AS qtd, MONTH(DataLoc) AS Mês FROM Locacao GROUP BY MONTH(DataLoc)

8) Exiba quantas mídias cada locação possui.
SELECT COUNT(*) AS qtd, CodLocacao FROM ItensLocacao GROUP BY CodLocacao

9) Exiba somente as locações que possuem mais de 3 filmes.
SELECT COUNT(*) AS qtd, CodLocacao FROM ItensLocacao GROUP BY CodLocacao HAVING COUNT(*) > 3

10) Exiba quantas mídias foram devolvidas por data, desconsiderando as mídias que não foram devolvidas.
SELECT COUNT(*) AS qtd, DataDev FROM ItensLocacao WHERE DataDev IS NOT NULL GROUP BY DataDev

2) Consultas na tabela de funcionários (dbo.funcionarios e dbo.setores)

1) Selecione o maior e o menor código de funcionário.
SELECT MAX(func_id) AS Máximo, MIN(func_id) AS Mínimo FROM dbo.funcionarios

2) Selecione a média salarial.
SELECT AVG(func_salario) AS MédiaSalário FROM dbo.funcionarios

3) Selecione todos os campos apenas dos 5 primeiros funcionários, ordenados por ordem decrescente de salário.
SELECT TOP 5 * FROM dbo.funcionarios ORDER BY func_salario DESC

4) Selecione todos os setores cujo nome termina com a letra "a".
SELECT * FROM dbo.setores WHERE setor_nome LIKE '%a'

5) Selecione o setor_id da tabela funcionários, de forma distinta.
SELECT DISTINCT setor_id FROM dbo.funcionarios

6) Selecione a média salarial dos funcionários que são gerentes.
SELECT AVG(func_salario) AS Média, gerente_id FROM dbo.funcionarios WHERE gerente_id IS NOT NULL GROUP BY gerente_id

7) Selecione o código do gerente e a quantidade de funcionários que trabalham para ele.
SELECT COUNT(gerente_id), gerente_id FROM dbo.funcionarios WHERE gerente_id IS NOT NULL GROUP BY gerente_id

8) Selecione todos os funcionários que nasceram antes de 1974 e que ganham mais de 4000,00, OU aqueles que trabalham no setor 1 e que ganham mais de 1000,00.
SELECT * FROM dbo.funcionarios WHERE (YEAR(func_dataNasc) < 1974 AND func_salario > 4000) OR (setor_id = 1 AND func_salario > 1000)

3) Atualizações (UPDATE) e agregações por mês

1) Aumente os preços das locações em 15% para as locações entre os meses 09 e 12 de qualquer ano.
UPDATE Locacao SET ValorTotalLoc = ValorTotalLoc * 1.15 WHERE MONTH(DataLoc) BETWEEN 9 AND 12

2) Atualize a mídia de código 3 para que seu gênero seja igual a 2.
UPDATE Midias SET codGenero = 2 WHERE CodMidia = 3

3) Utilizando a tabela ItensLocacao, exiba a quantidade de filmes devolvidos em cada mês.
SELECT MONTH(DataDev) AS mes, COUNT(*) AS quantidade FROM ItensLocacao GROUP BY MONTH(DataDev)

4) Liste o valor total gasto por cada cliente em cada mês.
SELECT CodCli, MONTH(DataLoc) AS mes, SUM(ValorTotalLoc) AS total FROM Locacao GROUP BY CodCli, MONTH(DataLoc) ORDER BY CodCli, MONTH(DataLoc)

5) Liste o número da locação, a data da locação e o nome do cliente.
SELECT L.CodLocacao, L.DataLoc, (SELECT C.Cliente FROM Cliente C WHERE C.CodCli = L.CodCli) FROM Locacao L

6) Exiba a descrição dos gêneros e a quantidade de filmes alugados para cada um deles, em ordem decrescente de quantidade.
SELECT G.descricao, COUNT(*) AS quantidade FROM Genero G INNER JOIN Midias M ON (M.codGenero = G.Codgenero) INNER JOIN ItensLocacao ITL ON (M.CodMidia = ITL.CodMidia) GROUP BY G.descricao ORDER BY quantidade DESC

7) Liste os gêneros que nunca tiveram um filme alugado.
SELECT G.descricao FROM Genero G LEFT JOIN Midias M ON (M.codGenero = G.Codgenero) LEFT JOIN ItensLocacao ITL ON (M.CodMidia = ITL.CodMidia) GROUP BY G.descricao HAVING COUNT(ITL.CodLocacao) = 0

8) Liste o nome do cliente e os valores gastos em cada mês com aluguel de filmes, separados por mês, com todos os valores de cada cliente em um único registro.
SELECT C.Cliente, ISNULL(SUM(juncao.Mes01), 0) AS M01, ISNULL(SUM(juncao.Mes02), 0) AS M02, ISNULL(SUM(juncao.Mes3), 0) AS M03 FROM (SELECT L.CodCli, SUM(ValorTotalLoc) AS Mes01, 0 AS Mes02, 0 AS Mes3 FROM Locacao L WHERE MONTH(L.DataLoc) = 01 GROUP BY L.CodCli, MONTH(DataLoc) UNION ALL SELECT L.CodCli, 0 AS Mes01, SUM(ValorTotalLoc) AS Mes02, 0 AS Mes3 FROM Locacao L WHERE MONTH(L.DataLoc) = 02 GROUP BY L.CodCli, MONTH(DataLoc)) AS juncao RIGHT JOIN Cliente C ON (juncao.CodCli = C.CodCli) GROUP BY C.Cliente

4) Subconsultas

1) Liste o número da locação e o nome do cliente de todas as locações.
SELECT l.CodLocacao, (SELECT c.Cliente FROM Cliente c WHERE l.CodCli = c.CodCli) AS Nome_Locacao FROM Locacao l

2) Liste o código da locação, o código da mídia, a descrição do filme e a data da locação.
SELECT il.CodLocacao, il.CodMidia, (SELECT m.DescMidia FROM Midias m WHERE m.CodMidia = il.CodMidia) AS DescMidia, (SELECT l.DataLoc FROM Locacao l WHERE il.CodLocacao = l.CodLocacao) AS DataLoc FROM ItensLocacao il

3) Liste todos os filmes do gênero 'Terror'.
SELECT m.DescMidia FROM Midias m WHERE m.codGenero = (SELECT g.Codgenero FROM Genero g WHERE g.descricao LIKE 'Terror%')

4) Liste todos os gêneros dos filmes cujo nome possui a letra '2' em qualquer posição.
SELECT g.descricao FROM Genero g WHERE g.Codgenero = (SELECT m.codGenero FROM Midias m WHERE ...)

5) Criação de tabelas e chaves estrangeiras

Criação da tabela Itens:
CREATE TABLE Itens (numPed INT, codProduto INT, qtdProduto INT NOT NULL, valorTotalProd DECIMAL(10,2) NOT NULL, CONSTRAINT PK_Itens PRIMARY KEY (codProduto, numPed))

Criação do relacionamento via chave estrangeira:
ALTER TABLE Pedido ADD CONSTRAINT FK_Pedido_Cliente FOREIGN KEY (codCliente) REFERENCES Cliente(codCliente)

6) UNION e UNION ALL

Exemplo de UNION:
SELECT * FROM (SELECT func_id AS numero, func_nome AS texto FROM funcionarios UNION SELECT setor_id, setor_nome FROM setores) AS minhaTabela WHERE minhaTabela.numero = 1

Exemplo de UNION ALL:
SELECT * FROM funcionarios UNION ALL SELECT * FROM funcionarios

7) Funções de data e hora

SELECT GETDATE()
SELECT CONVERT(VARCHAR, GETDATE(), 103) -- dd/MM/yyyy
SELECT CONVERT(VARCHAR, GETDATE(), 107) -- mmm dd, yyyy
SELECT CONVERT(VARCHAR, GETDATE(), 114) -- hh:mm:ss:cs

8) Funções de string

SUBSTRING:
SELECT SUBSTRING('Termomecanica', 1, 4)
SELECT SUBSTRING(CONVERT(VARCHAR, GETDATE(), 103), 7, 4)
SELECT SUBSTRING(descricao, 1, 3) AS texto FROM Genero

Tamanho:
SELECT Codgenero, descricao, LEN(descricao) AS qtdLetras FROM Genero

Extração à direita e à esquerda:
SELECT TOP 2 RIGHT(descricao, 4) FROM Genero
SELECT TOP 2 LEFT(descricao, 4) FROM Genero

Repetição de caracteres:
SELECT REPLICATE('*', 10)

Código do gênero no formato 000000 (1 vira 000001, 345 vira 000345):
SELECT REPLICATE('0', 6 - LEN(g.codgenero)) + CAST(codgenero AS VARCHAR), descricao FROM Genero g

9) Múltiplas consultas com JOIN e subconsulta

Funcionário e nome do gerente (com LEFT JOIN):
SELECT f.func_nome AS Nome_Funcionario, ISNULL(g.func_nome, 'Gerente') AS Nome_Gerente FROM Funcionarios f LEFT JOIN Funcionarios g ON (f.gerente_id = g.func_id)

Funcionário e nome do gerente (com subconsulta):
SELECT f.func_nome AS Nome_Funcionario, (SELECT g.func_nome FROM funcionarios g WHERE f.gerente_id = g.func_id) AS Nome_Gerente FROM funcionarios f

Funcionário e nome do setor (com INNER JOIN):
SELECT f.func_nome, s.setor_nome FROM funcionarios f INNER JOIN setores s ON (s.setor_id = f.setor_id)

Funcionário e nome do setor (com subconsulta):
SELECT f.func_nome, (SELECT s.setor_nome FROM setores s WHERE s.setor_id = f.setor_id) AS Nome_Setor FROM funcionarios f

10) Views

Criação de view com JOIN:
CREATE VIEW vw_Pedido AS SELECT numPed, nome, endereco FROM Pedido p INNER JOIN Cliente c ON (p.codCliente = c.codCliente)

Uso de view junto a JOIN:
SELECT * FROM vw_Pedido INNER JOIN Itens it ON (it.numPed = vw_Pedido.numPed) WHERE qtdProd > 25

Views de mídias e gêneros:
CREATE VIEW vm_MidiaGenero AS SELECT m.*, descricao FROM Midias m INNER JOIN Genero g ON (m.codGenero = g.codGenero)

Views de total locado por mês:
CREATE VIEW vw_TotalLocado_01 AS SELECT MONTH(L.DataLoc) AS Mes, SUM(L.ValorTotalLoc) AS Total FROM Locacao L WHERE MONTH(L.DataLoc) = 01 GROUP BY MONTH(L.DataLoc)

CREATE VIEW vw_TotalLocado_02 AS SELECT MONTH(L.DataLoc) AS Mes, SUM(L.ValorTotalLoc) AS Total FROM Locacao L WHERE MONTH(L.DataLoc) = 02 GROUP BY MONTH(L.DataLoc)

Total mês a mês de locações feitas:
SELECT SUM(tot01) AS tot01, SUM(tot02) AS tot02 FROM (SELECT Total AS tot01, 0 AS tot02 FROM vw_TotalLocado_01 UNION ALL SELECT 0 AS tot01, Total AS tot02 FROM vw_TotalLocado_02) AS novaTab

Listar as 4 primeiras mídias alugadas:
SELECT TOP 4 * FROM ItensLocacao

11) Filtros com MAX, IN e BETWEEN

Exibir o nome do filme mais caro (a primeira tentativa com MAX sem subconsulta gera erro; a forma correta usa subconsulta):
SELECT * FROM Midias WHERE Midias.ValorUnit = (SELECT MAX(ValorUnit) FROM Midias)

Uso de IN:
SELECT * FROM Midias WHERE codGenero IN (1, 4, 6)

Descrição do gênero de todas as mídias com preço entre 3,00 e 7,00 reais, sem JOIN:
SELECT g.descricao FROM Genero g WHERE g.Codgenero IN (SELECT m.codGenero FROM Midias m WHERE m.ValorUnit BETWEEN 3 AND 7)