0

I came across this question yesterday. I am looking for an explanation for just one point.

Which of the following statements are TRUE about an SQL query?

P : An SQL query can contain a HAVING clause even if it does not have a GROUP BY clause

Q : An SQL query can contain a HAVING clause only if it has a GROUP BY clause

R : All attributes used in the GROUP BY clause must appear in the SELECT clause

S : Not all attributes used in the GROUP BY clause need to appear in the SELECT clause

(A) P and R.
(B) P and S.
(C) Q and R.
(D) Q and S.

I selected A as an answer. But actually B is the correct answer.

I want to know that some websites mention R is correct, whereas others say S is correct. Which statement is correct between the last two?

0

1 Answer 1

5

"R" is incorrect because that is saying every field being grouped on also needs to be selected which is definitely not true. The fields in a GROUP BY clause are what define how to collapse a set of rows related by those fields. But there's no reason you have to return those same fields in the SELECT list. If you wanted to aggregate on a non-grouped field, you're not required to also return the grouped fields. For example the following is a valid query:

SELECT SUM(Price) AS TotalAmountSold
FROM SalesOrders
GROUP BY ProductName

Of course, there's not much meaning in the results of that query because it'll just return a list of total amounts without the actual ProductName associated with them. So there's no telling which total goes to which product.

But the fields in a GROUP BY clause may be added to the SELECT list (without any aggregation function) like so:

SELECT ProductName, SUM(Price) AS TotalAmountSold
FROM SalesOrders
GROUP BY ProductName

This makes for a meaningful query. But in summary the reason why "R" is incorrect is because it says "must" which isn't true, rather they may appear in the SELECT list.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.