SQL经典面试50题.docx

一般面试时考SQL，主要就是考你“统计分析”这一块，下面我们来看面试官经常采用的手段。????由4张简单的不能再简单的表，演变出50道SQL????哈哈哈哈，够这个面试官面个15，20个人，不带重复的了，而且每个SQL你真的不动动脑子还写不出呢，你别不服气，下面开始。???表结构：?????表Student???(S#,Sname,Sage,Ssex)?学生表??S#???student_no?Sage????student_age???Ssex????student_sex???表Course???(C#,Cname,T#)?课程表????C#???course_no?Cname????course_name?T#???teacher_no??????表SC（学生与课程的分数mapping?表）???(S#,C#,score)?成绩表??S#??student_noC#??course_noscore???分数啦??????表Teacher???(T#,Tname)?教师表??T#??teacher_noTname???teacher_name??????50道问题开始????????1、查询“001”课程比“002”课程成绩高的所有学生的学号；???select?a.S#?from?(select?s#,score?from?SC?where?C#=001)?a,(select?s#,score??from?SC?where?C#=002)?????where?a.scoreb.score?and?a.s#=b.s#;??????2、查询平均成绩大于60分的同学的学号和平均成绩；????select?S#,avg(score)??from?sc??group?by?S#?having?avg(score)?60;????3、查询所有同学的学号、姓名、选课数、总成绩；??select?Student.S#,Student.Sname,count(SC.C#),sum(score)??from?Student?left?Outer?join?SC?on?Student.S#=SC.S#????group?by?Student.S#,Sname????4、查询姓“李”的老师的个数；??select?count(distinct(Tname))??from?Teacher??where?Tname?like?李%;??????5、查询没学过“叶平”老师课的同学的学号、姓名；??select?Student.S#,Student.Sname??from?Student??where?S#?not?in?(select?distinct(?SC.S#)?fromSC,Course,Teacher?where??SC.C#=Course.C#and?Teacher.T#=Course.T#?andTeacher.Tname=叶平);????6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；??select?Student.S#,Student.Sname?fromStudent,SC?where?Student.S#=SC.S#?andSC.C#=001and?exists(?Select?*?from?SC?as?SC_2?where?SC_2.S#=SC.S#?and?SC_2.C#=002);????7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；??select?S#,Sname??from?Student??where?S#?in?(select?S#?from?SC,Course?,Teacher?where?SC.C#=Course.C#?andTeacher.T#=Course.T#?and?Teacher.Tname=叶平group?by?S#?having?count(SC.C#)=(select?count(C#)?fromCourse,Teacher??whereTeacher.T#=Course.T#?and?Tname=叶平));??????8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；??Select?S#,Sname?from?(select?Student.S#,Student.Sname,score?,(select?score?from?SC?SC_2?where?SC_2.S#=Student.S#and?SC_2.C#=002)?score2??from?Student,SC?where?Student.S#=SC.S#?andC#=001)?S_2?where?score2?score;??9、查询所有课程成绩小于60分的同学的学