经典SQL五十题

1、查询“01”课程比“02”课程成绩高的所有学生的学号；
2、查询平均成绩大于60分的同学的学号和平均成绩；
3、查询所有同学的学号、姓名、选课数、总成绩
4、查询姓“李”的老师的个数；
5、查询没学过“张三”老师课的同学的学号、姓名；
6、查询学过编号“01”并且也学过编号“02”课程的同学的学号、姓名；
7、查询学过“张三”老师所教的课的同学的学号、姓名；
8、查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名；
9、查询所有课程成绩小于60分的同学的学号、姓名；
10、查询没有学全所有课的同学的学号、姓名；
11、查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名；
12、查询和"01"号的同学学习的课程完全相同的其他同学的学号和姓名
13、把“SC”表中“张三”老师教的课的成绩都更改为此课程的平均成绩；
14、查询没学过"张三"老师讲授的任一门课程的学生姓名
15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
16、检索"01"课程分数小于60，按分数降序排列的学生信息
17、按平均成绩从高到低显示所有学生的平均成绩
18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
20、查询学生的总成绩并进行排名
21、查询不同老师所教不同课程平均分从高到低显示
22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
24、查询学生平均成绩及其名次
25、查询各科成绩前三名的记录
26、查询每门课程被选修的学生数
27、查询出只选修了一门课程的全部学生的学号和姓名
28、查询男生、女生人数
29、查询名字中含有"风"字的学生信息
30、查询同名同性学生名单，并统计同名人数
31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)
32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
37、查询不及格的课程，并按课程号从大到小排列
38、查询课程编号为"01"且课程成绩在60分以上的学生的学号和姓名；
40、查询选修“张三”老师所授课程的学生中，成绩最高的学生姓名及其成绩
42、查询每门功课成绩最好的前两名
43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
44、检索至少选修两门课程的学生学号
45、查询选修了全部课程的学生信息
46、查询各学生的年龄
47、查询本周过生日的学生
48、查询下周过生日的学生
49、查询本月过生日的学生
50、查询下月过生日的学生

表结构

学生表：student

教师表：teacher

课程表：

成绩表：

题目

1.

查询"01"课程比"02"课程成绩高的学生的信息及课程分数

解：

SELECT s.s_id, s.s_name, b.cid1, b.score1, b.cid2, b.score2
FROM student s
JOIN
(
SELECT a.s_id as id, a.c_id as cid1, a.score as score1, b.c_id as cid2, b.score as score2
FROM stu_sco a
JOIN stu_sco b
ON a.s_id = b.s_id 
WHERE a.score > b.score AND a.c_id = 1 AND b.c_id = 2
) b
ON s.s_id = b.id

结果：

解析：

其实单看下面这个sql一目了然：
这就是上面的b表，实际上这个b表已经能查出相关的信息了，只不过我们需要获取学生的姓名，所以外面还要跟student表join一次。

逻辑其实就是很简单，将成绩表做一个自身连接，由于是要找某一名学生的01课程分数大于02课程分数。因此前提就是必须是同一名学生，因此JOIN的条件为s_id相同。然后判断条件就很简单了。

SELECT a.s_id as id, a.c_id as cid1, a.score as score1, b.c_id as cid2, b.score as score2
FROM stu_sco a
JOIN stu_sco b
ON a.s_id = b.s_id 
WHERE a.score > b.score AND a.c_id = 1 AND b.c_id = 2

单运行上述sql结果为：

2.

查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

SELECT a.s_name, b.avg_socre
FROM student a
JOIN
(
SELECT s_id, AVG(score) as avg_socre
FROM stu_sco
GROUP BY s_id
HAVING avg_socre > 60
) b
ON a.s_id = b.s_id

解析：

• 其实就是查询平均成绩大于60的学生 —— b表
• 然后与a表做关联，查姓名即可

结果：

3.

查询所有同学的学号、姓名、选课数、总成绩

SELECT s_name, b.*
FROM student a
JOIN
(
SELECT s_id, COUNT(*) as num, SUM(score) as sum_score
FROM stu_sco
GROUP BY s_id
) b
ON a.s_id = b.s_id

解析：

• 只看b表，同时使用count(*) 和 SUM(score) 来计算课程数和总分数，最后使用GROUP BY根据id分类
• 使用a表join是因为需要查询姓名，关联条件则是s_id

结果

4

查询姓“李”的老师的个数

SELECT COUNT(*)
FROM teacher
WHERE t_name LIKE "李%"

5

查询没学过“张三”老师课的同学的学号、姓名

SELECT s_id, s_name
FROM student
WHERE s_id NOT IN 
(
SELECT s_id -- 查询选了张三老师课程的学生 
FROM stu_sco a
JOIN course b  -- 成绩表关联课程表
ON a.c_id = b.c_id
JOIN teacher c  -- 课程表关联教师表
ON b.t_id = c.t_id 
WHERE c.t_name = "张三"
)

思路：

查询选了张三老师课程的同学id，然后使用not in

结果：

6

查询学过编号“01”并且也学过编号“02”课程的同学的学号、姓名

SELECT s_id, s_name
FROM student
WHERE s_id IN
(
SELECT DISTINCT(s1.s_id)
FROM stu_sco s1  -- 两张成绩表关联
JOIN stu_sco s2
ON
s1.s_id = s2.s_id -- id相同
WHERE s1.c_id = '01' AND s2.c_id = '02' -- 左边学了01 右边学了02
)

结果：

7

查询学过“张三”老师所教的课的同学的学号、姓名

SELECT s1.s_id, s1.s_name, s2.c_id, t1.t_name
FROM student s1
JOIN stu_sco s2
ON s1.s_id = s2.s_id  -- 关联学生表查询姓名
JOIN course c1
ON s2.c_id = c1.c_id  -- 关联课程表查课程
JOIN teacher t1
ON c1.t_id = t1.t_id  -- 关联教师表查姓名
WHERE t1.t_name = '张三'

结果：

8

查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名；

SELECT s.s_id, s_name
FROM student s
WHERE s.s_id IN
(
SELECT s1.s_id
FROM stu_sco s1
JOIN stu_sco s2
ON s1.s_id = s2.s_id -- 成绩表做笛卡尔积
WHERE s1.c_id = '01' AND s2.c_id = '02' AND s1.score < s2.score -- 找到符合条件的行数据
)

结果：

9

查询所有课程成绩小于60分的同学的学号、姓名；

思路1：
查询有一门成绩大于60分的所有同学，然后关联学生表not in

注意： 这种方法有一种缺陷，就是如果缺考的学生也会算进来，所以建议用第二种

SELECT s_id, s_name
FROM student
WHERE s_id NOT IN
(
SELECT DISTINCT(s_id)
FROM stu_sco
WHERE score > 60 -- 查询分数大于60的 
)

查询最高成绩都小于60分的同学，关联学生表join

SELECT a.s_id, a.s_name
FROM student a
JOIN 
(
SELECT s_id, MAX(score) max_score
FROM stu_sco
GROUP BY s_id
HAVING max_score < 60 -- 查询最高分数都小于60的
) b
ON a.s_id = b.s_id

10

查询没有学全所有课的同学的学号、姓名；