[SQL Server] Exame 70-461–Material para Complementar os Estudos

Devido ao fato do exame 70-461 ser novo (oficialmente disponível desde o mês de julho deste ano), praticamente não existem materiais de referência em português. Todavia, temos à disposição boas fontes de estudo que estão em inglês.

A primeira referência que deixo para vocês são os dois livros citados abaixo:

Livro: Microsoft SQL Server 2012 T-SQL Fundamentals
Autor: Itzik Ben-Gan
Link:
http://www.amazon.com/Microsoft-Server-2012-T-SQL-Fundamentals/dp/0735658145
Meus comentários: este livro cobre todos os tópicos exigidos no exame 70-461 e vai um pouco além. Ele te ajudará a dominar os fundamentos da programação T-SQL, proporcionando uma excelente base de conhecimentos para os exames seguintes também. Recomendo fortemente.

Livro: Training Kit Exam 70-461: Querying Microsoft SQL Server 2012
Autor: Itzik Ben-Gan
Link:
http://www.amazon.com/Training-Kit-Exam-70-461-Microsoft/dp/0735666059
Meus comentários: sem dúvida alguma a série Training Kit é a que melhor direciona os estudos, pois aborda com exatidão os assuntos exigidos no exame. Além de exercícios que reforçam a aprendizagem, o livro vem com um CD contendo testes que simulam o exame. Vale à pena.

 

Para aqueles que gostam de estudar através da internet, estou deixando uma listagem dos assuntos do exame + respectivos links para os estudos.

1) Create Database Objects (24%)

Create and alter tables using T-SQL syntax (simple statements)

CREATE TABLE

Microsoft Links:
http://msdn.microsoft.com/en-us/library/ms174979(v=sql.110).aspx

DROP TABLE

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms173790(v=sql.110).aspx

ALTER TABLE

Microsoft Links:

http://msdn.microsoft.com/en-us/library/ms190273(v=sql.110).aspx

ALTER COLUMN

Microsoft links:

Veja Alter table

Create and alter views (simple statements)

CREATE VIEW

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms187956(v=sql.110).aspx

ALTER VIEW

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms173846(v=sql.110).aspx

DROP VIEW

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms173492(v=sql.110).aspx

Create indexed views

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms191432(v=sql.110).aspx

Create and modify constraints (simple statements)

create constraints on tables

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms188066(v=sql.110).aspx

define constraints

Microsoft links:

Veja acima

unique constraints

Microsoft links:

Veja acima

default constraints

Microsoft links:

Veja acima

primary and foreign key constraints

Microsoft links:

Veja acima

Create and alter DML triggers

inserted and deleted tables

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms189799(v=sql.110).aspx

http://msdn.microsoft.com/en-us/library/ms176072(v=sql.110).aspx

nested triggers

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms190739(v=sql.110).aspx

types of triggers

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms178110(v=sql.110).aspx

handle multiple rows in a session

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms190752(v=sql.110).aspx

 

2) Work with Data (27%)

Query data by using SELECT statements

use the ranking function to select top(X) rows for multiple categories in a single query

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms189798(v=sql.110).aspx

write and perform queries efficiently using the new code items such as synonyms and joins (except, intersect)

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms188055(v=sql.105).aspx

Implement sub-queries

pivot and unpivot

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms177410(v=SQL.105).aspx

apply operator

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms175156(v=SQL.105).aspx

cte statement

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms190766(v=SQL.105).aspx

with statement

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms175972(v=sql.110).aspx

Implement data types

impact of GUID (newid, newsequentialid) on database performance

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms190348.aspx

http://msdn.microsoft.com/en-us/library/ms189786(v=sql.110).aspx

Implement aggregate queries

new analytic functions

Microsoft links:

http://msdn.microsoft.com/en-us/library/hh213234

spatial aggregates

Microsoft links:

http://msdn.microsoft.com/en-us/library/hh403400(v=sql.110).aspx

Query and manage XML data

understand XML datatypes and their schemas and interoperability with limitations and restrictions

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms189887(v=SQL.90).aspx

implement XML schemas and handling of XML data

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms176009(v=sql.110).aspx

how to handle XML data in SQL Server and when and when not to use it, including XML namespaces

Microsoft links:

http://msdn.microsoft.com/en-us/library/bb522446(v=sql.110).aspx

import and export XML

Microsoft links:

http://msdn.microsoft.com/en-us/library/aa179112(v=SQL.80).aspx

http://msdn.microsoft.com/en-us/library/aa179108(v=sql.110).aspx

XML indexing

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms191497(v=sql.110).aspx

 

3) Modify Data (24%)

Create and alter stored procedures (simple statements)

write a stored procedure to meet a given set of requirements

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms190782(v=sql.110).aspx

branching logic

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms182717(v=sql.110).aspx

create stored procedures and other programmatic objects

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms182717(v=sql.110).aspx

create stored procedures and other programmatic objects

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms187926(v=sql.110).aspx

Modify data by using INSERT, UPDATE, and DELETE statements

given a set of code with defaults, constraints, and triggers, determine the output of a set of DDL

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms174335(v=sql.110).aspx

http://msdn.microsoft.com/en-us/library/ms177523(v=sql.110).aspx

http://msdn.microsoft.com/en-us/library/ms189835(v=sql.110).aspx

use output statement

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms177564(v=sql.110).aspx

Combine datasets

difference between UNION and UNION all

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms180026(v=sql.110).aspx

modify data by using MERGE statements

Microsoft links:

http://msdn.microsoft.com/en-us/library/bb510625(v=sql.110).aspx

Work with functions

understand deterministic and non-deterministic functions

Microsoft links:

http://msdn.microsoft.com/en-us/library/aa214775(v=SQL.110).aspx

create and alter user-defined functions (UDFs)

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms186755(v=sql.110).aspx

http://msdn.microsoft.com/en-us/library/ms186967(v=sql.110).aspx

4) Troubleshoot and Optimize Queries (25%)

Optimize queries

understand statistics

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms190397(v=sql.110).aspx

DMVs

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms188754(v=sql.110).aspx

hints

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms181714(v=sql.110).aspx

statistics IO

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms184361(v=sql.110).aspx

Manage transactions

understand begin tran, commit, and rollback

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms174377(v=sql.110).aspx

http://msdn.microsoft.com/en-us/library/ms188929(v=sql.110).aspx

http://msdn.microsoft.com/en-us/library/ms190295(v=sql.110).aspx

http://msdn.microsoft.com/en-us/library/ms181299(v=sql.110).aspx

implicit vs. explicit transactions

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms187807(v=sql.110).aspx

isolation levels

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms173763.aspx

trancount

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms187967(v=sql.110).aspx

Implement error handling

implement try/catch/throw

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms175976(v=sql.110).aspx

transaction management

Microsoft links:

http://msdn.microsoft.com/en-us/library/ms174377(v=sql.110).aspx


2 Comentários on “[SQL Server] Exame 70-461–Material para Complementar os Estudos”

  1. George Almeida Melo disse:

    conteudo fantastico, não só pra quem vai se certificar, mas tambem para aqueles que vivem em busca de conhecimento. recomendo a todos.

  2. Adeilson Brito disse:

    Obrigado George. Agora é só estudar.


Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s