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 used to support a customer service application.
You create tables named complaint, customer, and product as follows:
CREATE TABLE [dbo].[complaint] ([ComplaintID] [int], [ProductID] [int], [CustomerID] [int], [ComplaintDate] [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 complained about products that have an average sales price of 500 or more from September 01, 2011.
Which SQL query should you use?
A. SELECT c.CustomerName,p.ProductName,SUM(p.SalePrice) AS Sales
FROM product p
INNER JOIN complaint com ON p.ProductID = com.ProductID
INNER JOIN customer c ON com.CustomerID = c.CustomerID
GROUP BY GROUPING SETS ((c.CustomerName, p.ProductName), ());
B. SELECT c.CustomerName,p.ProductName,SUM(p.SalePrice) AS Sales
FROM product p
INNER JOIN complaint com ON p.ProductID = com.ProductID
INNER JOIN customer c ON com.CustomerID = c.CustomerID
GROUP BY GROUPING SETS ((c.CustomerName), (p.ProductName), ());
C. SELECT c.CustomerName,COUNT(com.ComplaintID) AS Complaints
FROM customer c
INNER JOIN complaint com ON c.CustomerID = com.CustomerID
WHERE COUNT(com.ComplaintID) > 10
GROUP BY c.CustomerName;
D. SELECT c.CustomerName,AVG(p.SalePrice) AS Sales
FROM product p
INNER JOIN complaint com ON p.ProductID = com.ProductID
INNER JOIN customer c ON com.CustomerID = c.CustomerID
WHERE com.ComplaintDate > ’09/01/2011′
AND AVG(p.SalePrice) >= 500
E. SELECT p.ProductName,DATEPART(mm, com.ComplaintDate) ComplaintMonth,SUM(p.SalePrice) AS Sales
FROM product p
INNER JOIN complaint com ON p.ProductID = com.ProductID
GROUP BY p.ProductName, ComplaintMonth;
F. SELECT c.CustomerName,COUNT(com.ComplaintID) AS complaints
FROM customer c
INNER JOIN complaint com ON c.CustomerID = com.CustomerID
GROUP BY c.CustomerName
HAVING COUNT(com.ComplaintID) > 10;
G. SELECT p.ProductName,DATEPART(mm, com.ComplaintDate) ComplaintMonth,SUM(p.SalePrice) AS Sales
FROM product p
INNER JOIN complaint com ON p.ProductID = com.ProductID
GROUP BY CUBE(p.ProductName, DATEPART(mm, com.ComplaintDate)); , ComplaintMonth;
H. SELECT p.ProductName,DATEPART(mm, com.ComplaintDate) ComplaintMonth,SUM(p.SalePrice) AS Sales
FROM product p
INNER JOIN complaint com ON p.ProductID = com.ProductID
GROUP BY CUBE;.CustomerName,COUNT(com.ComplaintID) AS complaints
FROM customer c INNER
JOIN complaint com ON c.CustomerID = com.CustomerID
GROUP BY c.CustomerName
HAVING COUNT(com.ComplaintID) > 10;
I. SELECT c.CustomerName, AVG(p.SalePrice) AS Sales
FROM product p
INNER JOIN complaint com ON p.ProductID = com.ProductID
INNER JOIN customer c ON com.CustomerID = c.CustomerID
WHERE com.ComplaintDate > ’09/01/2011′
GROUP BY c.CustomerName
HAVING AVG(p.SalePrice) >= 500
J. . SELECT p.ProductName,DATEPART(mm, com.ComplaintDate) ComplaintMonth,SUM(p.SalePrice)
AS Sales
FROM product p
INNER JOIN complaint com ON p.ProductID = com.ProductID
GROUP BY p.ProductName, DATEPART(mm, com.ComplaintDate);.ProductName,DATEPART(mm, com.ComplaintDate) ComplaintMonth,SUM
(p.SalePrice)
AS Sales
FROM product p
INNER JOIN complaint com ON p.ProductID = com.ProductID
GROUP BY CUBE;
