view (뷰) 생성, 수정, 인라인뷰(inline view), Top-N 실습

--view의 생성

create or replace view empvu80

as select employee_id, last_name, salary

from employees

where department_id = 80;

 

desc empview80;

 

select *

from empview80;

 

create view salvu50

    as select employee_id ID_NUMBER, last_name NAME, salary*12 ANN_SALARY

    from employees

    where department_id = 50;

   

 

select *

from salvu50;

 

drop view salvu50;

 

create view salvu50(ID_NUMBER, NAME, ANN_SALARY)

    as select employee_id, lasT_name, salary*12

    from employees

    where department_id = 50;

 

--view 이름 변경 (일반적인 테이블 이름 변경과 동일)

rename empview80 to empvu80;

 

--view의 수정

create or replace view empvu80

    (id_number, name, sal, department_id)

    as select employee_id, first_name || ' ' || last_name, salary, department_id

    from employees

    where department_id = 80;

   

   

create view dept_sum_vu

    (name, minsal, maxsal, avgsal)

    as select d.department_name, Min(e.salary), max(e.salary), avg(e.salary)

    from employees e, departments d

    where e.department_id = d.department_id

    group by d.department_name;

 

select *

from dept_sum_vu

order by name;

 

select *

from empvu80;

 

update empvu80

set last_name = 'Russell'

where employee_id = 145;

 

select *

from employees

where employee_id = 145;

 

--인라인 뷰

select a.last_name, a.salary, a.department_id, b.avgsal

from employees a, (select department_id, avg(salary) avgsal

                    from employees

                    group by department_id

                    ) b

where a.department_id = b.department_id

and a.salary < b.avgsal;

 

--Top-N

select rownum as rank, last_name, salary

from (select last_name, salary

    from employees

    order by salary desc)

where rownum <= 3;

 

 

select rowid id, employee_id

from employees;