DATA BASE _ 기초

SELECT * FROM TAB;
DESC DEPT;
SELECT * FROM DEPT;
DESC EMP;

SELECT EMPNO,ENAME,JOB,SAL,DEPTNO FROM EMP;
DESC EMP;

SELECT EMPNO AS 사원번호,ENAME 사원이름,SAL 본봉,SAL*1.2 AS 총급여 FROM EMP;

SELECT EMPNO,ENAME,COMM,COMM+100, SAL,SAL+COMM AS 총급여  FROM EMP;

SELECT ENAME ||' 의 직업은 '||JOB || '입니다.' AS "이름 직업" FROM EMP;
-- 중복행제거
/*
중복행제거
여러줄주석
*/
SELECT DISTINCT JOB FROM EMP;

SELECT EMPNO,ENAME,JOB,DEPTNO
FROM EMP
WHERE DEPTNO=30;

SELECT * 
FROM EMP
WHERE JOB='CLERK';
SELECT * FROM EMP;
SELECT * FROM EMP WHERE HIREDATE='81/11/17';

SELECT *
FROM EMP
WHERE SAL<=1000;

SELECT *
FROM EMP
WHERE SAL>=100 AND SAL <=2000;

SELECT EMPNO,ENAME,SAL 
FROM EMP 
WHERE SAL BETWEEN 1000 AND 2000;

SELECT EMPNO,ENAME,COMM 
FROM EMP
WHERE COMM IS NULL;

SELECT EMPNO,ENAME,COMM
FROM EMP
WHERE EMPNO IN(7369,7654,7934);

SELECT EMPNO,ENAME,JOB,COMM
FROM EMP
WHERE JOB IN('CLERK','SALESMAN');


SELECT *
FROM EMP
WHERE ENAME LIKE 'A%';

SELECT *
FROM EMP
WHERE ENAME LIKE '%T%';

SELECT *
FROM EMP
WHERE ENAME LIKE '_L%';

SELECT *
FROM EMP
WHERE ENAME LIKE '%\_%' ESCAPE '\' ;

SELECT * 
FROM EMP
WHERE COMM IS NULL;

SELECT * 
FROM EMP
WHERE COMM IS NOT NULL;

/*
INSERT INTO EMP VALUES
(9000,'M_BEER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
DELETE FROM EMP WHERE EMPNO=9000;
commit;
*/
SELECT *
FROM EMP
WHERE ENAME LIKE '%\_%' ESCAPE '\';

SELECT * FROM EMP;

SELECT EMPNO,ENAME,HIREDATE FROM EMP
ORDER BY HIREDATE ASC;

SELECT EMPNO,ENAME,SAL*12 FROM EMP
ORDER BY SAL*12 ASC;

SELECT EMPNO,ENAME,SAL*12 FROM EMP
ORDER BY 3 ASC;

SELECT EMPNO,ENAME FROM EMP
ORDER BY ENAME DESC;

SELECT EMPNO,ENAME,SAL FROM EMP
ORDER BY SAL DESC ,EMPNO ASC;
--단일행함수

DESC DUAL;
SELECT DUMMY FROM DUAL;

SELECT ROUND(45.528,2),ROUND(45.582,0),ROUND(45.582,-1) FROM EMP;
SELECT ROUND(45.528,2),ROUND(45.582,0),ROUND(45.582,-1) FROM DUAL;

SELECT TRUNC(45.528,2) ,TRUNC(45.528,0),TRUNC(45.528,-1),TRUNC(45.528,-2) FROM DUAL;

SELECT MOD(42342,7) FROM DUAL;

SELECT EMPNO,ENAME,SAL,MOD(SAL,10) FROM EMP;

SELECT CEIL(15.3),CEIL(-15.3) FROM DUAL;
SELECT FLOOR(15.3),FLOOR(-15.3) FROM DUAL;

SELECT HIREDATE FROM EMP;

SELECT SYSDATE FROM DUAL;
SELECT SYSTIMESTAMP FROM DUAL;


SELECT EMPNO,ENAME,SYSDATE,HIREDATE,TRUNC((SYSDATE - HIREDATE)/7,0) FROM EMP;

SELECT EMPNO,ENAME,TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE),0) FROM EMP;
SELECT EMPNO,ENAME,HIREDATE,ADD_MONTHS(HIREDATE,6) FROM EMP;
SELECT SYSDATE,ADD_MONTHS(SYSDATE,6) FROM DUAL;
ALTER SESSION SET NLS_LANGUAGE='AMERICAN';
SELECT SYSDATE,NEXT_DAY(SYSDATE,'SUN') FROM DUAL;
ALTER SESSION SET NLS_LANGUAGE='KOREAN';

