Database_61_SQL 활용 실습 6

 

    -- PRACTICE 6
--  LOCATIONS 및 COUNTRIES 테이블을 사용하여 HR 부서를 위해 모든 부서의 주소를 생성하는 query를 작성
    -- 출력에 위치 ID, 주소, 구/군, 시/도 및 국가를 표시하며, NATURAL JOIN을 사용하여 결과를 생성
SELECT location_id, street_address, city, state_province, country_name
FROM locations NATURAL JOIN countries;

-- 모든 사원의 성, 소속 부서번호 및 부서 이름을 표시하는 query를 작성
SELECT last_name, department_id, department_name
FROM employees JOIN departments
USING (department_id);

-- toronto에서 근무하는 모든 사원의 성, 직무, 부서 번호 및 부서 이름을 표시
    -- (힌트 : 3-way join 사용)
SELECT e.last_name, e.job_id, e.department_id, d.department_name
FROM employees e JOIN departments d
ON (e.department_id=d.department_id)
JOIN locations l
ON (d.location_id=l.location_id)
WHERE LOWER(l.city)='toronto';

-- 사원의 성 및 사원 번호를 해당 관리자의 성 및 관리자 번호와 함께 표시하는 보고서를 작성하는데, 
    -- 열 레이블을 각각 Employee, Emp#, Manager 및 Mgr#으로 지정
SELECT w.last_name AS "Employee", w.employee_id AS "Emp#", m.last_name AS "Manager", m.employee_id AS "Mgr#"
FROM employees w JOIN employees m
ON (w.manager_id=m.employee_id);

-- King과 같이 해당 관리자가 지정되지 않은 모든 사원을 표시하도록 이전 문장을 수정합니다. 사원 번호순으로 결과를 정렬
SELECT w.last_name AS "Employee", w.employee_id AS "Emp#", m.last_name AS "Manager", m.employee_id AS "Mgr#"
FROM employees w LEFT OUTER JOIN employees m
ON (w.manager_id=m.employee_id)
ORDER BY 2;

-- 사원의 성과 부서 번호 및 주어진 사원과 동일한 부서에 근무하는 모든 사원을 표시하는 보고서를 작성하시오. 
    -- 각 열에 적절한 레이블을 자유롭게 지정
SELECT e.last_name AS "Name", e.department_id AS dp_id, c.last_name AS "Crew"
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;

-- HR 부서에서 직무 등급 및 급여에 대한 보고서를 필요로 합니다. 
    -- 먼저 JOB_GRADES 테이블의 구조를 표시한 다음 모든 사원의 이름, 직무, 부서 이름, 급여 및 등급을 표시하는 query를 작성
DESC JOB_GRADES;
    /*
    이름              널?     유형          
    -----------       --    ----------- 
    GRADE_LEVEL         VARCHAR2(3) 
    LOWEST_SAL          NUMBER      
    HIGHEST_SAL         NUMBER      
    */

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);