[Oracle] JOIN 및 subquery 실습문제

-- 1. 70년대 생(1970~1979) 중 여자이면서 전씨인 사원의 이름과 주민번호, 부서 명, 직급 조회
SELECT * FROM EMPLOYEE;
SELECT * FROM DEPARTMENT;
SELECT * FROM JOB;

SELECT EMP_NAME, EMP_NO, DEPT_TITLE, JOB_NAME
FROM EMPLOYEE
JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
JOIN JOB USING (JOB_CODE)
WHERE SUBSTR(EMP_NO,1,2) BETWEEN 70 AND 79 
AND SUBSTR(EMP_NO,8,1) = 2
AND EMP_NAME LIKE '전%';

--2. 나이 상 가장 막내의 사원 코드, 사원 명, 나이, 부서 명, 직급 명 조회
SELECT * 
FROM (SELECT EMP_ID, EMP_NAME, EXTRACT(YEAR FROM SYSDATE) - CONCAT(19,SUBSTR(EMP_NO,1,2))+ 1 AS "나이", DEPT_TITLE, JOB_NAME
        FROM EMPLOYEE
        JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
        JOIN JOB USING (JOB_CODE)
        ORDER BY 나이)
WHERE ROWNUM = 1;

--3. 이름에 ‘형’이 들어가는 사원의 사원 코드, 사원 명, 직급 조회
SELECT EMP_ID, EMP_NAME, JOB_NAME
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE EMP_NAME LIKE '%형%';

--4. 부서코드가 D5이거나 D6인 사원의 사원 명, 직급 명, 부서 코드, 부서 명 조회
SELECT * --EMP_NAME, JOB_NAME, DEPT_CODE, DEPT_TITLE
FROM EMPLOYEE
JOIN JOB USING (JOB_CODE)
JOIN DEPARTMENT ON(DEPT_ID = DEPT_CODE)
WHERE DEPT_CODE IN ('D5','D6')
ORDER BY DEPT_CODE DESC, EMP_ID;

--5. 보너스를 받는 사원의 사원 명, 부서 명, 지역 명 조회
SELECT * FROM LOCATION;
SELECT EMP_NAME, BONUS, DEPT_TITLE, LOCAL_NAME
FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
WHERE BONUS IS NOT NULL;

--6. 사원 명, 직급 명, 부서 명, 지역 명 조회
SELECT EMP_NAME, JOB_NAME, DEPT_TITLE, LOCAL_NAME
FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
JOIN JOB USING (JOB_CODE)
JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE);

--8. 한 사원과 같은 부서에서 일하는 사원의 이름 조회
SELECT * FROM DEPARTMENT;
SELECT B.EMP_NAME, DEPT_CODE, A.EMP_NAME
FROM EMPLOYEE A
JOIN EMPLOYEE B USING (DEPT_CODE)
WHERE B.EMP_NAME != A.EMP_NAME
ORDER BY B.EMP_NAME;

--9. 보너스가 없고 직급 코드가 J4이거나 J7인 사원의 이름, 직급 명, 급여 조회
SELECT EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE
JOIN JOB USING (JOB_CODE)
WHERE JOB_CODE IN (SELECT JOB_CODE 
                    FROM EMPLOYEE
                    WHERE JOB_CODE IN ('J4', 'J7')
                    AND BONUS IS NULL);

--10. 보너스 포함한 연봉이 높은 5명의 사번, 이름, 부서 명, 직급, 입사일, 순위 조회 (NVL 이용)
SELECT * FROM EMPLOYEE;
SELECT 사번, 이름, 부서명, 직급, 입사일, ROWNUM AS "순위"
FROM (SELECT EMP_ID AS "사번", EMP_NAME AS "이름", DEPT_TITLE AS "부서명", JOB_NAME AS "직급", HIRE_DATE AS "입사일", (SALARY + SALARY * NVL(BONUS,0)) *12 AS "연봉"
        FROM EMPLOYEE
        JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
        JOIN JOB USING (JOB_CODE)
        ORDER BY 연봉 DESC)
WHERE ROWNUM <= 5;

--11. 부서 별 급여 합계가 전체 급여 총 합의 20%보다 많은 부서의 부서 명, 부서 별 급여 합계 조회

--11-1. JOIN과 HAVING 사용
SELECT * FROM DEPARTMENT;
SELECT * FROM EMPLOYEE;

SELECT DEPT_TITLE, SUM(SALARY)
FROM DEPARTMENT
JOIN EMPLOYEE ON (DEPT_ID = DEPT_CODE)
GROUP BY DEPT_TITLE
HAVING SUM(SALARY) >= (SELECT SUM(SALARY)*0.2 FROM EMPLOYEE); 

--11-2. 인라인 뷰 사용
SELECT 부서명, 부서_별_급여_합계
FROM (SELECT DEPT_TITLE AS "부서명", SUM(SALARY) AS "부서_별_급여_합계"
        FROM DEPARTMENT
        JOIN EMPLOYEE ON (DEPT_ID = DEPT_CODE)
        GROUP BY DEPT_TITLE)
WHERE 부서_별_급여_합계 >= (SELECT SUM(SALARY) * 0.2
                            FROM EMPLOYEE);

--12. 부서 명과 부서 별 급여 합계 조회
SELECT DEPT_TITLE, SUM(SALARY)
FROM DEPARTMENT
RIGHT JOIN EMPLOYEE ON (DEPT_CODE = DEPT_ID)
GROUP BY DEPT_TITLE;

티스토리툴바