華為java面試題1.doc

Student(S#,Sname,Sage,Ssex) 學(xué)生表Course(C#,Cname,T#) 課程表SC(S#,C#,score) 成績表Teacher(T#,Tname) 教師表 問題：1、查詢“001”課程比“002”課程成績高的所有學(xué)生的學(xué)號；select a.S#from (select s#,score from SC where C#=001) a,(select s#,score from SC where C#=002) bwhere a.scoreb.score and a.s#=b.s#; 2、查詢平均成績大于60分的同學(xué)的學(xué)號和平均成績；select S#,avg(score)from scgroup by S# having avg(score) 60; 3、查詢所有同學(xué)的學(xué)號、姓名、選課數(shù)、總成績；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、查詢姓“李”的老師的個(gè)數(shù)；select count(distinct(Tname)from Teacherwhere Tname like 李%; 5、查詢沒學(xué)過“葉平”老師課的同學(xué)的學(xué)號、姓名；select Student.S#,Student.Snamefrom Studentwhere 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、查詢學(xué)過“001”并且也學(xué)過編號“002”課程的同學(xué)的學(xué)號、姓名；select Student.S#,Student.Snamefrom Student,SCwhere Student.S#=SC.S# and SC.C#=001and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#=002);7、查詢學(xué)過“葉平”老師所教的所有課的同學(xué)的學(xué)號、姓名；select S#,Snamefrom Studentwhere S# in(select S#from SC ,Course ,Teacherwhere 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=葉平); 8、查詢所有課程成績小于60分的同學(xué)的學(xué)號、姓名；select S#,Snamefrom Studentwhere S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score60); 9、查詢沒有學(xué)全所有課的同學(xué)的學(xué)號、姓名；select Student.S#,Student.Snamefrom Student,SCwhere Student.S#=SC.S#group by Student.S#,Student.Sname having count(C#) T2.平均成績) 名次, S# 學(xué)生學(xué)號,平均成績FROM (SELECT S#,AVG(score) 平均成績 FROM SC GROUP BY S# ) T2ORDER BY 平均成績 desc;14、查詢各科成績前三名的記錄:(不考慮成績并列情況)SELECT t1.S# as 學(xué)生ID,t1.C# as 課程ID,Score as 分?jǐn)?shù)FROM SC t1WHERE score IN (SELECT TOP 3 scoreFROM SCWHERE t1.C#= C#ORDER BY score DESC)ORDER BY t1.C#; 15、查詢每門功成績最好的前兩名SELECT t1.S# as 學(xué)生ID,t1.C# as 課程ID,Score as 分?jǐn)?shù)FROM SC t1WHERE score IN (SELECT TOP 2 scoreFROM SCWHERE t1.C#= C#ORDER BY score DESC )ORDER BY t1.C#;補(bǔ)充：已經(jīng)知道原表year salary2000 10002001 20002002 30002003 4000解：select b.year,sum(a.salary)from salary a,salary bwhere a.year(select max(ID) from (select top 30 ID from A order