sql面试题详解2

接着下午的题目，这次也是分享5个sql笔试题，表结构就不给出来了

1.查询学过“001”并且也学过“002”课程的同学的学号、姓名

这个题目有2种常规做法，一种是使用exists,另一种是使用intersect。我推荐使用后面这种，sql语句如下

Select A.studentnum ,B.sname From t_score A,t_student B Where A.coursenum='c001'  And A.studentnum=B.studentnum
Intersect
Select A.studentnum,B.sname From t_score A,t_student B Where A.coursenum='c002'  And A.studentnum=B.studentnum

2.查询所有课程成绩小于60分的同学的学号、姓名

对于这样的sql网上也没有固定的答案，我看到有人用的是in或者not in,个人觉得这两种方法太麻烦，像下面这样写也可以

Select Distinct(A.studentnum),B.sname From t_score A,t_student B Where A.studentnum=B.studentnum And A.score<60 

3.查询没有学全所有课的同学的学号、姓名

这里主要是子查询和having的使用，语句如下

Select B.studentnum,B.sname From t_score A,t_student B Where A.studentnum=B.studentnum Group By B.studentnum,B.sname 
Having Count(A.coursenum)< (Select Count(coursenum) From t_course)

4.查询至少有一门课与学号为“1001”的同学所学相同的学生的学号和姓名

注意关键字“至少”，一般有这两个字基本上都会用到in查询，首先查询出1001同学所学的课程编号作为in条件就OK了

Select A.studentnum,A.sname From t_student A,t_score B Where A.studentnum=B.studentnum  And A.studentnum!='2009010500'And B.coursenum
In
(Select coursenum From t_score Where studentnum='2009010500')
Group By A.studentnum,A.sname

5.把成绩表中“叶平”老师教的课的成绩都改为该课程的平均成绩

这个题目稍微复杂一点，要点在于先求出叶平老师所授课的平均成绩和所教课程的课程编号。这2个关键数据求出来就没什么问题了

Update t_score Set score=(Select Avg(C.score) avgscore From t_course A,t_teacher B,t_score C Where A.teachernum=B.teachernum And B.tname='叶平' 
And A.coursenum=C.coursenum) 
Where coursenum=(Select A.coursenum  From t_course A,t_teacher B Where A.teachernum=B.teachernum And B.tname='叶平')

这种写法有点复杂但是看上去比较好理解

6.查询和“1002”号同学学习的课程完全相同的其他同学的学号和姓名

主要考察子查询、having、group by的使用，严谨的sql如下

Select A.studentnum,A.sname From t_student A,t_score B
Where A.studentnum=B.studentnum
And B.coursenum In (Select coursenum From t_score Where studentnum='2009010503') 
And A.studentnum!='2009010503'
Group By A.studentnum,A.sname Having Count( A.studentnum)=(Select count(coursenum) From t_score Where studentnum='2009010503')

7.删除学习“叶平”老师课的成绩表记录

这个题目主要考察删除和查询语句的使用，其中还使用到了子查询，sql语句如下

Delete From t_score Where coursenum=(Select coursenum From t_teacher A,t_course B Where A.teachernum=B.teachernum and A.tname='叶平2')

8.按平均成绩从高到低显示所有学生的“语文”、“数学”、“英语”三门课程的成绩，按如下形式显示显示：学生ID，语文、数学、英语、有效课程数、有效平均分

这个题目就复杂了，倒不是说sql有多复杂，主要是这种写sql的方式很少见，sql语句如下

Select t.studentnum As 学生ID,
(Select score From t_score A Where A.studentnum=t.studentnum And A.coursenum='c002') As 语文,
(Select score From t_score A Where A.studentnum=t.studentnum And A.coursenum='c003') As 数学,
(Select score From t_score A Where A.studentnum=t.studentnum And A.coursenum='c004') As 英语,
Count(*) As 有效课程,Avg(t.score) 有效平均分
From t_score t
Group By t.studentnum
Order By Avg(t.score) Desc

9.查询各科成绩最高和最低分：以如下形式显示：课程ID，最高分，最低分

这个题还是比较复杂，这里需要使用自连接查询，只要理解清楚自连接的用法就能写出完整的失去了语句了

Select distinct(A.coursenum) As 课程ID,A.score As 最高分
From t_score A,t_score B Where A.coursenum=B.coursenum
And A.score=(
  Select Max(score) From t_score C Where C.coursenum=A.coursenum Group By C.coursenum
)
And B.score=(
   Select Min(score) From t_score D Where D.coursenum=B.coursenum Group By D.coursenum
)

对于这个题目网上也提供了答案，但是这些答案没有经过实际验证，不少答案都存在问题。

10.查询不同老师所教不同课程平均分从高到低显示，包括教师编号，教师名称，课程名称，平均成绩

这个题也超简单，只需要把课程表、成绩表、教师信息表关联起来，用上avg函数之后分组就可以了

Select B.teachernum,B.tname,C.cname,Avg(A.score) As 平均成绩 From t_score A,t_teacher B,t_course C
Where B.teachernum=C.teachernum And A.coursenum=C.coursenum Group By B.teachernum,B.tname,C.cname
Order By Avg(A.score) Desc