2020-05-28SQL笔试 I 经典20题及答案解析(上)

面试经常碰到SQL面试题，今天给大家收集了20道SQL经典面试题，三人行必有我师！

01 建表语句

```createtableStudent(sidvarchar(10),snamevarchar(10),sage datetime,ssexnvarchar(10));insertintoStudentvalues('01','赵雷','1990-01-01','男');insertintoStudentvalues('02','钱电','1990-12-21','男');insertintoStudentvalues('03','孙风','1990-05-20','男');insertintoStudentvalues('04','李云','1990-08-06','男');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','数学','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','02',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);insertintoSCvalues('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 教师编号

--教师表

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.score>t2.score

```

3. 查询所有同学的学号、姓名、选课数、总成绩

```

selectstudent.sidassid ,sname,count(distinctcid) course_cnt,sum(score)astotal_scorefromstudentleftjoinsconstudent.sid=sc.sidgroup by sid,sname

```

4. 查询姓“李”的老师的个数；

```

selectcount(distincttid)asteacher_cntfromteacherwheretnamelike'李%'

```

5. 查询没学过“张三”老师课的同学的学号、姓名；

```

selectsid,snamefromstudentwheresidnotin (select sc.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))ascount2fromscgroupbysidhavingcount(if(cid='01',score,null))>0andcount(if(cid='02',score,null))>0 )tleftjoinstudentont.sid=student.sid

```

7. 查询学过“张三”老师所教的课的同学的学号、姓名；

```

select student.sid ,snamefrom (selectdistinctcidfromcourseleftjointeacheroncourse.tid=teacher.tidwhereteacher.tname='张三' )courseleftjoinsconcourse.cid=sc.cidleftjoinstudentonsc.sid=student.sidgroupbystudent.sid,sname

```

8. 查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名；

```

select t1.sid,snamefrom (selectdistinctt1.sidassidfrom(select*fromscwherecid='01')t1leftjoin(select*fromscwherecid='02')t2ont1.sid=t2.sidwheret1.score>t2.score )t1leftjoinstudentont1.sid=student.sid

```

9. 查询所有课程成绩小于60分的同学的学号、姓名；

```

select t1.sid,snamefrom (selectsid,max(score)fromscgroupbysidhavingmax(score<60) )t1leftjoinstudentont1.sid=student.sid

```

10. 查询没有学全所有课的同学的学号、姓名；

```

select t1.sid,snamefrom (selectcount(cid),sidfromscgroupbysidhavingcount(cid) < (selectcount(distinctcid)fromcourse) )t1leftjoinstudentont1.sid=student.sid

```

11. 查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名；

```

selectdistinctsc.sidfrom (select cidfromscwheresid='01' )t1leftjoinscont1.cid=sc.cid

```

12. 查询和"01"号的同学学习的课程完全相同的其他同学的学号和姓名

```

#注意是和'01'号同学课程完全相同但非学习课程数相同的,这里我用左连接解决这个问题select t1.sid,snamefrom (select sc.sid,count(distinctsc.cid)from (select cidfromscwheresid='01')t1#选出01的同学所学的课程leftjoinscont1.cid=sc.cidgroupbysc.sidhavingcount(distinctsc.cid)= (selectcount(distinctcid)fromscwheresid='01') )t1leftjoinstudentont1.sid=student.sidwheret1.sid!='01'

```

13. 把“SC”表中“张三”老师教的课的成绩都更改为此课程的平均成绩；

```

#暂跳过update题目

```

14. 查询没学过"张三"老师讲授的任一门课程的学生姓名

```

select snamefromstudentwheresidnotin (selectdistinctsidfromscleftjoincourseonsc.cid=course.cidleftjointeacheroncourse.tid=teacher.tidwheretname='张三' )

```

15. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

```

select t1.sid,sname,avg_scorefrom (selectsid,count(if(score<60,cid,null)),avg(score)asavg_scorefromscgroupbysidhavingcount(if(score<60,cid,null)) >=2 )t1leftjoinstudentont1.sid=student.sid

```

16. 检索"01"课程分数小于60，按分数降序排列的学生信息

```

selectsid,if(cid='01',score,100)fromscwhereif(cid='01',score,100)<60orderbyif(cid='01',score,100)desc

```

17. 按平均成绩从高到低显示所有学生的平均成绩

```

selectsid,avg(score)fromscgroupbysidorderbyavg(score)desc

```

18. 查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率

```

select sc.cid ,cname,max(score)asmax_score,min(score)asmin_score,avg(score)asavg_score,count(if(score>=60,sid,null))/count(sid)aspass_ratefromscleftjoincourseonsc.cid=course.cidgroupbysc.cid

```

19. 按各科平均成绩从低到高和及格率的百分数从高到低顺序

```

#这里先按照平均成绩排序，再按照及格百分数排序，select cid,avg(score)asavg_score,count(if(score>=60,sid,null))/count(sid)aspass_ratefromscgroupbycidorderbyavg_score,pass_ratedesc

```

20. 查询学生的总成绩并进行排名

```

selectsid,sum(score)assum_scorefromscgroupbysidorderbysum_scoredesc

```