数据库查询题目11-20

数据库查询题目11-20

11　用子查询实现查询选修“高等数学”课的全部学生的所有课程总成绩
12　请用两种方法实现：查找所有成绩都在68分以上的学生姓名
13　查找至少2门成绩在80分以上的学生姓名
14　查询至少有一门课与张三同学所学相同的同学的学号和姓名
15　没有选修“数学”课的学生的姓名
16　查询个人总成绩小于平均总成绩的学生姓名
17　用子查询实现张三在其各科成绩中最高分成绩所对应的课程号和成绩
18　找出张三的最高分和最低分以及对应的课程名
19　哪些学生的各科成绩均高于张三
20　按平均成绩从高到低显示所有学生的“数学”、“英语”、“语文”三门的课程成绩 (按如下形式显示:学生ID,高等数学,计算机数学,英语,有效课程数,有效平均分 )

11　用子查询实现查询选修“高等数学”课的全部学生的所有课程总成绩

select sid,sum(cmark) from  mark group by sid having sid in (
	select sid from mark where cid = (
		select cid from course where cname='高等数学'
	)
)
order by sid

12　请用两种方法（逻辑思路）实现：查找所有成绩都在68分以上的学生姓名

--方法一(直接做)：
select sname from student where sid in (
	select sid from mark group by sid having min(cmark)>68
)

--方法二（排除法）：
select distinct sname from student s join mark m on s.sid=m.sid where s.sid not in (
	select sid from mark where cmark<=68
)

13　查找至少2门成绩在80分以上的学生姓名

select sname from student s join mark m on s.sid=m.sid 
	where cmark>80 group by m.sid,sname having count(*) >=2

14　查询至少有一门课与张三同学所学相同的同学的学号和姓名
step1:先找张三的所选的课程cid
step2:在mark表中找出选课落在step1中的sid
step3:step2的sid换成sname,并去掉张三

select sid,sname from student where sid in(
	select sid from mark where cid in(
		select cid from mark m join student s on s.sid=m.sid 
		where sname='张三') ) and not sname='张三' 
order by sid

15　没有选修“数学”课的学生的姓名

select sname from student where sid not in (
	select sid from mark m join course c on m.cid=c.cid where cname='数学')

16　查询个人总成绩小于平均总成绩的学生姓名（没有学生缺考，学生选课相同）

select sname from student s join mark m on s.sid=m.sid 
	group by m.sid,sname having sum(cmark)<(
		select sum(cmark)/count(distinct sid) from mark 
	)

17　用子查询实现张三在其各科成绩中最高分成绩所对应的课程号和成绩

select cid,cmark from mark where sid=(
	select sid from student where sname='张三'
) and cmark=(
		select max(cmark) from mark where sid = (
			select sid from student where sname='张三'
		)
)

18　找出张三的最高分和最低分以及对应的课程名

select cname,cmark from mark m join course c on m.cid=c.cid 
where sid = (select sid from student where sname='张三') 
and ((cmark>=all(select cmark from mark m join student s on m.sid = s.sid where sname='张三')) 
or (cmark <= all(select cmark from mark m join student s on m.sid = s.sid where sname='张三')))
--或
select cname,cmark from mark m join course c on m.cid=c.cid 
where sid = (select sid from student where sname='张三') 
and ((cmark=(select max(cmark) from mark m join student s on m.sid = s.sid where sname='张三')) 
or (cmark =(select min(cmark) from mark m join student s on m.sid = s.sid where sname='张三')))
--或
select cname,cmark from mark m join course c on m.cid=c.cid 
where sid = (select sid from student where sname='张三') 
and cmark in ((select max(cmark) from mark m join student s on m.sid = s.sid where sname='张三') 
union (select min(cmark) from mark m join student s on m.sid = s.sid where sname='张三'))

19　哪些学生的各科成绩均高于张三

--排除有成绩比张三低或等的学生
select distinct sid from mark where sid not in (
select y.sid from mark x join mark y on x.cid=y.cid 
where x.sid=(select sid from student where sname='张三') 
and y.cmark<=x.cmark )

20　按平均成绩从高到低显示所有学生的“数学”、“英语”、“语文”三门的课程成绩 (按如下形式显示:学生ID,高等数学,计算机数学,英语,有效课程数,有效平均分 )

select sid 学生id,
(select cmark from mark m join course c on m.cid=c.cid where cname='数学' and sid=y.sid ) 数学,
(select cmark from mark m join course c on m.cid=c.cid where cname='语文' and sid=y.sid) 语文,
(select cmark from mark m join course c on m.cid=c.cid where cname='英语' and sid=y.sid) 英语,
count(*) 有效课程数,avg(cmark) 有效平均分 
from  mark y group by sid order by sid