oracle表示六月,【Oracle篇】六月笔记集合

--1、 完成查询如下表显示，显示全部学生的信息，按照学生编号的升序排列，对同一学号按照课程名称的字母顺序显示。

--学生编号 学生姓名 课程名称 成绩

select st.studno,st.studname,

sum(decode(cc.coursename,'JAVA',sc.grade,0)) "JAVA",

sum(decode(cc.coursename,'JSP',sc.grade,0)) "JSP",

sum(decode(cc.coursename,'Struts',sc.grade,0)) "Struts",

sum(decode(cc.coursename,'Oracle',sc.grade,0)) "Oracle",

sum(decode(cc.coursename,'Spring',sc.grade,0)) "Spring",

sum(decode(cc.coursename,'经济管理',sc.grade,0)) "经济管理",

sum(decode(cc.coursename,'国际商贸',sc.grade,0)) "国际商贸",

sum(decode(cc.coursename,'会计原理',sc.grade,0)) "会计原理",

sum(decode(cc.coursename,'外贸函电',sc.grade,0)) "外贸函电",

sum(decode(cc.coursename,'马克思主义原理',sc.grade,0)) "马克思主义原理"

from student st ,score sc,course cc where st.studno=sc.studno and cc.courseid=sc.courseid group by st.studno ,st.studname;

--2、查询显示单科最高成绩

--学生编号 学生姓名 课程名称 单科最高成绩

select st.studno,st.studname,s.s_k,cc.coursename

from (select s.studno sno,max(s.grade) over(partition by s.studno) s_k

from score s ) s ,score sc,student st,course cc

where sc.grade=s_k and s.sno=st.studno and cc.courseid=sc.courseid and st.studno=sc.studno;

--3、查询显示学生课程及格还是不及格

--学生编号 学生姓名 课程名称 考试通过状态

select st.studno,st.studname,cc.coursename,

case

when sc.grade>=60 then '及格'

else '不及格'

end "考试通过状态"

from student st ,score sc,course cc where st.studno=sc.studno and cc.courseid=sc.courseid;

--4、统计学生选科的数量

--学生编号 学生姓名 选课数量

select st.studno,st.studname,

count(coursename) over(partition by st.studno order by st.studname) course_count

from student st ,score sc,course cc where st.studno=sc.studno and cc.courseid=sc.courseid;

--5、查询单科成绩超过课程平均成绩的学生的信息，列出学生编号，学生姓名，课程名称和课程成绩

select st.studno,st.studname,cc.coursename,sc.grade

from student st ,score sc,course cc,(select avg(grade) gav,studno from score group by studno)avg_s

where st.studno=sc.studno and cc.courseid=sc.courseid and sc.grade>avg_s.gav group by st.studno,st.studname,cc.coursename,sc.grade;

-- 6、查询显示需要补考的学生的学生编号，学生姓名和课程名称

select st.studno,st.studname,cc.coursename

from student st ,score sc,course cc

where exists (select 1 from score s where s.grade<60)

and st.studno=sc.studno and cc.courseid=sc.courseid and sc.grade<60 group by st.studno,st.studname,cc.coursename,sc.grade;

--7、统计各科成绩平均分，显示课程编号，课程名称，平均分。

select avg(grade) avg_s,s.courseid from score s,course cc

where s.courseid=cc.courseid group by s.courseid;

--8、查询选修了java课程的学生信息

select st.*

from student st,course cc,score sc where st.studno=sc.studno and cc.courseid=sc.courseid and cc.coursename='JAVA';

--9、查询没有选修JAVA课程的学生信息

select st.*

from student st,course cc,score sc where st.studno=sc.studno and cc.courseid=sc.courseid and cc.coursename!='JAVA';

--10、查询选修了教师李可课程的学生信息

select st.*

from student st,teacherinfo tt,courseplan cp where st.studno=cp.studno and tt.teachid=cp.teachid and tt.teachname='李可';

--11、查询同时选修了A01和A02这两门课的学生的上课安排，显示学生编号，学生姓名、班级编号、课程编号、授课教师、上课日期

select all_stu.*,st.studno from (

select st.studno sno,st.studname,st.batchcode,cp.courseid,tt.teachname,cp.coursedt

from student st,teacherinfo tt,courseplan cp where

st.studno=cp.studno

and

cp.courseid='A02'

union

select st.studno sno,st.studname,st.batchcode,cp.courseid,tt.teachname,cp.coursedt

from student st,teacherinfo tt,courseplan cp where

st.studno=cp.studno

and

cp.courseid='A01') all_stu,student st

where st.studno=all_stu.sno order by st.studno;

--12、查询96571班都有哪些课程，在什么时间有哪位教师授课

