[OracleSQL] 명령어 개인 레퍼런스

1. basic

* 오라클 접속 방법
- sqlplus
- sqlplus 사용자명/비번

* 오라클 명령어
-[1] 최고 관리자(system/sys)로 접속하여 사용자의 계정을 풀어줌.
    : alter user 사용자명 account unlock;

-[2] 현재 접속하고 있는 계정 확인
    : show user;

-[3] 접속 계정 변경
    : conn 사용자명/비번

-[4] 사용자 암호 변경 (최고 관리자에서만)
    : alter user 사용자명 identified by 변경할 비번;

-[5] 계정과 비밀번호를 한번에 변경
    : alter user 사용자명 identified by 변경할 비번 account unlock;

* 오라클 명령어 (select문)
-[1] scott 사용자가 관리하는 테이블 목록
    : select * from tab;

-[2] 특정 테이블 구조 확인
    : desc 테이블명;

-[3] 특정 테이블의 데이터 표시
    : select * from 테이블명

-[4] 모든 컬럼(필드명)이 아닌, 필요한 컬럼(필드명) 내용만 출력
    : select 필드명(여러개) from 테이블명

-[5] 각각 필드명에 별칭을 주어서 출력 (출력하는 순간만)
    : select 필드명 (as) "별명" from 테이블명;

-[6] 중복된 데이터 제거하고 출력
    : select distinct 필드명 from 테이블명
       ex) 사원들의 직원명(job)을 중복을 제거한 후 출력.
           select distinct job from emp;

-[7] 급여가 3000이상인 사원 정보 출력
    : select empno, ename, sal from emp where sal >= 3000;

-[8] 이름이 scott인 사원의 정보 출력
    : select empon, ename, sal from emp where ename = 'SCOTT';
     ****데이터는 대소문자 구별함

-[9] 부서번호가 10이고 직업이 'MANAGER'인 사원 출력
    : select deptno, ename, job from emp
      where deptno = 10 and job = 'MANAGER';

-[10] 부서번호가 10이거나 직업이 'MANAGER'인 사원 출력
    : select deptno, ename, job from emp
      where deptno = 10 or job = 'MANAGER';

-[11] 1985년도 이후에 입사한 사원 정보
    : select empno, ename, hiredate from emp
      where hiredate >= '1985/01/01';

-[12] 부서번호가 10이 아닌 사원
    : select deptno, ename from emp where not(deptno = 10);
      select deptno, ename from emp where deptno <> 10;

-[13] 급여가 1000~3000 사이인 사원을 출력
    : select ename, sal from emp where sal >= 1000 and sal <= 3000;
      select ename, sal from emp where sal between 1000 and 3000;

-[14] 급여가 1300 또는 1500 또는 1600인 사원 정보 출력
    : select ename, sal from emp where sal = 1300 or sal = 1500 or sal = 1600;
      select ename, sal from emp where sal in (1300, 1500, 1600);

-[15]  이름이 'K'로 시작하는 사원 출력
    : select empno, ename from emp where ename like 'K%';

-[16]  이름이 'K'로 끝나는 사원 출력
    : select empno, ename from emp where ename like '%K';

-[17]  이름에 'K'가 포함되는 사원 출력
    : select empno, ename from emp where ename like '%K%';

-[18]  이름 2번째에 'A'가 들어가는 사원 출력
    : select empno, ename from emp where ename like '_A%';

-[19] 커미션을 받지 않는 사원
    : select empno, ename, comm from emp where comm is null;

-[20] 커미션을 받는 사원
    : select empno, ename, comm from emp where comm is not null;

-[21] 사번의 정렬(오름차순)으로 출력
    : select empno, ename from emp order by empno (asc);

-[22] 사번의 정렬(내림차순)으로 출력
    : select empno, ename from emp order by empno desc;

-[23] 사원의 연봉 계산 출력
    : select ename, sal, sal*12 from emp;
      select ename, sal, sal*12 (as) "연봉" from emp;

-[24] 커미션을 포함한 최종 연봉 출력 (*오류남)
    : select ename, sal, comm, sal*12+comm "연봉" from emp;

-[25] [24]의 오류 해결법 (한번도 입력하지않은 항목을 0으로 채우라는 함수 이용)
    : select ename, sal, comm, nvl(comm, 0), sal*12+nvl(comm, 0) "연봉" from emp;





2. 그룹함수

*오라클 명령어: Group 함수

-[1] 합계 > sum()
      : select sum(sal) from emp;

-[2] 카운트 > count()
      : select count(*) from emp;

-[3] 평균 > avg()
      : select avg(sal) from emp;

-[4] 최대값 > max()
      : select max(sal) from emp;

-[4] 최대값 > min()
      : select min(sal) from emp;

-[5] Group by 절 > 직업별 급여 평균
      : select job, avg(sal) from emp; (X) -> 단일 필드와 연관해서 출력하는건 안됨!
      : select job, avg(sal) from emp group by job;
     



3. 내장함수

*오라클 명령어: 내장 함수

-[1] 샘플 테이블인 dual 테이블 (가상테이블)
: select * from dual;

-[2] 임시 데이터 출력
    : select 1234*1234 from dual;

-[3] 반올림 > round()  (양수는 소숫점 이하자리로, 음수는 정수자리로)
    : select deptno, round(Sal, -3) from emp where deptno = 30;
      select round(12.3456, 3) from dual; -> 12.346

-[4] 모든 문자를 소문자로 변환 > lower()
    : select lower('Hong Kil Dong') "소문자" from dual;

-[5] 모든 문자를 대문자로 변환 > upper()
    : select upper('Hong Kil Dong') "대문자" from dual;

-[6] 첫글자만 대문자로 변환 > initcap()
    : select initcap('hong kil dong') "첫글자만 대문자" from dual;

-[7] 문자열 연결 > concat()
    : select concat('Hel', 'lo') from dual;

-[8] 문자열 길이 > length(), lengthb()  
    ( length는 글자수를 , lengthb는 byte값 반환 -> 한글 2byte, 영어 1byte 유니코드냐 아스키코드냐에따라 다름)
    : select length('에이콘'), lengthb('에이콘'), length('Acorn'), lengthb('Acorn') from dual;

-[9] 문자열 추출 > substr(), substrb()
    :  select substr('문자열', 시작위치, 끝위치) from 테이블명;
    :  select substr('홍길동 만세', 3, 5) from dual;
    :  select substrb('홍길동 만세', 3, 5) from dual;

-[10] 특장 문자열의 시작 위치 > instr()
    : select instr('홍길동 만세', '세') from dual;

-[11] 자리 채우기 > lpad(), rpad()
    : select lpad('문자열', 자리갯수, 빈공간에채워질문자 ) from dual;
    : select lpad('Oracle', 20, '#') from dual;   -> #########Oracle
    : select rpad('Oracle', 20, '#') from dual;   -> Oracle#########

-[12] 컬럼이나 대상 문자열에서 특정 문자가 첫번째 글자이거나 마지막 글자이면
        잘라내고 남은 문자열만 반환 (공란을 없애려고할 때 많이 쓰임) > trim()
    : select trim('a' from 'aaaOracleaaaaaa') from dual; -> Oracle
    : select trim(' ' from '      Oracle         ') from dual; -> Oracle

-[13] 절대값 > abs()
    : select abs(-10) from dual;

-[14] 소수자리 버리기 > florr()
    : select floor(12.3456) from dual;

-[15] 특정 자리 자르기 > trunc()
    : select trunc(12.3456, 3) from dual;

-[16] 나머지 > mod()
    : select mod(3, 5) from dual;

-[17] 날짜 > sysdate()
    : select sysdate from dual;

-[18] 개월 수 구하기 > months_between()  (현재날짜까지 개월 수 )
    : select ename, months_between(sysdate, hiredate) from emp where deptno = 10;

-[19] 개월 수 더하기 > add_months()
    : select add_months(sysdate, 4) from dual; -> 현재날짜에 4개월 더함

-[20] 다가올 요일에 해당하는 날짜 > next_day()
    : select next_day(sysdate, '일요일') from dual; -> 이번주 일요일 날짜 알려줌

-[21] 해당 달의 마지막 일 수 > last_day()
    : select last_day(sysdate) from dual;

-[22] 문자열로 변환 > to_char()
    : select to_char(sysdate, 'yyyy-mm-dd') from dual;

-[23] 날짜형으로 변환 > to_date()
    : select to_date('2009/12/31', 'yyyy/mm/dd') from dual;

-[24] NULL인 데이터를 다른 데이터로 변경 > nvl()
    : select ename, nvl(comm, 0) from emp; -> 출력하는 순간에만 0으로 바꿔줌

-[25] switch문과 같은 기능 > decode()
    : select ename, deptno, decode(deptno, 10, 'ACCOUNT',
                                                             20, 'RESEARCH',
                                                             30, 'SALES',
                                                             40, 'OPERATIONS') as "부서번호"
       from emp;

-[26] if elseif else 문과 같은 기능 > case()
    : select ename, deptno,
      case when deptno = 10 then 'ACCOUNT'
             when deptno = 20 then 'RECEARCH'
              when deptno = 30 then 'SALES'
               when deptno = 40 then 'OPERATIONS'
      end as "부서명" from emp;



4. 테이블 생성/ 수정/ 삭제

*오라클 SQL문 : 테이블 생성/수정/삭제

-[1] 테이블 생성 : create 문
    > create table exam01
       (
             exno      number(2),
             exname  varchar2(20),
             exsal      number(7, 2)    
       );
     
      -> char는 바이트할당 그대로 varchar2는 가변적으로 데이터할당
      -> number(할당메모리 ,실수를 표현할때 소숫점 이하 둘째자리까지)

-[2] 기존 테이블과 동일하게 테이블 만들기
    > create table exam02
       as
       select * from emp;

-[3] 기존 테이블에서 새로운 컬럼 추가 : alter문(필드 추가)
    > alter table exam01
       add
       (
             exjob varchar2(10)
        );
    
-[4]  테이블 구조 수정 : 필드 수정
    > alter table exam01
       modify
       (
           exjob varchar2(20)
        );

-[5]  테이블 구조 수정 : 필드 삭제
    > alter table exam01
       drop column exjob;
      
-[6] 테이블 삭제
    > drop table exam02;     

      -> 10 이후부터 오라클에서 완전삭제하는 것이 아니라 임시테이블 형태로 변경되어진다.
      -> 다시 사용하고싶을때 쓸 수 있게 하려고
      -> 10버전부터는 임시테이블도 삭제시켜주어야함

   --- 임시 테이블을 삭제하고 싶다면?
    > purge recyclebin;
 
   --- 처음부터 테이블을 완전 삭제하고 싶다면?
    > drop table exam02 purge;

-[7] 테이블 이름 변경
    > alter table exam01 rename to exam02;
    > rename exam01 to exam02;

-[8] 테이블 내의 모든 데이터(레코드) 삭제
    > truncate table exam01;





5. 자료형 명명규칙

*오라클 자료형 타입

1. 정수형 타입 : number(2)는 총 자리수가 2자리인 정수형 값이 필드에 저장된다.
2. 실수형 타입 : number(6, 2)는 소수점을 포함한 총자리수가 6자리이고,  소수점 둘째자리까지 있는 실수형 갑싱 저장된다.
                      첫번째 인자값 6은 소수점을 포함한 총 자리수를 의미하고, 두번째 인자값 2는 소수점 자리수를 의미한다.
 
3. 가변형 문자열 : varchar2()
                          입력 데이터가 실제 크기를 넘어서면 넘어선 크기만큼 자료형 크기가 늘어나지 않는다.
                          만약 varchar2(10)으로 만들어진 필드에는 10자리까지만 저장된다. 영문자로 5자의 레코드를 삽입한다면,
                          varchar2는 크기가 실제 레코드 크기가 5인만큼 줄어들어 저장된다. 그러므로 기억장소 절약의 장점이 있다.

4. 고정형 문자열 : char()
          char(10)으로 만들어진 필드에 영문자 5 크기 만큼 레코드를 삽입한다면 char 자료형은 고정형이기 때문에 실제 레코드 크기만큼(10) 줄어들지 않고
          고정형으로 할당된다.

* 테이블명, 컬럼명 명명 규칙

1. 반드시 문자로 시작해야한다.
2. 1~30자까지 가능
3. A~Z까지의 대소문자와 0~9까지의 숫자 조합
   특수 기호는(_, $, #)만 포함할 수 있음
4. 오라클에서 사용되는 예약어나 다른 객체명과 중복 불가.
5. 공백 허용 안됨.

cf) 오라클의 객체 : 테이블, 뷰, 시퀀스, 인덱스





6. 데이터 입력/ 수정/ 삭제

* 오라클 SQL문 : 데이터 입력/ 출력(select문)/ 수정/ 삭제

-[1] 샘플 테이블 생성
> create table exam02
   (
     deptno number(2),
     dname varchar2(14),
     loc varchar2(14)
    );

-[2] 데이터 입력 : insert into 문
> insert into exam02(loc, deptno, dname)
  values('NEW YORK', 10, 'ACCOUNT');

-[3] 데이터 입력 : 행 생략
> insert into exam02
  values(20, 'SALES', 'CHICAGO');

-[4] null 값 입력
> insert into exam02
  values(30, 'RESEARCH', null);

-[5] 데이터 출력 : select 문

-[6] 데이터 수정 : 필드의 데이터를 변경
부서번호 변경
> update exam02
   set deptno = 30;

-[7] 급여 10% 인상
>  update exam03
    set sal = sal * 1.1;

-[8] 부서번호가 10인 사원의 부서번호를 20으로 변경
> update exam03
   set deptno = 20
   where deptno = 10;

-[9] 급여가 3000이상인 사원만 급여를 10% 인상
> update exam03
   set sal = sal*1.1
   where sal >= 3000;

-[10] 사원 이름이 SCOTT인 자료의 부서번호를 10,
        직급을 MANAGER로 변경
> update exam03
   set deptno = 10, job = 'MANAGER'
   where ename = 'SCOTT';

-[11] 30번 부서 사원을 삭제
> delete from exam03
   where deptno = 30;

-[12] 모든 자료 삭제
> delete from exam03;

--- trunc()와 차이점 ?
delete는 데이터만 삭제 trunc는 테이블을 삭제한다음 다시 테이블을 생성해주는 시퀀스.





7. 조인(Join)

* 오라클 SQL 구문 : 조인 (Join)

-[1] 원하는 정보가 두 개 이상의 테이블에 나뉘어져 있을 때 결과 출력
> select deptno from emp where ename = 'SCOTT'; -> 20
> select * from dept where deptno = 20;

-[2] 조인 : 두 개 이상의 테이블에 나뉘어져 있는 데이터를
                한번의 sql문으로 원하는 결과를 얻을 수 있는 기능.

//cross join : 2개 이상의 테이블을 단순 연결
//equi join : 동일 컬럼을 기준으로 join

>  select * from emp, dept;
>  select *
    from emp, dept
    where emp.deptno = dept.deptno;

> select *
   from emp, dept
   where emp.deptno = dept.deptno
   and ename = 'SCOTT';

> select emp.ename, dept.dname, emp,deptno
   from emp, dept
   where emp.deptno = dept.deptno
   and emp.ename = 'SCOTT';

> select e.ename, d.dname, e.deptno
   from emp e, dept d
   where e.deptno =d.deptno
   and e.ename = 'SCOTT'

-[문제] 조인을 사용하여 ACCOUTING 부서 소속 사원의 이름과 입사일을 출력
> select ename, dname, hiredate
   from emp e, dept d
   where d.dname = 'ACCOUNTING'
   and d.deptno = e.deptno; 





8. 서브쿼리

* 오라클 명령어 : 서브쿼리

- SQL : Structed Query Langage
- 서브쿼리는 하나의 select 문장의 절 안에 포함된 또 하나의 select 문이다.
- 메인쿼리 / 서브쿼리
- 서브쿼리는 비교 연산자의 오른쪽에 기술해야하고  반드시 괄호로 둘러쌓아야한ㄷㅏ.
- 서브쿼리는 메인 쿼리가 실행되기 이전에 한 번만 실행이 된다.
- 단일행 서브쿼리 / 다중행 서브쿼리

SQL > select deptno
        from emp
        where ename = 'SCOTT';
SQL > select dname
        from dept
        where deptno = 20 ;

SQL > select dname
        from dept
        where deptno = (select deptno
                             from emp
                             where ename = 'SCOTT') ;

[단일행 서브쿼리]
SQL > select deptno from emp where ename = 'SCOTT';
SQL > select dname from dept where deptno = 20 ;

SQL > select dname
        from dept
        where deptno = (select deptno
                             from emp
                             where ename = 'SCOTT') ;

