-
Which of the following statements are true about an SQL query?
P. An SQL query can contain HAVING clause even if it does not have a GROUP BY clause
Q. An SQL query can contain HAVING clause only if it does not have 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
-
- P and R
- P and S
- Q and R
- Q and S
- P and R
Correct Option: C
According to standard SQL answer should be C. Refer: If we talk about different SQL implementations like My SQL, then option B is also right. But in question they seem to be talking about standard SQL not about implementation. For example below is a P is correct in most of the implementations. HAVING clause can also be used with aggregate function. If we use a HAVING clause without a GROUP BY clause, the HAVING condition applies to all rows that satisfy the search condition. In other words, all rows that satisfy the search condition make up a single group. See this for more details.
S is correct. To verify S is right, try the following queries in SQL:
CREATE TABLE temp
(
id INT,
name VARCHAR(100)
);
INSERT INTO temp VALUES (1, "abc");
INSERT INTO temp VALUES (2, "abc");
INSERT INTO temp VALUES (3, "bcd");
INSERT INTO temp VALUES (4, "cde");
SELECT Count(*)
FROM temp
GROUP BY name;
Output: count(*)
-------------
2
1
1