SELECT EMPNO,ENAME,HIREDATE,ROUND(HIREDATE,'MONTH'),SYSDATE,ROUND(SYSDATE,'MONTH') FROM EMP;
SELECT EMPNO,ENAME,HIREDATE,ROUND(HIREDATE,'YEAR'),SYSDATE,ROUND(SYSDATE,'YEAR') FROM EMP;

SELECT EMPNO,ENAME,HIREDATE,TRUNC(HIREDATE,'MONTH'),SYSDATE,TRUNC(SYSDATE,'MONTH') FROM EMP;
SELECT EMPNO,ENAME,HIREDATE,TRUNC(HIREDATE,'YEAR'),SYSDATE,TRUNC(SYSDATE,'YEAR') FROM EMP;
SELECT * FROM EMP;
--변환함수
SELECT EMPNO,ENAME FROM EMP WHERE EMPNO='7839';
SELECT EMPNO,ENAME FROM EMP WHERE EMPNO=TO_NUMBER('7839');

SELECT EMPNO,ENAME,HIREDATE FROM EMP WHERE hiredate='82/01/23';
-- 1982-01-23 , 1982 01 23 
SELECT EMPNO,ENAME,HIREDATE FROM EMP WHERE hiredate=to_date('82/01/23','RR/MM/DD');
ALTER SESSION SET NLS_LANGUAGE='AMERICAN';
SELECT EMPNO,ENAME,HIREDATE 
FROM EMP 
WHERE hiredate=to_date('1982 JANUARY 23','YYYY MONTH DD');

select hiredate,to_char(hiredate,'YYYY/MM/DD') from emp;
select sysdate,to_char(sysdate,'YEAR MONTH DD DAY HH:MI:SS') from dual;

select empno,ename,sal,to_char(sal,'L99,999.99') from emp;
select empno,ename,sal,to_char(sal,'$99,999.90') from emp;

select empno,ename,sal,comm,sal*12+comm, sal*12+NVL(comm,0) 총급여 from emp;
select empno,ename,sal,comm,sal*12+comm, sal*12+NVL2(comm,comm,0) 총급여 from emp;
select empno,ename,sal,comm,sal*12+comm, NVL2(comm,sal*12+comm,sal*12) 총급여 from emp;

select empno,ename,sal,
  case job when 'ANALYST' then sal*1.1
           when 'CLERK' then sal*1.2
           when 'MANAGER' then sal*1.3
           when 'PRESIDENT' then sal*1.4
           when 'SALESMAN' then sal*1.5
           else sal
  end "급여"
from emp;  

select empno,ename,sal,
  case     when job='ANALYST' then sal*1.1
           when job='CLERK' then sal*1.2
           when job='MANAGER' then sal*1.3
           when job='PRESIDENT' then sal*1.4
           when job='SALESMAN' then sal*1.5
           else sal
  end "급여"
from emp;  


select empno,ename,sal,
  case     when sal>=1000 then sal*1.1
           else sal
  end "급여"
from emp;  

--GROUP FUNCTION
select sum(sal),avg(sal),min(sal),max(sal) from emp;
select count(*),count(comm),count(nvl(comm,0)),sum(comm),round(avg(nvl(comm,0)),2) from emp;

select deptno,avg(sal) 
from emp
group by deptno;

select deptno,job,avg(sal)
from emp
group by deptno,job
order by deptno asc;

select deptno ,sum(sal)
from emp
where sal>800
group by deptno
having sum(sal)>9000
order by deptno asc;

 SELECT DEPTNO ,
        DECODE( JOB , 'CLERK', SAL) CLERK,
        DECODE( JOB , 'MANAGER', SAL) MANAGER FROM EMP;
--ANSI JOIN

SELECT EMPNO,ENAME,DNAME
FROM DEPT,EMP;

SELECT EMP.EMPNO,EMP.ENAME,DEPT.DNAME,DEPT.LOC
FROM EMP,DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO; 

SELECT e.EMPNO,e.ENAME,e.DEPTNO,d.DEPTNO,d.DNAME,d.LOC
FROM EMP e,DEPT d
WHERE e.DEPTNO=d.DEPTNO AND e.EMPNO = 7788;

SELECT e.EMPNO,e.ENAME,e.SAL,s.GRADE
FROM EMP e,SALGRADE s
WHERE e.SAL BETWEEN s.LOSAL AND s.HISAL;


