SQL-hr 실습4

hr 실습은 hr 생성 후 작성 가능.

문제를 다 푼 다음에 답 보기.

hr의 데이터가 많기 때문에 각각의 칼럼명을 알고 있어야 문제를 풀 수 있다.

 

<문제>

문제 1) 부서별 급여의 평균 연봉 출력(부서번호별 오름차순)

문제 2) 동일한 직업을 가진 사원들의 수 출력

문제 3) 자신의 담당 매니저의 고용일보다 빠른 입사자 찾기(employees 셀프조인)

문제 4) 도시 이름이 T로 시작하는 지역에 사는 사원들의 사번, last_name, department_id, city 출력 (employess, departments, locations 테이블 조인)

문제 5) 위치 ID가 1700인 사원들의 employee_id, last_name, department_id, salary 출력 (employees, departments 조인)

문제 6) 각 부서별 평균 연봉(소수점 2자리까지), 사원 수 조회. department_name, location_id,sal_avg, cnt출력.  (employess, departments 조인)

문제 7) Executive 부서에 근무하는 모든 사원들의 department_id, last_name, job_id 출력. (employees, departmens 조인)

문제 8) 기존의 직업을 여전히 가지고 있는 사원들의 employee_id, job_id출력. (job_history 내부조인)

문제 9) 각 사원별 소속부서에서 자신보다 늦게 고용되었으나 보다 많은 연봉을 받는 사원의 epartmen_id, last_name, salary, hire_date 출력. (employees 셀프 조인)

 

<정답>

꼭 이것만 정답은 아닙니다. 다른 방법이 있을 수 있습니다.

문제1-답)

SELECT
    department_id,
    round(AVG(salary))
FROM
    employees
GROUP BY
    department_id
ORDER BY
    department_id;

 

문제2-답)

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

 

문제3-답) 

SELECT
    e1.employee_id,
    e1.hire_date AS 내입사일,
    e1.manager_id,
    e2.hire_date AS 매니저입사일
FROM
         employees e1
    JOIN employees e2 ON e1.manager_id = e2.employee_id
WHERE
    e1.hire_date < e2.hire_date
ORDER BY
    e1.employee_id;

 

문제4-답)

SELECT
    employee_id,
    last_name,
    d.department_id,
    city
FROM
         employees e
    JOIN departments d ON e.department_id = d.department_id
    JOIN locations   l ON d.location_id = l.location_id
WHERE
    city LIKE 'T%';

 

문제5-답)

SELECT
    employee_id,
    last_name,
    e.department_id,
    salary
FROM
         employees e
    JOIN departments d ON e.department_id = d.department_id
WHERE
    d.location_id = 1700;

 

문제6-답)

SELECT
    department_name,
    location_id,
    round(AVG(salary), 2) AS sal_avg,
    COUNT(employee_id)    AS cnt
FROM
         employees e
    JOIN departments d ON e.department_id = d.department_id
GROUP BY
    d.department_name,
    location_id
ORDER BY
    location_id;

 

문제7-답)

SELECT
    e.department_id,
    e.last_name,
    e.job_id
FROM
         employees e
    JOIN departments d ON e.department_id = d.department_id
WHERE
    d.department_name = 'Executive';

 

문제8-답)

SELECT
    e.employee_id,
    e.job_id
FROM
         employees e
    JOIN job_history j ON e.employee_id = j.employee_id
WHERE
    e.job_id = j.job_id;

 

문제9-답)

SELECT DISTINCT
    e2.department_id,
    e2.last_name,
    e2.salary,
    e2.hire_date
FROM
         employees e1
    JOIN employees e2 ON e1.department_id = e2.department_id
WHERE
        e1.hire_date < e2.hire_date
    AND e1.salary < e2.salary
ORDER BY
    e2.department_id;