[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

---