sql优化面试题.docxVIP

sql优化面试题　　问题：　　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 #=001and exists( Select * from SC as SC_2 where SC_#=# and SC_#=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、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;　　Select S#,Sname from (select #,,score ,(select score from SC SC_2 where SC_#=# and SC_#=002) score2　　from Student,SC where #=# and C#=001) S_2 where score2　　9、查询所有课程成绩小于60分的同学的学号、姓名;　　select S#,Sname　　from Student　　where S# not in (select # from Student,SC where #=# and score60);　　10、查询没有学全所有课的同学的学号、姓名;　　select #,　　from Student,SC　　where #=# group by #, having count(C#) 　　11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;　　select S#,Sname from Student,SC where #=# and C# in select C# from SC where S#=1001;　　12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名;　　select distinct #,Sname　　from Student,SC　　where #=# and C# in (select C# from SC where S#=001);　　13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;　　SC set score=(select avg(SC_)　　from SC SC_2　　where SC_#=# ) from Course,Teacher where #=# and #=# and =叶平);　　14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名;　　select S# from SC where C# in (select C# from SC where S#=1002)　　group by S# having count(*)=(select count(*) from SC where S#=1002);　　15、删除学习“叶平”老师课的SC表记录;　　Delect SC