试题参考及解答

测试题第一部分表结构及表内容说明1、STUDENT--学生表/表结构/SNOVARCHAR2(6)NOTNULL,--学号SNAMEVARCHAR2(10)NOTNULL,--姓名SSEXVARCHAR2(2)NOTNULL,--性别SBIRTHDAYDATE,--生日CLASSVARCHAR2(8)--班级/表内容/SNOSNAMESSEXSBIRTHDAYCLASS1101曾华女1997/9/142033102匡明女1995/10/242031103王丽女1996/1/2342033104李军男1996/2/2042033105王芳女1995/2/1042031106陆君男1994/4/2942031107刘禹锡男1995/9/2342032108贺知章男1996/6/2642032109陆游男1997/4/142032110李清照女1995/8/842034111苏东坡男1997/12/1242034112阮籍男1995/10/20420342、COURSE--课程表/表结构/CNOVARCHAR2(10)NOTNULL,--课程号CNAMEVARCHAR2(20)NOTNULL,--课程名称TNOVARCHAR2(10)NOTNULL--任课教师/表内容/CNOCNAMETNO3-105计算机导论8253-245操作系统8046-166数据电路8569-888高等数学1003、SCORE--成绩表/表结构/SNOVARCHAR2(10)NOTNULL,--学号CNOVARCHAR2(10)NOTNULL,--课程号DEGREE1NUMBER(10,1)NOTNULL--成绩/表内容/SNOCNODEGREE11033-24586.01053-24575.01093-24568.01033-10592.01053-10588.01093-10576.01013-10564.01073-10591.01083-10578.01016-16685.01076-10679.01086-16681.04、TEACHER--教师表/表结构/TNOVARCHAR2(10)NOTNULL,--教师编号TNAMEVARCHAR2(10)NOTNULL,--教师姓名TSEXVARCHAR2(10)NOTNULL,--教师性别TBIRTHDAYDATENOTNULL,--教师生日PROFVARCHAR2(10),--教师职称DEPARTVARCHAR2(20)NOTNULL--教师院系/表内容/TNOTNAMETSEXTBIRTHDAYPROFDEPART804屈原男1958/12/2教授计算机系856杜甫男1969/3/2副教授电子工程系825王萍女1972/5/5讲师计算机系831刘冰女1977/8/14助教电子工程系第二部分题目1、查询所有学生的学号，姓名，性别。selectsno,sname,ssexfromstudent2、查询“42031”班或性别为“女”的学生的学号，姓名，班级，性别。selectsno,sname,class1,ssexfromstudentwhereclass1='42031'orssex='女'3、查询所有不姓王的学生的学号，姓名，性别。selectsno,sname,ssexfromstudentwheresnamenotlike'王%'selectsno,sname,ssexfromstudentwheresubstr(sname,1,1)'王'4、查询成绩在60到80之间的学生的学号，课程号，成绩，按照成绩降序排列。selectsno,cno,degree1fromscorewheredegree1=60anddegree1=80orderby3desc5、查询所有老师的名字，在名字后面加上“老师”。selecttname||'老师'fromteacher6、查询班级最后一位为“3”的学生信息。select*fromstudentwheresubstr(class1,length(class1),1)='3'select*fromstudentwhereclass1like'%3'select*fromstudentwheresubstr(class1,-1,1)='3'select*fromstudentwheresubstr(class1,5,1)='3'7、查询下月过生日的学生的信息。select*fromstudentwhereto_char(sbirthday,'mm')-1=to_char(sysdate,'mm')orto_char(sbirthday,'mm')+11=to_char(sysdate,'mm')select*fromstudentwhereto_char(sbirthday,'mm')=to_char(add_months(sysdate,1),'mm')select*fromstudentwhereextract(monthfromsbirthday)=extract(monthfromadd_months(sysdate,1))8、查询教师院系的类别。selectdistinctdepartfromteacherselectdepartfromteachergroupbydepart9、查询“42033”班的学生人数。selectcount(*)fromstudentwhereclass1='42033'10、查询成绩最高的学生学号，课程号，成绩。（26）selectsno,cno,degree1from(select*fromscoreorderbydegree1desc)whererownum=1selectsno,cno,degree1fromscorewheredegree1=(selectmax(degree1)fromscore)11、查询没有成绩的课程号、课程名称。（27）selectc.cno,c.cname,s.cnofromcoursec,scoreswherec.cno=s.cno(+)ands.cnoisnullselectcno,cnamefromcoursewherecnonotin(selectcnofromscore)selectcno,cnamefromcourseawherenotexists(selectcnofromscorebwherea.cno=b.cno)12、查询“3-105”号课程的平均分。（21）selectavg(degree1)fromscorewherecno='3-105'13、查询最高成绩大于90的学号。（20）selectsnofromscoregroupbysnohavingmax(degree1)90selectdistinctsnofromscorewheredegree190selectsnofromscorewheredegree19014、查询至少有5名学生选修的并以3开头的课程的平均分数。(23)selectavg(degree1)fromscorewherecnolike'3%'groupbycnohavingcount(*)=515、查询“42033”班所选课程的平均分。selectavg(degree1)fromscoresc,studentstwheresc.sno=st.snoandst.class1='42033'16、按照分数对学生成绩划分级别，90到100为A等，80到89为B等，70到79为C等，60到69为D等，其他为E等，查询结果显示学号，成绩，等级，并按照等级升序排列。selectsno,degree1,(casewhendegree1=90anddegree1=100then'A'whendegree1=80anddegree1=89then'B'whendegree1=70anddegree1=79then'C'whendegree1=60anddegree1=69then'D'else'E'end)等级fromscoreorderby3selectsno,degree1,decode(floor(degree1/10),10,'A',9,'A',8,'B',7,'C',6,'D','E')dengjifromscoreorderbydengji17、查询选修“3-105”课程且成绩高于“109”号同学成绩的所有学生的记录。（22）select*fromscorewheredegree1(selectdegree1fromscorewherecno='3-105'andsno='109')andcno='3-105'18、查询所有选修“计算机导论”课程的“男”同学的学号，姓名，成绩。selectst.sno,st.sname,degree1fromcoursec,scores,studentstwherec.cno=s.cnoands.sno=st.snoandst.ssex='男'andc.cname='计算机导论'19、查询“屈原“教师任课的课程名称，学号，学生成绩。selectc.cname,s.sno,s.degree1fromscores,coursecwheres.cno=c.cnoandc.tno=(selecttnofromteacherwheretname='屈原')selectc.cname,s.sno,s.degree1fromscores,teachert,coursecwheres.cno=c.cnoandt.tno=c.tnoandc.tno=(selecttnofromteacherwheretname='屈原')selectc.cname,s.sno,s.degree1fromscores,teachert,coursecwheres.cno=c.cnoandt.tno=c.tnoandt.tname='屈原'20、查询选修某课程的学生人数多于5人的教师姓名，课程名称，选课人数。selectt.tname,c.cname,cshufromcoursec,teachert,(selectcno,count(*)cshufromscoregroupbycnohavingcount(*)5)swherec.tno=t.tnoandc.cno=s.cnoselectt.tname,c.cname,cshufromcoursec,teachert,(selectcno,count(*)cshufromscoregroupbycno)swherec.tno=t.tnoandc.cno=s.cnoands.cshu5selectt.tname,c.cname,count(*)fromcoursec,teachert,scoreswherec.tno=t.tnoandc.cno=s.cnogroupbyt.tname,c.cnamehavingcount(*)5