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 return all customer names and total number of orders for customers who have placed more than 10 orders.
Which SQL query should you use?
A. SELECT c.CustomerName,AVG(p.SalePrice) AS Sales
FROM product p
INNER JOIN[order] o ON p.ProductID = o.ProductID INNER JOINcustomer c ON o.CustomerID = c.CustomerID
WHERE o.OrderDate > ’09/01/2011′
GROUP BY c.CustomerName
HAVING AVG(p.SalePrice) >= 500
B. SELECT c.CustomerName,AVG(p.SalePrice) AS Sales
FROM product p
INNER JOIN[order] o ON p.ProductID = o.ProductID
INNER JOINcustomer c ON o.CustomerID = c.CustomerID
WHERE o.OrderDate > ’09/01/2011′ AND AVG(p.SalePrice) >= 500
C. 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 p.ProductName, DATEPART(mm, o.OrderDate);
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 = c.CustomerID
GROUP BY GROUPING SETS ((c.CustomerName), (p.ProductName), ());
E. 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;
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(p.ProductName, DATEPART(mm, o.OrderDate));
G. 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;
H. 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 p.ProductName, OrderMonth;
I. 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;
J. 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 = c.CustomerID
GROUP BY GROUPING SETS ((c.CustomerName, p.ProductName), ());
