SQL基础测试题目

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

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

select s.STUDNO "学生编号",s.STUDNAME "学生姓名",c.COURSENAME "课程名称",g.GRADE "成绩"
from student s left join score g on(s.STUDNO=g.STUDNO) 
left join course c on (c.COURSEID=g.COURSEID)
order by s.STUDNO,c.COURSENAME;

score,student,course三表之间的等值连接即可

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

学生编号	学生姓名	课程名称	单科最高成绩
	zhansan	c	98

temp一个临时表，里面存着每一科的最高分，然后再和student，score，course做四张表的连接 (select courseid,max(grade) maxgrade from score group by coursed) temp
也可以用分析函数实现

create table temp("学生编号","学生姓名","课程名称","单科最高成绩") as
select s.STUDNO,s.STUDNAME,c.COURSENAME,g.GRADE
from student s,course c,
(select studno,courseid,grade from score 
where (grade,courseid) in (select max(grade),courseid from score group by courseid)) g
where s.STUDNO=g.STUDNO and c.COURSEID=g.COURSEID;

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

学生编号	学生姓名	课程名称	考试通过状态
			及格或者不及格

select s.STUDNO "学生编号",s.STUDNAME "学生姓名",c.COURSENAME "课程名称",
case when grade>=60 then '及格'
else '不及格' end "考试通过状态"
from student s,course c,score g 
where s.STUDNO=g.STUDNO and g.COURSEID = c.COURSEID;

用到case表达式

4、统计学生选科的数量

学生编号	学生姓名	课程名称

select s.STUDNO "学生编号",s.STUDNAME "学生姓名",temp.c "选课数量"
from student s,(select count(courseid) c,studno from courseplan group by studno) temp
where s.STUDNO=temp.STUDNO;

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

select s.STUDNO "学生编号",s.STUDNAME "学生姓名",c.COURSENAME "课程名称",g.grade "成绩" 
from student s,course c,score g,
(select courseid,avg(grade) avggrade from score group by courseid) gg
where s.STUDNO = g.STUDNO
and c.COURSEID   = g.COURSEID and gg.courseid=c.COURSEID and g.GRADE>gg.avggrade;

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

SELECT s.STUDNO "学生编号",s.STUDNAME "学生姓名",c.COURSENAME "课程名称",g.grade "成绩" 
FROM student s,course c,score g
where g.COURSEID = c.COURSEID
AND s.STUDNO   = g.STUDNO
and g.GRADE<60;

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

select c.COURSEID "课程编号",c.COURSENAME "课程名称",temp.avggrade "平均分"
from course c ,
(select courseid,avg(grade) avggrade from score group by courseid) temp where c.COURSEID=temp.courseid;

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

select s.STUDNO "学生编号",s.STUDNAME "学生姓名", s.BATCHCODE "班级编号"
from student s,score g,course c
where g.COURSEID = c.COURSEID
and s.STUDNO   = g.STUDNO
and c.COURSENAME='JAVA';

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

select s.STUDNO "学生编号",s.STUDNAME "学生姓名", s.BATCHCODE "班级编号"
from student s,score g,course c
where g.COURSEID = c.COURSEID
and s.STUDNO   = g.STUDNO
and c.COURSENAME<>'JAVA';

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

select s.STUDNO "学生编号",s.STUDNAME "学生姓名"
from student s,
(select tt.studno from courseplan tt where tt.TEACHID=(select tea.TEACHID from TEACHERINFO tea where tea.TEACHNAME='李可')) bb
where s.STUDNO=bb.STUDNO;

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

select s.STUDNO "学生编号",s.STUDNAME "学生姓名",s.BATCHCODE "班级编号",c.COURSEID "课程编号",t.TEACHNAME "授课教师",co.COURSEDT "上课日期"
from student s,course c,courseplan co,teacherinfo t
where s.STUDNO=co.STUDNO and c.COURSEID=co.COURSEID and t.TEACHID=co.TEACHID 
and (c.COURSEID='A01' or c.COURSEID='A02');

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

select s.BATCHCODE "班级编号",c.COURSEID "课程编号",c.COURSENAME "课程名称",co.COURSEDT "上课日期",t.TEACHNAME "授课教师"
from student s,course c,courseplan co,teacherinfo t,batch b
where c.COURSEID=co.COURSEID and t.TEACHID=co.TEACHID and s.STUDNO=co.STUDNO 
and s.BATCHCODE=b.BATCHCODE and b.BATCHCODE=96571;

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

select BATCHCODE "班级编号" from student s,
(select studno from courseplan where coursedt<>'周一') tt 
where s.studno=tt.studno;

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

select DISTINCT t.TEACHNAME "授课教师" 
from teacherinfo t, courseplan co where co.TEACHID=t.TEACHID and co.COURSEDT='周四';

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

select t.TEACHNAME "授课教师",co.COURSEID "课程编号",co.COURSEDT "上课日期"
from courseplan co,teacherinfo t 
where co.TEACHID=t.TEACHID and co.COURSEID='A02';

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

--每门课程中不及格的人数
select courseid,count(studno) nopass from score where grade<60 group by courseid;
--每门课程的总人数
select courseid,count(studno) allstu from score group by courseid;
select c1.courseid,coursename,c1.nopass/c2.allstu*100 
from (select courseid,count(studno) nopass from score where grade<60 group by courseid) c1,(select courseid,count(studno) allstu from score group by courseid)c2,course 
where c1.courseid=course.courseid and c2.courseid=course.courseid;

建两个内嵌视图，第一个统计某个科目及格人数，第二个统计某个科目总人数，两个表做等值连接，即可。

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

create or REPLACE view passcount as (select count(studno) count  from score   where grade<60  ) ;
create or REPLACE view allcount as (select count(studno) count  from SCORE ) ;
select TRUNC(pc.count/ac.count,2)*100||'%' from passcount pc,ALLCOUNT ac;

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

select s.STUDNAME "学生姓名",b.BATCHNAME "班级名称",g.COURSEID "课程编号",t.TEACHNAME "授课教师"
from student s,courseplan co,teacherinfo t,score g,batch b
where s.BATCHCODE=b.BATCHCODE and s.STUDNO=g.STUDNO and
co.TEACHID=t.TEACHID and g.STUDNO=co.STUDNO and g.GRADE>90;

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

select t.TEACHNAME from batch b,student s,courseplan co,teacherinfo t
where b.BATCHCODE=s.BATCHCODE and s.STUDNO=co.STUDNO and co.TEACHID=t.TEACHID
and b.BATCHNAME like '工业工程%班';

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

select coursedt from COURSEPLAN where STUDNO=1068;

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

select s.STUDNO,s.STUDNAME,g.GRADE from 
student s,score g 
where s.STUDNO = g.STUDNO and g.GRADE>90

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

select t.TEACHID,t.TEACHNAME from teacherinfo t,
(select teachid,count(courseid) ccount from courseplan group by teachid) tt
where tt.teachid>2 and t.TEACHID=tt.teachid;

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

select s.studno,s.studname,batchcode,temp.totalscore,rank() over(order by temp.totalscore desc) r 
from student s,(select studno,sum(grade) totalscore from score group by studno)temp 
where s.studno = temp.studno ;

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

select s.studno,s.studname,temp.totalscore,s.batchcode,rank() over( order by temp.totalscore desc)r
from student s,(select studno,sum(grade) totalscore from score group by studno order by totalscore) temp
where s.studno=temp.studno
order by s.batchcode,temp.totalscore desc;

基本思路是用分析函数求出班级内学生总成绩排名，然后和student表等值连接，获取姓名信息