Question

Com base no DER abaixo, qual a construção correta para a solicitaçãoseguinte? Encontre os empregados que têm, no mínimo, uma pessoa subordinada a cada um deles.a.SELECT EMPLOYEE_ID, MANAGER_ID, FIRST_NAME, LAST_NAME,JOB_ID, DEPARTMENT_ID, SALARYFROM EMPLOYEES EWHERE EXISTS (SELECT EMPLOYEE_IDFROM EMPLOYEESWHERE EMPLOYEES.MANAGER_ID =E. EMPLOYEE_ID)ORDER BY EMPLOYEE_ID;b.SELECT EMPLOYEE_ID, MANAGER_ID, FIRST_NAME, LAST_NAME,JOB_ID, DEPARTMENT_ID, SALARYFROM EMPLOYEES EWHERE NOT EXISTS (SELECT EMPLOYEE_IDFROM EMPLOYEESWHERE EMPLOYEES.MANAGER_ID =E. EMPLOYEE_ID)ORDER BY EMPLOYEE_ID;c.SELECT EMPLOYEE_ID, MANAGER_ID, FIRST_NAME, LAST_NAME,JOB_ID, DEPARTMENT_ID, SALARYFROM EMPLOYEES EWHERE EXISTS (SELECT EMPLOYEE_IDFROM EMPLOYEES)ORDER BY EMPLOYEE_ID;d.SELECT EMPLOYEE_ID, MANAGER_ID, FIRST_NAME, LAST_NAME,JOB_ID, DEPARTMENT_ID, SALARYFROM EMPLOYEES EWHERE EMPLOYEE_ID >= (SELECT EMPLOYEE_IDFROM EMPLOYEESWHERE EMPLOYEES.MANAGER_ID =E. EMPLOYEE_ID)ORDER BY EMPLOYEE_ID;e.SELECT EMPLOYEE_ID, MANAGER_ID, FIRST_NAME, LAST_NAME,JOB_ID, DEPARTMENT_ID, SALARYFROM EMPLOYEES EWHERE EXISTS IN (SELECT EMPLOYEE_IDFROM EMPLOYEESWHERE EMPLOYEES.MANAGER_ID =E. EMPLOYEE_ID)ORDER BY EMPLOYEE_ID;

Answer 1

Resposta:

A

SELECT EMPLOYEE_ID, MANAGER_ID, FIRST_NAME, LAST_NAME,

JOB_ID, DEPARTMENT_ID, SALARY

FROM EMPLOYEES E

WHERE EXISTS (SELECT EMPLOYEE_ID

  FROM EMPLOYEES

  WHERE EMPLOYEES.MANAGER_ID =

                             E. EMPLOYEE_ID)

ORDER BY EMPLOYEE_ID;

Explicação:

A opção B está errada pois o enunciado pede "que têm, no mínimo, uma pessoa subordinada" e o código NOT EXISTS retornará todos os que não tem uma pessoa subordinada;

SELECT EMPLOYEE_ID, MANAGER_ID, FIRST_NAME, LAST_NAME,JOB_ID, DEPARTMENT_ID, SALARY

FROM EMPLOYEES EWHERE NOT EXISTS (SELECT EMPLOYEE_ID

FROM EMPLOYEES WHERE EMPLOYEES.MANAGER_ID = E. EMPLOYEE_ID)

ORDER BY EMPLOYEE_ID;


A opção C está errada pois o código EMPLOYEE_ID

FROM EMPLOYEES vai retornar apenas o EMPLOYEE_ID sem conferir se tem alguém subordinado

SELECT EMPLOYEE_ID, MANAGER_ID, FIRST_NAME, LAST_NAME,JOB_ID, DEPARTMENT_ID, SALARY

FROM EMPLOYEES EWHERE EXISTS (SELECT EMPLOYEE_ID

FROM EMPLOYEES)

ORDER BY EMPLOYEE_ID;

A Opção D está errada pois o código >= é para comparação de valores "maiores ou iguais que", o que não é o solicitado no enunciado:

SELECT EMPLOYEE_ID, MANAGER_ID, FIRST_NAME, LAST_NAME,JOB_ID, DEPARTMENT_ID, SALARY

FROM EMPLOYEES EWHERE EMPLOYEE_ID >= (SELECT EMPLOYEE_ID

FROM EMPLOYEES WHERE EMPLOYEES.MANAGER_ID =E. EMPLOYEE_ID)

ORDER BY EMPLOYEE_ID;