所使用的表
Course:
| Cid | Cname | Tid |
|---|---|---|
| 01 | 语文 | 02 |
| 02 | 数学 | 01 |
| 03 | 英语 | 03 |
Sc:
| Sid | Cid | Score |
|---|---|---|
| 01 | 01 | 80 |
| 01 | 02 | 90 |
| 01 | 03 | 99 |
| 02 | 01 | 70 |
| 02 | 02 | 60 |
| 02 | 03 | 80 |
Student:
|Sid|Sname|Sage|Sex|
|—|—–|—|
|01|赵雷|1990-01-01 00:00:00|男|
Teacher:
| Tid | Tname |
|---|---|
| 01 | 张三 |
查询” 01 “课程比” 02 “课程成绩高的学生的信息及课程分数
SELECT * FROM Student
WHERE SId IN(SELECT v1.SId FROM (SELECT * FROM SC WHERE SC.CId = '01') As v1INNER JOIN (SELECT * FROM SC WHERE SC.CId = ‘02’) AS v2 ON v1.SId = v2.SId
WHERE v1.score > v2.score)查询同时上过” 01 “课程和” 02 “课程的的学生的成绩情况
SELECT v1.SId,v1.score As 01_score,v2.score As 02_score
FROM (Select * FROM SC WHERE SC.CId = ‘01’) AS v1
INNER JOIN (SELECT * FROM SC WHERE SC.CId = ‘02’) AS v2
ON v1.SId = v2.SId查询平均成绩大于60分的学生的学号和平均成绩
SELECT s.Sid,AVG(s.score)
FROM SC AS s
GROUP BY s.Sid
HAVING AVG(s.score) > 60
4.查询平均成绩小于60分的学生的学号和平均成绩 不包含无效成绩
AVG() GROUP BY HAVING
SELECT s.Sid,AVG(IFNULL(s.score,0))
FROM SC AS s
GROUP BY s.Sid
HAVING AVG(s.score) is NULL or AVG(s.score) < 60
5.查询张姓老师的数量
简单了
6.每门课程的学生人数
SELECT c.CId AS "课程id",count(*) AS "课程人数"
FROM Course AS c LEFT JOIN SC
on c.CId = SC.CId
GROUP BY c.CId
7.查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名
简单了
8.选修了全部课程的学生信息
SELECT *
FROM Student
WHERE Student.SId IN
(SELECT Student.SId
FROM Student LEFT JOIN SC
ON Student.SId = SC.SId
GROUP BY Student.SId
HAVING COUNT(*) = (SELECT COUNT(*) FROM Course))
9.查询存在不及格的课程
SELECT *
FROM Course
WHERE Course.CId IN (
SELECT DISTINCT CId
FROM SC
WHERE SC.score < 60
)
10.查询任何一门课程成绩在 70 分以上的学生姓名、课程名称和分数
SELECT Student.Sname,Course.Cname,SC.score
FROM Student,SC,Course
WHERE Student.SID = SC.SId AND SC.CId = Course.CId AND SC.score > 70
11.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
SELECT Student.Sname,SC.score
FROM Student,SC,Course
WHERE Student.SId = SC.SId AND Course.CId = SC.CId AND Course.Cname = "数学" AND SC.score < 60
12.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
SELECT a.SId,a.Sname,AVG(b.score)
FROM Student a,SC b
WHERE a.SId = b.SId
GROUP BY a.SId
HAVING AVG(b.score) > 85
13.查询男生、女生人数
SELECT Student.Ssex,COUNT(Student.Ssex)
FROM Student
GROUP BY Student.Ssex
14.检索” 01 “课程分数小于 60，按分数降序排列的学生信息
SELECT Student.*
FROM Student,SC
WHERE Student.SId = SC.SId AND SC.score < 60 AND SC.CId = '01'
ORDER BY SC.score DESC
15.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT SC.SId,SUM(CASE WHEN SC.CId = '01' THEN SC.score END) AS "01",
FROM SC
GROUP BY SC.SId
16.以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率，及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90，要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT SC.CId,
MAX(SC.score) AS "最高分",
MIN(SC.score) AS "最高分",
AVG(SC.score) AS "平均分",
CONCAT(CAST(COUNT(Case WHEN SC.score >=60 THEN SC.score END)/COUNT(SC.score)*100 AS DECIMAL),"%") AS "及格率"
FROM SC LEFT JOIN Course
ON SC.CId = Course.CId
GROUP BY SC.CId
17.查询没学过”张三”老师讲授的任一门课程的学生姓名
SELECT SC.SId
FROM SC,Student
WHERE SC.SId = Student.SId
GROUP BY SC.SId
HAVING SC.SId NOT IN (
SELECT SC.SId
FROM SC
WHERE SC.CId IN (
SELECT Course.CId
FROM Course
WHERE Course.TId = (SELECT Teacher.TId FROM Teacher WHERE Teacher.Tname = "张三")
)
)
18.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT Student.Sname,SC.score
FROM SC,Student,Course,Teacher
WHERE SC.SId = Student.SId
AND SC.CId = Course.TId
AND Course.TId = Teacher.TId
AND Teacher.Tname = "张三"
ORDER BY SC.score DESC
LIMIT 1
思考 LIMIT 和ORDER BY 错误的思路 用了MAX
19.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
在成绩有重复的情况下存在相同，需要子查询找出最高的成绩。找学生
20.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT any_value(A.CId),any_value(A.SId),any_value(A.score)
FROM SC AS A
INNER JOIN SC AS B
ON A.score = B.score
AND A.CId != B.CId
AND A.SId = B.SId
GROUP BY A.CId,A.SId