오라클 hr 연습문제(21~30)

-- 21. 동일한 직업을 가진 사원들의 총 수를 조회한다.

SELECT
    job_id,
    COUNT(employee_id) emp
FROM
    employees
GROUP BY
    job_id
ORDER BY
    job_id ASC;

-- 22. 매니저로 근무하는 사원들의 총 수를 조회한다.

SELECT
    COUNT(DISTINCT manager_id)
FROM
    employees
WHERE
    employee_id IN (
        SELECT DISTINCT
            manager_id
        FROM
            employees
        WHERE
            manager_id IS NOT NULL
    );

-- 23. 사내의 최대 연봉 및 최소 연봉의 차이를 조회한다.

SELECT
    MAX(salary) - MIN(salary)
FROM
    employees;

-- 24. 매니저의 사번 및 그 매니저 밑 사원들 중 최소 연봉을 받는 사원의 연봉을 조회한다.
--     매니저가 없는 사람들은 제외한다.
--     최소 연봉이 6000 미만인 경우는 제외한다.
--     연봉 기준 역순으로 조회한다.

SELECT
    manager_id,
    MIN(salary)
FROM
    employees
WHERE
    manager_id IS NOT NULL
GROUP BY
    manager_id
HAVING
    MIN(salary) >= 6000
ORDER BY
    MIN(salary) DESC;

-- 25. 부서 명, 위치 ID, 각 부서 별 사원 총 수, 각 부서 별 평균 연봉을 조회한다.
--     평균 연봉은 소수점 2 자리까지만 표현한다.

SELECT
    department_name,
    location_id,
    COUNT(employee_id),
    round(
        AVG(salary), 2
    ) avg_salary
FROM
    employees   e,
    departments d
WHERE
    e.department_id = d.department_id
GROUP BY
    department_name,
    location_id
ORDER BY
    location_id ASC;

-- 26. 총 사원 수 및 2001, 2003, 2005, 2006 년도 별 고용된 사원들의 총 수를 다음과 같이 조회한다.

SELECT DISTINCT
    (
        SELECT
            COUNT(employee_id)
        FROM
            employees
    ) AS countall,
    (
        SELECT
            COUNT(employee_id)
        FROM
            employees
        WHERE
            hire_date LIKE '2001%'
    ) AS count2001,
    (
        SELECT
            COUNT(employee_id)
        FROM
            employees
        WHERE
            hire_date LIKE '2003%'
    ) AS count2003,
    (
        SELECT
            COUNT(employee_id)
        FROM
            employees
        WHERE
            hire_date LIKE '2005%'
    ) AS count2005,
    (
        SELECT
            COUNT(employee_id)
        FROM
            employees
        WHERE
            hire_date LIKE '2006%'
    ) AS count2006
FROM
    employees;

-- 27.  각 부서별 각 직업 별 연봉 총 합 및 각 부서별 연봉 총 합을 조회한다.

SELECT
    department_id,
    job_id,
    SUM(salary),
    (
        SELECT
            SUM(salary)
        FROM
            employees e1
        WHERE
            e1.department_id = e2.department_id
    ) sumall
FROM
    employees e2
GROUP BY
    department_id,
    job_id
ORDER BY
    department_id,
    job_id;

-- 28. LAST_NAME 이 Zlotkey 와 동일한 부서에 근무하는 모든 사원들의 사번 및 고용날짜를 조회한다.
--     결과값에서 Zlotkey 는 제외한다.

SELECT
    employee_id,
    hire_date
FROM
    employees
WHERE
    department_id IN (
        SELECT
            department_id
        FROM
            employees
        WHERE
            last_name = 'Zlotkey'
    )
    AND last_name != 'Zlotkey'
ORDER BY
    employee_id ASC;

-- 29. 회사 전체 평균 연봉보다 더 받는 사원들의 사번 및 LAST_NAME 을 조회한다.

SELECT
    employee_id,
    last_name
FROM
    employees
WHERE
    salary > (
        SELECT
            AVG(salary)
        FROM
            employees
    )
ORDER BY
    employee_id ASC;

-- 30. LAST_NAME 에 u 가 포함되는 사원들과 동일 부서에 근무하는 사원들의 사번 및 LAST_NAME 을 조회한다.

SELECT
    employee_id,
    last_name
FROM
    employees
WHERE
    department_id IN (
        SELECT
            department_id
        FROM
            employees
        WHERE
            last_name LIKE '%u%'
    )
ORDER BY
    employee_id ASC;