/* Projeto: Views SQL Server Autor: Ivair de Oliveira e Silva Descri��o: Cria��o, altera��o e agrega��o de Views com filtros, JOINs e GROUP BY */

-- ===================================== -- VIEW: Produtos -- ===================================== CREATE VIEW vwProdutos AS SELECT ProductName AS NomeProduto, ColorName AS Cor, UnitPrice AS Preco, UnitCost AS Custo FROM DimProduct; GO

-- ===================================== -- VIEW: Funcion�rios -- ===================================== CREATE VIEW vwFuncionarios AS SELECT FirstName AS Nome, BirthDate AS DataNascimento, DepartmentName AS Departamento FROM DimEmployee; GO

-- ===================================== -- VIEW: Lojas -- ===================================== CREATE VIEW vwLojas AS SELECT StoreKey AS IdLoja, StoreName AS NomeLoja, OpenDate AS DataAbertura FROM DimStore; GO

-- ===================================== -- VIEW: Clientes -- ===================================== CREATE VIEW vwClientes AS SELECT CONCAT(FirstName, ' ', LastName) AS NomeCompleto,

CASE 
    WHEN Gender = 'M' THEN 'Masculino'
    WHEN Gender = 'F' THEN 'Feminino'
    ELSE 'Nao Informado'
END AS Genero,

EmailAddress,
FORMAT(YearlyIncome, 'C', 'pt-BR') AS RendaAnual

FROM DimCustomer; GO

-- ===================================== -- VIEW: Lojas Ativas -- ===================================== CREATE VIEW vwLojasAtivas AS SELECT StoreKey, StoreName, OpenDate, Status FROM DimStore WHERE Status = 'On'; GO

-- ===================================== -- VIEW: Funcion�rios Marketing -- ===================================== CREATE VIEW vwFuncionariosMkt AS SELECT FirstName AS Nome, EmailAddress, DepartmentName FROM DimEmployee WHERE DepartmentName = 'Marketing'; GO

-- ===================================== -- VIEW: Produtos espec�ficos -- ===================================== CREATE VIEW vwContosoLitwareSilver AS SELECT ProductKey, ProductName, BrandName, ColorName, UnitPrice FROM DimProduct WHERE BrandName IN ('Contoso', 'Litware') AND ColorName = 'Silver'; GO

-- ===================================== -- VIEW: Total vendido por produto -- ===================================== CREATE VIEW vwTotalVendidoProdutos AS SELECT p.ProductName AS NomeProduto, SUM(f.SalesQuantity) AS TotalVendido FROM FactSales f INNER JOIN DimProduct p ON f.ProductKey = p.ProductKey GROUP BY p.ProductName; GO

-- ===================================== -- ALTER VIEW: Produtos (com marca) -- ===================================== ALTER VIEW vwProdutos AS SELECT ProductName AS NomeProduto, BrandName AS Marca, ColorName AS Cor, UnitPrice AS Preco, UnitCost AS Custo FROM DimProduct; GO

-- ===================================== -- ALTER VIEW: Funcion�rios (feminino) -- ===================================== ALTER VIEW vwFuncionarios AS SELECT FirstName AS Nome, BirthDate AS DataNascimento, DepartmentName AS Departamento FROM DimEmployee WHERE Gender = 'F'; GO

-- ===================================== -- ALTER VIEW: Lojas Ativas -- ===================================== ALTER VIEW vwLojas AS SELECT StoreKey, StoreName, OpenDate FROM DimStore WHERE Status = 'On'; GO

-- ===================================== -- VIEW: Total por marca -- ===================================== CREATE VIEW vwTotalProdutosMarca AS SELECT BrandName, COUNT(*) AS TotalProdutos FROM DimProduct GROUP BY BrandName; GO

-- ===================================== -- ALTER VIEW: Total por marca com peso -- ===================================== ALTER VIEW vwTotalProdutosMarca AS SELECT BrandName, COUNT(*) AS TotalProdutos, SUM(Weight) AS PesoTotal FROM DimProduct GROUP BY BrandName; GO

-- ===================================== -- DROP VIEW -- ===================================== DROP VIEW vwTotalProdutosMarca; GO