Which SQL query should you use?

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 sum the monthly sales of each product.
Which SQL query should you use?
A. 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;
B. 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 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);
D. . SELECT c.CustomerName, AVG(p.SalePrice) AS Sales
FROM product p
INNER JOIN complaint com ON p.ProductID = com.ProductID
INNER JOINcustomer c ON com.CustomerID = c.CustomerID
WHERE com.ComplaintDate > ’09/01/2011′
GROUP BY c.CustomerName
HAVING 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 CUBE(p.ProductName, DATEPART(mm, com.ComplaintDate));
F. SELECT c.CustomerName, COUNT(com.ComplaintID) AS complaints
FROM customer c
INNER JOIN complaint com ON c.CustomerID = com.CustomerID
GROUP BYc.CustomerName
HAVING COUNT(com.ComplaintID) > 10;
G. 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
H. 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), ());
I. 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;
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 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.