--至少选修王老师所授课程中一门课程的男同学的姓名
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
)