+26 votes
7.3k views

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

1. P and R
2. P and S
3. Q and R
4. Q and S
asked
edited | 7.3k views
+3
In Raghu Ramkrishnan book, Pg 155:

"If GROUP BY is ommitted, the entire table is regarded as a single group."

## 3 Answers

+35 votes
Best answer

GATE 2012 Answer key is (C) $Q$ and $R$ are true.

But correct answer should be B.

• When group by is not present, having is applied to the whole table
"A grouped table is a set of groups derived during the evaluation of a <group by clause> or a <having clause>. A group is a multiset of rows in which all values of the grouping column or columns are equal if a <group by clause> is specified, or the group is the entire table if no <group by clause> is specified. A grouped table may be considered as a collection of tables. Set functions may operate on the individual tables within the grouped table."

This shows that P is indeed correct.

Also see "having clause section"

http://searchsqlserver.techtarget.com/answer/ISO-ANSI-SQL-and-the-GROUP-BY-clause

The above link says that all columns used in group by must be present in select clause as per SQL-92 standard but later standards doesn't enforce it. I tried this on MySQL and it works. It is allowed in MSSQL also- see below link.

From Microsoft (obviously applicable only to MS-SQL)

http://msdn.microsoft.com/en-us/library/ms177673.aspx

Expressions in the GROUP BY clause can contain columns of the tables, derived tables or views in the FROM clause. The columns are not required to appear in the SELECT clause <select> list.

Each table or view column in any nonaggregate expression in the <select> list must be included in the GROUP BY list:

So, as per standard it is not allowed, but in most current DBMS it is allowed. And there is no reason why this shouldn't be allowed. So, ideally 'S' is more correct than 'R' or both are debatable and marks should have been given to all.
answered by Veteran (384k points)
edited
0

I think standard implementation of SQL are not aggreeing with you.

+2

Well, if implementation matters:

mysql> select count(created) from qa_users having count(*) > 0;
+----------------+
| count(created) |
+----------------+
|          20790 |
+----------------+
1 row in set (0.01 sec)

+2
Correct Order :

FROM->ON->WHERE->GROUPBY->HAVING->SELECT->DISTINCT->ORDERBY
0

@Arjun

As per the rule, Any Attribute that is present in the having clause without being aggregated must appear in the group by clause, otherwise query is erroneous.

And you query only worked because it's not violating the rule mentioned above.

0

Udit Gupta 1 First FROM then ON then WHERE operation is performed, and so on is it right?

0
So are we safe to use the concepts of q and r options in future GATE exams although p and s are more accurate?
0
• This isn't correct.The order of sequence goes something like this:
• First joins are performed(if any).
• Then Where is used to filter out rows from the naive cartesian product.
• After that,group by is used to group the rows based on some attribute using the aggregate function(some databases will select random value if aggregate is  not mentioned)
• Then select which is essentially projecting some column of the resultant relation.
0
@Arjun sir.From the answer it is very clear that answer must be $B$ .

But i am confused regarding ISRO.If this question comes in ISRO we have 2 choice -:

$1$ mark option $C$ .

$2$ mark option $B$ and if the key says $C$, we can challenge it .

But i am afrad that will ISRO accept our challenge using the above link(references).

Please help
0
This type of questions should not be asked further because various standards have various conventions.
0
@Arjun Sir, I tried few queries similar to what you implemented on the DB mentioned in Silberschatz book. My queries were:

Q1: select count(id) from takes having count(*) > 0

Q2: SELECT MIN(id) AS mina, MAX(id) As maxa FROM takes HAVING MIN(id) < MAX(id)

The results of both the queries with and without the having clause were same, which means the having clause is redundant in such cases. Can you give an example where having is used without group by and the result is different?
+6 votes

SQL 92/99: having clause is allowed to use only if group by clause exists. // (for gate )

ORACLE SQL /MY SQL: having clause is allowed even group by not exists. if group by clause exists not exists each record treated as one record. // (for interview)

so Q is true.

if group by clause is based on" X " set of attributes, then select clause must select all all attributes of "X "set.

otherwise problem of ambiguity occurs.

SELECT A

FROM R

GROUP BY(A,B)

R

 A B 2 3 2 3 2 4 2 4 3 4 3 4
 A 2 2 3

ambiguity is present .

so R is true.

C is correct ans

answered by Active (4.7k points)
0
Is it that we have to abide by sql 92/99 for GATE?
+1 vote

P : An SQL query can contain a HAVING clause even if it does not have a GROUP BY clause - > yes
Q : An SQL query can contain a HAVING clause only if it has a GROUP BY clause                   - > It doesn't matter
R : All attributes used in the GROUP BY clause must appear in the SELECT clause                  -> NO
S : Not all attributes used in the GROUP BY clause need to appear in the SELECT clause        -> yes

Introduction to SQL HAVING clauseHAVING condition; The HAVING clause works like the WHEREclause if it is not used with the GROUP BY clause. The difference between the HAVING clause and the WHERE clause is that the WHERE clause is used to filter rows, while the HAVING clause is used to filter groups of rows.

Expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY Clause at the end of the SQL statement. This is an aggregate function such as the SUM, COUNT, MIN, MAX, or AVG functions. This is the column or expression that the aggregate_function will be used on.

answered by Active (2.8k points)
+1
According to standard SQL, SQL query can HAVING clause only if it has GROUP BY clause.

So statement Q is TRUE

Similarly statement R "All the attributes used GROUP BY clause need to appear in in SELECT clause " is TRUE for standard SQL.

So the correct option is (C)

But the above statement is voileted for the ORACLE & MYSQL software because they have modified it to make it more convenient for user.

So in real world option (B) is TRUE But for the gate prospective standard SQL has to be followed so option C is correct
Answer:

+18 votes
2 answers
1
+20 votes
2 answers
2
+3 votes
4 answers
3
+3 votes
3 answers
4
+16 votes
5 answers
5
+12 votes
3 answers
6
+17 votes
2 answers
7