[SQL]ROLLBACK,세션,RENAME,MODIFY,INDEX(생성,삭제),VIEW,인라인뷰(WITH절)

/*01.삽입*/
INSERT INTO DEPT_TCL VALUES (50,'DATABASE','SEOUL');

/*02.수정*/
UPDATE DEPT_TCL SET LOC = 'BUSAN' WHERE DEPTNO = 40;

/*03.삭제*/
DELETE FROM DEPT_TCL WHERE DNAME='RESEARCH';
COMMIT;
ROLLBACK;

/*P277 테이블에 있는 데이터 수정하기*/
SELECT * FROM DEPT_TEMP2;

/* UPDATE SET */
UPDATE DEPT_TEMP2
SET LOC='SEOUL';

 

/* 수정작업을 되돌리고 싶을 때 ROLLBACK*/
ROLLBACK;

/* DELETE FROM */
SELECT * FROM EMP_TEMP2;

DELETE FROM EMP_TEMP2
WHERE JOB = 'CLERK';

DROP TABLE EMP_TEMP2;

CREATE TABLE EMP_TEMP2
AS SELECT * FROM EMP

/* 세션(11-7)300P */
DELETE FROM DEPT_TCL WHERE DEPTNO =50;
SELECT * FROM DEPT_TCL;
COMMIT;;

 

/*create*/
CREATE TABLE EMP_DDL(
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_DDL;

/*변경 ALTER*/
CREATE TABLE EMP_ALTER
AS SELECT * FROM EMP;

 

/*컬럼 추가*/
ALTER TABLE EMP_ALTER
ADD HP VARCHAR(20);

 

COMMIT;

 

/* 컬럼 이름 수정 */
ALTER TABLE EMP_ALTER
RENAME COLUMN HP TO TEL;

/* MODIFY 열의 자료형 수정/변경 */
DESC EMP_ALTER;
ALTER TABLE EMP_ALTER
MODIFY EMPNO NUMBER(5);

 

/* 특정 열 삭제 */
ALTER TABLE EMP_ALTER
DROP COLUMN TEL;
SELECT * FROM EMP_ALTER;

 

/*13-10:인덱스 생성*/
CREATE INDEX IDX_EMP_SAL ON EMP(SAL);

/*13-10:인덱스 삭제*/
DROP INDEX IDX_EMP_SAL;

 

/*13-14(340P)뷰 생성*/
CREATE VIEW VW_EMP20
AS(SELECT EMPNO,ENAME,JOB,DEPTNO
FROM EMP
WHERE DEPTNO =20);

/*뷰 확인*/
SELECT * FROM USER_VIEWS;
SELECT * FROM VW_EMP20;

/*뷰 삭제*/
DROP VIEW VW_EMP20;

 

/*인라인 뷰를 사용한 TOP-N SQL문*/
/*ROWNUM을 추가로 조회(344P)*/
SELECT ROWNUM,E.* FROM EMP E ;

 

/*EMP를 SAL열 기준 정렬*/
SELECT ROWNUM,E.* FROM EMP E ORDER BY SAL DESC;

 

/*인라인 뷰 (서브쿼리 사용)*/
SELECT ROWNUM,E.* FROM (SELECT * FROM EMP E ORDER BY SAL DESC)E;

 

/*인라인 뷰 (WITH절 사용)*/
WITH E AS (SELECT * FROM EMP E ORDER BY SAL DESC)
SELECT ROWNUM,E.* FROM E;

 

/*인라인 뷰로 TOP-N 추출(서브쿼리사용)*/
SELECT ROWNUM,E.* FROM(SELECT * FROM EMP E ORDER BY SAL DESC)E WHERE ROWNUM<=3;

 

/*인라인 뷰로 TOP-N 추출(WITH사용)*/
WITH E AS (SELECT * FROM EMP E ORDER BY SAL DESC)
SELECT ROWNUM,E.*
FROM E
WHERE ROWNUM<=3;