SQL 之面试宝典

Student(S#,Sname,Sage,Ssex) 学生表

Course(C#,Cname,T#) 课程表

SC(S#,C#,score) 成绩表

Teacher(T#,Tname) 教师表

1、  查询“001”课程比“002”课程成绩高的所有学生的学号；

SELECT     S#
FROM         (SELECT     S#, SUM((CASE WHEN C# = 1 THEN score END)) AS s1, SUM((CASE WHEN C# = 2 THEN score END)) AS s2
                       FROM          SC
                       GROUP BY S#) AS t
WHERE     (s1 > s2)

（分表应用）

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、  查询所有同学的学号、姓名、选课数、总成绩；(left join ,rigth join 典型应用)

SELECT     s.S#, s.Sname, COUNT(*) AS Expr1, SUM(sc.score) AS Expr2
FROM         Student AS s RIGHT OUTER JOIN
                      SC AS sc ON s.S# = sc.S#
GROUP BY s.S#, s.Sname

3、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；（典型多表查，效率不好/ 建议列转行 查）

SELECT     S#, Sname, Sage, Ssex
FROM         Student AS s
WHERE     EXISTS
                          (SELECT     a.S#
                            FROM          (SELECT     S#
                                                    FROM          SC
                                                    WHERE      (C# = 1)) AS a INNER JOIN
                                                       (SELECT     S#
                                                         FROM          SC AS SC_1
                                                         WHERE      (C# = 2)) AS b ON a.S# = b.S#
                            WHERE      (s.S# = a.S#))

4、查询至少有两门课与学号为“1”的同学所学相同的同学的学号和姓名；（最优解/ 一门/两门都可解）

select student.S# from student,sc,(select * from sc where sc.S#=1) as scc  where scc.C#=sc.C# and student.S#=sc.S# group by student.S# having count(student.S#)>1 

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#;

exists用法

SELECT     S# AS 学生ID, C# AS 课程ID, score AS 分数
FROM         SC AS t1
WHERE     EXISTS
                          (SELECT     TOP (3) score
                            FROM          SC
                            WHERE      (t1.C# = C#) AND (t1.score = score)
                            ORDER BY score DESC)
ORDER BY 课程ID, 分数 DESC