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 Apurva 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 
I have been working on sql server for more than 2.5 years and I am considering giving this exam. I have already got the book T-Sql fundamentals by iztik ben. I am also planning on buy 70-461 exam kit. what more do you think will be required to pass this exam ?.
@Sumant, as I’ve mentioned in my responses below, the kit-book will give you a fair idea of what will be asked in the exam. But you will have to practice and do a hands on with SQL queries on all topics to secure good marks and understand SQL Server programming.
Check my responses below, and good luck for the exams.
~manoj
Hello Manoj Sir,
Yesterday(26 july 2015) i have also Passed this exam.i have 10 month experience in Querying Microsoft SQL Server.now next step is 70-462 but i don’t have much idea about administrator can you please guide me how to prepare for this.should i take this exam asap or wait and gain some experience with it.
For 70-462 exam prepare well before giving the exam, as it would be very tricky as it will be scenario based on DBA activities.
In the introduction page of the Training Kit it only few lesson of some specific chapters are mentioned.
do i need to go the whole whole or only those lesson of respective chapters ?Please advise. Thanks.
The intro page is the minimal requirement to pass the exam. If you just want to pass the exam then it would be fine. But if you want to gain knowledge, then don’t just limit yourself to the index, a specific book. There are lot of books around, lot of SQL blogs, lot of online T-SQL forums, etc. Just explore them, prepare.
Best of luck!
Ok .. I failed in my exam today like 2 hours ago… feeling bad but I believe I was not prepared.
Some points to be noted.
A) There was a small survey before the exam with questions like experience with SQL Server, proficiency in each section (very high, high, moderate, low) I chose moderate in all (just want to know if this affects the level of questions in exams).
B) I bought the Training kit and practiced the questions in the Exam Prep. There were 200 questions, answered almost 70-80 then I had to stop as they were more or less same. This practice test was very simple and not a single question in the exam was even slightly close to practice set.
C) Questions were very long and took time to read and understand the requirements. there were some words which i never came across during my prep.
D) Not sure how the marking is done, for example in case of writing the code for a question, i wrote 2 requirements out of 4 and checked syntax which was correct, do i get 50% marks ?
I am a computer science graduate working in finance field, I know I can do it with more focus and practice.
Can someone suggest me good study material , videos, or any website where i can register for tutorials.
I am appearing again in the third week of September.
HI Amir,
I sadly failed my first attempt of this exam on Monday. I do not believe the survey before the exam has any impact on the difficulty of questions that come up.
It would be interesting to see which type of questions are worth more points than others. I purchased the trancender questions before hand to prepare, a big mistake. Questions were totally different and did not prepare me very well.
I only got 40% which is not great, i need to go back to basics and relearn!
Sorry @Amir for getting back to you so late.
Like my answer to @Tom, I would also suggest you to not to concentrate on Training kits and dumps. Rather get hold of a good book like the Training Kit mentioned above or “SQL Server 2012 Internals” book. Do as much hands on SQL queries as you can.
Best of luck for your second shot !!!
~Manoj
Can anyone recommend a good study source for this exam? i purchased Trancender questions (105 in total), but did not find these very useful. Some subject matter appeared in exam, but no similar questions unfortunately.
Thanks
Tom
Hi @Tom, thanks for your comments.
There is no shortcut to success and passing this exam. I would say do not rely on Question bank and Training Kits for passing this exam. Rather spend sometime like a month and target each module for every week.
You just need to go through the Training Kit book I’ve mentioned above in my post. And have to do lot of hands on SQL queries.
Good luck and Thanks.
~Manoj
for a beginner how much time do i need to practice for 70-461 exam?
Depends upon how much time you can dedicate and how fast you can grasp/absorb things. Typically if you are working (job) and not familiar with SQL then daily by giving 3-4 hrs you can prepare for this exam in ~1-2 months, otherwise it could take a week or two by dedicating 7-8 hrs.
I have cleared 70-461 exam 2 days ago. What should I do ahead? 70-462 and 463 are the only options? Along with SQL certifications, I also want to gain developer certifications. What should I do? Prepare for 70-462 or some other basic level developer certification?
Hi..
Hope you are doing good…
I would like to do a microsoft sql DBA certification. could you please guide me.
How to register and where can i get the study material and cost of the examination etc..
Thanks,
Shabeer
Hello Shabeer
You can register at any Microsoft authorized testing center near you. NIIT is one of them.
You can find exam related details at https://www.microsoft.com/en-in/learning/exam-list.aspx
Just go to the link. Select the name of the exam which you want to give. You will get complete details of that exam including cost, syllabus and study resources.
There are several online learning platforms where you can prepare. There are a lot of helpful preparatory videos on Youtube. Besides, Microsoft Virtual Academy is a free learning resource provided by microsoft. Also, exam specific study guides are available. You can order them online.
All the best.
Happy Learning.
Hi @Shabeer,
You need to take 70-462 exam for DBA, please check this link: https://sqlwithmanoj.com/2015/05/01/preparing-for-70-462-exam-administering-microsoft-sql-server-2012-databases-certification/
Check @jainishrshah comments for more info.
And thanks
happy reading!
Thanks,
Manoj
I passed the test on December 10th.
I got 816 out of 1000. passing mark is 700. 43 questions 15 query writing, MCQ 20 , matching 5 , multiple check-box 3 may be.
Congrats @Mohammad. and best of luck for the next ones.
I passed 70-461 exam on December 10th.
I got 816 out of 1000. passing mark is 700. 43 questions 15 query writing, MCQ 20 , matching 5 , multiple check-box 3 may be.
Hi mohammad, do you have dumps or studying material i am writing next week email:davidmogotsi0@gmail.com
Dear @David, thanks for your comments. But I do not support going through dumps here.
For study material check the books and resources mentioned above.
Thanks,
Manoj
UPDATE on SQL 2014:
~manoj
Thanks for the update. Found your blog and appreciate the information and your thought process in regards to Dumps. I studied the dumps and failed. They do not prepare you for the exam.
thanks @Ru… appreciate that you liked this blog post. And prepared on your own for the exam. Best of luck!
~manoj
Passed…! got 833. I got 44 questions
Thanks Manoj for your guidance.
Hi
I am working in MNC from last 1.5 years as DBA. As part of my 2016 goal requirement and promotion I need to get the certification for 2012 server.
Can you please advice me do I need to give 70.461,462,463 all the three exams or there is specific exams to be given ? Also can you please provide me the links to prepare these exams.
Please help me. Waiting for you reply
Have passed 70-461 an hour ago with score 800. 43 questions and 2 hours of time. Whole spectrum of tasks and topics. Nothing extra hard, but few complex questions were related to cursors which aren
t my favorite topic. So before taking the exam ensure you have covered syntax and understand all main features of the topics mentioned on the official Microsoft page, you must be sure all of them will be asked on exam (except spatial types in my case).t found any question which wasn`t covered with the Training Kit.Regarding preparation, I have worked with Training Kit book, Nugget video and read various blog regarding SQL, job was related partially with SQL so active phase of the preparation took 1 month. I haven
Hello everybody. First of all, thank you Manoj, for this very usefull topic who helped me, and a lot another people, to pass 70-461.
I took exam yesterday, passed with 700 points(walking on the deadline :)), 49questions, 2hours of time.
I had 10 questions to write a code, they were hard-medium. Generally whole exam was, I wouldnt say hard, but very close to that. A lot of question to think about, and to realize what is actually needed for correct answer. I studied from Training kit who covers everything but the exercises from that kit are much easier than on exam so be sure to read forums, blogs and to exercise as much as you can on internet from all sources. Also I found very usefull video tutorials from Indian guy, named Pragim I think(just type Pragim Technologies), you will find him on YT for sure. Be sure to undetstand XML things cause I got 5 questions on exam related to xml chapter. Ofcourse you dont need to study theory from the book like a nerd, you just need to understand what is happening and to know when you need to use it, especialy in Optimization parts of the book. Also transactions are very important for exam. The part that was making me confused during the exam was that, that when I checked syntax with provided option in the questions where my task was to write queries, it was telling me that I have “spelling errors” on places where I put aliases in my code, and that was the case not in one, but in few questions. If somebody can explain it to me I would be very gratefull.
I’m happy that I passed the test, I have studied for it about 2 months active, never had experience with sql.
Next task for me is: 70-463, and ofcourse getting certs for W Server, and probably programing(480 or 483).
Good luck to everebody and keep studying! 🙂
Nicholas, Serbia
Congratulations @Nicholas, and thanks for your comments. Best of luck for next exams. ~Manoj
Hi Sir, Just went through your blog post. I am fresher and recently graduated in Computer Science Engineering.Recently started working as a data analyst and to complete the goal assigned by the company, I need an SQL certification for which I need your siggestion I have basic knowledge about Databases and SQL. Would you suggest me to take up the 98-364 database fundamentals exam or push my self a little harder and take up the 70-461 exam.( I have no knowledge about XML, and in depth queries). Thanks
Hi, thanks for your post, it is really helpful!
I was wondering.. what do you think about the 70-461 trainer handbook as a study guide?
Thanks a lot!
Hi David, thanks a lot for your comments and I’m really happy that people are getting benefited from this blog post. Handbook would be a very tough task for me as of now, would have to work full-time for sometime 🙂
~Manoj
Manoj thanks for the really quick answer. But I think I didn’t make myself clear… I meant the already existing Trainer Handbook (the one that MCT’s have access to).
Thanks again!
my bad 🙂
Yes, the Training Kit book mentioned above in my blog post will be very helpful. But you need to do good hands-on with SQL queries.
~Manoj
Still not quite there! hahaha
The training kit book and the MCT’s trainer handbook are two different tools. I was wondering if you had access to the MCT one.
no-no, got you on 2nd reply 🙂 I haven’t checked that book yet !!!
Ahh I see! Well, it says it is a great tool, but I wanted your opinion on that hehehe
Thanks anyways, best of luck!
Hey Manoj, I have a question for you!
How does the test correct your answer on the “type the query” questions?
Can you get like 50% of the question if you typed half of the code, or it considers only the full answer (all or nothing)?
Thanks again!
Not sure about this David. But I think it validates your query syntax and have some internal parsing logic. Thanks !
Hey Manoj! I’ve just passed the exam today with a score of 857. Thanks for your tips and your time. I’m aiming for the 462 now.
Hi Manoj!
I cleared my 70-461 (Querying Microsoft SQL Server 2012/14) exam on 02nd Jul 2016.
My doubt is, I am studying in Final-Year of Engineering(IT) and I have some previous knowledge about Data Warehouses and Data Mining(Informatica and SPSS Clementine).Now I am in a fix whether I should proceed with Exam 70-462 or 70-463.
Note: I have no knowledge about ‘ Database-Administration’.
Hi,
congrats for your certification.
It will be great help for me ,if you share any model questions(dumps) for”
070-461 – Querying Microsoft SQL Server 2012″ exam.
Please forward to “SIVAGVP@GMAIL.COM”.
Thanks in Advance.
Hi Manoj!
I recently cleared exam 70-461 (Querying Microsoft SQL Server 2012/14). Now I am in a fix whether to opt 462/463. I have some previous knowledge about Data Warehouses and Data Mining (Informatica and SPSS Clementine).
Note: I have no knowledge or experience in Database Administration,
Hi Manoj,
I cleared my 70-461 (Querying Microsoft SQL Server 2012/14) on 5th of july with 810 points.:)
I had a total of 2hrs to complete 49 questions,which included MCQs,Choose all that apply type questions,around 10 queries to write,5 questions on code arrangements,3 questions where i had to mark statements whether true/false.I studied from Training kit by Itzik Ben-Gan and Jumpstart videos from Microsoft Virtual Academy. Overall it was a complete awesome experience.
Thanks for all your support and useful tips.Now working on 70-462,Hope to clear it soon.
Hi Shivraj,
Could you please let us know about the writing queries? On which topic those were based on!!
Do we get a certificate , after clearing 461 ? Or only after clearing 461,462,463 we get a certificate ? I only want to appear for 461. I’m not interested in getting the MCSA tag.
Hey Manoj, I’ve been following your blog in the last days and I liked what I read, especially the tips on the exam 70-461, and video tutorials, for I will make my second attempt in a few days.
I would suggest the following link reference to the indexed views:
https://www.simple-talk.com/sql/learn-sql-server/sql-server-indexed-views-the-basics/
SQL Server 2014 certification update: I’ve added new updated post on SQL 2014 certification, please check this: https://sqlwithmanoj.com/2015/12/01/exam-70-461-sql-2014-update-querying-microsoft-sql-server-2014/
Hello,
i have issues reading complex queries and determine the outcome of the query especially during the exam. Does anyone know of a tip that can be used? i tried the exam once and did not make it. I need help and guidance to clear this exam as soon as possible. This is for 70-461 exam
Hello manub22
I have some question to ask you please. I do not understood why I failed the exam SQL SERVER 2012-2014 two time with the same result. First exam I took was 08/08/2016 and my result is 674 and the second exam was 14/01/2017 this now and my result again is 674. In my time exam I try to fill the question to say yes for answer but is not accept the click. and that happened The First exam and the second exam Also.
my email address is ricomk007@hotmail.co.uk.
Please if you can help me that will be helpful.
RAOUL
Manoj, Can you please provide cerfification data dump for SQL Sever 2012
I would not recommend to go with dumps, if you have any or are looking for. Practicing and learning can only make you understand the concepts.
Hi,
Will the official book “Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012” stillbe sufficient to pass the exam now as it now includes SQL Server 2014 as well?
Hi Kanchana, I referred this book back in 2012 only, and not sure if it includes SQL 2014 material. If you are sure and have checked this then the book should be sufficient. Best of luck for the exam !
Hi,
I have heard that Microsoft going to change the pattern by March 2017. Is that true or false?
If true then do you still prefer practicing with the old edition. Will that effect the current materail that sujested by Microsoft 2012 edision. Because I’m planning to take a test in the month of April. How long certification will be Valid
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.
HI Manoj, how are the exam points allocated? For example, if we have to write a select statement and only get 1 piece correct, will we still get those points or will we get 0 points for that question?
Just passed my 70-461 3 hours ago. Thank you
hey, congratulations !!!
Hello Manoj
any Helpful link on this blog to pass the MTA database fundamental exam
https://www.microsoft.com/en-us/learning/exam-98-364.aspx