4 . 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

首先根据sid 分组 ， 计算出每个同学的 每个课程的数量和加起来的总分
SELECT sc.SId ,SUM(sc.score)AS sumscore, COUNT(sc.CId)
FROM sc GROUP BY sc.SId

SELECT st.sid , st.sname , rr.sumscore , rr.ct
FROM (

(SELECT student.sid , student.Sname FROM student) AS st

LEFT JOIN
(SELECT sc.SId ,SUM(sc.score)AS sumscore, COUNT(sc.CId)AS ct
FROM sc GROUP BY sc.SId)AS rr

ON st.SId = rr.sid
)

4.1 查有成绩的学生信息

利用子查询
SELECT *FROM student WHERE student.SId IN (SELECT sc.SId FROM sc)

5 . 查询「李」姓老师的数量

SELECT COUNT(*) FROM teacher WHERE tname LIKE ‘李%’;

6 . 查询学过「张三」老师授课的同学的信息

SELECT * FROM student RIGHT JOIN

(SELECT sid FROM sc WHERE cid IN
(SELECT cid FROM course WHERE tid = ‘01’))AS rr
ON student.SId = rr. sid

7 . 查询没有学全所有课程的同学的信息

首先查出所有课程的总数量
SELECT COUNT(cid) FROM course

再查出学生学课的总数量等于查出所有课程总数量的sid
SELECT sc.sid FROM sc GROUP BY sc.SId
HAVING COUNT(sc.CId) = (SELECT COUNT(cid) FROM course)

再关联查询学生表：
SELECT * FROM student WHERE student.sid NOT IN
(SELECT sc.sid FROM sc GROUP BY sc.SId
HAVING COUNT(sc.CId) = (SELECT COUNT(cid) FROM course))

8 . 查询至少有一门课与学号为” 01 “的同学所学相同的同学的信息

首先查出01同学的所有课程
SELECT sc.cid FROM sc WHERE sc.SId = ‘01’

然后查出有哪些同学的所学课程再01学生的课程里面
(SELECT DISTINCT sc.sid FROM sc WHERE cid IN
(SELECT sc.cid FROM sc WHERE sc.SId = ‘01’))

最后关联学生表查出所有信息
SELECT student.* FROM student RIGHT JOIN
(SELECT DISTINCT sc.sid FROM sc WHERE cid IN
(SELECT sc.cid FROM sc WHERE sc.SId = ‘01’))AS rr
ON student.SId = rr.sid

9 . .查询和” 01 “号的同学学习的课程完全相同的其他同学的信息

(SELECT DISTINCT sc.sid FROM sc WHERE
sid<> ‘01’ AND
cid IN
(SELECT sc.cid FROM sc WHERE sc.SId = ‘01’)
HAVING COUNT(cid) = (SELECT COUNT(cid) FROM sc WHERE sid = ‘01’)
AS rr
ON student.SId = rr.sid)

10 . 查询没学过”张三”老师讲授的任一门课程的学生姓名

SELECT * FROM student WHERE student.SId NOT IN
(SELECT sid FROM sc WHERE cid IN(
SELECT cid FROM course WHERE tid IN
(SELECT teacher.tid FROM teacher WHERE tname = ‘张三’)
)
GROUP BY sid )

11 . 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT * FROM student RIGHT JOIN
(SELECT rr.sid ,rr.av FROM
(SELECT AVG (score)AS av ,sid , COUNT(cid)AS cc FROM sc
WHERE score < ‘60’ GROUP BY sid)AS rr
WHERE rr.cc >= ‘2’) AS rrr
ON student.SId = rrr.sid
GROUP BY rrr.sid

12 . 检索” 01 “课程分数小于 60，按分数降序排列的学生信

SELECT student.* , sc.score FROM student , sc
WHERE student.sid = sc.sid
AND sc.score < 60
AND cid = ‘01’
ORDER BY sc.score DESC

13 . 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT rr.sid ,student.Sname,rr.score , rr.avs FROM student RIGHT JOIN
(SELECT sc.SId , sc.score , AVG(score)AS avs FROM sc GROUP BY sid
ORDER BY sc.score DESC) AS rr
ON student.SId = rr.sid

14 . 查询各科成绩最高分、最低分和平均分：

SELECT rr.cid , course.Cname , rr.ma , rr.mi , rr.av FROM course RIGHT JOIN
(SELECT sc.cid , MAX(score)AS ma , MIN(score)AS mi ,AVG(score) AS av
FROM sc GROUP BY sc.cid)AS rr
ON course.CId = rr.cid

15 . 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

SELECT sc1.SId , sc1.CId , sc1.score ,COUNT(sc2.score)+1 AS rank

FROM sc AS sc1 LEFT JOIN sc AS sc2

ON sc1.score < sc2.score AND sc1.cid = sc2.cid
GROUP BY sc1.cid , sc1.sid , sc1.score
ORDER BY sc1.cid , rank DESC

16 . 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

SET @rank=0;
SELECT rr.sid , rr.total ,@rank :=@rank +1 AS ranks
FROM
(SELECT sc.sid , SUM(sc.score) total FROM sc GROUP BY sc.sid)AS rr

17 . 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

SELECT course.cname, course.cid,
SUM(CASE WHEN sc.score<=100 AND sc.score>85 THEN 1 ELSE 0 END) AS “[100-85]”,
SUM(CASE WHEN sc.score<=85 AND sc.score>70 THEN 1 ELSE 0 END) AS “[85-70]”,
SUM(CASE WHEN sc.score<=70 AND sc.score>60 THEN 1 ELSE 0 END) AS “[70-60]”,
SUM(CASE WHEN sc.score<=60 AND sc.score>0 THEN 1 ELSE 0 END) AS “[60-0]”
FROM sc LEFT JOIN course
ON sc.cid = course.cid
GROUP BY sc.cid;

18 . 查询各科成绩前三名的记录（不会）

SELECT FROM sc WHERE
(SELECT COUNT() FROM sc AS a
WHERE sc.CId =a.CId AND sc.score < a.score) <3
ORDER BY cid ASC , sc.score DESC

19 . 查询每门课程被选修的学生数

SELECT sc.cid , COUNT(sid) FROM sc GROUP BY sc.CId

20 . 查询出只选修两门课程的学生学号和姓名

SELECT rr.sid , student.Sname FROM student RIGHT JOIN
(SELECT sc.sid , COUNT(cid)AS c1 FROM sc GROUP BY sc.SId)AS rr
ON student.SId = rr.sid
AND rr.c1 =’2’