GATE | GATE CS 2012 | Question 15

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


Answer: (B)

Explanation: 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.


Quiz of this Question



My Personal Notes arrow_drop_up

Recommended Posts:



0 Average Difficulty : 0/5.0
No votes yet.






User Actions