SQL基础测试题目2.doc

SQL基础测试题目1、 完成查询如下表显示，显示全部学生的信息，按照学生编号的升序排列，对同一学号按照课程名称的字母顺序显示。学生编号学生姓名课程名称成绩select st.studno ,st.studname,co.coursename,sc.gradefrom student st left JOIN score sc ON (st.studno=sc.studno) left JOIN course co ON (co.courseid=sc.courseid)order by st.STUDNO，co.coursename2、查询显示单科最高成绩学生编号学生姓名课程名称单科最高成绩select su.*from (select st.studno , st.studname,co.coursename,sc.grade from student st join score sc on (st.studno=sc.studno) join course co on (co.courseid=sc.courseid)order by co.coursename) su ,(select max(grade) max1 ,coursename from (select st.studno , st.studname,co.coursename,sc.gradefrom student st join score sc on (st.studno=sc.studno) join course co on (co.courseid=sc.courseid)order by co.coursename)group by coursename)su1 where grade=(su1.max1)and su.grade=su1. max13、查询显示学生课程及格还是不及格学生编号学生姓名课程名称考试通过状态及格或者不及格select st.studno ,st.studname,co.coursename,sc.grade,case when sc.grade60 then 及格 else 不及格 endfrom student st left JOIN score sc ON (st.studno=sc.studno) left JOIN course co ON (co.courseid=sc.courseid)order by st.STUDNO4、统计学生选科的数量学生编号学生姓名选课数量select courseinfo.studno,courseinfo.studname,count(nvl(courseid,null) 选课数量from (select stuinfo.*,courseno.courseidfrom (select sc.courseid,sc.studno from score sc ) courseno right join(select st.studno,st.studname from student st )stuinfo on (courseno.studno=stuinfo.studno) courseinfogroup by courseinfo.studno,courseinfo.studname5、查询单科成绩超过课程平均成绩的学生的信息，列出学生编号，学生姓名，课程名称和课程成绩select info1.*from(selectround(avg(allinfo.grade),2)avggrade,allinfo.coursenamefrom (select st.studno,st.studname,co.coursename,sc.gradefrom student st join score sc on (st.studno=sc.studno) join course co on (sc.courseid=co.courseid) allinfo group by allinfo.coursename ) ss join (select st.studno,st.studname,co.coursename,sc.gradefrom student st join score sc on (st.studno=sc.studno) join course co on (sc.courseid=co.courseid) info1 on (info1.coursename=ss.coursename)where info1.gradess.avggrade6、查询显示需要补考的学生的学生编号，学生姓名和课程名称select allinfo.studno,allinfo.studname,allinfo.coursenamefrom (select st.studno,st.studname,co.coursename,sc.gradefrom student st join score sc on(st.studno=sc.studno)join course co on (sc.courseid=co.courseid) allinfowhere allinfo.grade607、统计各科成绩平均分，显示课程编号，课程名称，平均分。select al.courseid ,al.coursename,round(avg(grade),2)from (select sc.courseid,co.coursename,sc.gradefrom score sc join course co on(sc.courseid=co.courseid)algroup by al.courseid,al.coursename8、查询选修了java课程的学生信息select stU.studname,co.coursename,stu.studno from student stu left jOIN SCORE sc on (STU.STUDNO=SC.STUDNO) JOIN COURSE CO ON (SC.COURSEID=CO.COURSEID)where CO.COURSENAME=JAVA9、查询没有选修JAVA课程的学生信息SELECT stu.studno,stu.studnameFROM STUDENT STU where stu.studnoall (select stu.studno from student stu left jOIN SCORE sc on (STU.STUDNO=SC.STUDNO) JOIN COURSE CO ON (SC.COURSEID=CO.COURSEID)where CO.COURSENAME=JAVA)10、查询选修了教师李可课程的学生信息select st.studno,st.studname ,sc.courseidfrom student st join score sc on (sc.studno=st.studno)where sc.courseid=B0111、查询同时选修了A01和A02这两门课的学生的上课安排，显示学生编号，学生姓名、班级编号、课程编号、授课教师、上课日期12、查询96571班都有哪些课程，在什么时间有哪位教师授课select bat.batchname,tea.teachname,cou.coursename,cop.coursedtfrom batch bat ,courseplan cop,teacherinfo tea,course cou,student stuwhere bat.batchcode=96571 and bat.batchcode=stu.batchcode and cop.teachid=tea.teachidand cop.courseid=cou.courseid and cop.studno=stu.studno13、查询周一不上课的班级SELECT BAT.BATCHCODE,bat.batchname FROM teacherinfo tea ,course CO,courseplan COP ,STUDENT STU ,BATCH BATWHERE TEA.TEACHID=COP.TEACHID AND CO.COURSEID=COP.COURSEID AND STU.STUDNO=COP.STUDNO AND STU.BATCHCODE=BAT.BATCHCODE and COP.Coursedt周一14、查询周四上课的教师姓名SELECT TEA.TEACHNAME,COP.COURSEDTFROM TEACHERINFO TEA JOIN COURSEPLAN COP ON (COP.TEACHID=TEA.TEACHID AND COP.COURSEDT=周四)15、查询A02课程的授课教师和上课时间SELECT TEA.TEACHNAME,COP.COURSEID,COP.COURSEDTFROM TEACHERINFO TEA JOIN COURSEPLAN COP ON (COP.TEACHID=TEA.TEACHID and cop.courseid=A02)16、统计各个科目不及格人数占这个科目考生人数的百分比17、统计所有不及格人数占考生总数的百分比18、查询单科成绩在90分以上的学生是哪个班级的，授课教师是谁？Select distinct stu.studno,stu.studname,stu.batchcode,tea.teachname,ba.batchnamefrom student stu ,teacherinfo tea ,courseplan cop ,batch ba,score scwhere stu.studno=cop.studno and cop.teachid=tea.teachid and ba.batchcode=stu.batchcodeand sc.courseid=cop.courseid and sc.grade9019、查询工业工程班的授课教师都是谁？select souinfo.batchname,souinfo.batchcode,teainfo.teachnamefrom(select tea.teachname,cop.courseidfrom teacherinfo tea ,courseplan copwhere tea.teachid=cop.teachid) teainfo, (select distinct calssesinfo.* ,socu.courseidfrom (select sc.courseid,st.batchcodefrom score sc,course co,student stwhere sc.courseid=co.courseid and st.studno=sc.studno)socu,(select bat.* from batch batwhere bat.batchname like %)calssesinfowhere socu.batchcode=calssesinfo.batchcode) souinfowhere teainfo.courseid=souinfo.courseid20、查询1058号学生在什么时间都有课？select stu.studno,stu.studname ,cop.coursedt from student stu join courseplan cop on (stu.studno=cop.studno) where stu.studno=105821、查询哪些同学的考试成绩都在90分以上SELECT ALLSCORE.*FROM (select stu.*, sco.gradefrom student stu left join score scoon stu.studno=sco.studno) ALLSCOREWHERE ALLSCORE.GRADE=90 and allscore.studname=张涛22