[오라클로 배우는 데이터베이스 개론과 실습]실무 실습 A 1.7 부속질의

A 1.7 부속질의

 

샘플 문제

SELECT FIRST_NAME || ' ' || LAST_NAME "Name",  JOB_ID, SALARY
FROM EMPLOYEES
WHERE SALARY > (SELECT SALARY FROM EMPLOYEES WHERE LAST_NAME = 'Tucker');

문제 1

SELECT FIRST_NAME || ' ' || LAST_NAME "Name", JOB_ID, SALARY, HIRE_DATE
FROM EMPLOYEES E1
WHERE E1.SALARY = (SELECT  MIN(SALARY)
                                        FROM EMPLOYEES E2
                                        WHERE E1.JOB_ID = E2.JOB_ID
                                        GROUP BY JOB_ID);

문제 2

 SELECT FIRST_NAME || ' ' || LAST_NAME "Name", DEPARTMENT_ID, JOB_ID
 FROM EMPLOYEES E1
 WHERE SALARY > (SELECT  AVG(SALARY)
 FROM EMPLOYEES E2
 WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID
 GROUP BY DEPARTMENT_ID);

 

문제 3

SELECT EMPLOYEE_ID, FIRST_NAME || ' ' || LAST_NAME "Name", JOB_ID, HIRE_DATE
FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = L.LOCATION_ID
and l.city like 'O%';

 

문제 4

SELECT FIRST_NAME || ' ' || LAST_NAME "Name", JOB_ID , SALARY, DEPARTMENT_ID, 
(SELECT ROUND(AVG(SALARY))
 FROM EMPLOYEES E2
 WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID
 GROUP BY E1.DEPARTMENT_ID) "Department Avg Salary"
FROM EMPLOYEES E1;