View the Exhibit and examine the structure of the employees table.
Execute the following block of code:
What is the outcome?
A.
It gives an error because group functions cannot be used in anonymous blocks
B.
Itexecutes successfully and correctly gives the result of the sum of salaries in department 60.
C.
It executes successfully and incorrectly gives the result of the sum of salaries in department 60.
D.
It gives an error because the variable name and column name are the same in the where
clause of the select statement.
C
C
c
C
Can someone confirm why correct answer is C. It should be B reason being salary data type in table us NUMBER(8,2). When avg will be calculated and assigned into local variable decimal part will be truncated. So answer will vary.
C
@chunnu: Did you notice the where clause? It’s WHERE department_id = department_id.
So this condition will be true for all rows. As the variable name declare at line 3 has the same name as table column name, so table column will be given preference over variable.
I didn’t too.
Can’t picture an optimizer which between a variable and a column name in that case choose the column name…
I mean… wtf? 🙂
In potentially ambiguous SQL statements, the names of database columns take precedence over the names of local variables.
http://docs.oracle.com/cd/B14117_01/appdev.101/b10807/02_funds.htm
Nested table
http://www.orafaq.com/wiki/NESTED_TABLE
C
This execute successfully.
as martin said, column name get the precedence and department_id=department_id will evaluate to true, then sum of all record of the table (with department_ID) is assigned to V_SUM_SAL.
It is same as
SELECT SUM (salary)
INTO V_SUM_SAL
FROM employees
WHERE department_id IS NOT NULL;
C
when the department_id in where clause are both column Name,then the wert in v_sum_sal should be the sum of all employees’ salary but not the sum of Department 60.
B is correct 100% sure.