<이공계전문기술연수> 5. DB 실습

(1)

--1. 직급별 총 급여 및 연봉을 산출 하여라.

select * from employee;
select job_code, sum(salary) 총급여, avg(salary*12) 연봉 from employee group by job_code;

--2. [EMPLOYEE] 테이블에서 부서코드, 그룹별 급여의 합계, 그룹별 급여의 평균(정수처리), 인원수를 조회하고, 부서코드 순으로 정렬 (그룹은 부서)
select dept_code, sum(salary), floor(avg(salary)), count(*) from employee group by dept_code order by 1;

--3. [EMPLOYEE] 테이블에서 부서코드, 보너스를 지급받는 사원 수를 조회하고 부서코드 순으로 정렬
select dept_code 부서코드 , count(*) 사원수 from employee where bonus is not null group by dept_code order by 1;

--4. EMPLOYEE 테이블에서 직급이 J1을 제외하고, 직급, 직급별 사원수 및 평균급여를 출력하세요.
select * from employee;
select job_code 직급, floor(avg(salary))평균급여 ,count(*)사원수
from employee
where job_code not in ('J1')
group by job_code;

--5. 부서내 성별 인원수를 구하세요.
--다음과 같이 출력 되도록 만드시오
--ex)
--D1	남	2
--D1	여	1

select * from employee;

select dept_code , decode(substr(emp_no,8,1),'1','남','2','여'),count(*)
from employee
group by dept_code , decode(substr(emp_no,8,1),'1','남','2','여')
order by dept_code;


--급여 평균이 3000000원 이상인 그룹에 대한 급여 평균값 검색(dept_code)

select dept_code , floor(avg(salary)) from employee
group by dept_code
having floor(avg(salary)) >= 3000000;

--그룹이 된 다음에는 having을 써야됨!!!!!
--ROLLUP , CUBE 나중에 볼 필요있으면 볼 것

select * from job;
select emp_name, job_code from employee;

-------------------------------
--오라클 전용구문

select e.emp_name, j.job_code, j.job_name from employee e, job j
where e.job_code = j.job_code;
--------------------------------------------------------------------------
--ANSI 구문
--두 테이블에서 사용하는 공통값 컬럼명이 같은경우
select * from employee;
select * from department;
select * from job;


select emp_name, job_code, job_name
from employee
join job using(job_code);
-----------------------------------------------------
--두 테이블에서 사용하는 공통값 컬럼명이 다른경우

select emp_name, dept_code, dept_title
from employee
join department on(dept_code = dept_id);

select emp_name, dept_code, dept_title
from employee
left join department on(dept_code = dept_id); -- 왼쪽 테이블은 다 나와야됨

select emp_name, dept_code, dept_title
from employee
right join department on(dept_code = dept_id); -- 오른쪽 테이블은 다 나와야됨


select emp_name, dept_code, dept_title
from employee
full join department on(dept_code = dept_id); -- null 다나옴

-----------CROSS JOIN
select emp_name, dept_title from employee cross join department;

select * from employee;
--자신을 관리하고 있는 사원의 이름을 출력

select manager_id from employee where emp_id = 201;
select emp_name from employee where emp_id = manager_id;

select * from employee;

select e1.emp_name 사원이름, e2.emp_name 관리자이름
from employee e1
join employee e2 on(e1.manager_id = e2.emp_id)
where e1.emp_name='송종기';

--자신이 관리하고 있는 사원의 이름, 급여 및 직급이 어떻게 되는지 검색하여라
--E1 매니저 테이블, E2는 사원 테이블 초점으로 보고 있을때

select * from employee;

select e1.emp_name 매니저이름, e2.emp_name 사원이름, e2.salary 월급,
e2.job_code 직급코드
from employee e1
join employee e2 on (e1.emp_id = e2.manager_id)
order by 1;

select * from employee;
select * from department;
select * from LOCATION;

select emp_id, emp_name, dept_code, dept_title
from employee
join department on (dept_code = dept_id);

select dept_id, local_name from department
join location on(location_id = local_code);



select emp_id, emp_name, dept_code, dept_title, local_name
from employee
join department on (dept_code = dept_id)
join location on (location_id = local_code);



--각 사원들의 이름, 나이, 부서명, 직책명을 출력
--(부서코드, 직책코드 x)

select * from EMPLOYEE; 
select * from DEPARTMENT;
select * from job;

