SQL INSERT, UPDATE, DELETE & DDL 설명 및 예제

※ INSERT INTO ~ VALUES : 삽입 때 사용
※ CREATE TABLE OOO~ AS : 테이블을 복사해서 새로운 테이블 생성

※ DROP TBLAE OOO : 테이블 삭제하기

 

[예시 1] SYSDATE를 사용해서 날짜정보 입력하기

-- DEPT_TEMP 테이블 생성
CREATE TABLE DEPT_TEMP
    AS SELECT * FROM DEPT;
 
--SYSDATE 사용하여 날짜 정보 입력하기
INSERT INTO EMP_TEMP
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES
(1111, '빈센조', '변호사', 0322, SYSDATE, 12000, 1200, 50);

 

 

※ UPDATE ~ SET : 테이블에 저장된 데이터 수정 시 사용

[예시 2] 서브쿼리 사용, 부서번호 30

-- DEPT_TEMP 테이블 생성
CREATE TABLE DEPT_TEMP2
    AS SELECT * FROM DEPT;
 
--데이터 전체 수정하기
UPDATE INTO EMP_TEMP2
SET LOC = 'ITALY';
 
-- 수정한 내용 되돌리기
ROLLBAK;
 
--일부만 수정하기(서브쿼리 사용, 부서번호 30)
UPDATE DEPT_TEMP2
SET DNAME = (SELECT DNAME FROM DEPT
            WHERE DEPTNO = 30),
    LOC = (SELECT LOC FROM DEPT
            WHERE DEPTNO = 30)
WHERE DEPTNO = 30;

※ DELETE vs DROP
- DELETE :인스턴스 제거(데이터) - 복구 가능
- DROP : 스키마 제거(구조) - 복구 불가능

 

[예시 3] 서브쿼리 사용, 부서번호 50

-- DEPT_TEMP 테이블 생성
CREATE TABLE DEPT_TEMP2
    AS SELECT * FROM DEPT;
 
--데이터 일부 삭제하기
DELETE FROM EMP_TEMP2
WHERE JOB = '마피아';
 
 
 
--데이터 삭제하기 (서브쿼리 사용, 부서번호 30)
DELETE DEPT_TEMP2
WHERE EMPNO IN(SELECT E.EMPNO
                FROM EMP_TEMP2 E, SALGRADE S
                WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
                ANS S.GRADE = 3
                AND DEPTNO = 30);

※ DDL : 명령어 수행 시 바로 영구적으로 반영(commit)
=> ROLLBACK 불가

 

※ CREATE문 : 오라클 데이터베이스 객체 생성, 속성과 속성 제약 정의, 기본키 및 외래키 정의

 

<데이터 타입>

ANSI타입	ORACLE타입	ANSI타입		ORACLE타입
NUMBERIC(P, S) DECIMAL(P, S)	NUMBER(P,S)	DATE TIME/TIMESTAMP		DATE TIMESTAMP
INTEGER INT SAMLLINT	NUMBER(38)	NUMBER		NUMBER(4) NUMBER(6,2) NUMBER NUMBER(*, 5)
FLOAT(8) REAL(D) DOUBLE PRECISION(C)	NUMBER	VARCHAR2 NVARCHAR2 CHAR NCHAR		VARCHAR2(50) NVARCHAR2(50) CHAR(13) NCHAR(13)

[예시  4] CREATE문 전반적인 내용

--1) EMP_DDL 테이블 생성 : 직접 입력
CREATE TABLE EMP_DDL(
EMPNO NUMBER(4).
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4).
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
);
 
---2) EMP_DDL 테이블 생성 : 다른 테이블 복사
CREATE TABLE EMP_DDL
AS SELECT * FROM EMP;
 
--3) 일부 데이터 열 복사
CREATE TABLE EMP_DDL_DEPT
AS SELECT * 
    FROM EMP
    WHERE DEPTNO = 90;
 
--4) 기존 테이블의 열 구조만 복사(EMP ~ LOC)
CREATE TABLE EMP_DDL_COLUMN
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;

 

BOOK, CUSTOMER, ORDERS 테이블 만들기(테이블 먼저 만들고 문제1, 2 풀기)

--테이블 생성(BOOK, CUSTOMER, ORDERS)
CREATE TABLE BOOK(
BOOKID NUMBER(2) PRIMARY KEY,
BOOKNAME VARCHAR2(40),
PUBLISHER VARCHAR2(40),
PRICE NUMBER(8)
);
CREATE TABLE CUSTOMER(
CUSTID NUMBER(2) PRIMARY KEY,
NAME VARCHAR2(40).
ADDRESS VARCHAR2(50),
PHONE VARCHAR2(20)
);
CREATE TABLE ORDERS(
ORDERID NUMBER(2) PRIMARY KEY,
CUSTID NUMBER(2) REFERENCES CUSTOMER(CUSTID),
BOOKID NUMBER(2) REFERENCES BOOK(BOOKID),
SALEPRICE NUMBER(8),
ORDERDATE DATE
);

[문제 1]

고객별로 주문한 모든 도서의 총 판매액을 구하고 고객별로 정렬하여 출력하기

 

 

[문제 1 답]

SELECT C.NAME, SUM(O.SALEPRICE) --총 판매액
FROM CUSTOMER C, ORDERS O
WHERE C.CUSTID = O.CUSTID --고객별로 주문 확인
GROUP BY C.NAME --고객별로~(그룹화)
ORDER BY C.NAME; --고객별로 정렬

[문제 3] 

고객별 구매한 도서 수량 출력(0권 구매 포함)

 

 

[문제 3 답]

SELECT C.NAME, COUNT(C.CUSTID) AS "구매수량" 
--구매수량 : 이름을 기준으로 고객 아이디로 개수 세기
FROM CUSTOMER C, ORDERS O
WHERE C.CUSTID = O.CUSTID(+)
 -- 고객과 주문의 각각 ID 일치(왼쪽 조인, C.CUSTID 부분 NULL)
GROUP BY O.CUSTID, C.NAME;

 

[문제 4]

도서별 판매 수량 출력하기

 

 

[문제 4 답]

SELECT B.BOOKNAME, B.BOOKPRICE COUNT(O.BOOKID) AS "판매수량"  -- 주문한 책의 개수 세기
FROM BOOK. B, ORDERS O
WHERE B.BOOKID = O.BOOKID(+)
 -- 고객과 주문의 각각 ID 일치(왼쪽 조인,  B.BOOKID부분 NULL)
GROUP BY O.BOOKID, B.BOOKNAME, B.PRICE;