Nreal
2023-11-15
目录

SQL编程50题

# 数据表

学生表：

create table student(s_id varchar(10),s_name varchar(10),s_birth datetime,s_sex varchar(10));
insert into student values('01' , '赵雷' , '1990-01-01' , '男');
insert into student values('02' , '钱电' , '1990-12-21' , '男');
insert into student values('03' , '孙风' , '1990-05-20' , '男');
insert into student values('04' , '李云' , '1990-08-06' , '男');
insert into student values('05' , '周梅' , '1991-12-01' , '女');
insert into student values('06' , '吴兰' , '1992-03-01' , '女');
insert into student values('07' , '郑竹' , '1989-07-01' , '女');
insert into student values('08' , '王菊' , '1990-01-20' , '女');
1
2
3
4
5
6
7
8
9

课程表：

create table course(c_id varchar(10),c_name varchar(10),t_id varchar(10));
insert into course values('01' , '语文' , '02');
insert into course values('02' , '数学' , '01');
insert into course values('03' , '英语' , '03');
1
2
3
4

教师表：

create table teacher(t_id varchar(10),t_name varchar(10));
insert into teacher values('01' , '张三');
insert into teacher values('02' , '李四');
insert into teacher values('03' , '王五');
1
2
3
4

分数表：

create table score(s_id varchar(10),c_id varchar(10),s_score decimal(18,1));
insert into score values('01' , '01' , 80);
insert into score values('01' , '02' , 90);
insert into score values('01' , '03' , 99);
insert into score values('02' , '01' , 70);
insert into score values('02' , '02' , 60);
insert into score values('02' , '03' , 80);
insert into score values('03' , '01' , 80);
insert into score values('03' , '02' , 80);
insert into score values('03' , '03' , 80);
insert into score values('04' , '01' , 50);
insert into score values('04' , '02' , 30);
insert into score values('04' , '03' , 20);
insert into score values('05' , '01' , 76);
insert into score values('05' , '02' , 87);
insert into score values('06' , '01' , 31);
insert into score values('06' , '03' , 34);
insert into score values('07' , '02' , 89);
insert into score values('07' , '03' , 98);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# 1. 查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号

--子查询出选了01和02课程的s_id与s_score
select a.s_id from
(select s_id,s_score from score where c_id='01') a
inner join
(select s_id,s_score from score where c_id='02') b
on a.s_id=b.s_id
where a.s_score>b.s_score
1
2
3
4
5
6
7

如果还需要查出学生详细信息，与课程的分数：

select st.*,a.s_score '课程1',b.s_score '课程2'
from student st
inner join
(select s_id,s_score from score where c_id='01') a
on st.s_id=a.s_id
inner join
(select s_id,s_score from score where c_id='02') b
on st.s_id=b.s_id
where a.s_score>b.s_score
1
2
3
4
5
6
7
8
9

# 2. 查询平均成绩大于60分的学生的学号和平均成绩

select s_id,avg(s_score)
from score
group by s_id
having avg(s_score)>60
1
2
3
4

查询平均分低于60

--查询不出分数为null的
select * 
from student 
where s_id not in
(select s_id from score group by s_id having avg(s_score)>60)

--正确写法
select st.s_id,avg(IFNULL(sc.s_score,0))
from student st
left join score sc
on st.s_id=sc.s_id
group by s_id
having avg(sc.s_score) is null
or avg(sc.s_score)<60
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 3. 查询所有学生的学号、姓名、选课数、总成绩

select st.s_id, st.s_name, count(sc.c_id), sum(sc.s_score)
from student st
join score sc
on st.s_id = sc.s_id
group by sc.s_id
1
2
3
4
5

使用了聚合函数就一定要用 group by吗？

https://juejin.cn/post/7053966777088213005?searchId=20231021225607E5095B1931517923394A

# 5.查询没学过“张三”老师课的学生的学号、姓名

方法一：子查询

  1. 从teacher查询出张三的t_id；
  2. 从course查询出对应的c_id；
  3. 从score查询出对应的s_id；
  4. 从student查出不在s_id的学生;
