编辑代码

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 ('男', '女')) 
); 

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 S(SNO, SN, SD, SB, SEX) values('s31', '王浩', '计算机', '1999-10-15', '男');

CREATE TABLE C
(
CNO VARCHAR(6) PRIMARY KEY,
CN VARCHAR(10),
PC VARCHAR(6),
FOREIGN KEY (PC) REFERENCES C(CNO)
);

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

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 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, GRADE) values('s31', 'C01', NULL);

CREATE VIEW V_计算机系学生 AS
SELECT *
FROM S
WHERE SD = '计算机';

CREATE VIEW S_C_GRADE AS
SELECT S.SNO, S.SN, SC.CNO, SC.GRADE
FROM S
JOIN SC ON S.SNO = SC.SNO;

CREATE VIEW COMPUTE_AVG_GRADE AS
SELECT S.SNO, AVG(SC.GRADE) AS AVG_GRADE
FROM S
JOIN SC ON S.SNO = SC.SNO
WHERE S.SD = '计算机'
GROUP BY S.SNO;

CREATE OR REPLACE VIEW COMPUTE_AVG_GRADE AS
SELECT S.SNO, AVG(SC.GRADE) AS AVG_GRADE
FROM S
JOIN SC ON S.SNO = SC.SNO
WHERE S.SD = '数学'
GROUP BY S.SNO;

-- 删除原视图
DROP VIEW IF EXISTS V_计算机系学生;

-- 创建新视图并命名为 V_计算机系男生
CREATE VIEW V_计算机系男生 AS
SELECT *
FROM S
WHERE SD = '计算机' AND SEX = '男';

SELECT * FROM V_计算机系男生;
SELECT * FROM S_C_GRADE;
SELECT * FROM COMPUTE_AVG_GRADE;

SELECT S.SNO, S.SN, AVG(SC.GRADE) AS AVG_GRADE
FROM S
JOIN SC ON S.SNO = SC.SNO
GROUP BY S.SNO, S.SN
HAVING AVG(SC.GRADE) >= 90;

SELECT S.SD AS 系别, COUNT(*) AS 人数
FROM S
JOIN SC ON S.SNO = SC.SNO
GROUP BY S.SD
HAVING AVG(SC.GRADE) > 80
ORDER BY 人数 DESC;

DROP VIEW IF EXISTS V_计算机系男生;

DROP VIEW IF EXISTS COMPUTE_AVG_GRADE;