Consider a database table R with attributes A and B. Which of the following…
2016
Consider a database table R with attributes A and B. Which of the following SQL queries is illegal ?
- A.
SELECT A FROM R;
- B.
SELECT A, COUNT(*) FROM R;
- C.
SELECT A, COUNT(*) FROM R GROUP BY A;
- D.
SELECT A, B, COUNT(*) FROM R GROUP BY A, B;
Attempted by 631 students.
Show answer & explanation
Correct answer: B
Answer: The query "SELECT A, COUNT(*) FROM R;" is illegal in standard SQL.
Reason: Standard SQL requires that every column in the SELECT list is either inside an aggregate function or appears in the GROUP BY clause. The query mixes the aggregate COUNT(*) with the non-aggregated column A but does not include a GROUP BY clause, so it is not allowed.
"SELECT A FROM R;" — Valid: selects a single column without aggregation.
"SELECT A, COUNT(*) FROM R;" — Illegal: mixes an aggregate with a non-aggregated column without GROUP BY. To fix, either remove A, aggregate A (e.g., MAX(A)), or add GROUP BY A.
"SELECT A, COUNT(*) FROM R GROUP BY A;" — Valid: groups by A so COUNT(*) is computed per A.
"SELECT A, B, COUNT(*) FROM R GROUP BY A, B;" — Valid: groups by A and B so the aggregate is computed per (A, B) pair.
Note: Some DBMS extensions allow the illegal form (for example, older MySQL configurations that disable ONLY_FULL_GROUP_BY) by returning nondeterministic values for the non-aggregated column. Relying on that behavior is non-portable; prefer the standard-compliant form.