!SQLSERVER_EXER_LIST.md

Lista de Exercícios SQL Server

Alter_Update_Drop_Table-Column_Funcionarios.sql

--Add Colunas em uma tabela
ALTER TABLE Funcionarios
ADD cargo VARCHAR(100), bonus FLOAT

GO

--Atualizar dados de um determinado registro
UPDATE Funcionarios
SET cargo = 'Analista', bonus = 0.15
WHERE id_funcionario = 1

GO

--Alterar Tipo da coluna
ALTER TABLE Funcionarios
ALTER COLUMN salario INT

GO

--Apagar Colunas
ALTER TABLE Funcionarios 
DROP COLUMN cargo, bonus

GO

SELECT * FROM Funcionarios

Case-When-exemplos_SQLhashtag.sql

SELECT
	CustomerKey,
	FirstName + ' ' + LastName AS 'Nome Completo',
	CASE
		WHEN Gender = 'F' THEN 'Feminino'
		WHEN Gender = 'M' THEN 'Masculino'
	END AS 'Sexo',
	CASE
		WHEN MaritalStatus = 'M' THEN 'Casado'
		WHEN MaritalStatus = 'S' THEN 'Solteiro'
	END AS 'Relacionamento'
FROM
	DimCustomer
WHERE CustomerType = 'Person'

Select
	Brandname AS 'Marca',
	REPLACE(REPLACE(ProductName, BrandName, ''), ColorName, '') AS 'Produto',
	ColorName AS 'Cor',
	UnitPrice AS 'Pre�o unit�rio',
	CASE
		WHEN BrandName = 'Contoso' AND ColorName = 'Red' THEN UnitPrice * (1 - 0.1)
	END AS 'Pre�o com desconto (10%)'
FROM
	DimProduct

Create_Insert_Table_Funcionarios.sql

USE BDImpressionador

CREATE TABLE Funcionarios(
	id_funcionario INT,
	nome_funcionario VARCHAR(100),
	salario FLOAT,
	data_nascimento DATETIME
)

INSERT INTO Funcionarios(id_funcionario, nome_funcionario, salario, data_nascimento)
VALUES
	(1, 'Lucas', 1500, '03/20/1998'),
	(2, 'Andressa', 2300, '12/07/1988'),
	(3, 'Felipe', 4000, '02/13/1993'),
	(4, 'Marcelo', 7100, '04/10/1993'),
	(5, 'Carla', 3200, '09/02/1986'),
	(6, 'Juliana', 5500, '01/21/1989'),
	(7, 'Mateus', 1900, '11/02/1993'),
	(8, 'Sandra', 3900, '05/09/1990'),
	(9, 'Andr�', 1000, '03/13/1994'),
	(10, 'Julio', 4700, '07/05/1992')

SELECT * FROM Funcionarios

Exercicio1_SQLhashtag.sql

/*1. Voc� � respons�vel por controlar s dados de clientes e de produtos da sua empresa.
O que voc� precisar� fazer � confirmar se:

a. Existem 2.517 produtos cadastrados na base e, se tiver, voc� dever� reportar ao seu
gestor para saber se existe alguma defasagem no controle dos produtos.

b. At� o m�s passado, a empresa tinha um total de 19.500 clientes na base de controle.
Verifique se esse n�mero aumentou ou reduziu.*/

-- Respostas
SELECT * FROM DimProduct --a. 2517 Produtos.

SELECT * FROM DimCustomer --b. 18869 Clientes, diminuiu.

Exercicio2_SQLhashtag.sql

/*2. Voc� trabalha no setor de marketing da empresa Contoso e acaba de ter uma ideia de oferecer
descontos especiais para os clientes no dia de seus anivers�rios. Para isso, voc� vai precisar
listar todos os clientes e as Suas respectivas datas de nascimento, al�m de um contato.

a) Selecione as colunas: CustomerKey, FirstName, EmailAddress, BirthDate da tabela
dimCustomer.

b) Renomeie as colunas dessa tabela usando o alias (comando AS)*/

SELECT CustomerKey AS 'Chave do Cliente',
	FirstName AS 'Primeiro Nome',
	EmailAddress AS 'Email',
	BirthDate AS 'Data de Nascimento'
FROM DimCustomer

Exercicio3_SQLhashtag.sql

/*3. A Contoso est� comemorando anivers�rio de inaugura��o de 10 anos e pretende fazer uma
a��o de premia��o para os clientes. A empresa quer presentear os primeiros clientes desde
a inaugura��o.
Voc� foi alocado para levar adiante essa a��o. Para isso, voc� ter� que fazer 0 seguinte:

a) A Contoso decidiu presentear os primeiros 100 clientes da hist�ria com um vale compras
de R$ 10.000. Utilize um comando em SQL para retornar uma tabela com os 100 primeiros clientes
da tabela dimCustomer (selecione todas as colunas).

b) A Contoso decidiu presentear os primeiros 20% de clientes da hist�ria com um vale
compras de R$ 2.000. Utilize um comando em SQL para retornar 10% das linhas da sua
tabela dimCustomer (selecione todas as colunas).

c) Adapte o c�digo do item a) para retornar apenas as 100 primeiras linhas, mas apenas as
colunas FirstName, EmailAddress, BirthDate.

d) Renomeie as colunas anteriores para nomes em portugu�s.*/

SELECT TOP(100) --a) Primeiros 100 Clientes
	*
FROM DimCustomer

SELECT TOP(20) PERCENT --b) Primeiros 20% Clientes
	*
FROM DimCustomer

SELECT TOP(100) FirstName AS 'Primeiro Nome', --c/d) Resposta a) adaptada para 3 colunas e nomes renomeados
	EmailAddress AS 'Email',
	BirthDate AS 'Data de nascimento'
FROM DimCustomer

Exercicio4_SQLhashtag.sql

/*4. A empresa Contoso precisa fazer contato com os fornecedores de produtos para repor o
estoque. Voc� � da �rea de compras e precisa descobrir quem s�o esses fornecedores.
Utilize um comando em SQL para retornar apenas os nomes dos fornecedores na tabela
dimProduct e renomeie essa nova coluna da tabela.*/

SELECT DISTINCT 
	Manufacturer AS 'Fornecedores'
FROM DimProduct

Exercicio5_SQLhashtag.sql

/*5. O seu trabalho de investiga��o n�o para. Voc� precisa descobrir se existe algum produto
registrado na base de produtos que ainda n�o tenha sido vendido. Tente chegar nessa
informa��o.

Obs: caso tenha algum produto que ainda n�o tenha sido vendido, voc� n�o precisa descobrir
qual �, � suficiente saber se teve ou n�o algum produto que ainda n�o foi vendido.*/

SELECT * FROM DimProduct -- Temos 2517 Produtos registrados

SELECT DISTINCT ProductKey FROM FactSales -- 2516 Produtos Vendidos, ou seja, apenas 1 n�o foi vendido. 

Format-exemplos_SQLhashtag.sql

SELECT FORMAT(6772237, 'N') AS 'Number',
	FORMAT(6772237, 'G') AS 'Geral',
	FORMAT(6772237, 'C') AS 'Curency',
	FORMAT(CAST('10/01/2009' AS DATETIME), 'yyyy') AS 'Ano',
	FORMAT(CAST('10/01/2009' AS DATETIME), 'MM') AS 'Mes (n�mero)',
	FORMAT(CAST('10/01/2009' AS DATETIME), 'dd') AS 'Dia (n�mero)',
	FORMAT(CAST('10/01/2009' AS DATETIME), 'MMM', 'pt-BR') AS 'Mes (abrv.)',
	FORMAT(CAST('10/01/2009' AS DATETIME), 'MMMM', 'pt-BR') AS 'Mes (nome)',
	FORMAT(CAST('10/01/2009' AS DATETIME), 'ddd', 'pt-BR') AS 'Dia (abrv.)',
	FORMAT(CAST('10/01/2009' AS DATETIME), 'dddd', 'pt-BR') AS 'Dia (nome)',
	FORMAT(123456789000, '###-###-###-###') AS 'Personalizado'

FxAND-OR-NOT-1_SQLhashtag.sql

/*Exercicxos de Fixa��o: AND, OR e NOT
1. Selecione todas as linhas da tabela dimEmployee de funcion�rios do sexo feminino E do
departamento de Finan�as.*/

SELECT
	*
FROM DimEmployee
WHERE Gender = 'F' AND DepartmentName = 'Finance'

FxAND-OR-NOT-2_SQLhashtag.sql

/*Exerc�cios de Fixa��o: AND, OR e NOT
2. Selecione todas as linhas da tabela dimProduct de produtos da marca Contoso E da cor
vermelha E que tenham um UnitPrice maior ou igual a $100.*/

SELECT *
FROM DimProduct
WHERE BrandName = 'Contoso'
	AND ColorName = 'Red'
	AND UnitPrice >= 100

FxAND-OR-NOT-3_SQLhashtag.sql

--Exerc�cios de Fixa��o: AND. OR e NOT

--3. Selecione todas as linhas da tabela dimproduct com produtos da marca Litware OU da marca Fabrikam OU da cor Preta.

SELECT *
FROM DimProduct
WHERE BrandName = 'Litware'
	OR BrandName = 'Fabrikam'
	OR ColorName = 'Black'

FxAND-OR-NOT-4_SQLhashtag.sql

-- Exerc�cios de Fixa��o: AND, OR e NOT
--4. Selecione todas as linhas da tabela dimSaIesTerritory onde o continente � a Europa mas o pa�s N�O � igual a It�lia

SELECT *
FROM DimSalesTerritory
WHERE SalesTerritoryGroup = 'Europe'
	AND NOT SalesTerritoryCountry = 'Italy'

FxAND-OR-NOT-5_SQLhashtag.sql

-- Cuidados ao utilizar o AND em conjunto com o OR

-- Exemplo: Selecione todas as linhas da tabela dimProduct onde a cor do Produto pode ser igual a Preto OU Vermelho, MAS a marca deve ser obrigatoriamente igual a Fabrikam.

SELECT * 
FROM DimProduct
WHERE (ColorName = 'Black' 
	OR ColorName = 'Red')  -- Parenteses para priorizar primeiro o filtro de Cor
	AND BrandName = 'Fabrikam'

FxAND-OR-NOT-7_SQLhashtag.sql

