面试经常碰到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
```
网友评论