CREATE DATABASE test;
use test;
CREATE TABLE S
(
SNO VARCHAR(6) PRIMARY KEY ,
SN VARCHAR(10) NOT NULL ,
SD VARCHAR(16),
SB DATE,
SEX VARCHAR(2) DEFAULT '男',
CHECK (SEX IN ('男', '女'))
);
CREATE TABLE C
(
CNO VARCHAR(6) PRIMARY KEY,
CN VARCHAR(10),
PC VARCHAR(6),
FOREIGN KEY (PC) REFERENCES C(CNO)
);
CREATE TABLE SC
( SNO VARCHAR(6),
CNO VARCHAR(6),
GRADE DEC(4,1),
PRIMARY KEY (SNO, CNO),
FOREIGN KEY (SNO) REFERENCES S(SNO),
FOREIGN KEY (CNO) REFERENCES C(CNO),
CHECK (GRADE BETWEEN 0 AND 100)
);
CREATE INDEX I_sSN ON S(SN);
CREATE INDEX I_sSdSn ON S(SD DESC,SN);
CREATE TABLE SC
(
SNo CHAR (6) NOT NULL,
CNo CHAR (4) NOT NULL,
GRADE tintint NULL,
PRIMARY KEY(SNo,CNo),
INDEX(GRADE)
)