SQL语句练习50题

写在前面：

鉴于csdn上关于本套习题的回答错漏百出，50个题有一半对就不错了，而且错的一样互相抄，故写本回答。 

先序知识：hubu游兰老师的课

                  或者B站up：早上好我是DJ同学

命名的时候不能把列命名为rank,sum等等对应着一些特殊函数的名字

语句的定语修饰语就是我们要select的对象，一次寻找不到可以先分解成一些步骤寻找出子查询，然后将新表变成from的对象，多次嵌套之后一定可以实现的。

由于题目的数据量比较小，所以所有的代码均未考虑性能，如果各位有更加高效的答案欢迎补充

一个非常有助于理解的知识 

(7)     SELECT 
(8)     DISTINCT <select_list>
(1)     FROM <left_table>
(3)     <join_type> JOIN <right_table>
(2)     ON <join_condition>
(4)     WHERE <where_condition>
(5)     GROUP BY <group_by_list>
(6)     HAVING <having_condition>
(9)     ORDER BY <order_by_condition>
(10)    LIMIT <limit_number>

上面是sql语句的执行顺序，对于理解一些写法我觉得是很有用的 

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

//score s1和score s2相当于生成了两个相同的表只是名称不同而已，然后再利用两个“=”实现三个表的连接//
select student.*,s1.s_score as score01,s2.s_score as score02
from student,score s1,score s2
where student.s_id=s1.s_id 
and s1.s_id=s2.s_id
and s1.c_id='01'
and s2.c_id='02' 
and s1.s_score>s2.s_score ;

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

select student.*,s1.s_score as score01,s2.s_score as score02
from student,score s1,score s2
where student.s_id=s1.s_id 
and s1.s_id=s2.s_id
and s1.c_id='01'
and s2.c_id='02' 
and s1.s_score<s2.s_score ;

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

select st.s_id,st.s_name,avg(s_score)
from student as st,score
group by st.s_id,
st.s_name
having avg(s_score)>=60

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 (包括有成绩的和无成绩的)

select st.s_id,st.s_name,avg(s_score)
from student as st left join score on st.s_id=score.s_id
group by st.s_id,st.s_name
having avg(s_score)<60
or avg(s_score) is null;

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

select st.s_id,st.s_name,count(score.c_id) as '选课总数',sum(score.s_score) as '总分'
from student as st  inner join score on score.s_id=st.s_id
group by st.s_id,st.s_name;

6、查询"李"姓老师的数量

select count(teacher.t_name) as '姓李的人数'
from teacher
where teacher.t_name like '李%';

7、查询学过"张三"老师授课的同学的信息

SELECT a.* FROM Student a 
JOIN Score b ON b.s_id=a.s_id 
JOIN Course c ON b.c_id=c.c_id 
JOIN Teacher d ON d.t_id=c.t_id
AND d.t_name='张三';

 8、查询没学过"张三"老师授课的同学的信息

SELECT s.* 
FROM Student s
where s.s_id
not in(
select a.s_id
from Student a 
JOIN Score b ON b.s_id=a.s_id 
JOIN Course c ON b.c_id=c.c_id 
JOIN Teacher d ON d.t_id=c.t_id
AND  d.t_name like'张三');

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

select s.*
from student s inner join score on s.s_id=score.s_id
where score.c_id like '01' 
intersect
select s.*
from student s inner join score on s.s_id=score.s_id
where score.c_id like '02' 

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

select s.*
from student s 
where s.s_id in(select s_id from score where score.c_id='01')
and s.s_id not in(select s_id from score where score.c_id='02');

 11、查询没有学全所有课程的同学的信息

select distinct s.*
from student s left join score on s.s_id=score.s_id
where s.s_id not  in
(
select s.s_id 
from student s
where s.s_id in(select s_id from score where score.c_id='01')
and s.s_id  in(select s_id from score where score.c_id='02') 
and s.s_id  in(select s_id from score where score.c_id='03'))

12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

select distinct s.*
from student s left join score on s.s_id=score.s_id
where score.c_id in 
(
select score.c_id
from student s left join score on s.s_id=score.s_id
where s.s_id='01'
)

13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

SELECT  student.*
FROM student 
WHERE s_id  in(
		SELECT s_id FROM score WHERE s_id!='01'AND c_id 
					IN( SELECT c_id FROM score WHERE  s_id='01')
								GROUP BY s_id HAVING COUNT(*) =(SELECT COUNT(*) FROM score WHERE s_id='01'))

