Oracle JOIN

--1. 3개 테이블 조인

SELECT *

FROM

    EMPLOYEES EMP

INNER JOIN DEPARTMENTS DEP

ON

    EMP.DEPARTMENT_ID=DEP.DEPARTMENT_ID

INNER JOIN JOBS JOBS

ON

    EMP.JOB_ID=JOBS.JOB_ID;

 

--2. 부서명, 회사이름, 직원코드, LAST FIRST NAME붙여서 NAME이라는 컬럼으로 나오게 WHERE 에다가 MINI_SAL이 4000이상 MAX_SAL 1억 6000

SELECT

    DEP.DEPARTMENT_NAME,

    JOBS.JOB_TITLE,

    EMP.EMPLOYEE_ID,

    CONCAT(CONCAT(EMP.FIRST_NAME,' ') ,EMP.LAST_NAME) AS NAME

FROM

    DEPARTMENTS DEP

INNER JOIN EMPLOYEES EMP

ON

    DEP.DEPARTMENT_ID= EMP.DEPARTMENT_ID

INNER JOIN JOBS JOBS

ON

    EMP.JOB_ID=JOBS.JOB_ID

WHERE

    JOBS.MIN_SALARY >= 4000

AND JOBS.MAX_SALARY<=16000;

 

--3. EMP SALARY 2000에서 3000사이 사원 3000에서 4000사이 대리 4000에서 5000사이 과장 5000이상 차장 컬럼 이름은 A LAST FIRST NAME붙여서 NAME

SELECT

    CONCAT(CONCAT(FIRST_NAME,' '),LAST_NAME) AS NAME,

    CASE

        WHEN SALARY BETWEEN 2000 AND 3000

        THEN '사원'

        WHEN SALARY BETWEEN 3000 AND 4000

        THEN '대리'

        WHEN SALARY BETWEEN 4000 AND 5000

        THEN '과장'

        WHEN SALARY>5000

        THEN '차장'

    END AS A

FROM

    EMPLOYEES;

 

--4.매니저 이름을 출력하게 직원의 아이디, 이름, 매니저 아이디

SELECT

    EMPLOYEE_ID,

    EMPLOYEE_NAME,

    CASE

        WHEN MANAGER_NAME =' '

        THEN '-'

        ELSE MANAGER_NAME

    END AS MANAGER_NAME

 

FROM //만들어진 테이블에서 다시 컬럼 선택

    (

        SELECT

            A.EMPLOYEE_ID,

            CONCAT(CONCAT(A.FIRST_NAME, ' '),A.LAST_NAME) AS EMPLOYEE_NAME,

            CONCAT(CONCAT(B.FIRST_NAME, ' '),B.LAST_NAME) AS MANAGER_NAME

        FROM

            EMPLOYEES A

        LEFT OUTER JOIN EMPLOYEES B

        ON

            A.MANAGER_ID=B.EMPLOYEE_ID

) XX //임시 테이블 명

ORDER BY EMPLOYEE_ID ASC