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 identify all customers who have ordered for an average amount of more than 500 or more from September 01, 2011.
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 p.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 p.ProductID = o.ProductID
INNER JOIN customer c ON o.CustomerID = c.CustomerID
WHERE o.OrderDate > ’09/01/2011′
GROUP BY c.CustomerName
HAVING AVG(p.SalePrice) >= 500
D. 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));
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 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;
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 p.ProductName, OrderMonth;
H. SELECT c.CustomerName,p.ProductName,SUM(p.SalePrice) AS Sales
FROM product p
INNER JOIN [order] o ON p.ProductID = o.ProductID
INNER JOINcustomer c ON o.CustomerID = c.CustomerID
GROUP BY GROUPING SETS ((c.CustomerName), (p.ProductName), ());
I. 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), ());
J. SELECT c.CustomerName,AVG(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
WHERE o.OrderDate > ’09/01/2011′ AND AVG(p.SalePrice) >= 500
