SQL基础测试题目2

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

from 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.coursename

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

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.grade

from 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. max1

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

case when sc.grade>'60' then '及格' else '不及格' end

from student st left JOIN score sc ON (st.studno=sc.studno) left JOIN course co

ON (co.courseid=sc.courseid)

order by st.STUDNO

4、统计学生选科的数量

from (select stuinfo.*,courseno.courseid

from (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)) courseinfo

group by courseinfo.studno,courseinfo.studname

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

select info1.*

from((selectround(avg(allinfo.grade),2)avggrade,allinfo.coursename

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 (sc.courseid=co.courseid)) allinfo

group by allinfo.coursename ) ss join (select st.studno,st.studname,co.coursename,sc.grade

from 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.grade>ss.avggrade

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

select allinfo.studno,allinfo.studname,allinfo.coursename

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 (sc.courseid=co.courseid)) allinfo

where allinfo.grade<60

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

select al.courseid ,al.coursename,round(avg(grade),2)ƽ¾ù·Ö

from (select sc.courseid,co.coursename,sc.grade

from score sc join course co on(sc.courseid=co.courseid))al

group by al.courseid,al.coursename

8、查询选修了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='JAVA'

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

SELECT stu.studno,stu.studname

FROM STUDENT STU where stu.studno<>all (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.courseid

from student st join score sc on (sc.studno=st.studno)

where sc.courseid='B01'

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

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

select bat.batchname,tea.teachname,cou.coursename,cop.coursedt

from batch bat ,courseplan cop,teacherinfo tea,course cou,student stu

where bat.batchcode=96571 and bat.batchcode=stu.batchcode and cop.teachid=tea.teachid

and cop.courseid=cou.courseid and cop.studno=stu.studno

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

SELECT BAT.BATCHCODE,bat.batchname

FROM teacherinfo tea ,course CO,courseplan COP ,STUDENT STU ,BATCH BAT

WHERE 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.COURSEDT

FROM TEACHERINFO TEA JOIN COURSEPLAN COP ON (COP.TEACHID=TEA.TEACHID AND COP.COURSEDT='周四')

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

SELECT TEA.TEACHNAME,COP.COURSEID,COP.COURSEDT

FROM 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.batchname

from student stu ,teacherinfo tea ,courseplan cop ,batch ba,score sc

where stu.studno=cop.studno and cop.teachid=tea.teachid and ba.batchcode=stu.batchcode

and sc.courseid=cop.courseid and sc.grade>90

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

select souinfo.batchname,souinfo.batchcode,teainfo.teachname

from(select tea.teachname,cop.courseid

from teacherinfo tea ,courseplan cop

where tea.teachid=cop.teachid) teainfo,

(select distinct calssesinfo.* ,socu.courseid

from (select sc.courseid,st.batchcode

from score sc,course co,student st

where sc.courseid=co.courseid and st.studno=sc.studno)socu,

((select bat.* from batch bat

where bat.batchname like '%¹¤Òµ¹¤³Ì%'))calssesinfo

where socu.batchcode=calssesinfo.batchcode) souinfo

where teainfo.courseid=souinfo.courseid

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

select stu.studno,stu.studname ,cop.coursedt

from student stu join courseplan cop on (stu.studno=cop.studno)

where stu.studno='1058'

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

SELECT ALLSCORE.*

FROM (select stu.*, sco.grade

from student stu left join score sco

on stu.studno=sco.studno) ALLSCORE

WHERE ALLSCORE.GRADE>=90 and allscore.studname='张涛'

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

select alltea.* from (select count(*) 代课总数, tea.teachname

from teacherinfo tea join courseplan cop on (tea.teachid=cop.teachid)

group by tea.teachname)alltea

where alltea.代课总数=2

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

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