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.
Explanation:
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.
https://msdn.microsoft.com/en-us/library/ms184325.aspx
I don’t think any of these are right according to the question – if we use a Group By on OrderID we will get a max date for each Order, and we actually only want the date of the most recent order
6
0
Totally agree with LW.
This statement returns all orders ever placed by the customer.
=> no answer is correct
Furthermore ORDER BY is not needed at all, as there is only one OrderDate possible per OrderID
=> To answer this question a much more complex statement would be needed, e.g. using cte and ROW_NUMBER is needed
0
0
A is Correct.
2
6
NO correct answer. Skip
6
0
ANSWER A IS CORRECT
USE AdventureWorks
GO
SELECT c.CustomerID,isnull(soh.SalesOrderID,0)AS OrderID,isnull(max(soh.OrderDate),”) FROM sales.Customer as c
LEFT OUTER JOIN sales.SalesOrderHeader AS soh
on c.CustomerID=soh.CustomerID
GROUP BY c.CustomerID,soh.SalesOrderID
–,soh.OrderDate
ORDER BY c.CustomerID
4
2
The requirement That I dont see completed is default date 01/01/1990 . probably is human error.
1
0
you are right, they are using: ‘ ‘ which gives 01/01/1900 as date 0
select convert(datetime,0)
select convert(datetime,”)
select convert(datetime,0x0000000000000000)
versus using
01/01/1990
1
0
USE AdventureWorks
GO
SELECT c.CustomerID,isnull(soh.SalesOrderID,0)AS OrderID,isnull(max(soh.OrderDate),’01/01/1990′) FROM sales.Customer as c
LEFT OUTER JOIN sales.SalesOrderHeader AS soh
on c.CustomerID=soh.CustomerID
GROUP BY c.CustomerID,soh.SalesOrderID,soh.OrderDate
ORDER BY c.CustomerID
1
1
SELECT C.CustomerID,
COALESCE(LO.SalesOrderID, 0 ) AS LastOrderId,
COALESCE(LO.OrderDate, ‘19900101’ )AS LastOrderDate
FROM Sales.Customer AS C
OUTER APPLY (SELECT TOP(1) SalesOrderID ,OrderDate
FROM Sales.SalesOrderHeader AS O
WHERE CustomerID = C.CustomerID
ORDER BY OrderDate) AS LO
0
0