23.10.16. Class 복습 겸 문제

[문제1] 2006년도에 입사한 사원들의 job_id와 그 사원들의 job_id별 급여의 총액 중에 50000 이상인 값만 출력해주세요.

SELECT job_id, SUM(salary)
FROM hr.employees
WHERE job_id IN ( SELECT job_id
                  FROM hr.employees
                  WHERE hire_date >= to_date('20060101', 'yyyymmdd') 
                  AND hire_date < to_date('20070101', 'yyyymmdd') )
GROUP BY job_id
having SUM(salary) >= 50000 ;

[문제2] location_id 가 1700인 모든 사원들의 last_name, department_id, job_id를 출력해주세요.
1)  조인

(방법1) 오라클버전

SELECT e.last_name, d.department_id, e.job_id
FROM hr.employees e, hr.departments d
WHERE e.employee_id = d.department_id
AND d.location_id = 1700;

(방법2) JOIN ON

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

 

 

(방법3) JOIN USING

SELECT e.last_name, department_id, e.job_id
FROM hr.employees e JOIN hr.departments d
USING (department_id)
WHERE d.location_id = 1700;

2) 서브쿼리

SELECT last_name, department_id, job_id
FROM hr.employees
WHERE department_id IN( SELECT department_id
                        FROM hr.departments
                        WHERE location_id = 1700 ) ;

[문제3] 60번 부서 사원들의 급여 보다 더 많은 급여를 받는 사원들의 정보를 출력해주세요.

(방법1)

SELECT *
FROM hr.employees 
WHERE salary > ( SELECT MAX(salary)
                 FROM hr.employees
                 WHERE department_id = 60);

(방법2)

SELECT *
FROM hr.employees 
WHERE salary > ALL ( SELECT salary
                     FROM hr.employees
                     WHERE department_id = 60);

[문제4] Executive 부서이름의 소속된 모든 사원에 대한 employee_id, last_name, job_id를 출력해주세요.
1) 조인

(방법1)

SELECT e.employee_id, e.last_name, e.job_id
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id
AND d.department_name = 'Executive';

 

(방법2) JOIN ON

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

(방법3) JOIN USING

SELECT e.employee_id, e.last_name, e.job_id
FROM hr.employees e JOIN hr.departments d
USING( department_id )
WHERE d.department_name = 'Executive';

2) 서브쿼리

SELECT employee_id, last_name, job_id
FROM hr.employees
WHERE department_id = ( SELECT department_id
                        FROM hr.departments
                        WHERE department_name = 'Executive' );

[문제5] job_grades 테이블에서 사원들의 급여 등급에 포함(EXISTS)된 등급정보를 출력해주세요.

ⓐ
SELECT *
FROM hr.job_grades;

ⓑ
SELECT *
FROM hr.job_grades j, hr.employees e
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

ⓒ
SELECT j.grade_level
FROM hr.job_grades j, hr.employees e
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

ⓓ -1
SELECT *
FROM hr.job_grades
WHERE grade_level IN ( SELECT j.grade_level
                       FROM hr.job_grades j, hr.employees e 
                       WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal );

ⓓ -2
SELECT *
FROM hr.job_grades j
WHERE EXISTS ( SELECT 'X'
               FROM hr.employees
               WHERE salary BETWEEN j.lowest_sal AND j.highest_sal );

[문제6] job_grades 테이블에서 사원들의 급여 등급에 포함(NOT EXISTS)되지 않은 등급정보를 출력해주세요.

SELECT *
FROM hr.job_grades
WHERE grade_level NOT IN ( SELECT j.grade_level
                           FROM hr.job_grades j, hr.employees e 
                           WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal );

SELECT *
FROM hr.job_grades j
WHERE NOT EXISTS ( SELECT 'X'
                   FROM hr.employees
                   WHERE salary BETWEEN j.lowest_sal AND j.highest_sal );

 

[문제7] 부서별로 인원수를 출력주세요

1) DECODE 함수

SELECT
    count(decode (department_id, 10, 'x')) as "10", 
    count(decode (department_id, 20, 'x')) as "20",
    count(decode (department_id, 30, 'x')) as "30",
    count(decode (department_id, 40, 'x')) as "40",
    count(decode (department_id, 50, 'x')) as "50",
    count(decode (department_id, 60, 'x')) as "60",
    count(decode (department_id, 70, 'x')) as "70",
    count(decode (department_id, 80, 'x')) as "80",
    count(decode (department_id, 90, 'x')) as "90",
    count(decode (department_id, 100, 'x')) as "100",
    count(decode (department_id, 110, 'x')) as "110",
    count(decode (department_id, NULL, 'x')) as "부서가 없는 사원"
FROM hr.departments;

2) CASE WHEN THEN END

SELECT
    count(case when department_id = 10 then 'x' end ) as "10" ,
    count(case when department_id = 20 then 'x' end) as "20" ,
    count(case when department_id= 30 then 'x' end) as "30",
    count(case when department_id= 40 then 'x' end) as "40",
    count(case when department_id is NULL then 'x' end) as "부서가 없는 사원"
FROM hr.departments;