SELECT e.EMPNO,e.ENAME,e.SAL,s.GRADE
FROM EMP e,SALGRADE s
WHERE e.SAL>=s.LOSAL AND e.SAL<=s.HISAL;




SELECT e.EMPNO,e.ENAME,e.DEPTNO,d.DEPTNO,d.DNAME,d.LOC
FROM EMP e,DEPT d
WHERE e.DEPTNO(+)=d.DEPTNO;

INSERT INTO EMP VALUES(9999,'홍길동','MANAGER',7839,sysdate,2450,NULL,NULL);

SELECT * FROM EMP;

SELECT e.EMPNO,e.ENAME,e.DEPTNO,d.DEPTNO,d.DNAME,d.LOC
FROM EMP e,DEPT d
WHERE e.DEPTNO = d.DEPTNO(+);

rollback;

select * from emp;

select a.ename  "사원" ,b.ename "관리자"
from emp a,emp b 
where a.mgr = b.empno(+);

--SQL 1999 JOIN
select empno,ename,dname,loc 
from emp cross join dept;

select empno,ename,dname,loc 
from emp natural join dept;

select empno,ename,dname,loc 
from emp join dept using(deptno);

select empno,ename,dname,loc
from emp join dept
on emp.deptno=dept.deptno and dept.deptno=10;

select empno,ename,sal,dname,loc
from emp join dept
on emp.deptno=dept.deptno and sal in(800,3000,5000);

select e.empno,e.ename,d.dname,e.sal,s.grade
from emp e join dept d
           on e.deptno=d.deptno
           join salgrade s
           on e.sal between s.losal and s.hisal;

select a.ename "사원", b.ename "관리자"
from emp a join emp b
     on a.mgr=b.empno;     
     
INSERT INTO EMP VALUES(9999,'홍길동','MANAGER',7839,sysdate,2450,NULL,NULL);
     
select empno,ename,dname,loc
from emp right outer join dept
on emp.deptno=dept.deptno;

select empno,ename,dname,loc
from emp left outer join dept
on emp.deptno=dept.deptno;     
     
select empno,ename,dname,loc
from emp full outer join dept
on emp.deptno=dept.deptno;  

select sal from emp where ename='SCOTT';

select empno,ename,sal from emp where sal > 3000;


select empno,ename,sal 
from emp 
where sal >(select sal 
            from emp 
            where ename='SCOTT');
--DML
insert into dept(deptno,dname,loc) values(90,'인사과','서울');
insert into dept values(80,'인사과','서울');
insert into dept(loc,dname,deptno) values('서울','인사과',70);
select * from dept;
desc dept;
/*
desc dept
이름     널        유형           
------ -------- ------------ 
DEPTNO NOT NULL NUMBER(2)    
DNAME           VARCHAR2(14) 
LOC             VARCHAR2(13) 
*/
insert into dept(deptno,dname) values(91,'인사과');
insert into dept values(92,'인사과',null);

desc emp;
/*
desc emp
이름       널        유형           
-------- -------- ------------ 
EMPNO    NOT NULL NUMBER(4)    
ENAME             VARCHAR2(10) 
JOB               VARCHAR2(9)  
MGR               NUMBER(4)    
HIREDATE          DATE         
SAL               NUMBER(7,2)  
COMM              NUMBER(7,2)  
DEPTNO            NUMBER(2)  
*/
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
       values(9000,user,'연구원',7839,sysdate,5000,null,90);

select * from emp where empno >=9000;

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
       values(9001,'홍길동','MANAGER',7839,'2012/03/22',2000,null,90);

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
       values(9002,'임꺽정','MANAGER',7839,
       to_date('2012/03/20','YYYY/MM/DD'),2000,null,30);

create table dept_1(deptno number(2),dname varchar2(14),loc varchar2(13));
desc dept_1;

insert into dept_1(deptno,dname,loc) select deptno,dname,loc from dept;
select * from dept_1;

select * from dept;

update dept 
set dname='경리과',loc='부산'
where deptno=90;

select * from emp where empno=9001;

update emp
  set job=(select job 
           from emp 
           where empno=7900 
          ),
          sal=(select sal
              from emp 
              where empno=7844
              )
where empno=9001; 

update dept_1
set dname=(
            select dname 
            from dept 
            where deptno=30)
where deptno=(
              select deptno 
              from dept 
              where dname='SALES');

