edited by
39,681 views
65 votes
65 votes

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 by

6 Answers

Best answer
69 votes
69 votes

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. 
edited by
16 votes
16 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

2 votes
2 votes

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

 

1 votes
1 votes

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.

Answer:

Related questions

48 votes
48 votes
4 answers
1
go_editor asked Apr 21, 2016
13,582 views
Consider the following relations $A, B$ and $C:$ $$\overset{\textbf{A}}{\begin{array}{|c|c|c|}\hline\\\textbf{Id}& \textbf{Name}& \textbf{Age} \\\hline12& \text{A...
9 votes
9 votes
4 answers
4
gatecse asked Sep 29, 2014
2,662 views
Given the sequence of terms, $\text{AD CG FK JP}$, the next term is$\text{OV}$$\text{OW}$$\text{PV}$$\text{PW}$