Orcle数据库查询练习复习：四

一、题目

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

select * from course c,mark m where c.cid=m.cid and sid =(select sid from student where sname ='张三') 
and
(cmark >=all(select cmark from course c,mark m where c.cid=m.cid and sid =(select sid from student where sname ='张三'))
or
cmark <=all(select cmark from course c,mark m where c.cid=m.cid and sid =(select sid from student where sname ='张三')))

2.那些学生的各科成绩均高于张三

step1、找出有一门课成绩低于张三这门课成绩的人
step1.1、 select sid from student where sname='张三'
step1.2 select a.sid from mark a,mark b
where a.cid=b.cid and b.sid=1002 and a.cmark<b.cmark
step2、paichu step1,剩下的就是目的
step2.1、在成绩表中排除、找出剩下的sid
select sid from mark where sid not in (step1.2)
step2.2 在学生表中兑换成姓名
select sname from student where sid in(step2.1)

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

select sid 学生id ,(select cmark from mark where cid=
(select cid from course where cname='数学') and sid=sc.sid) 数学 ,(select cmark from mark where cid=
(select cid from course where cname='英语')and sid=sc.sid) 英语,count(*) 有效课程数,avg(cmark) 有效均分 
from mark sc group by sid

4.查询只选了数学和英语课的学生姓名

step1、select cid from course where cname='数学'
step1、select cid from course where cname='英语'
step3 select from mark a,mark b where a.cid=(step1)and b.cid=(step2) and a.sid=b.sid
step4 select sid from mark where sid in (step3) group by sid hving count(*)=2

5.找出计算机专业中均分最高的男生姓名

select sid from student where smajor='计算机' and ssex='男'
select sid,avg(cmark) amk from mark where sid in (step1) 
group by sid
select max(amk) from (step2)
select sid from (step2) where amk =(step3)

6.找出数学和英语均分最高的男生姓名

step1 select cid from course where cname in('数学','英语')
step2 select cid from student where ssex='男'
step3 select sid from mark where sid in(step2) and cid in (step1) group by sid

7.找出个人均分大于总均分（所有人所有课程的均分）的学生姓名

step1 select avg(cmark) from mark 
step2 select sid from mark group by sid having avg(cmark)>(step1)

8.上海地区哪门课的均分比福建差

step1 select sid from student where snativeplace='上海'
step2 select cid,avg(cmark)from mark where sid in=(step1) group by cid
step3 select sid from student where snativeplace='福建'
step4 select cid,avg(cmark) from mark where sid in=(step3) group by cid
step5 select * from mark a,mark b
where a.cid=(step2)and b.cid=(step4)and a.cid=b.cid and a.cid<b.cid
/*select * from mark a,mark b 
where a.sid='10001'and b.cid='10002' and a.cid=b.cid and a.cid>b.cid*/

9.　求各门课程去掉一个最高分和最低分后的平均分

step1 select * from mark where cid='2001' order by cmark
step2 select cname,(
select avg(smark) from( 
select sid,cid,cmark,rownum r from (
select * from mark m where cid='2001' order by cmark)
where r!=1 and r!=(select count(*)from mark m where cid='2001'))
j）均分 from course c

10.求2001课程去掉一个最高分和最低分后的平均分

step1 select * from mark where cid='2001' order by cmark
step2 select cname,(
select avg(smark) from( 
select sid,cid,cmark,rownum r from (
select * from mark m where cid='2001' order by cmark)
where r!=1 and r!=(select count(*)from mark m where cid='2001'))
）from course c