=====================
view
=====================
1)
인사관리를 위한 시스템 구축중 지역(Regions), 국가(Countries), 위치(Locations)에 대한 전체 리스트 정보를 빈번하게 사용할 경우가 생겼다. 여러 프로그램에서 매번 반복적으로 사용되며 일부 개발자의 경우 잘못된 조인으로 올바른 결과를 가져오지 못하는 경우가 발생하였다. 이를 해결하기 위해 복잡한 질의를 숨기기 위한 논리적 테이블의 용도로 쓰이는 DB Object 를 생성하기로 하였다.
DB Object 이름은 LOC_DETAILS_{DB OBJECT명} 을 사용한다.
위 설명에 해당하는 DB Object를 생성하기 위한 스크립트를 작성하라.
[DB Object 조회 결과 ? 화면에 다음과 같은 결과를 출력할 수 있는 DB Object를 생성하라]
conn hr/hr
create or replace view LOC_DETAILS_view
as
select r.region_id, c.country_id, l.location_id,
region_name, country_name,
street_address, postal_code,
city, state_province
from regions r, countries c, locations l
where r.region_id = c.region_id
and c.country_id = l.country_id
order by region_id , country_id;
regions : 각 대륙정보(대륙코드, 대륙명)
countries : 각 국가정보(국가코드, 국가명, ..대륙코드)
locations : 각 도시정보(우편번호, 거리이름, 도시명, 국가코드)
2) Sales 부서에서 Gerald Cambrault 라는 관리자(Manager_id)를 가지는 직원의 정보를 조회하여 employee_sales_Gerald 라는 view를 생성하십시오.
사원번호 | 사원명 | 부서명 | 관리자명
create or replace view employee_sales_Gerald
as
select employee_id, department_name, e.manager_id
from employees e, departments d
where e.manager_id =
(select employee_id from employees
where first_name='Gerald'
and last_name='Cambrault')
and department_name='Sales'
and e.department_id=d.department_id
3) 사원이 한 명 이상인 부서들에 대해 부서 번호, 부서 이름, 사원 수, 최고 급여, 최저 급여, 평균 급여, 급여 총액을 빈번하게 조회하여야 할 필요가 있어, VIEW를 생성하여 사용하고자 합니다.
생성된 DB Object(VIEW)의 전체 레코드에 대한 조회 결과가 아래와 같이 출력되도록 "DEPT_SAL_INFO"라는 VIEW 를 생성하는 sql 스크립트를 작성하십시오.
단, 평균 급여의 소수점 이하는
절사하고 view는 수정 가능하도록 생성합니다.
출력 :
DEPT_ID | DEPT_NAME | NUM_EMP | MAX_SAL | MIN_SAL | AVG_SAL | SUM _SAL
CREATE OR REPLACE VIEW DEPT_SAL_INFO
AS
SELECT D.DEPARTMENT_ID, DEPARTMENT_NAME,
COUNT(EMPLOYEE_id) 사원수, MAX(SALARY) "최고 급여",
MIN(SALARY) "최저 급여", AVG(SALARY) "평균 급여",
SUM(SALARY) "급여 총액"
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_ID, DEPARTMENT_NAME
HAVING COUNT(EMPLOYEE_id) >= 1;
4)
1999년 입사한(hire_date) 직원들의 사번(employee_id), 이름(first_name), 성(last_name), 부서명(department_name)을 조회하려 합니다.
임시 테이블 용도로 사용하는 Object를 생성합니다.
이름은 emp_1999_details 로 지정하고, 부서에 배치되지 않은 직원의 경우, '<NOT ASSIGNED>'로 보여줍니다.
create or replace view emp_1999_details
(사번, 이름, 성, 부서)
as
select employee_id , first_name,
last_name,
nvl(department_name,'<NOT ASSIGNED>')
from employees e, departments d
where substr(hire_date, 1, 2) = '99'
and d.department_id(+) = e.department_id;