Dicas para o exame 70-461 – Microsoft Querying SQL Server 2012

Fala galera, na semana passada refiz a prova 70-461 – Querying SQL Server 2012, e desta vez passei \o/. É uma prova que força o nosso cérebro, e também exige escrita de códigos… Gostei do que vi na prova, exige bastante atenção e conhecimento.

Não vou entrar nos detalhes dos assuntos que caíram na prova, porque o Luti fez isso neste post: Impressões da prova 70-461

Minha idéia é ajudar você a ver e entender um pouco de alguns códigos T-SQL referente aos assuntos que caíram na prova. Não são só esses tópicos que caíram, mas esses já te ajudam a ter uma idéia de uma parte da prova. Dentro deste link, vá até a sessão Skills Measures, e conheça todos os assuntos cobrados na prova.

Outro ponto interessante para estudar é o Training Kit deste exame, que você pode comprar diretamente no site da O’Reilly clicando aqui. Compramos este eBook aqui onde eu trabalho, e ajudou bastante para revisar os itens do exame.

O código T-SQL abaixo possui as explicações diretamente nos comentários acima de cada bloco de código. Podem copiar e colar no SQL Server Management Studio para ver a formatação do código e executar passo a passo para ver os resultados e entender as diferenças…

use tempdb
go

-- drop table tbNogare
create table tbNogare (id int identity(1,1), nome varchar(5))
GO
insert into tbNogare values ('AAA'),('BBB'),('CCC'),('AAA')
                   ,('DDD'),('DDD'),('EEE'),('EEE'),('AAA')
GO

/* TOP (x) WITH TIES -- Adiciona as ocorrencias repetidas do ultimo item 
encontrado */
select top (1) with ties nome from tbNogare order by nome
select top (5) with ties nome from tbNogare order by nome
select top (6) with ties nome from tbNogare order by nome

/* OFFSET FETCH -- cria paginação, iniciando em um registro e 
informando quantos outros devem surgir */
select id, nome from tbNogare order by ID 
offset 4 rows fetch first 2 rows only

/* CROSS JOIN -- cria um Produto Cartesiano, multiplicando todos os 
itens da tabela 1 por todos da tabela 2. Inclusive aplicando filtros */
select I.id, N.nome
from tbNogare I cross apply tbNogare N
where I.id < 3

select t2.id, t2.nome, t1.id, T1.nome from tbNogare t2
cross apply (select t.id, t.nome from tbNogare T where t.id >= 4) T1
where t2.id = 2

/* Table Value Function - Retornar uma tabela através de uma função */
CREATE FUNCTION dbo.FuncaoTabular (@id int) RETURNS TABLE
AS
RETURN (SELECT nome from tbNogare where id >= @id)

select * from FuncaoTabular(2)
select * from FuncaoTabular(4)
select * from FuncaoTabular(7)

/* WINDOWS FUNCTIONS */
-- Ultimo dia do mês, inserindo um mês como parametro
select EOMONTH(getdate(),-1) -- o parametro negativo, retorna os meses.
select EOMONTH(getdate(),0) -- o parametro zero, retorna o mês atual.
select EOMONTH(getdate(),1) -- o parametro positivo, avança os meses.

-- Windows Functions
select 
 first_value(id) over (order by id) primeiro
,lag(id) over (order by id) anterior
,id
, lead(id) over (order by id) seguinte
, last_value(id) over (order by id) ultimo
from tbNogare
where id in (3,4,5,6)

-- Ranking agrupado pela quebra e mantendo o count do numero da linha
select RANK() over (order by nome) item, nome
        from tbNogare order by nome

-- Ranking agrupado pela quebra e continuando o count do numero da linha
select DENSE_RANK() over (order by nome) item, nome 
        from tbNogare order by nome

-- Ranking agrupado pela quebra e dividindo o numero 
-- das linhas pelo parametro
select ntile(4) over (order by nome) item, nome
        from tbNogare order by nome

/* XML */
-- FOR XML RAW --> por padrão, apresenta os dados 
-- em atributos (dentro da tag)
select * from tbNogare for xml raw
select * from tbNogare for xml raw ('Linha') -- Nome da linha
select * from tbNogare for xml raw ('Linha'), elements -- ELEMENTOS
select * from tbNogare for xml raw ('Linha'), root('Raiz') -- Nó raiz
select * from tbNogare for xml raw ('Linha'), elements, root('Raiz')
select * from tbNogare for xml raw ('Linha'), elements, root('Raiz')
        , XMLSCHEMA('Nogare_70-461') -- Adicionar XMLSchema

