70-461 Tracking Thread

The name of that book is really deceiving. It goes into querying pretty deeply.

Matt27[lt]

Zorodzai wrote: »

So on a whim I decided to register for the 70-433 exam:

Background
Some time last year I started studying for the SQL 2008 dev certification. Life got in the way etc and I didn't get round to writing. When Microsoft decided to retire the 70-433 exam in July I decided to move on to 2012. Of course MS then decided to change the retirement date to January 2014.......

Reason for sitting exam
I had made a decision to do SQL 2012 certification but decided, since we use 2008 R2 at work and I felt I was fairly ready for the exam, I might as well wing it.

Result
Passed - with 833 !! Am still pursuing 2012 and hope to get through my MCSA done by end of this year.

Holy Cow! - My situation was the same with 70-450 (200

. The last time I checked MS was telling it will retire in 2013, so I skip it and went for 70-461 (2012). Now I see that I can still do 70-450. I hate MS so much for all this retired/not retired crap. No regrets on studying for 70-461, because it's worth it, but still - I HATE you MS.

Zorodzai

@Matt27 - I hate how MS just decide "this is no longer relevant" when, in the field, it's still very relevant ! I am glad they extended the expiration period but rather annoyed about being jerked around .....

Their stance has been for a while now that the certification will retire once a second version of the technology has been released from the one in question. So if you are studying for the 2008 certs you should expect that they will be retired once SQL Server 2014 is starting to hit RC. Technically speaking 2008 R2 and then 2012 meets that their criteria. And you know it has always been MS's M.O. that once partners and customers start to whine they extend things. Look at support for XP!

Personally I don't think that it really matters, if you are certified on 2012 then you clearly have the skills required to admin/develop for 2008. The only complaining that I really think would be justified if they moved the retirement up. They always push stuff out like this.

But if you have experience with 2005/2008/R2 and you are certified on 2012, then you are still golden. Your experience has been validated and you are showing that you're willing to put in the effort to keep your skills up-to-date.

Now if you want to talk about being jerked around, let's discuss the MCSM...

Anyone have a suggestion for book or material that focuses on Stored Proc's? I will be assigned to a project fairly soon where I will need to use Stored Proc's to retrieve data back from the database into Excel (so I can chart the information etc) I don't really want to learn about the other elements due to time constraints. Thanks.

For the most part it's pretty easy. Just Google the syntax.

Is it just a **** of the data? A view might be easier as you don't have to use any VBS at all. You just point Excel to the view and then it can do its stuff. Then users can do filtering/pivoting, etc. But to connect Excel to a sproc you will have to do some VBS.

It's a fairly complex query with multiple joins and case statements. It's ~5300 lines of SQL.

Sure, but that doesn't mean can't be a view or isn't just a data ****. If it is not using CTEs or some of the other features that cannot be used in views then there is no reason to make it a sproc.

If you don't need those features, then you can just make it a view and allow filtering, etc to be done in side of Excel. If you have to use some of those features or you have to parameterize then you'll need to use a sproc or a table valued function. But I would try getting away with a view first.

I agree that if you just want to get a data set back then a view is an appropriate choice.

papaguapo

If it is not using CTEs or some of the other features that cannot be used in views then there is no reason to make it a sproc.

CTEs CAN be used in views.

Okay okay it's been a while but I picked up my studies. I made it through the MS 2012 Query book from Itzik. I have a sandbox environment at work so that made it nice to use some of the features in 2012.

Is using this book enough to pass the exam in your opinion? Hopefully I have more luck this time. 433 got the best of me last time. Now I am just moving on to 2012

461 SQL.jpg

IMO that book is sufficient for the exam - assuming you can write a query without help on every topic in it - and know the syntax cold. I found the TrainSignal videos quite helpful for this exam as well.

I may go that route eventually but I want to apply what the book teaches to the data I actually work with not the mock database that is provided by MS. Thanks for the follow up! Was transignal pricey?

I think I paid $30/mo for trainsignal. They merged with PluralSight and I think the new price is similar. It's not strictly required to do all your practice in the provided database. In my case, I downloaded AdventureWorks and used that so that I could not use the provided examples. If you have access to a database at work that can support all the features, then I would 100% advise you to use that.

Yeah my Boss is in on me going through this, but but but he wants it on the company data. He refuses to give his blessing to work with adventure works data. Our data is much more complex we have geospatial data along with layers upon layers of data. Our company is a BI company that has a major data warehouse, production environment and several data marts spun off. The cool thing is they replicate everything so I have an analyst sandbox in our test server which is cool so if run a query that kills the I/O they could careless.

krj118

@CarlSaiyed - Which beginner book would you recommend? I've done some searches on Safari (I get that through work.) but I can only locate the T-SQL Fundamentals books of his. Are those the ones you are referring to?

ClausIRL

Hello guys, sorry for bringing this old thread back to life, I also started doing my 70-461 at the moment and I am planning to study the CBT Nuggets cause they show you a lot of hands on and how to actually do the stuff & will then move on to the Pluralsight course for the 70-461 from which I will also start taking notes, finally I will watch the full video of this course on Microsoft Virtual Academy as I found it on there too and will then start with the practice tests for 1-2 weeks until I get 100% a number of times.
Last I will do some revision and some hands on practice of queries on my virtual machine until I go for the exam. If all goes well with this I'll do the exact same for the 70-462 and 463. I got the CBT nuggets for all 3 which seem very helpful. Too bad pluralsight didn't upload a course for the 463 as well

Claus thanks for the update. I am hitting the training kit first from MS, I already completed the Query book from Itzik. I am using my home lab and work test environment to practice the different lesson plans within the training kit. If I am able to grasp this material fairly efficiently I may consider moving on to 462. Right now it's just 461 at the moment though.

For a very beginner, I recommend Head First SQL. I haven't read Iztik's beginner book but I did read the 70-461 book and I liked it a lot.

Carl 70-461 book is really nice. Just wrapped up 2 chapters both I had a strong grasp of most of the concepts. Cases and Coalesce mainly, but it was nice to look through some of the other expressions, like IIF and Choose both I had never used before in SSMS. I had used IIF before in Access a while ago. Thanks again for the great information.

Just an update if you are interested. Went over case statements and coalesce, which I use both a lot. However I found it interesting that there were two types of case statements simple and searched, I always used the searched case statements. Also I never realized there was a isnull function in SQL, I always by default used Coalesce. I guess anything can be apart of the test so I am making sure I know the differences.

Coalesce will take multiple params (its like a nested ISNULL). Use coalesce if you have multiple, ISNULL if you have only two. Keep in mind ISNULL returns the datatype of your first param, Coalesce returns the data type with the highest precedence.

Carl thanks for the follow up.

You are welcome!

Rivka

Question for you- Can you study for the 70-461 on MS Access? Or do you need to download the student edition of the SQL server from MS?
btw, Joes2Pros is FIVE books for the 461....people rave about it on Amazon, but I am going through book 1 and am not jumping up and down....

@Rivka - I would focus on learning how to install MS SQL developer or express. Access is limited and the SQL can look wonky sometimes. I believe it uses an older ANSI standard. Besides if you ever want to take 462 or higher you'll need SSMS.

Access will not work for this exam.

Got through page 56 or so and then rolled back through 20 - 50 again just to reinforce what I read. This is a new strategy for me, I usually read cover to cover, but for some reason since I am directly using SQL I find it more beneficial to read 20 or 30 pages and then go back through again to lock in the information. I also find that the second time I do this I uncover a lot of information I may of passed up. I am not the world strongest nor fastest read so setting reasonable goals is always a good things for me. (30 pages a night is perfect).

It's definitely no race! Keep it up, you will pass this exam!