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 =‘张三‘)))

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<b.cmarkstep2、paichu step1,剩下的就是目的step2.1、在成绩表中排除、找出剩下的sidselect sid from mark where sid not in (step1.2)step2.2 在学生表中兑换成姓名select sname from student where sid in(step2.1)

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

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

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)

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)

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.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*/

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

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