-- FOR XML AUTO --> por padrão, apresenta os dados
-- em atributos (dentro da tag)
select * from tbNogare for xml auto  -- NÃO pode nome da linha
select * from tbNogare for xml auto, elements  -- ELEMENTOS
select * from tbNogare for xml auto, root('Raiz') -- Nó raiz
select * from tbNogare for xml auto, elements, root('Raiz')
select * from tbNogare for xml auto, elements, root('Raiz')
        , XMLSCHEMA('Nogare_70-461') -- Adicionar XMLSchema

-- FOR XML PATH --> por padrão, apresenta os dados em 
-- elementos (fora da tag). Não suporta XMLSchema
select * from tbNogare for xml path
select * from tbNogare for xml path ('Linha') -- Nome da linha
select * from tbNogare for xml path ('Linha'), ROOT('Raiz') -- Nó raiz

/* Coluna Computada */
-- Retorno computado (calculo simples)
select (ID * 2) AS COLUNA from tbNogare 

-- Criação computada (calculo simples)
CREATE TABLE #tbNogare (id int identity, valor int, dobro as valor *2 )
GO
insert into #tbNogare (valor) values (2),(3),(4)
GO
select * from #tbNogare
GO

/* SEQUENCE */
CREATE SEQUENCE seqNogare AS INT
INCREMENT BY 1 -- Valor do incremento
MINVALUE 1 -- mínimo valor
MAXVALUE 10 -- máximo valor
CYCLE -- também pode ser NO CYCLE. Informa se volta pro começo ou não
START WITH 5 -- Informa o início do ciclo. Quando bater no valor máximo,
             -- volta pro início (valor mínimo)

-- É possível compartilhar uma sequence entre mais de um objeto
CREATE TABLE #tbNogare2 (id int)
CREATE TABLE #tbNogare3 (id int)

insert into #tbNogare2(id) values (next value for seqNogare)
go 3
insert into #tbNogare3(id) values (next value for seqNogare)
go 5

select * from #tbNogare2
select * from #tbNogare3

/* OUTPUT */
INSERT INTO #tbNogare(valor)
output inserted.dobro -- Retorna um valor quando inserido
values (10)

select * from #tbNogare

delete from #tbNogare
output deleted.id -- Retorna um valor quando deletado
where valor = 10

select * from #tbNogare

/* ISOLATION LEVEL */
-- READ COMMITED >> (default) Leitura de dados comitados (gera block)
-- READ UNCOMMITED >> Leitura de dados não comitados (leitura suja)
-- READ COMMITED SNAPSHOT >> Lê os ultimos dados comitados, qualquer 
-- alteração, grava a origem da alteração no TempDB
-- REPEATABLE READ >> Lê o dado quantas vezes forem necessárias dentro
-- da transação. Mantém os dados que sofreram DELETES ou UPDATES
-- mas adiciona os que sofreram INSERT. (Leitura Fantasma)
-- SNAPSHOT >> Lê o dado comitado, sem ter leitura suja ou fantasma
-- SERIALIZABLE >> Trava as colunas utilizadas no SELECT, não permitindo
-- inserção de dados que alterem o resultado

/* THROW */
SELECT * FROM tbNogare

BEGIN TRY
    INSERT tbNogare(ID) VALUES(1);
END TRY
BEGIN CATCH
    PRINT '-------0-------';
    THROW 50000, '>>> SEU ERRO AQUI <<<',0
END CATCH;

/* TRY CONVERT */
-- Quando falhar, ao invés de erro ele retorna null
SELECT CONVERT(int, '55')
SELECT CONVERT(int, 'texto')
SELECT TRY_CONVERT(int, '66')
SELECT TRY_CONVERT(int, 'texto')

/* TRY PARSE */
-- Quando falhar, ao invés de erro ele retorna null
SELECT PARSE('44' as int)
SELECT PARSE('b' as int)
SELECT TRY_PARSE('33' as int)
SELECT TRY_PARSE('b' as int)

DROP TABLE tbNogare
go

Aproveite que ainda está em época de segunda chance, e marque agora mesmo seu exame. Já pensou começar 2013 fazendo esta prova de SQL Server 2012??

Espero que isso lhe ajude a passar no exame, bons estudos!