高软面试题-数据库部分

Student(SId,Sname,Sage,Ssex)学生表 ame,TId)课程表SC(SId,CId,score)成绩表Teacher(TId,Tname)教师表seleIdfrom(selectsId,scorefromSCwhereCId='001')a,(selectsId,scorefromSCwhereCId='002')bwherea.score>b.scoreandfromscgroupbySIdhavingavg(score)fromStudentleftOuterjoinSConStudent.SId=SC.SIdgroupbyStudent.SId,Snameselectcount(distinct(Tname))fromTeacherwhereTnamelike李selectfromwhereSIdnotin(selectdistinct(SC.SId)fromSC,Course,Teacherwhere Teacher.TId=Course.TIdandTeacher.Tname='');6、查询学过“001”并且也学过“002”课程的同学的学号、selectStudent.SId,Student.SnamefromStudent,SCwhereStudent.SId=SC.SIdandSC.CId='001'andexists(Select*fromSCasSC_2whereSC_2.SId=SC.SIdandSC_2.CId='002');fromStudentwhereSIdin(selectSIdfromSC,Course,TeacherwhereSC.CId=Course.CIdandTeacher.TId=Course.TIdandTeacher.Tname=''groupbySIdhavingcount(SC.CId)=(selectcount(CId)fromCourse,Teacher whereTeacher.TId=Course.TIdandTname=''));8、查询课程“002”的成绩比课程“001”课程低的所有同学的学号、SelectSId,Snamefrom(selectStudent.SId,Student.Sname,score,(selectscorefromSCSC_2whereSC_2.SId=Student.SIdandSC_2.CId='002')score2fromStudent,SCwhereStudent.SId=SC.SIdandCId='001')S_2wherescore2fromStudentwhereSIdnotin(selectStudent.SIdfromStudent,SC =SC.SIdandfromStudent,SCwhereStudent.SId=SC.SIdgroupby Student.SId,Student.Snamehavingcount(CId)<(selectcount(CId)fromCourse);selectSId,SnamefromStudent,SCwhereStudent.SId=SC.SIdandCIdinselectCIdfromSCwherefromStudent,SCwhereStudent.SId=SC.SIdandCIdin(selectCIdfromSCwhereupdateSCsetscore=(selectavg(SC_2.score)fromSCwhereSC_2.CId=SC.CId)fromCourse,TeacherwhereCourse.CId=SC.CIdandCourse.TId=Teacher.TIdandTeacher.Tname='');14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和；selectSIdfromSCwhereCIdin(selectCIdfromSCwhereSId='1002')groupbySIdhavingcount(*)=(selectcount(*)fromSCwhereSId='1002');DelectfromcoursewhereCourse.CId=SC.CIdandCourse.TId=Teacher.TIdandTname='InsertSCselectSId,'002',(SelectfromSCwhereCId='002')fromStudentwhereSIdnotin(SelectSIdfromSCwhere式显示：学生ID,,数据库,企业管理,英语,有效课程数,有效平均分SELECTSIdas,(SELECTscoreFROMSCWHERESC.SId=t.SIdANDCId='004')AS,(SELECTscoreFROMSCWHERESC.SId=t.SIdANDCId='001')AS,(SELECTscoreFROMSCWHERESC.SId=t.SIdANDCId='006')AS,COUNT(*)AS有效课程数AVG(t.scoreASFROMSCAStGROUPBYSIdORDERBYSELECTL.CIdAsID,L.scoreAS最高分,R.scoreAS最低分FROMSCL,SCASRWHEREL.CId=R.CIdandL.score=(SELECTFROMSCASIL,StudentASWHEREL.CId=IL.CIdandIM.SId=IL.SIdGROUPBYIL.CId)R.Score=(SELECTFROMSCASIRWHERER.CId=IR.CIdGROUPBYSELECTt.CIdAS课程号 ame)AS课程名,isnull(AVG(score),0)AS平均成,100*SUM(CASE isnull(score,0)>=60THEN1ELSE0END)/COUNT(*ASFROMSCT,CourseGROUPBYORDERBY100*SUM(CASEWHEN isnull(score,0)>=60THEN1ELSE0END)/COUNT(*)20、查询如下课程平均成绩和及格率的百分数(用"1行"显示):企业管理（001），OO&UML（003），数据库SELECTSUM(CASEWHENCId='001'THENscoreELSE0END)/SUM(CASECIdWHENTHEN1ELSE0ENDAS,100*SUM(CASEWHENCId'001ANDscore60THEN1ELSE0END)/SUM(CASEWHENCId='001'THEN1ELSE0END)AS企业管理及格百分数,SUM(CASEWHENCId'002THENscoreELSE0END)/SUM(CASECIdWHEN'002'THEN1ELSE0END)AS平均分,100*SUM(CASEWHENCId'002ANDscore60THEN1ELSE0END)/SUM(CASEWHENCId='002'THEN1ELSE0END)AS及格百分数,SUM(CASEWHENCId'003THENscoreELSE0END)/SUM(CASECIdWHEN'003'THEN1ELSE0END)ASUML平均分,100*SUM(CASEWHENCId'003ANDscore60THEN1ELSE0END)/SUM(CASEWHENCId='003'THEN1ELSE0END)ASUML及格百分数,SUM(CASEWHENCId'004THENscoreELSE0END)/SUM(CASECIdWHEN'004'THEN1ELSE0END)AS数据库平均分,100*SUM(CASEWHENCId'004ANDscore60THEN1ELSE0END)/SUM(CASEWHENCId='004'THEN1ELSE0END)AS数据库及格百分数FROMSELECTmax(Z.TId)AS教师ID,MAX(Z.Tname)AS教师,C.CIdAS课程ＩＤ,MAX( AS课程名称,AVG(Score)AS平均成绩FROMSCAST,CourseASC,TeacherASZwhereT.CId=C.CIdandC.TId=Z.TIdGROUPBYORDERBYAVG(Score) （002UM（003 DISTINCTtop3SC.SIdAs学生学号,Student.SnameAS学生,T1.scoreAS企业管理,T2.scoreAS,T3.scoreASUML,T4.scoreAS数据库,ISNULL(T1.score,0ISNULL(T2.score,0ISNULL(T3.score,0ISNULL(T4.score,0asFROM LEFTJOINSCASONSC.SId=T1.SIdANDT1.CId='001'LEFTJOINSCAST2ONSC.SId=T2.SIdANDT2.CId='002'LEFTJOINSCAST3ONSC.SId=T3.SIdANDT3.CId='003'LEFTJOINSCAST4ONSC.SId=T4.SIdANDT4.CId=WHEREstudent.SId=SC.SIdISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL(T3.score,0)+ISNULL(T4.score,0)NOTINTOP15WITHISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL(T3.score,0)+FROMLEFTJOINscASONsc.SId=T1.SIdANDT1.CId='k1'LEFTJOINscAST2ONsc.SId=T2.SIdANDT2.CId='k2'LEFTJOINscAST3ONsc.SId=T3.SIdANDT3.CId='k3'LEFTJOINscAST4ONsc.SId=T4.SIdANDT4.CId=ORDERBYISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL(T3.score,0)+ISNULL(T4.score,0)DESC);SELECTSC.CIdasID,Cnameas课程名称,SUM(CASEWHENscoreBETWEEN85AND100THEN1ELSE0END)AS[100-,SUM(CASEWHENscoreBETWEEN70AND85THEN1ELSE0END)AS[85-,SUM(CASEWHENscoreBETWEEN60AND70THEN1ELSE0END)AS[70-,SUM(CASEWHENscore<60THEN1ELSE0END)AS[60-FROMwhereSC.CId=Course.CIdGROUPBY SELECT1+(SELECTCOUNTdistinct平均成绩FROM(SELECTSId,AVG(score)ASFROMSCGROUPBYSId)ASWHERE平均成T2.平均成绩as名次,SIdas学生学号,平均成绩FROM(SELECTSId,AVG(score)平均FROMSCGROUPBYSId)ASORDERBYSELECTt1.SIdasID,t1.CIdasID,ScoreasFROMSCWHEREscoreIN(SELECTTOP3scoreFROMSCWHEREt1.CId=ORDERBYscore)ORDERBYselectcId,count(SId)fromscgroupbyselectSC.SId,Student.Sname,count(CId)ASfromSCwhereSC.SId=Student.SIdgroupbySC.SId,Student.Snamehaving28、查询男生、人Selectcount(Ssex)as男生人数fromStudentgroupbySsexhavingSsex='男';Selectcount(Ssex)as人数fromStudentgroupbySsexhavingSsex='女'；SELECTSnameFROMStudentWHERESnamelike张selectSname,count(*)fromStudentgroupbySname 31、1981年出生的学生(注：Student表中Sage列的类型是datetime)selectSname, CONVERT(char(11),DATEPART(year,Sage))asagefromstudent SelectCId,Avg(score)fromSCgroupbyCIdorderbyAvg(score),CIdDESCfromStudent,SCwhereStudent.SId=SC.SIdgroupbySC.SId,Sname fromStudent,SC,CoursewhereSC.SId=Student.SIdandSC.CId=Course.CId ame='数据库'andscoreSELECT FROMwhereSC.SId=Student.SIdandSC.CId=Course.CId36、查询任何一门课程成绩在70分以上的、课程名称和分数； distinctstudent.SId,student.Sname,SC.CId,SC.scoreFROMstudent,ScselectcIdfromscwherescore<60orderbyCId;selectSC.SId,Student.SnamefromSC,StudentwhereSC.SId=Student.SIdandScore>80andselectcount(*)fromselectfromStudent,SC,CoursewhereStudent.SId=SC.SIdandSC.CId=C.CIdandC.TId=Teacher.TIdandTeacher.Tname=''andSC.score=(selectmax(score)fromSCwhereCId=C.CId);selectcount(*)fromscgroupbyselect Id,B.scorefromSC ,SCBwhereA.Score=B.ScoreandA.CId<>B.CIdSELECTt1.SIdasID,t1.CIdasID,ScoreasFROMSCWHEREscoreIN(SELECTTOP2scoreFROMSCWHEREt1.CId=ORDERBYscore)ORDERBY CIdas课程号,count(*)as count(*) sc selectSnamefromStudentwhereSIdnotin(selectSIdfromCourse,Teacher,SCwhereCourse.TId=Teacher.TIdandSC.CId=course.CIdandTname='');selectSId,avg(isnull(score,0))fromSCwhereSIdin(selectSIdfromSCwherescore<60groupbySIdhavingcount(*)>2)groupbySId;selectSIdfromSCwhereCId='004'andscore<60orderbyscoredeletefromScwhereSId='001'and 借书卡 CNO ，CLASS班 BNO书号，BNAME书名,AUTHOR作者，PRICE单价，TY库 借书记录。CNO借书，BNO书号，RDATE还书日期找出借书超过5本的读者,输出借书及所借册数查询借阅了"水浒"一书的读者，输出及班级查询过期未还，输出借阅者（）、书号及还书日期查询书名包括"网络"的，输出书号、书名、作者查询现有中价格最高的，输出书名及作者将"C01"班同学所借的还期都延长一周从BOOKS表中删除当前无人借阅的记录如果经常按书名查询信息，请建立合适的索引BORROW表上建立一个触发器，完成如下功能：如果读者借阅的书名是"数据库技术及应用"，就将该读者的借阅记录保存在BORROW_SAVE表中（注ORROW_SAVE表结构同BORROWCREATETABLECNOintFOREIGNKEYREFERENCESCARD(CNO),BNOintFOREIGNKEYREFERENCESBOOKS(BNO),RDATEPRIMARYFROMBORROWGROUPBYCNO查询借阅了"水浒"一书的读者，输出及班SELECT*FROMCARDcWHEREEXISTS(SELECT*FROMBORROWa,BOOKSbWHEREa.BNO=b.BNO SELECT*FROMBORROW查询书名包括"网络"的，输出书号、书名、作WHEREBNAMELIKEN'%网络%'WHEREPRICE=(SELECTMAX(PRICE)FROM FROMBORROWa,BOOKSWHEREa.BNO=b.BNOANDb.BNAME=N'计算方法'ANDNOTEXISTS(SELECT*FROMBORROWaa,BOOKSbbWHEREaa.BNO=bb.BNO ORDER O将"C01"班同学所借的还期都延长一UPDATEbSETRDATE=DATEADD(Day,7,b.RDATE)FROMCARDa,BORROWb ANDDELETEAFROMBOOKSaWHERENOTEXISTS(WHEREBNO=a.BNO)