[문제] SCOTT과 동일한 직급(job)을 가진 사원을 출력하는 sql문을 작성해보세용.
SQL > select ename, job
        from emp
        where job = (select job
                      from emp
                      where ename = 'SCOTT');

[문제] SCOTT의 급여와 동일하거나 더 많이 받는 사원명과 급여를 출력해보아라
SQL > select ename, sal
        from emp
        where sal >= (select sal
                    from emp
                    where ename = 'SCOTT'
                    );


[서브쿼리 & 그룹함수]
평균 급여보다 더 많은 급여를 받는 사원들을 출력해라.
SQL > select ename, sal
        from emp
        where sal > (select avg(sal)
                        from emp 
                        );

[다중행 서브쿼리]
연봉을 3000이상 받는 사원이 소속된 부서와 동일한 부서에서 근무하는 사원들의 정보를 출력하세여.
SQL > select ename, deptno
        from emp
        where sal >= 3000;

-error
SQL > select ename, sal, deptno
        from emp
        where deptno = (select ename, deptno
                              from emp
                              where sal >= 3000 );

[in 연산자] 메인쿼리의 비교 조건('=' 연산자로 비교할 경우)이 서브쿼리의 결과중에
              하나라도 일치하면 참이다.
              반환되는 결과값이 두 개 이상일 경우, (deptno = 10, 20)
SQL > select ename, sal, deptno
        from emp
        where deptno in ( select deptno
                                from emp
                                where sal >= 3000
                              );
             
[문제] in 연산자를 이용하여 부서별로 가장 급여를 많이 받는 사원의 정보(사원 번호,
        사원명, 급여, 부서번호)를 출력하세요.
SQL > select empno, ename, sal, deptno
         from emp
         where sal in ( select max(sal)
                           from emp
                   group by deptno   
                         );

[all 연산자]
: 메인쿼리의 비교 조건이 서브쿼리의 검색 결과와 모든 값이 일치하면 참이다.
: 찾아진 값에 대해서 and 연산을 해서 모두 참이면 참이되는 셈이된다.
: > all은 '모든 비교값보다 크냐'고 묻는 것이 되므로 최대값보다 더 크면 참이 된다.

-30번 소속 사원들 중에서 급여를 가장 많이 받는 사원보다 더 많은 급여를 받는 사람의
이름과 급여를 출력해.
-단일행 서브쿼리 & 그룹함수 이용
SQL > select ename, sal
        from emp
        where sal > (select max(sal)
                       from emp
                       where deptno = 30
                       ) ;

/error
SQL > select ename, sal
        from emp
        where sal > (select sal
                        from emp
                        where deptno = 30
                        );

SQL > select ename, sal
        from emp
        where sal > all ( select sal
                              from emp
                              where deptno = 30
                             ) ;

[문제] 영업사원(saleman)들보다 급여를 많이 받는 사원들의 이름과 급여를 출력하되
        영업사원은 출력되지 않게 명령문을 작성해 보세요.
>>영업사원중에 가장 많이 받는 사람?보다 더 많이 받는
SQL > select ename, sal
        from emp
        where sal > all ( select sal
                             from emp
                             where job = 'SALESMAN'
                             );

[any 연산자]
: any 조건은 메인 쿼리의 비교 조건이 서브쿼리의 검색 결과와 하나 이상만 일치하면 참이다.
: > any 는 찾아진 값에 대해서 하나라도 크면 참이 된다.
: 그러므로 찾아진 값 중에서 가장 작은 값. 즉, 최소값 보다 크면 참이된다.


- 부서 번호가 30번인 사원들의 급여 중 가장 낮은 값(950)보다 높은 급여를 받는 사원의
  이름, 급여를 출력하는 명령문을 작성해봐yo
[단일행 서브쿼리 & 그룹함수]
SQL > select ename, sal
        from emp
        where sal > ( select min(sal)
                         from emp
                         where deptno = 30
                         ) ;
[다중행 서브쿼리]
SQL > select ename, sal
        from emp
        where sal > any(
                             select sal
                             from emp
                             where deptno = 30
                             );

[문제] 영업 사원들의 최소 급여보다 많이 받는 사원들의 이름, 급여, 직급을 출력하되
        영업 사원은 출력하지 않습니다.
SQL > select ename, sal, job
        from emp
        where sal > any (
                              select sal
                              from emp
                              where job = 'SALESMAN'
                              )
         and job <> 'SALESMAN' ;





9. 무결성 제약조건

* 오라클 - 테이블 무결성 제약조건

* 데이터 무결성 제약 조건 (Data Interity Constraint Rule)
  : 테이블에 부적절한 자료(데이터)가 입력되는 것을 방지하기 위해서 테이블을 생성할 때
   각 컬럼에 대해서 정의하는 여러가지 규칙을 의미.

- Not null/null : null을 허용할 지 아니면 반드시 입력받게 할 것인지의 조건.
- Unuique : 지정된 컬럼에 중복되지 않고 유일한 값만 저장되는 조건.
- Primary key(기본키) : unique + not null
- Check : 특정한 값만 저장되는 필드 조건.
- Default : 기본값으로 특정 값이 저장되도록 설정하는 조건.
- Foreign key(외래키) : 다른 테이블의 컬럼에 들어있는 값만 저장을 허용.

* USER_CONSTRATINT 데이터 딕셔너리 뷰 : 제약 조건에 관한 정보를 알려 줌.

SQL > desc user_constraints ;
        - owner : 제약 조건을 소유한 사용자명을 저장하는 칼럼.
        - constraint_name : 제약 조건명을 저장하는 컬럼.
        - constraint_type : 제약 조건의 유형을 저장하는 컬럼.
           . P : Primary key
           . R : Foriegn key
           . U : Unique
           . C : Check, Not null
        - table_name : 각 제약 조건들이 속한 테이블의 이름.
        - search_condition : 어떤 내용이 조건으로 사용되었는지 설명.
        - r_constraint_name : 제약 조건이 foreign key인 경우 어떤 primary key를
                                   참조했는지에 대한 정보를 갖음.

> column constraint_name format a15
> select constraint_name, constraint_type, table_name
   from user_constraints;

* user_cons_columns 데이터 딕셔너리 뷰 : 어떤 컬럼에 어떤 제약 조건이 지정되었는지 알려주는
                                                          데이터 딕셔너리.
> column owner format a10   (컬럼 출력을 10칸으로 설정)
> column constraint_name format a15
> column table_name format a15
> column column_name format a15
> select * from user_cons_columns;



== 컬럼 레벨 제약 조건 지정==

--[1] NOT NULL 제약 조건을 설정하지 않고 테이블 생성
> drop table emp01;
> create table emp01
   (
       empno number(4),
       ename varchar2(10),
       job varchar2(10),
       deptno number(2)
   );

> insert into emp01
   values (null, null, 'SALEMAN', 40);

--[2] not null 제약 조건을 걸고 테이블 생성
> drop table emp02;
> create table emp02
   (
       empno number(4) not null,
       ename varchar2(10) not null,
       job varchar2(10),
       deptno number(2)
   );
> insert into emp02
   values (null, null, 'SALEMAN', 40);
  #[err] not null조건으로 인해 null 값은 삽입할 수 없음!!

> insert into emp02
   values (null, 'SCOTT', 'SALEMAN', 40); 
  #[err] 하나라도 null이 있으면 안됨!

> insert into emp02
   values (7499, 'ALLEN', 'SALEMAN', 40); 

--[3] Unique 제약 조건을 설정하여 테이블 생성
> drop table emp03;
> create table emp03
   (
       empno number(4) unique,
       ename varchar2(10) not null,
       job varchar2(10),
       deptno number(2)
   );

-> unique 속성은 desc 명령어로 안보인다.

> insert into emp03
   values (7499, 'ALLEN', 'SALEMAN', 40);
> insert into emp03
   values (7499, 'JONES', 'MANAGER', 20); 
#[err]무결성 제약조건(SCOTT.SYS_COO11056에 위배됩니다.  데이터 중복 X

> insert into emp03
   values (7566, 'JONES', 'MANAGER', 20);  #잘 넣어짐
> insert into emp03
   values (null, 'SMITH', 'CLERK', 30);   #근데 NULL값이 들어가네?

--[4] not null & unique 제약 조건
   - 사용자가 제약 조건명을 지정하지 않고 제약 조건만을 명시할 경우
      오라클 서버가 자동으로 제약 조건명을 부여한다.
   - 오라클이 부여하는 제약 조건명은 SYS_다음에 숫자를 나열한다.
   - 어떤 제약 조건을 위배했는지 알 수 없기 때문에
     사용자가 의미있게 제약 조건명을 명시할 수 있도록 오라클은 제공,
  
> drop table emp04;
> create table emp04
   (
       empno number(4) constraint emp04_empno_uk unique, 
       ename varchar2(10) constraint emp04_ename_nn not null,
       job varchar2(10),
       deptno number(2)
   );
# 에러메시지 날때 제약 조건 앞에 지어진 이름이 뜬다.

> insert into emp04
   values (7499, 'ALLEN', 'SALEMAN', 40);
> insert into emp04
   values (7499, 'JONES', 'MANAGER', 20);
   #무결성 제약 조건(SCOTT.EMP04_EMPNO_UK)에 위배됩니다.

> select table_name, constraint_name
   from user_constraints
   where table_name in ('EMP04');
#특정 테이블의 제약조건들 확인할 수 있다. 테이블이름은 반드시 대문자로!

--[5] Primary key(기본키) 제약 조건 설정하기
    - unique + not null
    - 반드시 입력하되 데이터 중복 X

> drop table emp05;
> create table emp05
   (
       empno number(4) constraint emp05_empno_pk primary key, 
       ename varchar2(10) constraint emp05_ename_nn not null,
       job varchar2(10),
       deptno number(2)
   );
> insert into emp05
   values (7499, 'ALLEN', 'SALEMAN', 40);
> insert into emp05
   values (7499, 'JONES', 'MANAGER', 20);  #무결성 제약조건 (SCOTT. EMP05_EMPNO_PK)에 위배됩니다.
> insert into emp05
   values (null, 'SMITH', 'CLERK', 30);  #null값 넣을 수 없다!

--[6] 참조 무결성을 위한 Foreign key(외래키) 제약 조건
   : 부모 키가 되기 위한 컬럼은 반드시 부모 테이블의 기본키(primary key)이거나,
     유일키(unique)로 설정되어 있어야한다.

> drop table dept01;
> create table dept01
   as
   select * from dept;
#복사를 해올때 데이터만 해오고 제약조건은 복사하지않는다!

> create table dept01 (
     deptno  number(2) constraint dept01_deptno_pk primary key,
     deptname varchar2(14),
     loc varchar2(13)         
    );
> insert into dept01
   values (10, 'ACCOUTING', 'NEW YORK');
> insert into dept01
   values (20, 'RESEARCH', 'DALLAS');
> insert into dept01
   values (30, 'SALES', 'CHICAGO');

> create table emp06  (
       empno number(4) constraint emp06_empno_pk primary key, 
       ename varchar2(10) constraint emp06_ename_nn not null,
       job varchar2(10),
       deptno number(2) constraint emp06_deptno_fk references dept01(deptno)
   );
  #자식테이블에서 외래키 제약조건을 걸어줘야한다.

> select table_name, constraint_name
   from user_constraints
   where table_name in ('EMP06');

> insert into emp06
   values (7499, 'ALLEN', 'SALEMAN', 30);
> insert into emp06
   values (7566, 'JONES', 'MANAGER', 50); #[err] 부모키가 없습니다.

--[7] Check 제약 조건 설정하기
       : 급여 컬럼을 생성하되 값은 500~5000 사이의 값만 저장 가능
       : 성별 저장 컬럼으로 gender를 정의하고 'M', 'F' 둘 중 하나만 저장 가능

> drop table emp07;
> create table emp07(
     empno number(4) constraint emp07_empno_pk primary key,
             ename varchar2(20) constraint emp07_ename_nn not null,
             sal number(7, 2) constraint emp07_sal_ck check(sal between 500 and 5000),
     gender varchar2(1) constraint emp07_gender_ck check(gender in ('M', 'F'))
    );
> insert into emp07
   values (7499, 'ALLEN', 200, 'M'); #[err] 체크조건에 위배!
> insert into emp07
   values (7499, 'ALLEN', 700, 'M');
> insert into emp07
   values (7499, 'JONES', 900, 'A'); #[err]

--[8] Default 제약 조건 설정하기
      : 지역(loc) 컬럼에 아무 값도 입력하지 않았을 때 default 값인 'SEOUL'이 입력 되도록 설정

> drop table dept02;
> create table dept02 (
        deptno number(2) primary key,
     dname varchar2(14),
             loc varchar2(12) default 'SEOUL'
    );
> insert into dept02(deptno, dname)
   values (10, 'ACCOUNTING');        #자동으로 SEOUL이 들어감
> insert into dept02
   values (20, 'RESEARCH', 'NEW YORK');
> insert into dept02
   values (30, 'SALES');      #[err] 값의 수가 충분하지 않습니다.

--[9]

--[10] 컬럼 레벨로 제약 조건명 명시해서 제약 조건 설정하기
> create table emp10  (
       empno number(4) constraint emp10_empno_pk primary key, 
       ename varchar2(10) constraint emp10_ename_nn not null,
       job varchar2(10),
       deptno number(2) constraint emp10_deptno_fk references dept01(deptno)
   );

--[11] 테이블 레벨 방식으로 제약조건 설정하기
        ***주의 : not null 제약 조건은 테이블 레벨 방식으로 제약 조건을 지정할 수 없다.
> create table emp11  (
       empno number(4) , 
       ename varchar2(10) constraint emp11_ename_nn not null,
       job varchar2(10),
       deptno number(2),   
       constraint emp11_empno_pk primary key(empno),
       unique(job),
       foreign key(deptno) references dept01(deptno) 
   );

--[12] 제약 조건 추가하기
> delete from emp01;

> alter table emp01
   add constraint emp01_empno_pk primary key(empno);
> alter table emp01
   add constraint emp01_deptno_fk foreign key(deptno) references dept01(deptno);

> select table_name, constraint_name, constraint_type
   from user_constraints
   where table_name in ('EMP01');
> insert into emp01
  values( null, null, 'SAM', 40);  #[err]제약조건으로 삽입 X

--[13] not null 제약 조건 추가 하기
> alter table emp01
   modify ename constraints emp01_ename_nn not null;

--[14] 제약 조건 제거하기
> alter table emp01
   drop primary key;

> alter table emp01
   drop emp01_ename_nn;

--[15] 제약 조건(외래키) 삭제
> delete from dept01
   where deptno = 30;   #[err] 자식레코드 발견!

   1) 제약 조건의 비활성화  
      - 자식 테이블인 사원테이블(emp06)은 부모테이블(dept01)에 기본키인 부서번호를 참조하고 있다.
      - 부서 테이블의 30번 부서는 사원 테이블에 근무하는 30번 사원이 존재하기 때문에 삭제할 수 없다.
      - 부모테이블의 부서번호 30번이 삭제되면 자식테이블에서 자신이 참조하는 부모를 잃어버리게 되므로 삭제할 수 없는 것이다.
      - 그렇다면 어떻게 삭제?
          <1> 부서 테이블의 30번 부서에서 근무하는 사원을 삭제한 후 부서 테이블에서 30번 부서를 삭제
          <2> 참조 무결성 때문에 삭제가 불가능하므로 emp06 테이블의 외래키 제약 조건을 제거한 후 30번 부서를 삭제.
      - 제약 조건 비활성화
          <1> 테이블에서 제약 조건을 삭제하지 않고 일시적으로 적용시키지 않도록 하는 방법으로 제약 조건을 비활성화 하는 방법
          <2> .DISABLE_CONSTRAINT : 제약조건 일시 비활성화
                 .ENABLE_CONSTRAINT : 비활성화된 제약 조건을 해제하여 다시 활성화.

    > alter table emp06
          disable constraint emp06_deptno_fk;
     > delete from dept01
        where deptno = 30;
     > alter table emp06
        enable constraint emp06_deptno_fk;  #[err] 값이 이미 지워져서 에러

     > insert into dept01
        values(30, 'SALES', 'CHICAGO');
     > alter table emp06
       enable constraint emp06_deptno_fk;  #다시 데이터 생성하고 하면 잘 수행됨

   2) cascade 옵션
     - 부모테이블(dept01)과 자식테이블(emp06)간의 참조 설정(외래키)이 되어 있을 때 부모테이블의 제약 조건을 비활성화하면
       이를 참조하고 있는 자식테이블의 제약 조건까지 비활성화 시켜주는 옵션.
     
      > alter table dept01
         disable primary key cascade;
      > alter table dept01
         drop primary key;
      > alter table dept01
         drop primary key cascade;
         





