# 0601 新增學員和教練表格

-- 新增學員表格--
CREATE TABLE STUDENT (
S_NO NUMBER(10) PRIMARY KEY NOT NULL,
S_NAME VARCHAR2(30) NOT NULL,
S_PSW VARCHAR2(10) NOT NULL,
S_MAIL VARCHAR2(30) NOT NULL,
S_TEL VARCHAR2(10) NOT NULL,
S_ADD VARCHAR2(65) NOT NULL,
S_POIN NUMBER(10) NOT NULL,
S_STA NUMBER(1) NOT NULL,
S_SEX NUMBER(1) NOT NULL,
S_BIR DATE NOT NULL,
S_PIC BLOB
);

--新增學員表格內容--
INSERT INTO STUDENT VALUES ('01', '小傑', 'S010101', '[e-mail]' , '0976798081', '桃園市平鎮區中央路115號', 0, 0, 0, TO_DATE('2020-05-05','YYYY-MM-DD'), NULL);
INSERT INTO STUDENT VALUES ('02', '奇犽', 'S020202', '[e-mail]' , '0976798080', '桃園市平鎮區中央路116號', 0, 0, 0, TO_DATE('2020-07-07','YYYY-MM-DD'), NULL);

commit;

-- 新增教練表格--
CREATE TABLE COACH (
C_NO NUMBER(10) PRIMARY KEY NOT NULL,
C_NAME VARCHAR2(30) NOT NULL,
C_PSW VARCHAR2(10) NOT NULL,
C_MAIL VARCHAR2(30) NOT NULL,
C_TEL VARCHAR2(10) NOT NULL,
C_ACC VARCHAR2(14) NOT NULL,
C_POIN NUMBER(10) NOT NULL,
C_STA NUMBER(1) NOT NULL,
C_PIC BLOB,
C_SEX NUMBER(1) NOT NULL,
C_SC_TOTAL NUMBER(10),
C_SC_QTY NUMBER(10)
);

--新增教練表格內容--
INSERT INTO COACH VALUES ('01', '比司吉', 'C010101', '[e-mail]' , '0976798081', '15864235845618', 0, 0, NULL, 0, 0, 0);
INSERT INTO COACH VALUES ('02', '彼特', 'C020202', '[e-mail]' , '0976798080', '11588963257223', 0, 0, NULL, 0, 0, 0);

commit;