210917(금) DB 9일차 - 인라인 / 시노님

<인라인>

 

ex5) 뷰 - 인라인 : (select 안의 select) 서브쿼리의 일종이지만 인라인이라 부른다!

서브쿼리: where절 안에 들어간다 인라인: from절 안에 들어간다 -> 인라인은 테이블 역할을 한다는 것!

사원테이블을 가지고 부서별 평균급여를 뷰(v_view7)로 작성하시오

조건1) 반올림해서 100단위까지 구하시오

조건2) 타이틀은 부서ID, 부서평균

조건3) 부서별로 오름차순 정렬 하시오

조건4) 부서ID가 없는 경우 5000으로 표시하시오

 

create or replace view v_view7("부서ID", "부서평균")

as select nvl(department_id, 5000),

round( avg(salary), -3)

from employees

group by department_id

 order by department_id asc;

select * from v_view7;

----------------------------------------

select 부서ID, 부서평균

from (select nvl(department_id, 5000) "부서ID",

               round( avg(salary), -3) "부서평균"

               from employees

               group by department_id

               order by department_id asc);

 

 

 

[문제5]

5-1. 부서별 최대급여를 받는 사원의 부서명, 최대급여를 출력하시오

 

문 5-1 답: 

select 부서명, 최대급여
from(select department_name 부서명, --공백없어서 ""안써도 된다.
               max(salary) 최대급여
        from employees
        join departments using(department_id)
        group by department_name);

 

 

5-2. 5-1번 문제에 최대급여를 받는 사원의 이름도 구하시오

문 5-2 답: 

select 이름, 부서명, 최대급여
from(select last_name 이름,
               department_name 부서명, --공백없어서 ""안써도 된다.
               salary 최대급여
        from employees
        join departments using(department_id)
        where (department_name, salary) in (select department_name, max(salary)
                                             from employees 
                                             join departments using(department_id)
                                             group by department_name));

 

이렇게도 가능~!!

select last_name as 이름, department_name as 부서명, salary as 최대급여

from employees

join departments using(department_id) where (salary, department_id) = any (select  max(salary), department_id from employees group by department_id);

 

 

---------- db에서 꺼내오는 일이 많기 때문에 ex) 6, 7, 8 많이이용! ----------

 

ex6) Top N분석 / rownum: 행번호 만들어 준다 디벨로퍼행번호 말고 DB에 저장되는 행번호~!!!

급여를 가장 많이 받는 사원3명의 이름, 급여를 표시 하시오

select rownum, last_name, salary

from (select last_name, nvl(salary,0) as salary from employees order by 2 desc) => 인라인이 테이블 역할! 

where rownum<=3;

 

 

ex7) 최고급여를 받는 사원1명을 구하시오

select rownum, last_name, salary

from (select last_name, nvl(salary,0)as salary from employees order by 2 desc)

where rownum=1; ← rownum=2는 error (특정 행은 사용할 수 없음) => 처음부터 시작한 경우말고는 불가..........

 

 

ex8) 급여의 순위를 내림차순 정렬 했을 때, 3개씩 묶어서 2번째 그룹을 출력하시오

(4,5,6 순위의 사원 출력 : 페이징 처리 기법)

 

방법1

select * from

(select rownum , ceil(rownum/3) as page, tt.* from                          cf) ceill : 소수점이하 올림/ tt.*: tt 몽땅 가져와~!

(select last_name, nvl(salary,0) as salary from employees order by salary desc) tt   => 그냥 tt라고 이름 지어준 것

)  => 이 인라인이 테이블 역할!!!

 

where page=2;

 

 

방법2

select * from

(select rownum rn, tt.* from

(select last_name, nvl(salary,0) as salary from employees order by 2 desc)tt

) where rn>=4 and rn<=6;

 

 

 

[문제6] 사원들의 연봉을 구한 후 최하위 연봉자 5명을 추출하시오

조건1) 연봉 = 급여*12+(급여*12*커미션)

조건2) 타이틀은 사원이름, 부서명, 연봉

