DRAG DROP
Note: This question is part of a series of questions that use the same scenario. For your convenience,
the scenario is repeated in each question. Each question presents a different goal and answer choices,
but the text of the scenario is exactly the same in each question in this series.
You are developing a database to track customer orders. The database contains the following tables:
Sales.Customers, Sales.Orders, and Sales.OrderLines. The following table describes the columns in
Sales.Customers.
The following table describes the columns in Sales.Orders.
The following table describes the columns in Sales.OrderLines.
You need to create a function that accepts a CustomerID as a parameter and returns the following information:
all customer information for the customer
the total number of orders for the customer
the total price of all orders for the customer
the average quantity of items per order
How should you complete the function definition? To answer, drag the appropriate Transact-SQL segment to
the correct locations. Transact-SQL segment may be used once, more than once, or not at all. You may need
to drag the split bar between panes or scroll to view content.
Select and Place:
Answer: 
Explanation:
Box1: RETURNS TABLE
The function should return the following information:
all customer information for the customer
the total number of orders for the customer
the total price of all orders for the customer
the average quantity of items per order
Box 2: COUNT
The function should return the total number of orders for the customer.
Box 3: SUM
The function should return the total price of all orders for the customer.
Box 3. AVG
The function should return the average quantity of items per order.
Box 4: GROUP BY
Need to use GROUP BY for the aggregate functions.
https://msdn.microsoft.com/en-us/library/ms186755.aspx
The main structure of the query and the answer is correct, but the given example will not fulfill the requirement “the total number of orders for the customer”.
Doing the COUNT(O.OrderID) will count all single orders multiplied with the number of order lines for this order. The correct syntax would be COUNT(DISTINCT O.OrderID)! The correct value for TotalNumberOfOrders is shown by the attribte “CorrectTotalNumberOfOrders”.
The example code below will show the different results:
IF EXISTS (SELECT TOP(1) 42 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ and TABLE_NAME = ‘Customers’)
BEGIN
DROP TABLE dbo.Customers;
END;
IF EXISTS (SELECT TOP(1) 42 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ and TABLE_NAME = ‘Orders’)
BEGIN
DROP TABLE dbo.Orders;
END;
IF EXISTS (SELECT TOP(1) 42 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ and TABLE_NAME = ‘OrderLines’)
BEGIN
DROP TABLE dbo.OrderLines;
END;
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCustomerInformation]’) AND type IN ( N’FN’, N’IF’, N’TF’, N’FS’, N’FT’ ))
BEGIN
DROP FUNCTION [dbo].[GetCustomerInformation]
END;
CREATE TABLE Customers (CustomerID INT PRIMARY KEY, CustomerName NVARCHAR(100));
CREATE TABLE Orders (OrderID INT PRIMARY KEY, CustomerID INT, OrderDate DATE);
CREATE TABLE OrderLines (OrderLineID INT PRIMARY KEY, OrderID INT, Quantity INT, UnitPrice decimal(18,2));
INSERT INTO Customers VALUES (1, ‘Customer1’), (2, ‘Customer2’), (3, ‘Customer3′);
INSERT INTO Orders VALUES (1,1,’2018-01-08′), (2,1,’2018-01-08′), (3,2,’2018-01-08′), (4,3,’2018-01-08’);
INSERT INTO OrderLines VALUES
(1,1,5,1.2),(2,1,3,1.5),(3,1,11,2.7),
(4,2,4,7.6),(5,2,3,0.75),
(6,3,1,1),(7,3,1,5),(8,3,1,9.1),(9,3,1,1),
(10,4,200,12.34);
GO
CREATE FUNCTION [dbo].[GetCustomerInformation] (@CustomerID INT)
RETURNS TABLE
AS
RETURN
(
SELECT
C.CustomerID, C.CustomerName,
COUNT(O.OrderID) AS TotalNumberOfOrders,
COUNT(DISTINCT O.OrderID) AS CorrectTotalNumberOfOrders,
SUM(OL.UnitPrice) As TotalOrderPrice,
AVG(OL.Quantity) As AverageOrderQuantity
FROM Customers AS C
INNER JOIN Orders AS O ON O.CustomerID = C.CustomerID
INNER JOIN OrderLines AS OL ON OL.OrderID = O.OrderID
WHERE C.CustomerID = @CustomerID
GROUP BY C.CustomerID, C.CustomerName
);
GO
SELECT * FROM dbo.GetCustomerInformation (1);
SELECT * FROM dbo.GetCustomerInformation (2);
SELECT * FROM dbo.GetCustomerInformation (3);
agree