/*7. De acordo com a quantidade de funcion�rios, cada loja receber� uma determinada quantidade
de m�quinas de caf�. As lojas ser�o divididas em 3 categorias:

CATEGORIA 1: De 1 a 20 funcion�rios -> 1 m�quina de caf�
CATEGORIA 2: De 21 a 50 funcion�rios -> 2 m�quinas de caf�
CATEGORIA 3: Acima de 51 funcion�rios -> 3 m�q de caf�

Identifique, para cada caso, quais s�o as lojas de cada uma das 3 categorias acima (basta fazer
uma verifica��o).*/

SELECT 
	StoreName AS 'CATEGORIA 1' 
FROM 
	DimStore
WHERE 
	EmployeeCount <= 20
	-- EmployeeCount BETWEEN NULL AND 20 

SELECT 
	StoreName AS 'CATEGORIA 2' 
FROM 
	DimStore
WHERE 
	EmployeeCount > 20 AND EmployeeCount <= 50
	-- EmployeeCount BETWEEN 21 AND 50

SELECT 
	StoreName AS 'CATEGORIA 3' 
FROM 
	DimStore
WHERE 
	EmployeeCount > 50

MD-10-Exercicio-1_SQLhashtag.sql

--M�DULO 10: EXERC�CIOS

/*1. Quando estamos manipulando tabelas, � importante pensar em como os dados ser�o 
apresentados em um relat�rio. Imagine os nomes dos produtos da tabela DimProduct. Os 
textos s�o bem grandes e pode ser que mostrar os nomes completos dos produtos n�o seja a 
op��o mais interessante, pois provavelmente n�o v�o caber em um gr�fico e a visualiza��o 
ficar� ruim.

--a) Seu gestor te pede para listar todos os produtos para que seja criado um gr�fico para ser 
apresentado na reuni�o di�ria de equipe. Fa�a uma consulta � tabela DimProduct que 
retorne (1) o nome do produto e (2) a quantidade de caracteres que cada produto tem, 
e ordene essa tabela do produto com a maior quantidade de caracteres para a menor.*/

SELECT
	DimProduct.ProductName,
	LEN(Dimproduct.ProductName) AS 'Qtd. Caracteres'
FROM
	DimProduct
ORDER BY LEN(ProductName) DESC

--b) Qual � a m�dia de caracteres dos nomes dos produtos?

SELECT
	AVG(LEN(ProductName)) AS 'M�dia Caracteres'
FROM 
	DimProduct

--c) Analise a estrutura dos nomes dos produtos e verifique se seria poss�vel reduzir o tamanho do nome dos produtos. (Dica: existem informa��es redundantes nos nomes dos produtos? Seria poss�vel substitu�-las?)

SELECT
	DimProduct.ProductName,
	DimProduct.ColorName,
	DimProduct.BrandName,
	TRIM(REPLACE(REPLACE(DimProduct.ProductName, DimProduct.BrandName, ''), ColorName, '')) AS 'Nome Reduzido'
FROM
	DimProduct

--d) Qual � a m�dia de caracteres nesse novo cen�rio?

SELECT
	AVG(LEN(TRIM(REPLACE(REPLACE(DimProduct.ProductName, DimProduct.BrandName, ''), ColorName, '')))) AS 'Nova M�dia Caracters'
FROM
	DimProduct

MD-10-Exercicio-2_SQLhashtag.sql

/*M�DULO 10: EXERC�CIOS 

2.  A coluna StyleName da tabela DimProduct possui alguns c�digos identificados por n�meros 
distintos, que v�o de 0 at� 9, como pode ser visto no exemplo abaixo. 
Por�m, o setor de controle decidiu alterar a identifica��o do StyleName, e em vez de usar 
n�meros, a ideia agora � passar a usar letras para substituir os n�meros, conforme exemplo 
abaixo: 
0 -> A, 1 -> B, 2 -> C, 3 -> D, 4 -> E, 5 -> F, 6 -> G, 7 -> H, 8 -> I, 9 - J 
� de sua responsabilidade alterar os n�meros por letras na coluna StyleName da tabela 
DimProduct. Utilize uma fun��o que permita fazer essas substitui��es de forma pr�tica e r�pida.*/

SELECT
	TRANSLATE(StyleName, '0123456789', 'ABCDEFGHIJ') AS 'StyleName'
FROM 
	DimProduct

MD-10-Exercicio-3_SQLhashtag.sql

/*M�DULO 10: EXERC�CIOS 

3. O setor de TI est� criando um sistema para acompanhamento individual de cada funcion�rio da 
empresa Contoso. Cada funcion�rio receber� um login e senha. O login de cada funcion�rio ser� 
o ID do e-mail, como no exemplo abaixo: 
J� a senha ser� o FirtName + o dia do ano em que o funcion�rio nasceu, em MAI�SCULA. Por 
exemplo, o funcion�rio com E-mail: mark0@contoso.com e data de nascimento 15/01/1990 
dever� ter a seguinte senha: 
Login: mark0 
Senha: MARK15 
O respons�vel pelo TI pediu a sua ajuda para retornar uma tabela contendo as seguintes colunas 
da tabela DimEmployee: Nome completo (FirstName + LastName), E-mail, ID do e-mail e Senha. 
Portanto, fa�a uma consulta � tabela DimProduct e retorne esse resultado.*/ 

SELECT
	CONCAT(DimEmployee.FirstName, ' ', DimEmployee.LastName) AS 'Nome Completo',
	DimEmployee.EmailAddress AS 'E-mail',
	SUBSTRING(EmailAddress, 1, CHARINDEX('@', EmailAddress) - 1) AS 'ID do e-mail',
	--LEFT(EmailAdress, (CHARINDEX('@', EmailAdress) - 1)
	CONCAT(UPPER(FirstName), DATEPART(DAYOFYEAR, BirthDate)) AS 'Senha'
FROM
	DimEmployee

MD-10-Exercicio-4_SQLhashtag.sql

/*M�DULO 10: EXERC�CIOS 

4. A tabela DimCustomer possui o primeiro registro de vendas no ano de 2001.  
Como forma de reconhecer os clientes que compraram nesse ano, o setor de Marketing solicitou 
a voc� que retornasse uma tabela com todos os clientes que fizeram a sua primeira compra neste 
ano para que seja enviado uma encomenda com um presente para cada um. 
Para fazer esse filtro, voc� pode utilizar a coluna DateFirstPurchase, que cont�m a informa��o da 
data da primeira compra de cada cliente na tabela DimCustomer. 
Voc� dever� retornar as colunas de FirstName, EmailAddress, AddressLine1 e DateFirstPurchase 
da tabela DimCustomer, considerando apenas os clientes que fizeram a primeira compra no ano 
de 2001.*/

SELECT
	FirstName AS 'Nome',
	EmailAddress AS 'E-mail',
	AddressLine1 AS 'Endere�o 1',
	DateFirstPurchase AS 'Primeira Compra'
FROM
	DimCustomer
WHERE DATEPART(YEAR, DateFirstPurchase) = 2001
--FORMAT(DateFirstPurchase, 'yyyy') = 2001
--YEAR(DateFirstPuchase) = 2001

MD-10-Exercicio-5_SQLhashtag.sql

/*M�DULO 10: EXERC�CIOS 

5. A tabela DimEmployee possui uma informa��o de data de contrata��o (HireDate). A �rea de 
RH, no entanto, precisa das informa��es dessas datas de forma separada em dia, m�s e ano, pois 
ser� feita uma automatiza��o para cria��o de um relat�rio de RH, e facilitaria muito se essas 
informa��es estivessem separadas em uma tabela. 
Voc� dever� realizar uma consulta � tabela DimEmployee e retornar uma tabela contendo as 
seguintes informa��es: FirstName, EmailAddress, HireDate, al�m das colunas de Dia, M�s e Ano 
de contrata��o.

Obs1: A coluna de M�s deve conter o nome do m�s por extenso, e n�o o n�mero do m�s.

Obs2: Lembre-se de nomear cada uma dessas colunas em sua consulta para garantir que o 
entendimento de cada informa��o ficar� 100% claro.*/

SELECT
	FirstName AS 'Nome',
	EmailAddress AS 'E-mail',
	HireDate AS 'Data de Contrata��o',
	DATEPART(DAY, HireDate) As 'Dia',
	DATENAME(MONTH, HireDate) AS 'M�s',
	FORMAT(HireDate,'MMMM', 'pt-BR') AS 'M�s (pt-BR)', 
	DATEPART(YEAR, HireDate) AS 'Ano'
FROM
	DimEmployee

MD-10-Exercicio-6_SQLhashtag.sql

/*M�DULO 10: EXERC�CIOS 

6. Descubra qual � a loja que possui o maior tempo de atividade (em dias). Voc� dever� fazer essa 
consulta na tabela DimStore, e considerar a coluna OpenDate como refer�ncia para esse c�lculo.*/

SELECT TOP(1)
	StoreName AS 'Loja',
	OpenDate AS 'Data de Abertura',
	DATEDIFF(DAY, OpenDate, GETDATE()) AS 'Maior Tempo Ativo (dias)'
FROM
	DimStore
WHERE CloseDate IS NULL
ORDER BY DATEDIFF(DAY, OpenDate, GETDATE()) DESC

MD-11-Exercicio-1_SQLhashtag.sql

/*M�DULO 11: EXERC�CIOS 

1. O setor de vendas decidiu aplicar um desconto aos produtos de acordo com a sua classe. O 
percentual aplicado dever� ser de: 

Economy -> 5% 
Regular -> 7% 
Deluxe -> 9% 

a) Fa�a uma consulta � tabela DimProduct que retorne as seguintes colunas: ProductKey, 
ProductName, e outras duas colunas que dever�o retornar o % de Desconto e UnitPrice com 
desconto.*/

SELECT
	ProductKey,
	ProductName,
	CASE
		WHEN ClassName = 'Deluxe' THEN 0.09
		WHEN ClassName = 'Regular' THEN 0.07
		WHEN ClassName = 'Economy' THEN 0.05
	END AS '% Desconto',
	UnitPrice AS 'Pre�o',
	UnitPrice * (1 - CASE WHEN ClassName = 'Deluxe' THEN 0.09
	WHEN ClassName = 'Regular' THEN 0.07
	WHEN ClassName = 'Economy' THEN 0.05 END) AS 'Pre�o com desconto'
FROM
	Dimproduct

/*b) Fa�a uma adapta��o no c�digo para que os % de desconto de 5%, 7% e 9% sejam facilmente 
modificados (dica: utilize vari�veis). */

DECLARE @varDeluxe FLOAT = 0.09, @varRegular FLOAT = 0.07, @varEconomy FLOAT = 0.05

SELECT
	ProductKey,
	ProductName,
	CASE
		WHEN ClassName = 'Deluxe' THEN @varDeluxe
		WHEN ClassName = 'Regular' THEN @varRegular
		WHEN ClassName = 'Economy' THEN @varEconomy
	END AS '% Desconto',
	UnitPrice AS 'Pre�o',
	UnitPrice * (1 - 
		CASE 
			WHEN ClassName = 'Deluxe' THEN @varDeluxe
			WHEN ClassName = 'Regular' THEN @varRegular
			WHEN ClassName = 'Economy' THEN @varEconomy 
		END) AS 'Pre�o com desconto'
FROM
	DimProduct

MD-11-Exercicio-2_SQLhashtag.sql

/*M�DULO 11: EXERC�CIOS 

2. Voc� ficou respons�vel pelo controle de produtos da empresa e dever� fazer uma an�lise da 
quantidade de produtos por Marca. 
A divis�o das marcas em categorias dever� ser a seguinte:

CATEGORIA A: Mais de 500 produtos  
CATEGORIA B: Entre 100 e 500 produtos  
CATEGORIA C: Menos de 100 produtos  

Fa�a uma consulta � tabela DimProduct e retorne uma tabela com um agrupamento de Total de 
Produtos por Marca, al�m da coluna de Categoria, conforme a regra acima.*/

SELECT
	BrandName AS 'Marca',
	COUNT(ProductKey) AS 'Qtd. Produto',
	CASE
		WHEN COUNT(ProductKey) > 500 THEN 'A'
		WHEN COUNT(ProductKey) >= 100 THEN 'B'
		WHEN COUNT(ProductKey) < 100 THEN 'C'
	END AS 'Categoria'
FROM
	Dimproduct
GROUP BY BrandName

MD-11-Exercicio-3_SQLhashtag.sql

/*M�DULO 11: EXERC�CIOS 

3. Ser� necess�rio criar uma categoriza��o de cada loja da empresa considerando a quantidade de 
funcion�rios de cada uma. A l�gica a ser seguida ser� a l�gica abaixo:

EmployeeCount >= 50; 'Acima de 50 funcion�rios' 
EmployeeCount >= 40; 'Entre 40 e 50 funcion�rios' 
EmployeeCount >= 30; 'Entre 30 e 40 funcion�rios' 
EmployeeCount >= 20; 'Entre 20 e 30 funcion�rios' 
EmployeeCount >= 40; 'Entre 10 e 20 funcion�rios' 
Caso contr�rio: 'Abaixo de 10 funcion�rios'

Fa�a uma consulta � tabela DimStore que retorne as seguintes informa��es: StoreName, 
EmployeeCount e a coluna de categoria, seguindo a regra acima.*/

SELECT
	StoreName,
	EmployeeCount,
	CASE 
		WHEN EmployeeCount >= 50 THEN 'Acima de 50 funcion�rios'
		WHEN EmployeeCount >= 40 THEN 'Entre 40 e 50 funcion�rios'
		WHEN EmployeeCount >= 30 THEN 'Entre 30 e 40 funcion�rios'
		WHEN EmployeeCount >= 20 THEN 'Entre 20 e 30 funcion�rios'
		WHEN EmployeeCount >= 10 THEN 'Entre 10 e 20 funcion�rios'
		ELSE 'Abaixo de 10 funcion�rios'
	END AS 'Categoria'
FROM
	DimStore

MD-11-Exercicio-4_SQLhashtag.sql

/*M�DULO 11: EXERC�CIOS 

4. O setor de log�stica dever� realizar um transporte de carga dos produtos que est�o no dep�sito 
de Seattle para o dep�sito de Sunnyside.  
N�o se tem muitas informa��es sobre os produtos que est�o no dep�sito, apenas se sabe que 
existem 100 exemplares de cada Subcategoria. Ou seja, 100 laptops, 100 c�meras digitais, 100 
ventiladores, e assim vai. 
O gerente de log�stica definiu que os produtos ser�o transportados por duas rotas distintas. Al�m 
disso, a divis�o dos produtos em cada uma das rotas ser� feita de acordo com as subcategorias (ou 
seja, todos os produtos de uma mesma subcategoria ser�o transportados pela mesma rota):

Rota 1: As subcategorias que tiverem uma soma total menor que 1000 kg dever�o ser 
transportados pela Rota 1. 

Rota 2: As subcategorias que tiverem uma soma total maior ou igual a 1000 kg dever�o ser 
transportados pela Rota 2. 

Voc� dever� realizar uma consulta � tabela DimProduct e fazer essa divis�o das subcategorias por 
cada rota. Algumas dicas: 

- Dica 1: A sua consulta dever� ter um total de 3 colunas: Nome da Subcategoria, Peso Total e Rota. 

- Dica 2: Como n�o se sabe quais produtos existem no dep�sito, apenas que existem 100 
exemplares de cada subcategoria, voc� dever� descobrir o peso m�dio de cada subcategoria e 
multiplicar essa m�dia por 100, de forma que voc� descubra aproximadamente qual � o peso total 
dos produtos por subcategoria.

- Dica 3: Sua resposta final dever� ter um JOIN e um GROUP BY.*/

SELECT
	DimProductSubcategory.ProductSubcategoryName AS 'Subcategoria',
	ROUND(AVG(DimProduct.Weight), 2) * 100 AS 'Peso Total', 
	CASE
		WHEN ROUND(AVG(DimProduct.Weight), 2) * 100 >= 1000 THEN 2
		WHEN ROUND(AVG(DimProduct.Weight), 2) * 100 < 1000 THEN 1
	END AS 'Rota'
FROM
	DimProduct
INNER JOIN DimProductSubcategory
	ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey
GROUP BY DimProductSubcategory.ProductSubcategoryName

MD-11-Exercicio-5_SQLhashtag.sql

/*M�DULO 11: EXERC�CIOS 

5. O setor de marketing est� com algumas ideias de a��es para alavancar as vendas em 2021. Uma 
delas consiste em realizar sorteios entre os clientes da empresa.  
Este sorteio ser� dividido em categorias: 

�Sorteio M�e do Ano�: Nessa categoria v�o participar todas as mulheres com filhos. 
�Sorteio Pai do Ano�: Nessa categoria v�o participar todos os homens com filhos. 
�Caminh�o de Pr�mios�: Nessa categoria v�o participar todas os demais clientes (homens e mulheres sem filhos). 

Seu papel ser� realizar uma consulta � tabela DimCustomer e retornar 3 colunas: 

- FirstName AS �Nome� 
- Gender AS �Sexo� 
- TotalChildren AS �Qtd. Filhos� 
- EmailAdress AS �E-mail� 
- A��o de Marketing: nessa coluna voc� dever� dividir os clientes de acordo com as categorias 
�Sorteio M�e do Ano�, �Sorteio Pai do Ano� e �Caminh�o de Pr�mios�. */

SELECT
	FirstName + ' ' + LastName AS 'Nome Completo',
	Gender AS 'Sexo',
	TotalChildren AS 'Qtd. Filho',
	EmailAddress AS 'E-mail',
	CASE
		WHEN TotalChildren > 0 AND Gender = 'F' THEN 'Sorteio M�e do ano'
		WHEN TotalChildren > 0 AND Gender = 'M' THEN 'Sorteio Pai do ano'
		ELSE 'Caminh�o de Pr�mios'
	END AS 'A��o de Marketing'
FROM
	DimCustomer
WHERE CustomerType = 'Person'

MD-11-Exercicio-6_SQLhashtag.sql

/*1M�DULO 11: EXERC�CIOS 

6. Descubra qual � a loja que possui o maior tempo de atividade (em dias). Voc� dever� fazer essa 
consulta na tabela DimStore, e considerar a coluna OpenDate como refer�ncia para esse c�lculo. 
Aten��o: lembre-se que existem lojas que foram fechadas. */

SELECT
	StoreName AS 'Loja',
	OpenDate AS 'Data de abertura',
	CASE
		WHEN CloseDate IS NOT NULL THEN DATEDIFF(DAY, OpenDate, CloseDate)
		ELSE DATEDIFF(DAY, OpenDate, GETDATE())
	END AS 'Tempo em atividade (dias)',
	Status
FROM
	DimStore
ORDER BY 'Tempo em atividade (dias)' DESC

MD-12-Exercicio-1_SQLhashtag.sql

/*Exerc�cios VIEW: M�dulo 12

1. a) A partir da tabela DimProduct, crie uma View contendo as informa��es de 
ProductName, ColorName, UnitPrice e UnitCost, da tabela DimProduct. Chame essa View 
de vwProdutos. */

USE ContosoRetailDW

GO
CREATE VIEW vwProdutos AS
SELECT
	ProductName,
	ColorName,
	UnitPrice,
	UnitCost
FROM
	DimProduct

/*b) A partir da tabela DimEmployee, crie uma View mostrando FirstName, BirthDate, 
DepartmentName. Chame essa View de vwFuncionarios.*/
GO
CREATE VIEW vwFuncionarios AS
SELECT
	FirstName,
	BirthDate,
	DepartmentName
FROM
	DimEmployee
GO
/*c) A partir da tabela DimStore, crie uma View mostrando StoreKey, StoreName e 
OpenDate. Chame essa View de vwLojas. */

CREATE VIEW vwLojas AS
SELECT
	StoreKey,
	StoreName,
	OpenDate
FROM
	DimStore

MD-12-Exercicio-2_SQLhashtag.sql

/*Exerc�cios VIEW: M�dulo 12

2. Crie uma View contendo as informa��es de Nome Completo (FirstName + 
LastName), G�nero (por extenso), E-mail e Renda Anual (formatada com R$). 
Utilize a tabela DimCustomer. Chame essa View de vwClientes. */

USE ContosoRetailDW
GO
ALTER VIEW vwClientes AS
SELECT
	CONCAT(FirstName, ' ', LastName) AS 'CompleteName',
	CASE
		WHEN Gender = 'F' THEN 'Feminino'
		WHEN Gender = 'M' THEN 'Masculino'
	END AS 'Gender',
	EmailAddress,
	FORMAT(YearlyIncome, 'C', 'pt-BR') AS 'YearlyIncomeReal'
FROM
	DimCustomer
GO
SELECT * FROM vwClientes

MD-12-Exercicio-3_SQLhashtag.sql

/*Exerc�cios VIEW: M�dulo 12

3. a) A partir da tabela DimStore, crie uma View que considera apenas as lojas ativas. Fa�a 
um SELECT de todas as colunas. Chame essa View de vwLojasAtivas.*/



/*b) A partir da tabela DimEmployee, crie uma View de uma tabela que considera apenas os 
funcion�rios da �rea de Marketing. Fa�a um SELECT das colunas: FirstName, EmailAddress 
e DepartmentName. Chame essa de vwFuncionariosMkt.*/



/* c) Crie uma View de uma tabela que considera apenas os produtos das marcas Contoso e 
Litware. Al�m disso, a sua View deve considerar apenas os produtos de cor Silver. Fa�a 
um SELECT de todas as colunas da tabela DimProduct. Chame essa View de 
vwContosoLitwareSilver. */

MD-12-Exercicio-4_SQLhashtag.sql

/*Exerc�cios VIEW: M�dulo 12

4. Crie uma View que seja o resultado de um agrupamento da tabela FactSales. Este 
agrupamento deve considerar o SalesQuantity (Quantidade Total Vendida) por Nome do 
Produto. Chame esta View de vwTotalVendidoProdutos. 
OBS: Para isso, voc� ter� que utilizar um JOIN para relacionar as tabelas FactSales e 
DimProduct. */
USE ContosoRetailDW
GO
CREATE VIEW vwTotalVendidoProdutos AS
SELECT
	ProductName,
	SUM(SalesQuantity) AS 'Qtd. Vendida'
FROM
	FactSales
INNER JOIN DimProduct
	ON FactSales.ProductKey = DimProduct.ProductKey
GROUP BY ProductName
GO
SELECT * FROM vwTotalVendidoProdutos

MD-12-Exercicio-5_SQLhashtag.sql

/*Exerc�cios VIEW: M�dulo 12

5. Fa�a as seguintes altera��es nas tabelas da quest�o 1. 
a. Na View criada na letra a da quest�o 1, adicione a coluna de BrandName.*/
USE ContosoRetailDW
GO

ALTER VIEW vwProdutos AS
SELECT
	ProductName,
	ColorName,
	BrandName,
	UnitPrice,
	UnitCost
FROM
	DimProduct

/*b. Na View criada na letra b da quest�o 1, fa�a um filtro e considere apenas os 
funcion�rios do sexo feminino. */
GO

ALTER VIEW vwFuncionarios AS
SELECT
	FirstName,
	BirthDate,
	DepartmentName
FROM
	DimEmployee
WHERE Gender = 'F'

GO
/*c. Na View criada na letra c da quest�o 1, fa�a uma altera��o e filtre apenas as lojas 
ativas. */

ALTER VIEW vwLojas AS
SELECT
	StoreKey,
	StoreName,
	OpenDate
FROM
	DimStore
WHERE Status = 'On'

MD-12-Exercicio-6_SQLhashtag.sql

/*Exerc�cios VIEW: M�dulo 12

6. a) Crie uma View que seja o resultado de um agrupamento da tabela DimProduct. O 
resultado esperado da consulta dever� ser o total de produtos por marca. Chame essa 
View de vw_6a.*/
USE ContosoRetailDW
GO
CREATE VIEW vw_6a AS
SELECT
	BrandName AS 'Marca',
	COUNT(*) AS 'Total Produtos'
FROM
	DimProduct
GROUP BY BrandName
GO
/*b) Altere a View criada no exerc�cio anterior, adicionando o peso total por marca. Aten��o: 
sua View final dever� ter ent�o 3 colunas: Nome da Marca, Total de Produtos e Peso Total. */

ALTER VIEW vw_6a AS
SELECT
	BrandName AS 'Nome da Marca',
	COUNT(*) AS 'Total de Produtos',
	SUM(Weight) AS 'Peso Total'
FROM
	DimProduct
GROUP BY BrandName
GO
--c) Exclua a View vw_6a.

