Database 기본5

----------------------------2021-03-08
 
-- 직급이 대리, ASIA 지역에 근무하는 직원의
-- 사번, 이름, 직급명, 부서명, 근무지역명, 급여(원화표시)
SELECT EMP_ID, EMP_NAME, JOB_NAME, DEPT_TITLE, LOCAL_NAME, TO_CHAR(FLOOR(SALARY),'L999,999,999') AS 급여
FROM EMPLOYEE
JOIN JOB ON (EMPLOYEE.JOB_CODE=JOB.JOB_CODE)
JOIN DEPARTMENT ON (DEPT_ID=DEPT_CODE)
JOIN LOCATION ON (LOCATION_ID=LOCAL_CODE)
WHERE JOB_NAME='대리' AND LOCAL_NAME LIKE 'ASIA%';
 
-- ㄴ 변수???? 사용
SELECT EMP_ID, EMP_NAME, JOB_NAME, DEPT_TITLE, LOCAL_NAME, TO_CHAR(FLOOR(SALARY),'L999,999,999') AS 급여
FROM EMPLOYEE E
JOIN JOB J ON (E.JOB_CODE=J.JOB_CODE)
JOIN DEPARTMENT D ON (DEPT_ID=DEPT_CODE)
JOIN LOCATION L ON (LOCATION_ID=LOCAL_CODE)
WHERE JOB_NAME='대리' AND LOCAL_NAME LIKE 'ASIA%';
 
-- NON EQUAL JOIN : 동등한 컬럼을 가지고 비교를 하는게 아니라 범위컬럼을 비교
SELECT EMP_NAME, SALARY, SAL_GRADE.SAL_LEVEL
FROM EMPLOYEE E
    JOIN SAL_GRADE ON (E.SALARY BETWEEN SAL_GRADE.MIN_SAL AND SAL_GRADE.MAX_SAL);
 
-- 전지연의 매니저 이름 조회 (단일행 서브쿼리 : 서브쿼리 RESULTSET이 1개 ROW 1개 인 것)
SELECT E.EMP_ID, E.EMP_NAME, E.MANAGER_ID, M.EMP_NAME AS 매니저명
FROM EMPLOYEE E
    JOIN EMPLOYEE M ON E.MANAGER_ID=M.EMP_ID
WHERE E.EMP_NAME='전지연';
 
-- 위를 서브쿼리 이용
SELECT EMP_NAME
FROM EMPLOYEE
WHERE EMP_ID=(SELECT MANAGER_ID FROM EMPLOYEE WHERE EMP_NAME='전지연');
 
-- 전체사원의 평균 급여보다 많이 받는 사원의 이름, 부서코드, 월급 조회
SELECT EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY>(SELECT AVG(SALARY) FROM EMPLOYEE);
 
-- 전 사원 중 최소급여를 받는 사원의 이름, 부서코드, 직책코드, 월급 조회
SELECT MIN(SALARY) FROM EMPLOYEE;
SELECT EMP_NAME, DEPT_CODE, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY = (SELECT MIN(SALARY) FROM EMPLOYEE);
 
-- 최대급여를 받는 사원과 윤은해랑 같은 급여 받는 사원 조회
SELECT EMP_NAME, DEPT_CODE, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY = (SELECT MAX(SALARY) FROM EMPLOYEE)
    OR SALARY=(SELECT SALARY FROM EMPLOYEE WHERE EMP_NAME='윤은해');
 
-- 다중행 서브쿼리 : 서브쿼리 결과 (RESULTSET) 컬럼 1개, ROW 다수
SELECT DEPT_CODE FROM EMPLOYEE WHERE EMP_NAME IN ('송종기','윤은해');
SELECT EMP_NAME, DEPT_CODE
FROM EMPLOYEE
--WHERE DEPT_CODE = (SELECT DEPT_CODE FROM EMPLOYEE WHERE EMP_NAME IN ('송종기', '윤은해')); = 사용 안됨
WHERE DEPT_CODE IN (SELECT DEPT_CODE FROM EMPLOYEE WHERE EMP_NAME IN ('송종기', '윤은해'));
-- ㄴ WHERE DEPT_CODE IN('D9', 'D5');
 
-- 대표, 부사장을 제외한 사원의 사원명, 부서명, 직책명 조회
SELECT EMP_NAME, DEPT_TITLE, JOB_NAME
FROM EMPLOYEE
    JOIN JOB ON (JOB.JOB_CODE=EMPLOYEE.JOB_CODE)
    JOIN DEPARTMENT ON (DEPT_ID=DEPT_CODE)
WHERE JOB_NAME NOT IN (SELECT JOB_NAME FROM DEPARTMENT WHERE JOB_NAME IN('대표','부사장'));
 
-- 직급이 대표, 부사장이 아닌 ASIA1 에서 근무하는 사원 조회
SELECT EMP_NAME
FROM EMPLOYEE
WHERE JOB_CODE NOT IN (SELECT JOB_CODE
                            FROM JOB
                        WHERE JOB_NAME IN('부사장', '대표'))
                            AND DEPT_CODE IN (SELECT DEPT_ID
                            FROM DEPARTMENT
                            JOIN LOCATION ON LOCATION_ID=LOCAL_CODE WHERE LOCAL_NAME='ASIA1');
 
