【Oracle篇】六月笔记集合

--1、 完成查询如下表显示，显示全部学生的信息，按照学生编号的升序排列，对同一学号按照课程名称的字母顺序显示。 --学生编号 学生姓名 课程名称 成绩 select st.studno,st.studname, sum(decode(cc.coursename,'JAVA',sc.grade,0)) JAVA, sum(decode(cc.c

--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;