ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam 70-761 All Questions

View all questions & answers for the 70-761 exam
Go to Exam

Exam 70-761 topic 1 question 28 discussion

Actual exam question from Microsoft's 70-761
Question #: 28
Topic #: 1
[All 70-761 Questions]

You have a database that includes the tables shown in the exhibit. (Click the exhibit button.)

You need to create a list of all customers, the order ID for the last order that the customer placed, and the date that the order was placed. For customers who have not placed orders, you must substitute a zero for the order ID and 01/01/1990 for the date.
Which Transact-SQL statement should you run?
A.

B.

C.

D.

  • A. Option A
  • B. Option B
  • C. Option C
  • D. Option D
Show Suggested Answer Hide Answer
Suggested Answer: A 🗳️
ISNULL Syntax: ISNULL ( check_expression , replacement_value ) author:"Luxemburg, Rosa"
The ISNULL function replaces NULL with the specified replacement value. The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression.
References:
https://msdn.microsoft.com/en-us/library/ms184325.aspx
by Dieter at Aug. 19, 2019, 11:51 a.m.

Comments

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Submit Cancel
Tazul
Highly Voted 5 years, 8 months ago
None of them correct. Correct answer Select C.CustomerID, ISNULL(MAX(SOH.SalesOrderID),0) SalesOrderID, ISNULL(MAX(SOH.OrderDate),'01/01/1990') OrderDate from Sales.Customers C left outer join Sales.SalesOrderHeader SOH on C.CustomerID=SOH.CustomerID group by C.CustomerID order by C.CustomerID
upvoted 20 times
Bartek
5 years, 8 months ago
ISNULL(MAX(SOH.SalesOrderID),0) SalesOrderID This II type You max number of digit include in orderid This is wrong, You should group by salesoorderid too
upvoted 2 times
Tazul
5 years, 7 months ago
If you add SalesOrderID to Group By, then result will include all SalesOrderID, not the latest one. You can test using following script- Select C.CustomerID, SOH.SalesOrderID, max(SOH.OrderDate) OrderDate from Sales.Customers C left outer join Sales.SalesOrderHeader SOH on C.CustomerID=SOH.CustomerID group by C.CustomerID, SOH.SalesOrderID
upvoted 4 times
...
...
...
Dieter
Highly Voted 5 years, 8 months ago
However, A is not totally correct since the replacement of the default date is not mentioned.
upvoted 7 times
mlourinho
5 years, 6 months ago
If you execute DECLARE @TESTE DATE = NULL SELECT ISNULL(@TESTE, '') , you will find that the result will be 1900-01-01
upvoted 6 times
Bahalzamon
5 years, 5 months ago
true, but 1900-01-01 was not what was requested, it was 01/01/1990. your about 90 years off. =P
upvoted 6 times
Engineerdocing
4 years, 10 months ago
I frankly think this is a question error, since other options dont event attempt to replace
upvoted 1 times
...
...
Oooo
4 years, 8 months ago
This is interesting.
upvoted 1 times
...
...
...
Vermonster
Most Recent 4 years, 4 months ago
Agreee with TCroots on answer but probably too complex for exam so going with A except with LEFT OUTER JOIN vs. JOIN
upvoted 1 times
...
Andy7622
4 years, 5 months ago
ALSO option A is the only one which uses functions working with NULLs
upvoted 1 times
...
Andy7622
4 years, 5 months ago
the only join that matches is LEFT OUTER JOIN , which is in A
upvoted 1 times
...
Andy7622
4 years, 5 months ago
Out of all queries the closest is A
upvoted 1 times
...
Backy
4 years, 8 months ago
A. is incorrect for at least two reasons Reason1 - question asks to substitute 01/01/1990, not 01/01/1900 Reason2 - incorrect grouping GROUP BY C.CustomerID, SOH.SalesOrderID since SOH.SalesOrderID is the key, MAX(OrderDate) will always pick up only one OrderDate or NULL, you need GROUP BY C.CustomerID but then you have to remove SOH.SalesOrderID from SELECT If you need both OrderDate and SalesOrderID of the last order then you have to redesign the whole query
upvoted 2 times
...
truthee
5 years ago
USE AdventureWorks2016 Go OPTION A is correct; SELECT c.CustomerID, ISNULL(soh.SalesOrderID, 0), ISNULL(MAX(soh.OrderDate), '') FROM Sales.Customer c LEFT JOIN Sales.SalesOrderHeader soh ON c.CustomerID = soh.CustomerID GROUP BY c.CustomerID, soh.SalesOrderID ORDER BY c.CustomerID; Same result as: SELECT c.CustomerID, ISNULL(soh.SalesOrderID, 0), ISNULL(MAX(soh.OrderDate), '01/01/1900') FROM Sales.Customer c LEFT JOIN Sales.SalesOrderHeader soh ON c.CustomerID = soh.CustomerID GROUP BY c.CustomerID, soh.SalesOrderID ORDER BY c.CustomerID;
upvoted 2 times
Ricky7876
4 years, 12 months ago
The question says to substitute 01/01/1990 not 01/01/1900. Additionally, if you group by SalesOrderID then you may not get a unique list of customers (What if a customer placed more than 1 order? you are trying to find the OrderID associated with the latest Orderdate). I think Tazul has the best answer except I don't think you need to use the Order By (since you are looking for a MAX value the order wouldn't matter). Lastly, I think you need to make an assumption that the larger the OrderID the larger (later) the OrderDate.
upvoted 2 times
...
BabyBee
4 years, 5 months ago
Tested it and it returns all orders, not only the last order. None of the queries provide the answer.
upvoted 1 times
...
...
shruthiBattula
5 years, 2 months ago
CREATE TABLE #PERSONS ( PERSONID INT, NAME VARCHAR(15) ) CREATE TABLE #orders ( orderid int, personid int, orderdate date ) SELECT * FROM #PERSONS SELECT * FROM #ORDERS INSERT INTO #PERSONS (PERSONID, NAME) VALUES (1, 'BARTEK'), (2,'KASIA'), (3,'TOMEK'),(4,'MARTA'),(5,'SHRAVY') INSERT INTO #ORDERS(orderid,personid,orderdate) VALUES (1,1,'2019-01-01'),(2,2,'2019-01-02'),(3,3,'2019-01-02'),(4,2,'2019-01-03') select c.PERSONID, isnull(max(o.orderid),0) as orderid, isnull(max(o.orderdate),'19900101') as lastorderdate from #PERSONS c left join #orders o on c.PERSONID=o.PERSONID group by c.PERSONID--,isnull(o.orderid,0) ORDER BY C.PERSONID
upvoted 2 times
tcroots19
5 years, 2 months ago
The problem here is what if your latest order has a lower OrderId (flip the 4 and 2 for person 2), it always pulls the 4
upvoted 1 times
tcroots19
5 years, 2 months ago
here's the solution using the tables from ShruthiBattuala https://stackoverflow.com/questions/48528584/sql-query-to-get-three-most-recent-records-by-customer OR https://stackoverflow.com/questions/331367/sql-statement-help-select-latest-order-for-each-customer select PERSONID, NAME, orderdate, orderid FROM ( SELECT c.PERSONID, c.NAME, o.orderdate, o.orderid, row_number() OVER(PARTITION BY c.PERSONID ORDER BY o.orderdate desc, o.orderid desc) AS rowNum FROM dbo.Persons AS C JOIN dbo.Orders AS O ON c.PERSONID = o.personid ) AS Records WHERE rowNum = 1;
upvoted 2 times
...
...
KosteK
4 years, 5 months ago
But when you add to #orders the value (5,2,'2018-12-03') the query will not meet requirements
upvoted 1 times
...
...
Scooter123
5 years, 6 months ago
Agree with Tazul
upvoted 1 times
zritter19
5 years, 4 months ago
Tazul indeed have written the best solution
upvoted 1 times
...
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago