SUBQUERY

Q. 전 직원의 평균 급여보다 급여를 더 적게받는 사원들의 사원명, 직급코드, 급여 조회

SELECT
    emp_name,
    job_code,
    salary
FROM
    employee
WHERE
    salary < (
        SELECT
           round(AVG(salary))
        FROM
            employee
    );

Q. 최저급여를 받는 사원의 사번, 이름, 급여, 입사일 조회

SELECT
    emp_id,
    emp_name,
    salary,
    hire_date
FROM
    employee
WHERE
    salary = (
        SELECT
            MIN(salary)
        FROM
            employee
    );

Q. 노옹철 사원의 급여보다 많이받는 사원들의 사번, 이름, 부서명, 급여조회

SELECT
    emp_id,
    emp_name,
    dept_title,
    salary
FROM
    employee
    LEFT JOIN department ON ( dept_code = dept_id )
WHERE
    salary > (
        SELECT
            salary
        FROM
            employee
        WHERE
            emp_name = '노옹철'
    );

Q. 부서별 급여합이 가장 큰 부서의 부서코드, 급여합

SELECT
    dept_code,
    SUM(salary)
FROM
    employee
GROUP BY
    dept_code
HAVING
    SUM(salary) = (
        SELECT
            MAX(SUM(salary))
        FROM
            employee
        GROUP BY
            dept_code
    );

Q. '전지연'사원과 같은 부서의 사람들의 사번, 사원명, 전화번호, 입사일, 부서명 조회(단, 전지연 사원 제외)

SELECT
    emp_id,
    emp_name,
    phone,
    hire_date,
    dept_title
FROM
         employee
    JOIN department ON ( dept_code = dept_id )
WHERE
        dept_code = (
            SELECT
                dept_code
            FROM
                employee
            WHERE
                emp_name = '전지연'
        )
    AND emp_name != '전지연';

Q. 박나라 사원과 같은 직급코드, 같은 사수를 가지고있는 사원들의 사번, 사원명, 직급코드, 사수번호(단, 박나라 사원 제외)

SELECT
    emp_id,
    emp_name,
    job_code,
    manager_id
FROM
    employee
WHERE
        ( job_code,
          manager_id ) = (
            SELECT
                job_code,
                manager_id
            FROM
                employee
            WHERE
                emp_name = '박나라'
        )
    AND emp_name != '박나라';

Q. 각 부서별 최고급여를 받는 사원들의 사번, 사원명, 부서코드, 급여

SELECT
    emp_id,
    emp_name,
    dept_code,
    salary
FROM
    employee
WHERE
    ( dept_code, salary ) IN (
        SELECT
            dept_code, MAX(salary)
        FROM
            employee
        GROUP BY
            dept_code
    );

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

SELECT
    dept_title,
    SUM(salary)
FROM
         employee
    JOIN department ON ( dept_code = dept_id )
GROUP BY
    dept_title
HAVING
    SUM(salary) > (
        SELECT
            SUM(salary) * 0.2
        FROM
            employee
    );

Q. 나이 상 가장 막내의 사원 코드, 사원 명, 나이, 부서 명, 직급 명 조회

SELECT
    emp_id,
    emp_name,
    EXTRACT(YEAR FROM sysdate) -
    CASE
        WHEN substr(emp_no, 1, 2) <= to_char(sysdate, 'YY') THEN
                2000 + to_number(substr(emp_no, 1, 2))
        ELSE
            1900 + to_number(substr(emp_no, 1, 2))
    END
    + 1 AS "나이",
    dept_title,
    job_name
FROM
         employee
    JOIN department ON ( dept_code = dept_id )
    JOIN job USING ( job_code )
WHERE
    EXTRACT(YEAR FROM sysdate) -
    CASE
        WHEN substr(emp_no, 1, 2) <= to_char(sysdate, 'YY') THEN
                2000 + to_number(substr(emp_no, 1, 2))
        ELSE
            1900 + to_number(substr(emp_no, 1, 2))
    END
    = (
        SELECT
            MIN(EXTRACT(YEAR FROM sysdate) -
                CASE
                    WHEN substr(emp_no, 1, 2) <= to_char(sysdate, 'YY') THEN
                        2000 + to_number(substr(emp_no, 1, 2))
                    ELSE
                        1900 + to_number(substr(emp_no, 1, 2))
                END
            )
        FROM
            employee
    );

Q. 가장 최근에 입사한 사원 5명 조회(사원명, 급여, 입사일)

SELECT
    ROWNUM,
    emp_name,
    salary,
    hire_date
FROM
    (
        SELECT
            *
        FROM
            employee
        ORDER BY
            hire_date DESC
    )
WHERE
    ROWNUM <= 5;

Q. 부서별 평균급여가 높은 3개의 부서 조회

SELECT
    dept_code,
    평균급여
FROM
    (
        SELECT
            dept_code,
            ROUND(AVG(salary)) AS "평균급여"
        FROM
            employee
        GROUP BY
            dept_code
        ORDER BY
            AVG(salary) DESC
    )
WHERE
    ROWNUM <= 3;