Exam 70-442: PRO: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005
Posted by zieglers on April 24, 2007
Today I got the exam 70-442: PRO: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005. I passed with a score 980 / 1000. It’d be nice if I were able to see my incorrect answer…
Anyways, here are my recommendations to the ones preparing for getting this exam soon:
There were 55 questions and 7 testlets again as usual (for North America). Namely, those testlets are
- Tailspin Toys
- Margie’s Travel
- Consolidated Messenger (asked twice with different set of questions)
- Contoso Ltd.
- Woodgrove Bank
- Choice Part
I want to mention about some keywords you need to know while dealing with those questions in the exam.
As for Tailspin Toys, make sure you understand the meaning and usage of the terms: DataSet, Read Committed, MARS.
For Margie’s Travel, the keywords are ‘set operations’, DataSet ( again 🙂 ), Repeatable Read, Data Reader.
For Consolidated Messenger (my favorite one 🙂 ) : Read Committed Snapshot, Keyset ( I strongly recommend you to fully understand Cursor options ), MARS, Data Reader, XML Reader.
For Contoso Ltd. : MARS, READPAST, Snapshot Isolation Level, Data Reader.
For the multiple choice part: RANK, CTEs (recursive stuff 🙂 , especially for hierarchical reporting in a single table if the columns are refering to each other. Since CTE is an important concept, understand where it is used and try to write one on your own. 😉 ), XML functions like .exist, .value, .nodes, Cursor types (Static, Dynamic, Keyset, …), CUBE, PIVOT, PERCENT, LEFT OUTER JOIN, RIGHT OUTER JOIN, CROSS APPLY, CONTAINS …
Hope you also get a good exam experience.. 🙂
zieglers
Zieglers 
showstoppa said
congratulations!!
h**p://www.4shared.com/file/11557697/75f6fd7d/P4S_442_V229.html
can you check this document and confirm questions?
thanks!
zieglers said
They are pretty accurate as well..
zieglers
Jacky said
Hi Zieglers,
Would you mind sending me the questions? I passed 70-431 last weekend, and would like moving to 70-442 & 70-441 now. Do you recommend which one I should do first?
zieglers said
Hi Jacky,
Since you got your 70-431 and became MCTS, now you can continue with MCITP certification. In this track you have several sub-tracks.
If you get 70-441 and then 70-442, then you become MCITP: Database Developer.
You can also choose to go with 70-443 & 70-444. Once you pass these two, then you become MCITP: Database Administrator.
Thay are independent sub-tracks. So you can choose any of both to go first.
I also recommend you to check Microsoft Learning site for MCITP:
http://www.microsoft.com/learning/mcp/mcitp/default.mspx
zieglers
bravorex7 said
Hi guys,
congrats zeiglers, n thanks for starting the thread.
does anyone have PDFs / questions for 441 n 442?? cud u plz post em.
thanks guys
Jacky said
Hi Mates,
I passed 70-442 with 980/1000 today. Not too bad… It contains 7 testlets. The questions I had in the exams are:
1. Choice – 15 questions
2. WoodGrove Bank – 8 questions
3. Blue Yonder Airlines – 5 questions
4. City Power & Light – 5 quesitons
5. Consolidated Messenger – 9 questions
6. Humongous Insurance – 5 questions
7. Contoso Ltd – 8 questions
Guys, word hard and to get it… haha…
Thanks,
Jacky said
Hi Mates,
Another thing I would like to mention is every testlet has its own time limit. So please be careful the clock when sitting there…
Thanks,
Jacky
zieglers said
Congrats, Jacky! Thanks for sharing your exam experience…
zieglers
krajeck said
Hey Guys – well done to you all!!
Do you know if there are any blogs for 70-443 / 444???
Thanks again.
sql7999 said
Yeah, does anybody know any blogs or forums for 70-443? The 70-431 here is excellent.
dishanf said
can anybody pls check the version of pass4sure? Is it still 2.29 ??
duanxn said
Hi,
While reading the testking or actualtest Q&A, I found the answer for the first question is incorrect:
You need to write a query that retrieves all product categories that exist either directly or indirectly under category 7. Which query should you use?
A.
SELECT * FROM Categories AS c2 WHERE c2.CategoryID IN ( SELECT
CategoryID FROM Categories WHERE CategoryID = 7 EXCEPT SELECT
cs.CategoryID FROM Categories AS cs WHERE ParentCategoryID = 7)
B.
SELECT * FROM Categories AS c2 WHERE c2.CategoryID IN ( SELECT
CategoryID FROM Categories WHERE ParentCategoryID = 7 UNION ALL SELECT
cs.CategoryID FROM Categories AS cs INNER JOIN Categories AS c ON
c.ParentCategoryID = cs.CategoryID)
C.
WITH c AS ( SELECT CategoryID FROM Categories WHERE ParentCategoryID = 7
UNION ALL SELECT cs.CategoryID FROM Categories AS cs INNER JOIN c ON
c.ParentCategoryID = cs.CategoryID)SELECT * FROM Categories AS c2 WHERE
c2.CategoryID IN (SELECT c.CategoryID FROM c)
D.
WITH c AS ( SELECT CategoryID FROM Categories WHERE CategoryID = 7
UNION ALL SELECT cs.CategoryID FROM Categories AS cs INNER JOIN c ON
c.ParentCategoryID = cs.CategoryID)SELECT * FROM Categories AS c2 WHERE
c2.CategoryID IN (SELECT c.CategoryID FROM c)
The answers both Q&A are “C”.But I think it should be
WITH c AS ( SELECT CategoryID FROM Categories WHERE ParentCategoryID = 7
UNION ALL SELECT cs.CategoryID FROM Categories AS cs INNER JOIN c ON
—–>>
cs.ParentCategoryID = c.CategoryID) SELECT * FROM Categories AS c2 WHERE
c2.CategoryID IN (SELECT c.CategoryID FROM c)
Can anybody confirm?
zieglers said
No, the answer is C. It is correct.
Please check the classical ‘manager-employee hierarchy’ examples from the net using CTE. It has to be … WHERE ParentCategoryID = 7 …, since it’s the root.
zieglers
manojkumarfbd said
Hi All
I am preparing for the 70-442 & 70-441. Can anybody provide me the PDF for these exams.
Email:- manojkumarfbd@gmail.com
thank
Mnaoj kumar
jinxguy23 said
is the question same as what is in like anyother sql exam from the testking or pass4sure
jzh0mk said
is pass4sure still working for 70-442 ?
fodder74 said
In response to #12 and #13, DuanXn is correct to point out they have the tables in their inner join backwards. “c.ParentCategoryID” doesn’t exist as the select statement associated with c is only retrieving the CategoryID column.
Ignoring that mistake, the correct answer is D, not C. CategoryID is the root, not ParentCategoryID.
I confirmed this by building the table and actually running each query to see the results. Answer C only brings back those records where ParentCategoryID = 7 and skips the record with CategoryID = 7.
rtdba said
Hi guys, can anybody post the exam pdfs for 441 & 442 pls.
bonkster said
Microsoft Self Paced Training Kit E-Book 70-442
http://www.esnips.com/doc/0592fdfd-8e6f-4131-a0d8-865dab33db3b/70-442_Designing_And_Optimizing_Data_Access_By_Using_SQL_Server_2005
Enjoy! I also uploaded 70-441 till 70-445 e-books
sleeveofwizard said
is Pass4Sure v2.29 still working?
dlangham said
Passed today with 940 and yes Pass4Sure v2.29 is still OK to use.
cobra007 said
Guys, I need to write exam 70-447 but Microsfot say I need to go through book 7-443 and 70-444. Has any one here written exam 70-447 and did you prepare going through those two books?
hardik10 said
is Pass4Sure v2.29 still working for 70-442 exam ??
Thanks
marion00 said
http://www.4shared.com/file/11557697/75f6fd7d/P4S_442_V229.html
The above link and file resulted in “NOT FOUND” on the 4shared.com site. Anyone may provide some helpful suggestions how I can locate the v2.29 from Pass4Sure? It will be greatly appreciated!
Thanks!
marasiso said
Hi all my friends .
really i need your help ..
I want the 70-442 exam test king or pass4sure and i do not know where can i get it .
Thanks a lot .
domi666 said
Here is the link for 70-442
http://rapidshare.com/files/128213638/70-442.rar
marasiso said
Thanks alot domi666 but i need your opinion about this version
thanks
domi666 said
Inside of the rar you’ll find latest from Pass4sure v2.29, ExamWorx and from Actualtest.com 09.13.2006
Hope this will help you.
sam543 said
Hi,
Looks like no one has visited for a while, need some help about the exam ,is it same or modified, pls help those who wrote it recently. Appreciate your feedback.
kfpanda said
passed today with 960 and p4s v2.29 is still working …
Good luck.
mzhou said
kfpanda,
Would you please tell me more detail about 070-442, thanks.
Mike
mzhou said
I passed 070-432 this morning. Yes,p4s v2.29 is still working.
Thanks for all guys who provided the information about test.
Happy New Year.
Mike
awazone said
Passed 🙂 Good-luck everyone!
deamondragon said
Hi guys,
I passed 70-431 and I’m planning to take the 70-441 or 70-442 as my next exam. Which exam is easier to pass? Is there some version of these two exams, which is recently often repeated in examinations at Prometric? What do you recommend? Is someone passed some of these exams recently?
deamondragon said
Hi guys,
I passed 70-442! P4s v2.29 is still working!
sqlexpressfree said
hi,
has anyone taken this test recently?
thanks all
nabeelasif said
Hi everyone,
I have done my exam 70-431. Now move to 70-442. Is it possible or first I will do the exam 70-441? Another question regarding the simulations for 70-442. Is 70-442 exam includes simulations like 70-431?
Thanks
sqlexpressfree said
hi guys,
Im going to take this test in 2 weeks, is the pass4sure 2.29 still valid?
thanks everyone
deamondragon said
I need cd materials (sample code and databases) that came with this MS Press Book: “Microsoft SQL Server 2005 Reporting Services Step by Step”.
If anybody is interesting for this book, here is the link:
http://rapidshare.com/files/133128755/Microsoft_SQL_Server_2005_Reporting_Services_Step_by_Step.zip
booyeeka said
hi, same question – is the pass4sure 2.29 still valid?
thank you in advance!!!
booyeeka said
yep! 2.29 is still valid. 940/1000
🙂
swagger123 said
Hey there
mate of mine wrote this exam on friday 4 sep 2009 and appraently it has changed he scored less than 300 can anyone confirm this for me or post the new exam
you help would be apprecaited i write in less than a week thanks
wfairb said
hey — sorry about your friend — let me know how you make out — i’m right behind ya.
good luck
wfairb said
p4s website says:
“Questions and Answers : 140 Q&A
Updated: September 7th , 2009”
it doesn’t mention what the current version is…
gbdimitrov said
Hi guys,
I passed 70-442 today! P4s v2.29 is still valid!
Good luck!
wfairb said
Congrats and thanks for the update!!
hitman1111 said
Hi guys,
is P4S v2.29 still valid?
Many thanks
rcivalero said
I am taking the test next week. Are the questions still valid?
debz1 said
Firstly Good Luck!! I am also taking it next week – so if anyone can give any advice is P4Sure v 2.29 still valid?
hitman1111 said
Hi,
any updates?
Thanks a lot!
harisma2010 said
I passed this exam at 19 November, 2010. 935/1000 6 testlets and 14 common questions. Many questions from TestKings and ActualTests which are in Internet, but exists some new common questions which are i do not see before. Who need learning materials, send me a letter on email (gvozdik75@mail.ru)