【数据库】软件测试之MySQL数据库练习题目

有表如下：

Student 学生表

SC 成绩表

Course 课程表

Teacher 老师表

每个学生可以学习多门课程，每一个课程都有得分，每一门课程都有老师来教，一个老师可以教多个学生

1、查询姓‘朱’的学生名单 

select * from Student where sname like '朱%'

2、查询同名字同性别学生名单，并统计同名人数

select sname,ssex,count(*) from Student GROUP BY sname,ssex having count(*) >= 2

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

select cid,avg(score) from SC GROUP BY cid ORDER BY avg(score) ASC,cid DESC

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

-- 子查询
select SC.sid, Student.sname , avg(SC.score) from SC,Student where SC.sid = Student.sid GROUP BY SC.sid,Student.sname HAVING avg(SC.score) > 85
-- 关联查询
-- Student s ----s是表Student的别名
select  SC.sid, s.sname, avg(SC.score) from SC INNER JOIN Student s on SC.sid = s.sid GROUP BY SC.sid,s.sname HAVING avg(SC.score) > 85;
-- 或 :
select  s.sid, s.sname, avg(score) from SC INNER JOIN Student s on SC.sid = s.sid GROUP BY sid,s.sname HAVING avg(score) > 85;

5、查询课程名称为“睡觉”，且分数低于60的学生姓名和分数

-- 方法1、通过关联查询
select s.sname,SC.score
from Course c INNER JOIN SC on c.cid = SC.cid INNER JOIN Student s on SC.sid = s.sid
where c.cname = '睡觉' and SC.score < 60

-- 方法2、通过子查询
select s.sname,SC.score from Student s,SC where s.sid = SC.sid and SC.score < 60 and SC.cid in (
select c.cid from Course c where c.cname = '睡觉')

6、查询所有学生的选课情况

-- ⚠️ 以学生为主 所以是左查询
select s.sname,c.cname from Student s LEFT JOIN SC on s.sid = SC.sid INNER JOIN Course c on SC.cid = c.cid
-- 或者第二个用 left join
select s.sname,c.cname from Student s LEFT JOIN SC on s.sid = SC.sid LEFT JOIN Course c on SC.cid = c.cid

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

-- (1)、用having
select s.sname,c.cname,SC.score from Student s INNER JOIN SC on s.sid = SC.sid INNER JOIN Course c on SC.cid = c.cid having SC.score > 70
-- (2)、用where
select s.sname,c.cname,SC.score from Student s INNER JOIN SC on s.sid = SC.sid INNER JOIN Course c on SC.cid = c.cid where SC.score > 70

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

select c.cid,c.cname,count(*) from SC INNER JOIN Course c on SC.cid = c.cid GROUP BY c.cid,c.cname

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

select t.tname,c.cname,avg(SC.score)
from Teacher t INNER JOIN Cource c on c.tid = t.tid 
INNER JOIN SC on SC.cid = c.cid 
GROUP BY t.tid c.cname 
ORDER BY avg(sc.score) desc

10、按照各科平均成绩从低到高和及格率百分比从高到低显示——select里面嵌套另外的select

-- 及格率 ： 这一科 及格人数 / 这一科目的总人数
-- ⚠️直接这样用结果不对：(select count(*) from SC where score >= 60) 因为没有办法和外面的select确认是同一个课程
-- where sc2.cid = SC.cid  ----这句表示 查询的是当前课程

select 
    SC.cid,
	avg(SC.score),
	(select count(*) from SC sc2 where sc2.cid = SC.cid and sc2.score >= 60)/count(*) 
from SC 
	GROUP BY SC.cid  
	ORDER BY SC.cid

11、查询和‘2’号的同学 学习的课程完全相同的其他同学的学号和姓名

-- 课程完全相同：课程在2号学习课程范围内 + 数量一样

--2同学:【a,b,c,d,e】
--1同学:【a,b,c,d,e,f】——1同学就不行，用下面步骤2去除它

-- 步骤1: 2号学生学过的课
select cid from SC where sid = 2

-- 步骤2: 没学过2号学生课的同学/学过课程 不在2号学过课程范围内 的人——比如上面的1同学学过f课程，不在这个范围内
select SC.sid ,SC.cid from SC where SC.cid not in (
	select SC.cid from SC where SC.sid = 2
)

-- 步骤3:剩下的都是学习过2号学生课 以内的人 
-- 可能少学 那就同时需要数量一致
select SC.sid from SC where SC.sid not in (
	select SC.sid from SC where SC.cid not in (
		select SC.cid from SC where SC.sid = 2
	)
)GROUP BY SC.sid having count(*) = (select count(*) from SC where SC.sid = 2)

-- 去除2号同学
select SC.sid from SC where SC.sid not in (
	select SC.sid from SC where SC.cid not in (
		select SC.cid from SC where SC.sid = 2
	)
) and SC.sid != 2 GROUP BY SC.sid having count(*) = (select count(*) from SC where SC.sid = 2)

-- 去除2号同学后的学生名字和学号
select s.sid,s.sname from Student s where s.sid in (
	select SC.sid from SC where SC.sid not in (
		select SC.sid from SC where SC.cid not in (
			select SC.cid from SC where SC.sid = 2
		)
	) and SC.sid != 2 GROUP BY SC.sid having count(*) = (select count(*) from SC where SC.sid = 2)
)

12、查询学过‘黄观’老师所教的所有课的同学的学号、姓名 ---包括 不止学过黄观老师的课

(1):黄瓜老师教的课：
select c.cid from Course c INNER JOIN Teacher t on c.tid = t.tid where t.tname = '黄观'
(2):完全学习过黄观老师课的人 可能不止黄观老师的课
select s.sid,s.sname from Student s where s.sid in(
  select SC.sid from SC where SC.cid in (
        select c.cid from Course c INNER JOIN Teacher t on c.tid = t.tid where t.tname = '黄观' 
    )GROUP BY SC.sid HAVING count(*) = (select count(*) from Course c INNER JOIN Teacher t on c.tid = t.tid where t.tname = '黄观')
 )

13、把 ‘SC’ 表中 ’黄观‘ 老师教的课的成绩都更改为此课程的平均成绩

-- 错误的解题思路：在查询该表的时候 去修改表中的数据，报错，不允许这样操作
update SC set score = (select avg(score) from SC)
-- ‼️引入临时表 t 把查询的平均成绩作为临时表t
update SC,(select SC.cid as cid,avg(SC.score) as score from SC group by SC.cid) t 
set SC.score = t.score
where SC.cid = t.cid 
and SC.cid in
(select c.cid from Course c INNER JOIN Teacher t on c.tid = t.tid where t.tname = '黄观')

14、查询 课程编号‘2’ 的成绩 比 课程编号‘1’ 课程低的所有同学的学号、姓名

-- 有两个课程 需要单独查询，所以设置 sc2 和 sc1---->sc2.cid = 2 and sc1.cid = 1
-- sc2.score < sc1.score
-- 需要是同一个同学：sc1.sid = sc2.sid
select * from Student where sid in (
select sc1.sid from SC sc2,SC sc1 where sc2.score < sc1.score and sc2.cid = 2 and sc1.cid = 1 and sc1.sid = sc2.sid
)

15、查询没学过“黄观“老师课的同学的学号、姓名

select s.sid,s.sname from Student s where s.sid not in(
  select SC.sid from SC where SC.cid in (
        select c.cid from Course c INNER JOIN Teacher t on c.tid = t.tid where t.tname = '黄观' )GROUP BY SC.sid 
)

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

select sid,avg(score) from SC GROUP BY sid HAVING avg(score) > 60