10. 시퀀스

*오라클 - 시퀀스(Sequence)
  : 테이블 생성 후 시퀀스(일련번호)를 따로 만들어야 한다.

--[1] 샘플테이블 생성
> create table memos
  (
     num number(4) primary key,
     name varchar2(20) not null,
     postDate Date default (sysdate)
  );

--[2] 해당 테이블의 시퀀스 설정
> create sequence memos_seq
   start with 1 increment by 1;   #start with 시작값 increment by 증가값;

--[3] 데이터 입력 : 일련번호 포함
> insert into memos (num, name)
   values(memos_seq.nextval, '홍길동');
> insert into memos (num, name)
   values(memos_seq.nextval, '홍길서');
> insert into memos (num, name)
   values(memos_seq.nextval, '홍길남');
> insert into memos (num, name)
   values(memos_seq.nextval, '홍길북');

--[4] 현재 시퀀스가 어디까지 증가되어져 있는지 확인
> select memos_seq.currval from dual;

--[5] 시퀀스 수정 : 최대 증가값을 6까지로 제한
> alter sequence memos_seq maxvalue 6;

> insert into memos (num, name)
   values(memos_seq.nextval, '홍남이');   #시퀀스 6
> insert into memos (num, name)
   values(memos_seq.nextval, '홍만이');   #[err] 시퀀스가 7이 되므로 에러!

--[6] 시퀀스 삭제
> drop sequence memos_seq;      #더이상 이 시퀀스를 사용할 수 없다


cf) 권한 부여 (시퀀스를 사용하려면 권한이 필요하다 / scott은 이미 있음)
   - system으로 접속 후
     > grant create sequence to 사용자명;






11. 트랜잭션

*오라클 - 트랜잭션(transaction)
  - commit : 명령어 완전 실행
  - rollback : 명령어 되돌리기

--[1] 테이블 생성
> create table trans(
     num number(2) primary key,
     name varchar2(20) not null,
     email varchar2(50),
     title varchar2(150),
     postDate Date default sysdate,
     postIP varchar2(20)
);

--[2] 시퀀스(일련번호) 생성
> create sequence trans_seq
   start with 1 increment by 1;
  
> insert into trans
   values (trans_seq.nextval, '홍길동', 'a@a.com', '안녕하세요, 홍길동입니다' , sysdate, '127.0.0.1');
  
   # 여기까지하고 다른 cmd창에서 select * from trans; 해보면 선택된 레코드가 없다고 한다!
      이때 원래 창에서 commit 을 한번 해주면 뜬다~
      
> insert into trans
   values (trans_seq.nextval, '홍길서', 'b@a.com', '안녕하세요, 홍길서입니다' , sysdate, '127.0.0.1');
      
  # 여기까지 하고 원래창에서 rollback 하고 다른 창에서 보면 두번째 데이터는 없다.
  # 자동커밋을 내부적으로 지원하고 있다.
       마지막 commit 부터 다음 commit사이까지가 rollback으로 돌아갈 수 있는 시점 





12. 뷰(view)

*오라클 - 뷰(View)
- 물리적인 테이블에 근거한 논리적인 가상 테이블

- 가상이란 단어는 실질적으로 데이터를 저장하고 있지 않기 때문에 붙인 것이고, 테이블이란 단어는 실질적으로 데이터를 저장하고 있진 않지만 사용자는 마치
     테이블을 사용하는 것과 동일하게 뷰를 사용할 수 있기 때문에 붙인 것.

- 뷰는 기본 테이블에서 파생된 객체로서 기본 테이블에 대한 하나의 쿼리문임.

- 실제 테이블에 저장된 데이터를 뷰를 통해서 볼 수 있다.

- 사용자에게 주어진 뷰를 통해서 기본 테이블을 제한적으로 사용하게 됨

- 뷰는 이미 존재하고 있는 테이블에 제한적으로 접근하도록 한다.

- 뷰를 생성하기 위해서는 실질적으로 데이터를 저장하고 있는 물리적인 테이블이 존재해야 하는데 이 테이블을 기본 테이블이다.


--[1] 뷰의 기본 테이블 생성
    : dept 테이블의 복사본을 dept_copy 로 생성
> create table dept_copy
    as
     select * from dept;     #단 제약조건은 복사되지 않음!
    
     : emp 테이블의 복사본을 emp_copy로 생성
> create table emp_copy
   as
   select * from emp;

--[2] 뷰 정의하기
    : 뷰를 생성하기 위해서는 create view로 시작함. as 다음은 마치 서브쿼리문과 유사함.
    : 서브쿼리에는 우리가 지금까지 사용했던 select문을 기술하면 됨

// 만일 30번 부서에 소속된 사원들의 사번과 이름과 부서번호를 자주 검색한다고 한다면
> select empno, ename, deptno
    from emp_copy
    where deptno = 30;
    
> create view emp_view30
   as
   select empno, ename, deptno
   from emp_copy
   where deptno = 30;
  
   # view를 생성하려면 권한이 필요하다! (system으로 접속해서 권한 얻어온다.)
     > grant create view to 사용자명;
    
> select * from emp_view30;  

   # 마치 테이블처럼 사용할 수 있다!
      긴 쿼리문을 간단하게 사용할 수 있다!
      
--[3] 뷰의 내부구조와 user_views 데이터 딕셔너리
> desc user_views;
> select view_name, text
   from user_views;
  
--[4] 뷰의 동작 원리
  1. 사용자가 뷰에 대해서 질의를 하면 user_views에서 뷰에 대한 정의를 조회한다.
  2. 기본 테이블에 대한 뷰의 접근 권한을 살핀다.
  3. 뷰에 대한 질의를 기본 테이블에 대한 질의로 변환한다.
  4. 기본 테이블에 대한 질의를 통해 데이터 검색한다.
  5. 검색된 결과를 출력한다.
 

--[5] 뷰와 기본 테이블 관계 파악
     : 1. 뷰를 통한 데이터 저장이 가능?
> insert into emp_view30
   values (8000, 'TOM', 30);
> select * from emp_view30;
> select * from emp_copy;
  
   # 저장이 가능하다!  emp_copy 테이블에도 저장이 되어있다!
      결론 : 뷰를 통해 데이터를 저장하면 기본테이블에 저장된다.
    
       2. insert문에 뷰(emp_view30)를 사용하였지만, 뷰는 쿼리문에 대한 이름일 뿐 새로운 레코드는 기본 테이블(emp_copy)에 실질적으로 추가되는 것이다.
     
       3. 뷰는 실질적인 데이터를 저장한 기본 테이블을 볼 수 있도록 한 투명한 창이다.
          즉, 기본 테이블의 모양이 바뀐 것이고 그 바뀐 내용을 뷰라는 창을 통해서 볼 뿐이다.
          뷰에 insert 뿐만 아니라, update, delete 모두 사용할 수 있는데, 이 명령문 역시 뷰의 텍스트에 저장되어 있는 기본 ㅔ이블을 변경하는 것이다.
           
--[6] 뷰를 사용하는 이유
     1. 복잡하고 긴 쿼리문을 뷰로 정의하면 접근을 단순화 시킬 수 있다.
     2. 보안에 유리하다.
        - 사용자마다 특정 객체만 조회할 수 있도록 권한을 부여할 수 있기 때문
> create view emp_view
   as
   select empno, ename, job, hiredate, deptno
   from emp_copy;
  
--[7] 뷰의 종류
    : 뷰는 뷰를 정의하기 위해서 사용되는 기본 테이블의 수에 따라
        - 단순 뷰 (simple view)
        - 복합 뷰 (complex view)
       
     : 특징
       - 단순 뷰
          + 하나의 테이블로 생성
          + 그룹 함수의 사용이 불가능
          + distinct 사용이 불가능
          + insert/update/delete(DML) 등의 사용 가능
         
       - 복합 뷰
          + 여러 개의 테이블로 생성
          + 그룹 함수의 사용이 가능
          + distinct 사용이 가능
          + DML 사용이 불가능
         
----[7-1] 단순 뷰에 대한 데이터 조작
> insert into emp_view30
   values (8010, 'HONG', 30);
  
----[7-2] 단순 뷰의 컬럼에 별칭 부여하기
> create view emp_view_copy( 사원번호, 사원명, 급여, 부서번호)
   as
   select empno, ename, sal, deptno
   from emp_copy;