DROP VIEW vw_6a

MD-13-Exercicio-1_SQLhashtag.sql

/*M�DULO 13: EXERC�CIOS 
1. a) Crie um banco de dados chamado BD_Teste.*/

CREATE DATABASE BD_Teste

/*b) Exclua o banco de dados criado no item anterior.*/ 

DROP DATABASE BD_Teste

/*c) Crie um banco de dados chamado Exercicios.*/

CREATE DATABASE Exercicios

MD-13-Exercicio-2_SQLhashtag.sql

/*M�DULO 13: EXERC�CIOS 

2. No banco de dados criado no exerc�cio anterior, crie 3 tabelas, cada uma contendo as seguintes 
colunas: 

Tabela 1: dCliente - ID_Cliente - Nome_Cliente - Data_de _Nascimento 
Tabela 2: dGerente - ID_Gerente - Nome_Gerente - Data_de_Contratacao - Salario 
Tabela 3: fContratos - ID_Contrato - Data_de_Assinatura - ID_Cliente - ID_Gerente - Valor_do_Contrato 

Lembre-se dos seguintes pontos: 

a) Garantir que o Banco de Dados Exercicios est� selecionado. 

b) Definir qual ser� o tipo de dados mais adequado para cada coluna das tabelas. Lembrando que 
os tipos de dados mais comuns s�o: INT, FLOAT, VARCHAR e DATETIME. 
Por fim, fa�a um SELECT para visualizar cada tabela. */

USE Exercicios
CREATE TABLE dCliente(
	ID_Cliente INT,
	Nome_Cliente VARCHAR(100),
	Data_de_Nascimento DATETIME
)
CREATE TABLE dGerente(
	ID_Gerente INT,
	Nome_Gerente VARCHAR(100),
	Data_de_Contratacao DATETIME,
	Salario FLOAT
)
CREATE TABLE fContratos(
	ID_Contrato INT,
	Data_de_Assinatura DATETIME,
	ID_Cliente INT,
	ID_Gerente INT,
	Valor_do_Contrato FLOAT
)

SELECT * FROM dCliente
SELECT * FROM dGerente
SELECT * FROM fContratos

MD-13-Exercicio-3_SQLhashtag.sql

/*M�DULO 13: EXERC�CIOS 

3. Em cada uma das 3 tabelas, adicione os seguintes valores: */

USE Exercicios
INSERT INTO dCliente(ID_Cliente, Nome_Cliente, Data_de_Nascimento)
VALUES
	(1, 'Andr� Martins', '02/12/1989'),
	(2, 'B�rbara Campos', '05/07/1992'),
	(3, 'Carol Freitas', '04/23/1985'),
	(4, 'Diego Cardoso', '10/11/1994'),
	(5, 'Eduardo Pereira', '11/09/1988'),
	(6, 'Fabiana Silva', '09/02/1989'),
	(7, 'Gustavo Barbosa', '06/27/1993'),
	(8, 'Helen Viana', '02/11/1990')

INSERT INTO dGerente(ID_Gerente, Nome_Gerente, Data_de_Contratacao, Salario)
VALUES
	(1, 'Lucas Sampaio', '03/21/2015', 6700),
	(2, 'Mariana Padilha', '01/10/2011', 9900),
	(3, 'Nath�lia Santos', '10/03/2018', 7200),
	(4, 'Ot�vio Costa', '04/18/2017', 11000)

INSERT INTO fContratos(ID_Contrato, Data_de_Assinatura, ID_Cliente, ID_Gerente, Valor_do_Contrato)
VALUES
	(1, '01/12/2019', 8, 1, 23000),
	(2, '02/10/2019', 3, 2, 15500),
	(3, '03/07/2019', 7, 2, 6500),
	(4, '03/15/2019', 1, 3, 33000),
	(5, '03/21/2019', 5, 4, 11100),
	(6, '03/23/2019', 4, 2, 5500),
	(7, '03/28/2019', 9, 3, 55000),
	(8, '04/04/2019', 2, 1, 31000),
	(9, '04/05/2019', 10, 4, 3400),
	(10, '04/05/2019', 6, 2, 9200)

SELECT * FROM dCliente
SELECT * FROM dGerente
SELECT * FROM fContratos

MD-13-Exercicio-4_SQLhashtag.sql

/*M�DULO 13: EXERC�CIOS 

4. Novos dados dever�o ser adicionados nas tabelas dCliente, dGerente e fContratos. Fique livre 
para adicionar uma nova linha em cada tabela contendo, respectivamente,  
(1) um novo cliente (id cliente, nome e data de nascimento) 
(2) um novo gerente (id gerente, nome, data de contrata��o e sal�rio) 
(3) um novo contrato (id, data assinatura, id cliente, id gerente, valor do contrato) */

USE Exercicios
INSERT INTO dCliente(ID_Cliente, Nome_Cliente, Data_de_Nascimento)
VALUES
	(9, 'Pedro Brantes', '01/13/2004')

INSERT INTO dGerente(ID_Gerente, Nome_Gerente, Data_de_Contratacao, Salario)
VALUES
	(5, 'Carlos Alexandre', '02/15/2019', 27000)

INSERT INTO fContratos(ID_Contrato, Data_de_Assinatura, ID_Cliente, ID_Gerente, Valor_do_Contrato)
VALUES
	(11, '05/11/2019', 9, 5, 13000)

SELECT * FROM dCliente
SELECT * FROM dGerente
SELECT * FROM fContratos

MD-13-Exercicio-5_SQLhashtag.sql

/*M�DULO 13: EXERC�CIOS

5. O contrato de ID igual a 4 foi registrado com alguns erros na tabela fContratos. Fa�a uma 
altera��o na tabela atualizando os seguintes valores: 

Data_de_Assinatura: 17/03/2019 

ID_Gerente: 2 

Valor_do_Contrato: 33500*/

USE Exercicios

UPDATE fContratos
SET Data_de_Assinatura = '03/17/2019', ID_Gerente = 2, Valor_do_Contrato = 33500
WHERE ID_Contrato = 4

SELECT * FROM fContratos

MD-13-Exercicio-6_SQLhashtag.sql

/*M�DULO 13: EXERC�CIOS 

6. Delete a linha da tabela fContratos que voc� criou na quest�o 4. */

DELETE
FROM fContratos
WHERE ID_Contrato = 11

SELECT * FROM fContratos

MD-4-Exercicio-10_SQLhashtag.sql

