1、完成查询如下表显示，显示全部学生的信息，按照学生编号的升序排列，对同一学号按照课程名称的字母顺序显示。
| 学生编号 | 学生姓名 | 课程名称 | 成绩 |
select stu.studno,stu.studname,se.coursename,sc.grade
from student stu,course se, score sc
where stu.studno=sc.studno and se.courseid=sc.courseid;
2、查询显示单科最高成绩
| 学生编号 | 学生姓名 | 课程名称 | 单科最高成绩 |
Select distinct st.studno,st.studname,co.coursename,maxc.xxl
from student st,course co,
(select sc.studno,s.xxl,s.courseid from score sc,
(select max(grade) xxl,sc.courseid from score sc group by sc.courseid)s
where sc.courseid=s.courseid ) maxc
where st.studno=maxc.studno and co.courseid=maxc.courseid;
3、查询显示学生课程及格还是不及格
| 学生编号 | 学生姓名 | 课程名称 | 考试通过状态 |
| 及格或者不及格 |
| 学生编号 | 学生姓名 | 选课数量 |
select st.studno,st.studname,fenzu.coursecount from student st,
(select count(stu.coursename) coursecount,stu.studno from
(select stu.studno,stu.studname,co.coursename
from student stu,course co, score sc
where stu.studno=sc.studno and co.courseid=sc.courseid) stu group by stu.studno) fenzu
where st.studno=fenzu.studno
5、查询单科成绩超过课程平均成绩的学生的信息，列出学生编号，学生姓名，课程名称和课程成绩
select stu.studno,stu.studname,co.courseid,co.coursename,pinjun.grade
from student stu,course co,
(select sc.studno,st.courseid,sc.grade from score sc,
(select avg(stude.grade) av ,stude.courseid from
(select stu.studno,stu.studname,co.coursename,co.courseid,sc.grade
from student stu,course co, score sc
where stu.studno=sc.studno and co.courseid=sc.courseid) stude
group by stude.courseid) st
where sc.courseid=st.courseid and sc.grade>st.av) pinjun
where pinjun.courseid=co.courseid and stu.studno=pinjun.studno
6、查询显示需要补考的学生的学生编号，学生姓名和课程名称
select st.studno,st.studname,sc.grade
from student st,score sc
where st.studno=sc.studno
and sc.grade<60
7、统计各科成绩平均分，显示课程编号，课程名称，平均分。
select co.courseid,co.coursename,chengji.avge pingjunzhi from course co,
(select avg(stude.grade) avge,stude.courseid from
(select stu.studno,se.courseid,se.coursename,sc.grade
from student stu,course se, score sc
where stu.studno=sc.studno and se.courseid=sc.courseid) stude
group by stude.courseid) chengji
where co.courseid = chengji.courseid
8、查询选修了java课程的学生信息
select *from
(select stu.studno,stu.studname,co.coursename,sc.grade
from student stu,course co, score sc
where stu.studno=sc.studno and co.courseid=sc.courseid) jse
where jse.coursename='JAVA'
9、查询没有选修JAVA课程的学生信息
select *from
(select stu.studno,stu.studname,co.coursename,sc.grade
from student stu,course co, score sc
where stu.studno=sc.studno and co.courseid=sc.courseid) jse
where jse.coursename!='JAVA'
10、查询选修了教师李可课程的学生信息
select stun.studno,stu.studname
from student stu,
(select plan.studno
from courseplan plan
where plan.teachid =
(select te.teachid
from teacherinfo te
where te.teachname = '李可')) stun
where stu.studno=stun.studno
11、查询同时选修了A01和A02这两门课的学生的上课安排，显示学生编号，学生姓名、班级编号、课程编号、授课教师、上课日期
select stu.studno,stu.studname,stu.batchcode,
co.courseid,te.teachname,plan.coursedt
from student stu,course co,teacherinfo te,courseplan plan
where stu.studno=plan.studno
and plan.courseid= co.courseid
and plan.teachid= te.teachid
and (co.courseid='A01' OR co.courseid='A02')
12、查询96571班都有哪些课程，在什么时间有哪位教师授课
select stu.batchcode,
co.courseid,co.coursename,te.teachname,plan.coursedt
from student stu,course co,teacherinfo te,courseplan plan
where stu.studno=plan.studno
and plan.courseid= co.courseid
and plan.teachid= te.teachid
and stu.batchcode=96571
13、查询周一不上课的班级
select biao.batchcode from
(select stu.batchcode,
co.courseid,co.coursename,te.teachname,plan.coursedt
from student stu,course co,teacherinfo te,courseplan plan
where stu.studno=plan.studno
and plan.courseid= co.courseid
and plan.teachid= te.teachid
and plan.coursedt!='周一') biao
group by biao.batchcode
14、查询周四上课的教师姓名
select plan.courseid,plan.teachid,plan.coursedt,te.teachname
from courseplan plan,teacherinfo te
where te.teachid=plan.teachid
and plan.coursedt='周四'
15、查询A02课程的授课教师和上课时间
select plan.courseid,plan.teachid,plan.coursedt,te.teachname
from courseplan plan,teacherinfo te
where te.teachid=plan.teachid
and plan.courseid='A02'
16、统计各个科目不及格人数占这个科目考生人数的百分比
select (sux.coun/summ.coun)*(100/100) bili
from (select count(studno) coun ,courseid from score
group by courseid ) summ,
(select count(studno) coun ,courseid from score where grade<60
group by courseid ) sux
where summ.courseid=sux.courseid
建两个内嵌视图，第一个统计某个科目及格人数，第二个统计某个科目总人数，两个表做等值连接，即可。
17、统计所有不及格人数占考生总数的百分比
select (summ.ml/xxl.xl) bili from
(select count(studno) ml from score where grade<60) summ,
(select count(studno) xl from score) xxl
18、查询单科成绩在90分以上的学生是哪个班级的，授课教师是谁？
select te.teachid,te.teachname,sco.grade,bat.batchname
from batch bat,teacherinfo te, courseplan plan,
(select *from score where grade>90) sco
where sco.studno=plan.studno
and plan.teachid =te.teachid
19、查询工业工程班的授课教师都是谁？
select gong.batchcode,gong.batchname,te.teachname
from (select *from batch where batchname like '工业工程%') gong,
teacherinfo te,student stu,courseplan plan
where gong.batchcode=stu.batchcode
and stu.studno=plan.studno
and plan.teachid=te.teachid
20、查询1068号学生在什么时间都有课？
select id.studname,id.batchcode,co.courseid,co.coursedt
from (select *from student stu where stu.studno=1068) id,
courseplan co
where id.studno=co.studno
21、查询哪些同学的考试成绩都在90分以上
select *from student,
(select distinct sc.studno from score sc where sc.grade>90) id
where student.studno=id.studno
22、查询同时代课超过两门课程的教师
直接在教学计划里面根据teachid分组，统计不重复的课程信息，然后用having字句过滤
23、汇总各个学生考试成绩的总分，并排名次。显示学生编号，学生姓名，班级编号，总分
select stu.studno,stu.studname,stu.batchcode,stud.avge,stud.he
from (select sc.studno,avg(sc.grade) avge,sum(sc.grade) he from
score sc
group by sc.studno) stud,student stu
where stud.studno=stu.studno
order by stud.he asc
24、按照班级分组，显示学生的编号，学生姓名和总分，在一个班级内按照总分排名
select rank_info.studno,stu.studname,rank_info.grade,rank_info.grade_rank
from student stu,
(select stu_info.studno,sum(stu_info.grade) grade,
rank() over (partition by stu_info.batchcode order by sum(stu_info.grade) desc) grade_rank
from (select stu.studno,stu.batchcode,sc.grade
from student stu,score sc
where stu.studno=sc.studno) stu_info
group by stu_info.batchcode,stu_info.studno) rank_info
where rank_info.studno=stu.studno
用到了分析函数和内嵌视图
基本思路是用分析函数求出班级内学生总成绩排名，然后和student表等值连接，获取姓名信息下载本文
