Archive
Passed 70-461 Exam : Querying Microsoft SQL Server 2012
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!!!
SQL Tags
M | T | W | T | F | S | S |
---|---|---|---|---|---|---|
« Nov | ||||||
1 | ||||||
2 | 3 | 4 | 5 | 6 | 7 | 8 |
9 | 10 | 11 | 12 | 13 | 14 | 15 |
16 | 17 | 18 | 19 | 20 | 21 | 22 |
23 | 24 | 25 | 26 | 27 | 28 | 29 |
30 | 31 |
Categories
- Analysis Services (1)
- Big Data (5)
- DBA Stuff (33)
- Hadoop (5)
- Interview Q (8)
- Microsoft Azure (24)
- MS BI (8)
- DW and BI (1)
- Integration Services (1)
- Power BI (4)
- Reporting Services (1)
- Tabular Model (1)
- Others (194)
- Blockchain (1)
- Certifications (9)
- Cloud Computing (2)
- Cpp (32)
- Cpp Graphics (21)
- Excel (10)
- Informatica (5)
- Java (56)
- Linux (8)
- Microsoft (14)
- MS.net (2)
- Oracle (1)
- Powershell (3)
- Python (1)
- Reviews (5)
- Security (1)
- SQL Server (7)
- SQLwithManoj (11)
- Uncategorized (6)
- VBA Macro (1)
- Visual Studio (1)
- Windows (6)
- SQL Server Conferences (1)
- SQL Server Internals (83)
- Datatypes (5)
- DB Concepts (28)
- Indexes (10)
- JOINS (4)
- ML Python (2)
- SQL DB Engine (7)
- SQL Server Migration (1)
- SQL Server Upgrade (1)
- Stored Procedures (11)
- tempdb (3)
- XML (16)
- SQL Server Questions (41)
- SQL Server Versions (135)
- SQL Azure (4)
- SQL Server 2005 (7)
- SQL Server 2008 (5)
- SQL Server 2012 (33)
- SQL Server 2014 (18)
- SQL Server 2016 (49)
- SQL Server 2017 (20)
- SQL Server 2019 (2)
- SQL Server Express (1)
- T SQL (180)
- Differences (31)
- JSON (6)
- Misconception (7)
- Optimization Performance (13)
- SQL Basics (15)
- SQL Built-in Functions (6)
- SQL Errors (42)
- SQL Tips (67)
- SQL Trivia (9)
Archives
Top Posts
- SQL Error - The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE
- Using IDENTITY function with SELECT statement in SQL Server
- Download & Install SQL Server Management Studio (SSMS) 2016 (decoupled from SQL Server engine setup)
- SQL DBA - Windows could not start the SQL Server... refer to service-specific error code 17051 - SQL Server Evaluation period has expired
- SQL Error - Windows could not start the SQL Server (MSSQLSERVER) on Local Computer (moved Master DB)
- SQL Server 2016 RTM full and final version available - Download it now !!!
- Querying Excel 2010 from SQL Server in 64-bit environment
- Recursive CTE error - The maximum recursion 100 has been exhausted before statement completion
- Reading JSON string with Nested array of elements | SQL Server 2016 - Part 3
- Getting started with SQL Server 2014 | Download and Install Free (Express) or Full version
Blog Stats, since Aug 2010
- 4,141,799 hits
StatCounter …since April 2012

Leisure blog: Creek & Trails
- YouTube – Your Google Ads account was cancelled due to no spend
- YouTube latest update on its YPP (YouTube Partner Program) which may affect your channel
- Starting your own blog !!!
- How to file ITR (Income Tax Return) online AY 2017-18 (for simple salaried)
- Scam – Become a kin/hier and earn a fortune – via LinkedIn and Email
Disclaimer
This is my personal blog site.
The opinions expressed here represent my own and not those of my employer. For accuracy and official reference refer to MS Books On Line and/or MSDN/TechNet.
My employer do not endorse any tools, applications, books, or concepts mentioned on the blog. I have documented my personal experience on this blog.