조건3) 연봉은 ￦25,000 형식으로 하시오

 

문 6 답:

SELECT LAST_NAME AS 사원이름,
       DEPARTMENT_NAME AS 부서명,
       TO_CHAR(SALARY, 'L99,999') AS 급여
FROM(SELECT ROWNUM RN, AA.*
FROM(SELECT LAST_NAME,
            DEPARTMENT_NAME,
            SALARY*12+(SALARY*12* NVL(COMMISSION_PCT,0)) AS SALARY
     FROM EMPLOYEES
     JOIN DEPARTMENTS USING(DEPARTMENT_ID)
     ORDER BY 3) AA
) WHERE RN>=1 AND RN<=5;

---

<SYNONYM> 시노님 - 테이블 등에 별명 짓는 것~!!!

- Synonym은 오라클 객체(테이블, 뷰, 시퀀스, 프로시저)에 대한 대체이름(Alias)를 말한다

- Synonym은 Object가 아니라 Object에 대한 직접적인 참조이다

- 데이터베이스의 투명성을 제공하기 위해서 사용 한다

다른 유저의 객체를 참조할 때 많이 사용 한다

- 객체의 긴 이름을 짧게 만들어 SQL 코딩을 단순화 할 수 있다

- 객체의 실제 이름, 소유자, 위치를 감추기 때문에 데이터베이스의 보안을 유지할 수 있다

=> SQL문 길이도 줄이고 보안유지도 되기 때문에 사용 한다

 

* 종류

Private Synonym

전용 Synonym은 특정 사용자만 사용할 수 있다

 

Public Synonym

공용 Synonym은 사용자 그룹이 소유하면 그 데이터베이스에 있는 모든 사용자가 공유한다

 

 

[형식]

CREATE [PUBLIC] SYNONYM 시노님이름 FOR 객체이름

 

 

[실습]

1. HR 계정으로 접속해서 C##JAVA 계정에게 EMPLOYEES 테이블을 조작할 수 있는 권한 부여

cf) 계정 만들기 배울 때

grant create session, create table, create sequence, create view to 계정; 이런식으로 권한을 줘야 할 수 있는 것이었다

이렇게 권한을 준다!

 

HR 계정에서 실습

grant all on employees to c##java; => employees에 접근할 수 있는 권한을 준다!

 

2. C##JAVA 계정에 접속해서 Synonym(동의어)를 생성

hr계정의 employees 테이블을 java계정에서 hr_emp 동의어로 사용한다

 

CREATE SYNONYM Synonym이름 FOR 다른 계정의 테이블명

 

C##JAVA 계정에서 실습

create synonym hr_emp for hr.employees;

ORA-01031: 권한이 불충분합니다

01031. 00000 - "insufficient privileges"

*Cause: An attempt was made to perform a database operation without

the necessary privileges.

*Action: Ask your database administrator or designated security

administrator to grant you the necessary privileges

 

★ 먼저 SYNONYM를 생성할 수 있는 권한이 있어야 한다

SYSTEM 계정(관리자 계정)에서 권한을 부여한다

 

1) SYSTEM 계정에서 실습

grant create synonym to c##java;  => 시노님을 만들 수 있는 권한을 c##java에게 준다~!!!

 

2) 다시 C##JAVA 계정에서

create synonym hr_emp for hr.employees;

select * from user_synonyms;

 

3. 쿼리

select * from hr.employees;

이런 식으로 사용하면 SQL문이 길어질 때 테이블명이 길어서 문제가 되고 다른 스키마(계정)에 있는 객체의 위치를 알려주게 되어 보안상 안 좋다

 

select * from hr_emp; - Synonym 이용

Synonym 이름을 짧게 하여 SQL문 길이도 줄이고 보안유지도 되기 때문에 사용 한다

 

4. 삭제

DROP SYNONYM 시노님명

drop synonym hr_emp;

 

select * from user_synonyms;

Synonym 동의어가 삭제된 것을 확인할 수 있다