No.60 - Database4 : 데이터베이스 문제 모음

[문제]
1. HR 부서용으로 모든 부서의 주소를 생성하는 query를 작성합니다. LOCATIONS 및
COUNTRIES 테이블을 사용합니다. 출력에 위치 ID, 동/리, 구/군, 시/도 및 국가를 표시합니다. NATURAL JOIN을 사용하여 결과를 생성합니다.

select location_id, street_address, city, country_name
from countries natural join locations;

2. HR 부서에서 해당 부서가 있는 모든 사원에 대해 보고서를 요구합니다. 모든 사원의 성,
부서 번호 및 부서 이름을 표시하는 query를 작성합니다.

select a.last_name, a.department_id, b.department_name
from employees a join departments b
on (a.department_id = b.department_id);

3. HR 부서에서 Toronto에 근무하는 사원에 대한 보고서를 요구합니다. Toronto에서 근무하는
모든 사원의 성, 직무, 부서 번호 및 부서 이름을 표시합니다.

select emp.last_name, job.job_title, dep.department_id, dep.department_name
from jobs job join employees emp
on(job.job_id = emp.job_id)
join departments dep
on (emp.department_id = dep.department_id)
join locations loc
on (loc.location_id = dep.location_id)
where loc.city like 'Toronto';

4. 사원의 성 및 사원 번호를 해당 관리자의 성 및 관리자 번호와 함께 표시하는 보고서를
작성합니다. 열 레이블을 각각 Employee, Emp#, Manager 및 Mgr#으로 지정합니다.

select a.employee_id "Employee", a.employee_id "Emp#", b.last_name, b.manager_id "Manager"
from employees a join employees b
on(a.manager_id = b.employee_id);

5. King을 비롯하여 해당 관리자가 지정되지 않은 모든 사원을 표시하도록 합니다. 사원 번호순으로 결과를 정렬합니다.

select emp.employee_id
from employees mgr join employees emp
on (mgr.employee_id = emp.employee_id)
where emp.manager_id is null
order by 1;

SELECT w.last_name "Employee", w.employee_id "EMP#",
m.last_name "Manager", m.employee_id "Mgr#"
FROM employees w
LEFT OUTER JOIN employees m
ON (w.manager_id = m.employee_id)
order by w.employee_id asc;

6. HR 부서용으로 사원의 성과 부서 번호 및 해당 사원과 동일한 부서에 근무하는 모든 사원을
표시하는 보고서를 작성합니다. 각 열에 적절한 레이블을 지정합니다.

SELECT e.department_id department, e.last_name employee,c.last_name colleague
FROM employees e JOIN employees c
ON (e.department_id = c.department_id)
WHERE e.employee_id <> c.employee_id
ORDER BY e.department_id, e.last_name, c.last_name;

7. HR 부서에서 직책 등급 및 급여에 대한 보고서를 요구합니다. JOB_GRADES 테이블을
먼저 생성하고, JOB_GRADES 테이블의 구조를 표시합니다. 그런 다음 모든 사원의 이름,
직무, 부서 이름, 급여 및 등급을 표시하는 query를 작성합니다.

CREATE TABLE job_grades (
grade_level       CHAR(1),
lowest_sal    NUMBER(8,2) NOT NULL,
highest_sal   NUMBER(8,2) NOT NULL
);

ALTER TABLE job_grades
ADD CONSTRAINT jobgrades_grade_pk PRIMARY KEY (grade_level);

INSERT INTO job_grades VALUES ('A', 1000, 2999);
INSERT INTO job_grades VALUES ('B', 3000, 5999);
INSERT INTO job_grades VALUES ('C', 6000, 9999);
INSERT INTO job_grades VALUES ('D', 10000, 14999);
INSERT INTO job_grades VALUES ('E', 15000, 24999);
INSERT INTO job_grades VALUES ('F', 25000, 40000);

commit;

SELECT e.last_name, e.job_id, d.department_name,
e.salary, j.grade_level
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
JOIN job_grades j
ON (e.salary BETWEEN j.lowest_sal AND j.highest_sal);

8. HR 부서에서 Davies 이후에 채용된 모든 사원의 이름을 파악하려고 합니다. 사원 Davies
이후로 채용된 모든 사원의 이름과 채용 날짜를 표시하기 위한 query를 작성합니다.

SELECT last_name, hire_date
FROM employees
WHERE hire_date > (SELECT hire_date 
 		   FROM employees
		   WHERE last_name = 'Davies');

9. HR 부서에서 관리자보다 먼저 채용된 모든 사원의 이름과 채용 날짜 및 해당 관리자의
이름과 채용 날짜를 찾으려고 합니다.

SELECT emp.last_name, emp.hire_date, man.last_name,man.hire_date
FROM employees emp join employees man
on (emp.employee_id = man.manager_id)
WHERE emp.hire_date > man.hire_date;

 

 

<내부적으로 조인 동작을 확인할 수 있다.>

EXPLAIN plan for
select employee_id,department_name
from employees e JOIN departments d
USING (department_id);

SELECT * FROM table(dbms_xplan.display());




// 순서대로 해석하라는 뜻
EXPLAIN plan for
select /*+ ORDERED */ employee_id,department_name
from employees e JOIN departments d
USING (department_id);

SELECT * FROM table(dbms_xplan.display());

 

- 출력 사이즈 조정

set linesize 250;
set pagesize 5000;

// 컬럼 가로길이 사이즈 줄임
col first_name format a12 
col last_name for a12
col email for a12