select s_id,s_name from student where s_id not in(
	select s_id from score where c_id = (
		select c_id from course where t_id = (
			select t_id from teacher where t_name='张三'
		)
	)
)
1
2
3
4
5
6
7

方法二：表连接

新建一张表，包含score和t_id,t_name

select s_id,s_name from student where s_id not in(
	select s_id from score s
	inner join course c on s.c_id = c.c_id
	inner join teacher t on c.t_id = t.t_id
	where t.t_name = '张三'
)
1
2
3
4
5
6

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

方法一：子查询

select s_id,s_name from student where s_id in(
	select s_id from score s
	inner join course c on s.c_id = c.c_id
	inner join teacher t on c.t_id = t.t_id
	where t.t_name = '张三'
)
1
2
3
4
5
6

方法二：表连接

select st.s_id,st.s_name,s.c_id,c.c_name,t.t_name
from student st
inner join score s on st.s_id = s.s_id
inner join course c on s.c_id = c.c_id
inner join teacher t on c.t_id = t.t_id
where t.t_name = '张三'
1
2
3
4
5
6

# 7.查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名

select * from student 
where s_id in
(select s_id from score where c_id=01)
and s_id in
(select s_id from score where c_id=02)
1
2
3
4
5

# 10.查询没有学全所有课的学生的学号、姓名

方法一：子查询（无法查出一门都没学的）

select s_id,s_name from student
where s_id in(
	select s_id from score
	group by s_id
	having count(distinct c_id) < (select count(distinct c_id) from course)
)
1
2
3
4
5
6

方法二：表连接

select st.*,sc.* from student st
left join score sc 
on st.s_id = sc.s_id
group by st.s_id
having count(distinct sc.c_id) < (select count(distinct c_id) from course)
1
2
3
4
5

# 11.查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名

先查询出01学号的学生所学的课程id，再查询哪些学生的课程在这个ids中；

select distinct s_id from score
where c_id in(
	select c_id from score
	where s_id = '01'
) and s_id != '01'
1
2
3
4
5

方法一：表连接

select a.s_id, a.s_name from student a
inner join (
	select distinct s_id from score
	where c_id in(
		select c_id from score
		where s_id = '01'
	) and s_id != '01'
) b
on a.s_id = b.s_id
1
2
3
4
5
6
7
8
9

方法二：in

select s_id, s_name from student
where s_id in(
	select distinct s_id from score
	where c_id in(
		select c_id from score
		where s_id = '01'
	) and s_id != '01'
)
1
2
3
4
5
6
7
8

# 12.查询和“01”号同学所学课程完全相同的其他同学的学号

查询出修过01同学没修过的课的同学id

select distinct s_id from score
where c_id not in(
	select c_id from score where s_id='01'
)
1
2
3
4

查询出与01同学课程数相同的同学id

select s_id from score
where s_id!='01'
group by s_id
having count(distinct c_id) = (
	select count(distinct c_id) from score where s_id='01'
)
1
2
3
4
5
6
select ...
where s_id in (1)
and s_id not in (2)
1
2
3

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

方法一：表连接

select * from student where s_id not in(
	select s.s_id from score s
	inner join course c on s.c_id=c.c_id
	inner join teacher t on c.t_id=t.t_id
	where t.t_name = '张三'
)
1
2
3
4
5
6

方法二：子查询

select * from student st where st.s_id not in(
	select s_id from score s where s.c_id = (
		select c_id from course c where c.t_id = ( 
			select t_id from teacher where t_name='张三'
		)
	)
)
1
2
3
4
5
6
7

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

查询出有两门不及格课程的学生id

select s_id from score where s_score<60
group by s_id having count(distinct c_id)>=2
1
2

用了avg函数，底下必须group by，否则漏查

select st.s_id,st.s_name,avg(s.s_score) from student st
inner join score s on st.s_id=s.s_id
where st.s_id in (
	select s_id from score where s_score<60
	group by s_id having count(distinct c_id)>=2
)
group by s_id,s_name
1
2
3
4
5
6
7

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

