What is the outcome?

View the Exhibit and examine the structure of the employees table.

Execute the following block of code:

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.

Show Hint

← Previous question

Next question →

Leave a Reply 18

William

Why C?
I don’t see the error calculating the sum of salaries. I think B is the right answer

Raji

department_id=department_id is satisfied by each and every row. The second department_id is not considered from variable. it will be taken as column Name only. So the sum of all rows will be the output. hence C is right.

Igor

Try again…
C is right.

Srinivas

problem in where condition, in this case variable is treated as column so incorrect result.

vin

Srinivas is right, good ans.

Sid

C because variable name as well as column name are same (DEPARTMENT_ID)

It would give sum of all the rows in employees table so answer is C

Hector Gonzalez

La respuesta es la C), esto debido a que hay una ambigüedad en la condición dentro de la cláusula WHERE

Hay una variable declarada en la sección llamada departmente_id y en la tabla employees que esta siendo referenciada en la cláusula FROM existe una columna llamada department_id, por lo tanto el Oracle server, otorga prioridad a los nombres de columnas, luego a los nombres de las variables, por lo que al momento de evaluar la condición dentro de la cláusula WHERE el Oracle server descarta el valor de 60 asignado a la variable department_id.

supongamos lo siguiente

department_id salary CLAUSULA WHERE
department_id=department id
10 10000 1.- Se descarta el valor asignado a la variable department_id en la sección DECLARE.
10 12000 2.- El server Oracle da prioridad a los nombres de las columnas por lo que filtra las filas que
30 23000 cumplen la condición dentro de la cláusula WHERE.
30 15500 3.- Se aplica la función de grupo SUM(salary).
40 12400 4.- Esto regresa la sumatoria de los salarios del departamento 10, 30, 40, 50, 60, por lo que si
50 11000 se regresará la sumatoria del departamento 60 pero adicionalmente regresa la sumatoria de los
50 26700 departmentos restantes, y el objetivo de la clausula WHERE fue que solo se obtuviera la sumatoria
60 29000 de salarios del departamento 60, esto basados en la declaración de la variable department_id en
60 8000 la sección DECLARE. Si cambiamos el nombre de la variable department_id dentro de la sección
60 43000 DECLARE y en la clausula WHERE de la siguiente forma:

DECLARE
v_sum_sal NUMBER;
ln_department_id employees.department_id%TYPE:=60;
BEGIN
SELECT SUM(salary) INTO v_sum_sal
FROM employees
WHERE department_id=ln_department_id;
DBMS_OUTPUT.PUT_LINE(‘The sum of salary is’ || v_sum_sal);
END;
/

Probar lo anterior en el esquema de HR.

hector gonzalez

La anterior respuesta es correcta en cuanto a los incisos pero los grupos, solo se formarían si tuviéramos presente la clausula GROUP BY.

samkelo

c is an answer

c.v.kumar

b is correct answer

Heena

correct answer is C.
as it will consider all the department ids because of the same name and will compute an incorrect result.

salvo

ali

answer C ,why let see

first will show total salaries from department 30 by using same variable

SQL> declare
2 total number;
3 department_id number :=30;
4 begin
5 select sum(salary) into total from employees where department_id=department_id;

6 dbms_output.put_line(total);
7 end;
8 /

total is : 684416

second will show total of salaries from all employees :

SQL> select sum(salary) from employees;

SUM(SALARY)
———–
691416

now last step let see different between 2 results :

684416 – 691416 = 7000
this 7000 actually employee salary have not department_id ,let see:

SQL> select salary from employees where department_id is null;

SALARY
———-
7000

that’s mean when use same variable in condition will be return all expect null value
like here it return all employee salaries sum expect 7000 because have not department_id .

thanks for all.