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. It executes 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.
|
Download Printable PDF. VALID exam to help you PASS. |
 |
I also think correct answer is C.
https://docs.oracle.com/cd/E18283_01/appdev.112/e17126/nameresolution.htm
“In ambiguous SQL statements, the names of columns take precedence over the names of local variables and formal parameters. For example, if a variable and a column with the same name are used in a WHERE clause, SQL considers both names to refer to the column.”
This is confirmed by below examples:
DECLARE
v_sum_sal number;
department_id employees.department_id%TYPE:=60;
begin
select sum(salary)
into v_sum_sal from employees
where department_id=department_id;
dbms_output.put_line(‘The sum of salary is ‘||v_sum_sal);
end;
/
The sum of salary is 684416
DECLARE
v_sum_sal number;
v_department_id employees.department_id%TYPE:=60;
begin
select sum(salary)
into v_sum_sal from employees
where department_id=v_department_id;
dbms_output.put_line(‘The sum of salary is ‘||v_sum_sal);
end;
/
The sum of salary is 28800
DECLARE
v_sum_sal number;
v_department_id employees.department_id%TYPE:=60;
begin
select sum(salary)
into v_sum_sal from employees
where department_id=60;
dbms_output.put_line(‘The sum of salary is ‘||v_sum_sal);
end;
/
The sum of salary is 28800