学生选课数据库中的表的结构参考学生选课数据库的定义和维护
1.嵌套查询
1.1查询选修了'数据库原理与应用'课程的学生学号和姓名
SELECT sno 学号, sname 姓名
FROM student
WHERE sno IN(SELECT sno
FROM SC
INNER JOIN Course c ON c.cno = SC.cno AND c.cname = '数据库原理与应用'
)
1.2查询比'王华'同学年龄大的学生的学号和姓名
SELECT sno 学号, sname 姓名
FROM student
WHERE sage>(SELECT sage
FROM student
WHERE sname='王华'
)
1.3查询选修'数据库原理与应用'的成绩低于'张三'的学生的学号和成绩
SELECT sno 学号, grade 成绩
FROM SC
INNER JOIN Course c ON SC.cno = c.cno AND c.cname = '数据库原理与应用'
WHERE grade <(SELECT SC.grade
FROM SC
INNER JOIN Course c ON SC.cno = c.cno AND c.cname = '数据库原理与应用'
INNER JOIN student s ON SC.sno = s.sno AND s.sname = '王华'
)
1.4查询其他学员中比数计学院学生年龄都小的学生
SELECT *
FROM student
WHERE sdept != '数学与计算机科学学院' AND
sage<(SELECT MIN(sage)
FROM student
WHERE sdept = '数学与计算机科学学院'
)
1.5查询选修'数据库原理与应用'课程的学生姓名
SELECT sname
FROM student
WHERE sno IN(SELECT sno
FROM SC
INNER JOIN Course c ON SC.cno = c.cno AND c.cname = '数据库原理与应用'
)
1.6查询选修了全部课程的学生姓名
SELECT sname 姓名
FROM student
WHERE sno IN(SELECT sno
FROM SC
GROUP BY SC.sno
HAVING COUNT(*) =(SELECT COUNT(*)
FROM Course
)
)
1.7查询至少选修了'2024010002'学生所选修课中一门课程的学生的学号和姓名
SELECT DISTINCT s.sno 学号, sname 姓名
FROM student s
INNER JOIN SC ON SC.sno = s.sno
WHERE SC.cno = ANY(SELECT cno
FROM SC
WHERE sno='2024010002'
)
1.8查询至少选修了'2024010002'学生所选修课的全部课程的学生的学号和姓名
SELECT s.sno 学号, sname 姓名
FROM student s
WHERE NOT EXISTS(SELECT *
FROM SC sc1
WHERE sc1.sno='2024010002' AND
NOT EXISTS(SELECT *
FROM SC sc2
WHERE s.sno = sc2.sno AND
sc2.cno = sc1.cno
)
)
1.9查询既选修了'数据结构'又选修了'数据库原理与应用'课程的学生姓名
SELECT sname 姓名
FROM student s
WHERE sno IN(SELECT sno
FROM SC
INNER JOIN Course c ON SC.cno = c.cno
WHERE c.cname = '数据结构' AND
s.sno IN (SELECT sno
FROM SC
INNER JOIN Course c ON SC.cno = c.cno
WHERE c.cname = '数据库原理与应用'
)
)
1.10查询选修了'数据结构'或选修了'数据库原理与应用'课程的学生学号
SELECT sno 学号
FROM student
WHERE sno IN(SELECT sno
FROM SC
INNER JOIN Course c ON SC.cno = c.cno
WHERE c.cname = '数据结构' OR c.cname = '数据库原理与应用'
)
1.11查询选修了'数据结构'而没有选修'数据库原理与应用'课程的学生学号
SELECT sname 姓名
FROM student s
WHERE sno IN(SELECT sno
FROM SC
INNER JOIN Course c ON SC.cno = c.cno
WHERE c.cname = '数据结构' AND
s.sno NOT IN (SELECT sno
FROM SC
INNER JOIN Course c ON SC.cno = c.cno
WHERE c.cname = '数据库原理与应用'
)
)
1.12查询全是男同学选修的课程号
SELECT cno 课程号
FROM Course
WHERE cno NOT IN(SELECT cno
FROM SC
INNER JOIN student s ON s.sno = SC.sno
WHERE ssex = '女'
)
2.分组查询和统计查询
2.1使用集合运算查询选修了'数据结构'或选修了'数据库原理与应用'课程的学生学号
SELECT sname 姓名
FROM student s
INNER JOIN SC ON SC.sno = s.sno
INNER JOIN Course c ON c.cno = SC.cno
WHERE c.cname = '数据结构'
UNION
SELECT sname 姓名
FROM student s
INNER JOIN SC ON SC.sno = s.sno
INNER JOIN Course c ON c.cno = SC.cno
WHERE c.cname = '数据库原理与应用'
2.2统计选修了课程的学生人数
SELECT COUNT(DISTINCT sno) 总人数
FROM sc
2.3查询选修成绩合格, 并且选课门次超过4门以上学生的学号和总成绩
SELECT sno, SUM(grade) 总成绩
FROM SC
GROUP BY sno
HAVING COUNT(*) > 4 AND MIN(grade) >= 60
2.4统计各院系的学生人数
SELECT sdept 院系, COUNT(*) 总人数
FROM student
GROUP BY sdept
2.5统计各年龄的学生人数
SELECT sage 年龄, COUNT(*) 总人数
FROM student
GROUP BY sage
2.6统计每个学生的选修课程数目和平均成绩
SELECT sno 学号, COUNT(sno) 选课数目, AVG(grade) 平均成绩
FROM SC
GROUP BY sno
2.7查询每门课程的详细信息及选修人数
SELECT c.*, COUNT(DISTINCT sno) 选课人数
FROM Course c
LEFT JOIN SC ON SC.cno = c.cno
GROUP BY c.cno, c.cname, c.ccredit, c.cpno
