SQL测试题

1.用一条SQL 语句 查询出每门课都大于80 分的学生姓名

     name   kecheng   fenshu
     张三    语文       81
     张三     数学       75
     李四     语文       76
     王五     数学       100
     王五     英语       90

     A: select distinct name from table where name not in (select distinct name from table where fenshu<=80)Where是一个约束声明，Where是在结果返回之前起作用的，且Where中不能使用聚合函数。
     B: select name from table group by name having min(fenshu)>80Having是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作，在Having中可以使用聚合函数。

2.一个叫 team 的表，里面只有一个字段name, 一共有4 条纪录，分别是a,b,c,d, 对应四个球对，现在四个球对进行比赛，用一条sql 语句显示所有可能的比赛组合.

     select a.name, b.name from team a, team b where a.name < b.name

3.日程安排提前五分钟提醒
     select * from 日程安排 where datediff('minute',开始时间,getdate())>5

4.查询表A中存在ID重复三次以上的记录,完整的查询语句如下：
     select * from (select count(ID) as count from table group by ID)T where T.count>3

5.查询A(ID,Name)表中第31至40条记录，ID作为主键可能是不是连续增长的列，完整的查询语句如下：

     select top 10 * from A where ID >(select max(ID) from (select top 30 ID from A order by A ) T) order by A

6.触发器的作用？

     触发器是一中特殊的存储过程，主要是通过事件来触发而被执行的。它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。

7.什么是存储过程？

     存储过程是一个预编译的SQL 语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL ，使用存储过程比单纯SQL 语句执行要快。可以用一个命令对象来调用存储过程。

8.什么是内存泄漏？

     一般我们所说的内存泄漏指的是堆内存的泄漏。堆内存是程序从堆中为其分配的，大小任意的，使用完后要显示释放内存。当应用程序用关键字new 等创建对象时，就从堆中为它分配一块内存，使用完后程序调用free 或者delete 释放该内存，否则就说该内存就不能被使用，我们就说该内存被泄漏了。

9.维护数据库的完整性和一致性，你喜欢用触发器还是自写业务逻辑？为什么？

     尽可能使用约束，如check, 主键，外键，非空字段等来约束，这样做效率最高，也最方便。其次是使用触发器，这种方法可以保证，无论什么业务系统访问数据库都可以保证数据的完整新和一致性。最后考虑的是自写业务逻辑，但这样做麻烦，编程复杂，效率低下。

10.什么叫视图？什么是游标？

     视图：是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。

     游标：是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

二、

Student(S#,Sname,Sage,Ssex)学生表 Course(C#,Cname,T#)课程表 SC(S#,C#,score)成绩表 Teacher(T#,Tname)教师表

问题：

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

1	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分的同学的学号和平均成绩

1	select S#, avg(score) from sc group by S# having avg(score)>60

3、查询所有同学的学号、姓名、选课数、总成绩

1	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、查询没有学过“叶平”老师可的同学的学号、姓名：

1 2 3 4

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 Teahcer.Tname ='叶平');

5、查询学过“叶平”老师所教的所有课的同学的学号、姓名：

1 2 3 4 5 6

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='叶平'));

6、查询学过“011”并且也学过编号“002”课程的同学的学号、姓名：

1 2 3 4

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、查询至少有一门课与学号为“1001”同学所学相同的同学的学号和姓名：

1 2 3 4

select s#, Sname from Student, SC where student.s# = sc.s# and c# in (select c# from SC where s#='1001');

8、按各科平均成绩从低到高和及格率的百分数从高到低顺序：

1 2 3 4 5 6 7 8

SELECT t.C# AS 课程号, max(course.Cname)AS 课程名, isnull(AVG(score),0) AS 平均成绩, 100 * SUM(CASE WHEN  isnull(score,0)>=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

9、查询不同老师所教不同课程平均分从高到低显示：

1 2 3 4 5 6 7 8

SELECT max(Z.T#) AS 教师ID, MAX(Z.Tname) AS 教师姓名, C.C# AS 课程ID, 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

10、查询各科成绩前三名的记录（不考虑成绩并列情况）：

1 2 3 4 5 6 7

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)

11、查询每门课程被选修的学生数：

1 2 3

select c#, count(s#) from sc group by c#;

12、查询出只选修一门课程的全部学生的学号和姓名：

1 2 3 4 5

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;

13、查询男生、女生人数：

1 2 3 4 5 6 7 8

select count(Ssex) as 男生人数 from student group by Ssex having Ssex='男'； select count(Ssex) as 女生人数 from student group by Ssex having Ssex='女';

14、查询同名同姓的学生名单，并统计同名人数：

1 2 3 4

select sanme,count(*) from student group by sname havang count(*)>1;

15、1981年出生的学生名单（注：student表中sage列的类型是datetime）:

1 2 3

select sname, convert(char(11),DATEPART(year,sage)) as age from student where convert(char(11),DATEPART(year,Sage))='1981';

16、查询平均成绩大于85的所有学生的学号、姓名和平均成绩：

1 2 3 4 5

select Sname,SC.S# ,avg(score)     from Student,SC      where Student.S#=SC.S# group by SC.S#,Sname having    avg(score)>85;

17、查询课程名称为“数据库”，且分数低于60的学生名字和分数：

1 2 3

select sname, isnull(score,0) from student, sc ,course where sc.s#=student.s#  and sc.c#=course.c# and course.cname='数据库' and score<60;

18、查询所有学生的选课情况：

1 2 3

select sc.s#,sc.c#,sname,cname from sc,student course where sc.s#=student.s# and sc.c#=course.c#;

19、查询课程编号为“003”且课程成绩在80分以上的学生的学号和姓名：

1 2 3

select sc.s#,student.sname from sc,student where sc.s#=student.s# and score>80 and c#='003';

20、查询每门课程成绩最好的前两名：

1 2 3 4 5 6

select t1.s# as 学生ID,t1.c#  课程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#;

21、查询没学过”叶平”老师讲授的任一门课程的学生姓名：

1 2 3 4

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='叶平');

22、查询两门以上不及格课程的同学的学号以及其平均成绩：

1 2 3 4

select s#,avg(isnull(score,0)) from sc where s# in (select s# from sc where score<60 group by s# having count(*)>2) group by s#;