Home > Certifications, SQL Server 2012 > Passed 70-461 Exam : Querying Microsoft SQL Server 2012

Passed 70-461 Exam : Querying Microsoft SQL Server 2012

November 16, 2012 Leave a comment Go to 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!!!


  1. Sumant
    July 16, 2015 at 2:40 pm

    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 ?.

    • July 16, 2015 at 2:54 pm

      @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

  2. July 27, 2015 at 12:11 pm

    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.

    • August 11, 2015 at 12:35 pm

      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.

  3. Prabhakar
    August 11, 2015 at 12:10 pm

    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.

    • August 11, 2015 at 12:37 pm

      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!

  4. Amir Anwar
    August 29, 2015 at 3:59 pm

    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.

    • Tom
      September 9, 2015 at 6:50 pm

      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!

    • September 9, 2015 at 6:57 pm

      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

  5. Tom
    September 9, 2015 at 6:52 pm

    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

    • September 9, 2015 at 6:55 pm

      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

  6. Tshepiso
    October 29, 2015 at 5:08 pm

    for a beginner how much time do i need to practice for 70-461 exam?

    • October 29, 2015 at 5:14 pm

      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.

  7. jainishrshah
    November 11, 2015 at 12:49 pm

    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?

  8. Shabeer
    December 6, 2015 at 10:08 pm

    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

    • jainishrshah
      December 7, 2015 at 12:08 pm

      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.

    • December 7, 2015 at 12:20 pm

      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

  9. January 6, 2016 at 10:25 pm

    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.

    • January 6, 2016 at 10:27 pm

      Congrats @Mohammad. and best of luck for the next ones.

  10. Mohammad Tuaha
    January 6, 2016 at 10:32 pm

    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.

    • David
      January 25, 2016 at 4:49 pm

      Hi mohammad, do you have dumps or studying material i am writing next week email:davidmogotsi0@gmail.com

    • January 25, 2016 at 6:09 pm

      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

  11. February 3, 2016 at 3:50 pm

    UPDATE on SQL 2014:

    MCSA (461/462/463) exams will be having SQL 2012 content only.

    MCSE (DP: 464/465 and BI: 466/467) exams are updated with SQL 2014 topics. And same will go with 2016 after release.

    ~manoj

  12. Ru
    February 9, 2016 at 12:03 am

    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.

    • February 9, 2016 at 9:47 am

      thanks @Ru… appreciate that you liked this blog post. And prepared on your own for the exam. Best of luck!
      ~manoj

  13. sai
    February 16, 2016 at 8:58 am

    Passed…! got 833. I got 44 questions

    Thanks Manoj for your guidance.

  14. February 20, 2016 at 3:05 pm

    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

  15. Oleg
    March 22, 2016 at 4:19 pm

    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 arent 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).
    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
    t found any question which wasn`t covered with the Training Kit.

  16. Nicholas
    May 11, 2016 at 1:53 am

    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

    • May 11, 2016 at 11:31 am

      Congratulations @Nicholas, and thanks for your comments. Best of luck for next exams. ~Manoj

  17. May 13, 2016 at 3:32 pm

    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

  18. June 21, 2016 at 4:53 pm

    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!

    • June 21, 2016 at 5:11 pm

      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

      • David
        June 21, 2016 at 5:17 pm

        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!

      • June 21, 2016 at 5:22 pm

        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

      • David
        June 21, 2016 at 5:27 pm

        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.

      • June 21, 2016 at 5:32 pm

        no-no, got you on 2nd reply 🙂 I haven’t checked that book yet !!!

      • David
        June 21, 2016 at 5:40 pm

        Ahh I see! Well, it says it is a great tool, but I wanted your opinion on that hehehe
        Thanks anyways, best of luck!

      • David
        June 28, 2016 at 11:11 pm

        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!

      • June 29, 2016 at 9:56 am

        Not sure about this David. But I think it validates your query syntax and have some internal parsing logic. Thanks !

      • David
        July 18, 2016 at 11:03 pm

        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.

  19. Bhavin Patel
    July 3, 2016 at 6:19 pm

    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’.

    • August 31, 2016 at 11:21 pm

      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.

  20. bhavinpatel
    July 3, 2016 at 6:23 pm

    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,

  21. July 8, 2016 at 12:05 am

    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.

    • August 28, 2016 at 8:47 am

      Hi Shivraj,

      Could you please let us know about the writing queries? On which topic those were based on!!

  22. Ryan
    July 17, 2016 at 12:54 pm

    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.

  23. Rafael Lima - Brazil
    October 15, 2016 at 1:42 am

    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/

  24. December 4, 2016 at 12:21 pm

    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/

  25. Vox
    December 22, 2016 at 12:23 am

    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

  26. January 15, 2017 at 12:43 am

    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

  27. BB
    January 17, 2017 at 11:18 am

    Manoj, Can you please provide cerfification data dump for SQL Sever 2012

    • January 17, 2017 at 2:46 pm

      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.

  28. Kanchana Meghraj
    January 25, 2017 at 3:39 pm

    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?

    • January 25, 2017 at 6:24 pm

      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 !

  29. srikanth ponnapati
    February 23, 2017 at 2:02 am

    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

  30. scp1906
    March 22, 2017 at 8:44 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.

  31. Rupa
    November 14, 2017 at 3:08 pm

    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?

  32. Percell
    January 8, 2019 at 2:34 am

    Just passed my 70-461 3 hours ago. Thank you

  33. Harpreet Singh Dhandal
    November 19, 2019 at 11:38 pm

    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

Comment pages
  1. June 1, 2016 at 8:24 pm
  2. December 1, 2016 at 9:40 pm
  3. January 2, 2017 at 5:39 pm
  4. March 16, 2017 at 9:56 pm
  5. January 1, 2018 at 5:10 pm

Leave a Reply to Rupa Cancel 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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: