테이블 TABLE - 1

-- 테이블 스페이스 생성
CREATE TABLESPACE TABLESPACE2
DATAFILE 'D:\TMP\TEST_TEST1.DBF' SIZE 10M
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED         -- 추가되는 용량
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
 
-- 테이블 스페이스 수정
-- 테이블 CREATE, DROP, ALTER(수정)
-- 데이터 INSERT, DELETE, UPDATE
ALTER TABLESPACE TABLESPACE2
RENAME TO TEST_TBS_NEW;
 
ALTER DATABASE
DATAFILE 'D:\TMP\TEST_TEST1.DBF' RESIZE 7M;
 
-- 테이블 생성
CREATE TABLE TB_TEST01(
    COL1 VARCHAR2(10),
    COL2 VARCHAR2(10),
    COL3 VARCHAR2(10)
);
 
CREATE TABLE TB_TEST02(
    COL1 VARCHAR2(10),
    COL2 VARCHAR2(10)
)TABLESPACE TABLESPACE1;
 
-- 테이블 복제 : 데이터 포함
CREATE TABLE TB_TEST03
AS
SELECT *
FROM jobs;
 
SELECT * FROM tb_test03;
 
-- 테이블 복제 : 데이터 미포함
CREATE TABLE TB_TEST04
AS
SELECT *
FROM jobs
WHERE 1=2;
 
SELECT * FROM tb_test04;
--------------------------------선택한 컬럼만 가져오기
CREATE TABLE TB_TEST05
AS
SELECT job_id, job_title
FROM jobs;
 
SELECT * FROM tb_test05;
---------------------------------------------------------
 
 
CREATE TABLE TB_TEST05("업무번호", "업무명")
AS
SELECT job_id, job_title
FROM jobs;
 
--------------------------------------------------------------------
 
CREATE TABLE TB_TEST05("부서번호", "총합계", "평균")
AS
SELECT department_id, SUM(salary), AVG(salary) 
FROM employees
GROUP BY department_id;
---------------------------------------------------------------------
 
 
-- 테이블 수정
-- 테이블명 변경
ALTER TABLE TB_TEST04
RENAME 
TO
TB_TEST99;
 
-- 단일 컬럼 추가
ALTER TABLE TB_TEST99
ADD
COL_NEW1 VARCHAR2(30);
 
-- 다중 컬럼 추가
ALTER TABLE TB_TEST99
ADD
(COL_NEW2 NUMBER, COL_NEW3 DATE);
 
-- 단일 컬럼 수정
ALTER TABLE TB_TEST99
MODIFY
COL_NEW1 VARCHAR2(20);
 
-- 다중 컬럼 수정
ALTER TABLE TB_TEST99
MODIFY
(COL_NEW2 VARCHAR2(10), COL_NEW3 NUMBER);
 
-- 컬럼 삭제
ALTER TABLE TB_TEST99
DROP
COLUMN COL_NEW1;
 
ALTER TABLE TB_TEST99
DROP
(COL_NEW2, COL_NEW3);
 
ALTER TABLE TB_TEST99
RENAME
COLUMN
JOB_ID TO JOBNUM;
 
DROP TABLE TB_CHAR;
 
DROP TABLE TB_TEST01;
DROP TABLE TB_TEST02;
DROP TABLE TB_TEST03;
DROP TABLE TB_TEST99;
 
-- 휴지통 비우기
PURGE RECYCLEBIN;
 
CREATE TABLE TB_TEST(DEPTNO, DEPTNAME, MGRID, LOCID)
AS
SELECT *
FROM departments
WHERE 1=2;
 
-- INSERT
INSERT INTO tb_test(DEPTNO, DEPTNAME, MGRID, LOCID)
VALUES(10, '기획부', 100, 120); 
 
INSERT INTO tb_test(DEPTNO, DEPTNAME)
VALUES(20, '관리부');
 
INSERT INTO tb_test
VALUES(30, '전산부', 300, 230);
 
INSERT INTO tb_test         -- not enough values ERROR
VALUES(30, '전산부', 300);
 
INSERT INTO tb_test(DEPTNAME, DEPTNO, LOCID, MGRID)
VALUES('경리부', 40, 210, 150); 
 
INSERT INTO tb_test(DEPTNO, DEPTNAME, MGRID, LOCID)
VALUES('50', '개발부', 250, 110);
 
SELECT * FROM tb_test;
 
-- DELETE
DELETE FROM tb_test
WHERE deptname = '전산부';
 
DELETE FROM tb_test
WHERE mgrid IS NULL;
 
-- UPDATE
UPDATE tb_test
SET MGRID = 120
WHERE DEPTNO = 40;
 
UPDATE tb_test
SET mgrid = 300, locid = 270
WHERE DEPTNAME = '개발부';