select tt.teachname,cp.coursedt,cc.coursename

from teacherinfo tt,courseplan cp,course cc,student st where st.batchcode='96571' and st.studno=cp.studno and tt.teachid=cp.teachid;

--13、查询周一不上课的班级

select st.batchcode,cp.coursedt

from teacherinfo tt,courseplan cp,course cc,student st where cp.coursedt!='周一' and st.studno=cp.studno and tt.teachid=cp.teachid;

--14、查询周四上课的教师姓名

select cp.coursedt,tt.teachname

from teacherinfo tt,courseplan cp

where cp.coursedt='周四' and tt.teachid=cp.teachid;

--15、查询A02课程的授课教师和上课时间

select cc.coursename,tt.teachname,cp.coursedt

from teacherinfo tt,courseplan cp,course cc where cp.courseid='A02'

and tt.teachid=cp.teachid order by cp.courseid;

--16、统计各个科目不及格人数占这个科目考生人数的百分比

select count(*)

from student st,

select round((no_grade.n_g/all_grade.a_g)*100,2)||'%' geade_perce,cc.coursename,st.studno

from (select count(*) n_g,cc.coursename from score sc,course cc,student st

where grade<60 and sc.courseid=cc.courseid and st.studno=sc.studno group by cc.coursename) no_grade,

(select count(*) a_g,cc.coursename from score sc,course cc,student st

where sc.courseid=cc.courseid and st.studno=sc.studno) all_grade,score sc,course cc,student st where sc.courseid=cc.courseid and st.studno=sc.studno;

--17、统计所有不及格人数占考生总数的百分比

select round((no_grade.n_g/all_grade.a_g)*100,2)||'%' geade_perce

from (select count(*) n_g from score sc,course cc

where grade<60 and sc.courseid=cc.courseid ) no_grade,(select count(*) a_g from score sc,course cc

where sc.courseid=cc.courseid ) all_grade;

--18、查询单科成绩在90分以上的学生是哪个班级的，授课教师是谁？

select cp.courseid,tt.teachname

from student st,courseplan cp,teacherinfo tt,score sc

where sc.grade>90 and st.studno=sc.studno and cp.courseid=sc.courseid and tt.teachid=cp.teachid;

--19、查询工业工程班的授课教师都是谁？

select tt.teachname,bb.batchname

from courseplan cp,teacherinfo tt,student st,bbatch bb

where bb.batchname like '%工业工程%'

and tt.teachid=cp.teachid and st.batchcode=bb.batchcode;

--20、查询1068号学生在什么时间都有课？

select cp.coursedt

from courseplan cp,teacherinfo tt,student st,bbatch bb

where st.studno=1058 and tt.teachid=cp.teachid and st.batchcode=bb.batchcode;

--21、查询哪些同学的考试成绩都在90分以上

select st.studname

from courseplan cp,teacherinfo tt,student st,bbatch bb,score sc

where sc.grade>90 and tt.teachid=cp.teachid and st.batchcode=bb.batchcode group by st.studname;

--22、查询同时代课超过两门课程的教师

select tt.teachname

from courseplan cp,teacherinfo tt,student st,bbatch bb,score sc

where (select count(cc.coursename) from courseplan cp,teacherinfo tt,course cc

where tt.teachid=cp.teachid and cc.courseid=cp.courseid)>2

and tt.teachid=cp.teachid and st.batchcode=bb.batchcode group by tt.teachname;

--23、汇总各个学生考试成绩的总分，并排名次。显示学生编号，学生姓名，班级编号，总分

-------------------------

select * from (

select DENSE_RANK() over(order by all_grade.sum_grade desc) rk,all_grade.*

from

(select sum(sc.grade) sum_grade,st.studno sno,st.studname sna,st.batchcode sba,bb.batchname

from student st,score sc,bbatch bb

where bb.batchcode=st.batchcode and st.studno=sc.studno group by st.studno,st.studname,st.batchcode,bb.batchname

order by st.studno,sum_grade desc

)all_grade,student st where st.batchcode=all_grade.sba

)

where rk<=10 ;

--------------------------

--24、按照班级分组，显示学生的编号，学生姓名和总分，在一个班级内按照总分排名

select * from (

select DENSE_RANK() over(partition by all_grade.sba order by all_grade.sum_grade desc) rk,all_grade.*

from

(select sum(sc.grade) sum_grade,st.studno sno,st.studname sna,st.batchcode sba,bb.batchname

from student st,score sc,bbatch bb

where bb.batchcode=st.batchcode and st.studno=sc.studno group by st.studno,st.studname,st.batchcode,bb.batchname

order by st.studno,sum_grade desc

)all_grade,student st where st.batchcode=all_grade.sba

)

where rk<=3;

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：php中文网