select emp_name, extract(year from sysdate)-(1900+substr(emp_no,1,2))+1 as 나이,
NVL(dept_title,'부서없음') as 부서명, job_name as 직책명
from employee
left join department on (dept_code = dept_id)
join job using (job_code)
order by 1;

---

(2)

--1.2020년 12월 25일이 무슨 요일인지 조회하시오.

select to_char(to_date('20201225','yyyymmdd'),'day') from dual;


--2.주민번호가 1970년대 생이면서 성별이 여자이고, 성이 전씨인 직원들의 사원명, 주민번호
--부서명 직급명을 조회하시오!!!

select emp_name, emp_no, dept_title, job_name
from employee
join department on (dept_code = dept_id)
join job using (job_code)
where substr(emp_no,1,1) = 7 and substr(emp_no,8,1) = 2 and emp_name like '전%';

--3, 이름에 '형'자가 들어가는 직원들의 사번, 사원명, 부서명을 조회하시오!!

select emp_id , emp_name, dept_title
from employee
join department on (dept_code = dept_id)
where emp_name like '%형%';


--4. 해외영업부에 근무하는 사원명, 직급명, 부서코드, 부서명을 조회하시오!!

select emp_name 사원명, job_name 사원명, dept_code 부서코드, dept_title 부서명
from employee
join job using (job_code)
join department on (dept_code = dept_id)
where dept_title like '해외영업%부';

--5. 보너스 포인트를 받는 직원들의 사원명, 보너스포인트, 부서명, 근무지역명을 조회하시오.

select emp_name, bonus, dept_title, local_name
from employee
left join department on (dept_code = dept_id)
left join location on (location_id = local_code)
where bonus is not null;

--6. 부서코드가 D2 인 직원들의 사원명, 직급명, 부서명, 근무지역명을 조회하시오.

select emp_name, job_name, dept_title, local_name
from employee
join department on (dept_id = dept_code)
join job using (job_code)
join location on (local_code = location_id)
where dept_code = 'D2';

--7. 급여등급테이블의 최대급여(MAX_SAL) - 50만원 보다 많이 받는 직원들의
--사원명, 직급명, 급여, 연봉을 조회하시오.
--(사원테이블과 급여등급테이블을 SAL_LEVEL컬럼기준으로 조인할 것)

select emp_name 사원명, job_name 직급명, salary 급여, salary*12 연봉, max_sal 최대급여
from employee
join job using (job_code)
join sal_grade using (sal_level)
where salary > max_sal - 500000;

--8. 한국(KO)과 일본(JP)에 근무하는 직원들의 사원명, 부서명, 지역명, 국가명을 조회하시오.

select emp_name 사원명, dept_title 부서명, local_name, national_name
from employee
join department on (dept_code = dept_id)
join location on (location_id = local_code)
join national using (national_code)
where national_name = '한국' or national_name = '일본';

--9. 같은부서에 근무하는 직원들의 사원명, 부서명, 동료이름을 조회하시오! self join 사용

select e1.emp_name 사원명, dept_title 부서명, e2.emp_name 동료이름
from employee e1
join department on (dept_id = dept_code)
join employee e2 using(dept_code)
where e1.emp_name != e2.emp_name
order by 1;

--10. 보너스포인트가 없는 직원들 중에서 직급이 차장과 사원인 직원들의 사원명, 직급명, 급여를
--조회하시오. (단, join과 in 사용할 것)
---------------------------------------------------------------------다시
select emp_name 사원명, job_name 직급명, salary 급여
from employee
join job using (job_code)
where bonus is null and job_name in('차장' ,'사원');

--11. 재직중인 직원과 퇴사한 직원의 수를 조회하시오
select * from employee;
select case ent_yn when 'Y' then '재직' when 'N' then '퇴사' end ,count(*)
from employee
group by ent_yn;

---

(3)

--1)부서코드가 D5인사람의 EMP_ID, EMP_NAME, DEPT_CODE , SALARY

--2)SALARY가 300만원보다 큰사람의 EMP_ID, EMP_NAME, DEPT_CODE, SALARY

--MINUS 는 'D5'를 제외하는 것!

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY > 3000000
MINUS
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5';

---

(4)

--전지연 직원의 관리자 이름을 출력


select * from employee;

select manager_id from employee where emp_name = '전지연';

select emp_name from employee where emp_id = 214;

select emp_name 
from employee
where emp_id=(select manager_id from employee where emp_name = '전지연');