select *  from dept_1 where deptno=30;
select * from dept_1;

delete from emp 
where deptno=91;

delete emp 
where deptno=(select deptno 
              from dept 
              where dname='경리과');

select deptno,dname from dept;
select * from emp;

rollback;       

select * from emp;
select * from dept;
delete from dept where deptno=91;
delete from dept where deptno=70;
delete from dept where deptno=80;


delete from dept where deptno=92;
select * from dept;
commit;
       
delete from emp; 
select * from emp;
rollback;
--DDL

create table dept_2(deptno number(2),dname varchar2(10),loc varchar2(10));
desc dept_2;
create table scott.dept_3(deptno number(2),dname varchar2(10),loc varchar2(10));
desc dept_3;

create table def_table(num number(2),writeday date);

create table def_table2(num number(2),writeday date default sysdate);

insert into def_table(num) values(1);
insert into def_table(num,writeday) values(2,sysdate);

insert into def_table2(num) values(1);
insert into def_table2(num,writeday) values(2,default);

select * from def_table;
select * from def_table2;

create table new_tmp(tmp_id number(2),w_day  date,w_day2 timestamp);
desc new_tmp;
insert into new_tmp values(1,sysdate,sysdate);
select * from new_tmp;

create table new_tmp2(tmp_id number ,w_day timestamp  with time zone);
desc new_tmp2;
insert into new_tmp2 values(1,sysdate);
insert into new_tmp2 values(2,sysdate);
insert into new_tmp2 values(3,sysdate);
insert into new_tmp2 values(4,sysdate);
insert into new_tmp2 values(5,sysdate);
select * from new_tmp2;

create table new_tmp3(tmp_id number ,w_day timestamp  with local time zone);
insert into new_tmp3 values(1,sysdate);
insert into new_tmp3 values(2,sysdate);
insert into new_tmp3 values(3,sysdate);
insert into new_tmp3 values(4,sysdate);
insert into new_tmp3 values(5,sysdate);
insert into new_tmp3 values(6,sysdate);
insert into new_tmp3 values(7,sysdate);
insert into new_tmp3 values(8,sysdate);
insert into new_tmp3 values(9,sysdate);
insert into new_tmp3 values(10,sysdate);
select * from new_tmp3;

SELECT SESSIONTIMEZONE, DBTIMEZONE, localtimestamp FROM dual;
alter session set time_zone = '+09:00';
commit;

create table new_tmp4
( time1  interval year to month,
  time2 interval day to second);
desc new_tmp4;  

insert into new_tmp4 values( 
interval '10-1' year to month,
interval '10 10:10:10' day to second);

select  to_char(sysdate,'YYYY/MM/DD HH24:MI:SS'),
        to_char(sysdate+time1,'YYYY/MM/DD HH24:MI:SS'),
        to_char(sysdate+time1+time2,'YYYY/MM/DD HH24:MI:SS')
from new_tmp4;

create table deptA 
as
select * from dept;

desc deptA;
select * from deptA;

create table deptB(no,name)
as
select deptno,dname from dept;

desc deptB;
select * from deptB;

create table deptC
as
select * from dept where 1=2;

select count(*) from deptC;
--constraint

create table sawon(
  s_no number(4),
  s_name varchar2(10) not null,
  s_hiredate date constraint sawon_s_hiredate_nn not null
);
desc sawon;
select * from user_constraints where table_name='SAWON3';

insert into sawon values(1,'길동',sysdate);
insert into sawon values(2,'춘향',sysdate);

select * from sawon;
--column level
create table sawon2 (
  s_no number(2),
  s_name varchar2(10),
  s_email varchar2(20) constraint sawon2_s_email_uk unique
);
insert into sawon2 values(1,'홍길동','adb@abc.com');
insert into sawon2 values(2,'유관순','adb@abc.co.kr');
--insert into sawon2 values(3,'강감찬','adb@abc.co.kr');
-- table level
create table sawon3 (
  s_no number(2),
  s_name varchar2(10),
  s_email varchar2(20),
  constraint sawon3_s_email_uk unique(s_email)
);

create table sawon4 (
  s_no number(2),
  s_name varchar2(10),
  s_email varchar2(20),
  constraint sawon4_s_email_s_name_uk unique(s_name, s_email)
);

