《SQL笔试I经典44题及答案解析~》由会员分享，可在线阅读，更多相关《SQL笔试I经典44题及答案解析~（27页珍藏版）》请在金锄头文库上搜索。
1、SQL笔试I经典44题及答案解析今天这篇文章，是关于44道经典SQL测试题：01建表语句createtableStudent(sidvarchar(10),snamevarchar(10),sagedatetime,ssexnvarchar(10);insertintoStudentvalues(01,赵雷,1990-01-01,男);insertintoStudentvalues(02,钱电,1990-12-21,男);insertintoStudentvalues(03,孙风,1990-05-20,男);insertintoStudentvalues(04,李云,1990-08-06,男)
2、;insertintoStudentvalues(05,周梅,1991-12-01,女);insertintoStudentvalues(06,吴兰,1992-03-01,女);insertintoStudentvalues(07,郑竹,1989-07-01,女);insertintoStudentvalues(08,王菊,1990-01-20,女);createtableCourse(cidvarchar(10),cnamevarchar(10),tidvarchar(10);insertintoCoursevalues(01,语文,02);insertintoCoursevalues(02
3、,数学,01);insertintoCoursevalues(03,英语,03);createtableTeacher(tidvarchar(10),tnamevarchar(10);insertintoTeachervalues(01,张三);insertintoTeachervalues(02,李四);insertintoTeachervalues(03,王五);createtableSC(sidvarchar(10),cidvarchar(10),scoredecimal(18,1);insertintoSCvalues(01,01,80);insertintoSCvalues(01,0
4、2,90);insertintoSCvalues(01,03,99);insertintoSCvalues(02,01,70);insertintoSCvalues(02,02,60);insertintoSCvalues(02,03,80);insertintoSCvalues(03,01,80);insertintoSCvalues(03,02,80);insertintoSCvalues(03,03,80);insertintoSCvalues(04,01,50);insertintoSCvalues(04,02,30);insertintoSCvalues(04,03,20);inse
5、rtintoSCvalues(05,01,76);insertintoSCvalues(05,02,87);insertintoSCvalues(06,01,31);insertintoSCvalues(06,03,34);insertintoSCvalues(07,02,89);insertintoSCvalues(07,03,98);02表结构预览-学生表Student(SId,Sname,Sage,Ssex)-SId学生编号,Sname学生姓名,Sage出生年月,Ssex学生性别-课程表Course(CId,Cname,TId)-CId课程编号,Cname课程名称,TId教师编号-教师表
6、Teacher(TId,Tname)-TId教师编号,Tname教师姓名-成绩表SC(SId,CId,score)-SId学生编号,CId课程编号,score分数1.查询“01”课程比“02”课程成绩高的所有学生的学号；selectdistinctt1.sidassidfrom(select*fromscwherecid=01)t1leftjoin(select*fromscwherecid=02)t2ont1.sid=t2.sidwheret1.scoret2.score2.查询平均成绩大于60分的同学的学号和平均成绩；selectsid,avg(score)fromscgroupbysid
7、havingavg(score)603.查询所有同学的学号、姓名、选课数、总成绩selectstudent.sidassid,sname,count(distinctcid)course_cnt,sum(score)astotal_scorefromstudentleftjoinsconstudent.sid=sc.sidgroupbysid,sname4.查询姓“李”的老师的个数；selectcount(distincttid)asteacher_cntfromteacherwheretnamelike李%5.查询没学过“张三”老师课的同学的学号、姓名；selectsid,snamefrom
8、studentwheresidnotin(selectsc.sidfromteacherleftjoincourseonteacher.tid=course.tidleftjoinsconcourse.cid=sc.cidwhereteacher.tname=张三)6.查询学过“01”并且也学过编号“02”课程的同学的学号、姓名；selectt.sidassid,snamefrom(selectsid,count(if(cid=01,score,null)ascount1,count(if(cid=02,score,null)ascount2fromscgroupbysidhavingcoun
9、t(if(cid=01,score,null)0andcount(if(cid=02,score,null)0)tleftjoinstudentont.sid=student.sid7.查询学过“张三”老师所教的课的同学的学号、姓名；selectstudent.sid,snamefrom(selectdistinctcidfromcourseleftjointeacheroncourse.tid=teacher.tidwhereteacher.tname=张三)courseleftjoinsconcourse.cid=sc.cidleftjoinstudentonsc.sid=student.
10、sidgroupbystudent.sid,sname8.查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名；selectt1.sid,snamefrom(selectdistinctt1.sidassidfrom(select*fromscwherecid=01)t1leftjoin(select*fromscwherecid=02)t2ont1.sid=t2.sidwheret1.scoret2.score)t1leftjoinstudentont1.sid=student.sid9.查询所有课程成绩小于60分的同学的学号、姓名；selectt1.sid,snamef
11、rom(selectsid,max(score)fromscgroupbysidhavingmax(score60)t1leftjoinstudentont1.sid=student.sid10.查询没有学全所有课的同学的学号、姓名；selectt1.sid,snamefrom(selectcount(cid),sidfromscgroupbysidhavingcount(cid)(selectcount(distinctcid)fromcourse)t1leftjoinstudentont1.sid=student.sid11.查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名
12、；selectdistinctsc.sidfrom(selectcidfromscwheresid=01)t1leftjoinscont1.cid=sc.cid12.查询和01号的同学学习的课程完全相同的其他同学的学号和姓名#注意是和01号同学课程完全相同但非学习课程数相同的,这里我用左连接解决这个问题selectt1.sid,snamefrom(selectsc.sid,count(distinctsc.cid)from(selectcidfromscwheresid=01)t1#选出01的同学所学的课程leftjoinscont1.cid=sc.cidgroupbysc.sidhaving
13、count(distinctsc.cid)=(selectcount(distinctcid)fromscwheresid=01)t1leftjoinstudentont1.sid=student.sidwheret1.sid!=0113.把“SC”表中“张三”老师教的课的成绩都更改为此课程的平均成绩；#暂跳过update题目14.查询没学过张三老师讲授的任一门课程的学生姓名selectsnamefromstudentwheresidnotin(selectdistinctsidfromscleftjoincourseonsc.cid=course.cidleftjointeacheronco
14、urse.tid=teacher.tidwheretname=张三)15.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩selectt1.sid,sname,avg_scorefrom(selectsid,count(if(score60,cid,null),avg(score)asavg_scorefromscgroupbysidhavingcount(if(score=2)t1leftjoinstudentont1.sid=student.sid16.检索01课程分数小于60，按分数降序排列的学生信息selectsid,if(cid=01,score,100)fromscwhereif(cid=01,score,100)60orderbyif(cid=01,score,100)desc17.按平均成
