文档介绍：十几道 sql 语句面试题第1 部分: 题目: Student(S#,Sname,Sage,Ssex) 学生表 Course(C#,Cname,T#) 课程表 SC(S#,C#,score) 成绩表 Teacher(T#,Tname) 教师表问题: 1 、查询“ 001 ”课程比“ 002 ”课程成绩高的所有学生的学号; select # from (select s#,score from SC where C#= ’ 001 ′) a, (select s#,score from SC where C#= ’ 002 ′)b where > and #=#; 2 、查询平均成绩大于 60 分的同学的学号和平均成绩; select S#,avg(score) from sc group by S# having avg(score) >60; 3 、查询所有同学的学号、姓名、选课数、总成绩; select #,,count(#),sum(score) from Student left Outer join SC on #=# group by #,Sname 4 、查询姓“李”的老师的个数; select count(distinct(Tname)) from Teacher where Tname like ‘李%’;5 、查询没学过“叶平”老师课的同学的学号、姓名; select #, from Student where S# not in (select distinct( #) from SC,Course,Teacher where #=# and #=# and = ’叶平’); 6 、查询学过“ 001 ”并且也学过编号“ 002 ”课程的同学的学号、姓名; select #, from Student,SC where #=# and #= ’ 001 ′ and exists( Select * from SC as SC_2 where #=# and #= ’ 002 ′); 7 、查询学过“叶平”老师所教的所有课的同学的学号、姓名; select S#,Sname from Student where S# in (select S# from SC ,Course ,Teacher where #=# and #=# and = ’叶平’ group by S# having count(#)=(select count(C#) from Course,Teacher where #=# and Tname= ’叶平’)); 8 、查询所有课程成绩小于 60 分的同学的学号、姓名; select S#,Sname from Student where S# not in (select # from Student,SC where #=# and score>60); 9 、查询没有学全所有课的同学的学号、姓名; select #, from Student,SC where #=# group by #, having count(C#) <(select count(C#) from Course); 10 、查询至少有一门课与学号为“ 1001 ”的同学所学相同的同学的学号和姓名; select S#,Sname from Student,SC where #=# and C# in( select C# from SC where S#='1001' ); 11 、删除学****叶平”老师课的 SC 表记录; Delect SC from course ,Teacher where #=# and #= # and Tname=' 叶平'; 12 、查询各科成绩最高和最低的分:以如下形式显示:课程 ID ,最高分,最低分 SELECT # 课程 ID,L