Intro to Database (8) : Oracle SQL developer 6 - DML, TCL

2019-07-08

Subsets of SQL

1. DML : Data Manipulation Language, 데이터 조작문 

2. TCL : Transaction Control Language, 트랜잭션 관리문 

 

 

---

실습(1) : 새 사용자를 만들어  1,2 에 대해 실습해보자

먼저 시스템 계정으로 접속해, 다른 사용자 탭을 우클릭해서 사용자 생성

새로 생성한 사용자에 시스템 계정의 권한으로 몇 가지 권한을 부여해주도록 합니다. 그 옵션들은 다음과 같습니다. 여기서 권한 선택을 제대로 하지 못하면 테이블 생성 등의 작업이 잘 되지 않을 수 있습니다. 

• CREATE SESSION
• CREATE TABLE
• UNLIMITED TABLESPACE
• SELECT ANY TABLE
• UPDATE ANY TABLE
• INSERT ANY TABLE
• DROP ANY TABLE  

권한이 부여됨 이 쪽 줄에 체크해주면 된다. 

권한 부여가 다 되었다면 테이블을 생성하자. 고급 옵션을 체크하고 작업하도록 하자.

3개의 테이블(CUSTOMER, COMMODITY, ORDERS)을 생성할 것이고, 처음에 했을 때와는 다르게 UI상에서 테이블 생성 메뉴를 선택해 생성하도록 합시다. 이게 더 편합니다. 

1) CUSTOMER TABLE 

CUSTOMER TABLE 을 생성하고 데이터 유형과 크기는 위와 같이 한다. 그리고 제약조건에서 기본키로 CUSTOMER_ID를 지정한다. 기본키 설명은 이 다다음 사진 참고

해당 SQL문 : 

CREATE TABLE customers(
   customer_id    VARCHAR2(4),
   customer_name  VARCHAR2(20),
   address        VARCHAR2(60),
   contact        VARCHAR2(20),
   CONSTRAINT cust_id_pk PRIMARY KEY(customer_id)); 

CUSTOMER TABLE 을 생성

2) COMMODITIES TABLE 

해당 SQL문 :

CREATE TABLE commodities(
   commodity_id     VARCHAR2(4),
   commodity_name   VARCHAR2(20),
   unit_price       NUMBER(8,2) NOT NULL,
   CONSTRAINT comm_id_pk PRIMARY KEY(commodity_id));

 

 

기본키는 제약조건에서 설명합니다

3) ORDERS 

해당 SQL문 :

CREATE TABLE orders(
   order_id       VARCHAR2(4),
   customer_id    VARCHAR2(4),
   commodity_id   VARCHAR2(4),
   units          NUMBER(8,2) NOT NULL,
   total_cost     NUMBER(8,2) NOT NULL,
   CONSTRAINT ordr_id_pk PRIMARY KEY(order_id),
   CONSTRAINT ordr_cust_fk FOREIGN KEY (customer_id)REFERENCES customers(customer_id),
   CONSTRAINT ordr_comm_fk FOREIGN KEY (commodity_id)REFERENCES commodities(commodity_id),
   CONSTRAINT check_unit CHECK(units > 0),
   CONSTRAINT check_totl CHECK(total_cost > 0));

 

테이블을 생성하고 기본키 하나와 외래키 2개를 설정합니다. 그리고 검사 조건까지 설정합니다 

+키를 눌러서 제약 조건을 추가할 수 있다.

테이블 생성이 완료 되었다면, 데이터를 삽입해보자. 

 

INSERT INTO customers VALUES ('C001', 'BDAVIS', 'Boston', '650.551.4876');
INSERT INTO customers VALUES ('C002', 'SSTEPHEN', 'ST.Louis', '650.501.9321');
INSERT INTO customers VALUES ('C003', 'DCARTER', 'California', '650.507.6632');

INSERT INTO commodities VALUES ('M001', 'DVD Player', 109);
INSERT INTO commodities VALUES ('M002', 'Cereal', 03);
INSERT INTO commodities VALUES ('M003', 'Scrabble', 29);

INSERT INTO orders VALUES ('R001', 'C003', 'M002', 50, 150);
INSERT INTO orders VALUES ('R002', 'C001', 'M003', 30, 87);
INSERT INTO orders VALUES ('R003', 'C003', 'M001', 6, 654);

의 데이터를 developer를 이용해 다음과 같이 넣으면 된다. 

처음 배웠을 때와는 다르게 sql 문을 사용하지 않고 하나하나 입력해보았다.

---

UPDATE    COMMODITIES SET 	  UNIT_PRICE = 129 
WHERE     COMMODITY_NAME = 'DVD Player';

DELETE FROM ORDERS WHERE ORDER_ID = 'R001';
ROLLBACK;

DELETE FROM ORDERS WHERE ORDER_ID = 'R001';
CREATE TABLE A_ORDER AS SELECT * FROM ORDERS;
ROLLBACK;

DROP TABLE COMMODITIES CASCADE CONSTRAINTS;
REVOKE CREATE SESSION FROM online_shoppe;

/* DML */
create table a_enrol as 
select * from enrol where stu_no < 20150000;

--108, 20151062, 92 입력
--109, 20152088, 85 입력
--110, 20152088 입력
--111, 20153075, null 입력
insert into a_enrol values (108, 20151062, 92);
insert into a_enrol values (109, 20152088, 85);
insert into a_enrol (sub_no, stu_no) values (110, 20152088);
insert into a_enrol values (111, 20153075, null);

--복수행 삽입 Example
--Enrol table에서 stu_no 2015로 시작되는 학생을 선택하여 a_enrol에 삽입하시요
insert into a_enrol (select * from enrol where stu_no like '2015%');
--Update Example
--점수를 5점 높이시요
update a_enrol set enr_grade = enr_grade + 5;
--Sub_no가 101인 학생들의 점수를 5점 높이시요
update a_enrol set enr_grade = enr_grade + 10 where sub_no = 101;
--sub_name이 시스템분석설계인 학생들의 점수를 10점 올리시요
update a_enrol set enr_grade = enr_grade + 10
where sub_no = (select sub_no from subject where sub_name = '시스템분석설계');