--전 직원의 평균급여보다 많은 급여를 받는 직원의 사번, 이름, 직급코드, 급여 출력

select * from employee;

select emp_id , emp_name, job_code, salary from employee
where salary >  (select avg(salary) from employee);

---

(5)

--실습문제

--1. 윤은해와 급여가 같은 사원들을 검색해서, 사원번호, 사원이름 급여를 출력하라.
--단, 윤은해는 출력되면 안됨

select emp_id , emp_name, salary from employee where salary
=  (select salary from employee where emp_name = '윤은해') and emp_name !='윤은해';

--2. employee 테이블에서 기본급여가 제일 많은 사람과 제일 적은 사람의 정보를 사번, 사원명, 기본급여를 나타내세요!!

select emp_id, emp_name, salary from employee where salary
= (select max(salary) from employee) or salary = (select min(salary) from employee);


--3. D1, D2 부서에 근무하는 사원들 중에서 기본급여가 D5 부서 직원들의 '평균월급'보다 많은 사람들만
--부서번호, 사원번호, 사원명, 월급을 나타내세요!

select dept_code, emp_id, emp_name, salary from employee
where dept_code in ('D1', 'D2') and salary > (select avg(salary) from employee where dept_code in('D5'));

select avg(salary) from employee where dept_code in('D5');



select rownum, employee.* from employee;
-- 게시판 페이징처리

 

---

(6) 

select * from member;
create table member(
    member_id varchar2(20),
    member_pw varchar2(20),
    member_name varchar2(20)
    );
    
    comment on column member.member_id is'회원아이디';
    comment on column member.member_pw is'비밀번호';
    comment on column member.member_name is'회원이름';


create table user_nocons(
user_no number,
user_id varchar2(20),
user_pwd varchar2(30),
user_name varchar2(20),
gender varchar2(30)
);
insert into user_nocons values
(1, 'user01', 'pass01','홍길동','남');

select * from user_nocons;
insert into user_nocons values
(2,null,null,null,null);

create table user_notnull(
user_no number not null,
user_id varchar2(20) not null,
user_pwd varchar2(30) not null,
user_name varchar2 (20),
gender varchar2(30)
);

select * from USER_NOTNULL;

insert into user_notnull values
(1,'user01','pass01','홍길동','남자');
insert into user_notnull values
(2,null,null,null,null);

drop table user_unique;
create table user_unique(
    user_no number,
    user_id varchar2(20),
    user_pwd varchar2(30) not null,
    user_name varchar2(20),
    gender varchar2(20),
    unique(user_id)
);

select * from user_unique;
insert into user_unique values(1,'user01','pass01','홍길동','남');
insert into user_unique values(2,'user02','pass01','임지안','남');
insert into user_unique values(3,null,'pass01','박상철','남');



create table user_primarykey(
    user_no number primary key,
    user_id varchar2(20) unique,
    user_pwd varchar2(30) not null,
    user_name varchar2(20),
    gender varchar2(10)
--  primary key(user_no) 여기에 써줘도 됨! 2번째줄 unique 빼고  
);


insert into user_primarykey values
(1,'user01','pass01','임지안','남');
insert into user_primarykey values
(2,'user02','pass01','임지안2','남');

select * from user_primarykey where user_no = 1;



--쇼핑몰 회원테이블
--구매테이블

create table shop_member(
    member_id varchar2(20) primary key,
    member_pw varchar2(30) not null,
    member_name varchar2(20),
    gender varchar2(20),
    phone char(13)
);

insert into shop_member values('user01','pass01','임지안1','남','010-8910-6879');
insert into shop_member values('user02','pass02','임지안2','남','010-8910-6879');
insert into shop_member values('user03','pass03','임지안3','남','010-8910-6879');
insert into shop_member values('user04','pass04','임지안4','남','010-8910-6879');
insert into shop_member values('user05','pass05','임지안5','남','010-8910-6879');

select * from shop_member;

create table shop_buy2(
    buy_no number primary key,
    user_id varchar2(20),
    product_name varchar2(50),
    foreign key (user_id) references shop_member(member_id) on delete set null  --on delete cascade
    -- 참조하는 컬럼은 반드시 primary key이거나 , unique 조건을 만족 시켜야됨!!!!
);

select * from shop_buy2;
insert into shop_buy2 values(1, 'user01','컨디션');
insert into shop_buy2 values(2, 'user02','컨디션');
insert into shop_buy2 values(3, 'user03','컨디션');
insert into shop_buy2 values(4, 'user04','컨디션');


