一、题目
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 ='张三'))orcmark <=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 bwhere a.cid=b.cid and b.sid=1002 and a.cmark
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.sidstep4 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 sidselect 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
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 cidstep3 select sid from student where snativeplace='福建'step4 select cid,avg(cmark) from mark where sid in=(step3) group by cidstep5 select * from mark a,mark bwhere a.cid=(step2)and b.cid=(step4)and a.cid=b.cid and a.cidb.cid*/
9. 求各门课程去掉一个最高分和最低分后的平均分
step1 select * from mark where cid='2001' order by cmarkstep2 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 cmarkstep2 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