MySQL面试50题，四张表的查询

MySQL50题

01、 查询’01’课程比’02’课程成绩高的所有学生的学号；

#自连接
select 
	a.S_id ,s.Sname
from
	sc a,sc b,student s
where
		a.C_id = 01
and b.C_id = 02
and a.S_id = b.S_id
and s.S_id = a.S_id
and a.score > b.score

#2.长形数据变成宽型数据
select
		s.S_id
from
		(select 
			a.S_id,
			max(case when a.C_id = 01 then a.score end) s01,
			max(case when a.C_id = 02 then a.score end) s02
		from
			sc a
		group by
			a.S_id) t ,student s
where
		t.s01 > t.s02
and t.S_id = s.S_id

02、 查询平均成绩大于60分的同学的学号和平均成绩

select
	a.S_id,
	(select s.Sname from student s where s.S_id = a.S_id) S_name,
	avg(a.score)
from
	sc a
group by S_id
having avg(a.score) > 60

在这里要注意连接表时。连接条件where要放到分组之前

#两个表连接
select
	a.S_id,
	s.Sname,
	avg(a.score)
from
	sc a, student s
where
	s.S_id = a.S_id
group by S_id
having avg(a.score) > 60

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

因为有一个没有成绩的，所有内连接要改成外连接；

#两张表 ，用函数有分组
	
select
	s.S_id,
	s.Sname,
	count(sc.C_id) cnt,
	ifnull(sum(sc.score), 0) sum
from
	sc 
right join
	student s
on
	s.S_id = sc.S_id
group by
	sc.S_id

05、 查询没学过“Li Pengfei”老师课的同学的学号、姓名；

select * from student where S_id not in(
select
	a.S_id
from
	sc a ,course b ,teacher c
where
		c.T_id = b.T_id
and	b.C_id = a.C_id
and c.Tname = 'Li Pengfei')
#2这种查询效率更高
select * from student where S_id not exists(
	select 1 from
		(select
			a.S_id
		from
			sc a ,course b ,teacher c
		where
				c.T_id = b.T_id
		and	b.C_id = a.C_id
		and c.Tname = 'Li Pengfei')	t
	where t.S_id = student.S_id);

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

这个就有点类似于第一题的解题思路，同样有两种方法；

select
	s.Sname ,s.S_id
from
	sc a ,sc b ,student s
where 
		a.C_id = 01
and b.C_id = 02
and a.S_id = b.S_id
and b.S_id = s.S_id
	

07、 查询学过“Li Pengfei”老师所教的所有课的同学的学号、姓名；

	d.S_id,
	d.Sname
from
	sc a ,course b ,teacher c ,student d
where
		c.T_id = b.T_id
and	b.C_id = a.C_id
and a.S_id = d.S_id
and c.Tname = 'Li Pengfei'

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

select
	s.S_id,
	s.Sname
from
	sc a, sc b ,student s
where
		a.C_id = 01
and b.C_id = 02
and b.score < a.score
and a.S_id = b.S_id
and b.S_id = s.S_id

09、 查询所有课程成绩小于80分的同学的学号、姓名；(查看两段sql的区别)

select
			a.S_id ,
			(select s.Sname from student s where s.S_id = a.S_id) S_name
from
			sc a
		where a.score < 80
		group by 
			a.S_id
		having count(a.C_id) = 3

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

select
			a.S_id ,
			(select s.Sname from student s where s.S_id = a.S_id) S_name
from
			sc a
		group by 
			a.S_id
		having count(a.C_id) < 3

select
	s.S_id,
	s.Sname,
	count(sc.C_id) cnt
from
	sc 
right join
	student s
on
	s.S_id = sc.S_id
group by
	sc.S_id
having count(sc.C_id) < 3

11、 查询至少有一门课与学号为“07”的同学所学相同的同学的学号和姓名；

select s.S_id ,s.Sname from student s where s.S_id in
(select distinct b.S_id from sc b where C_id in
(select
	a.C_id
from
	sc a 
where
	a.S_id = 07) )

***12、 查询学过学号为“07”的同学所有门课的其他同学学号和姓名；

***15、 删除学习“Li Pengfei”老师课的SC表记录；

***16.向SC表插入一些记录，这些记录要求符合条件：没有上过编号“03”课程的同学学号，“02”，以及“02”课的平均成绩

17、 按平均成绩从高到低显示所有学生的“数学”，“语文”，“英语”三门的课程成绩，按如下形式显示：

有显示为null的问题

select
	a.S_id student_id,
	max(case when a.C_id = 02 then a.score end) math,
	max(case when a.C_id = 01 then a.score end) chinese,
	max(case when a.C_id = 03 then a.score end) english,
	sum(a.C_id) course_sum,
	avg(score) avg_score
from
 sc a
group by 
	a.S_id
order by
	avg(score) desc;

补充一个开窗函数的用法

#开窗函数
select 
		avg(a.score) over(patition by a.S_id) avg_s
from
		sc a

18、 查询各科成绩最高和最低分，以如下形式显示：cours_id,max,min

select
		a.C_id cours_id,
		max(score) max,
		min(score) min 
from
		sc a
group by
		a.C_id

***19、 按各科平均成绩从低到高和及格率的百分数从高到低顺序

在这里插入代码片

20、 查询如下课程平均成绩和及格率的百分数（用“1行”显示）：math(01),chinese(02),english(03);

select C_id ,avg(score) , (select (round(sum(case when score >= 60 then 1 else 0 end)/count(1),2)) from sc where C_id = 01) jig 
from sc
where C_id = 01

union

select C_id ,avg(score) , (select (round(sum(case when score >= 60 then 1 else 0 end)/count(1),2)) from sc where C_id = 02) jig 
from sc
where C_id = 02

union

select C_id ,avg(score) , (select (round(sum(case when score >= 60 then 1 else 0 end)/count(1),2)) from sc where C_id = 03) jig 
from sc
where C_id = 03

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

select
		c.T_id,
		c.Tname,
		round(avg(a.score)) avg_C
from
		sc a ,course b ,teacher c
where
		a.C_id = b.C_id
and b.T_id = c.T_id
group by
		b.C_id
order by
		avg_C desc

***22、 查询如下课程成绩从第3名到第6名的学生成绩单：math(01),chinese(02),english(03)-student_id,student_name,math,chinese,english,avg_score; (本题有争议)

23、 统计列印各科成绩，各分数段人数：课程ID，课程名称，[100-85],[85-70],[70-60],[-60]

思路：先把前两列建出来，然后往后一列一列添加

select
	a.C_id,
	a.Cname,
	sum(case when score > 85 and score <= 100 then 1 else 0 end) '[100-85]',
	sum(case when score > 70 and score <= 85 then 1 else 0 end) '[85-70]',
	sum(case when score > 60 and score <= 70 then 1 else 0 end) '[70-60]',
	sum(case when score >0 and score <= 60 then 1 else 0 end) '[60-0]'
from
	course a
left join
	sc b
on
	b.C_id = a.C_id
group by 
	a.C_id ,a.Cname

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

***25、 查询各科成绩前三名的记录：（不考虑成绩并列情况）

***37、 查询不及格的课程，显示学号、姓名、课程号、成绩；

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

select
	C_id,
	count(S_id) cnt
from
	sc
group by C_id
order by cnt desc ,C_id asc;

45、 检索至少选修3门课程的学生学号；

select S_id ,count(C_id) cnt
from sc
group by S_id
having cnt>= 3

***46、 查询全部学生都选修的课程的课程号和课程名；

在这里插入代码片

***47、 查询没学过“Li Pengfei”老师所授的任一门课程的学生姓名；

在这里插入代码片