[SQL] 10. DDL(2)

# AS SUBQUERY 

CREATE TABLE table_name AS SUBQUERY;

//서브쿼리로 테이블 생성, 컬럼명 변경
CREATE TABLE TABLE_SUBQUERY2 (EID, ENAME, SALARY, DNAME, JTITLE)
AS 	SELECT EMP_ID, EMP_NAME, SALARY, DEPT_NAME, JOB_TITLE
	FROM	EMPLOYEE
	LEFT JOIN	DEPARTMENT USING(DEPT_ID)
	LEFT JOIN	JOB USING(JOB_ID);

SELECT	*	FROM	TABLE_SUBQUERY2;

//서브쿼리로 테이블 생성시 제약을 걸어준다.
CREATE TABLE TABLE_SUBQUERY3
( EID PRIMARY KEY, 
  ENAME, 
  SALARY CHECK (SALARY > 2000000), 
  DNAME, 
  JTITLE NOT NULL)
AS 	SELECT EMP_ID, EMP_NAME, SALARY, DEPT_NAME, JOB_TITLE
	FROM	EMPLOYEE
	LEFT JOIN	DEPARTMENT USING(DEPT_ID)
	LEFT JOIN	JOB USING(JOB_ID);
//오류: SALARY가 150만원인 경우가 존재, JTITLE에 NULL값도 존재

---

# 테이블 수정

//칼럼 추가
SELECT	*	FROM	DEPARTMENT;

ALTER TABLE DEPARTMENT
ADD MGR_ID CHAR(3) ;


//칼럼의 제약조건 수정
CREATE TABLE EMP3 AS SELECT * FROM EMPLOYEE;
ALTER TABLE EMP3
ADD PRIMARY KEY(EMP_ID)
ADD UNIQUE(EMP_NO)
MODIFY HIRE_DATE NOT NULL;

SELECT * FROM EMP3;