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: (C)

Explanation: According to standard SQL answer should be C. Refer

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


Quiz of this Question

GATE CS Corner    Company Wise Coding Practice

Recommended Posts:



0 Average Difficulty : 0/5.0
No votes yet.