INSERT INTO SAWON4 VALUES (1, '홍길동', 'ABC@ABC.CO.KR');
INSERT INTO SAWON4 VALUES (2, '유관순', 'ABC@ABC.CO.KR');
-- INSERT INTO SAWON4 VALUES (3, '유관순', 'ABC@ABC.CO.KR'); --unique constraint (%s.%s) violated
INSERT INTO SAWON4 VALUES (3, '이순신', NULL);
INSERT INTO SAWON4 VALUES (4, NULL, NULL);
INSERT INTO SAWON4 VALUES (5, NULL, NULL); --유니크에 안걸려서 중복삽입 가능.
SELECT * FROM SAWON4;

--12.16 PRIMARY KEY 제약조건 
/*
: 테이블에 기본키를 의미하며, 한 개의 테이블에는 오직 한 개의 기본키를 만들 수 있다.
: UNIQUE + NOT NULL 제약조건 특성을 갖는다.
: 테이블 수준 및 컬럼 수준 , 2 가지 방법 모두 사용 가능하다.
: UNIQUE 와 마찬가지로 INDEX 가 자동 생성된다.
 CONSTRAINT 이름 UNIQUE,NOTNULL,PRIMARY 해주면 된다.
*/
CREATE TABLE SAWON5 (
  S_NO NUMBER(2) CONSTRAINT SAWON5_S_NO_PK PRIMARY KEY,
  S_NAME VARCHAR2(10),
  S_SAL NUMBER(10)
); --방법1
INSERT INTO SAWON5 VALUES(1, '이순신', 2000);
-- INSERT INTO SAWON5 VALUES(1, '김순신', 2000); --"unique constraint (%s.%s) violated"

CREATE TABLE SAWON6 (
  S_NO NUMBER(2),
  S_NAME VARCHAR2(10),
  S_SAL NUMBER(10),
  CONSTRAINT SAWON6_S_NO_PK PRIMARY KEY (S_NO)); -- 방법2
select * FROM user_constraints WHERE TABLE_NAME = 'SAWON6';

--12.17FOREIGN KEY 제약조건
/*
: 참조 무결성 제약조건이라고도 한다.
: 반드시 다른 테이블의 기본키나 또는 UNIQUE 컬럼의 값을 참조하도록 제한한다. ( NULL허용)
*/
CREATE TABLE DAPT2
( DEPTNO NUMBER(2) CONSTRAINT DEPT2_DEPTNO_PK PRIMARY KEY,
  DNAME VARCHAR2(10),
  LIC VARCHAR2(10)
  );
RENAME DAPT2 TO DEPT2;
  
INSERT INTO DEPT2 VALUES (10, '인사', '서울');
-- INSERT INTO DAPT2 VALUES (10, '총무', '서울'); --unique constraint (SCOTT.DEPT2_DEPTNO_PK)
INSERT INTO DEPT2 VALUES (20, '인사', '부산');
SELECT * FROM DEPT2;

-- 외래키참조
CREATE TABLE EMP2
( EMPNO NUMBER (4) CONSTRAINT EMP2_EMPNO_KO PRIMARY KEY,
  ENAME VARCHAR2(10),
  SAL NUMBER(10),
  DEPTNO NUMBER(2) CONSTRAINT EMP2_DEPTNO_FK REFERENCES DAPT2(DEPTNO) );
SELECT * FROM user_constraints WHERE TABLE_NAME = 'EMP2'; 
INSERT INTO EMP2 VALUES (1111,'이효리',5600, 10);
INSERT INTO EMP2 VALUES (2222,'강수지',5600, 20);
-- INSERT INTO EMP2 VALUES (2222,'강수지',5600, 14); --unique constraint (SCOTT.EMP2_EMPNO_KO) violated
SELECT * FROM EMP2;


CREATE TABLE EMP3 
( EMPNO NUMBER (4) CONSTRAINT EMP3_EMPNO_PK PRIMARY KEY,
  ENAME VARCHAR2 (10),
  SAL NUMBER(10),
  DEPTNO NUMBER(2),
  CONSTRAINT EMP3_DEPTNO_FK FOREIGN KEY (DEPTNO) REFERENCES DEPT2(DEPTNO));
  
  
INSERT INTO EMP3 VALUES (1111,'이효리',5600, 10);
INSERT INTO EMP3 VALUES (2222,'강수지',5600, 20);
SELECT * FROM EMP3;