select st.*,s.s_score from student st
inner join score s 
on st.s_id = s.s_id
where s.c_id='01' and s.s_score<60
order by s.s_score desc
1
2
3
4
5

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

select s_id, avg(s_score),
case when c_id='01' then s_score else null end '语文',
max(case when c_id='02' then s_score else null end) '数学',
max(case when c_id='03' then s_score else null end) '英语'
from score
group by s_id
order by avg(s_score) desc
1
2
3
4
5
6
7

为什么需要max？

不加max，只是新增两个列，给第一个列赋予值后，后两列无法取到值；可以取每列的最大值给新增列赋值；

# 18.查询各科成绩最高分、最低分和平均分

以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率；

select s.c_id, c.c_name, max(s.s_score), min(s.s_score), avg(s.s_score),
sum(case when s.s_score>=60 then 1 else 0 end)/count(s_id) '及格率',
sum(case when s.s_score>=90 then 1 else 0 end)/count(s_id) '优秀率'
from score s
inner join course c on s.c_id = c.c_id
group by c.c_id
1
2
3
4
5
6

# 19.按各科成绩进行排序，并显示排名

select s_id, c_id, s_score, row_number() over (order by s_score desc)
from score

//row_number() 12345
//dense_rank() 1223
//rank() 1224
1
2
3
4
5
6

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

select * from(
	select st.s_id, sc.c_id,
	row_number() over(partition by c_id order by s_score desc) m
	from score sc inner join student st
	on sc.s_id = st.s_id
)a where m in(2,3)
1
2
3
4
5
6

partition by 分组列

# 23.使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称

select c.c_id, c.c_name,
sum(case when sc.s_score<=100 and sc.s_score>85 then 1 else 0 end) '[100-85]',
count(case when sc.s_score<=85 and sc.s_score>70 then 1 else null end) '[85-70]'
from score sc inner join course c
on sc.c_id = c.c_id
group by c.c_id,c.c_name
1
2
3
4
5
6

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

select s_id, avg(s_score), row_number() over(order by avg(s_score) desc)
from score 
group by s_id
1
2
3

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

select c_id,
max(case when m=1 then s_score else null end) '第一',
max(case when m=2 then s_score else null end) '第二',
max(case when m=3 then s_score else null end) '第三'
from (
	select c_id,s_score,row_number() over(partition by c_id order by s_score desc) m
	from score sc inner join student st
	on sc.s_id = st.s_id
) a
where m in (1,2,3)
group by c_id
1
2
3
4
5
6
7
8
9
10
11

# 26.查询出只有两门课程的全部学生的学号和姓名

select st.s_id, st.s_name, count(sc.c_id)
from student st inner join score sc
on st.s_id = sc.s_id
group by st.s_id, st.s_name
having count(sc.c_id) = 2
1
2
3
4
5

# 31.查询1990年出生的学生名单

select * from student 
where year(s_birth)=1990

select month('2019-09-27')
select month(curdate())
1
2
3
4
5

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

select c_id, avg(s_score) from score
group by c_id
order by avg(s_score), c_id desc
1
2
3

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

select st.s_id,st.s_name,
max(case when c.c_name='语文' then s.s_score else null end) '语文',
max(case when c.c_name='数学' then s.s_score else null end) '数学',
max(case when c.c_name='英语' then s.s_score else null end) '英语'
from score s
inner join course c on s.c_id = c.c_id
inner join student st on s.s_id = st.s_id
group by st.s_id,st.s_name
1
2
3
4
5
6
7
8

# 40.查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩

select st.s_name, sc.s_score
from score sc
inner join course c on sc.c_id = c.c_id
inner join teacher t on c.t_id = t.t_id
inner join student st on sc.s_id = st.s_id
where t.t_name = '张三'
order by sc.s_score desc limit 0,1
1
2
3
4
5
6
7
基础篇

基础篇→

Theme by Vdoing | Copyright © 2021-2024