数据库课后作业

首先设计4张表：“student”、“sc”、“course”、“teacher”




再往这4张表里填入数据：





现在开始做题目：
– 1.查询姓“朱”的学生名单

select * from student where sname like '朱%'
1

– 2.查询同名同性学生名单，并统计同名人数

select sname,ssex,count(*) from student group by sname,ssex having count(*) >= 2
1

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

select cid,avg(score) as score from sc group by cid order by score asc,cid desc
1

– 4.查询平均成绩大于85的所有学生的学号、姓名和平均成绩
– 通关关联查询

select s.sid,s.sname,avg(score) from sc inner join student s on s.sid = sc.sid GROUP BY sid,s.sname having avg(score) > 85 
1

– 通过子查询

select s.sid,s.sname,avg(score) 
from student s,sc 
where s.sid = sc.sid and sc.sid in (
    select sc.sid 
    from sc 
    GROUP BY sc.sid 
    having avg(score) > 85
)
GROUP BY s.sid,s.sname
1
2
3
4
5
6
7
8
9

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

select s.sname,sc.score
from course c inner join sc on c.cid = sc.cid inner join student s on s.sid = sc.sid
where c.cname = '睡觉' and sc.score < 60
1
2
3

– 通过子查询

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 = '睡觉'
)
1
2
3

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

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
1

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

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
1
2

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

select cid,count(*) from sc group by cid
1

– 延伸：查询每门课程（要求显示名称）被选修的学生数

select c.cid,c.cname,count(*) from sc inner join course c on sc.cid = c.cid group by c.cid,c.cname
1

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

select t.tname,c.cname,avg(sc.score) from
teacher t inner join course c on t.tid = c.tid
inner join sc on sc.cid = c.cid
group by t.tname,c.cname order by avg(sc.score) desc
1
2
3
4

– 10.按各科平均成绩从低到高和及格率的百分数从高到低排序

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
1
2
3
4
5

– 11.查询和“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)
)
1
2
3
4
5
6
7

– 12.查询学过“黄观”老师所教的所有课的同学的学号、姓名

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(c.cid) from course c inner join teacher t on c.tid = t.tid where t.tname ='黄观')
1
2
3

– 13.把“SC”表中“黄观”老师教的课的成绩都更改为此课程的平均成绩

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 ='黄观')
1
2
3

– 14.查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名

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
)
1
2
3

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

select * from student where 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 = '黄观')
) 
1
2
3
4

– 16.查询平均成绩大于60的同学的学号和平均成绩

select sc.sid,avg(sc.score) from sc group by sc.sid having avg(sc.score)>60
1