Note: This question is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one question in the series.
You are a developer for a Microsoft SQL Server 2008 R2 database instance.
You create tables named order, customer, and product as follows:
CREATE TABLE [dbo].[order] ([OrderID] [int], [ProductID] [int], [CustomerID] [int],[OrderDate] [datetime]);
CREATE TABLE [dbo].[customer] ([CustomerID] [int], [CustomerName] [varchar](100),[Address] [varchar](200), [City] [varchar](100), [State] [varchar] (50),
[ZipCode] [varchar](5));
CREATE TABLE [dbo].[product] ([ProductID] [int], [ProductName] [varchar](100), [SalePrice] [money], [ManufacturerName] [varchar](100));
You need to write a query to sum the sales made to each customer by the following entries:
•The Customer name and product name
•The grand total of all sales
Which SQL query should you use?
A. SELECT p.ProductName,DATEPART(mm, o.OrderDate) OrderMonth,SUM(p.SalePrice) AS Sales
FROM product p
INNER JOIN[order] o ON p.ProductID = o.ProductID
GROUP BY ProductName, DATEPART(mm, o.OrderDate);
B. SELECT c.CustomerName, COUNT(o.OrderID) AS Orders
FROM customer c
INNER JOIN [order] o ON c.CustomerID = o.CustomerID
WHERE COUNT(o.OrderID) > 10
GROUP BY c.CustomerName;
C. SELECT c.CustomerName,AVG(p.SalePrice) AS Sales
FROM product p
INNER JOIN [order] o ON ProductID = o.ProductID
INNER JOIN customer c ON o.CustomerID = CustomerID
WHERE o.OrderDate > ’09/01/2011′
GROUP BY c.CustomerName
HAVING AVG(p.SalePrice) >= 500
D. SELECT c.CustomerName,p.ProductName,SUM(p.SalePrice) AS Sales
FROM product p
INNER JOIN [order] o ON p.ProductID = o.ProductID
INNER JOIN customer c ON o.CustomerID = CustomerID
GROUP BY GROUPING SETS ((c.CustomerName, p.ProductName), ());
E. SELECT c.CustomerName,p.ProductName,SUM(p.SalePrice) AS Sales
FROM product p
INNER JOIN [order] o ON p.ProductID = o.ProductID
INNER JOIN customer c ON o.CustomerID = CustomerID
GROUP BY GROUPING SETS ((c.CustomerName), (p.ProductName), ());
F. SELECT p.ProductName,DATEPART(mm, o.OrderDate) OrderMonth,SUM(p.SalePrice) AS Sales
FROM product p
INNER JOIN [order] o ON p.ProductID = o.ProductID
GROUP BY CUBE;
G. SELECT p.ProductName,DATEPART(mm, o.OrderDate) OrderMonth,SUM(p.SalePrice) AS Sales
FROM product p
INNER JOIN [order] o ON p.ProductID = o.ProductID
GROUP BY CUBE(p.ProductName, DATEPART(mm, o.OrderDate));
H. SELECT c.CustomerName,AVG(p.SalePrice) AS Sales
FROM product p
INNER JOIN [order] o ON ProductID = o.ProductID
INNER JOIN customer c ON o.CustomerID = CustomerID
WHERE o.OrderDate > ’09/01/2011′
AND AVG(p.SalePrice) >= 500
I. SELECT p.ProductName,DATEPART(mm, o.OrderDate) OrderMonth,SUM(p.SalePrice) AS Sales
FROM product p
INNER JOIN [order] o ON p.ProductID = o.ProductID
GROUP BY ProductName, OrderMonth;
J. SELECT c.CustomerName,COUNT(o.OrderID) AS Orders
FROM customer c
INNER JOIN [order] o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName
HAVING COUNT(o.OrderID) > 10;
