You work as the database administrator at an investment company named Domain.com. Domain.com has its headquarters in New York and branch offices in Boston, Atlanta, Miami, and New Orleans. The Domain.com network contains a SQL Server 2005 database server named Certkiller -DB01 that is located at headquarters.
Certkiller -DB01 contains a large database named CK_Investments. The CK_Investments database stores investment data for each office. You need to implement a stored procedure that will return the average length of an investment at each office.
What Transact SQL query should you use?
A.
SELECT o.City, AverageInvestmentPeriod =
AVG(DateDiff(day,i.StartDate,i.EndDate))
FROM Investments AS i
INNER JOIN Offices AS o
ON o.OfficeID = i.OfficeID
GROUP BY o.OfficeID
B.
SELECT o.City, AverageInvestmentPeriod =
AVG(DateDiff(day,i.StartDate,i.EndDate))
FROM Investments AS i
INNER JOIN Offices AS o
ON o.OfficeID = i.OfficeID
GROUP BY o.City
C.
SELECT o.City, AverageInvestmentPeriod = AVG(i.StartDate – i.EndDate) FROM Investments AS i
INNER JOIN Offices AS o
ON o.OfficeID = i.OfficeID
GROUP BY o.OfficeID
D.
SELECT o.City, AverageInvestmentPeriod = AVG(i.StartDate – i.EndDate) FROM Investments AS i
INNER JOIN Offices AS o
ON o.OfficeID = i.OfficeID
GROUP BY o.City
Explanation:
The DateDiff function can be used to return the length of an investment period and the AVG aggregate function can be used to average the length of an investment period returned by the DateDiff function. When you use the AVG aggregate function, you can only include the aggregate and columns in the GROUP BY clause; therefore you need to group by city name.
Incorrect Answers:
A: When you use the AVG aggregate function, you can only include the aggregate and columns in the GROUP BY clause; therefore you cannot group by o.CityID as the o.CityID column in not retrieved by the SELECT statement.
C: You cannot use the minus operator on a datetime or smalldatetime column.
D: You cannot use the minus operator on a datetime or smalldatetime column. Also, when you use the AVG aggregate function, you can only include the aggregate and columns in the GROUP BY clause; therefore you cannot group by o.CityID as the o.CityID column in not retrieved by the SELECT statement.