SQL Server 2008 - Modulo 09 - Curso 2778

--Aula 01 - Considerations for Querying Data
-- ====================================================================================
-- Autor        : Lineu Alves Lima Filho
-- Data Criação    : 02/11/2009
-- Descrição    : Módulo 09 - Lição 01
-- ====================================================================================
-- Versão        : 1.0
-- ====================================================================================
-- Versão            : 1.1
-- Data Atualização    : 25/02/2010
-- ====================================================================================



USE AdventureWorks2008
GO

--Estimated Execution Plan
--Selecionar o script a ser executado
--Clicar em Query -> Display Estimated Execution Plan

--Ou

--CTRL + L


SELECT PersonType, FirstName, MiddleName, LastName
FROM Person.Person
WHERE FirstName = 'Michael'
ORDER BY LastName





--Actual Execution Plan
--Clicar em Query -> Include Actual Execution Plan ou CTRL + M (a opção será ativada)
--Selecionar o script e executá-lo com F5
--Clicar sobre a Tab "Execution plan"


SELECT PersonType, FirstName, MiddleName, LastName
FROM Person.Person
WHERE FirstName = 'Michael'
ORDER BY LastName





--Visualizando o Plano de Execução em formato TEXTO
SET SHOWPLAN_TEXT ON
GO

SELECT PersonType, FirstName, MiddleName, LastName
FROM Person.Person
WHERE FirstName = 'Michael'
ORDER BY LastName

SET SHOWPLAN_TEXT OFF
GO





--Visualizando o Plano de Execução em formato XML
SET SHOWPLAN_XML ON
GO

SELECT PersonType, FirstName, MiddleName, LastName
FROM Person.Person
WHERE FirstName = 'Michael'
ORDER BY LastName

SET SHOWPLAN_XML OFF
GO





--Salvando Planos de Execução
--Clicar com o botão direito do mouse sobre o Plano de Executação
--Escolher a opção "Save Execution Plan As..."




--Referência Planos de Execução:
--ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_4deptrbl/html/17d5daa1-8f14-46e2-9cea-0ed520217d1e.htm



--Conversão Implícita

--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 produtos
(
    cod_prod        INT            IDENTITY        PRIMARY KEY,
    nome_prod        NCHAR(50),
    val_prod        DEC(9,2),
    qtda_prod        INT
)

INSERT INTO produtos VALUES
('Batata', 1.99, 10), ('Cenoura', 2.98, 10),('Feijão', 5, 3.88)

--Multiplicando val_prod (DECIMAL) com qtda_prod (INT)
SELECT    nome_prod,
        val_prod,
        qtda_prod,
        (val_prod * qtda_prod) AS Total
FROM produtos



--O parâmetro é do tipo CHAR e a coluna é do tipo INT
CREATE PROC usp_cod_prod @cod_prod CHAR(03)
AS
SELECT    nome_prod,
        val_prod,
        qtda_prod,
        (val_prod * qtda_prod) AS Total
FROM produtos
WHERE cod_prod = @cod_prod


--Executando a Stored Procedure
EXEC usp_cod_prod '1'





--Conversão Explícita


--Sintaxe
--CAST(ColunaOuVariável AS TipoDeDado)

DECLARE @var DEC(9,2)
SET @var = 10.99

SELECT CAST(@var AS VARCHAR(05))
SELECT CAST(@var AS INT)
SELECT CAST(@var AS MONEY)

--Sintaxe
--CONVERT(TipoDeDado, ColunaOuVariável, Estilo)



DECLARE @var DEC(9,2)
SET @var = 10.99

SELECT CONVERT(VARCHAR(05), @var)
SELECT CONVERT(INT, @var)
SELECT CONVERT(MONEY, @var)



--Convertendo Data
SELECT CONVERT(VARCHAR(30), GETDATE(), 101) AS [Padrão Americano]
SELECT CONVERT(VARCHAR(30), GETDATE(), 103) AS [Padrão Brasileiro]
SELECT CONVERT(VARCHAR(30), GETDATE(), 105) AS [Padrão Italiano]