delete from shop_member where member_id = 'user01';


select * from shop_buy3;





--CHECK : 해당 컬럼에 입력되는 값을 체크
--설정된 값 이외는 에러발생

create table user_check(
    user_no number primary key,
    user_id varchar2(20) unique,
    user_pw varchar2(30) not null,
    user_name varchar2(20),
    gender varchar2(10) check (gender in('남','여')) -- 남, 여 로만 넣을수 있음!
);

insert into user_check values
(1,'user01','pass01','이윤수','남');
insert into user_check values
(2,'user02','pass01','이윤수','여');

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

--직원사번,이름,급여,부서이름,직급
select * from department;

create table employee_copy
as
select emp_id , emp_name, salary, dept_title, job_name
from employee
left join department on(dept_id = dept_code)
left join job using(job_code);

select * from employee_copy;
-- 컬럼명, 데이터타입, 값 다 복사되는데!!!!!!!!!!!
-- 제약조건은 NOT NULL만 복사됨 -> ALTER 를 이용해서 수정을 해야됨!!!!!!!!!!!!!

-------------------------------------------------------------------------------
create table employee_copy2
as
select emp_id , emp_name, salary, dept_title, job_name
from employee
left join department on(dept_id = dept_code)
left join job using(job_code) where 1 = 0;
select * from employee_copy2;

--default : 기본값 적용
create table test_tbl(
    data1 number default 0,
    data2 number
);
insert into test_tbl  values(default,1);
select * from test_tbl;


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

create table expert_tbl(
    
    member_code number primary key,
    member_id varchar2(20) unique,
    member_pwd char(20) not null,
    member_name nchar(10) not null,
    member_addr char(50) not null,
    gender varchar2(5) check (gender in('남','여')),
    phone varchar2(50) default 000-0000-0000  -- '000-0000-000' 이렇게 해야됨!!!!   
);

create table A3Company_TBL(
    member_code number,
    skill nchar(10),
    
    foreign key (member_code) references expert_tbl(member_code) 
    on delete cascade
);

insert into expert_tbl values
(1,'user01','pwd01', '지안', '서울', '남', default);

insert into expert_tbl values
(2,'user02','pwd02', '지안', '서울', '여', default);

select * from expert_tbl;


insert into A3Company_TBL values
(1, '좋음');

insert into A3Company_TBL values
(2, '매우좋음');
select * from A3COMPANY_TBL;

delete from a3company_tbl where member_code = '1';

delete from expert_tbl where member_id = 'user01';

select * from expert_tbl;
select * from a3company_tbl;

CREATE TABLE EMP_01(
    EMP_ID NUMBER,
    EMP_NAME VARCHAR2(30),
    DEPT_TITLE VARCHAR2(20)
);
INSERT INTO EMP_01 VALUES (111,'이윤수','D20');

INSERT INTO EMP_01 (EMP_ID, EMP_NAME)
VALUES(222,'이윤수');

SELECT * FROM EMP_01;

------------------------------------------------------------------------------
SELECT * FROM DEPARTMENT;

INSERT INTO EMP_01 (SELECT EMP_ID, EMP_NAME, DEPT_TITLE 
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID));
SELECT * FROM EMP_01;

SELECT EMP_ID, EMP_NAME, DEPT_TITLE 
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);

--------------------------------------------------------------------------------
--@UPDATE : 테이블에 기록된 컬럼의 값을 수정하는 구문
-- 업데이트 후 테이블의 행의 갯수 변화 없음!!

CREATE TABLE DEPT_COPY
AS SELECT * FROM DEPARTMENT;
SELECT * FROM DEPT_COPY;

UPDATE DEPT_COPY SET DEPT_TITLE = '전략기획팀'
WHERE DEPT_ID = 'D9';

SELECT * FROM DEPT_COPY;
--------------------------------------------------------------------------------
CREATE TABLE EMP_SALARY
AS
SELECT EMP_ID,EMP_NAME, DEPT_CODE,SALARY,BONUS
FROM EMPLOYEE;
SELECT* FROM EMP_SALARY;

SELECT SALARY,BONUS FROM EMP_SALARY WHERE EMP_ID = 200;
UPDATE EMP_SALARY
SET
SALARY = (SELECT SALARY FROM EMP_SALARY WHERE EMP_ID = 200),
BONUS = (SELECT BONUS FROM EMP_SALARY WHERE EMP_ID = 200)
WHERE EMP_ID = 201;

