Oracle完整的试题

1.*********************************select a.* from (select * from sc a where a.cno='c001') a,(select * from sc b where b.cno='c002') bwhere a.sno=b.sno and a.score > b.score;*********************************select * from sc awhere a.cno='c001'and  exists(select * from sc b where b.cno='c002' and a.score>b.scoreand a.sno = b.sno)*********************************2.*********************************select sno,avg(score) from sc  group by sno having avg(score)>60;*********************************3.*********************************select a.*,s.sname from (select sno,sum(score),count(cno) from sc group by sno) a ,student s where a.sno=s.sno*********************************4.*********************************select count(*) from teacher where tname like '刘%';*********************************5.*********************************select a.sno,a.sname from student awhere a.sno not in (select distinct s.sno  from sc s,      (select c.*        from course c ,           (select tno             from teacher t             where tname='谌燕')t        where c.tno=t.tno) b  where s.cno = b.cno ) *********************************select * from student st where st.sno not in(select distinct sno from sc s join course c on s.cno=c.cnojoin teacher t on c.tno=t.tno where tname='谌燕')*********************************6.*********************************select st.* from sc a join sc b on a.sno=b.sno join student ston st.sno=a.snowhere a.cno='c001' and b.cno='c002' and st.sno=a.sno;*********************************7.*********************************select st.* from student st join sc s on st.sno=s.snojoin course c on s.cno=c.cnojoin teacher t on c.tno=t.tnowhere t.tname='谌燕'*********************************8.*********************************select * from student stjoin sc a on st.sno=a.snojoin sc b on st.sno=b.snowhere a.cno='c002' and b.cno='c001' and a.score < b.score*********************************9.*********************************select st.*,s.score from student stjoin sc s on st.sno=s.snojoin course c on s.cno=c.cnowhere s.score <60*********************************10.*********************************select stu.sno,stu.sname,count(sc.cno) from student stuleft join sc on stu.sno=sc.snogroup by stu.sno,stu.snamehaving count(sc.cno)<(select count(distinct cno)from course)===================================select * from student where sno in(select sno from         (select stu.sno,c.cno from student stu        cross join course c        minus        select sno,cno from sc))===================================*********************************11.*********************************select st.* from student st,(select distinct a.sno from (select * from sc) a,(select * from sc where sc.sno='s001') bwhere a.cno=b.cno) hwhere st.sno=h.sno and st.sno<>'s001'*********************************12.*********************************select * from scleft join student ston st.sno=sc.sno where sc.sno<>'s001'and sc.cno in(select cno from scwhere sno='s001')*********************************13.*********************************update sc c set score=(select avg(c.score)  from course a,teacher b                            where a.tno=b.tno                            and b.tname='谌燕'                            and a.cno=c.cno                            group by c.cno)where cno in(select cno from course a,teacher bwhere a.tno=b.tnoand b.tname='谌燕')*********************************14.*********************************select* from sc where sno<>'s001'minus(select* from scminusselect * from sc where sno='s001')*********************************15.*********************************delete from scwhere sc.cno in(select cno from course cleft join teacher t on  c.tno=t.tnowhere t.tname='谌燕' )*********************************16.*********************************insert into sc (sno,cno,score)select distinct st.sno,sc.cno,(select avg(score)from sc where cno='c002') from student st,scwhere not exists(select * from sc where cno='c002' and sc.sno=st.sno) and sc.cno='c002';*********************************17.*********************************select cno ,max(score),min(score) from sc group by cno;*********************************18.*********************************select cno,avg(score),sum(case when score>=60 then 1 else 0 end)/count(*) as 及格率from sc group by cnoorder by avg(score) , 及格率 desc*********************************19.*********************************select max(t.tno),max(t.tname),max(c.cno),max(c.cname),c.cno,avg(score) from sc , course c,teacher t where sc.cno=c.cno and c.tno=t.tnogroup by c.cnoorder by avg(score) desc*********************************20.*********************************select sc.cno,c.cname,sum(case  when score between 85 and 100 then 1 else 0 end) AS "[100-85]",sum(case  when score between 70 and 85 then 1 else 0 end) AS "[85-70]",sum(case  when score between 60 and 70 then 1 else 0 end) AS "[70-60]",sum(case  when score <60 then 1 else 0 end) AS "[<60]"from sc, course cwhere  sc.cno=c.cno group by sc.cno ,c.cname;*********************************21.*********************************select * from (select sno,cno,score,row_number()over(partition by cno order by score desc) rn from sc)where rn<4*********************************22.*********************************select cno,count(sno)from sc group by cno;*********************************23.*********************************select sc.sno,st.sname,count(cno) from student stleft join sc on sc.sno=st.snogroup by st.sname,sc.sno having count(cno)=1;*********************************24.*********************************select ssex,count(*)from student group by ssex;*********************************25.*********************************select * from student where sname like '张%';*********************************26.*********************************select sname,count(*)from student group by sname having count(*)>1;*********************************27.*********************************select sno,sname,sage,ssex from student t where to_char(sysdate,'yyyy')-sage =1988*********************************28.*********************************select cno,avg(score) from sc group by cno order by avg(score)asc,cno desc;*********************************29.*********************************select st.sno,st.sname,avg(score) from student stleft join sc on sc.sno=st.snogroup by st.sno,st.sname having avg(score)>85;*********************************30.*********************************select sname,score from student st,sc,course cwhere st.sno=sc.sno and sc.cno=c.cno and c.cname='Oracle' and sc.score<60*********************************31.*********************************select st.sno,st.sname,c.cname from student st,sc,course cwhere sc.sno=st.sno and sc.cno=c.cno;*********************************32.*********************************select st.sname,c.cname,sc.score from student st,sc,course cwhere sc.sno=st.sno and sc.cno=c.cno and sc.score>70*********************************33.*********************************select sc.sno,c.cname,sc.score from sc,course cwhere sc.cno=c.cno and sc.score<60 order by sc.cno desc;*********************************34.*********************************select st.sno,st.sname,sc.score from sc,student st where sc.sno=st.sno and cno='c001' and score>80;*********************************35.*********************************select count(distinct sno) from sc;*********************************36.*********************************select st.sname,score from student st,sc ,course c,teacher twherest.sno=sc.sno and sc.cno=c.cno and c.tno=t.tnoand t.tname='谌燕' and sc.score=(select max(score)from sc where sc.cno=c.cno)*********************************37.*********************************select cno,count(sno) from sc group by cno;*********************************38.*********************************select a.* from sc a ,sc b where a.score=b.score and a.cno<>b.cno*********************************39.*********************************select * from (select sno,cno,score,row_number()over(partition by cno order by score desc) my_rn from sc t)where my_rn<=2*********************************40.*********************************select cno,count(sno) from sc group by cno having count(sno)>10order by count(sno) desc,cno asc;*********************************41.*********************************select sno from sc group by sno having count(cno)>1; ||select sno from sc group by sno having count(sno)>1; *********************************42.*********************************select distinct(c.cno),c.cname from course c ,scwhere sc.cno=c.cno||select cno,cname from course c where c.cno in(select cno from sc group by cno)*********************************43.*********************************select st.sname from student stwhere st.sno not in(select distinct sc.sno from sc,course c,teacher twhere sc.cno=c.cno and c.tno=t.tno and t.tname='谌燕')*********************************44.*********************************select sno,avg(score)from sc where sno in(select sno from sc where sc.score<60 group by sno having count(sno)>1) group by sno*********************************45.*********************************select sno from sc where cno='c004' and score<90 order by score desc;*********************************46.*********************************delete from sc where sno='s002' and cno='c001';*********************************