编辑代码

create database sdg;
use sdg;
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);

-- 连接查询
-- select SN from S,SC where CNO='C02' and S.SNO=SC.SNO;
-- select distinct SN from S,SC where CNO<>'C02'and S.SNO=SC.SNO;
-- select distinct SN from S left join SC on S.SNO and CNO='C02' where SC.SNO IS NULL;
-- 嵌套查询
-- select SN from S where SNO IN (select SNO from SC where CNO='C02');
-- select SN from S where SNO not IN (select SNO from SC where CNO='C02');
-- select SNO,GRADE from SC where GRADE > any(select GRADE from SC where SNO in (select SNO from S where SN='许若'));
SELECT SC.SNO, SC.CNO
FROM SC
WHERE SC.GRADE > (
    SELECT AVG(SC2.GRADE)
    FROM SC SC2
    WHERE SC2.SNO = SC.SNO
    GROUP BY SC2.SNO
);
select * from S where SD <> '计算机' and SB < any(select SB from S where SD='计算机');
select SN from S where exists (select * from SC where SC.SNO=S.SNO and CNO='C02');
select SN from S where NOT exists (select * from SC where SC.SNO=S.SNO and CNO='C02');