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

edited | 10.8k views
+8
In Raghu Ramkrishnan book, Pg 155:

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

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.
by
edited
0

(d)q and s

select statement need not have all the attributes used in group by
+1
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
0
thanks for clearing that, discussion is healthy for all of us :D

is it the same goes for "order By"?
+2

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

0
so for the question like this we would get mark if we attempt or if we leave it blank ?
0
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.
0
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
+1
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 ?
+1
thats correct. But here question is not asking evaluation order..
+1
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 ?
+2
I have given more references :) It is debatable. A is more correct as per theory and B is more correct as per current DBMS'.
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
+1

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

+2
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?
0
preparing for gate means we're studying old technology, at least before 1990.

that's why it's better to stick onto theory, don't follow any link on Internet because they've updated technology information. after gate no problem to research about advanced technology.
0
If you post rubbish things, you will get blocked. GATE is about concepts -- and doesnt involve any implementation things as should any standard exam. For example, it has C language and none of the question will violate C11 standard which is the latest one but again it wont have any implementation specific things as done by any specific compiler which is in addition to the standards. But yes, following unauthorized Internet links are harmful for GATE.
0
With due respect Sir, I don't want to post rubbish things & don't want that rubbish things posted by others.because I'm using this forum as my GATE preparation resource.

I posted this from my mind's perspective.

If such questions will be asked in Gate & suppose in best case I know all the scenario (like here), then which path I should follow. This will create a confusion for even an easy question. Here I think they ask this question from theory perspective( I mean before updation). I just want to tell that - knowing How much they want from us is important.

If my comment violate any rules in this forum then I'm happy to delete this, but please don't block me.
0
option c is correct

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

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.

+2
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

According to Standard SQL,

The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups. This GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause. The attributes used in GROUP BY clause must present in SELECT statement.

The HAVING Clause enables you to specify conditions that filter which group results appear in the results. The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause. So, we cannot use HAVING clause without GROUP BY clause.

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

0
option S is true, if they would tell this along with - the attributes which are not present in group by, must present in any aggregate function not mandatorily with Select Clause.