1z0-071 Exam - Question 47

Question

Examine the description of the EMPLOYEES table:Which statement will fail?A. B. C. D.

Examice · Accepted 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.

MZEnuovazelanda · Answer

It's a mess! the first three are incorrect for the semicolon

darkma · Answer

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.

karols32 · Answer

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.

fgramos · Answer

D is the right answer. Works fine and AB and C are incorrect by semicolon

zouve · Answer

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

JCKOMI · Answer

A IS NOT correct for semicolon and also count is not applied to where clause

Ronit_8055 · Answer

ABC are incorrect for semicolon and even for HVAING should comes after GROUP BY clause.

Orxan_H · Answer

A B are incorrect answers

MIGHTYSTUNNERS · Answer

C is wrong Because HAVING Clause come under group by clause

m_darwish · Answer

Ignore semicolon , A will fail .

alelejaja · Answer

A is the correct answer

1z0-071 Exam - Question 47

Discussion