Failure is not terrible. The terrible thing is that I have never worked hard and comforted myself comfortably. Even a little regret is covered by numbness.悉心整理　祝您一臂之力（WORD文档/A4打印/可编辑/页眉可删）
数据仓库面试题及答案
  Student(S#,Sname,Sage,Sse-x) 学生表 S#：学号；Sname：学生姓名；Sage：学生年龄；Sse-x：学生性别
  Course(C#,Cname,T#) 课程表 C#,课程编号；Cname：课程名字；T#：教师编号
  SC(S#,C#,score) 成绩表 S#：学号；C#,课程编号；score：成绩
  Teacher(T#,Tname) 教师表 T#：教师编号； Tname：教师名字
多表查询sql语句面试题
  问题：
  1、查询“001”课程比“002”课程成绩高的所有学生的学号；
  select a.S# from (select s#,score from SC where C#='001') a,(select s#,score
  from SC where C#='002') b
  where a.scoreb.score and a.s#=b.s#;
  2、查询平均成绩大于60分的同学的学号和平均成绩；
  select S#,avg(score)
  from sc
  group by S# having avg(score) 60;
  3、查询所有同学的学号、姓名、选课数、总成绩；
  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、查询姓“李”的老师的个数；
  select count(distinct(Tname))
  from Teacher
  where Tname like '李%';
  5、查询没学过“叶平”老师课的同学的学号、姓名；
  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、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
  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、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
  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='叶平'));

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。