闲来无事家中写SQL

--至少选修王老师所授课程中一门课程的男同学的姓名
SELECT sname FROM s
WHERE sex = '男' AND (1 <= (
	SELECT COUNT(*) FROM sc, c
	WHERE s.sn = sc.sn AND sc.cn = c.cn AND c.teacher = '王老师'));

--找出令狐冲同学没有选的课程号和课程名
SELECT DISTINCT cn, cname 
FROM c
WHERE c.cn NOT IN (
	SELECT DISTINCT sc.cn
	FROM s, sc
	WHERE s.sname = '令狐冲' AND s.sn = sc.sn);

--查找选修所有课程的学生号和姓名
SELECT sn, sname
FROM s
WHERE NOT EXISTS (
	SELECT *
	FROM c
	WHERE NOT EXISTS (SELECT *
		FROM sc
		WHERE sc.sn = s.sn AND sc.cn = c.cn));

--查找全部学生都选修的课程的课程号和课程名
--不存在没有选择该课程的学生
SELECT cn, cname
FROM c
WHERE NOT EXISTS (
	SELECT * 
	FROM s
	WHERE NOT EXISTS(
		SELECT *
		FROM sc
		WHERE s.sn = sc.sn AND sc.cn = c.cn
	)
);

--检索选修课包含王老师所授课的学生学号
SELECT sn, sname
FROM s
WHERE EXISTS (
	SELECT * FROM sc WHERE s.sn = sc.sn AND EXISTS (
		SELECT * FROM c WHERE sc.cn = c.cn AND c.teacher = '周老师'
	)
);

--把低于总平均成绩的女同学成绩提高5分
UPDATE sc
SET grade = grade + 5
WHERE sc.grade < (
	SELECT AVG(grade) 
	FROM sc
	WHERE EXISTS (
		SELECT *
		FROM s
		WHERE sc.sn = s.sn AND s.sex = '女'
	));

--统计至少有一个学生选修的课程的门数
SELECT *
FROM c
WHERE EXISTS (
	SELECT *
	FROM s
	WHERE c.cn IN (
		SELECT sc.cn 
		FROM sc
		WHERE s.sn = sc.sn)
);

 

CREATE TABLE c
(
  cn integer NOT NULL,
  cname character varying,
  teacher character varying,
  CONSTRAINT pk_cn PRIMARY KEY (cn)
)

CREATE TABLE s
(
  sn integer NOT NULL,
  sname character varying,
  age integer,
  sex character varying,
  CONSTRAINT pk_sn PRIMARY KEY (sn)
)

CREATE TABLE sc
(
  sn integer NOT NULL,
  cn integer NOT NULL,
  grade integer,
  CONSTRAINT pk_sn_cn PRIMARY KEY (sn, cn),
  CONSTRAINT fk_cn FOREIGN KEY (cn)
      REFERENCES c (cn) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_sn FOREIGN KEY (sn)
      REFERENCES s (sn) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)