[Oracle]2022.07.24 서브쿼리 실습문제

--문제1
--기술지원부에 속한 사람들의 사람의 이름,부서코드,급여를 출력하시오

SELECT 
    EMP_NAME,
    DEPT_CODE,
    SALARY
FROM EMPLOYEE
WHERE DEPT_CODE IN(SELECT DEPT_ID FROM DEPARTMENT WHERE DEPT_TITLE='기술지원부');

--문제2
--기술지원부에 속한 사람들 중 가장 연봉이 높은 사람의 이름,부서코드,급여를 출력하시오

기술지원부의 부서코드 구하기

SELECT MAX(SALARY)FROM EMPLOYEE
WHERE DEPT_CODE IN(SELECT DEPT_ID FROM DEPARTMENT WHERE DEPT_TITLE='기술지원부');

본코드

SELECT 
    EMP_NAME,
    DEPT_CODE,
    SALARY
FROM EMPLOYEE
WHERE SALARY =(SELECT MAX(SALARY)FROM EMPLOYEE 
WHERE DEPT_CODE IN(SELECT DEPT_ID FROM DEPARTMENT WHERE DEPT_TITLE='기술지원부'));

--문제3
--매니저가 있는 사원중에 월급이 전체사원 평균을 넘고
--사번,이름,매니저 이름,월급(만원단위부터)을 구하시오
--* 단, JOIN을 이용하시오

 SELECT
    EMP_ID
    ,EMP_NAME
    ,(SELECT EMP_NAME FROM EMPLOYEE M WHERE E.MANAGER_ID=M.EMP_ID)
    ,SALARY
FROM EMPLOYEE E
WHERE MANAGER_ID IS NOT NULL AND SALARY > (SELECT AVG(SALARY) FROM EMPLOYEE);

--문제4
--부서 별 각 직급마다 급여 등급이 가장 높은 직원의 이름, 직급코드, 급여, 급여등급 조회

부서별 가장 높은 급여등급 조회

SELECT 
    DEPT_CODE,
    MAX(SAL_LEVEL)
FROM EMPLOYEE
GROUP BY DEPT_CODE;

본코드

SELECT 
    DEPT_CODE,
    EMP_NAME,
    JOB_CODE,
    SALARY,
    SAL_LEVEL
FROM EMPLOYEE
WHERE (DEPT_CODE, SAL_LEVEL) IN (SELECT DEPT_CODE, MAX(SAL_LEVEL)FROM EMPLOYEE
GROUP BY DEPT_CODE)
ORDER BY 1;

--문제5
--부서별 평균 급여가 2200000 이상인 부서명, 평균 급여 조회
--단, 평균 급여는 소수점 버림

SELECT
    DEPT_TITLE
    , FLOOR(AVG(SALARY))
FROM EMPLOYEE
JOIN DEPARTMENT ON DEPT_ID=DEPT_CODE
GROUP BY DEPT_TITLE
HAVING FLOOR(AVG(SALARY))>2200000;

--문제6
--직급의 연봉 평균보다 적게 받는 여자사원의
--사원명,직급코드,부서코드,연봉을 이름 오름차순으로 조회하시오
--연봉 계산 => (급여+(급여보너스))12
-- 사원명,직급명,부서명,연봉은 EMPLOYEE 테이블을 통해 출력이 가능함

직급별 평균 연봉 테이블 구하기

SELECT JOB_CODE, AVG((SALARY+(SALARY*NVL(BONUS,0)))*12) 
FROM EMPLOYEE GROUP BY JOB_CODE;

본코드

SELECT 
    E.EMP_NAME,
    E.JOB_CODE,
    E.DEPT_CODE,
    (SALARY+(SALARY*NVL(BONUS,0)))*12"개인연봉"
FROM EMPLOYEE E,
(SELECT JOB_CODE, 
AVG((SALARY+(SALARY*NVL(BONUS,0)))*12)"부서평균연봉" 
FROM EMPLOYEE 
GROUP BY JOB_CODE) M
WHERE E.JOB_CODE = M.JOB_CODE 
AND (SALARY+(SALARY*NVL(BONUS,0)))*12< M.부서평균연봉 
AND SUBSTR(EMP_NO,8,1)=2
ORDER BY SALARY ASC;