oracle数据库增删改查练习50例-答案(精)(3)

32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列select c.cno ,c.cname,a.avg_score

from course c,(select cno,round(avg(score,2 avg_score from sc group by cno awhere c.cno = a.cnoorder by a.avg_score desc ,c.cno;

33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩select st.sno, st.sname, a.avg_score from student st, (select sno, round(avg(score, 2 avg_score from sc group by sno having round(avg(score, 2 >= 85 a where st.sno = a.sno;

34、查询课程名称为\数学\，且分数低于60的学生姓名和分数select st.sname,a.scorefrom student st,(select sno, score from sc where cno = (select cno from course where cname = '数学' and score < 60 awhere st.sno = a.sno

35、查询所有学生的课程及分数情况select st.*, c.cname, sc.cno, sc.score from student st, course c, sc where st.sno = sc.sno and sc.cno = c.cno

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；select st.*, c.cname, sc.cno, sc.score from student st, course c, sc, (select sno, min(score from sc group by sno having min(score >= 70 a where st.sno = a.sno and a.sno = sc.sno and sc.cno = c.cno

37、查询不及格的课程 select st.*,c.cname,a.cno,a.scorefrom student st,course c,(select * from sc where score < 60 awhere st.sno = a.snoand c.cno = a.cno

38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名select st.*,c.cname,a.cno,a.scorefrom student st,course c,(select sno,cno,scorefrom scwhere cno = '01' and score >= 80 awhere st.sno = a.snoand c.cno = a.cno

39、求每门课程的学生人数select a.cno,c.cname,a.cnfrom course c,(select cno,count(sno cn from sc group by cno awhere c.cno = a.cno

40、查询选修\张三\老师所授课程的学生中，成绩最高的学生信息及其成绩select st.*, c.cname, b.cno, b.score

from student st, course c, (select * from sc where score = (select max(score from (select * from sc where cno = (select cno from course where tno = (select tno from teacher where tname = '张三' a and cno = (select cno from course where tno = (select tno from teacher where tname = '张三' b where st.sno = b.sno and c.cno = b.cno

41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩select distinct sc1.sno, sc1.cno, sc1.scorefrom sc sc1,sc sc2where sc1.score = sc2.scoreand sc1.cno != sc2.cno

42、查询每门功成绩最好的前两名

select st.sno, st.sname, st.sage, st.ssex, a.cno, a.paim from student st, (select sno, cno, score, dense_rank( over(partition by cno order by score desc paim from sc a where st.sno = a.sno and a.paim <= 2

select st.sno, st.sname, st.sage, st.ssex, a.cno, a.paim from student st, (select sno, cno, score, rank( over(partition by cno order by score desc paim from sc a where st.sno = a.sno and a.paim <= 2

44、检索至少选修两门课程的学生学号select st.sno,st.sname,aa.cnfrom student st,(select sno,count(cno cn from sc group by sno having count(cno >= 2 aawhere st.sno = aa.sno

46、查询各学生的年龄select st.*, (to_char(sysdate, 'yyyy' - to_char(st.sage, 'yyyy' \年龄\

47、查询本周过生日的学生select st.* from student st where to_char(st.sage, 'mmdd' between to_char(trunc(sysdate, 'iw', 'mmdd' and to_char(trunc(sysdate, 'iw' + 6, 'mmdd'

48、查询下周过生日的学生select st.* from student st where to_char(st.sage, 'mmdd' between to_char(trunc(sysdate, 'iw'+7, 'mmdd' and to_char(trunc(sysdate, 'iw' + 13, 'mmdd'

select to_char(trunc(sysdate, 'iw'+7, 'mmdd' from dual; select to_char(trunc(sysdate, 'iw'+ 13, 'mmdd' from dual;

49、查询本月过生日的学生select st.* from student st where to_char(st.sage, 'mm' = to_char(sysdate,'mm'

50、查询下月过生日的学生select st.*from student st where to_char(st.sage, 'mm' = to_char(add_months(trunc(sysdate,1,'mm'

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库oracle数据库增删改查练习50例-答案(精)(3)在线全文阅读。