1. Exam 70-461 – Querying the Microsoft SQL Server 2012 : Microsoft Certified in Business Intelligence

Posted on by

The first exam tests your ability to query a Microsoft SQL Server 2012, which means it will mainly test your SQL knowledge.

The following are the skills measured:

  • Create database objects (24%)
    • Tables – ALTER; DROP; ALTER COLUMN; CREATE
    • Views  – CREATE, ALTER, DROP
    • Procedures and Functions
    • Contraints – define constraints; unique constraints; default constraints; primary and foreign keys
    • DML Triggers – nested triggers; types of triggers; update functions; handle multiple rows in a session; performance implications of triggers
  • Work with data (27%)
    • SELECT
    • Sub Queries – query plans; pivot and unpivot; apply operator; cte statement; with statement
    • Data Types – Using data types, impact of GUID (newid, newsequentialid) on database performance
    • Aggregate queries – New analytic functions; grouping sets; spatial aggregates; apply ranking functions
    • Query and manage XML data
  • Modify data (24%)
    • Create and alter stored procedures
    • Modify data by using INSERT, UPDATE, and DELETE statements
    • UNION and UNION ALL
    • Work with functions – Understand deterministic, non-deterministic functions; scalar and table values; apply built-in scalar functions; create and alter user-defined functions (UDFs)
  • Troubleshoot and Optimize
    • Optimize queries – Understand statistics; read query plans; plan guides; DMVs; hints; statistics IO; dynamic vs. parameterized queries; describe the different join types (HASH, MERGE, LOOP) and describe the scenarios they would be used in
    • Manage Transactions – Mark a transaction; understand begin tran, commit, and rollback; implicit vs explicit transactions; isolation levels; scope and type of locks; trancount
    • Evaluate the use of row-based operations vs. set-based operations – When to use cursors; impact of scalar UDFs; combine multiple DML operations
    • Implement error handling – Implement try/catch/throw; use set based rather than row based logic; transaction management

Number of Questions: 45-55
Type of Questions: Multiple Choice (May have scernario questions)
Passing Score: 700 (This does not mean 70%.  Each question is given a score.)
Time Limit: 120 minutes

Back to main menu