documents/hr.sql at master · publicseo/documents · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
CREATE TABLE TB_CATEGORIES
(
    CATE_NO         NUMBER(5,0)         NOT NULL,
    CATE_NAME       VARCHAR2(200 BYTE)  NOT NULL,
    CATE_PARENT_NO  NUMBER(5,0)
);


CREATE TABLE TB_CUSTOMERS
(
    CUST_NO           NUMBER(5,0)         NOT NULL,
    CUST_ID           VARCHAR2(20 BYTE)   NOT NULL,
    CUST_NAME         VARCHAR2(50 BYTE)   NOT NULL,
    CUST_PASSWORD     VARCHAR2(20 BYTE)   NOT NULL,
    CUST_POINT        NUMBER(8,0)         DEFAULT 0,
    CUST_EMAIL        VARCHAR2(256 BYTE),
    CUST_USED         CHAR(1 BYTE)        DEFAULT 'Y',
    CUST_CREATE_DATE  DATE                DEFAULT sysdate
);


CREATE TABLE TB_ORDERS
(
    ORDER_NO           NUMBER(7,0)        NOT NULL,
    CUST_NO            NUMBER(5,0),
    ORDER_STATUS       VARCHAR2(20 BYTE)  DEFAULT '°áÀç¿Ï·á',
    ORDER_CREATE_DATE  DATE               DEFAULT sysdate
);


CREATE TABLE TB_ORDER_PRODUCTS
(
    ORDER_NO      NUMBER(7,0),
    PRODUCT_NO    NUMBER(5,0),
    ORDER_PRICE   NUMBER(8,0),
    ORDER_AMOUNT  NUMBER(4,0)
);


CREATE TABLE TB_PRODUCTS
(
    PROD_NO             NUMBER(5,0)         NOT NULL,
    PROD_NAME           VARCHAR2(200 BYTE)  NOT NULL,
    PROD_MAKER          VARCHAR2(100 BYTE)  NOT NULL,
    PROD_PRICE          NUMBER(8,0)         NOT NULL,
    PROD_DISCOUNT_RATE  NUMBER(4,3)         NOT NULL,
    PROD_STOCK          NUMBER(5,0),
    PROD_CREATE_DATE    DATE                DEFAULT sysdate,
    CATE_NO             NUMBER(5,0)
);


CREATE SEQUENCE CATE_SEQ
 START WITH 161
 INCREMENT BY 1
 MINVALUE 1
 NOMAXVALUE
 NOCYCLE
 CACHE 20
 NOORDER
;


CREATE SEQUENCE CUSTOMER_SEQ
 START WITH 10018
 INCREMENT BY 1
 MINVALUE 1
 NOMAXVALUE
 NOCYCLE
 NOCACHE
 NOORDER
;


CREATE SEQUENCE ORDER_SEQ
 START WITH 1000000
 INCREMENT BY 1
 MINVALUE 1
 NOMAXVALUE
 NOCYCLE
 NOCACHE
 NOORDER
;


CREATE SEQUENCE PRODUCT_SEQ
 START WITH 10021
 INCREMENT BY 1
 MINVALUE 1
 NOMAXVALUE
 NOCYCLE
 CACHE 20
 NOORDER
;