Advertisements
Home > SQL Server 2014 > Exam 70-461, SQL 2014 update – Querying Microsoft SQL Server 2014

Exam 70-461, SQL 2014 update – Querying Microsoft SQL Server 2014


 
In one of my previous post I talked about [Exam 70-461 for SQL Server 2012], and I really got good and big response, with ~250 comments. As SQL Server 2014 got released most of the folks asked me about the certification update.

So here in this post I’ll talk about the same exam 70-461 which is now updated for SQL Server 2014, and Microsoft has not changed the exam code for this version of SQL Server.

 
This updated certification exam is still based on SQL Server 2012 with new features of SQL Server 2014. Previously with SQL 2012 the exam was divided into 4 modules, please check my previous post, link above. Here with SQL 2014 update the exam is reshaped into 20 modules listed below:
 

1. Introduction to Microsoft SQL Server 2014
– SQL Server architecture | MSDN
– Editions | MSDN
– SQL Server Management Studio | MSDN

2. Introduction to T-SQL Querying
– T-SQL intro | MSDN
– Set based queries
– Predicate Logic | MSDN
– SELECT statement Logical Ordering | MSDN

3. Writing SELECT Queries
– Simple SELECT queries | MSDN
– Find & eliminate duplicates, and DISTINCT keyword
– Working with Columns
– CASE expressions | MSDN

4. Querying Multiple Tables
– SQL JOINs (INNER, OUTER, CROSS & Self) | MSDN | TechNet

5. Sorting and Filtering Data
– Sorting data with ORDER BY clause | MSDN
– Filtering data with WHERE clause MSDN
– Filtering data with TOP keyword | MSDN
– Using TOP-OFFSET clause | MSDN | TechNet
– Working with NULL/unknown values

6. Working with SQL Server 2014 Data Types
– New SQL 2014 datatypes (Character, Date, Time) | MSDN
– Write Queries using different Datatypes | MSDN

7. Using DML to Modify Data
– Inserting Data | MSDN
– Modifying Data | MSDN
– Deleting Data | MSDN

8. Using Built-In Functions
– SQL 2014 new functions (Conversion, Logical)

9. Grouping and Aggregating Data
– Using Aggregate functions | MSDN
– Using GROUP BY clause | MSDN
– Using HAVING clause | MSDN

10. Using Sub-Queries
– Self-Contained Subqueries | TechNet
– Correlated Subqueries | TechNet
– Using EXISTS() predicate with Subqueries | TechNet

11. Using Table Expressions
– Using Views | MSDN
– Using Inline Table Values functions | MSDN | TechNet
– Using Derived Tables
– Using CTEs (Common Table Expressions) | MSDN | TechNet

12. Using Set Operators
– The UNION, UNION ALL Operator | MSDN
– EXCEPT vs INTERSECT | MSDN
– Using APPLY (CROSS & OUTER) | TechNet

13. Using Window Ranking, Offset, and Aggregate Functions
– Window functions with OVER() clause | MSDN
– More Window functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE) | MSDN

14. Pivoting and Grouping Sets
– Writing queries with PIVOT and UNPIVOT | MSDN | TechNet
– Using Grouping Sets | MSDN | TechNet

15. Querying data with Stored Procedures
– Creating Stored Procedures (SP) | MSDN
– Using Parameters with SPs | MSDN
– Working with Dynamic SQL | MSDN
   – EXECUTE | MSDN
   – sp_ExecuteSQL | MSDN

16. Programming with T-SQL
– Working with Variables, Batches | MSDN | TechNet
– Using IF conditions | MSDN
– WHILE loops | MSDN

17. Implementing Error Handling
– Using TRY/CATCH block | MSDN
– Working with Error Handling | MSDN
– Returning error information
– Raising user-defined errors and passing system errors | MSDN

18. Implementing Transactions
– Using Transactions | MSDN
– Use of “SET XACT_ABORT” with Transactions | MSDN | MSDN-2
– Effects of Isolation Levels on Transactions | MSDN

19. Improving Query Performance
– Factors affecting Query Performance | MSDN
– Checking Execution plan
– Optimize SQL queries, Query tuning | MSDN
– Optimize SQL Indexes, Index tuning | MSDN

20. Querying SQL Server Metadata
– Querying System Catalog Views | MSDN
– Querying System Catalog Functions | MSDN
– Using System Stored Procedures | MSDN
– Using Dynamic Management Objects (DMVs, DMFs) | MSDN

 
–> Check SQL Server 2014 videos here:

 
I’ll be updating the above post going forward and as I post something related to SQL Server 2012 & 2014.

Please provide your comments if you want me to talk about any of the above items, thanks !


Advertisements
  1. scp1906
    March 23, 2017 at 3:50 pm

    Hey Manoj,
    I’ve already read all the book and seen the videos in Microsoft Virtual Studio Academy.
    How do you think I should pratice from now on to get my preparation right to the exame? I have exame next week.
    Thanks in advance.

  2. Akira
    March 22, 2017 at 10:26 am

    Hi, I have registered for 70-461 certification & it says ‘461 – Querying Microsoft SQL Server 2012 – English (ENU)’.

    What does this mean? Will this exam include features of SQL 2014 with these 20 modules or just the 4 modules pattern you previously mentioned?

    Would appreciate your quick reply.

  3. Patt
    January 17, 2017 at 2:52 am

    Dear Manoj,

    Has the exam 70-461 been split into 2 version (2012 and 2014) or does it get updated to include version 2014 (essentially phased out the old 2012 exams)?

    If it’s the latter, would it still be safe to focus on studying with the Study kit on your original link? Or should I be focusing on these 20 modules instead?

    Thank you,

    • January 17, 2017 at 2:43 pm

      The exam is only a single unit, upgraded to SQL 2014. So, you must have understanding of SQL 2014 new features on top of SQL 2012.

      • Patt
        January 17, 2017 at 7:41 pm

        Would you by any chance have a list of the new features that come with SQL 2014 we should focus on?

        Looks like most of the videos and learning materials out there are made before SQL 2014 was released. Will we miss out a lot if we just focus on those?

        Thanks again,

      • January 17, 2017 at 8:45 pm

        Yes @Patt, in SQL 2014 the new T-SQL feature launched was In-memory (or hekaton) tables, and enhancements to Column Stored Indexes. You can check my following page to know about these : https://sqlwithmanoj.com/hekaton-sql-2014/

  1. December 1, 2016 at 9:48 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: