[SQL Server] Exame 70-461–Material para Complementar os Estudos
Publicado; 31/10/2012 Arquivado em: Exame 70-461 | Tags: 70-461, exame 70-461, exame sql server 2012 2 ComentáriosDevido 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: |
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: |
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: |
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 |
conteudo fantastico, não só pra quem vai se certificar, mas tambem para aqueles que vivem em busca de conhecimento. recomendo a todos.
Obrigado George. Agora é só estudar.