# ORACLE CHAPTER.3
###### tags: `Ming` `ORACLE`

{%hackmd BJrTq20hE %}

CREATE TABLE PUBLISHER(
PUBLISHER_id VARCHAR2(40) PRIMARY KEY NOT NULL,
PUBLISHER_NAME VARCHAR2(40) NOT NULL,
CONTACT VARCHAR2(40),
PHONE VARCHAR2(40) NOT FULL,
CREATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE BOOK (
ISBN CHAR(13) PRIMARY KEY NOT NULL,
BOOK_NAME VARCHAR2(200) NOT NULL,
PRICE NUMBER(8,2) CHECK (PRICE >= 0),
AUTHOR VARCHAR2(200),
PUBLICATION_DATE DATE,
PUBLISHER_ID VARCHAR2(40),
CONSTRAINT FK_BOOK_PUBLISHER
FOREIGN KEY (PUBLISHER_ID) REFERENCES PUBLISHER(PUBLISHER_ID)
);

Question. 3-1
CREATE TABLE CUSTOMER(
CUSTOMER_ID VARCHAR2(40) NOT NULL PRIMARY KEY,
CUSTORMER_NAME VARCHAR2(40)NOT NULL,
PHONE VARCHAR2(40)NOT NULL,
ADDRESS VARCHAR2(200)NOT NULL
);

Question. 3-2
CREATE TABLE ORDER_MASTER(
ORDER_ID NUMBER(10) NOT NULL PRIMARY KEY,
CUSTOMER_ID VARCHAR2(40),
ORDER_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

-- 建立外來健：
1.設定外來鍵的名稱
2.將選定的欄位參照到其他表格以建立FK
P.S.沒有逗點代表還是同一行
CONSTRAINT FK_ORDERMASTER_CUSTOMER
FOREIGN KEY(CUSTOMER_ID) REFERENCES CUSTOMER(CUSTOMER_ID)
);

Question. 3-3
CREATE TABLE ORDER_DETAIL(
ORDER_ID NUMBER(10),
ISBN CHAR(13)NOT NULL,

-- 複合組鍵的表示方法
PRIMARY KEY(ORDER_ID, ISBN),
--------------------------------------------------------------------
QUANTITY NUMBER(10),
CONSTRAINT FK_ORDERDETAIL_ORDERMASTER
FOREIGN KEY (ORDER_ID) REFERENCES ORDER_MASTER(ORDER_ID),
CONSTRAINT FK_ORDERDETAIL_BOOK
FOREIGN KEY (ISBN) REFERENCES BOOK(ISBN)
);

Question. 3-4
ALTER TABLE ORDER_DETAIL ADD MY_COMMENT VARCHAR2(200);
ALTER TABLE ORDER_DETAIL RENAME COLUMN MY_COMMENT TO YOUR_COMMENT;
ALTER TABLE ORDER_DETAIL MODIFY YOUR_COMMENT VARCHAR2(300);
ALTER TABLE ORDER_DETAIL DROP COLUMN YOUR_COMMENT;

--------------------------------------------------------------------
ALTER TABLE ORDER_DETAIL DROP CONSTRAINT FK_ORDERDETAIL_BOOK;
ALTER TABLE ORDER_DETAIL DROP CONSTRAINT FK_ORDERDETAIL_ORDERMASTER;
DROP TABLE ORDER_DETAIL;