DAY26
Q1) USING(컬럼,컬럼), ON : N개의 테이블 JOIN
EMPLOYEE(사원) , JOB (직위) , DEPARTMENT(부서) , LOCATION (부서위치) 
직급이 대리이면서 아시아 지역에서 근무하는 사원의 이름과 부서이름을 출력해보자
SELECT EMP_NAME , DEPT_NAME
FROM EMPLOYEE JOIN JOB USING (JOB_ID)
JOIN DEPARTMENT USING (DEPT_ID)
JOIN LOCATION ON (LOC_ID = LOCATION_ID)
WHERE JOB_TITLE = '대리' // JOB_TITLE = TRIM(' 대리 ') 이렇게 해도
AND LOC_DESCRIBE LIKE '아시아%';
Q2) 사원의 이름과 근무지역 부서명을 출력해보자
SELECT EMP_NAME , LOC_DESCRIBE , DEPT_NAME
FROM EMPLOYEE JOIN DEPARTMENT USING (DEPT_ID)
JOIN LOCATION ON (LOC_ID = LOCATION_ID);
Q3) SET Operator : 두 개 이상의 쿼리 결과를 하나로 결합시키는 연산자
SELECT절에 기술하는 컬럼 개수와 데이터 타입은 모든 쿼리에서 동일해야 한다.
- UNION : 양쪽 쿼리 결과를 모두 포함 (중복된 결과는 1번만 표현)
- UNION ALL : 양쪽 쿼리 결과를 모두 포함 (중복 결과도 모두 표현)
- INTERSECT : 양쪽 쿼리 결과에 모두 포함되는 행만 표현
- MINUS : 쿼리1과 쿼리2가 존재할 경우 쿼리1 결과에만 포함되고 쿼리2 결과에는 포함되지 않는 행만 표현
모든 직원의 현재 ROLE 과 이전 ROLE를 함께 출력하되 사원의 ID와 ROLE_NAME을 출력하자
SELECT EMP_ID , ROLE_NAME
FROM EMPLOYEE_ROLE -- 22건
UNION
SELECT EMP_ID , ROLE_NAME
FROM ROLE_HISTORY; -- 4건 => 중복된 결과 제외 25건 출력
SELECT EMP_ID , ROLE_NAME
FROM EMPLOYEE_ROLE -- 22건
UNION ALL
SELECT EMP_ID , ROLE_NAME
FROM ROLE_HISTORY; -- 4건 => 중복된 결과 포함 26건 출력
Q4) 입사후 현재 ROLE과 동일한 ROLE를 가졌던 적이 있는 직원을 조회해보자
SELECT EMP_ID , ROLE_NAME
FROM EMPLOYEE_ROLE
INTERSECT
SELECT EMP_ID , ROLE_NAME
FROM ROLE_HISTORY; => 1건 출력
Q5) 입사후 현재 ROLE과 동일한 ROLE를 부여 받은적이 없는 직원을 조회해보자
SELECT EMP_ID , ROLE_NAME
FROM EMPLOYEE_ROLE
MINUS
SELECT EMP_ID , ROLE_NAME
FROM ROLE_HISTORY; => 21건 출력
Q6) SELECT절에 기술하는 (컬럼 개수와 데이터 타입은) 모든 쿼리에서 동일해야 한다
20번 부서 번호에 해당하는 사원의 이름과 JOB_ID, 입사일과
20번 부서 번호에 해당하는 부서이름과 부서 ID의 데이터를 결합하고자 한다.
(컬럼의 개수가 맞지 않을 경우 DUMMY Column을 사용)
SELECT EMP_NAME, JOB_ID , HIRE_DATE
FROM EMPLOYEE
WHERE DEPT_ID = '20'
UNION
SELECT DEPT_NAME , DEPT_ID , NULL
FROM DEPARTMENT
WHERE DEPT_ID = '20';
Q7) UNION 구문을 이용해서 50번 부서원을 관리자와 직원으로 구분해 출력
사원의 번호, 사원의 이름, 구분을 출력하자.
ex) 124 정지현 직원
141 김예수 관리자
--- 50번 부서이면서 141번의 사원번호면 관리자
--- 50번이면서 141 사원번호가 아니면 직원
SELECT EMP_ID , EMP_NAME , '관리자' AS 구분
FROM EMPLOYEE
WHERE EMP_ID = '141' AND DEPT_ID = '50'
UNION
SELECT EMP_ID , EMP_NAME , '직원' AS 구분
FROM EMPLOYEE
WHERE MGR_ID = '141' AND DEPT_ID = '50'
ORDER BY 3,1; // 구분으로 정렬
Q8) SET - JOIN 의 관계
--SET
SELECT EMP_ID , ROLE_NAME
FROM EMPLOYEE_ROLE
INTERSECT
SELECT EMP_ID , ROLE_NAME
FROM ROLE_HISTORY;
--JOIN
SELECT EMP_ID , ROLE_NAME
FROM EMPLOYEE_ROLE
JOIN ROLE_HISTORY USING(EMP_ID,ROLE_NAME);
Q9) SET - IN의 관계 : 사원의 이름과 직급을 출력해보자
단 직급을 대리 또는 사원만 출력한다.
--JOIN
SELECT EMP_NAME,JOB_TITLE 직급
FROM EMPLOYEE
JOIN JOB USING(JOB_ID)
WHERE JOB_TITLE IN('대리','사원')
ORDER BY 2,1; // 두번째 컬럼을 오름차순으로 , 첫번째 컬럼을 오름차순으로
--SET
SELECT EMP_NAME,'대리' 직급
FROM EMPLOYEE
JOIN JOB USING(JOB_ID)
WHERE JOB_TITLE = '대리'
UNION
SELECT EMP_NAME,'사원' 직급
FROM EMPLOYEE
JOIN JOB USING(JOB_ID)
WHERE JOB_TITLE = '사원'
ORDER BY 2,1;Q10) SubQuery
JONES의 월급과 같은 월급을 받는 사원의 이름과 월급을 출력해보자.
1. JONES가 받는 월급을 찾는다. ------------> 2975
SELECT SAL
FROM EMP
WHERE ENAME = 'JONES';
2. 1번의 결과로 쿼리를 구현한다.
SELECT ENAME , SAL
FROM EMP
WHERE SAL = 2975;
3. SubQuery 로 구현해보자
SELECT ENAME , SAL
FROM EMP
WHERE SAL = (SELECT SAL
FROM EMP
WHERE ENAME = 'JONES');SubQuery : 하나의 쿼리가 다른 쿼리에 포함되는 구조
다른 쿼리에 포함된 내부 쿼리(서브 쿼리)는 외부 쿼리(메인 쿼리)에 사용될 값을 반환하는 역할
[형식]
SELECT
FROM
WHERE expr operator (
SELECT
FROM
WHERE...
);
1) 서브쿼리는 일반적인 SQL구문과 동일 (별도 형식이 지정되어 있지 않다.)
2) SELECT, FROM , WHERE , HAVING 절 등에서 사용이 가능하다.
3) 서브쿼리는 ()로 묶여있다.
4) 서브쿼리안에서는 ;을 사용하지 않는다.
5) 서브쿼리의 리턴하는 유형에 따라 연산자를 구분해서 사용한다.
6) 단일행 서브쿼리 = ,>,<,>=,<=,<> 등을 사용한다.
7) 다중행 서브쿼리 IN , ANY, ALL 등을 사용한다.Q11) EMPLOYEE에서 나승원과 같은 부서에 근무하는 사원의 이름을 출력 해보자.
SELECT EMP_NAME
FROM EMPLOYEE
WHERE DEPT_ID = ( SELECT DEPT_ID
FROM EMPLOYEE
WHERE EMP_NAME = '나승원');
Q12) EMPLOYEE에서 나승원과 같은 직업을 가진 사원의 이름과 직업ID, 봉급을 출력하자
단 봉급은 나승원보다 많이 받는 사원의 정보를 출력 한다.
SELECT EMP_NAME , JOB_ID , SALARY
FROM EMPLOYEE
WHERE JOB_ID = (SELECT JOB_ID
FROM EMPLOYEE
WHERE EMP_NAME = '나승원') AND SALARY > (SELECT SALARY
FROM EMPLOYEE
WHERE EMP_NAME = '나승원');
Q13) EMPLOYEE 사원 테이블에서 가장 작은 봉급과 같은 봉급을 받는 사원의 이름, 직업 아이디, 봉급을 출력하자
SELECT EMP_NAME, JOB_ID ,SALARY
FROM EMPLOYEE
WHERE SALARY = ( SELECT MIN(SALARY)
FROM EMPLOYEE);
Q14) 부서별 최대 봉급의 합이랑 같은 봉급의 합을 받은 부서이름과 봉급의 합을 출력해보자
SELECT DEPT_NAME , SUM(SALARY)
FROM EMPLOYEE
LEFT JOIN DEPARTMENT USING(DEPT_ID)
GROUP BY DEPT_ID, DEPT_NAME
HAVING SUM(SALARY) = (SELECT MAX(SUM(SALARY))
FROM EMPLOYEE
GROUP BY DEPT_ID);
// HAVING은 집계함수 조건..??
Q15) 부서별 최소 봉급과 같은 봉급을 받는 사원의 사원번호와 사원의 이름을 출력하자_IN
SELECT EMP_ID , EMP_NAME
FROM EMPLOYEE
WHERE SALARY IN (SELECT MIN(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_ID);
// =은 하나 비교할때 IN은 여러개 비교할때
Q16) 전체 직원에 대한 관리자와 직원을 구분해서 표시해보자 / 사원번호, 사원의 이름, 구분(관리자, 직원)을 출력_IN
SELECT EMP_ID , EMP_NAME ,
CASE WHEN EMP_ID IN(SELECT MGR_ID FROM EMPLOYEE) THEN '관리자' ELSE '직원' END AS 구분
FROM EMPLOYEE
ORDER BY 3,1;
Q17) 과장직급보다 급여가 많은 대리직급의 직원을 조회해보자. 사원의 이름 , 봉급을 출력해보자 _ANY
1) < ANY : 비교대상 중 최대값보다 작을 때
2) > ANY : 비교대상 중 최소값보다 클때
3) = ANY : IN연산자와 동일
SELECT EMP_NAME , SALARY
FROM EMPLOYEE
JOIN JOB USING (JOB_ID)
WHERE JOB_TITLE = '대리'
AND SALARY >ANY(SELECT SALARY
FROM EMPLOYEE
JOIN JOB USING (JOB_ID)
WHERE JOB_TITLE = '과장');
Q18) 모든 과장들의 직급보다 급여가 많은 대리 직급의 직원을 조회해보자 _ALL
1) < ALL : 비교 대상 중 최소 값보다 적을때
2) > ALL : 비교 대상 중 최대 값보다 클때
SELECT EMP_NAME , SALARY
FROM EMPLOYEE
JOIN JOB USING (JOB_ID)
WHERE JOB_TITLE = '대리'
AND SALARY >ALL(SELECT SALARY
FROM EMPLOYEE
JOIN JOB USING (JOB_ID)
WHERE JOB_TITLE = '과장');
Q19) 자기 직급의 평균 급여를 받는 직원을 조회하자 이름(EMP_NAME), 직급 (JOB_TITLE) , 봉급 (SALARY)
SELECT EMP_NAME , JOB_TITLE , SALARY
FROM EMPLOYEE
LEFT JOIN JOB USING(JOB_ID)
WHERE (NVL(JOB_ID,' '),SALARY) //묶여있어서 두개 비교
IN (SELECT NVL(JOB_ID,' '), TRUNC(AVG(SALARY),-5)
FROM EMPLOYEE
GROUP BY JOB_ID)
ORDER BY JOB_ID;
박하일 -> 과장 -> 2600000
정도연 차장 2600000
직급별 평균 급여
SELECT JOB_TITLE , TRUNC(AVG(SALARY),-5)
FROM EMPLOYEE
JOIN JOB USING(JOB_ID)
GROUP BY JOB_TITLE, JOB_ID;
Q20) FROM절을 이용해서 풀어보자
SELECT EMP_NAME, JOB_TITLE, SALARY
FROM (SELECT JOB_ID , TRUNC(AVG(SALARY),-5) AS JOBAVG
FROM EMPLOYEE
GROUP BY (JOB_ID)) V
JOIN EMPLOYEE E ON(JOBAVG = SALARY AND NVL(E.JOB_ID,' ') = NVL(V.JOB_ID,' '))
LEFT JOIN JOB J ON (E.JOB_ID = J.JOB_ID)
ORDER BY V.JOB_ID;
Q21) SubQuery _ Correlated SubQuery : 메인 쿼리에서 고려된 각 후보 행들에 대해 서브쿼리가 다른 결과를 반환해야
하는 경우(메인 쿼리에서 처리되는 각 행의 값에 따라 리턴이 달라져야 하는 경우)에 유용하다
SELECT EMP_NAME, JOB_TITLE, SALARY
FROM EMPLOYEE E
LEFT JOIN JOB J ON (E.JOB_ID = J.JOB_ID)
WHERE SALARY = (
SELECT TRUNC(AVG(SALARY),-5)
FROM EMPLOYEE
WHERE NVL(JOB_ID,' ') = NVL(E.JOB_ID,' ')
)
ORDER BY E.JOB_ID;