CREATE database edg;
use edg;
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)
on update cascade
on delete NO action,
FOREIGN KEY (CNO) REFERENCES C(CNO)
on update cascade
on delete NO action,
CHECK (GRADE BETWEEN 0 AND 100)
);
INSERT INTO C(CNO, CN, PC) values('C01', '高等数学', NULL);
INSERT INTO C(CNO, CN, PC) values('C11', 'C语言程序设计', NULL);
INSERT INTO C(CNO, CN, PC) values('C02', '数据结构', 'C11');
INSERT INTO C(CNO, CN, PC) values('C03', '操作系统', 'C02');
INSERT INTO C(CNO, CN, PC) values('C05', '作战指挥', 'C01');
INSERT INTO C(CNO, CN, PC) values('C06', '离散数学', 'C01');
insert into S(SNO,SN,SD,SB,SEX)values('s01','王玲','计算机','2000-06-30','女');
insert into S(SNO,SN,SD,SB,SEX)values('s02','李源','计算机','1995-03-23','男');
insert into S(SNO,SN,SD,SB,SEX)values('s03','罗军','计算机','1995-08-12','男');
insert into S(SNO,SN,SD,SB,SEX)values('s04','赵泽','计算机','2000-06-30','女');
insert into S(SNO,SN,SD,SB,SEX)values('s05','许若','自动化','2000-06-30','男');
insert into S(SNO,SN,SD,SB,SEX)values('s06','王先华','自动化','2000-06-30','男');
insert into S(SNO,SN,SD,SB,SEX)values('s07','朱珠','自动化','2000-06-30','女');
insert into S(SNO,SN,SD,SB,SEX)values('s08','李国民','数学','2000-06-30','男');
insert into S(SNO,SN,SD,SB,SEX)values('s20','陈浩然','数学','2000-06-30','男');
insert into S(SNO,SN,SD,SB,SEX)values('s21','张强','计算机','2000-06-30','男');
insert into SC(SNO,CNO,GRADE)values('s04','c06',90.0);
insert into SC(SNO,CNO,GRADE)values('s04','c11',87);
insert into SC(SNO,CNO,GRADE)values('s05','c03',79);
insert into SC(SNO,CNO,GRADE)values('s05','c05',88);
insert into SC(SNO,CNO,GRADE)values('s05','c02',90);
insert into SC(SNO,CNO,GRADE)values('s05','c01',93);
insert into SC(SNO,CNO,GRADE)values('s05','c11',99.0);
insert into SC(SNO,CNO,GRADE)values('s04','c03',90.0);
insert into SC(SNO,CNO,GRADE)values('s06','c11',88.0);
insert into SC(SNO,CNO,GRADE)values('s02','c03',88.0);
insert into SC(SNO,CNO,GRADE)values('s01','c05',NULL);
INSERT IGNORE INTO SC(SNO,CNO) select SNO,CNO from S,C where SD='计算机' and CN='操作系统';
update S set SD ='计算机' where SN='许若';
update SC SET GRADE=93 where SNO ='S05' and CNO IN (select CNO from C where cn='离散数学');
update SC SET GRADE=GRADE*1.05 where GRADE<(select GRADE from (select avg(GRADE),CNO from SC group by CNO) SC1 where SC.CNO=SC1.CNO);