SQL Server 2008 - Modulo 05 - Curso 2778

--Aula 01 - Writing Basic Subqueries

--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 estoque
(
    cod_prod    INT        IDENTITY,
    nome_prod    CHAR(30),
    val_prod    DEC(9,2),
    qtda_prod    INT
)
GO


INSERT INTO estoque VALUES
('Arroz',9.89, 10), ('Batata',1.99, 16), ('Cebola',2.48, 21),
('Feijão',3.59, 7), ('Cenoura',2.00, 91), ('Beterraba',1.19, 23),
('Farinha',2.19, 78), ('Ervilha',1.19, 2), ('Milho Verde',3.49, 7),
('Tomate',4.59, 10)


SELECT TOP 10 * FROM estoque



--Retornando todas as colunas do registro cujo
--valor do produto é o maior em estoque.
SELECT * FROM estoque
WHERE val_prod IN (SELECT MAX(val_prod)
                   FROM estoque)

--Exemplo de Scalar Subquery -> retorna somente um registro.





--Retornando todas as colunas do registro cujo
--código do produto seja maior que 7.
SELECT * FROM estoque
WHERE cod_prod IN (SELECT cod_prod
                   FROM estoque
                   WHERE cod_prod > 7)
                  
--Exemplo de Tabular Subquery -> retorna vários registros.





--Retornando o nome, o valor, o valor médio dos protudos em estoque
--e o valor dos produtos menos o valor médio dos produtos
--em estoque (val_prod - AVG(val_prod).
SELECT    nome_prod,
        val_prod,
        (SELECT AVG(val_prod) FROM estoque) AS [Valor Médio],
        val_prod - (SELECT AVG(val_prod) FROM estoque) AS [Diferença]
FROM estoque





--SOME, ANY e ALL

CREATE TABLE tabela01
(
    codigo    INT
)
GO

INSERT INTO tabela01 VALUES (1), (2), (3), (4)

SELECT TOP 10 * FROM tabela01


--O script abaixo retornará VERDADEIRO quando pelo menos 1 valor satisfazer a condição.
IF 3 < SOME (SELECT codigo FROM tabela01)
    PRINT 'Verdadeiro'
ELSE
    PRINT 'Falso'


--Todos os valores devem satisfazer a condição para o script abaixo retornar VERDADEIRO.
IF 3 < ALL (SELECT codigo FROM tabela01)
    PRINT 'Verdadeiro'
ELSE
    PRINT 'Falso'



--Buscar nome_prod e val_prod dos produtos cujo valor seja menor ou igual
--o valor médio em estoque menos 2.00 (AVG(val_prod) - 2) (ALL).
SELECT nome_prod, val_prod
FROM estoque
WHERE NOT val_prod >= ALL (SELECT AVG(val_prod) - 2
                           FROM estoque)



--Buscar nome_prod e val_prod dos produtos cujo valor seja menor ou igual
--o valor médio em estoque menos 2.00 (AVG(val_prod) - 2) (SOME ou ANY).
SELECT nome_prod, val_prod
FROM estoque
WHERE val_prod <= SOME (SELECT AVG(val_prod) - 2
                        FROM estoque)
                       
                       
--Aula 02 - Writing Correlated Subqueries

USE DB2778
GO

--Correlated Subquery

--Retornar as colunas nome_prod, val_prod e qtda_prod cuja quantidade do
--produto seja 10.
SELECT nome_prod, val_prod, qtda_prod
FROM estoque e
WHERE 10 IN (SELECT qtda_prod
             FROM estoque
             WHERE cod_prod = e.cod_prod)




--Retornar as colunas nome_prod e qtda_prod cuja quantidade do
--produto esteja entre 20 e 30.
SELECT nome_prod, qtda_prod
FROM estoque e
WHERE EXISTS (SELECT * FROM estoque
              WHERE qtda_prod BETWEEN 20 AND 30
              AND cod_prod = e.cod_prod)

--Aula 03 - Comparing Subqueries with Joins and Temporary Tables
USE DB2778
GO

--Tabela Temporária Local
CREATE TABLE #estoque
(
    cod_prod    INT        IDENTITY,
    nome_prod    CHAR(30),
    val_prod    DEC(9,2),
    qtda_prod    INT
)
GO

INSERT INTO #estoque VALUES
('Arroz',9.89, 10), ('Batata',1.99, 16), ('Cebola',2.48, 21),
('Feijão',3.59, 7), ('Cenoura',2.00, 91), ('Beterraba',1.19, 23),
('Farinha',2.19, 78), ('Ervilha',1.19, 2), ('Milho Verde',3.49, 7),
('Tomate',4.59, 10)


SELECT TOP 10 * FROM #estoque


--Abrir outra sessão e executar a query acima.





--Em outra sessão, criar a seguinte tabela temporária global

--Tabela Temporária Global
CREATE TABLE ##estoque
(
    cod_prod    INT        IDENTITY,
    nome_prod    CHAR(30),
    val_prod    DEC(9,2),
    qtda_prod    INT
)
GO

INSERT INTO ##estoque VALUES
('Arroz',9.89, 10), ('Batata',1.99, 16), ('Cebola',2.48, 21),
('Feijão',3.59, 7), ('Cenoura',2.00, 91), ('Beterraba',1.19, 23),
('Farinha',2.19, 78), ('Ervilha',1.19, 2), ('Milho Verde',3.49, 7),
('Tomate',4.59, 10)


SELECT TOP 10 * FROM ##estoque

--Abrir uma nova sessão e executar a query acima.



--Aula 04 - Using Common Table Expressions

--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 estoque
(
    cod_prod    INT        IDENTITY,
    nome_prod    CHAR(30),
    val_prod    DEC(9,2),
    qtda_prod    INT
)
GO


INSERT INTO estoque VALUES
('Arroz',9.89, 10), ('Batata',1.99, 16), ('Cebola',2.48, 21),
('Feijão',3.59, 7), ('Cenoura',2.00, 91), ('Beterraba',1.19, 23),
('Farinha',2.19, 78), ('Ervilha',1.19, 2), ('Milho Verde',3.49, 7),
('Tomate',4.59, 10)


SELECT TOP 10 * FROM estoque



--Tabela Derivada
--Retornando as colunas nome_prod e qtda_prod cuja quantidade em
--estoque seja inferir a 10.
SELECT nome_prod, qtda_prod
FROM (SELECT * FROM estoque
      WHERE qtda_prod < 10) AS TabelaDerivada
ORDER BY qtda_prod ASC





--Exemplo de Common Table Expression (CTE)
WITH MinhaPrimeiraCTE(nome)
AS
(
    SELECT nome = 'Lineu'
)
SELECT nome FROM MinhaPrimeiraCTE





--CCommon Table Expression (CTE)
--Retornando as colunas nome_prod e qtda_prod cuja quantidade em
--estoque seja inferir a 10.
WITH QtdaMinima(nome_produto, qtda_produto)
AS
(
    SELECT nome_prod, qtda_prod FROM estoque
    WHERE qtda_prod < 10
)
SELECT * FROM QtdaMinima
ORDER BY qtda_produto ASC





--Exemplo de Recursive Common Table Expression (CTE)
WITH MinhaSegundaCTE(nome)
AS
(
    SELECT nome = CONVERT(VARCHAR(8000),'Executou 1')
    UNION ALL
    SELECT nome + '1' FROM MinhaSegundaCTE WHERE LEN(nome) < 15
)
SELECT nome FROM MinhaSegundaCTE