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
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!!!
Leave a Reply to manub22 Cancel reply
Current Visitors
StatCounter …since April 2012
Leisure blog: Creek & Trails
- An error has occurred; the feed is probably down. Try again later.
SQL with Manoj 
Hi Manoj! Could you please throw some light on the difficulty level on the ‘Optimizing SQL Queries’ part of the exam 70-461?How hard are the questions for this subset of the exam? I have enough of experience working on SQL queries. But, I have nil experience in optimizing SQL queries and having difficulty preparing for the same. Thanks in advance!
Difficulty level depends upon your experience, for a novice it would be high. But as you mentioned you have fare knowledge of TSQL queries, it would be moderate for you. The Optimize module weightage is 24%, and with a little prep you can pass the exam. To score cent-percent you should have good knowledge in all modules. You just need to do lot of hands on and go through all the topics, not missing anything.
Best of luck.
~Manoj
Thanks for the quick reply, Manoj! I am trying my best to do demo practice on Optimization module too. But, I just don’t know what kind of questions are asked for this module. For example, if they ask what execution plan iterator will be used for a specific query. I am scared I wouldn’t be able to answer these kind of question correctly. I have been passing the simulated exam with above 90% given in the Training Kit of Exam 70-461…but I am not sure if these simulated exam are at par with real exam.
Anyways, Thanks!
Hi manoj
can you send me some materials to me on satpute.kailas@gmail.com for 70-641 course.
Thanks.
Any thoughts about “code non regression” ? This is listed in the 70-461 subject list
@Phil, regarding “Code Non Regression” you can check my comments on following MSDN TSQL forum post: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/c5b68594-346d-4ff9-9132-d27afe9867c8/70461-querying-sql-2012?forum=transactsql
Thanks,
Manoj
Hi Manoj,
Please let me know the procedure to apply for 70-461 exam second shot.
Nice blog Manoj,
Keep the good work……..
In section “–> Troubleshoot & Optimize | 25%”, “- Query Hints (OPTION (OPTIMIZED FOR … [UNKNOWN]))” should be “- Query Hints (OPTION (OPTIMIZE FOR … [UNKNOWN]))”
there is no “D” in OPTIMIZE clause 🙂
Hello guys, I wish to ask if the multiple choice questions found in the Training Kit can really help one pass this exam.
They are not the same questions, but I think that most of them will be helpful.
Hi Manoj
First up thanks for such a helpful archive.
I am new to SQL server. I want to change my career path to sql server dba hopefully.
As you know sql server 2014 is already out. I have materials from sql server 2012. Are they sufficient to cover the exam? Because query part should be the same for both right?
Regards
Hi @Muktadir,
Most of the topics would be same in 2012 & 2014, but I would advice you to go thru the 2014 material only.
Thanks manoj, I’ve started my preparation with this info.Will update as soon as i take up the exam
best of luck @Ramraj
Hello guys good day. In preparation for this exams, i have gone through the training kit text book and and Training kit exam prep. i wish to ask anybody who has gone in for this exam if this material can be enough preparation for the exam?
I also wish to know the differences and similarities between the exams and the Training kit exam prep. Thanks in advance for your responce
@Daryl, yes the material available online and Training kit is enough and sufficient for this exam.
Hi Manoj. My Name is Mesfin. I am new to SQL SERVER & I want to take exam 70-461 but I am worried from where I can start preparing. How many days it will take for the new learners to pass the exam? I am quick learner. Please I need advice.
I recently passed 70-461 exam with 737 score. I am quite happy with the score considering the fact that I have just few months of experience with SQL Server. I just studied the training kit for about 4-5 months and practiced by creating a database on Evaluation copy of SQL Server 2012. The exam was quite hard and different with what given in Training kit simulated exam. You need to have a thorough understanding of ranking functions along with grouping function and for XML. A good link for XML is mentioned below:
https://www.simple-talk.com/sql/learn-sql-server/using-the-for-xml-clause-to-return-query-results-as-xml/
I had just referred this link for XML queries and its quite informative. All the best! 🙂
Hello Pooman, congrats. Thanks very much for the feedback and the link. it was very helpful to me. I am currently preparing for this exam to the take it in about 1 week time.
Thanks for your blog. I passed 70-461 exam today. Your articles were very helpful in passing the exam.
Hello Padma, congratulations. please I am actually prepared to take this exam but I do not knoew how I can schedule for it. pleas can someone help me out with the possibilities to schedule and take this exams. I will be very greatfull.
Hello Manoj,
Your blog is too good, it is a guide for all those who are interested in giving Certification.
Am planning to give 70-461 exam but am bit confused whether we need to give 2012 or 2014 exam? Please Manoj let me know what exam will be good 2012 or 2014
Thanks a lot for the blog !!
Hi Arpita, thanks for your comments and that you liked my blog.
Exam code 70-461 is for SQL Server 2012 only, and for 2014 Microsoft has not yet released the exams, and there is no date communicated yet. Right now you can appear for 2012, and later on you can simply upgrade to 2014.
As soon as I’ll know about this I’ll post it on this blog, stay tuned.
Thanks,
Manoj
actually really surprised ppl got questions for writing their own query. Passed mine Jan 2014 and was informed that in the new edition of the exam there were minimal query-composing questions. Anyway, good job for ppl who got those questions and passed but felt extremely sorry for my friend to whom I told about the no-query-writing-question policy which led to him failing the exam because he got about 10 of those questions………………..
Hey Manoj, thanks for lot of informative links, it really gave me a kick start. Just wanted to check, i want to practice SQL queries at home. How can i do that? Which DB to download and install.
Thanks in advance!
Thanks @Simar, that you like this post 🙂
You can download SQL Server 2012/2014 Express from official Microsoft SQL Server site, link: http://www.microsoft.com/en-in/download/details.aspx?id=29062.
And to practice SQL Queries you can download AdventureWorks2012 Database from codeplex, link: http://msftdbprodsamples.codeplex.com/releases/view/55330
Best of luck for your exams!
And let me know when you pass.
~Manoj
Awesome..Thanks a lot for the quick response!
Hello Manoj
Thanks for Sharing all the info about certification.
I am MSBI Developer.
Suppose if I want to pursue certification for BI 70-463 (MCSE Level) then shall i need to clear out first 70-461 (MCSA) or shall i directly attempt 70-463 which is more relevant to me.
Please advice
Thanks
@GOURAV, no you can directly take 70-463 exam, no need to take 461/462
~Manoj
Hi Manoj,
I passed my 70-561 yesterday with great passing score. Yours blog was the first thing that I saw on 70-461. It have put may things in simplified form which made me belive that I can pass this exam easilly.
I read the book training kit for 70-461 twice, I practised a lot to write queries and read many blogs on internet on various topics to gain the knowledge.
It took be about 45 days (daily 2-3 hours) to prepare for this exam.
Thanks again
Sachin
sachingoyal22@hotmail.com
Hi Sachin,
Congratulations on passing the exam, really appreciate the hard you’d put in studying and practicing for the exams. I really liked that my blog post on 70-461 was helpful for you 🙂
Good luck for next exams in near future!!!
~Manoj
i m working on sql server dba and i want to do certification but i m confusion on code like 70-462 etc and 70-64 can u expln wts dis And which one i have to take
Hi Rafi,
461 is for TSQL, i.e. Programming with SQL
462 is for DBA i.e. you are working on
463 is for DW/BI, which involves DatawareHouse, Marts, Cube & SSIS
Let me know if you need more information on this.
~Manoj
I read all ur comment block it’s very helpful, thx for important information.
I did my mcsd course in 2010 but not able to appear in certification exam, I worked with sql-server 2008 and VB6 (developed software for automobile n payroll) , now I want to go for certification, kindly guide me .
Hi @Ali, thanks for your comments and appreciate that you like this post & comments were helpful to you.
Now as 2012 Certification exams are already available since last 2 yrs, you can take them, starting with 70-461, after this you can take 70-462/3 as per your preference, though there is no sequence, you can give these 3 exams in any order.
Let me know if you want any help.
Thanks,
Manoj
Hi , i want to give exam 70461 , i have read training kit exam 70-461. I have exp of 4 years in sql server.Is it necessary to go through practice test like measureup or any other? As most of people are saying training kit test are easier then actual exam.
Hi Megha,
The training kit would be enough for you to take the exam, but you should also do lot of hands on with practice on TSQL queries on all topics. Then you would not need any other material for prep.
Best of luck for the exam.
~Manoj
Hello Manoj –
Thank you for all your advise and direction you are blessing us with on this site. I have a question myself.
I am a bachelors degree holder in Computer Software Engineering. My work background is Desktop Support (IT). I want to upgrade my skills – So, I picked up MS SQL 2012. I am currently studying for the 70-461 exam ( for about 3 weeks now). And I look to write the exam by November 1.
I am doing a lot of hands-on practicals and labs – as you advised. But, HOW do I dive into the SQL DBA Job market Please? Any advise for me/us please? Any resources?
Thank you for all you do Manoj ~
i passed 70-461 exam..but i dont know its an mcp or mcsa..can i use any logos in my resume??
Hi Hari, first of all congratulations on passing the exam.
As you’ve passed first exam, so you earn the MCP title. After passing all 3 exams (70-461/2/3) you will earn the MCSA title.
You might have got an email (or will) from Microsoft, with the MSDN link where you can view your Certification details, also download your certificate,and can use the logo that I’ve used on my blog home page.
~Manoj
Hello Manoj,
I am thinking to give 70-461. While going through Microsoft Learning, I got to know that it has a long list. Also some topics have huge subtopics. Do we really need in depth knowledge like exact query to view the definition of table (or) how to create fileTable with all details is needed? or Just knowledge how it works is enough?
Thanks
Pavan
Pavan, Sorry for replying so late.
Well, I think you should ask this question to yourself, weather you are passionate about SQL or not. Even after going indepth there is lot to know in SQL Server space. So do you want to limit yourself to this exam?
But yes, if you go through all the topics covered in the kit-book and practice them you will pass it.
All the best!!!
~Manoj
Could you please let me know.. after writting exam, How can we find marks we have scored in the exam? Is there any site for this available or they will tell the marks there it self
Surendar,
You will know the result immediately in the prometric center where you will give exam.
~Manoj
I followed your study guide a passed the 461 exam today with a score of 828. Thank you for sharing your experience and breaking down the topics into such a succinct and helpful manner.
congratulations @emersonerd, for passing with flying colors 🙂
Hello Sir,
1st of all Thank you .. Your blog is one of those blogs which really helped me a lot while studying SQl Server! The posts were very illustrative . Thank you very much . Infact I got to know about the Microsoft 70-461 exam through this blog itself .
I just gave the exam and passed it with 914 score.. They gave me my test score card. I wanna ask is that it ? I mean is there any more certificate which I am gonna get via email ? Please Reply soon …
Congratulations @Lyns, on passing exam with v-good scores. You will get an email and by that you should be able to login into the site and download your certificate PDF.
~Manoj
Sir,
I have still not got any certificate from Microsoft.. I am starting to worry now ..
Hi manoj,
can you send me some materials to me on p.agrawal@gmail.com for 70-641 course. Thanks!
sorry correct Email Id is p.agrawal@hotmail.com
Hi
I have recently passed the 98-364 exam database fundamentals and i am considering taking the 70-461 exam. Are their any good free study materials that will help me revise. I am currently working as a Junior DBA and any help would be appreciated
I’ve passed the exam today, even though it was pretty tricky. Thank you Mr Pandey, you’ve been of great help!
Did you have any questions where you had to write queries?
yes Apurva, there may or may not be questions where you will need to write SQL queries.
Thanks for your reply. I was wondering if Amir had those because he took the exam recently. Some people said they didn’t get any. So was checking.
Not everybody gets same set of questions, its all random, and you may get 50 to 70 questions. And writing SQL queries is also confirmed, you may get or not.
~Manoj
Hello, I’m sorry I haven’t replied earlier – I didn’t get the notification that someone replied to my post. If it’s not too late already, I just wanna let you know that I indeed had several questions including typing queries. But don’t worry, there was a spellcheck option and tasks weren’t too hard. Everything you need to know is listed in this blog.
Good luck!
thanks Amir for your comments & providing feedback to the group!!!
No problem Amir. I had already passed on March 10th and I also had query typing questions,
Yeah, I noticed that too after i replied. Congratulations and sorry once more I wasn’t too helpful. Just a note to everyone else taking this exam soon: Make sure you know EVERYTHING being discussed in this article, watch this video https://youtu.be/Kp3dS-T16x4?list=PLahhVEj9XNTct1VslZ_LhNSI8F5_8Ikve and I’m sure you’ll pass. At first this video doesn’t look too helpful, however it does have a couple of great tips for this exam (I know it helped me get 3 questions on the exam right).
Good luck to everyone!
Thanks. Not sure if anyone asked this. I could not find above. Can you review questions at the end and go back and change the answers? And is time provided plenty to finish exam? Do you get time for reviewing?
yes @Apurva
I passed the test on March 10th! Thank you for your help!
My name is Tonia-Rex chukwuyenum i wrote Querry microsoft sql server 2012 (70-461) exam on the 18th of November 2014. my Reg no. is z74lon5198, candidate no. cd1176381,score 700 and site number nk49k. please i will be happy if you could provide me a link to print my certificate.
thanks as i look forward for your reply.
Tonia-Rex Chukwuyenum
Can you try logging here with your live or hotmail ID: https://www.microsoft.com/learning/en-us/dashboard.aspx
For any microsoft certification exam dumps kindly contact xyz@gmail.com
We do not encourage DUMPS here. Please do not try to SPAM this bog post by such comments.
Thank you!! This blog has been super helpful and informative in preparing for the exam and tips & tricks for work! I still have 3 weeks to study, I feel confident following your guide. 😀
thanks @Icacmba… best of luck for the exam!!!
and do let us know your experience 🙂
Took the exam today and passed!! 😀
Next is 70-462
@Icacmba, congrats, and best of luck for 70-462
Plz, share your experience here.
Congratulations!
Reblogged this on forkingandglory.
thanks!
Hello! Thank you for your very useful blog! last year in December I tried to pass, but got only 350 of 700 passing score…now I am preparing with training kit!
Manoj, I need an advice, in what language I must take examination? does it matter if I take it in English or in Russian? whether is it important for certification? please, tell me, if don’t then I will take it in Russian? =)
@Lamia, I think you should give exam in the language you are comfortable with.
Like, my mother tongue is Hindi (Indian) but this exam is not in this language. And my second language is English and I was quite comfortable in giving the exam in english, and studying for the same.
thank you =) but that’s a pity =( liable languages for this examination is only
“Languages: English, Chinese (Simplified), French, German, Japanese, Portuguese (Brazil)”
So, I think english is the only option for you.
But I don’t think this should be any problem to you, its simple english.
Reblogged this on .
thanks Sachin!
Hi,
Below is the question from training kit , i think correct answer is D.But still confused,is book’s ans is correct?
3. Which clause of the MERGE statement isn’t standard?
A. The WHEN MATCHED clause
B. The WHEN NOT MATCHED clause
C. The WHEN NOT MATCHED BY SOURCE clause
D. All MERGE clauses are standard.
3. Correct Answer: C
A. Incorrect: The WHEN MATCHED clause is standard.
B. Incorrect: The WHEN NOT MATCHED clause is standard.
C. Correct: The WHEN NOT MATCHED BY SOURCE clause isn’t standard.
D. Incorrect: The WHEN NOT MATCHED BY SOURCE clause isn’t standard.
@megha, the correct answer is C only. Only A & B clauses are standards as per ANSI, C clause is an extension to these 2 clauses.
As per MS Book (T-SQL Querying, SQL Server 2014):
The WHEN MATCHED and WHEN NOT MATCHED clauses are standard. But that’s where the standard stops. Microsoft reckoned that there’s a case that the standard doesn’t deal with but which might be important for you to deal with—when a target row is not matched by a source row. Such is the case in our sample data, with customers 1 and 4. So Microsoft introduced an extension to the standard in the form of a clause called WHEN NOT MATCHED BY SOURCE.
ok..thanks a lot for your ans with explanation..
Hi ,
The information is so helpful! Thank you for writing.
I’m a computer science student. Still I have very minimum knowledge on SQL because of bad profs I’ve got in my college. But I’m interested in learning SQL.
How much time will it take for a beginner like me to prepare for the exam? Will it take more than a month considering I’m a slow learner?
Is the exam standard very high?
Please help!
Thank you 🙂
Hi @Esha,
Thanks for the appreciation.
As you are interested in learning SQL, preparing for certification is a very good idea. The exam standard is not very high, but for a newcomer it would take around a month to get familiar and prepare for this exam. Check the books I’ve mentioned and you can buy the Training Kit, its worth purchasing. Do check the links I’ve provided in the post for various topics in all modules. To practice SQL queries you can download the free version of SQL Server Express from Microsoft site.
Let me know whenever you need any help.
Thanks,
Manoj
Thank you so much for your quick response.
Yeah I’ve downloaded the PDF of the book you have mentioned.
Actually I’ll be leaving to US for Masters by July. I want to finish this exam by then. All I want to know is , how much preparation time do we need being a begginer ? Will 1 month suffice?
Thanks
Esha
Hi Manoj
I have completed 70- 462 exam. What title i can get for this. shall i put it as MCSA?
Hi @Janani,
Congratulations on passing the exam, you have earned the MCP title by passing one exam.
To earn MCSA you have to pass the other two exams as well (461 & 463). Please check this blog post: https://sqlwithmanoj.com/2013/11/06/sql-server-2012-certification-path/
Thanks,
Manoj
Hi,
Is it enough to prepare Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012 this book or do I need to go through any other study material before appearing for this exam.
Thanks.
Yes, the Training kit would be enough… but you should also have good hands on Queries and practicals covered in the book/exam. There is no use of cramming the book just to pass the exam.
Do leave a comment/reply when you pass the exam.
Best of luck!
Hello Sir, now i am studying training kit of 70-461 exam and practicing according to that . is it enough for exam or do i study something else .. ?? and what about future scope of this exam in career ? thnx in advance
Yes the Training kit would be sufficient for passing 70-461 exam, considering you have also practiced with SQL queries. Otherwise just cramming the book won’t help.
This exam could be a good start for your career, but don’t limit to the paper only, explore more and learn other things in SQL Server as well.
Ok sir, thank you for your kind of information .
hi mate, could u please give me a link where i can get sample questions.
exam 70-461
Is the test simular to the Microsoft Press Practice Exam? It seems too easy.
It would be sufficient, but don’t limit yourself to just passing the exam. Explore few other resources, like books, msdn BoL, channel9 videos.
Do, let me know if you need any help in learning materials.
Hi Manoj,
I am a application developer in a MNC with 2 yrs experience. I had worked in SQL. I wanted to ask that how should be the approach of studying for this exam. Book first and then dumps. or how it should be. How much focus on book and dumps. I ahve 2 months time to study. I have bought the Training Kit Book recommended on MSDN. But I could not find any dumps. ..Can you provide me the dumps for this Exam. 70-461..Hope to get your reply. Thanks in advance..
Forget Dumps, prepare for the exam if you really want to learn SQL & pass the exam. Go through various online study material and Training Kit book. Practice SQL queries and SQL Server fundamentals.
LMK if you need any help.
~Manoj