--Referência Conversão Implícita e Explícita:
--ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/a87d0850-c670-4720-9ad5-6f5a22343ea8.htm


--Aula 02 - Working with Data Types
-- ====================================================================================
-- Autor        : Lineu Alves Lima Filho
-- Data Criação    : 02/11/2009
-- Descrição    : Módulo 09 - Lição 02
-- ====================================================================================
-- Versão        : 1.0
-- ====================================================================================
-- Versão            : 1.1
-- Data Atualização    : 25/02/2010
-- ====================================================================================


USE DB2778
GO

CREATE TABLE DataHora
(
    ID                INT                PRIMARY KEY,
    Hora            TIME            DEFAULT GETDATE(),
    Data            DATE            DEFAULT GETDATE(),
    DataPequena        SMALLDATETIME    DEFAULT GETDATE(),
    DataHora        DATETIME        DEFAULT GETDATE(),
    DataHora2        DATETIME2        DEFAULT GETDATE(),
    FusoHorario        DATETIMEOFFSET    DEFAULT GETDATE()
)
GO

INSERT INTO DataHora (ID) VALUES (1), (2), (3), (4), (5)


--Horário de Londres
INSERT INTO DataHora (ID, FusoHorario)
VALUES (6, '2010-02-25 11:35:00+02:00')


--Horário de Sydney na Austrália
INSERT INTO DataHora (ID, FusoHorario)
VALUES (7, '2010-02-25 11:35:00+10:00')


SELECT * FROM DataHora





--Tipo de dado HIERARCHYID

CREATE TABLE funcionarios
(
    func_id        HIERARCHYID         NOT NULL,
    nome_func    CHAR(30),
)
GO

--Chefes
INSERT INTO funcionarios (func_id, nome_func) VALUES (HIERARCHYID::GetRoot(), 'Mario')
INSERT INTO funcionarios (func_id, nome_func) VALUES (HIERARCHYID::GetRoot(), 'Julia')

--Subordinados
INSERT INTO funcionarios (func_id, nome_func) VALUES ('/1/', 'Joana')
INSERT INTO funcionarios (func_id, nome_func) VALUES ('/1/', 'Maria')
INSERT INTO funcionarios (func_id, nome_func) VALUES ('/1/', 'Lucas')
INSERT INTO funcionarios (func_id, nome_func) VALUES ('/1/', 'Henrique')
INSERT INTO funcionarios (func_id, nome_func) VALUES ('/1/', 'Roberto')
INSERT INTO funcionarios (func_id, nome_func) VALUES ('/1/', 'Eduarda')


SELECT * FROM funcionarios


--Visualizando todos os funcionários
SELECT    func_id,                            --Código do funcionário armazenado em binário
        func_id.ToString() AS [Caminho],    --Representação do funcionário no organograma
        func_id.GetLevel() AS [Nível],        --Nível do funcionário no organograma
        nome_func                            --Nome do funcionário
FROM funcionarios


--Visualizando os funcionários subordinados a Mario e Julia (Chefes)
SELECT    func_id,
        func_id.ToString() AS [Caminho],
        func_id.GetLevel() AS [Nível],
        nome_func
FROM funcionarios
WHERE func_id.IsDescendantOf('/1/') = 1


--Apagando a tabela "funcionarios"
DROP TABLE funcionarios


--Aula 03 - Cursors and Set-Based Queries
-- ====================================================================================
-- Autor        : Lineu Alves Lima Filho
-- Data Criação    : 02/11/2009
-- Descrição    : Módulo 09 - Lição 03
-- ====================================================================================
-- Versão        : 1.0
-- ====================================================================================
-- Versão            : 1.1
-- Data Atualização    : 25/02/2010
-- ====================================================================================


USE DB2778
GO


CREATE TABLE cidades
(
    cod_cid        INT            IDENTITY        PRIMARY KEY,
    nome_cid    NCHAR(50)
)


