drop table customer;
CREATE TABLE CUSTOMER
(CNO   VARCHAR2(3),
 CNAME VARCHAR2(5),
 AREA  VARCHAR2(5));

INSERT INTO CUSTOMER VALUES ('C01', '李四', '台中');
INSERT INTO CUSTOMER VALUES ('C02', '丁一', '台南');
INSERT INTO CUSTOMER VALUES ('C03', '王五', '台北');
INSERT INTO CUSTOMER VALUES ('C04', '張三', '高雄');
INSERT INTO CUSTOMER VALUES ('C05', '劉三', '台南');

drop table orders;
CREATE TABLE ORDERS
(ODATE DATE,
 CNO   VARCHAR2(3),
 PNO   VARCHAR2(3),
 QTY   NUMBER  (5));

INSERT INTO ORDERS VALUES (TO_DATE('08/11/03', 'YY/MM/DD'), 'C01', 'P01', 20);
INSERT INTO ORDERS VALUES (TO_DATE('08/11/05', 'YY/MM/DD'), 'C01', 'P01',100);
INSERT INTO ORDERS VALUES (TO_DATE('08/11/03', 'YY/MM/DD'), 'C01', 'P03',100);
INSERT INTO ORDERS VALUES (TO_DATE('08/11/07', 'YY/MM/DD'), 'C03', 'P04', 20);
INSERT INTO ORDERS VALUES (TO_DATE('08/11/08', 'YY/MM/DD'), 'C03', 'P04', 10);
INSERT INTO ORDERS VALUES (TO_DATE('08/11/08', 'YY/MM/DD'), 'C04', 'P03', 20);
INSERT INTO ORDERS VALUES (TO_DATE('08/11/07', 'YY/MM/DD'), 'C04', 'P04', 20);

drop table product;
CREATE TABLE PRODUCT
(PNO   VARCHAR2(3),
 PNAME VARCHAR2(5),
 PRICE NUMBER  (5));

INSERT INTO PRODUCT VALUES ('P01', '西瓜', 50);
INSERT INTO PRODUCT VALUES ('P02', '蘋果', 80);
INSERT INTO PRODUCT VALUES ('P03', '鳳梨', 40);
INSERT INTO PRODUCT VALUES ('P04', '香蕉', 20);
INSERT INTO PRODUCT VALUES ('P05', '楊桃', 10);

drop table ship;
CREATE TABLE SHIP
(AREA  VARCHAR2(5),
 FEE   NUMBER  (5));

INSERT INTO SHIP VALUES ('台北', 70);
INSERT INTO SHIP VALUES ('台中', 100);
INSERT INTO SHIP VALUES ('台南', 0);
INSERT INTO SHIP VALUES ('高雄', 30);
INSERT INTO SHIP VALUES ('澎湖', null);
INSERT INTO SHIP VALUES ('花蓮', 50);