/*10. A empresa possui 16 produtos da marca Contoso da cor Silver e com um Peso entre 10 e 30. Descubra quais s�o esses produtos e ordene o resultado em ordem decrescente de acordo com o pre�o (UnitPrice).*/

SELECT
	Productname AS 'Nome dos Produtos' 
FROM 
	DimProduct
WHERE 
	Weight BETWEEN 10 AND 30
	AND
	BrandName = 'Contoso'
	AND
	ColorName = 'Silver'
ORDER BY UnitPrice DESC

MD-4-Exercicio-1_SQLhashtag.sql

/*1. Voc� � o gerente do �rea de compras e precisa criar um relat�rio com as TOP 100 vendas, de
acordo com a quantidade vendida. Voc� precisa fazer isso em 10mim pois o diretor de compras
solicitou essa informa��o para apresentar em uma reuni�o.

Utilize seu conhecimento em SQL para buscar essas TOP 100 vendas, de acordo com o total vendido*/

SELECT TOP(100) * FROM FactSales
ORDER BY SalesAmount DESC 

MD-4-Exercicio-2_SQLhashtag.sql

/*2. Os TOP 10 produtos com maior UnitPrice possuem exatamente o mesmo pre�o. Por�m, a
empresa quer diferenciar esses pre�os de acordo com o peso (Weight) de cada um.
O que voc� precisar� fazer � ordenar esses top 10 produtos, de acordo com a coluna (UnitPrice) e, al�m disso, estabelecer um crit�rio de desempate, para que ele seja mostrado na
ordem, do maior para o menor.

Caso ainda assim haja um empate entre 2 ou mais produtos, pens em  uma forma de criar
um segundo crit�rio de desempate (al�m do peso).*/

SELECT TOP(10) *
FROM DimProduct
ORDER BY 
	UnitPrice DESC,
	Weight DESC,
	Size DESC

MD-4-Exercicio-3_SQLhashtag.sql

SELECT
	ProductName AS 'Produtos de Categoria A',
	Weight AS 'Peso'
FROM 
	DimProduct
WHERE
	Weight >= 100
ORDER BY 
	Weight DESC

MD-4-Exercicio-4_SQLhashtag.sql

/*4. Voc� foi alocado para criar um relat�rio das lojas registradas atualmente na Contoso.
a)Descubra quantas lojas a empresa tem no total. Na consulta que voc� dever� fazer � tabela
DimStore retorne as seguintes informa��es: StoreName, openDate, EmployeeCount

b)Renomeie as colunas anteriores para deixar a sua consulta mais intuitiva.

c)Dessas lojas, descubra quantas (e quais) lojas ainda est�o ativas.*/

SELECT DISTINCT StoreName AS 'Nome da Loja',
	OpenDate AS 'Data de Abertura',
	EmployeeCount AS 'Funcion�rios'
FROM DimStore

-- SELECT DISTINCT StoreName FROM DimStore
-- WHERE Status = 'On'
-- 294 Lojas ainda est�o Ativas

MD-4-Exercicio-5_SQLhashtag.sql

/*5. O gerente da �rea de controle de qualidade notificou � Contoso que todos os produtos Home
Theater da marca Litware disponibilizados para venda no dia 15 de mar�o de 2009, foram
identificados com defeitos de f�brica.

O que voc� dever� fazer � identificar os ID's desses produtos e repassar ao gerente para que ele
possa notificar as lojas e consequentemente solicitar a suspens�o das vendas desses produtos.*/

SELECT 
	* 
FROM
	DimProduct
WHERE
	BrandName = 'Litware'
	AND 
		ProductName LIKE '%Home Theater%' 
	AND 
		AvailableForSaleDate = '20090315'

MD-4-Exercicio-6_SQLhashtag.sql

/*6. imagine que voc� precise extrair um relat�rio da tabela DimStore, com informa��es de lojas.
Mas voc� precisa apenas das lojas que n�o est�o mais funcionando atualmente.

a) Utilize a coluna de Status para filtrar a tabela e trazer apenas as lojas que n�o est�o mais funcionando.

b) Agora imagine que essa coluna de Status n�o existe na sua tabela. Qual seria a outra forma que voc� teria de descobrir quais s�o as lojas que n�o est�o mais funcionando?*/

SELECT * FROM DimStore -- a)
WHERE Status = 'Off'

SELECT * FROM DimStore -- b)
WHERE CloseDate IS NOT NULL

MD-4-Exercicio-8_SQLhashtag.sql

/*8. A empresa decidiu que todas as televis�es de LCD receber�o um super desconto no pr�ximo m�s. O seu trabalho � fazer uma consulta � tabela DimProduct e retornar os ID's, Nomes e Pre�os de todos os produtos LCD existentes.*/

SELECT 
	ProductKey AS 'ID',
	ProductName AS 'Nome do Produto',
	UnitPrice AS 'Pre�o'
FROM 
	DimProduct
WHERE 
	ProductDescription LIKE '%LCD%'

MD-4-Exercicio-9_SQLhashtag.sql

/*9. Fa�a uma lista com todos os produtos das cores: Green, Orange, Black, Silver e Pink. Estes
produtos devem ser exclusivamente das marcas: Contoso Litware e Fabrikam.*/

SELECT *
FROM 
	DimProduct
WHERE 
	ColorName IN ('Green', 'Orange', 'Black', 'Silver', 'Pink')
	AND
	BrandName IN ('Contoso', 'Litware', 'Fabrikam')

MD-5-Exercicio-1_SQLhashtag.sql

/*1. O gerente comercial pediu a voc� uma an�lise da Quantidade Vendida e Quantidade Devolvida para o canal de venda mais importante da empresa: Store

Utilize uma fun��o SQL para fazer essas consultas no seu banco de dados.
Obs: Fa�a essa an�lise considerando a tabela FactSales.*/

SELECT
	SUM(SalesQuantity) AS 'Qtd. Vendida',
	SUM(ReturnQuantity) AS 'Qtd. Devolvida'
FROM FactSales
WHERE channelKey = 1 

--SELECT TOP(1000) * FROM FactSales

--SELECT TOP(1000) * FROM DimChannel

MD-5-Exercicio-2_SQLhashtag.sql

/*2. Uma nova a��o no setor de Marketing precisar� avaliar a m�dia salarial de todos os clientes da empresa, mas apenas de ocupa��o Professional. Utilize um comando SQL para atingir esse resultado.*/

SELECT
	AVG(YearlyIncome) AS 'M�dia Salarial'
FROM 
	DimCustomer
WHERE Occupation = 'Professional'

MD-5-Exercicio-3_SQLhashtag.sql

--3. Voc� precisar� fazer uma an�lise da quantidade de funcion�rios das lojas registradas na empresa. O seu gerente te pediu os seguintes n�meros e informa��es:

--a)Quantos funcion�rios tem a loja com mais funcion�rios?

SELECT 
	MAX(EmployeeCount) AS 'Num. F�ncionarios'
FROM 
	Dimstore

--b)Qual � o nome dessa loja?

SELECT 
	TOP(1) StoreName AS 'Nome da Loja',
	EmployeeCount
FROM 
	Dimstore
ORDER BY EmployeeCount DESC

--c)Quantos funcion�rios tem a loja com menos funcion�rios?

SELECT 
	MIN(EmployeeCount) AS 'Num. F�ncionarios'
FROM 
	Dimstore

--d)Qual � o nome dessa loja?

SELECT 
	TOP(1) StoreName AS 'Nome da Loja',
	EmployeeCount
FROM 
	Dimstore
WHERE EmployeeCount IS NOT NULL
ORDER BY EmployeeCount ASC

MD-5-Exercicio-4_SQLhashtag.sql

/*4. A �rea de RH est� com uma nova a��o para a empresa, e para isso precisa saber a quantidade total de funcion�rios do sexo Masculino e do sexo Feminino.

a) Descubra essas duas informa��es utilizando o SQL.

b) O funcion�rio e a funcion�ria mais antigos receber�o uma homenagem. Descubra as seguintes informa��es de cada um deles: Nome, E-mail, Data de Contrata��o.*/

SELECT * FROM DimEmployee

SELECT
	COUNT(EmployeeKey) AS 'Qtd. Func. Feminino'
FROM DimEmployee
WHERE Gender = 'F' -- 87

SELECT
	COUNT(EmployeeKey) AS 'Qtd. Func. Masculino'
FROM DimEmployee
WHERE Gender = 'M' -- 206

SELECT
	TOP(1) FirstName,
	LastName,
	EmailAddress,
	HireDate,
	Gender
FROM DimEmployee
WHERE Gender = 'F'
ORDER BY HireDate ASC
--WHERE HireDate = (SELECT MIN(HireDate) FROM DimEmployee WHERE Gender = 'F')

SELECT
	TOP(1) FirstName,
	LastName,
	EmailAddress,
	HireDate,
	Gender
FROM DimEmployee
WHERE Gender = 'M'
ORDER BY HireDate ASC
--WHERE HireDate = (SELECT MIN(HireDate) FROM DimEmployee WHERE Gender = 'M')

MD-5-Exercicio-5_SQLhashtag.sql

/*5. Agora voc� precisa fazer uma an�lise dos produtos. Ser� necess�rio descobrir as seguintes informa��es:

a) Quantidade distinta de cores de produtos.
b) Quantidade distinta de marcas
c) Quantidade distinta de classes de produto
Para simplificar, voc� pode fazer isso em uma mesma consulta.*/

--SELECT * FROM dimproduct

SELECT
	COUNT(DISTINCT ColorName) AS 'Qtd. Cores',
	COUNT(DISTINCT BrandName) AS 'Qtd. Marcas',
	COUNT(DISTINCT ClassName) AS 'Qtd. Classes'
FROM DimProduct

MD-6-Exercicio-10_SQLhashtag.sql

--DIMEMPLOYEE 

--10. Fa�a uma tabela resumo mostrando o total de VacationHours para cada cargo (Title). Voc� deve considerar apenas as mulheres, dos departamentos de Production, Marketing, Engineering e Finance, para os funcion�rios contratados nos anos de 1999 e 2000.

--SELECT * FROM DimEmployee

SELECT
	Title AS 'Cargo',
	SUM(VacationHours) AS 'Horas de F�rias'
FROM
	DimEmployee
WHERE Gender = 'F'
	AND DepartmentName IN ('Production', 'Marketing', 'Engineering', 'Finance')
	AND HireDate BETWEEN '19990101' AND '20001231'
GROUP BY Title

MD-6-Exercicio-1_SQLhashtag.sql