-- 다중행 서브쿼리를 동등비교 했을 땐, 결괏값을 OR로 묶어서 처리한다.
 
-- ANY / ALL 다중값에 대해 대소비교를 할 수 있는 예약어
-- ANY : RESULTSET 으로 나온값을 OR 연결해서 대소비교 한다.
-- X > ANY(다중행서브쿼리) : ANY에 있는 어떤 값보다 X가 크면 TRUE -> 최소값보다 크면 TRUE;
 
-- D5,D6부서에서 최저급여 받는 사원보다 많이받는 사원의 이름 월급 부서코드 조회
SELECT EMP_NAME, SALARY, DEPT_CODE
FROM EMPLOYEE
WHERE SALARY > ANY(SELECT SALARY FROM EMPLOYEE WHERE DEPT_CODE IN ('D6', 'D5'));
 
-- 직책이 J3, J5인 사원 최소급여 보다 많이 받는 사원 조회
SELECT *
FROM EMPLOYEE
WHERE SALARY > ANY(SELECT SALARY FROM EMPLOYEE WHERE JOB_CODE IN ('J3', 'J5'));
 
-- X < ANY (서브쿼리) : ANY에 있는 어떤 값보다 X가 작기만 하다 -> 최댓값보다 작으면 TRUE.
 
-- 부서 D2, D5 에서 최대급여인 사원보다 더 받는 사원 조회
SELECT *
FROM EMPLOYEE
WHERE SALARY > (SELECT MAX(SALARY) FROM EMPLOYEE WHERE DEPT_CODE = 'D2' OR DEPT_CODE = 'D5');
 
-- 다중행, 다중열 서브쿼리 : 행, 열 모두 다수
 
-- 부서별 최소급여 조회
SELECT DEPT_CODE, MIN(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE;
-- 부서별 최소급여받는 사원 조회
SELECT *
FROM EMPLOYEE
WHERE (DEPT_CODE, SALARY) IN (SELECT DEPT_CODE, MIN(SALARY) FROM EMPLOYEE GROUP BY DEPT_CODE);
 
-- 부서별 최대급여 사원 하고 최소급여 받는 사원 조회
-- 사원명, 부서명, 급여 출력.. NULL 도 출력
SELECT EMP_NAME, NVL(DEPT_TITLE,'인턴'), SALARY
FROM EMPLOYEE
    LEFT JOIN DEPARTMENT ON DEPT_CODE=DEPT_ID
WHERE (NVL(DEPT_CODE,1), SALARY)IN(SELECT NVL(DEPT_CODE,1), MAX(SALARY)
                                    FROM EMPLOYEE GROUP BY DEPT_CODE)
    OR (NVL(DEPT_CODE,'GOM'), SALARY)IN(SELECT NVL(DEPT_CODE,'GOM'), MIN(SALARY)
                                    FROM EMPLOYEE GROUP BY DEPT_CODE);
 
-- 상관서브쿼리(상호연관) : 메인뭐리의 값을 서브쿼리에 주고 서브쿼리를 수행한 다음 그 결과를 다시 메인쿼리로 반환
-- ㄴ 서브쿼리의 WHERE절에 메인쿼리의 값을 가져와 비교
 
-- EXISTS : 서브쿼리 결과가 있을 때 ROW 있으면 TRUE, ROW 0개면 FALSE
 
SELECT *
FROM EMPLOYEE
WHERE EXISTS (SELECT 1 FROM EMPLOYEE WHERE EMP_ID='200');
 
-- 상관서브쿼리로 DEPT_CODE 가 있는 사원 출력
-- IS NOT NULL 또는 JOIN 미사용
-- 이름, DEPT_CODE
SELECT EMP_NAME, DEPT_CODE
FROM EMPLOYEE E
WHERE EXISTS(SELECT EMP_NAME FROM EMPLOYEE WHERE DEPT_CODE=E.DEPT_CODE);
-- 웨어절 셀렉트 바로 뒤 EMP_NAME에는 아무 컬럼이나 넣어도 됨. 값이 있나 확인용이라..
 
-- 급여를 가장 많이받는 사원을 상관서브쿼리로 조회
-- 사워명, 급여
SELECT EMP_NAME, SALARY
FROM EMPLOYEE E
WHERE NOT EXISTS(SELECT '돈많다~' FROM EMPLOYEE WHERE SALARY>E.SALARY);
 
-- 직급이 J1,J2,J3가 아닌 사원 중에서 자신의 부서 평균급여보다 많이 받는 사원 조회
SELECT EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE E
WHERE JOB_CODE NOT IN ('J1','J2','J3')
                AND SALARY>(SELECT AVG(SALARY)FROM EMPLOYEE WHERE DEPT_CODE=E.DEPT_CODE);