You work as the database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. All servers on the Domain.com network run Windows Server 2003 and all clients run Windows XP Professional.
The Domain.com network hosts a database that is used to store news stories. You need to run a query that returns the number of new stories in each category and subcategory that have been added in the last 7 days. Your stored procedure must also return the name of the subcategory and the category to which it belongs.
What query should you use?
A.
SELECT c.CategoryName, c.SubcategoryName, COUNT(s.SubcategoryID) FROM Samples AS s INNER JOIN Categories AS c
ON sSubcategoryID = c.SubcategoryID
WHERE DateDiff("day", s.Date, GETDATE()) <= 7
GROUP BY c.CategoryName, c.SubcategoryName
ORDER BY c.CategoryName, c.SubcategoryName
B.
SELECT c.CategoryName, c.SubcategoryName, COUNT(s.SubcategoryID) FROM Samples AS s INNER JOIN Categories AS c
ON sSubcategoryID = c.SubcategoryID
WHERE DateDiff("week", s.Date, GETDATE()) <= 1
GROUP BY c.SubcategoryName
ORDER BY c.SubcategoryName, c.CategoryName
C.
SELECT c.CategoryName, c.SubcategoryName, COUNT(s.SubcategoryID) FROM Samples AS s INNER JOIN Categories AS c
ON sSubcategoryID = c.SubcategoryID
WHERE DateDiff("week", s.Date, GETDATE()) <= 1
GROUP BY c.CategoryName, c.SubcategoryName
ORDER BY c.CategoryName, c.SubcategoryName
D.
SELECT c.CategoryName, c.SubcategoryName, COUNT(s.SubcategoryID) FROM Samples AS s INNER JOIN Categories AS c
ON sSubcategoryID = c.SubcategoryID
WHERE DateDiff("day", s.Date, GETDATE()) <= 7
ORDER BY c.CategoryName, c.SubcategoryName
Explanation:
The COUNT aggregate function will return the number of non-null items within the result set based on the GROUP BY clause. To only retrieve the sample chapters that have been added in the last 7 days, you should use the DateDiff function to limit the result set to 7 days.
Incorrect Answers:
B: The DateDiff function will return sample chapters that have been added either this week or last week rather than the last 7 days. Also, when the COUNT aggregate function is used, the columns referenced in the SELECT statement must appear in the GROUP BY clause or a syntax error will occur.
C: The DateDiff function will return sample chapters that have been added either this week or last week rather than the last 7 days.
D: When the COUNT aggregate function is used, the columns referenced in the SELECT statement must appear in the GROUP BY clause or a syntax error will occur.