经典sql面试题附答案

根据给定的SQL题目及其答案，我们可以总结出一系列重要的SQL知识点和技巧，这些知识点对于学习SQL及准备面试都非常有帮助。 ### 1. 比较两个不同表中的记录 **知识点**: 子查询和连接操作是解决此类问题的关键技术。通过在两个不同的表中比较相同字段的数据来找出符合条件的记录。 **示例**: 在第一个问题中，我们通过子查询分别获取了“001”和“002”两门课程的成绩，并通过外部查询将这两组数据进行比较，找出成绩更高的学生学号。 ```sql 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. 使用聚合函数与HAVING子句 **知识点**: AVG()函数用于计算平均值，GROUP BY子句用于对结果集进行分组，HAVING子句则用于过滤这些分组后的结果。 **示例**: 第二个问题展示了如何使用这些功能来找出平均成绩大于60分的学生。 ```sql select S#, avg(score) from sc group by S# having avg(score) > 60; ``` ### 3. 左连接与聚合函数结合使用 **知识点**: LEFT JOIN用于确保左侧表中的所有记录都会出现在结果集中，即使右侧表中没有匹配的记录。与聚合函数结合使用可以统计每位学生的选课数量和总成绩。 **示例**: 第三个问题中，我们使用LEFT JOIN连接学生表和成绩表，然后通过GROUP BY进行分组统计。 ```sql select Student.S#, Student.Sname, count(SC.C#), sum(score) from Student left join SC on Student.S# = SC.S# group by Student.S#, Sname ``` ### 4. 使用LIKE操作符进行模糊匹配 **知识点**: LIKE操作符允许我们在WHERE子句中使用通配符来搜索模糊匹配的字符串。 **示例**: 第四个问题中，我们利用LIKE '李%'来找出所有名字以“李”开头的老师。 ```sql select count(distinct(Tname)) from Teacher where Tname like '李%'; ``` ### 5. 使用NOT IN排除特定条件 **知识点**: NOT IN操作符可以帮助我们排除指定集合中的值，适用于查找不包含某些值的记录。 **示例**: 在第五个问题中，我们找出没有上过“叶平”老师课程的学生。 ```sql 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. 存在性子查询的应用 **知识点**: EXISTS子句用于检查子查询的结果集是否为空，通常用于判断某个条件是否存在。 **示例**: 第六个问题展示了如何使用EXISTS来找出同时选修了“001”和“002”课程的学生。 ```sql 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. 多表连接与子查询嵌套 **知识点**: 当需要从多个表中获取数据并进行复杂的逻辑判断时，可以使用多表连接配合子查询嵌套。 **示例**: 第七个问题中，我们通过多表连接以及嵌套子查询找出了学过“叶平”老师所有课程的学生。 ```sql 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”低的所有学生。 ```sql 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 < score; ``` ### 9. 排除特定条件 **知识点**: NOT IN和NOT EXISTS是两种常用的排除特定条件的方法，它们在处理NULL值时有所不同。 **示例**: 第九个问题使用NOT IN来找出所有课程成绩小于60分的学生。 ```sql select S#, Sname from Student where S# not in (select Student.S# from Student, SC where S.S# = SC.S# and score > 60); ``` ### 10. 分组后的条件筛选 **知识点**: GROUP BY配合HAVING子句可以实现对分组后的数据进行进一步的筛选。 **示例**: 第十个问题中，我们使用GROUP BY和HAVING来找出没有学全所有课程的学生。 ```sql select Student.S#, Student.Sname from Student, SC where Student.S# = SC.S# group by Student.S#, Student.Sname having count(C#) < (select count(C#) from Course); ``` ### 11. 利用IN操作符简化查询 **知识点**: IN操作符可以用于匹配一组值中的任意一个，非常适合于简化查询语句。 **示例**: 第十一个问题中，我们使用IN操作符来找出与学号为“1001”的同学所学课程相同的其他同学。 ```sql select S#, Sname from Student, SC where Student.S# = SC.S# and C# in select C# from SC where S# = '1001'; ``` ### 12. 基于已知条件的扩展查询 **知识点**: 当已经知道某些条件时，可以通过扩展这些条件来进一步筛选数据。 **示例**: 第十二个问题中，我们基于已知的学号“001”同学的课程信息，找出所有学过他所学课程的其他同学。 ```sql select distinct SC.S#, Sname from Student, SC where Student.S# = SC.S# and C# in (select C# from SC where S# = '001'); ``` ### 13. 表更新操作 **知识点**: UPDATE语句用于修改表中的数据。通常需要指定哪些列被更新以及更新的条件是什么。 **示例**: 虽然题目只给出了前面的部分，但可以推测这里可能涉及到了对SC表进行某种更新操作。 通过以上分析，我们可以看到这些问题涵盖了SQL的基础知识到高级应用，包括连接操作、聚合函数、子查询、条件判断等多个方面。这些技能不仅对准备SQL面试非常有用，也是日常开发工作中不可或缺的能力。