INSERT INTO cidades VALUES
('São José dos Campos'), ('Taubaté'), ('Caçapava'),
('Guaratinguetá'), ('Lorena'), ('Cruzeiro'),
('Jacareí'), ('Mogi das Cruzes'), ('Pindamonhangaba'),
('São Paulo'), ('Suzano'), ('Campinas')


SELECT TOP 20 * FROM cidades


--Cursor Manual - Explicação Didática
--Executar os passos separadamente

--Passo 1 -> Declarando o cursor
DECLARE cidade CURSOR
FOR SELECT * FROM dbo.cidades

--Passo 2 -> Abrindo o cursor
OPEN cidade

--Passo 3 -> Buscando os registros
FETCH NEXT FROM cidade

--Passo 4 -> Fechando o cursor
CLOSE cidade

--Passo 5 -> Retirando o cursor da memória
DEALLOCATE cidade





--Cursor Automático
--1º Executar os passos separadamente
--2º Executar o bloco de código todo de uma só vez

--Passo 1 -> Declarando o cursor
DECLARE cidade CURSOR
FOR SELECT * FROM dbo.cidades

--Passo 2 -> Abrindo o cursor
OPEN cidade

--Passo 3 -> Buscando os registros
FETCH NEXT FROM cidade

--Passo 4 -> Loop
WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH NEXT FROM cidade
END

--Passo 5 -> Fechando o cursor
CLOSE cidade

--Passo 6 -> Retirando o cursor da memória
DEALLOCATE cidade





--Cursor utilizando variável

--Passo 1 -> Declarando variáveis
DECLARE @nome_cidade NVARCHAR(50)

--Passo 2 -> Declarando o cursor
DECLARE @cidade CURSOR

--Passo 3 -> Inicializando o cursor
SET @cidade = CURSOR FOR
SELECT nome_cid FROM dbo.cidades

--Passo 4 -> Abrindo o cursor
OPEN @cidade

--Passo 5 -> Buscando os registros
FETCH NEXT FROM @cidade INTO @nome_cidade

--Passo 6 -> Loop
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @nome_cidade
    FETCH NEXT FROM @cidade INTO @nome_cidade
END

--Passo 7 -> Fechando o cursor
CLOSE @cidade

--Passo 8 -> Retirando o cursor da memória
DEALLOCATE @cidade



--Aula 04 - Dynamic SQL

-- ====================================================================================
-- Autor        : Lineu Alves Lima Filho
-- Data Criação    : 02/11/2009
-- Descrição    : Módulo 09 - Lição 04
-- ====================================================================================
-- Versão        : 1.0
-- ====================================================================================
-- Versão            : 1.1
-- Data Atualização    : 25/02/2010
-- ====================================================================================



USE AdventureWorks2008
GO


--Usando EXECUTE
DECLARE @CodigoSQL NVARCHAR(500)
SET @CodigoSQL = 'SELECT TOP 10 * FROM Person.Person'
EXEC (@CodigoSQL)


--Usando SP_EXECTESQL
DECLARE @CodigoSQL NVARCHAR(500)
SET @CodigoSQL = 'SELECT TOP 10 * FROM Person.Person'
EXEC sp_executesql @CodigoSQL





--Usando EXECUTE
DECLARE    @ScriptSQL        NVARCHAR(1000)
DECLARE    @ListaColunas    NVARCHAR(200)
SET @ListaColunas = 'FirstName, MiddleName, LastName'
SET @ScriptSQL = 'SELECT TOP 10 ' + @ListaColunas + ' FROM Person.Person'
EXEC (@ScriptSQL)


--Usando SP_EXECTESQL
DECLARE    @ScriptSQL        NVARCHAR(1000)
DECLARE    @ListaColunas    NVARCHAR(200)
SET @ListaColunas = 'FirstName, MiddleName, LastName'
SET @ScriptSQL = 'SELECT TOP 10 ' + @ListaColunas + ' FROM Person.Person'
EXECUTE sp_executesql @ScriptSQL