Answer (C)
According to standard SQL answer should be option (C) which is answer key given by GATE authority.
If we talk about different SQL implementations like MySQL, 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, try 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
Alternative way –
Statement (P) “An SQL query can contain a HAVING clause even if it does not have a GROUP BY clause” is correct because Having caluse is applied after the aggregation phase and must be used if you want to filter aggregate results and Having doesn’t require Group By clause. A HAVING clause without a GROUP BY clause is valid and (arguably) useful syntax in Standard SQL. Consider this example, which is valid Standard SQL:
SELECT 'T' AS result
FROM Book
HAVING MIN(NumberOfPages) < MAX(NumberOfPages);
Statement (S) “Not all attributes used in the GROUP BY clause need to appear in the SELECT clause” is correct but if we use Group By clause must, there are limitations on what we can put into the Select clause.