SELECT* FROM EMP_SALARY;

DELETE FROM EMP_SALARY WHERE EMP_ID = 200;

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--런닝맨실습----------------------------------------------------------------------
CREATE TABLE RUNNINGMAN (

    NO NUMBER PRIMARY KEY,
    ID VARCHAR2(20) UNIQUE,
    PASS VARCHAR2(20) NOT NULL,
    NAME VARCHAR2(20) NOT NULL,
    NICKNAME VARCHAR2(20) UNIQUE,
    GENDER CHAR(1),
    TEL VARCHAR(20) default '010-0000-0000',
    ADDRESS VARCHAR2(90)
);

INSERT INTO RUNNINGMAN values
(1,'muhan',1234,'유재석','못생긴형','M','010-4958-2333','압구정');
INSERT INTO RUNNINGMAN values
(2,'bignose',4444,'지석진','지는해','M',default,'서울');
INSERT INTO RUNNINGMAN values
(3,'tiger',6658,'김종국','안꼬','M','010-6674-8986','안양');
INSERT INTO RUNNINGMAN values
(4,'haha',5356,'하하','신분세탁','M','010-8997-1122','서래마을');
INSERT INTO RUNNINGMAN values
(5,'ace',5555,'송지효','천성임','F',default,'서울시');
INSERT INTO RUNNINGMAN values
(6,'girrafe',6658,'이광수','중기친구','M','010-2378-9991','남양주');
INSERT INTO RUNNINGMAN values
(7,'ssapssap',5356,'양세찬','김종국팬','M','010-1215-7777','동두천');
INSERT INTO RUNNINGMAN values
(8,'doli',5555,'전소민','전두릅','F',default,'일산');

select * from runningman;

--중기친구 닉네임가진애 비밀번호를 9999로 변경
update runningman set pass = '9999'
where nickname = '중기친구';

--전화번호가 기본값으로 되어있는 사원들의 tel정보를 '없음'으로
--단,1번의 쿼리문으로 모두변경

update runningman set tel = '없음'
where tel = '010-0000-0000';
select * from runningman;

--런닝맨 멤버 중 유재석이 하차했습니다.
--유재석을 테이블에서 삭제하여라!!

delete from runningman where no = 1;
select * from runningman;

--유재석이 하차한 뒤로 시청률이 떨어져서 프로그램이 폐지되었습니다.
--TRUNCATE를 통해서 모든 멤버의 정보를 초기화 하여라.

TRUNCATE TABLE RUNNINGMAN;
select * from runningman;
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

CREATE TABLE MEMBER_TBL(
    USER_NO NUMBER PRIMARY KEY,
    USER_ID VARCHAR2(20),
    USER_PWD VARCHAR2(20)
);

DESC MEMBER_TBL;
SELECT * FROM MEMBER_TBL;
-- 이미 생성된 테이블에 새로운 컬럼 추가 (USER_NAME)
ALTER TABLE MEMBER_TBL ADD (USER_NAME VARCHAR2(20));
ALTER TABLE MEMBER_TBL ADD (USER_AGE NUMBER DEFAULT 0 );
INSERT INTO MEMBER_TBL VALUES(
1,'TEST01','1234','이윤수',DEFAULT);

SELECT * FROM MEMBER_TBL;

ALTER TABLE MEMBER_TBL
ADD CONSTRAINT MTBL_ID_UNQ UNIQUE (USER_ID);

ALTER TABLE MEMBER_TBL 
MODIFY USER_PWD CONSTRAINT MTBL_PWD_NN NOT NULL;

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'MEMBER_TBL';

--제약조건 삭제하기
--1. 제약조건 이름검색
ALTER TABLE MEMBER_TBL
DROP CONSTRAINT MTBL_PWD_NN; -- 이름써야됨!!

DESC MEMBER_TBL;
--컬럼명 수정하기

ALTER TABLE MEMBER_TBL 
RENAME COLUMN USER_PWD TO PASSWORD;

ALTER TABLE MEMBER_TBL DROP COLUMN USER_AGE;
ALTER TABLE MEMBER_TBL DROP COLUMN USER_NO CASCADE CONSTRAINTS; -- 


ALTER TABLE MEMBER_TBL RENAME TO MTBL;
SELECT * FROM MTBL;

