Examine the description of the EMPLOYEES table:
Which statement will fail?
A.
B.
C.
D.
Exam 1z0-071 All QuestionsBrowse all questions from this exam
Question 47
Correct Answer:
The only clause in a SQL statement that may include aggregate functions, such as COUNT(*), is the HAVING clause, and they must be used after the GROUP BY clause if the query includes a grouped aggregation. What is more, semicolons are typically used to signify the end of a SQL statement and would not generally indicate correctness or errors within a SQL query itself. Statements A, B, and C will fail for different reasons related to their structure and placement of clauses. In statement A, usage of an aggregate function, COUNT(*), directly within a WHERE clause is syntactically incorrect, as the WHERE clause cannot handle aggregate functions. Statements B and C misplace the HAVING clause relative to their other components, failing generally due to SQL syntax rules. Therefore, D is the appropriate answer, as it employs WHERE for the initial condition, groups appropriately, and applies HAVING for aggregated filtering after the grouped data is espoused.
Discussion
darkma
The first query is syntactically incorrect. You cannot use the COUNT function in the WHERE clause. The second query is also incorrect. The HAVING clause is used to filter the results of an aggregation, and it should be used after the GROUP BY clause, not after the WHERE clause. The third query is also incorrect for the same reason as the second one. The HAVING clause should come after the GROUP BY clause. The fourth query is correct and will work as expected. It filters rows where the department_id is not equal to 90 using the WHERE clause, groups the remaining rows by department_id, and then applies the HAVING clause to count the rows within each group and filter out groups where the count is greater than or equal to 3.
NSilva
I tried it out in Oracle's Apex (Db v19C), using a data set from the internet. SELECT id FROM TITANIC HAVING ID = 1 GROUP BY ID; It does not affect the result if you use HAVING and GROUP BY in a different position from one to another.
MZEnuovazelanda
It's a mess! the first three are incorrect for the semicolon
zouve
A if you correct the semicolon to each and every query A the one that is failing with ORA-00934: group function is not allowed here
fgramos
D is the right answer. Works fine and AB and C are incorrect by semicolon
yanoolthecool
Agreed, that is if we assume the question is 'which statement will NOT fail'
karols32
Don't pay attention about semicolon and the answer is A. Order of HAVING and GROUP BY doesn't matter. WHERE is not required in statement.
alelejaja
A is the correct answer
m_darwish
Ignore semicolon , A will fail .
MIGHTYSTUNNERS
C is wrong Because HAVING Clause come under group by clause
Orxan_H
A B are incorrect answers
Ronit_8055
ABC are incorrect for semicolon and even for HVAING should comes after GROUP BY clause.
TheOracleWasTaken
I think it works regardless, probably not correct but it works
JCKOMI
A IS NOT correct for semicolon and also count is not applied to where clause