create, alter, select table using subquery in oracle - 서브 쿼리를 이용한 테이블 생성

안녕하세요

이번 시간에는 오라클(oracle)에서 서브쿼리(subquery)를 활용하여 테이블을 생성하고

조회하고 삽입하는 예제를 올려봅니다.

잘 봐주세요~!



서브쿼리를 이용한 테이블 생성

CREATE TABLE 테이블명(컬럼명1, 컬럼명2, ...)
AS SUBQUERY

[1] 사원테이블에서 30번 부서에 근무하는 사원의 정보만 추출하여 EMP_30 테이블을 생성하세요
단 일은 사번, 이름, 업무, 입사일, 급여, 보너스를 포함시키세요

CREATE TABLE EMP_30(EMPNO, ENAME, JOB, HIREDATE, SAL, COMM)
AS
SELECT EMPNO, ENAME, JOB, HIREDATE, SAL, COMM
FROM EMP WHERE DEPTNO=30;

SELECT * FROM EMP_30;

[2] EMP에서 부서별로 인원수, 평균급여, 급여의 합, 최소급여, 최대급여를 포함하는 EMP_SUM 테이블을 생성하세요

CREATE TABLE EMP_SUM(DEPTNO, CNT, AVG_SAL, SUM_SAL, MIN_SAL, MAX_SAL)
AS
SELECT DEPTNO, COUNT(ENAME), ROUND(AVG(SAL),2), SUM(SAL), MIN(SAL), MAX(SAL) FROM EMP GROUP BY DEPTNO;

SELECT * FROM EMP_SUM;

[3] EMP에서 사번, 이름, 부서번호, 업무만 포함하는 EMP_TEMP 테이블을 생성하는데, 데이터는 포함하지 않고 구조만 생성하세요

CREATE TABLE EMP_TEMP2(EMPNO, ENAME, DEPTNO, JOB)
AS
SELECT EMPNO, ENAME, DEPTNO, JOB FROM EMP WHERE 1=2; -- 조건을 말도 안되게 주면 구조만 생성
SELECT * FROM EMP_TEMP2;

테이블에 컬럼 추가 및 변경, 삭제

-컬럼 추가
ALTER TABLE 테이블명 ADD(추가할 컬럼정보 [DEFAULT 기본값])
-컬럼 변경
ALTER TABLE 테이블명 MODIFY(변경할 컬럼정보 [DEFAULT 기본값])
-컬럼 삭제
ALTER TABLE 테이블명 DROP COLUMN(삭제할 컬럼명)
-컬럼명 변경
ALTER TABLE 테이블명 RENAME COLUMN 예전컬럼명 TO 새컬럼명

CREATE TABLE DEMO(
NO NUMBER);

SELECT * FROM DEMO;

[3]DEMO 테이블에 NAME 컬럼을 추가하세요 VARCHAR2(10) 유형으로
  DEMO 테이블에 POINT 컬럼을 추가하세요 NUMBER(3)
  ALTER TABLE DEMO ADD NAME VARCHAR2(10);
  ALTER TABLE DEMO ADD POINT NUMBER(3);

  DESC DEMO;

[4]DEMO 테이블에 생성된 POINT 컬럼의 자료형을 CHAR(3)으로 변경하세요
 ALTER TABLE DEMO MODIFY POINT CHAR(3);

[5]DEMO 테이블의 POINT 컬럼을 삭제하세요
ALTER TABLE DEMO DROP POINT;

ALTER TABLE DEMO ADD POINT NUMBER(3) NOT NULL;

INSERT INTO DEMO VALUES (1,'홍길동',100);

INTRO 컬럼을 추가하세요 VARCHAR2(100) NOT NULL로 한다면 => 오류가 발생하면서 추가가 안됨
NOT NULL인 열을 추가하기 위해서는 테이블이 비어있어야 함
ALTER TABLE DEMO ADD INTRO VARCHAR2(100);
DESC DEMO;

COMMIT;