--1. FACTSALES 
--SELECT TOP(1000) * FROM FactSales

--a) Fa�a um resumo da quantidade vendida de acordo com o canal de vendas (channelkey)

SELECT
	channelkey AS 'Canal de Vendas',
	SUM(SalesQuantity) AS 'Qtd. Vendida'
FROM 
	FactSales
GROUP BY channelKey

--b) Fa�a um agrupamento mostrando a quantidade total vendida e a quantidade total devolvida de acordo com o ID das lojas (StoreKey).

SELECT
	StoreKey AS 'ID da Loja',
	SUM(SalesQuantity) AS 'Qtd. Vendida',
	SUM(ReturnQuantity) AS 'Qtd. Devolvida'
FROM 
	FactSales
GROUP BY StoreKey

--c) Fa�a um resumo do valor total vendido para cada canal de venda, mas apenas para o ano de 2007.

SELECT
	channelKey AS 'Canal de Vendas',
	SUM(SalesAmount) AS 'Faturamento Total'
FROM 
	FactSales
WHERE DateKey < '20080101' AND DateKey >= '20070101'
--WHERE DateKey BETWEEN '20070101' AND '20071231'
GROUP BY channelKey

MD-6-Exercicio-2_SQLhashtag.sql

--2. FACTSALES
--Voc� precisa fazer uma an�lise de vendas por produtos. O objetivo final � descobrir o valor total vendido (SalesAmount) por produto.

--SELECT TOP(1000) * FROM FactSales

--a) A tabela final dever� estar ordenada de acordo com a quantidade vendida e al�m disso, mostrar apenas os produtos que tiveram um resultado final de vendas maior do que $5.000.000.

SELECT
	ProductKey AS 'ID do Produto',
	SUM(SalesQuantity) AS 'Qtd. Vendida',
	SUM(SalesAmount) AS 'Total Faturado'
FROM
	FactSales
GROUP BY
	ProductKey
HAVING 
	SUM(SalesAmount) >= 5000000
ORDER BY 
	SUM(SalesQuantity) DESC

--b) Fa�a uma adapta��o no exerc�cio anterior e mostre os Top 10 produtos com mais vendas. Desconsidere o filtro de $5.000.000 aplicado.

SELECT TOP(10)
	ProductKey AS 'ID do Produto',
	SUM(SalesQuantity) AS 'Qtd. Vendida',
	SUM(SalesAmount) AS 'Total Faturado'
FROM 
	FactSales
GROUP BY 
	ProductKey
ORDER BY 
	SUM(SalesQuantity) DESC

MD-6-Exercicio-3_SQLhashtag.sql

--3. FACTONLINESALES

--SELECT TOP(1000) * FROM FactOnlineSales

--a) Voc� deve fazer uma consulta � tabela FactOnlineSales e descobrir qual � o ID do cliente que mais realizou compras online (de acordo com a coluna SalesQuantity)

SELECT TOP(1)
	CustomerKey AS 'ID do Cliente',
	SUM(SalesQuantity) AS 'Qtd. Comprada'
FROM 
	FactOnlineSales
GROUP BY CustomerKey
ORDER BY SUM(SalesQuantity) DESC

--b) Feito isso, fa�a um agrupamento de total vendido (SalesQuantity) por ID do produto e descubra quais foram os top 3 produtos mais comprados pelo cliente do exercicio anterior (a).

SELECT TOP(3)
	ProductKey AS 'ID do Produto',
	SUM(SalesQuantity) AS 'Qtd. Comprada'
FROM
	FactOnlineSales
WHERE CustomerKey = 19037
GROUP BY ProductKey
ORDER BY SUM(SalesQuantity) DESC

MD-6-Exercicio-4_SQLhashtag.sql

--4. DIMPRODUCT 

--a) Fa�a um agrupamento e descubra a quantidade total de produtos por marca.

SELECT
	BrandName AS 'Marca',
	COUNT(ProductKey) AS 'Qtd. Produtos'
FROM 
	DimProduct
GROUP BY BrandName

--b) Determine a m�dia do pre�o unit�rio (UnitPrice) para cada ClassName.

SELECT
	ClassName AS 'Classe',
	AVG(UnitPrice) AS 'Pre�o M�dio'
FROM 
	DimProduct
GROUP BY ClassName

--c) Fa�a um agrupamento de cores e descubra o peso total que cada cor de produto possui.

SELECT
	ColorName AS 'Cor',
	SUM(Weight) AS 'Peso'
FROM 
	DimProduct
GROUP BY ColorName

MD-6-Exercicio-5_SQLhashtag.sql

--5. DIMPRODUCT 

--Voc� dever� descobrir o peso total para cada tipo de produto (StockTypeName). A tabela final deve considerar apenas a marca 'Contoso' e ter os seus valores classificados em ordem decrescente.

--SELECT * FROM DimProduct

SELECT
	StockTypeName AS 'Tipo de Estoque',
	SUM(Weight) AS 'Peso Total'
FROM 
	DimProduct
WHERE 
	BrandName = 'Contoso'
GROUP BY StockTypeName
ORDER BY SUM(Weight) DESC

MD-6-Exercicio-6_SQLhashtag.sql

--6. DIMPRODUCT

--Voc� seria capaz de confirmar se todas as marcas dos produtos possuem � disposi��o todas as 16 op��es de cores?

--SELECT * FROM DimProduct

SELECT
	BrandName AS 'Nome da Marca',
	COUNT(DISTINCT ColorName) AS 'Qtd. Cores D�sponiveis'
FROM
	DimProduct
GROUP BY BrandName
ORDER BY 'Qtd. Cores D�sponiveis' DESC

MD-6-Exercicio-7_SQLhashtag.sql

--DIMCUSTOMER 

--7. Fa�a um agrupamento para saber o total de clientes de acordo com o Sexo e tamb�m a m�dia salarial de acordo com o Sexo. Corrija qualquer resultado �inesperado� com os seus conhecimentos em SQL. 

--SELECT * FROM DimCustomer

SELECT
	Gender AS 'Sexo',
	COUNT(CustomerKey) AS 'Total de Clientes',
	AVG(YearlyIncome) AS 'M�dia Salarial'
FROM
	DimCustomer
WHERE Gender IS NOT NULL
GROUP BY Gender

MD-6-Exercicio-8_SQLhashtag.sql

--DIMCUSTOMER 

--8. Fa�a um agrupamento para descobrir a quantidade total de clientes e a m�dia salarial de acordo com o seu n�vel escolar. Utilize a coluna Education da tabela DimCustomer para fazer esse agrupamento. 

--SELECT * FROM DimCustomer

SELECT
	Education AS 'N�vel Escolar',
	COUNT(CustomerKey) AS 'Qtd. Clientes',
	AVG(YearlyIncome) AS 'M�dia Salarial'
FROM 
	DimCustomer
WHERE Education IS NOT NULL
GROUP BY Education

MD-6-Exercicio-9_SQLhashtag.sql

--DIMEMPLOYEE

--9. Fa�a uma tabela resumo mostrando a quantidade total de funcion�rios de acordo com o Departamento (DepartmentName). Importante: Voc� dever� considerar apenas os funcion�rios ativos. 

--SELECT * FROM DimEmployee

SELECT
	DepartmentName AS 'Departamento',
	COUNT(EmployeeKey) AS 'Qtd. Funcion�rios'
FROM
	DimEmployee
WHERE Status = 'Current'
--WHERE EndDate IS NULL
GROUP BY DepartmentName
ORDER BY COUNT(EmployeeKey) DESC

MD-7-Exercicio-10_SQLhashtag.sql

--M�DULO 7: EXERC�CIOS

--10. A tabela abaixo � resultado de um Join entre a tabela FactSales e as tabelas: DimChannel, DimStore e DimProduct. Recrie esta consulta e classifique em ordem crescente de acordo com SalesAmount.

SELECT TOP(1000)
	FactSales.SalesKey,
	DimChannel.ChannelName,
	DimStore.StoreName,
	DimProduct.ProductName,
	FactSales.SalesAmount
FROM
	FactSales
LEFT JOIN DimChannel
	ON FactSales.channelKey = DimChannel.ChannelKey
LEFT JOIN DimStore
	ON FactSales.StoreKey = DimStore.StoreKey
LEFT JOIN DimProduct
	ON FactSales.ProductKey = DimProduct.ProductKey
ORDER BY SalesAmount DESC

MD-7-Exercicio-1_SQLhashtag.sql

--M�DULO 7: EXERC�CIOS 

--1. Utilize o INNER JOIN para trazer os nomes das subcategorias dos produtos, da tabela DimProductSubcategory para a tabela DimProduct. 

SELECT
	*
FROM 
	DimProduct
INNER JOIN DimProductSubcategory
	ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey
	

MD-7-Exercicio-2_SQLhashtag.sql

--M�DULO 7: EXERC�CIOS 

--2. Identifique uma coluna em comum entre as tabelas DimProductSubcategory e DimProductCategory. Utilize essa coluna para complementar informa��es na tabela DimProductSubcategory a partir da DimProductCategory. Utilize o LEFT JOIN.

SELECT
	*
FROM 
	DimProductSubcategory
INNER JOIN DimProductCategory
	ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey

MD-7-Exercicio-3_SQLhashtag.sql

--M�DULO 7: EXERC�CIOS 

--3. Para cada loja da tabela DimStore, descubra qual o Continente e o Nome do Pa�s associados (de acordo com DimGeography). Seu SELECT final deve conter apenas as seguintes colunas: StoreKey, StoreName, EmployeeCount, ContinentName e RegionCountryName. Utilize o LEFT JOIN neste exerc�cio.

SELECT
	DimStore.StoreKey,
	DimStore.StoreName AS 'Loja',
	DimGeography.ContinentName AS 'Continente',
	DimGeography.RegionCountryName AS 'Pa�s',
	DimStore.EmployeeCount AS 'Qtd. Funcion�rios'
FROM DimStore
LEFT JOIN DimGeography
	ON DimStore.GeographyKey = DimGeography.GeographyKey

MD-7-Exercicio-4_SQLhashtag.sql

--M�DULO 7: EXERC�CIOS 

--4. Complementa a tabela DimProduct com a informa��o de ProductCategoryDescription. Utilize o LEFT JOIN e retorne em seu SELECT apenas as 5 colunas que considerar mais relevantes. 

