编辑代码

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)
);

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', '赵泽', '计算机', '1997-09-12', '女');
INSERT INTO S(SNO, SN, SD, SB, SEX) values('s05', '许若', '自动化', '1999-06-27', '男');
INSERT INTO S(SNO, SN, SD, SB, SEX) values('s06', '王仙华', '自动化', '1996-05-20', '男');
INSERT INTO S(SNO, SN, SD, SB, SEX) values('s07', '朱祝', '自动化', '1998-07-10', '女');
INSERT INTO S(SNO, SN, SD, SB, SEX) values('s20', '李国民', '数学', '1999-12-31', '男');
INSERT INTO S(SNO, SN, SD, SB, SEX) values('s21', '陈浩然', '计算机', '2000-10-15', '男');

INSERT INTO SC(SNO, CNO, GRADE) values('s04', 'C06', 90.0);
INSERT INTO SC(SNO, CNO, GRADE) values('s04', 'C11', 87.0);
INSERT INTO SC(SNO, CNO, GRADE) values('s05', 'C03', 79.5);
INSERT INTO SC(SNO, CNO, GRADE) values('s05', 'C05', 88.0);
INSERT INTO SC(SNO, CNO, GRADE) values('s05', 'C02', 90.0);
INSERT INTO SC(SNO, CNO, GRADE) values('s05', 'C01', 93.0);
INSERT INTO SC(SNO, CNO, GRADE) values('s05', 'C06', 99.0);
INSERT INTO SC(SNO, CNO, GRADE) values('s05', 'C11', 72.0);
INSERT INTO SC(SNO, CNO, GRADE) values('s04', 'C02', 90.0);
INSERT INTO SC(SNO, CNO, GRADE) values('s06', 'C03', 88.0);
INSERT INTO SC(SNO, CNO, GRADE) values('s07', 'C11', 88.0);
INSERT INTO SC(SNO, CNO, GRADE) values('s21', 'C05', NULL);

INSERT 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.0 WHERE SNO = 's05' AND CNO IN
                                (SELECT CNO 
                                FROM C 
                                WHERE CN = '离散数学');
-- UPDATE SC SET GRADE = GRADE * 1.05 WHERE GRADE <
--                                     (SELECT AVG(GRADE)
--                                     FROM SC SC1
--                                     WHERE SC.CNO = SC1.CNO);
UPDATE SC SET SC.GRADE = SC.GRADE * 1.05 
WHERE SC.GRADE <
(SELECT GRADE 
FROM (SELECT AVG(GRADE),CNO
        FROM SC
        GROUP BY CNO)SC1
        WHERE SC.CNO = SC1.CNO);
-- DELETE FROM SC WHERE GRADE < (SELECT AVG(GRADE) FROM SC);
DELETE FROM SC WHERE GRADE < (SELECT GRADE 
                            FROM (SELECT AVG(GRADE),CNO
                                FROM SC
                                GROUP BY CNO)SC1
                                WHERE SC.CNO = SC1.CNO);