Advertisements

Archive

Posts Tagged ‘70-461’

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

December 1, 2015 7 comments

 
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

Passed 70-461 Exam : Querying Microsoft SQL Server 2012

November 16, 2012 266 comments

 
Yesterday (Nov 15th 2012) I went through this exam and passed it. From long back I was looking an opportunity to give at least a single SQL Server Certification Exam to check my worth.

Just a week back I scheduled a slot for this Exam (for yesterday). I was looking for some reading material for the same but I couldn’t get any. Though there is a book from Microsoft Press i.e. Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012, but it was not available at my nearest.

As I maintain this blog on SQL Server and also evangelize on SQL Server 2012 latest features from long back, so I thought to give it a try and wow I went thru in a single shot!!!

Here by this post I want to provide my readers some information and knowledge about this Exam, so that they can plan well for this and look forward to take up this exam.
 

–> Exam Highlights:

– There were total 54 Questions
– Total time to attempt the paper was 3 Hrs.
– Total Marks: 1000
– Passing Marks: 700
– The Questions in the Exam were:
1. Mostly Objective Type.
2. Some were Subjective: where you’ve to write code, like SELECT statement and/or View Definition, etc.
3. And very few were Multiple Choice.
4. A few Question were where you’ve to pick up the appropriate code snippet and arrange them in correct sequence.
 

–> The Exam is divided into 4 modules:
 

1. Create Database Objects | 24%

– CREATE/ALTER/DROP Database, [link], [video]

– CREATE/ALTER/DROP Table, [link], [video]

– ADD/DROP/Rename table’s Columns, [video]

– Data Types [video]:
– – (XML, DATETIME, SPATIAL, VARCHAR)

– Constraints [link], [video]
– – (Primary Key, Unique Key, Foreign Key, NOT NULL, Check, Default)

– Computed Columns [video]:

– Views [link]

– Indexed Views (SCHEMABINDING, COUNT_BIG(*), CLUSTERED INDEX)

– Stored Procedures, [link].

– DML Triggers (INSERTED, UPDATED, UPDATE function) vs CHECK Constraint

– TRIGGER for VIEWS (INSTEAD OF)

– UDF (Functions), [link].

– SP vs UDF, [link].
 

2. Work with Data | 27%

– New Functions in SQL Server 2012 (IFF, TRY_PARSE, CONCAT, FORMAT), [link].

– FETCH-OFFSET, [link].

– SEQUENCE, [link].

– Ranking Functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE), [link], [video].

– OVER() Window Function, [link]

– JOINS (INNER, OUTER LEFT, OUTER RIGHT, CROSS), [link].

– APPLY Operators (CROSS APPLY vs OUTER APPLY), [link].

– CTE and Sub-Queries, [link].

– PIVOT, [link].

– ROLLUP, CUBE & GROUPING SETS, [link].

– Dynamic SQL

– ANY, SOME, ALL

– CASE vs ISNULL vs COALESCE, [link].

– FOR XML RAW/AUTO/PATH [ELEMENTS], [link].

– Implementing XML Schemas and Handling of XML data
 

3. Modify Data | 24%

– Stored Procedure (with EXECUTE AS, RECOMPILE)

– MERGE Statement (TARGET, SOURCE, WHEN MATCHED, WHEN NOT MATCHED, OUTPUT), [link].

– EXCEPT vs INTERSECT
– UNION vs UNION ALL, [link].

– SCALAR vs TABLE Valued Functions.

– Use of APPLY with UDFs, [link].

– VARCHAR(MAX) and .WRITE(), [link].
 

4. Troubleshoot & Optimize | 25%

– Using Statistics

– SQL Internal JOINS (NESTED – Small, MERGE – Large Sorter, HASH – Large Unsorted), [link].

– TRANSACTIONS (BEGIN, COMMIT, ROLLBACK, XACT_ABORT, TRANCOUNT), [link].

– ISOLATION Levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT, SERIALIZABLE)

– TRY/CATCH, [link].

– RAISE vs THROW [link]

– CURSORS (Row-Based) vs SET Based Approach

– Table Hints (UPDLOCK, ROWLOCK, TABLOCK, …etc)

– Query Hints (OPTION (OPTIMIZED FOR … [UNKNOWN]))

 
For “SQL Server 2014” exam certification check here.

 
–> You can check following Study materials to prepare for this Exam:

Microsoft Link for this Certification: https://www.microsoft.com/learning/en-us/exam-70-461.aspx

Books on AMAZON.com:
Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012

Microsoft SQL Server 2012 T-SQL Fundamentals
 

–> Download “SQL Server 2014 Express” (free) version to practice T-SQL Queries:

 
Join SQL Server 2016 groups on [LinkedIn] and [Facebook] for more updates.

 
All The Best!!!