Study material for exam 70-461 Querying Microsoft SQL Server 2012 and 2014

By: Daniel Calbimonte   |   Updated: 2016-11-28   |   Comments (1)   |   Related: More > Professional Development Certifications

Problem

I would like to pass the Microsoft 70-461 exam. Can you give me links, books and recommendations for the exam?

Solution

Yes. In this tip, we will answer some typical questions, give links, books and training exams to pass the 70-461 exam.

What is the 70-461 certification exam?

This is the Exam related to SQL Server 2012 and 2014 focused on SQL Server Querying.

Is this exam about ANSI SQL?

ANSI SQL is an standard for several Databases like Oracle, DB2, SQL Server, etc. This exam is about Transact SQL or T-SQL.

What is the difference between ANSI SQL and T-SQL?

ANSI SQL is a standard for relational databases. T-SQL is an extension of ANSI SQL created for Microsoft in SQL Server. The exam contains some sections on ANSI SQL and other sections on T-SQL.

It is my first exam about SQL Server. Do you recommend taking this exam first?

Yes. If you already have some experience with other databases, this exam is a good first step for SQL Server. If you do not have any experience on other databases, another starting point would be the MTA Database Fundamentals.

Is this exam focused on SQL Server 2014?

Yes, SQL Server 2012 and 2014.

Which books would you recommend for this exam?

There are thousands of books in the market, but these ones we know and recommend:

1. The official book is the Training Kit (Exam 70-461) Querying Microsoft SQL Server 2012. It is a good book, however it will be not be enough to pass the exam. The CD with questions that comes with the book is very simple compared to the real exam.

2. Microsoft SQL Server 2012 T-SQL Fundamentals (Developer Reference). This is a good complementary book of the first one.

3. T-SQL Querying (Developer Reference) is another great book to learn T-SQL.

4. Beginning T-SQL 3rd ed. Edition. This is another option. It is similar to other books mentioned before, but it is a different author.

5. Pro T-SQL Programmer's Guide is an advanced book about T-SQL. It does not cover most of the exam chapters, but it has nice examples about XML, Triggers and other topics.

6. SQL Server Concurrency: Locking, Blocking and Row Versioning is a great book to study the locking sections in the exam.

Which courses would you recommend?

Can you recommended some courses:

• Querying Microsoft SQL Server 2012 Databases Jump Start
• T-SQL Fundamentals 2016
• Advanced T-SQL Querying, Programming and Tuning for SQL Server 2012, 2014 and 2016
• SQL Server 2012 Optimization, Performance Tuning and Troubleshooting
• T-SQL Fundamentals
• SQL Server - TSQL

Which simulation exams would you recommend?

• 70-461 Querying Microsoft SQL Server 2012 Microsoft Official Practice Test
• Practice Lab for Microsoft 70-461
• Practice Exam for Microsoft 70-461
• Microsoft Practice Exam for 70-461 MSCert: Querying Microsoft SQL Server 2012/2014

What is the passing score?

700/1000

Do you recommend any links to continue my learning?

Yes. Here are some recommended links:

Create database objects

• Create and alter tables using T-SQL syntax (simple statements)
  • Data Definition Language (DDL) Statements (Transact-SQL)
  • Tables
  • Add Columns to a Table (Database Engine)
  • Tutorial: Writing Transact-SQL Statements
  • CREATE TABLE (Transact-SQL)
  • ALTER TABLE (Transact-SQL)
  • DROP TABLE (Transact-SQL)
• Create and alter views (simple statements)
  • Views
  • ALTER VIEW (Transact-SQL)
  • CREATE VIEW (Transact-SQL)
  • DROP VIEW (Transact-SQL)
  • Create Indexed Views
• Design views
  • ADD SIGNATURE (Transact-SQL)
  • DROP SIGNATURE (Transact-SQL)
  • sys.crypt_properties (Transact-SQL)
• Create and modify constraints (simple statements)
  • Primary and Foreign Key Constraints
  • Unique Constraints and Check Constraints
  • table_constraint (Transact-SQL)
• Create and alter DML triggers
  • Create and alter DML triggers
  • CREATE TRIGGER (Transact-SQL)
  • Using INSTEAD OF triggers in SQL Server for DML operations
  • Create Nested Triggers
  • Improve SQL Server Efficiency by Switching to INSTEAD OF Triggers
  • SQL Server 2014 DML Triggers: Tips & Tricks from the Field

Work with data

• Query data by using SELECT statements
  • SELECT Clause (Transact-SQL)
  • SQL Server Ranking Functions Row_Number and Rank
  • SQL Server Synonyms Tips
  • FROM (Joins, Apply, OPENXML)
  • Compare SQL Server Datasets with INTERSECT and EXCEPT
  • Information Schema Views (Transact-SQL)
  • INFORMATION_SCHEMA.TABLES
  • COALESCE vs. ISNULL
  • Writing efficient queries
  • T-SQL Code review: How MS can support customers requests for code review/improvement
  • SQL Server T-SQL Tips
  • Advanced T-SQL Querying, Programming and Tuning for SQL Server 2012, 2014 and 2016
  • Different Options for Importing Data into SQL Server
  • SQL Server Query Performance Guidelines Tutorial
• Implement sub-queries
  • SQL Server Execution Plans
  • Use SQL Server's UNPIVOT operator to help normalize output
  • Script to create dynamic PIVOT queries in SQL Server
  • Using PIVOT and UNPIVOT
  • SQL Server CROSS APPLY and OUTER APPLY
  • WITH common_table_expression (Transact-SQL)
  • Recursive Queries using Common Table Expressions (CTE) in SQL Server
• Implement data types
  • Data Types (Transact-SQL)
  • SQL Server performance issues when using mismatched data types
  • SQL Server 2008 Date and Time Data Types
  • Auto generated SQL Server keys with the uniqueidentifier or IDENTITY
  • GUID vs INT Debate
  • Comparing SQL Server Datatypes, Size and Performance for Storing Numbers
• Implement aggregate queries
  • SQL Server 2012 Analytical Functions - Percentile_Cont and Percentile_Disc
  • SQL Server 2008 Group By Sets
  • GROUPING SETS in SQL Server 2008
  • Spatial Aggregates in SQL Server Denali
  • SQL Server 2005 and 2008 Ranking Functions Row_Number and Rank
  • Ranking Functions (Transact-SQL)
• Query and manage XML data
  • SQL Server XML Tips
  • XML Indexes (SQL Server)
  • Add Namespaces to Queries with WITH XMLNAMESPACES
  • XML Data Types Reference
  • Performance tips of using XML data in SQL Server
  • Performance Optimizations for the XML Data Type in SQL Server 2005

Modify data

• Create and alter stored procedures (simple statements)
  • CREATE PROCEDURE (Transact-SQL)
  • ALTER PROCEDURE (Transact-SQL)
  • Stored Procedures (Database Engine)
  • SQL Server Stored Procedure Tutorial
  • SQL Server Triggers Tips
  • User-Defined Functions
  • CREATE FUNCTION (Transact-SQL)
  • DML Triggers
  • SQL Server Stored Procedures Tips
• Modify data by using INSERT, UPDATE, and DELETE statements
  • INSERT (Transact-SQL)
  • UPDATE (Transact-SQL)
  • DELETE (Transact-SQL)
  • DISABLE TRIGGER (Transact-SQL)
  • Testing SQL Server Performance of Database Inserts and Updates
  • OUTPUT Clause (Transact-SQL)
  • Techniques For Improving SQL Query Performance - Indexing, Parameterization and Partitioning
  • SQL Server Optimizing Update Queries for Large Data Volumes
• Combine datasets
  • Comparing performance for the MERGE statement to SELECT, INSERT, UPDATE or DELETE
  • UNION vs. UNION ALL in SQL Server
  • UNION (Transact-SQL)
  • COALESCE vs. ISNULL
• Work with functions
  • Deterministic and Nondeterministic Functions
  • User-Defined Functions
  • CREATE FUNCTION (Transact-SQL)

Troubleshoot and optimize

• Optimize queries
  • SQL Server Execution Plans
  Graphical Query Plan Tutorial
  • Performance Tuning with SQL Server Dynamic Management Views
  • Parameterized SQL Query over Dynamic SQL Query
  • SQL Server JOIN Hints
• Manage transactions
  • SQL Server Concurrency: Locking, Blocking and Row Versioning
  • Introduction to the SQL Server Transaction Log Tutorial
• Evaluate the use of row-based operations vs. set-based operations
  • Cursors
  • SQL Server cursor performance problems
  • Performance Considerations of User-Defined Functions in SQL Server 2012
  • SQL Server : Combining multiple DML (Data Manipulation Language) actions (INSERT, DELETE, UPDATE) into single SQL Statement using MERGE in SQL Server
• Implement error handling
  • SQL Server 2005 Try and Catch Exception Handling
  • TRY...CATCH (Transact-SQL)
  • Understanding “Set based” and “Procedural” approaches in SQL
  • SQL Server: Transaction Management

Next Steps

There are several links that will be useful to you:

• Exam 70-461
• Get study resources from learners like you
• Exam 70-461 forum
• Check out the Professional Development resources on MSSQLTips.com.

About the author

Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips

Related Resources

• More Career Tips...