----[문제] 부서 30번에 근부하는 사원 정보만을 출력하세요 (위에 만든 뷰 테이블을 이용)
> select * from emp_view_copy
   where 부서번호 = 30;                    # 별칭으로 사용해야한다!
           
----[7-3] 그룹 함수를 사용한 단순 뷰
               : ***중요***
               뷰를 작성할때 select절 다음에 sum이라는 그룹함수를 사용하면 결과를 뷰의 특정 컬럼처럼 사용하게 됨,
               따라서 물리적인 컬럼이 존재하지 않는 가상 컬럼이기에 컬럼명도 상속 받을 수 없다.
               뷰를 생성할 때 가상 컬럼을 사용하려면 사용자가 반드시 이름(별명)을 따로 설정해야한다.
                 
> create view view_sal
   as
   select deptno, sum(sal), avg(sal)
   from emp_copy
   group by deptno;
      #[err] 이 식은 열의 별명과 함께 지정해야 합니다.
  
> create view view_sal
   as
   select deptno, sum(sal) as "SalSum", avg(sal) "SalAvg"
   from emp_copy
   group by deptno;
  
----[주의 - 단순 뷰의 경우 DML 명령어의 조작이 불가능한 경우]
     1. 뷰 정의에 포함되지 않은 컬럼 중에 기본 테이블의 컬럼이 not null 제약 조건이 지정되어
        있는 경우 insert 문 사용이 불가능하다.
        왜? 뷰에 대한 insert문은 기본테이블에 null 값을 입력하는 형태가 되기 때문이다.
        ( 뷰는 일부 컬럼만 보여주니까 나머지 항목은 null로 넣음)
     2. sal * 12 와 같이 산술 표현식으로 정의된 가상 컬럼이 뷰에 정의되면 insert/update가 불가능
     3. distinct를 포함한 경우에도 DML(Data Manipulation language) 명령을 사용할 수  없다.
     4. 그룹 함수나 group by절을 포함한 경우에도 DML명령을 사용 할 수 없다.

     >select * from view_sal;
     > insert into view_sal
         values (40, 30000, 4000);       #[err] 안됨
     > create view veiw_sal_year
         as
          select ename, sal * 12
          from emp_copy;                      #[err] 안만들어짐~ 대신 sal * 12 에 별명을 붙이면 가능!
     > insert into view_sal_year
        value ('ANGEL', 12000);            #[err] 별명이 붙어있는 경우에도 insert는 안됨
       
----[7-4] 복합 뷰
> select empno, ename, sal, e.deptno, dname, loc
    from emp e, dept d
     where e.deptno = d.deptno
     order by empno desc;
    
> create view emp_view_dept
   as
   select empno, ename, sal, e.deptno, dname, loc
   from emp e, dept d
   where e.deptno = d.deptno
   order by empno desc;
  
--[8] 뷰 삭제
> select view_name, text
   from user_views;

> drop view emp_view;

--[9] 뷰 생성에 사용되는 다양한 옵션 (or replace)
    : 존재하지 않는 뷰이면 새로운 뷰를 생성하고 기존에 존재하는 뷰이면 그 내용을 변경한다.
> create or replace view emp_view30
   as
   select empno, ename, comm,deptno
   from emp_copy
   where deptno = 30;

--[10] 뷰 생성에 사용되는 다양한 옵션 (force / noforce)
     : force - 기본테이블이 존재하지 않을 때도 뷰를 생성해야 되는 경우 사용하는 옵션
        noforce - 기본테이블이 존재하는 경우만 뷰가 생성(default로 설정되어있음)

> desc employees;                     #이런 테이블 만들어 준 적 없음~
> create or replace view employees_view
   as
   select empno, ename, deptno
   from employees
   where deptno = 30;           #[err] 기본테이블이 없음!
  
> create or replace force view employees_view
   as
   select empno, ename, deptno
   from employees
   where deptno = 30;           #오류는 발생하지만 뷰는 만들어진다!
  
> select view_name, text
   from user_views;                    #뷰가 만들어져 있는 것을 확인
  
--[11] 뷰 생성에 사용되는 다양한 옵션 (with check option)
     : 뷰를 생성할 때 조건 제시에 사용된 컬럼 값을 변경하지 못하도록 하는 기능 제공
        뷰를 설정할 때 조건으로 설정한 컬럼 이외의 다른 컬럼의 내용은 변경할 수 있다.
> create or replace view emp_view30
   as
   select empno, ename, sal, deptno
   from emp_copy
   where deptno = 30;   

> update emp_view30
    set
        deptno = 20
     where sal >= 1200;
    
> select * from emp_view30;    #바뀐 레코드는 뜨지 않는다.

> create or replace view view_chk30
   as
   select empno, ename, sal, comm, deptno
   from emp_copy
   where deptno = 30 with check option;

> update view_chk30
    set
        deptno = 20
     where sal >= 900;                       #결과 : 0행이 갱신되었습니다.
    
--[12] 뷰 생성에 사용되는 다양한 옵션 (with read only)
     : 기본 테이블의 어떤 컬럼에 대해서도 뷰를 통한 내용 수정을 불가능하게 만드는 옵션
> update view_chk30
   set
     comm = 1000;
    
> create or replace view view_read30
   as
   select empno, ename, sal, comm, deptno
   from emp_copy
   where deptno = 30 with read only;        #조건 뒤에 붙이지만 전체 수정 불가!
    
> update view_read30
   set
     comm = 1000;                 #[err] 읽기 전용 뷰에서는 DML 작업을 수행 할 수 없습니다.





13. Top쿼리

*오라클 - Top쿼리
  :Top 쿼리 : 상위 몇 개의 data만을 출력하고 싶을 때
  ex) 사원 중에서 입사일이 가장 빠른 사람 5명(Top-5)만을 얻어오는 질의문
  : Top-N을 구하기 위해 rownum & 인라인뷰 사용 (oracle에서만 제공)
 
> select rownum, empno, ename, hiredate
      from emp;
      
> select rownum, empno, ename, hiredate
      from emp
       order by hiredate;
       # rownum은 insert 하는 순간 만들어지고 고유한 값이다.
          (rownum이 순차적으로 되어있지않음)
 
> create or replace view view_hire
     as
     select empno, ename, hiredate
     from emp
     order by hiredate;
    
> select rownum, empno, ename, hiredate
   from view_hire;
     #rownum이 순차적으로 할당되어있다. 왜냐면 뷰를 쓸때 새롭게
       insert하는 것과 같기 때문에
        (게시판 작성하면서 최근 날짜 순으로 일련번호 메길 때 많이 쓰인다.)
       
--[1] rownum을 이용
> select rownum, empno, ename, hiredate
   from view_hire
   where rownum <= 5;
  
--[2] 인라인 뷰를 이용
   :sql문에서 사용하는 서브 쿼리의 일종으로 보통 from 절에 위치해서 테이블처럼 사용하는 것.
> select rownum, empno, ename, hiredate
    from (select empno, ename, hiredate 
               from emp
                 order by hiredate)
     where rownum <= 5;
   





14. 사용자 권한

*오라클 - 사용자(User) 권한(Role)
--[1] 권한의 역할과 종류
    - 권한 : 사용자가 특정 테이블을 접근할 수 있도록 하거나 해당 테이블에 sql(select/ insert/ update/ delete)문을 사용 할 수 있도록 제한을 두는 것.
    - 데이터베이스 보안을 위한 권한은 시스템 권한과 객체 권한으로 나눌 수 있다.
    - 시스템 권한 : 사용자의 생성과 제거, DB 접근 및 각종 객체를 생성할  수 있는 권한 등 DBA에 부여됨
    - 객체 권한 : 객체를 조작할 수 있는 권한.

--[2] user01 계정 생성
> conn system/manager
> create user user01 identified by tiger;

--[3] 데이터베이스 접속 권한
> grant create session to user01;

> create table emp01(
          empno number(4),
          ename varchar2(10),
          job varchar2(10),
          deptno number(2)
   );          
   #[err] 권한이 불충분합니다!
 
--[4] 테이블 생성 권한
> conn system/manager
> grant create table to user01;
> create table emp01(
          empno number(4),
          ename varchar2(10),
          job varchar2(10),
          deptno number(2)
  );
    # insert를 바로 할 수 없다!
 
--[5] 테이블 스페이스 확인
    : 테이블 스페이스(table space)는 디스크 공간을 소비하는 테이블과 뷰 그리고 그 밖의 다른 데이터베이스 객체들이 저장되는 장소.
