GATE CS 2012 Q.15 (CS Q.15): SQL Queries and Integrity Constraints

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

$P$ and $R$

$P$ and $S$

$Q$ and $R$

$Q$ and $S$

Official answer and solution

Official answer: C
From: Official GATE 2012 answer key

Note on this question

Official GATE 2012 key: C (Q and R). This question is widely disputed: in standard SQL a HAVING clause may be used without GROUP BY (the whole table is one group) and GROUP BY columns need not appear in SELECT, which would make P and S true. Graded here as per the official key.

Solution

Contrast the pedagogical relational textbook definition of the HAVING clause with modern ANSI SQL query syntax rules, explaining the official answer key rationale.

Official key perspective (traditional textbook SQL teaching convention):

Under classical database textbook pedagogy, the HAVING clause is presented strictly as a filter applied to groups created by a GROUP BY clause. Under this view, statement $Q$ ('An SQL query can contain a HAVING clause only if it has a GROUP BY clause') was considered true, and $P$ was deemed false.
Furthermore, early teaching conventions often held that attributes specified in GROUP BY must be projected in the SELECT list (statement $R$ ) so that the grouped output relation is meaningful to the user.
Following this textbook curriculum convention, the official answer key for GATE 2012 chose option (C), which asserts $Q$ and $R$ as true.

Modern ANSI SQL standard and DBMS implementation reality:

Statement $P$ : In standard SQL, a HAVING clause can appear without a GROUP BY clause. When omitted, the entire relation is treated as a single group (for example, SELECT AVG(salary) FROM employees HAVING COUNT(*) > 10; is completely valid SQL).
Statement $S$ : An attribute in the GROUP BY list does not need to appear in the SELECT clause (for instance, SELECT dept_id, COUNT(*) FROM emp GROUP BY dept_id, job; is standard compliant).
Under standard SQL, statements $P$ and $S$ are actually true (Option B). However, candidate responses are graded against the official key choice (C).

Final answer

The answer is the one in the official GATE answer key. The solution is this site's own explanation.

Source

Paper: GATE 2012 Computer Science and Information Technology (CS)
Question: 15 of 65, printed as CS Q.15
Section: Computer Science and Information Technology

Report an error in this question

GATE questions are the work of the organising institutes. This site is independent and is not affiliated with them.

Question

Solution

Source