Oracle Database 10g SQL Fundamentals I - Practice 5

Oracle Database 10g SQL Fundamentals I- Les05_v(Displaying Data from Multiple Tables) Practice 5

 

1.

SELECT L.LOCATION_ID, L.STREET_ADDRESS, L.CITY, L.STATE_PROVINCE, C.COUNTRY_NAME
FROM LOCATIONS L, COUNTRIES C
WHERE L.COUNTRY_ID = C.COUNTRY_ID
ORDER BY L.LOCATION_ID
;

2.
SELECT E.LAST_NAME, E.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
;

3.

SELECT E.LAST_NAME, E.JOB_ID, E.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = L.LOCATION_ID
AND L.CITY ='Toronto'
;

4.

SELECT A1.LAST_NAME AS Employee, A1.EMPLOYEE_ID AS EMP#,  B1.LAST_NAME AS Manager, A1.MANAGER_ID AS Mgr#
FROM EMPLOYEES A1, EMPLOYEES B1
WHERE A1.MANAGER_ID = B1.EMPLOYEE_ID
ORDER BY A1.EMPLOYEE_ID
;

5.

SELECT A1.LAST_NAME AS Employee, A1.EMPLOYEE_ID AS EMP#,  B1.LAST_NAME AS Manager, A1.MANAGER_ID AS Mgr#
FROM EMPLOYEES A1, EMPLOYEES B1
WHERE A1.MANAGER_ID = B1.EMPLOYEE_ID(+)
ORDER BY A1.EMPLOYEE_ID

6.

SELECT A1.LAST_NAME, A2.LAST_NAME
FROM EMPLOYEES A1, EMPLOYEES A2
WHERE A1.DEPARTMENT_ID = A2.DEPARTMENT_ID
AND A1.EMPLOYEE_ID <> A2.EMPLOYEE_ID
ORDER BY A1.DEPARTMENT_ID, A1.EMPLOYEE_ID DESC
;

7.

SELECT E.LAST_NAME, E.JOB_ID, D.DEPARTMENT_NAME, E.SALARY, J.GRADE_LEVEL
FROM EMPLOYEES E, DEPARTMENTS D, JOB_GRADES J
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND E.SALARY BETWEEN J.LOWEST_SAL AND J.HIGHEST_SAL
;

8.

SELECT A1.LAST_NAME, A1.HIRE_DATE
FROM EMPLOYEES A1, EMPLOYEES A2
WHERE A1.HIRE_DATE > A2.HIRE_DATE
AND A2.LAST_NAME = 'Davies'
;

9.

SELECT A1.LAST_NAME, A1.HIRE_DATE
FROM EMPLOYEES A1, EMPLOYEES A2
WHERE A1.MANAGER_ID = A2.EMPLOYEE_ID
AND A1.HIRE_DATE < A2.HIRE_DATE
;