> alter user user01 quota 2m on users(system : 다른버전);     #메모리공간 할당
> insert into emp01
   values (7369, 'SMITH', 'CLERK', 20);


--[6] with admin option
    : 사용자에게 시스템 권한을 with admin option과 함께 부여하면 그 사용자는 데이터베이스 관리자가 아닌데도 불구하고 부여받은
       시스템 권한을 다른 사용자에게 부여할 수 있는 권한도 함께 부여받는다.
      
> create user user02 identified by tiger;
> grant create session to user02 with admin option;
> create user user03 identified by tiger;
> conn user02/tiger;
> grant create session to user03;               #user02로 user03에게 권한 부여 가능!


--[7] 테이블 객체에 대한  select 권한 부여 (scott/emp -> user01)
     1) > conn scott/tiger 계정 접속
     2) > grant select on emp to user01;
     3) > conn user01/tiger;
     4) > select * from emp;               #[err] 테이블 또는 뷰가 존재하지 않ij습니다

--[8] 스키마(SCHEMA) : 객체를 소유한 사용자명을 의미
     1) > select * from scott.emp;

--[9] 사용자에게 부여된 권한 조회
      .user_tab_privs_made : 현재 사용자가 다른 사용자에게 부여한 권한 정보를 알려줌
      .user_tab_privs_recd : 자신에게 부여된 사용자 권한을 알고 싶을 때
      
> select * from user_tab_privs_made;
> select * from user_tab_privs_recd;

--[10] 비밀번호 변경 시
> alter user user01 identified by user01;

----[참고] 한번에 권한 주기
> grant create session, create table, create view to user01;

--[11] 객체 권한 제거하기
> conn scott/tiger
> select * from user_tab_privs_made;
> revoke select on emp from user01;

--[12] with grant option (6번과 비교~)
   : 사용자에게 객체 권한을 with grant option과 함께 부여하면 사용자는
     객체를 접근할 권한을 부여 받으면서 그 권한을 다른 사용자에게 부여할
     수 있는 권한도 함께 부여받는다.
> conn scott/tiger
> grant select on scott.emp to user02 with grant option;
> conn user02/tiger                                                  # user02가 user03에게 scott의 권한 부여함
> grant select on scott.emp to user03;                       #여기에서는 scott.emp라고 꼭 해줘야함~

--[13] 사용자 계정 제거
> conn system/manager;
> drop user user03;

--[14] 롤(Role)
    : 사용자에게 보다 효율적으로 권한을 부여할 수 있도록 여러 개의 권한을 묶어 놓은 것.
    : 사용자를 생성했으면 그 사용자에게 각종 권한을 부여해야만 생성된 사용자가
      데이터베이스를 사용할 수 있다.
    
     1) connect role
          : 사용자가 데이터베이스에 접속 가능하도록 하기 위해서 다음과 같이
             가장 기본적인 시스템 권한 8가지를 묶어 놓은 권한
          : alter session, create cluster, create database link, create sequence,
             create session, create synonym, create table, create view
   
     2) resource role
          : 사용자 객체 (테이블, 뷰, 시퀀스)를 생성할 수 있도록 시스템 권한을 묶어놓은 것
          : create cluster, create procedure,
             create sequence, create table, create trigger
         
     3) DBA role       
         : 사용자들이 소유한 데이터베이스 객체를 관리하고 사용자들이 작성하고 변경하고
             제거할 수 있도록 하는 모든 권한을 가짐 (가급적 부여X)

> conn system/manager
> create user user03 identified by tiger;
> grant connect, resource to user03;

> conn user03/tiger;
> select * from dict
   where table_name like '%ROLE%';





15. 인덱스

*오라클 - 인덱스(Index)
  
   - 조회를 빠르게(빠른 검색) 하도록 도와줌.
   - sql 명령문의 처리 속도를 향상시키기 위해서 컬럼에 생성하는 오라클 객체.
  
   - 장점
     . 검색 속도가 빨라진다.
     . 시스템에 걸리는 부하를 줄여서 시스템 전체의 성능을 향상시킨다.
    
     - 단점
     . 인덱스를 위한 추가적인 공간이 필요하다. (메모리 할당 필요)
     . 인덱스를 생성하는데 시간이 걸린다.
     . 데이터의 변경 작업(DML : insert/ update/ delete)이 자주 일어날 경우,
        오히려 성능이 떨어진다.
       
--[1] 인덱스 정보 조회
> select index_name, table_name, column_name
    from user_ind_columns
    where table_name in ('EMP', 'DEPT');            #테이블명은 대문자로!
    

--[2] 조회 속도 비교하기
> drop table emp01;
> create table emp01 as select * from emp;
> select index_name, table_name, column_name
    from user_ind_columns
    where table_name in ('EMP', 'EMP01');
     # emp 테이블에는 empno 컬럼에 인덱스가 존재하지만
     # emp를 서브쿼리로 복사한 emp01 테이블에는 인덱스가 존재하지 않음
     # ->서브쿼리문으로 복사한 테이블은 구조와 내용만 복사될 뿐 제약조건은
            복사되지 않는다.
            
            
> insert into emp01
   select * from emp01;   
    # 계속 추가해서 데이터늘리기~ 917504행 추가까지 ㅋㅋ
    
> insert into emp01(empno, ename)
   values (8000, 'ANGEL');
  
> set timing on                    #시간재기   00: 00: 00.18
> select empno, ename
    from emp01
     where ename = 'ANGEL';
    
--[3] 인덱스 생성
    : 기본키(primary key)나 유일키(unique key)가 아닌 컬럼에 대해서 인덱스를 지정하려면
       create index 명령어 사용.

> create index idx_emp01_ename
   on emp01(ename);                                                 #인덱스 생성하는데 시간이 오래걸림
> select index_name, table_name, column_name
    from user_ind_columns
    where table_name in ('EMP', 'EMP01');                #인덱스 만들어진걸 확인할 수 있음
    
> select empno, ename
    from emp01
     where ename = 'ANGEL';                                        # 00: 00: 00.03
    
--[4] 인덱스 제거
> drop index idx_emp01_ename;

--[5] 인덱스를 사용해야 하는 경우 판단하기
   : 인덱스를 사용해야 하는 경우
      + 테이블에 행(레코드)의 수가 많을 때
       + where문에 해당 컬럼이 많이 사용될 때
       + 검색 결과가 전체 데이터의 2~4% 정도 일 때
       + join에 자주 사용되는 컬럼이나 null을 포함하는 컬럼이 많은 경우
      
     : 인덱스를 사용하지 말아야 하는 경우
      + 테이블에 행(레코드)의 수가 적을 때
       + where문에 해당 컬럼이 자주 사용되지 않을 때
      + 검색 결과가 전체 데이터의 10~15% 이상 일 때
      + 테이블에 DML 작업이 많은 경우(입력 수정 삭제 등이 자주 일어날 때)
      
      
--[6] 인덱스의 물리적인 구조와 인덱스의 재생성
> alter index idx_emp01_ename rebuild;

--[7]  인덱스의 종류
  1. 고유 인덱스(Unique Index)
     : 기본키나 유일키처럼 유일한 값을 갖는 컬럼에 대해서 생성하는 인덱스
> create unique index idx_dept01_deptno
   on dept01(deptno);
         
  2. 비고유 인덱스(Nonunique Index)
     : 중복된 데이터를 갖는 컬럼에 대해서 인덱스를 생성하는 경우
> drop table dept01 (cascade constraint);
> create table dept01
   as select * from dept;    
> insert into dept01 values (10, '인사과', '서울');
> insert into dept01 values (20, '총무과', '서울');
> insert into dept01 values (30, '개발부', '대전');
    
> create unique index idx_dept01_loc
   on dept01(loc);                                           #[err] 중복 키가 있습니다.
  
> create unique index idx_dept01_deptno
   on dept01(deptno);                                        #가능
 
  3. 단일 인덱스(Single Index)
     : 한 개의 컬럼으로 구성한 인덱스.
    
  4. 결합 인덱스(Composite Index)
     : 두 개 이상의 컬럼으로 인덱스를 구성하는 것.
> create index idx_dept01_com
    on dept01(deptno, dname);
    
  5. 함수 기반 인덱스(Funtion Based Index)
> create index idx_emp01_annsal
    on emp01(sal *12);
    
> select index_name, column_name
    from user_ind_columns
    where table_name = 'EMP01'
      
       