NAME이라는 컬럼을 USERNAME이라고 변경
ALTER TABLE DEMO RENAME COLUMN NAME TO USERNAME;

제약조건 추가=> NO 컬럼에 PK 제약조건 주기
ALTER TABLE DEMO ADD CONSTRAINT DEMO_NO_PK PRIMARY KEY(NO);

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS
FROM user_constraints where table_name='DEMO';

INSERT INTO DEMO VALUES(2, '김길동', 300, '자기소개');

제약조건 비활성화
ALTER TABLE 테이블명 DISABLE CONSTRAINT 제약조건명 [CASCADE]
ALTER TABLE DEMO DISABLE CONSTRAINT DEMO_NO_PK; -- STATUS : DISABLEED로 변경

-- 프라이머리키 제약조건이 비 활성화 됨 2번이 계속 동일하게 들어감
ROLLBACK;

제약조건 활성화
ALTER TABLE DEMO ENABLE CONSTRAINT DEMO_NO_PK;



객체이름 변경(테이블명 변경)
RENAME OLD_NAME TO NEW NAME;

EMP_30 테이블명을 EMP_TEMP30으로 변경하세요
RENAME EMP_30 TO EMP_TEMP30;
SELECT * FROM EMP_TEMP30;

테이블 삭제
DROP TABLE 테이블명;

DROP TABLE EMP_30;
ROLLBACK;

DROP TABLE EMP2;

EMP와 동일한 구조의 테이블 EMP2를 만드세요(사번,이름,급여,입사일,부서번호)
(구조만 복사)

CREATE TABLE EMP2(EMPNO,ENAME,SAL,HIREDATE,DEPTNO)
AS
SELECT EMPNO,ENAME,SAL,HIREDATE,DEPTNO FROM EMP WHERE 1=2;

SELECT * FROM EMP2;

INSERT INTO EMP2(EMPNO, ENAME, SAL)
VALUES(1122, 'SCOTT', 3000);

INSERT INTO EMP2
VALUES(1123, 'JAMES', 4000, SYSDATE, 20);

서브쿼리를 이용한 INSERT문

INSERT INTO EMP2
SELECT EMPNO, ENAME, SAL, HIREDATE, DEPTNO
FROM EMP WHERE DEPTNO=10;

EMP2에서 7782번 사원의 부서번호를 20번 부서로 수정하고 급여는 500원을 인상해주세요
UPDATE EMP2 SET DEPTNO=20, SAL = SAL+500
WHERE EMPNO=7782;

[5] MEMBER에서 13/9/1 이후 등록한 고객들의 마일리지를 300점씩 올려주세요
UPDATE MEMBER SET MILEAGE = MILEAGE + 300
WHERE REG_DATE > '13/09/01';
ROLLBACK;

[6] 등록된 MEMBER중 이름에 '김' 자가 들어있는 모든 이름을 '최'로 변경하세요
UPDATE MEMBER SET NAME = REPLACE(NAME, '김', '최') WHERE NAME LIKE '김%';
SELECT * FROM MEMBER;
ROLLBACK;

[7] EMP2에서 SCOTT의 부서번호와 등록일을 MILLER와 동일한 부서번호, 등록일로 수정하세요

UPDATE EMP2 SET(DEPTNO, HIREDATE)
= (SELECT DEPTNO, HIREDATE FROM EMP WHERE ENAME='MILLER')
WHERE ENAME='SCOTT';

INSERT INTO EMP2(EMPNO, ENAME) VALUES (7788, 'SCOTT');

COMMIT;
SELECT * FROM EMP2;

UPDATE MEMBER SET ADDR=NAME, JOB=MILEAGE;
SELECT * FROM MEMBER;
ROLLBACK;

EMP에서 사번 7782번 사원을 삭제하세요
DELETE FROM EMP WHERE EMPNO=7782;
ROLLBACK;

EMP에서 입사일자가 83년인 사원의 정보를 삭제하세요
SELECT * FROM EMP;
DELETE FROM EMP
WHERE TO_CHAR(HIREDATE,'YY')='83';