The employee table contains these columns:
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
SALARY NUMBER(7,2)
You need to display the names of employees on more than an average salary of all employees. Evaluate the SQL statement.
SELECT last_name, first_name FROM employee WHERE salary < AVG(salary);
Which change should you make to achieve the desired results?
A: Change the function in the Where clause.
B: Move the function to the select clause and add a group clause.
C: Use a sub query in the where clause to compare the average salary value.
D: Move the function to the select clause and add a group by clause and a having clause.
ANSWER: C
EXPLANATION: Answer C shows the correct way to change query, because function AVG can not be used in WHERE clause.
INCORRECT ANSWERS:
A: Usage of function AVG is correct.
B: This query does not require grouping to extract correct information from the table.
D: This query does not require to use GROUP BY and HAVING clauses to extract correct information from table.
