4k 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

(A) P and R
(B) P and S
(C) Q and R
(D) Q and S

retagged | 4k views

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"

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 (332k points)
selected by

(d)q and s

select statement need not have all the attributes used in group by
No. That is not true. group by is done after select. So, if select doesn't return a column, we cannot use that column in group by.

Since this is GATE 2012 question, you can see the published GATE key to confirm it.
http://gatecse.in/w/images/b/b5/Key_CS_2012.pdf
thanks for clearing that, discussion is healthy for all of us :D

is it the same goes for "order By"?

Yes- discussions are healthy. I just did a bit more research and  this is the best link I found.

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 correct than 'R' or both are debatable and marks should have been given to all. Already 10/100 marks were given free in this exam (the worst I have seen of all GATE exams) because of such questions and this should have been added to that.

Similarly, for order by answer is "No". We cannot use any attribute in order by which are not in select. But in many DBMS it works.

http://dba.stackexchange.com/questions/34951/order-by-clause-is-allowed-over-column-that-is-not-in-select-list

so for the question like this we would get mark if we attempt or if we leave it blank ?
For this question, mark was given only for (C) choice probably because no one debated for it. But for all debated questions marks will be givel to all whether attempted or left blank.
the same argument goes for Having clause as well :) so it could be B as well..for sure they should have given marks to all
According to the link given, group by is evaluated before select. http://tinman.cs.gsu.edu/~raj/sql/node22.html Is this not correct ? What's the conceptual order of evaluation then ?
thats correct. But here question is not asking evaluation order..
In one of your comments above, you explained that - " Group by is done after select. So, if select doesn't return a column, we cannot use that column in group by ". So, isn't this false ?
I have given more references :) It is debatable. A is more correct as per theory and B is more correct as per current DBMS'.

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

Well, if implementation matters:

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


Correct Order :

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

@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.

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

So are we safe to use the concepts of q and r options in future GATE exams although p and s are more accurate?
• 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.

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 Boss (7.9k points)
Is it that we have to abide by sql 92/99 for GATE?

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.3k points)