14、查询没学过"张三"老师讲授的任一门课程的学生姓名

SELECT  s_name
FROM student 
WHERE s_id not in(
select s_id
from score 
where c_id in (select c_id from course where t_id in (select t_id from teacher where t_name like '张三')))
		

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

select s.s_id,s.s_name,avg(sc.s_score)
from Student s inner join Score sc  on s.s_id = sc.s_id
where sc.s_score<60
group by s.s_id, s.s_name
having count(distinct c_id)  >=2;

16、检索"01"课程分数小于60，按分数降序排列的学生信息

select student.*,score.s_score
from student inner join score on student.s_id=score.s_id
where score.c_id='01' and score.s_score<60
order by score.s_score DESC

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

select *
from score as b
inner join (select s_id,avg(s_score) '平均成绩'
            from score
						group by s_id)as a on a.s_id=b.s_id
order by a.平均成绩

18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

       --及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

select score.c_id,course.c_name,max(s_score) as '最高分',min(s_score) as '最低分',avg(s_score) as '平均分',
sum(case when s_score>=60 then 1 else  0 end)/count(*)as 及格率,
sum(case when s_score>=70 and s_score<80 then 1 else  0 end)/count(*) as 中等率,
sum(case when s_score>=80 and s_score<90 then 1 else  0 end)/count(*) as 优良率,
sum(case when s_score>=90  then 1 else  0 end)/count(1) as 优秀
from score,course
where score.c_id=course.c_id
group by c_id

上面的答案主要用到 case when  then else,有些类似if else 判断语句，case when 后面接的是匹配项，如果匹配就返回then 后面的内容，如果不匹配那么就返回else 后面的内容

如case the s_score>=60 then 1 else 0 end ，即按照group 和where 里的语句依次遍历s_score,如果>=60则返回1累积到外层函数sum里，否则返回0

我最开始的做法如下，不知道这个为什么不对，好奇怪，我的思路是依次统计各个分数段的人数并且形成一个表，然后用这个表的count这一栏数据统计我们的各种率，但是却在不断报错，不知道为什么。

select score.c_id,course.c_name,max(score.s_score) as '最高分',min(score.s_score) as '最低分',avg(score.s_score) as '平均分',
a.count(*)/count(*) as 及格率,
b.count(*)/count(*) as 中等率,
c.count(*)/count(*) as 优良率,
d.count(*)/count(*) as 优秀率
 
from score,course,
(select c_id,count(*)
from score
where s_score >=60
group by c_id
) as a
,
(select c_id,count(*)
from score
where s_score >=70 and s_score <80
group by c_id
) as b
,
(select c_id,count(*)
from score
where s_score >=80 and s_score <90
group by c_id) as c
,
(select c_id,count(*)
from score
where s_score >=90 
group by c_id ) as d
 
where student.s_id=score.s_id
group by c_id

19、按各科成绩进行排序，并显示排名(实现不完全)2

select * ,(select count(distinct s_score)+1 from score B where B.c_id = A.c_id and B.s_score > A.s_score) as 'rank' 
from score A 
order by c_id,rank

 distinct 是指可以并列，删去的话那就是不能并列，会出现null值

 20、查询学生的总成绩并进行排名

select *,(select count(总分)+1 from (select s_id,sum(s_score) as'总分'
from score 
group by s_id )as b
where a.总分<b.总分) as '排名'
from(select s_id,sum(s_score) as'总分'
from score 
group by s_id )as a
order by 排名

这个是错的，跑不出来，但是我觉得思路是没问题的，不知道为啥。

主要思路就是，先求出来学生号和总分的表，然后再将这个表作为查询的表。再按照19题写排名的方法去套一下。

知道为啥错了，排名不能命名为rank，换个名字就可以了，以上答案是正确的。

21、查询不同老师所教不同课程平均分从高到低显示

SELECT course.c_id,c_name,t_id,avg(s_score) as'排名'
from course inner join score on course.c_id=score.c_id
group by course.c_id
order by 排名 desc

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

select s_id,c_id,s_score,(select count(distinct s_score)+1
from score b
where b.c_id=a.c_id and b.s_score>a.s_score
) as 'rk'
from score a

这个代码有问题，只能查出来对应的排名，末尾用个where rk between 2 and 3 就会出现查找全部为空值

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

和18题完全一致，但是我还是不理解为什么我创建新表统计不同分段学生人数的方法不行

select course.c_name, course.c_id,
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 score sc left join course
on sc.c_id = course.c_id
group by sc.c_id;

