[오라클] 조인, 서브 쿼리 연습 문제

1. JOIN을 이용하여 사원ID가 100번인 사원의 부서번호와 부서이름을 출력하시오.

SELECT E.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE D.DEPARTMENT_ID =100;

 

2. INNER JOIN을 이용하여 사원이름과 함께 그 사원이 소속된 도시이름과 지역명을 출력하시오

SELECT E.FIRST_NAME, L.CITY, 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;

 

3. INNER JOIN과 USING 연산자를 사용하여 100번 부서에 속하는 직원명과 직원의 담당 업무명, 속한 부서의 도시명을 출력하시오. (100번 부서에는 직원 6명있음)

SELECT E.FIRST_NAME,J.JOB_TITLE ,D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D, JOBS J, LOCATIONS L
WHERE E.DEPARTMENT_ID = 100
    AND J.JOB_ID = E.JOB_ID
    AND E.DEPARTMENT_ID = D.DEPARTMENT_ID
    AND D.LOCATION_ID = L.LOCATION_ID;

 

4. JOIN을 사용하여 커미션을 받는 모든 사원의 이름, 부서ID, 도시명을 출력하시오

SELECT E.FIRST_NAME, D.DEPARTMENT_ID, L.CITY
FROM  EMPLOYEES E, DEPARTMENTS D, LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = L.LOCATION_ID 
AND E.COMMISSION_PCT IS NOT NULL
ORDER BY FIRST_NAME;

 

5. INNER JOIN과 와일드카드를 사용하여 이름에 A가 포함된 모든 사원의 이름과 부서명을 출력하시오(단, 대소문자 구분 없음)

SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND E.FIRST_NAME LIKE '%A%';

 

6. JOIN을 사용하여 Seattle에 근무하는 모든 사원의 이름, 업무, 부서번호 및  부서명을 출력하시오

SELECT E.FIRST_NAME, J.JOB_TITLE, D.DEPARTMENT_NAME, D.DEPARTMENT_ID,L.CITY
FROM EMPLOYEES E  JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
JOIN LOCATIONS L
ON  L.LOCATION_ID = D.LOCATION_ID
JOIN JOBS J
ON E.JOB_ID = J.JOB_ID
AND L.CITY = 'Seattle';

 

7. SELF 조인을 사용하여 사원의 이름 및 사원번호와 매니저 이름 및 매니저 번호와  함께 조회하시오.

SELECT E2.EMPLOYEE_ID , E2.FIRST_NAME AS "직원명", E2.MANAGER_ID, E1.FIRST_NAME "매니저명"
FROM EMPLOYEES E1 JOIN EMPLOYEES E2
ON E2.MANAGER_ID = E1.EMPLOYEE_ID;

 

8. OUTER JOIN, SELF JOIN을 사용하여 관리자가 없는 사원을 포함하여 사원번호를 기준으로 내림차순 정렬하여 조회

SELECT E2.EMPLOYEE_ID , E2.FIRST_NAME AS "직원명", E2.MANAGER_ID, E1.FIRST_NAME "매니저명"
FROM EMPLOYEES E1 RIGHT OUTER JOIN EMPLOYEES E2
ON E2.MANAGER_ID = E1.EMPLOYEE_ID
ORDER BY 1 DESC;

 

+ 셀프 조인

- FROM 절 : 테이블1개(E)가 2개인 것처럼, 각각 다른 별칭(E1,E2)을 준다.
- WHERE 조건절 : 테이블E1과 테이블E2의 공통 컬럼을 지정하여 관계를 설정한다.

9. SELF JOIN을 사용하여 'Oliver' 사원의 부서명, 그 사원과 동일한 부서에서 근무하는  동료 사원의 이름을 조회 (단, 각 열의 별칭은 부서명, 동료로 할 것)

SELECT TEMP.FIRST_NAME "동료", D.DEPARTMENT_NAME "부서명"
FROM 
    (
    SELECT FIRST_NAME, DEPARTMENT_ID
    FROM EMPLOYEES
    WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
                            FROM EMPLOYEES 
                            WHERE FIRST_NAME = 'Oliver')
    )TEMP, DEPARTMENTS D
WHERE TEMP.DEPARTMENT_ID = D.DEPARTMENT_ID
ORDER BY 1;

 

10. SELF JOIN을 사용하여 관리자보다 먼저 입사한 모든 사원의 이름 및 입사일을 매니저 이름 및 입사일과 함께 출력하시오

SELECT E1.FIRST_NAME,E1.HIRE_DATE, E2.FIRST_NAME "매니저이름", E2.HIRE_DATE "입사일"
FROM EMPLOYEES E1 JOIN EMPLOYEES E2
ON E1.MANAGER_ID = E2.EMPLOYEE_ID
WHERE E2.HIRE_DATE > E1.HIRE_DATE ;

11. Last name이 ‘King’을 Manager로 둔 사원의 이름과 급여를 조회하시오.

SELECT E1.FIRST_NAME , E1.SALARY
FROM EMPLOYEES E1 JOIN EMPLOYEES E2
ON E2.EMPLOYEE_ID = E1.MANAGER_ID
WHERE E2.LAST_NAME = 'King';

 

12. Finance부서의 사원에 대한 부서번호, 사원이름 및 담당 업무를 표시하시오

SELECT E.DEPARTMENT_ID, E.FIRST_NAME, J.JOB_TITLE
FROM EMPLOYEES E JOIN JOBS J
ON E.JOB_ID = J.JOB_ID
JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE D.DEPARTMENT_NAME = 'Finance';

 

13. 평균 급여보다 많은 급여를 받고 이름에 M이 포함된 사원과 같은 부서에서 근무하는 사원의 사원번호, 이름, 급여를 조회하시오

SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY >= (SELECT AVG(SALARY)  FROM EMPLOYEES)
AND
FIRST_NAME LIKE '%M%';