编辑代码

CREATE DATABASE EUDC;
use EUDC;
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', '男');
SELECT * FROM S;

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('C02', '数据结构', 'C01');
INSERT INTO C(CNO, CN, PC) values('C03', '操作系统', 'C02');
INSERT INTO C(CNO, CN, PC) values('C04', '数据库', 'C03');
INSERT INTO C(CNO, CN, PC) values('C05', '作战指挥', 'C04');
INSERT INTO C(CNO, CN, PC) values('C06', '离散数学', 'C01');
INSERT INTO C(CNO, CN, PC) values('C07', '信息安全', 'C06');
INSERT INTO C(CNO, CN, PC) values('C08', '大学英语', NULL);
INSERT INTO C(CNO, CN, PC) values('C11', 'C语言程序设计', NULL);
SELECT * FROM C;

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

SELECT DISTINCT SNO FROM SC;

SELECT * FROM S WHERE SD = '计算机';

SELECT SNO, SN FROM S WHERE SD = '计算机';

SELECT * FROM S WHERE (SD = '计算机' OR SD = '信息') AND SEX = '男';

SELECT SNO AS 学号, (GRADE*0.75) AS 调整后成绩 FROM SC WHERE CNO = 'C01' AND GRADE BETWEEN 80 AND 90;

SELECT SNO, CNO FROM SC WHERE GRADE IS NULL;

SELECT SNO, GRADE FROM SC WHERE CNO IN ('C01', 'C04') ORDER BY GRADE DESC, SNO ASC;

SELECT * FROM S WHERE SB >= '1995-01-01' ORDER BY SN ;

SELECT * FROM S WHERE SN LIKE '李%' OR SN LIKE '刘%';

SELECT SNO, SN, SEX FROM S WHERE SN NOT LIKE '张%'
                                AND SN NOT LIKE '王%'
                                 AND SN NOT LIKE '李%'
                                AND SN NOT LIKE '刘%';

SELECT COUNT(DISTINCT SNO) AS 选修课程的学生人数 FROM SC; 

SELECT MAX(GRADE) AS 最高分 FROM SC WHERE CNO = 'C01'; 

SELECT SNO, AVG(GRADE) AS 平均分 FROM SC GROUP BY SNO;

SELECT SNO FROM SC GROUP BY SNO HAVING COUNT(CNO) > 2;  

SELECT SNO, SUM(GRADE) AS 总成绩 FROM SC GROUP BY SNO HAVING SUM(GRADE) > 200;