24、查询学生平均成绩及其名次

select *,(select count(distinct 平均分)+1 from (select s_id,avg(s_score) as'平均分'from score group by s_id ) as b where a.平均分<b.平均分 ) as 'rk'
from (select s_id,avg(s_score) as'平均分'from score group by s_id ) as a
order by 平均分 desc

25、查询各科成绩前三名的记录

            -- 1.选出b表比a表成绩大的所有组

            -- 2.选出比当前id成绩大的 小于三个的

这问题啥意思，看不懂

26、查询每门课程被选修的学生数

select c_id, count(*)
from score
group by c_id

  27、查询出只有两门课程的全部学生的学号和姓名

select student.s_id,s_name
from (select *
from (select s_id, count(*) as 'cnt'
from score
group by s_id) as a
where a.cnt like 2)as a1 inner join student on student.s_id=a1.s_id

 28、查询男生、女生人数

select s_sex,count(s_sex) as '总数'
from student 
group by s_sex

29、查询名字中含有"风"字的学生信息

select *
from student 
where s_name like '%风%'

 30、查询同名同性学生名单，并统计同名人数

SELECT s_name,s_sex,COUNT(*) 
FROM student 
GROUP BY s_name,s_sex 
HAVING COUNT(*)>1

  31、查询1990年出生的学生名单

select *
from student 
where year(s_birth)='1990'

 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

select c_id,avg(s_score)
from score
group by c_id
order by avg(s_score) desc,c_id asc

 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

select student.s_id,s_name,avg(s_score)
from student inner join score on student.s_id=score.s_id
group by s_id
having avg(s_score)>=85

 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数

select s_name,s_score
from student,score,course
where student.s_id=score.s_id and score.c_id=course.c_id and course.c_name='数学' and score.s_score<60

 35、查询所有学生的课程及分数情况

select s_name,c_id,s_score
from student,score
where student.s_id=score.s_id 

  36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数

select s_name,c_name,s_score
from student,score,course
where student.s_id=score.s_id and score.c_id=course.c_id and s_score>=70

 37、查询不及格的课程

select s_name,c_name,s_score
from student,score,course
where student.s_id=score.s_id and score.c_id=course.c_id and s_score<=60

38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名 

select student.s_id,s_name
from student,score
where student.s_id=score.s_id and score.c_id='01' and s_score>=80

 39、求每门课程的学生人数

select c_id,count(*)
from score
group by c_id

 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

select student.*,max(s_score)
from student,score,teacher,course
where student.s_id=score.s_id and teacher.t_id=course.t_id and teacher.t_name='张三' and score.c_id=course.c_id
group by student.s_id

 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

SELECT
    s1.*
FROM Score as s1 INNER JOIN Score as s2 ON s1.s_id = s2.s_id AND s1.c_id <> s2.c_id AND s1.s_score = s2.s_score;

 42、查询每门功成绩最好的前两名

select sa.c_id, sa.s_id
  from score sa
  left join score sb
    on sa.c_id = sb.c_id
   and sa.s_score < sb.s_score
 group by sa.c_id, sa.s_id
having count(sa.c_id) < 2
​

我又不会了，好难过qwq（写于4.10)

计数count 在select里不写好像也可以用，因为having 的执行顺序比select靠前(写于4.12）

 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select c_id,count(s_score) 
from score 
group by c_id 
having count(s_score)>5
order by count(s_score) desc,c_id asc

 44、检索至少选修两门课程的学生学号

select s_id,count(c_id) as '所修课程数'
from score
group by s_id
having 所修课程数>2
							

 45、查询选修了全部课程的学生信息

SELECT s_id,COUNT(c_id) 
FROM score
GROUP BY s_id HAVING COUNT(c_id)=(SELECT COUNT(c_id) FROM course)

 46、查询各学生的年龄

select 2023-s_birth as age
from student

 47、查询本周过生日的学生

SELECT s_name
FROM Student
WHERE WEEK(s_birth) = WEEK(CURRENT_DATE)

 48、查询下周过生日的学生

SELECT s_name
FROM Student
WHERE WEEK(s_birth) = WEEK(CURRENT_DATE)+1

 49、查询本月过生日的学生

SELECT s_name
FROM Student
WHERE month(s_birth) = month(CURRENT_DATE)

 50、查询下月过生日的学生

SELECT s_name
FROM Student
WHERE month(s_birth) = month(CURRENT_DATE)+1