SQL数据库面试题小汇总

??????峤

　　Student(S#,Sname,Sage,Ssex) 学生表       S#：学号；Sname：学生姓名；Sage：学生年龄；Ssex：学生性别
　　Course(C#,Cname,T#) 课程表                    C#,课程编号；Cname：课程名字；T#：教师编号
　　SC(S#,C#,score) 成绩表                             S#：学号；C#,课程编号；score：成绩
　　Teacher(T#,Tname) 教师表                        T#：教师编号； Tname：教师名字
　　问题：
　　1、查询“001”课程比“002”课程成绩高的所有学生的学号；
　　select a.S# from (select s#,score from SC where C#='001') a,(select s#,score
　　from SC where C#='002') b
　　where a.score>b.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#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平');
　　6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
　　select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'and 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# and Teacher.T#=Course.T# and Teacher.Tname='叶平' group by S# having count(SC.C#)=(select count(C#) from Course,Teacher  where Teacher.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# and C#='001') S_2 where score2 60);
　　10、查询没有学全所有课的同学的学号、姓名；
　　select Student.S#,Student.Sname
　　from Student,SC
　　where Student.S#=SC.S# group by  Student.S#,Student.Sname having count(C#) =60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数
　　FROM SC T,Course
　　where t.C#=course.C#
　　GROUP BY t.C#
　　ORDER BY 100 * SUM(CASE WHEN  isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC
　　20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），OO&UML （003），数据库（004）
　　SELECT SUM(CASE WHEN C# ='001' THEN score ELSE 0 END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分
　　,100 * SUM(CASE WHEN C# = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END) AS 企业管理及格百分数
　　,SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) AS 马克思平均分
　　,100 * SUM(CASE WHEN C# = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '002' THEN 1 ELSE 0 END) AS 马克思及格百分数
　　,SUM(CASE WHEN C# = '003' THEN score ELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS UML平均分
　　,100 * SUM(CASE WHEN C# = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '003' THEN 1 ELSE 0 END) AS UML及格百分数
　　,SUM(CASE WHEN C# = '004' THEN score ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 数据库平均分
　　,100 * SUM(CASE WHEN C# = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '004' THEN 1 ELSE 0 END) AS 数据库及格百分数
　　FROM SC
　　21、查询不同老师所教不同课程平均分从高到低显示
　　SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS 课程ＩＤ,MAX(C.Cname) AS 课程名称,AVG(Score) AS 平均成绩
　　FROM SC AS T,Course AS C ,Teacher AS Z
　　where T.C#=C.C# and C.T#=Z.T#
　　GROUP BY C.C#
　　ORDER BY AVG(Score) DESC
　　22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理（001），马克思（002），UML （003），数据库（004）
　　[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩
　　SELECT  DISTINCT top 3
　　SC.S# As 学生学号,
　　Student.Sname AS 学生姓名 ,
　　T1.score AS 企业管理,
　　T2.score AS 马克思,
　　T3.score AS UML,
　　T4.score AS 数据库,
　　ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as 总分
　　FROM Student,SC  LEFT JOIN SC AS T1
　　ON SC.S# = T1.S# AND T1.C# = '001'
　　LEFT JOIN SC AS T2
　　ON SC.S# = T2.S# AND T2.C# = '002'
　　LEFT JOIN SC AS T3
　　ON SC.S# = T3.S# AND T3.C# = '003'
　　LEFT JOIN SC AS T4
　　ON SC.S# = T4.S# AND T4.C# = '004'
　　WHERE student.S#=SC.S# and
　　ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
　　NOT IN
　　(SELECT
　　DISTINCT
　　TOP 15 WITH TIES
　　ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
　　FROM sc
　　LEFT JOIN sc AS T1
　　ON sc.S# = T1.S# AND T1.C# = 'k1'
　　LEFT JOIN sc AS T2
　　ON sc.S# = T2.S# AND T2.C# = 'k2'
　　LEFT JOIN sc AS T3
　　ON sc.S# = T3.S# AND T3.C# = 'k3'
　　LEFT JOIN sc AS T4
　　ON sc.S# = T4.S# AND T4.C# = 'k4'
　　ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC);
　　23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[  T2.平均成绩) as 名次,
　　S# as 学生学号,平均成绩
　　FROM (SELECT S#,AVG(score) 平均成绩
　　FROM SC
　　GROUP BY S#
　　) AS T2
　　ORDER BY 平均成绩 desc;
　　25、查询各科成绩前三名的记录:(不考虑成绩并列情况)
　　SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
　　FROM SC t1
　　WHERE score IN (SELECT TOP 3 score
　　FROM SC
　　WHERE t1.C#= C#
　　ORDER BY score DESC
　　)
　　ORDER BY t1.C#;
　　26、查询每门课程被选修的学生数
　　select c#,count(S#) from sc group by C#;
　　27、查询出只选修了一门课程的全部学生的学号和姓名
　　select SC.S#,Student.Sname,count(C#) AS 选课数
　　from SC ,Student
　　where SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1;
　　28、查询男生、女生人数
　　Select count(Ssex) as 男生人数 from Student group by Ssex having Ssex='男';
　　Select count(Ssex) as 女生人数 from Student group by Ssex having Ssex='女'；
　　29、查询姓“张”的学生名单
　　SELECT Sname FROM Student WHERE Sname like '张%';
　　30、查询同名同性学生名单，并统计同名人数
　　select Sname,count(*) from Student group by Sname having  count(*)>1;;
　　31、1981年出生的学生名单(注：Student表中Sage列的类型是datetime)
　　select Sname,  CONVERT(char (11),DATEPART(year,Sage)) as age
　　from student
　　where  CONVERT(char(11),DATEPART(year,Sage))='1981';
　　32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
　　Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC ;
　　33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
　　select Sname,SC.S# ,avg(score)
　　from Student,SC
　　where Student.S#=SC.S# group by SC.S#,Sname having    avg(score)>85;
　　34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数
　　Select Sname,isnull(score,0)
　　from Student,SC,Course
　　where SC.S#=Student.S# and SC.C#=Course.C# and  Course.Cname='数据库'and score =70 AND SC.S#=student.S#;
　　37、查询不及格的课程，并按课程号从大到小排列
　　select c# from sc where scor e 80 and C#='003';
　　39、求选了课程的学生人数
　　select count(*) from sc;
　　40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩
　　select Student.Sname,score
　　from Student,SC,Course C,Teacher
　　where Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T# and Teacher.Tname='叶平' and SC.score=(select max(score)from SC where C#=C.C# );
　　41、查询各个课程及相应的选修人数
　　select count(*) from sc group by C#;
　　42、查询不同课程成绩相同的学生的学号、课程号、学生成绩
　　select distinct  A.S#,B.score from SC A  ,SC B where A.Score=B.Score and A.C# B.C# ;
　　43、查询每门功成绩最好的前两名
　　SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
　　FROM SC t1
　　WHERE score IN (SELECT TOP 2 score
　　FROM SC
　　WHERE t1.C#= C#
　　ORDER BY score DESC
　　)
　　ORDER BY t1.C#;
　　44、统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序排列，若人数相同，按课程号升序排列
　　select  C# as 课程号,count(*) as 人数
　　from  sc
　　group  by  C#
　　order  by  count(*) desc,c#
　　45、检索至少选修两门课程的学生学号
　　select  S#
　　from  sc
　　group  by  s#
　　having  count(*)  >  =  2
　　46、查询全部学生都选修的课程的课程号和课程名
　　select  C#,Cname
　　from  Course
　　where  C#  in  (select  c#  from  sc group  by  c#)
　　47、查询没学过“叶平”老师讲授的任一门课程的学生姓名
　　select Sname from Student where S# not in (select S# from Course,Teacher,SC where Course.T#=Teacher.T# and SC.C#=course.C# and Tname='叶平');
　　48、查询两门以上不及格课程的同学的学号及其平均成绩
　　select S#,avg(isnull(score,0)) from SC where S# in (select S# from SC where score 2)group by S#;
　　49、检索“004”课程分数小于60，按分数降序排列的同学学号
　　select S# from SC where C#='004'and score