/*SELECT
	*
FROM DimProduct

SELECT
	*
FROM DimProductCategory

SELECT
	*
FROM DimProductSubcategory*/

SELECT
	DimProduct.ProductName AS 'Produto',
	DimProduct.ProductDescription AS 'Descri��o',
	DimProductCategory.ProductCategoryName AS 'Categoria',
	DimProductSubcategory.ProductSubcategoryName AS 'Subcategoria',
	DimProduct.BrandName AS 'Marca'
FROM DimProduct
LEFT JOIN DimProductSubcategory
	ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey
	LEFT JOIN DimProductCategory
		ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey

MD-7-Exercicio-5_SQLhashtag.sql

--M�DULO 7: EXERC�CIOS 

--5. A tabela FactStrategyPlan resume o planejamento estrat�gico da empresa. Cada linha representa um montante destinado a uma determinada AccountKey. 

--a) Fa�a um SELECT das 100 primeiras linhas de FactStrategyPlan para reconhecer a tabela.

--b) Fa�a um INNER JOIN para criar uma tabela contendo o AccountName para cada AccountKey da tabela FactStrategyPlan. O seu SELECT final deve conter as colunas: StrategyPlanKey, DateKey, AccountName, Amount 

SELECT TOP(100)
	FactStrategyPlan.StrategyPlanKey,
	FactStrategyPlan.Datekey,
	DimAccount.AccountName,
	FactStrategyPlan.Amount
FROM
	FactStrategyPlan
INNER JOIN DimAccount
	ON FactStrategyPlan.AccountKey = DimAccount.AccountKey

MD-7-Exercicio-6_SQLhashtag.sql

--M�DULO 7: EXERC�CIOS 

--6. Vamos continuar analisando a tabela FactStrategyPlan. Al�m da coluna AccountKey que identifica o tipo de conta, h� tamb�m uma outra coluna chamada ScenarioKey. Essa coluna possui a numera��o que identifica o tipo de cen�rio: Real, Or�ado e Previs�o. Fa�a um INNER JOIN para criar uma tabela contendo o ScenarioName para cada ScenarioKey da tabela FactStrategyPlan. O seu SELECT final deve conter as colunas: StrategyPlanKey, DateKey, ScenarioName, Amount

SELECT
	FactStrategyPlan.StrategyPlanKey,
	FactStrategyPlan.Datekey,
	DimScenario.ScenarioName,
	FactStrategyPlan.Amount
FROM 
	FactStrategyPlan
INNER JOIN DimScenario
	ON FactStrategyPlan.ScenarioKey = DimScenario.ScenarioKey

MD-7-Exercicio-7_SQLhashtag.sql

--M�DULO 7: EXERC�CIOS 

--7. Algumas subcategorias n�o possuem nenhum exemplar de produto. Identifique que subcategorias s�o essas.

SELECT
	DimProductSubcategory.ProductSubcategoryKey AS 'ID',
	DimProductSubcategory.ProductSubcategoryName AS 'Subcategoria'
FROM
	DimProduct
RIGHT JOIN DimProductSubcategory
	ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey
WHERE DimProduct.ProductName IS NULL

MD-7-Exercicio-8_SQLhashtag.sql

--M�DULO 7: EXERC�CIOS

--8. A tabela abaixo mostra a combina��o entre Marca e Canal de Venda, para as marcas Contoso, Fabrikam e Litware. Crie um c�digo SQL para chegar no mesmo resultado.

SELECT DISTINCT 
	DimProduct.BrandName,
	DimChannel.ChannelName
FROM
	DimProduct CROSS JOIN DimChannel
WHERE DimProduct.BrandName IN ('Contoso', 'Fabrikam', 'Litware')

MD-7-Exercicio-9_SQLhashtag.sql

--M�DULO 7: EXERC�CIOS

--9. Neste exerc�cio, voc� dever� relacionar as tabelas FactOnlineSales com DimPromotion. Identifique a coluna que as duas tabelas t�m em comum e utilize-a para criar esse relacionamento. Retorne uma tabela contendo as seguintes colunas: OnlineSalesKey, DateKey, PromotionName, SalesAmount

--A sua consulta deve considerar apenas as linhas de vendas referentes a produtos com desconto (PromotionName <> 'No Discount'). Al�m disso, voc� dever� ordenar essa tabela de acordo com a coluna DateKey, em ordem crescente.

SELECT TOP(10000) --FactOnlineSales � muito grande ent�o limitei a 10k
	FactOnlineSales.OnlineSalesKey,
	FactOnlineSales.DateKey,
	DimPromotion.PromotionName,
	FactOnlineSales.SalesAmount
FROM
	FactOnlineSales
LEFT JOIN DimPromotion
	ON FactOnlineSales.PromotionKey = DimPromotion.PromotionKey
WHERE DimPromotion.PromotionName <> 'No Discount'
ORDER BY FactOnlineSales.DateKey DESC

MD-8-Exercicio-10_SQLhashtag.sql

--M�DULO 8: DIMPRODUCT/DIMPRODUCTSUBCATEGORY

--10. Fa�a um agrupamento duplo de quantidade de produtos por BrandName e A tabela final dever� ser ordenada de acordo com a coluna BrandName.

SELECT
	DimProduct.BrandName AS 'Marca',
	DimProductSubcategory.ProductSubcategoryName AS 'Subcategoria',
	COUNT(DimProduct.ProductName) AS 'Qtd. Produto'
FROM
	DimProduct
INNER JOIN DimProductSubcategory
	ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey
GROUP BY DimProduct.BrandName,
	DimProductSubcategory.ProductSubcategoryName
ORDER BY DimProduct.BrandName ASC

MD-8-Exercicio-1_SQLhashtag.sql

--M�DULO 8: FACTSALES

--1.

--a) Fa�a um resumo da quantidade vendida de acordo com o nome do canal de vendas (ChannelName). Voc� deve ordenar a tabela final de acordo com SalesQuantity. em ordem decrescente.

SELECT
	DimChannel.ChannelName AS 'Canal',
	SUM(FactSales.SalesQuantity) AS 'Qtd. Vendida'
FROM
	FactSales
INNER JOIN DimChannel
	ON FactSales.channelKey = DimChannel.ChannelKey
GROUP BY DimChannel.ChannelName
ORDER BY SUM(FactSales.SalesQuantity) DESC

--b) Fa�a um agrupamento mostrando a quantidade total vendida e quantidade total devolvida (Return de acordo com o nome das lojas (StoreName).

SELECT TOP(1000)
	DimStore.StoreName AS 'Nome da Loja',
	SUM(FactSales.SalesQuantity) AS 'Qtd. Vendida',
	SUM(FactSales.ReturnQuantity) AS 'Qtd. Devolvida'
FROM
	FactSales
INNER JOIN  DimStore
	ON FactSales.StoreKey = DimStore.StoreKey
GROUP BY DimStore.StoreName

--c) Fa�a um resumo do valor total vendido para cada m�s (CalendarMonthLabeI) e ano

SELECT TOP(1000)
	DimDate.CalendarYear AS 'Ano',
	DimDate.CalendarMonthLabel AS 'M�s',
	SUM(FactSales.SalesAmount) AS 'Total Vendido'
FROM
	FactSales 
INNER JOIN DimDate
	ON FactSales.DateKey = DimDate.Datekey
GROUP BY DimDate.CalendarYear,
	DimDate.CalendarMonthLabel,
	DimDate.CalendarMonth
ORDER BY DimDate.CalendarMonth ASC

MD-8-Exercicio-2_SQLhashtag.sql

--M�DULO 8: FACTSALES

--2. Voc� precisa fazer uma an�lise de vendas por produtos. O objetivo final � descobrir o valor total vendido por produto.

--a) Descubra qual � a cor de produto que mais � vendida (de acordo com SalesQuantity)

SELECT TOP(1)
	DimProduct.ColorName AS 'Cor',
	SUM(FactSales.SalesQuantity) AS 'Qtd. Vendida'
FROM
	FactSales
INNER JOIN DimProduct
	ON FactSales.ProductKey = DimProduct.ProductKey
GROUP BY DimProduct.ColorName
ORDER BY SUM(FactSales.SalesQuantity) DESC

--b) Quantas cores tiveram uma quantidade vendida acima de 3.000.000.

SELECT
	DimProduct.ColorName AS 'Cor',
	SUM(FactSales.SalesQuantity) AS 'Qtd. Vendida'
FROM
	FactSales
INNER JOIN DimProduct
	ON FactSales.ProductKey = DimProduct.ProductKey
GROUP BY DimProduct.ColorName
HAVING SUM(FactSales.SalesQuantity) > 3000000

MD-8-Exercicio-3_SQLhashtag.sql

--M�DULO 8: FACTSALES

--3. Observe a imagem abaixo: se trata de um agrupamento de quantidade vendida agrupada por ProductCategoryName. Crie o mesmo agrupamento do zero. Obs: voc� precisar� fazer mais do que 1 INNER JOIN, dado que a rela��o entre FactSales e DimProductCategory n�o � direta.


SELECT
	DimProductCategory.ProductCategoryName,
	SUM(FactSales.SalesQuantity) AS 'Qtd. Vendida'
FROM
	FactSales
INNER JOIN DimProduct
	ON FactSales.ProductKey = DimProduct.ProductKey
		INNER JOIN DimProductSubcategory
			ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey
				INNER JOIN DimProductCategory
					ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey
GROUP BY DimProductCategory.ProductCategoryName

MD-8-Exercicio-4_SQLhashtag.sql

--M�DULO 8: FACTONLINESALES

--4. a) Voc� deve fazer uma consulta � tabela FactOnlineSales e descobrir qual � o nome completo do cliente que mais realizou compras online (de acordo com a coluna SalesQuantity)

SELECT TOP(1)
	DimCustomer.CustomerKey,
	DimCustomer.FirstName,
	DimCustomer.LastName,
	SUM(FactOnlineSales.SalesQuantity) AS 'Qtd. Comprada'	
FROM
	FactOnlineSales
INNER JOIN DimCustomer
	ON FactOnlineSales.CustomerKey = DimCustomer.CustomerKey
WHERE DimCustomer.CustomerType = 'Person'
GROUP BY FirstName, LastName, dimcustomer.CustomerKey
ORDER BY SUM(FactOnlineSales.SalesQuantity) DESC

--b) Feito ISSO, fa�a um agrupamento de produtos e descubra quais foram os top 10 produtos mais comprados pelo cliente da letra a), considerando o nome do produto.

