--Recriando o banco de dados DB2778
USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = 'DB2778')
DROP DATABASE DB2778
GO
CREATE DATABASE DB2778
GO
USE DB2778
GO
CREATE TABLE clubes
(
cod_clube INT IDENTITY(1,1),
nome_clube CHAR(30),
pontos_clube INT
)
GO
INSERT INTO clubes VALUES
('Palmeiras',25), ('Grêmio',29), ('Vitória',26),
('Flamengo',28), ('Cruzeiro',27), ('São Paulo',26)
--Retornando o nome e o número de pontos de todos os clubes
SELECT nome_clube, pontos_clube
FROM clubes
--Retornando o nome e o número de pontos de todos os clubes ordenado por pontos_clube (DESC)
SELECT nome_clube, pontos_clube
FROM clubes
ORDER BY pontos_clube DESC
--Retornando o nome e o número de pontos dos 4 primeiros colocados ordenado por pontos_clube (DESC)
SELECT TOP 4 nome_clube, pontos_clube
FROM clubes
ORDER BY pontos_clube DESC
--Retornando o nome e o número de pontos dos 4 primeiros colocados ordenado por pontos_clube (DESC)
--Incluir os clubes empatados em 4º lugar com o mesmo número de pontos
SELECT TOP 4 WITH TIES nome_clube, pontos_clube
FROM clubes
ORDER BY pontos_clube DESC
--**********************************************************
-- AVG, COUNT, MAX, MIN e SUM
--**********************************************************
CREATE TABLE estoque
(
cod_prod INT,
nome_prod CHAR(30),
val_prod DEC(9,2)
)
GO
INSERT INTO estoque VALUES
(1,'Arroz',5.00), (2,'Batata',2.00), (3,'Cebola',2.00)
--Retornando o valor médio dos produtos em estoque
SELECT AVG(val_prod)
FROM estoque
--Inserindo um produto sem valor
INSERT INTO estoque (cod_prod, nome_prod) VALUES (4,'Farinha')
--Retornando o valor médio dos produtos em estoque
SELECT AVG(val_prod)
FROM estoque
--Inserindo um produto com valor
INSERT INTO estoque VALUES (5,'Tomate',5.00)
--Retornando o valor médio dos produtos em estoque
SELECT AVG(val_prod)
FROM estoque
--Retornando o valor total dos produtos em estoque
SELECT SUM(val_prod)
FROM estoque
--Retornando o MENOR valor do produto em estoque
SELECT MIN(val_prod)
FROM estoque
--Retornando o MAIOR valor do produto em estoque
SELECT MAX(val_prod)
FROM estoque
--Retornando o número de produtos em estoque
SELECT COUNT(*) FROM estoque
--Retornando o número de produtos em estoque usando uma coluna (PROBLEMA)
SELECT COUNT(val_prod) FROM estoque
--Ou
--Retornando o número de produtos em estoque usando uma coluna (SOLUÇÃO)
SELECT COUNT(ISNULL(val_prod, 0)) FROM estoque
--**********************************************************
-- COMPUTE e COMPUTE BY
--**********************************************************
CREATE TABLE loja_pet
(
codigo INT IDENTITY,
tipo CHAR(30),
loja VARCHAR(30),
valor DECIMAL(9,2)
)
GO
INSERT INTO loja_pet VALUES ('Cachorro','Loja Vila Rubi',100.00),
('Gato','Loja Vila Rubi',200.00), ('Cobra','Loja Colinas',950.00),
('Cachorro','Loja Vila Rubi',300.00), ('Tartaruga','Loja Center Vale',50.00),
('Cachorro','Loja Colinas',350.00), ('Gato','Loja Vila Rubi',120.00),
('Cachorro','Loja Center Vale',550.00), ('Gato','Loja Colinas',180.00)
SELECT * FROM loja_pet
--Adicionando a coluna "qtda" na tabela loja_pet
ALTER TABLE loja_pet
ADD qtda SMALLINT
SELECT * FROM loja_pet
----------------------------------------------------------------------------
--Atualizando a coluna "qtda" com a quantidade de animais na loja
UPDATE loja_pet SET qtda = 2 WHERE codigo = 1
UPDATE loja_pet SET qtda = 3 WHERE codigo = 2
UPDATE loja_pet SET qtda = 10 WHERE codigo = 3
UPDATE loja_pet SET qtda = 5 WHERE codigo = 4
UPDATE loja_pet SET qtda = 8 WHERE codigo = 5
UPDATE loja_pet SET qtda = 1 WHERE codigo = 6
UPDATE loja_pet SET qtda = 4 WHERE codigo = 7
UPDATE loja_pet SET qtda = 4 WHERE codigo = 8
UPDATE loja_pet SET qtda = 12 WHERE codigo = 9
----------------------------------------------------------------------------
SELECT * FROM loja_pet
--Verificando o total de animais em estoque (COMPUTE)
SELECT loja, tipo, qtda
FROM loja_pet
ORDER BY tipo, qtda
COMPUTE SUM(qtda)
--Verificando o total de animais em estoque ordenado por tipo (COMPUTE BY)
SELECT loja, tipo, qtda
FROM loja_pet
ORDER BY tipo, qtda
COMPUTE SUM(qtda) BY tipo
--Verificando o total de animais em estoque ordenado por loja (COMPUTE BY)
SELECT loja, tipo, qtda
FROM loja_pet
ORDER BY loja, qtda
COMPUTE SUM(qtda) BY loja
--Aula 02 - Summarizing Grouped Data
USE DB2778
GO
CREATE TABLE livros
(
cod_liv INT IDENTITY PRIMARY KEY,
nome_liv NCHAR(50) NOT NULL,
idioma_liv NCHAR(20),
ano_edicao_liv NCHAR(04)
)
GO
SET NOCOUNT ON
INSERT INTO livros VALUES ('Olhe Outra Vez', 'Português', '2005')
INSERT INTO livros VALUES ('O Andar do Bebado', 'Português', '2006')
INSERT INTO livros VALUES ('O Símbolo Perdido', 'Português', '2008')
INSERT INTO livros VALUES ('Abatendo Gigantes', 'Português', '2008')
INSERT INTO livros VALUES ('Apareça e Cresça', 'Português', '2009')
INSERT INTO livros VALUES ('The Lost Symbol', 'Inglês', '2004')
INSERT INTO livros VALUES ('The Girl Who Played With Fire', 'Inglês', '2005')
INSERT INTO livros VALUES ('The Storm of War', 'Inglês', '2005')
INSERT INTO livros VALUES ('Mi Buenos Aires Querido', 'Espanhol', '2005')
SELECT * FROM livros
--Verificando o número de livros agrupando por idioma (GROUP BY)
SELECT idioma_liv AS [Idioma], COUNT(idioma_liv) AS [# de Livros]
FROM livros
GROUP BY idioma_liv
--Verificando o número de livros agrupando por idioma (GROUP BY)
--e retornando o número total de livros (WITH ROLLUP)
SELECT idioma_liv AS [Idioma], COUNT(idioma_liv) AS [# de Livros]
FROM livros
GROUP BY idioma_liv
WITH ROLLUP
--Verificando o número de livros agrupando por idioma e ano_edicao_liv (GROUP BY)
--e retornando o número total de livros publicado de cada ano (WITH CUBE)
SELECT idioma_liv AS [Idioma], ano_edicao_liv, COUNT(idioma_liv) AS [# de Livros]
FROM livros
GROUP BY idioma_liv, ano_edicao_liv
WITH CUBE
--COALESCE
CREATE TABLE funcionario
(
cod_func INT IDENTITY PRIMARY KEY,
nome_func NCHAR(50) NOT NULL,
valor_h DEC(9,2),
salario DEC(9,2)
)
GO
INSERT INTO funcionario (nome_func, valor_h) VALUES
('Maria', 50), ('Aline', 32), ('João', 28)
INSERT INTO funcionario (nome_func, salario) VALUES
('Leonardo', 1200), ('Roberto', 1800), ('Júlia', 2100)
SELECT * FROM funcionario
--Verificar o salário dos funcionários retornando o valor semanal ou mensal (COALESCE)
SELECT nome_func,
CAST(COALESCE(valor_h, salario) AS MONEY) AS [Semanal ou Mensal]
FROM funcionario
--Aula 03 - Ranking Grouped Data
USE DB2778
GO
CREATE TABLE vendas
(
codigo INT IDENTITY,
vendedor NCHAR(30),
produto NCHAR(30),
quantidade NUMERIC(15,4),
telefonemas INT
)
GO
SET NOCOUNT ON
INSERT INTO vendas VALUES ('João','Geladeira',5,15)
INSERT INTO vendas VALUES ('Jonas','Geladeira',2,13)
INSERT INTO vendas VALUES ('Rafael','Televisão',6,9)
INSERT INTO vendas VALUES ('Rafael','Geladeira',1,6)
INSERT INTO vendas VALUES ('João','Televisão',2,8)
INSERT INTO vendas VALUES ('Jonas','Bicicleta',7,11)
INSERT INTO vendas VALUES ('Rafael','Bicicleta',3,9)
INSERT INTO vendas VALUES ('Jonas','Roupeiro',4,9)
INSERT INTO vendas VALUES ('João','Computador',9,25)
INSERT INTO vendas VALUES ('Rafael','Computador',1,5)
INSERT INTO vendas VALUES ('João','Fogão',3,6)
INSERT INTO vendas VALUES ('Rafael','Fogão',6,8)
INSERT INTO vendas VALUES ('Jonas','Cama',2,3)
INSERT INTO vendas VALUES ('João','Cama',5,9)
SELECT * FROM vendas
--Retornar um ranking dos vendedores que mais venderam (ROW_NUMBER)
SELECT ROW_NUMBER() OVER(ORDER BY quantidade DESC) AS [Classificação],
vendedor,
produto,
quantidade
FROM vendas
--Retornar um ranking dos vendedores que mais venderam, ordenado por produto (ROW_NUMBER com PARTITION)
SELECT ROW_NUMBER() OVER(PARTITION BY produto ORDER BY quantidade DESC) AS [Classificação],
vendedor,
produto,
quantidade
FROM vendas
--Observando a diferença entre ROW_NUMBER, RANK e DENSE_RANK
SELECT vendedor,
produto,
quantidade,
ROW_NUMBER() OVER(ORDER BY quantidade DESC) AS [Row_Number],
RANK() OVER(ORDER BY quantidade DESC) AS [Rank],
DENSE_RANK() OVER(ORDER BY quantidade DESC) AS [Dense Rank]
FROM vendas
ORDER BY quantidade DESC
--Retornando um ranking com grupos de vendas (NTILE)
SELECT vendedor,
produto,
quantidade,
ROW_NUMBER() OVER(ORDER BY quantidade DESC) AS Número,
NTILE(3) OVER(ORDER BY quantidade DESC) AS Grupo
FROM vendas
ORDER BY quantidade DESC
-- Aula 04 - Creating Crosstab Queries
USE DB2778
GO
CREATE TABLE relatorio
(
relat_ano INT,
relat_mes VARCHAR(15),
relat_valor NUMERIC(9,2)
)
GO
INSERT relatorio VALUES
(2008, 'Jan', 10), (2008, 'Fev', 20), (2008, 'Mar', 30),
(2009, 'Jan', 40), (2009, 'Fev', 50), (2009, 'Mar', 60)
SELECT * FROM relatorio
--PIVOT
SELECT relat_ano, Jan, Fev, Mar
INTO relat_pivot
FROM relatorio PIVOT (SUM(relat_valor) FOR relat_mes IN (Jan, Fev, Mar)) AS pivot_table
ORDER BY relat_ano
SELECT * FROM relat_pivot
--UNPIVOT
SELECT * FROM relat_pivot UNPIVOT
(relat_valor FOR relat_mes IN (Jan, Fev, Mar)) AS unpivot_table
Nenhum comentário:
Postar um comentário