Which SQL query should you use?

Note: This question is part of a series of questions that use the same set of answer choices. Each answer choice may be used once, more than once, or not at all.
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 return all customer names and total number of complaints for customers who have made more than 10 complaints.
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,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
C. 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
D. 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;
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 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);
G. 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), ());
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(p.ProductName, DATEPART(mm, com.ComplaintDate));
I. 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;

microsoft-exams

Leave a Reply

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.