SELECT TOP(10)
	DimProduct.ProductName,
	SUM(FactOnlineSales.SalesQuantity) AS 'Qtd. Comprada'
FROM
	FactOnlineSales
INNER JOIN DimProduct
	ON FactOnlineSales.ProductKey = DimProduct.ProductKey
WHERE FactOnlineSales.CustomerKey = 7665
GROUP BY DimProduct.ProductName
ORDER BY SUM(FactOnlineSales.SalesQuantity) DESC

MD-8-Exercicio-5_SQLhashtag.sql

--M�DULO 8: FACTONLINESALES

--5. Fa�a um resumo mostrando o total de produtos comprados de acordo com o sexo dos clientes.

SELECT
	DimCustomer.Gender AS 'Sexo',
	SUM(FactOnlineSales.SalesQuantity) AS 'Qtd. Comprada'
FROM
	FactOnlineSales
INNER JOIN DimCustomer
	ON FactOnlineSales.CustomerKey = DimCustomer.CustomerKey
WHERE DimCustomer.Gender IS NOT NULL
GROUP BY DimCustomer.Gender

MD-8-Exercicio-6_SQLhashtag.sql

 --M�DULO 8: FACTEXCHANGERATE

--6. Fa�a uma tabela resumo mostrando a taxa de cambio m�dia de acordo com cada CurrencyDescription. A tabela final deve conter apenas taxas entre 10 e 100.

SELECT
	DimCurrency.CurrencyDescription AS 'Moeda',
	AVG(FactExchangeRate.AverageRate) AS 'Taxa M�dia'
FROM
	FactExchangeRate
INNER JOIN DimCurrency
	ON FactExchangeRate.CurrencyKey = DimCurrency.CurrencyKey
GROUP BY DimCurrency.CurrencyDescription
HAVING AVG(FactExchangeRate.AverageRate) BETWEEN 10 AND 100

MD-8-Exercicio-7_SQLhashtag.sql

--M�DULO 8: FACTSTRATEGYPLAN

--7. Descubra o valor total na tabela destinado para os cen�rios: Actual e Budget

SELECT
	DimScenario.ScenarioName AS 'Cen�rio', 
	SUM(FactStrategyPlan.Amount) AS 'Valor Total'
FROM
	FactStrategyPlan
INNER JOIN DimScenario
	ON FactStrategyPlan.ScenarioKey = DimScenario.ScenarioKey
WHERE DimScenario.ScenarioName IN ('Actual', 'Budget')
--WHERE DimScenario.ScenarioName <> 'Forecast'
GROUP BY DimScenario.ScenarioName

MD-8-Exercicio-8_SQLhashtag.sql

--M�DULO 8: FACTSTRATEGYPLAN

--8. Fa�a uma tabela resumo mostrando o resultado do planejamento estrat�gico agrupado por ano.

SELECT
	DimDate.CalendarYear AS 'Ano',
	SUM(FactStrategyPlan.Amount) AS 'Total'
FROM
	FactStrategyPlan
INNER JOIN DimDate
	ON FactStrategyPlan.Datekey = DimDate.Datekey
GROUP BY DimDate.CalendarYear
ORDER BY DimDate.CalendarYear ASC

MD-8-Exercicio-9_SQLhashtag.sql

--M�DULO 8: DIMPRODUCT/DIMPRODUCTSUBCATEGORY

--9. Fa�a um agrupamento da quantidade de produtos por ProductSubcategoryName. Leve em considera��o em sua an�lise apenas a marca Contoso e a cor Silver.

SELECT
	DimProductSubcategory.ProductSubcategoryName AS 'Subcategoria',
	COUNT(ProductName) AS 'Qtd. Produtos'
FROM
	DimProduct
INNER JOIN DimProductSubcategory
	ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey
WHERE DimProduct.BrandName = 'Contoso'
	AND DimProduct.ColorName = 'Silver'
GROUP BY DimProductSubcategory.ProductSubcategoryName

MD-9-Exercicio-1_SQLhashtag.sql

--M�DULO 9: EXERC�CIOS

--1. Declare 4 vari�veis inteiras. Atribua os seguintes valores a elas: 
--valor1 = 10 
--valor2 = 5 
--valor3 = 34 
--valor4 = 7

DECLARE 
	@valor1 INT = 10,
	@valor2 INT = 5,
	@valor3 INT = 34, 
	@valor4 INT = 7

--a) Crie uma nova vari�vel para armazenar o resultado da soma entre valor1 e valor2. Chame essa vari�vel de soma.

DECLARE @soma INT = @valor1 + @valor2

--b) Crie uma nova vari�vel para armazenar o resultado da subtra��o entre valor3 e valor 4. Chame essa vari�vel de subtracao.

DECLARE @subtracao INT = @valor3 - @valor4

--c) Crie uma nova vari�vel para armazenar o resultado da multiplica��o entre o valor 1 e o valor4. Chame essa vari�vel de multiplicacao.

DECLARE @multiplicacao INT = @valor1 * @valor4

--d) Crie uma nova vari�vel para armazenar o resultado da divis�o do valor3 pelo valor4. Chame essa vari�vel de divisao. Obs: O resultado dever� estar em decimal, e n�o em inteiro.

DECLARE @divisao FLOAT = CAST(@valor3 AS FLOAT) / @valor4

--e) Arredonde o resultado da letra d) para 2 casas decimais. 

SELECT 
	@soma AS 'letra a',
	@subtracao AS 'letra b',
	@multiplicacao AS 'letra c',
	@divisao AS 'letra d',
	ROUND(@divisao, 2) AS 'letra d arredondado'

MD-9-Exercicio-2_SQLhashtag.sql

--M�DULO 9: EXERC�CIOS 

--2. Para cada declara��o das vari�veis abaixo, aten��o em rela��o ao tipo de dado que dever� ser especificado. 

--a) Declare uma vari�vel chamada �produto� e atribua o valor de �Celular�. 

DECLARE @produto VARCHAR(50) = 'Celular'

--b) Declare uma vari�vel chamada �quantidade� e atribua o valor de 12.  

DECLARE @quantidade INT = 12

--c) Declare uma vari�vel chamada �preco� e atribua o valor 9.99. 

DECLARE @preco FLOAT = 9.99

--d) Declare uma vari�vel chamada �faturamento� e atribua o resultado da multiplica��o entre �quantidade� e �preco�.  

DECLARE @faturamento FLOAT = @quantidade * @preco

--e) Visualize o resultado dessas 4 vari�veis em uma �nica consulta, por meio do SELECT.

SELECT
	@produto AS 'Produto',
	@quantidade AS 'Quantidade',
	@preco AS 'Pre�o',
	@faturamento AS 'Faturamento'

MD-9-Exercicio-3_SQLhashtag.sql

--M�DULO 9: EXERC�CIOS

--3. Voc� � respons�vel por gerenciar um banco de dados onde s�o recebidos dados externos de usu�rios.
--Em resumo, esses dados s�o: 

-- Nome do usu�rio
-- Data de nascimento
-- Quantidade de pets que aquele usu�rio possui

--Voc� precisar� criar um c�digo em SQL capaz de juntar as informa��es fornecidas por este usu�rio.
--Para simular estes dados, crie 3 vari�veis, chamadas: nome, data_nascimento e num_pets.
--Voc� dever� armazenar os valores �Andr�, �10/02/1998� e 2, respectivamente.
--O resultado final a ser alcan�ado � mostrado no print abaixo:

--Dica: voc� precisar� utilizar as fun��es CAST e FORMAT para chegar no resultado. 

DECLARE 
	@nome VARCHAR(30) = 'Andr�',
	@data_nascimento DATETIME = '10/02/1998', 
	@num_pets INT = 2

SELECT 
	'Meu nome � ' + @nome + ', nasci em ' + FORMAT(@data_nascimento,'MM/dd/yyyy') + ' e tenho ' + CAST(@num_pets AS VARCHAR(30)) + ' pets.'

MD-9-Exercicio-4_SQLhashtag.sql

--M�DULO 9: EXERC�CIOS 

/*4. Voc� acabou de ser promovido e o seu papel ser� realizar um controle de qualidade sobre as lojas da empresa.
A primeira informa��o que � passada a voc� � que o ano de 2008 foi bem complicado para a empresa, pois foi quando duas das principais lojas fecharam.
O seu primeiro desafio � descobrir o nome dessas lojas que fecharam no ano de 2008,
para que voc� possa entender o motivo e mapear planos de a��o para evitar que outras lojas importantes tomem o mesmo caminho.
O seu resultado dever� estar estruturado em uma frase, com a seguinte estrutura: �As lojas fechadas no ano de 2008 foram:  � + nome_das_lojas */

--Obs: utilize o comando PRINT (e n�o o SELECT!) para mostrar o resultado. 

DECLARE
	@nome_das_lojas VARCHAR(50) = ''

SELECT
	@nome_das_lojas = @nome_das_lojas + DimStore.StoreName + ', '
FROM
	DimStore
WHERE FORMAT(CloseDate, 'yyyy') = 2008
--WHERE YEAR(CloseDate) = 2008
--WHERE CloseDate BETWEEN '01/01/2008' AND '12/31/2008'

PRINT 'As lojas fechadas no ano de 2008 foram: ' + @nome_das_lojas

MD-9-Exercicio-5_SQLhashtag.sql

--M�DULO 9: EXERC�CIOS 

--5. Voc� precisa criar uma consulta para mostrar a lista de produtos da tabela DimProduct para uma subcategoria espec�fica: �Lamps�.
--Utilize o conceito de vari�veis para chegar neste resultado.

DECLARE
	@lamps_sub VARCHAR(50)

SELECT
	@lamps_sub = DimProductSubcategory.ProductSubcategoryKey
FROM 
	DimProductSubcategory
WHERE ProductSubcategoryName = 'Lamps'

SELECT
	DimProduct.ProductName AS 'Nome do Produto'
FROM
	DimProduct
WHERE DimProduct.ProductSubcategoryKey = @lamps_sub

/*DECLARE
	@varSubKey INT,
	@varSubName VARCHAR(60)

SET @varSubName = 'Lamps'
SET @varSubKey = (SELECT DimProductSubcategory.ProductSubcategoryKey FROM DimProductSubcategory WHERE ProductSubcategoryName = @varSubName)

SELECT
	DimProduct.ProductName
FROM 
	DimProduct
WHERE ProductSubcategoryKey = @varSubKey*/