16. 저장프로시져

*오라클 - 저장프로시저(procedure)
   -복잡한 쿼리문(insert/ update/ delete)들을 필요할 때 마다 다시 입력할 필요없이 간단하게 호출만 해서 복잡한 쿼리문의 실행결과를 얻어올 수 있음
   - 성능도 향상되고, 호환성 문제도 해결
   - 여러 번 반복 호출해서 사용할 수 있는 장점.
  
> drop table emp01;
> create table emp01
      as
       select * from emp;
      
> ed proc01;              
     #메모장이 뜨면서 proc01.sql파일이 만들어짐
     
       create or replace procedure DEL_ALL
          is
          begin
          delete from emp01
          end;
          /
     #sql최초 접속 경로(C:\Users\user) 에 파일 있음
    
> @proc01                                   #프로시저 생성
> execute del_all                    # 실행
> show error;

--[1] 저장 프로시저 조회하기
> desc user_source;
> select name, text
   from user_source

--[2] 저장 프로시저의 매개변수
> ed proc02

  create or replace procedure del_name(vename emp01.ename%type)
  is
  begin
  delete from emp01 where ename = vename;
  end;
  /

> @proc02
> execute del_ename('SMITH');

--[3] IN, OUT, INOUT 매개변수
> ed proc03

create or replace procedure sel_empno
(  vempno in emp.empno%type,
     vename out emp.ename%type,
     vsal out emp.sal%type,
     vjob out emp.job%type
)
is
begin

select ename, sal, job
into vename, vsal, vjob
from emp where empno = vempno;

end;
/

> @proc03
> variable var_ename varchar2(15);                    # 반환받아온 값들을 출력하기 위한 변수
> variable var_sal varchar2(15);
> variable var_job varchar2(15);
> execute sel_empno(7788, :var_ename, :var_sal, :var_job);
> print var_ename;
> print var_sal;
> print var_job;





문제들

[문제1] 10번 부서 소속의 사원 중에서 커미션을 받는 사원의 수를 구해보시오. ?
SQL > select count(*)
        from emp
        where nvl(comm, 0) > 0
        and deptno = 10;

SQL > select count(*)
        from emp
        where nvl(comm, 0) <> 0
        and deptno = 10;

[문제1-1] 30번 부서 소속의 사원 중에서 커미션을 받는 사원의 수를 구해보시오. ?


[문제2] 가장 최근에 입사한 사원의 입사일과 입사한 지 가장 오래된 사원의 입사일을 출력하는
         쿼리문을 작성해 보세요.
SQL > select min(hiredate), max(hiredate)
        from emp;
SQL > select min(hiredate) "Old", max(hiredate) "New"
        from emp;
         //별칭 붙이기. 큰따옴표를 붙이면 입력내용 그대로. 큰따옴표 없으면 결과는 대문자로 출력.

[문제3] SCOTT과 동일한 근무지에서 근무하는 사원의 이름을 출력하세요.
SQL > select ename
        from emp
        where deptno = (
                             select deptno
                             from emp
                             where ename='SCOTT'
                              );

[문제4] 직급이 'SALESMAN'인 사원이 받는 급여들의 최소 급여보다 많이 받는 사원들의
         이름과 급여를 출력하되 부서 번호가 20번인 사원은 제외하세요. (다중행 연산자 이용)
SQL > select ename, sal, deptno
        from emp
        where sal > any(
                            select sal
                            from emp
                            where job = 'SALESMAN'
                           )
                      and deptno <> 20 ;

-단일행서브쿼리 이용
select ename, sal, deptno
        from emp
        where sal > (
                         select min(sal)
                         from emp
                         where job = 'SALESMAN'
                          )
                      and deptno <> 20 ;

[문제5] 직급이 'SALESMAN'인 사원이 받는 급여들의 최대 급여보다 많이 받는 사원들의
         이름과 급여를 출력하되 부서번호가 10번인 사원은 제외하세요. (다중행 연산자 이용)
SQL >  select ename, sal
         from emp
         where sal > all (
                            select sal
                            from emp
                            where job = 'SALESMAN'
                            )
                           and deptno <> 10 ;

[문제6] 사원테이블(emp)과 부서 테이블(dept)을 join하여 사원 이름, 부서번호, 부서명을 출력하세요.
         단, 40번 부서의 부서 이름도 출력되도록 쿼리문을 작성하세요. ????????????
SQL > select emp.ename, dept.deptno, dept.dname
        from emp, dept
        where emp.deptno(+) = dept.deptno ;

//Ansi표준 ? ??????????????????????????
select ename, emp.deptno, dname
from emp full outer join dept
on emp.deptno = dept.deptno;


[문제7] 뉴욕에서 근무하는 사원의 이름과 급여를 출력하세요. (join 이용)
//오라클 초기 입력방법?
SQL > select ename, sal
        from emp, dept
        where emp.deptno = dept.deptno
        and dept.loc = 'NEW YORK';

//ANSI 표준
SQL > select ename, sal
         from emp inner join dept
         on emp.deptno = dept.deptno
         where dept.loc = 'NEW YORK' ;


[문제1] 기본테이블은 EMP_COPY로 합니다. 20번 부서에 소속된 사원들의 사번과 이름, 부서번호와 상관의 사번을
       출력하기 위한 select문을 emp_view20이라는 이름의 뷰로 정의해 보세요.
       
        create or replace view emp_view20
        as
        select empno, ename, deptno, mgr
        from emp_copy;
       
        select * from emp_view20;

[문제2] 각 부서별 최대 급여와 최소 급여를 출력하는 뷰를 sal_view 라는 이름으로 작성하세요.
 
        create or replace view sal_view
        as
        select max(sal) "최대 급여", min(sal) "최소 급여", deptno
        from emp_copy
        group by deptno;
 
        select * from sal_view;
       
       
[문제3] 인라인뷰를 이용하여 급여를 많이 받는 순서대로 3명만 출력하는 뷰(sal_top3_view)를 작성하세요.
         
          select rownum, empno, ename, sal
          from (select rownum, empno, ename, sal
                   from emp_copy
                    order by nvl(sal, 0) desc)
          where rownum <= 3;
         
          ---------------------------------------------------
         
          create or replace view sal_top3_view
          as
          select rownum as "순위", empno, ename, sal
          from (select empno, ename, sal
                   from emp_copy
                    where sal is not null
                    order by sal desc
                  )
          where rownum <=3;
         
         
[문제4] acorn/tiger 계정을 생성해서 순번/사원번호/이름/직업/근무부서(컬럼)의 항목을 갖는 테이블을 만들어서 데이터를
       저장하되 순번 데이터 입력은 sequence를 이용하여 저장하고, view_acorn 뷰를 생성해서 순번/사원번호/이름만
       출력할 수 있도록 만들어보세요.
       
        conn system/manager;
        create user acorn identified by tiger;
        grant connect, resource to acorn;
        grant create view to acorn;
       
        conn acorn/tiger;
       
        create table acorn_emp(
               num number(2),
               empno number(4),
               ename varchar2(20),
               job varchar2(20),
               deptno number(2)
        );
       
        create sequence acorn_seq
        start with 1 increment by 1;
                 
          insert into acorn_emp
          values (acorn_seq.nextval, 1111, 'SMITH', 'MANAGER', 10);
         
          insert into acorn_emp
          values (acorn_seq.nextval, 2222, 'JONES', 'SALESMAN', 20);
         
          insert into acorn_emp
          values (acorn_seq.nextval, 3333, 'TOM', 'PRESIDENT', 30);
         
          create or replace view view_acorn
         as
         select num, empno, ename
         from acorn_emp;
         
          select * from view_acorn;
         
         
         
         
          ----------------------------------
         
         
          conn system/manger;
          create user acorn identified by tiger;
          grant create session, create table, create sequence, create view to acron;
          alter user acorn quota 2m on users;
          conn acorn/tiger;
         
          create table emp01 (
               no number(2) primary key,
               empno number(4) not null,
               ename varchar2(10) not null,
               job varchar2(10),
               deptno number(2)
          );
         
          create sequence emp01_seq
          start with 1 increment by 1;
         
          insert into emp01
          values (emp01_seq.nextval, 7000, 'SMITH', 'SALES', 20);
         
          insert into acorn_emp
          values (acorn_seq.nextval, 2222, 'JONES', 'SALESMAN', 20);
         
          create or replace view view_acorn
         as
         select no, empno, ename
         from acorn_emp;