Exam A Q14
I guess the correct answer should not include a subquery.
Let's follow the requirements:
1) NOT use object delimiters — ok
2) Return the most recent orders first.
SELECT * FROM Orders ORDER BY OrderDate DESC

3) Use the first initial of the table as an alias
SELECT O.* FROM Orders O ORDER BY O.OrderDate DESC

4) Return the most recent order date for each customer
SELECT
MAX(O.OrderDate)
FROM Orders O
GROUP BY O.CustomerID
ORDER BY MAX(O.OrderDate) DESC

5) Retrieve the last name of the person who placed the order
SELECT
C.LastName,
MAX(O.OrderDate)
FROM Orders O
INNER JOIN Customers C
ON C.CustomerID = O.CustomerID
GROUP BY O.CustomerID, C.LastName
ORDER BY MAX(O.OrderDate) DESC

6) Return the order date in a column named MostRecentOrderDate that appears as the last column in the report
SELECT
C.LastName,
MAX(O.OrderDate) AS MostRecentOrderDate
FROM Orders O
INNER JOIN Customers C
ON C.CustomerID = O.CustomerID
GROUP BY O.CustomerID, C.LastName
ORDER BY MAX(O.OrderDate) DESC

It is valid im Brazil. Pass with 900/1000 (session with 25 QUERING questions with 80 minutes) and 933/1000 (session with 25 ADM questions with 80 minutes) today. I uploaded new version of this dump (will be avaiable soon).

Hi friends

I am planning to appear for this exam.. please tell me if this dump is valid or not.

Will take the exam with in a week if its valid

one thing to remind all candidates, make sure you have scrolled to the end of the answer list when doing 'Build list and reorder' test, especially when your test centres are using crap mouses on the test machines…

passed yesterday with 800, there were about 3 new questions, i will try to remember and post here…

I think PRECEDING and previous same meaning

Thanks Cibek, i didn't read that difference before..
Thanks for clarification.

Mike —>> Exist a very little difference between those question
Q36 say: "for each year and its PRECEDING year"
Q13 say :"for each year and its PREVIOUS year"
So in the first question you should use LEAD, and the other LAG,

Hope clarify your doubt

Hello all, there are 2 same question with different answer, i mean Q13 of Exam B and Q36 of exam A, Which are correct of
A) SELECT Territory, Year, Profit,
LAG(Profit,1,0) OVER (PARTITION BY Territory Order by Year) as PrevProfit FROM Profit .. OR
B) SELECT Territory, Year, Profit,
LEAD(Profit,1,0) OVER (PARTITION BY Territory Order by Year) as PrevProfit FROM Profit. ??
———-
Thanks for your answers.

Passed from a week ago with 733 for part 1 (querying) and 866 for part 2 Administration) , some Querying questions (part 1) are out of the dump, you should not study this dump based on memorizing the answer's letters and order because the choices order is not similar to the order in the dump.

The exam is two parts, the first one is related to Querying topics and the second one is related to the Administration ones, the Administration part answers are more accurate than the development ones, also all Administration questions are from the dump.

Thanks for Derek and AnotherInbox

question 28 answer is wrong.
correct answer should be

WITH CTEDupRecords
AS
(
SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName
FROM Products
GROUP BY ProductName
HAVING COUNT(*) > 1
)
DELETE p
FROM Products p
JOIN CTEDupRecords cte ON
p.ProductName = cte.ProductName
AND p.CreatedDateTime < cte.CreatedDateTime

thanks everyone for the great dump, passed with 833, but there are a few new questions in both query part (ex. Return Data from a Stored Procedure) and admin part. Even, some questions are the same but expressions of the answers changed, so you have to not only memorize the questions and their exact answers, but study the link to understand the concepts better as well.

@Derek, in reaction to your question.

Exam B Q3
It is a bit unclear on how to interpretate 'efficient'. Easiest qry to write, or easiest qry to be executed by SQL. I am guessing, most efficient to SQL Engine/Execute.

Answer D is a query that has values in the where statement that are datatypes equal to the fields or the values in the index on the fields. Therefore is is more likely that SQLengine can do a seek which is more efficient than a scan.

The datatype is datetime, so date + time.

A will only return data from that day on the time 00:00:00, there are more (mili)seconds in a day you might miss something.
B will only return data exactly equal to getdate(), to the second. So you would miss some again.
C might work if there was no syntax error in the where statement. The getdate() function has to many arguments. If there was no error in the statement this would be 'most efficient to write', only because there is less code to write.
D will work effecient. It runs the where statement againts the database in the correct datatypes and first will go to:
-earliest time getdate() yyyy-mm-dd 00:00:00:000
-earliest time the day after current day (with dateadd +D1) yyyy-mm-dd 00:00:00:000, so with using < you will return the current day until 23:59:59:99999. All returned in correct (efficient datatype) and the qry itself is correct.

Fixed the answers more than 10 questions