You are a database developer writing reports for a sales management application. A customer table has the following definition:
CREATE TABLE customer
(CustomerID INT,
FirstName VARCHAR(30),
LastName VARCHAR(50),
StreetAddress VARCHAR(100),
City VARCHAR(100),
[State] VARCHAR(25),
PostalCode VARCHAR(5));
An order table has the following definition:
CREATE TABLE [order]
(OrderID INT,
ProductID INT,
CustomerID INT,
OrderDate DATETIME);
You need to write a report that contains the following columns:
You also need to ensure that the report meets the following requirements:
•Contains only customers who have placed orders
•Contains only customers who have Postal Codes beginning with 89
•Returns only one record for each customer
•The report is ordered by the CustomerID in ascending order
Which Transact-SQL query should you use? (Choose 2 – Complete)
A. SELECT c.CustomerID,c.FirstName + ‘ ‘ + c.LastName AS FullName,c.PostalCode,COUNT(*) AS
OrderCount,MIN(o.OrderDate) AS EarliestOrderDate
FROM Customer c
INNER JOIN [order] o ON CustomerId = o.CustomerId
WHEREc.PostalCode LIKE ‘89%’
GROUP BY c.CustomerId, c.FirstName + ‘ ‘ + c.LastName, c.PostalCode, o.CustomerId
ORDER BY c.CustomerID
B. SELECT c.CustomerID,c.FirstName + ‘ ‘ + c.LastName AS FullName,c.PostalCode,COUNT(*) AS
OrderCount,MIN(o.OrderDate) AS EarliestOrderDate
FROM Customer c
INNER JOIN [order] o ON CustomerId = o.CustomerId
WHERE c.PostalCode = ‘89%’
GROUP BY c.CustomerId, c.FirstName + ‘ ‘ + c.LastName, c.PostalCode, o.CustomerId
ORDER BY FullName
C. WITH FullNames AS
(SELECT CustomerId,FirstName + ‘ ‘ + LastName AS FullName
FROM customer)
SELECT c.CustomerID,fn.FullName,c.PostalCode,COUNT(*) AS
OrderCount,MIN(o.OrderDate) AS EarliestOrderDate
FROM Customer c
INNER JOIN FullNames fn ON fn.CustomerID = c.CustomerID
INNER JOIN [order] o ON c.CustomerId = o.CustomerId
WHERE CustomerID PostalCode LIKE ‘89%’
GROUP BY c.CustomerId, fn.FullName, c.PostalCode, o.CustomerId
ORDER BY c.
D. SELECT c.CustomerID,c.FirstName + ‘ ‘ + c.LastName AS FullName,c.PostalCode,COUNT(*) AS
OrderCount,MIN(o.OrderDate) AS EarliestOrderDate
FROM Customer c
LEFT OUTER JOIN [order] o ON c.CustomerId = o.CustomerId
WHERE c.PostalCode LIKE ‘89%’
GROUP BY c.CustomerId, FirstName + ‘ ‘ + c.LastName, c.PostalCode, o.CustomerId
ORDER BY c.CustomerID
USING FullNames AS
( SELECTCustomerId,FirstName + ‘ ‘ + LastName AS FullName
FROM customer)
SELECT c.CustomerID,fn.FullName,c.PostalCode,COUNT(*) AS
OrderCount,MIN(o.OrderDate) AS EarliestOrderDate
FROM Customer c
INNER JOIN FullNames fn ON fn.CustomerID = c.CustomerID
INNER JOIN [order] o ON c.CustomerId = o.CustomerId
WHERE PostalCode LIKE ‘89%’
GROUP BY c.CustomerId, fn.FullName, c.PostalCode, o.CustomerId
ORDER BY c.CustomerID
E. SELECT c.CustomerID,c.FirstName & ‘ ‘ & c.LastName AS FullName,c.PostalCode,COUNT(*) AS
OrderCount,MIN(o.OrderDate) AS EarliestOrderDate
FROM Customer c
INNER JOIN [order] o ON CustomerId = o.CustomerId
WHERE c.PostalCode LIKE ‘89%’
GROUP BY c.CustomerId, FirstName & ‘ ‘ & c.LastName, c.PostalCode, o.CustomerId
ORDER BY c.CustomerID
F. SELECT c.CustomerID,c.FirstName + ‘ ‘ + c.LastName AS FullName,c.PostalCode,COUNT(*) AS
OrderCount,MIN(o.OrderDate) AS EarliestOrderDate
FROM Customer c
INNER JOIN [order] o ON CustomerID CustomerId = o.CustomerId
WHEREc.PostalCode LIKE ‘89%’
ORDER BY c.
G. WITH FullNames AS
( SELECTCustomerId,FirstName + ‘ ‘ + LastName AS FullNam
FROM customer)
SELECTc.CustomerID,fn.FullName,c.PostalCode,COUNT(*) AS
OrderCount,MIN(o.OrderDate) AS EarliestOrderDate
FROM Customer c
INNER JOIN FullNames fn ON fn.CustomerID = c.CustomerID
INNER JOIN [order] o ON c.CustomerId = o.CustomerId
GROUP BY c.CustomerId, fn.FullName, c.PostalCode, o.CustomerId
ORDER BY c.CustomerId