DROP TABLE MTBL;

CREATE TABLE COFFEE(
    NAME VARCHAR2(20),
    PRICE NUMBER
);

INSERT INTO COFFEE VALUES ('아메리카노' , 1800);
INSERT INTO COFFEE VALUES ('카페모카' , 2400);
INSERT INTO COFFEE VALUES ('마키아또' , 5000);
INSERT INTO COFFEE VALUES ('아이스아메' , 1800);
INSERT INTO COFFEE VALUES ('뜨거운아메' , 1800);
SAVEPOINT SP1;
ROLLBACK;
ROLLBACK TO SP1;
COMMIT;
SELECT *FROM COFFEE;


SELECT * FROM USER_TABLES; -- 자신이 소유한 객체 정보 조회
SELECT * FROM USER_CONS_COLUMNS; -- 자신이 소유한 테이블의 컬럼정보
SELECT * FROM USER_CONSTRAINTS; -- 자신이 소유한 테이블의 제약조건
SELECT * FROM USER_COL_COMMENTS;

--------------------------------------------------------------------------------
CREATE OR REPLACE VIEW EMP_VIEW --  생성하는 뷰가 존재하지 않으면 새로 생성, 이미 존재하면 뷰를 갱신
AS
SELECT EMP_NO, EMP_NAME, EMAIL, PHONE FROM EMPLOYEE;
SELECT * FROM EMP_VIEW;
--------------------------------------------------------------------------------

CREATE SEQUENCE SEQ_EMP_ID
START WITH 1
INCREMENT BY 1
MAXVALUE 10
NOCYCLE
NOCACHE;
--------------
--SEQUENCE이름.NEXTVAL : 시퀀스값 증가
--SEQUENCE이름.CURRVAL : 현재 시퀀스값 확인

SELECT SEQ_EMP_ID.NEXTVAL FROM DUAL; -- 10넘어가면 ERROR
SELECT SEQ_EMP_ID.CURRVAL FROM DUAL;
--------------------------------------------------------------------------------
CREATE TABLE SEQ_TEST(
    NO NUMBER PRIMARY KEY,
    NAME VARCHAR2(20) NOT NULL,
    AGE NUMBER NOT NULL
);

CREATE SEQUENCE SEQ_TEST_NO
START WITH 1
INCREMENT BY 1
MAXVALUE 100
NOCYCLE
NOCACHE;
INSERT INTO SEQ_TEST VALUES(SEQ_TEST_NO.NEXTVAL,'임지안',26);

SELECT * FROM SEQ_TEST;
--수정 --> NEXT 할때마다 이제 10씩 올라감!!!!
----BUT!!!! START WITH 값은 변경 불가
----START WITH 값은 삭제 후 다시 생성!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
----(PRIMARY KEY 만들때 많이 사용)
ALTER SEQUENCE SEQ_TEST_NO 
INCREMENT BY 10
MAXVALUE 400;

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

CREATE TABLE KH_MEMBER(
    MEMBER_ID NUMBER,
    MEMBER_NAME VARCHAR2(20),
    MEMBER_AGE NUMBER,
    MEMBER_JOIN_COM NUMBER
);\
CREATE SEQUENCE KH_MEMBER_ID
START WITH 500
INCREMENT BY 10
MAXVALUE 10000
NOCYCLE
NOCACHE;

CREATE SEQUENCE KH_MEMBER_JOIN_COM
START WITH 1
INCREMENT BY 1
MAXVALUE 1000
NOCYCLE
NOCACHE;

INSERT INTO KH_MEMBER VALUES(KH_MEMBER_ID.NEXTVAL, '임지안1' , 20, KH_MEMBER_JOIN_COM.NEXTVAL);
                                    --'임지안1'||KH.MEMBER_JOIN_COM.NEXTVAL 하면 붙여나옴
SELECT * FROM KH_MEMBER;
--------------------------------------------------------------------------------

CREATE INDEX EMP_IDX ON EMPLOYEE(EMP_NAME, EMP_NO, HIRE_DATE);
SELECT EMP_NAME, EMP_NO, HIRE_DATE FROM EMPLOYEE;
SELECT * FROM EMPLOYEE;
DROP INDEX EMP_IDX;

CREATE SYNONYM EMP FOR EMPLOYEE;
SELECT * FROM EMP;
-----------------------------------지금 만든 동의어는 KH계정에서만 사용이 가능!!