/*
FOREIGN KEY 추가옵션
- 부모 테이블의 행 삭제 시 문제될 수 있는 자식테이블 행 설정법.

: ON DELETE CASCADE
  - FK 제약조건에 의해 참조되는 테이블( 부모 테이블)의 행이 삭제되면, 
   해당 행 을 참조하는 테이블(자식 테이블)의 행도 같이 삭제되도록 한다.
: ON DELETE SET NULL
  - FK 제약조건에 의해 참조되는 테이블( 부모 테이블)의 행이 삭제되면, 
  해당 행 을 참조하는 테이블(자식 테이블)의 행을 NULL 로 설정한다.
*/
--CHECK CONSTRAINT
CREATE TABLE SAWON7
( S_NO NUMBER(4),
  S_NAME VARCHAR2(10),
  S_SAL NUMBER (10) CONSTRAINT SAWON7_S_SAL_CHK CHECK(S_SAL > 500) ); --행 레벨
  

CREATE TABLE SAWON8
( S_NO NUMBER(4),
  S_NAME VARCHAR2(10),
  S_SAL NUMBER (10), CONSTRAINT SAWON8_S_SAL_CHK CHECK(S_SAL > 500) ); --테이블레벨
  
--INSERT INTO SAWON7 VALUES (1, '김미은', 400); --check constraint (SCOTT.SAWON7_S_SAL_CHK) violated
INSERT INTO SAWON7 VALUES (2, '김미숙', 600);

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME ='SAWON7';

--DROP
/*
데이터베이스에서 해당 테이블을 제거하는 것이다.
: 테이블에 저장된 모든 데이터와 관련 INDEX가 삭제된다.
*/
DROP TABLE SAWON8;
DESC SAWON8;
-- TRUNCATE: 
/*테이블잘라내기
: 테이블의 모든 행들을 삭제할수있다.
: 테이블이 사용하고 있던 저장 공간을 해제하여 다른 테이블들이 사용 할 수 있도록 한다.
: DELETE 명령은 저장공간을 해제하지 않는다.
: ROLLBACK 정보를 발생시키지 않아서 DELETE 보다 수행속도가 빠르다
단, DELETE와 달리 ROLLBACk 은 불가능하다.
*/
TRUNCATE SAWON8;


----------------- 2013.03.26 화요일
--CH13. 기타스키마 객체
-- 13.3 VIEW
CREATE VIEW EMP_VIEW 
AS
SELECT EMPNO, ENAME, SAL, HIREDATE
FROM EMP
WHERE DEPTNO= 10; -- "insufficient privileges" -> 권한 줘야 함. sys에서 grant create view to scott;
desc emp_view ;
select * from emp_view;

create view dept_view
as
select deptno as no, dname name
from dept;
desc dept_view;
select * from dept_view;

-- 13.4 view 수정
create or replace view dept_view
as 
select deptno no, dname name, loc location
from dept;
desc dept_view;
select * from dept_view;

-- 13.4 복합 뷰 생성
create view emp_dept_view
as
select empno, ename, dname
from emp, dept
where emp.deptno = dept.deptno
and dept.deptno = 30;
desc emp_dept_view;
select * from dept_view;


select * from emp_view;
delete from emp_view where empno = 7782;

--13.6 제약조건
create or replace view emp_view
as
select * from emp
where deptno = 10
with check option constraint empview10_check;

select * from emp;

update emp_view
set deptno = 10 where ename = 'KING';
select * from emp;

-- 13.6 WITH READ ONLY
create or replace view dept_view
as
select * from dept
where deptno = 10
with read only;
delete from dept_view; -- cannot perform a DML operation on a read-only view
select * from emp;

--13.9 시퀀스 사용
create sequence emp_empno_seq
increment by 1
start with 100;

desc user_sequences;
select * from user_sequences;
insert into emp values (emp_empno_seq.nextval, '김수미', '인사', null, sysdate, 2500, 300, 40);
select * from emp;

select emp_empno_seq.currval from dual; -- 현재 값 보기
select emp_empno_seq.nextval from dual; -- nextval 할 때 마다 값이 증가

alter sequence emp_empno_seq
increment by 2; -- 다시 되돌려줌
select emp_empno_seq.nextval from dual;
select emp_empno_seq.currval from dual;

drop sequence emp_empno_seq; -- sequence EMP_EMPNO_SEQ이(가) 삭제되었습니다.

-- 13.11 인덱스 -> 넘어갈게요. 회사가서 하시면됩니다.

--01. 사용자관리
-- 1.5 사용자에게 권한 부여 
-- grant create session, create table to suin; --권한주기(suin 이 conn 생성가능)
select * from session_privs;

 -- 1.7 객체권한 부여
 grant select on dept to  OSI;
 grant select on emp to OSI;
 
 grant update(dname) on dept to OSI;