Continuação de Correção dos Exercícios de SQL no Schema RH.
7 – Selecione todos os id´s e nomes dos gerentes, ids e nomes de departamentos gerenciados por esses gerentes. (De 3 soluções para esse problema)
SELECT D.MANAGER_ID, E.FIRST_NAME,
D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.EMPLOYEE_ID = D.MANAGER_ID
SELECT D.MANAGER_ID, E.FIRST_NAME,
D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E LEFT JOIN DEPARTMENTS D
ON E.EMPLOYEE_ID = D.MANAGER_ID
SELECT D.MANAGER_ID, E.FIRST_NAME,
D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES
CROSS JOIN DEPARTMENTS D
WHERE E.EMPLOYEE_ID = D.MANAGER_ID
8 – Selecione todos os ids e nomes de departamentos, a qual não possuem nenhum empregado alocado (dê 2 soluções para esse problema);
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, E.EMPLOYEE_ID
FROM DEPARTMENTS D, EMPLOYEES E
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID(+)
AND E.DEPARTMENT_ID IS NULL
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, E.EMPLOYEE_ID
FROM DEPARTMENTS D
LEFT JOIN EMPLOYEES E
ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
WHERE E.DEPARTMENT_ID IS NULL
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, E.EMPLOYEE_ID
FROM EMPLOYEES E
RIGHT JOIN DEPARTMENTS D
ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
WHERE E.DEPARTMENT_ID IS NULL
9 – Selecione todos os ids e nomes de empregados, o nome do departamento, o nome do pais e o nome da região, onde as regiões devem ser iguais a Europe e Asia (dê 3 soluções para esse problema);
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, D.DEPARTMENT_NAME,
C.COUNTRY_NAME, R.REGION_NAME
FROM EMPLOYEES E, DEPARTMENTS D,
LOCATIONS L, COUNTRIES C,
REGIONS R
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = L.LOCATION_ID
AND L.COUNTRY_ID = C.COUNTRY_ID
AND C.REGION_ID = R.REGION_ID
AND R.REGION_NAME IN (‘Europe’,’Asia’);
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, D.DEPARTMENT_NAME,
C.COUNTRY_NAME, R.REGION_NAME
FROM EMPLOYEES E
INNER JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
INNER JOIN LOCATIONS L
ON D.LOCATION_ID = L.LOCATION_ID
INNER JOIN COUNTRIES C
ON L.COUNTRY_ID = C.COUNTRY_ID
INNER JOIN REGIONS R
ON C.REGION_ID = R.REGION_ID
WHERE R.REGION_NAME IN (‘Europe’,’Asia’);
