数据库的嵌套查询（子查询），分组查询和统计查询—学生选课

学生选课数据库中的表的结构参考学生选课数据库的定义和维护

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 max(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 DISTINCT Course.cno 课程号
FROM Course
INNER JOIN SC ON SC.cno = Course.cno
WHERE Course.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.*, IFNULL(t.选课人数, 0) 选课人数
FROM Course c
LEFT JOIN(SELECT COUNT(sno) 选课人数, cno
	FROM SC
	GROUP BY cno) t ON c.cno = t.cno