23.10.13. Class 복습 겸 문제

SQL 실행순서 

FROM > ON > JOIN > WHERE > GROUP BY > HAVING > SELECT > ORDER BY

tip) ON절의 조건인 JOIN이 되면서 실행되고, WHERE절의 조건은 JOIN이 모두 끝나고 나서 실행됩니다. ON과 WHERE를 같이 사용할 때와, ON만 사용할 때의 결과가 같다면 ON만 사용하는 것이 좋다.

[문제1] 2006년도에 입사한 사원들의 부서이름별 급여의 총액, 평균을 출력해주세요.

1) 오라클 버전

SELECT d.department_name 부서명, SUM(e.salary) 총액급여, ROUND(AVG(e.salary)) 평균 급여
FROM hr.employees e, hr.departments h
WHERE e.hire_date >= to_date('20060101', 'yyyymmdd')
AND e.hire_date < to_date('20070101', 'yyyymmdd')
AND e.department_id = d.department_id
GROUP BY d.department_name;

2) ANSI 표준  (JOIN - ON)

SELECT d.department_name 부서명, SUM(e.salary) 총액급여, ROUND(AVG(e.salary)) 평균 급여
FROM hr.employees e JOIN hr.departments h
ON e.department_id = d.department_id
WHERE e.hire_date >= to_date('20060101', 'yyyymmdd')
AND e.hire_date < to_date('20070101', 'yyyymmdd')
GROUP BY d.department_name;

 

TIP) 시간 범위까지 예민하게 생각해주자! 

SELECT *
FROM hr.employees e
WHERE e.hire_date >= to_date('20060101', 'yyyymmdd')
AND e.hire_date <= to_date('20061231', 'yyyymmdd') ;

ex) 쇼핑몰 table에서 주문 컬럼에서 2006년의 data 출력해주세요. 

    - 2006년 12월 31일 00:12에 주문, 15:01에 주문, 23:57에 주문 등 여러 경우가 있다. 

 

위와 같이 to_date('20061231', 'yyyymmdd') 로 쿼리문을 작성했을 경우에는 

이렇게 나오기 때문에, 시간별로 주문했을 때에는 카운트를 할 수가 없다. 

 

경우의 수는 2 가지이다. 

첫 번째, 시간까지 작성하는 경우

SELECT *
FROM hr.employees e 
WHERE e.hire_date >= to_date('20060101', 'yyyymmdd')
AND e.hire_date <= to_date('20061231 23:59:59', 'yyyymmdd HH24:MI:SS') ;

두 번째, 하루 늘려서 작성하는 경우

SELECT *
FROM hr.employees e 
WHERE e.hire_date >= to_date('20060101', 'yyyymmdd')
AND e.hire_date < to_date('20070101', 'yyyymmdd');

 

[문제2] 2007년도에 입사한 사원들의 도시이름별 급여의 총액, 평균을 출력해주세요.
(단, 부서 배치를 받지 않은 사원들의 정보도 출력해주세요.)

1) 오라클 버전

SELECT l.city 도시별, SUM(e.salary) 총액, AVG(e.salary) 평균 급여 
FROM hr.employees e, hr.departments h, hr.locations l
WHERE e.hire_date >= to_date('20070101', 'yyyymmdd')
AND e.hire_date < to_date('20080101', 'yyyymmdd')
AND e.department_id = d.department_id(+)
AND d.location_id = l.location_id(+)
GROUP BY l.cityl

 

2) ANSI 표준 (OUTER JOIN - ON)

SELECT l.city 도시별, SUM(e.salary) 총액, AVG(e.salary) 평균 급여 
FROM hr.employees e LEFT OUTER JOIN hr.departments h
ON e.department_id = d.department_id
LEFT OUTER JOIN hr.locations l
ON d.location_id = l.location_id(+)
WHERE e.hire_date >= to_date('20070101', 'yyyymmdd')
AND e.hire_date < to_date('20080101', 'yyyymmdd')
GROUP BY l.cityl

 

[문제3] last_name에 a문자가 2개 이상 포함되어 있는 사원들의 last_name,salary,grade_level, department_name을 출력해주세요.

1) 오라클버전

SELECT e.last_name, e.salary, j.grade_level, d.department_name
FROM hr.employees e, hr.departments d, hr.job_grades j
WHERE instr(e.last_name, 'a', 1, 2) >= 2
AND e.salary BETWEEN j.lowest_sal AND j.higest_sal
AND e.department_id = d.department_id;

 

2) ANSI 표준 (JOIN - ON )

SELECT e.last_name, e.salary, j.grade_level, d.department_name
FROM hr.employees e JOIN hr.job_grades j
ON e.salary BETWEEN j.lowest_sal AND j.higest_sal
JOIN hr.departments d
ON e.department_id = d.department_id
WHERE instr(e.last_name, 'a', 1, 2) >= 2 ;

 

[문제4] 담당관리자(직속상관)보다 먼저 입사한 사원의 이름과 입사일 및 해당 관리자의 이름과 입사일 출력해주세요.

1) 오라클 버전

SELECT w.employee_id, w.last_name, w.hire_date, m.employee_id, m.last_name, m.hire_date 
FROM hr.employees w, hr.employees m 
	-- 일반 직원, 직속상관
WHERE w.manager_id = m.employee_id
AND w.hire_date < m.hire_date ;

 

2) ANSI 표준 (SELF JOIN )

SELECT w.employee_id, w.last_name, w.hire_date, m.employee_id, m.last_name, m.hire_date
FROM hr.employees w JOIN hr.employees m
ON w.manager_id = m.employee_id
WHERE w.hire_date < m.hire_date;