오라클 문법 연습 (210223)

CREATE TABLE DEPT_TCL
    AS SELECT *
        FROM DEPT;
       
SELECT * FROM DEPT_TCL;

INSERT INTO DEPT_TCL VALUES(50, 'DATABASE', 'SEOUL');

UPDATE DEPT_TCL SET LOC = 'BUSAN' WHERE DEPTNO = 40;

DELETE FROM DEPT_TCL WHERE DNAME = 'RESEARCH';

SELECT * FROM DEPT_TCL;

ROLLBACK;

SELECT * FROM DEPT_TCL;

INSERT INTO DEPT_TCL VALUES(50, 'NETWORK', 'SEOUL');

UPDATE DEPT_TCL SET LOC = 'BUSAN' WHERE DEPTNO = 20;

DELETE FROM DEPT_TCL WHERE DEPTNO = 40;

SELECT * FROM DEPT_TCL;

COMMIT;

SELECT * FROM DEPT;

CREATE TABLE EMP_DDL(
    EMPNO       NUMBER(4),
    ENAME       VARCHAR(10),
    JOB         VARCHAR2(9),
    MGR         NUMBER(4),
    HIREDATE    DATE,
    SAL         NUMBER(7,2),
    COMM        NUMBER(7,2),
    DEPTNO      NUMBER(2)
);

DESC EMP_DDL;

CREATE TABLE DEPT_DDL
    AS SELECT * FROM DEPT;
    
DESC DEPT_DDL;

SELECT * FROM DEPT_DDL;

CREATE TABLE EMP_DDL_30
    AS SELECT *
        FROM EMP
       WHERE DEPTNO = 30;
       
      
SELECT * FROM EMP_DDL_30;

CREATE TABLE EMPDEMPT_DDL
    AS SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE,
              E.SAL, E.COMM, D.DEPTNO, D.DNAME, D.LOC
        FROM EMP E, DEPT D
       WHERE 1 <> 1;
      
SELECT * FROM EMPDEMPT_DDL;

CREATE TABLE EMP_ALTER
    AS SELECT * FROM EMP;
   
SELECT * FROM EMP_ALTER;

ALTER TABLE EMP_ALTER
    ADD HP VARCHAR(20);
   
SELECT * FROM EMP_ALTER;

ALTER TABLE EMP_ALTER
    RENAME COLUMN HP TO TEL;
   
SELECT * FROM EMP_ALTER;

ALTER TABLE EMP_ALTER
    MODIFY EMPNO NUMBER(5);
    
DESC EMP_ALTER;

ALTER TABLE EMP_ALTER
    DROP COLUMN TEL;
  
SELECT * FROM EMP_ALTER;

RENAME EMP_ALTER TO EMP_RENAME;

DESC EMP_RENAME;

SELECT * FROM EMP_RENAME;

TRUNCATE TABLE EMP_RENAME;

SELECT * FROM EMP_RENAME;

DROP TABLE EMP_RENAME;

DESC EMP_RENAME;

CREATE TABLE EMP_HW(
    EMPNO       NUMBER(4),
    ENAME       VARCHAR(10),
    JOB         VARCHAR(9),
    MGR         NUMBER(4),
    HIREDATE    DATE,
    SAL         NUMBER(7,2),
    COMM        NUMBER(7,2),
    DEPTNO      NUMBER(2)
);

DESC EMP_HW;

ALTER TABLE EMP_HW
    ADD BIGO VARCHAR(20);
   
DESC EMP_HW;

ALTER TABLE EMP_HW
    MODIFY BIGO VARCHAR(30);
   
DESC EMP_HW;

ALTER TABLE EMP_HW
    RENAME COLUMN BIGO TO REMARK;
   
SELECT * FROM EMP_HW;

SELECT * FROM EMP;

INSERT INTO EMP_HW
    SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, NULL
        FROM EMP;
       
SELECT * FROM EMP_HW;

DROP TABLE EMP_HW;

SELECT * FROM DICTIONARY;

SELECT TABLE_NAME
    FROM USER_TABLES;

SELECT OWNER, TABLE_NAME
    FROM ALL_TABLES;
   
SELECT * FROM DBA_TABLES;

SELECT * FROM USER_INDEXES;

SELECT * FROM USER_IND_COLUMNS;

CREATE INDEX IDX_EMP_SAL
    ON EMP(SAL);
    
   
SELECT * FROM USER_IND_COLUMNS;

DROP INDEX IDX_EMP_SAL;

SELECT * FROM USER_IND_COLUMNS;

- 트랜잭션제어와 섹션

- 데이터정의어

- 객체