50SQL语句题目及答案.doc

/*Student(S#,Sname,Sage,Ssex) 学生表 Course(C#,Cname,T#) 课程表 SC(S#,C#,score) 成绩表 Teacher(T#,Tname) 教师表 */-1、查询“001”课程比“002”课程成绩高的所有学生的学号； select s1.s#from SC s1,SC s2where s1.s#=s2.s# and s1.c#=001 and s2.c#=002 and s1.scores2.score;-2、查询平均成绩大于60分的同学的学号和平均成绩； select s.s#,avg(s.score)from SC sgroup by s.s#having avg(s.score)60;-3、查询所有同学的学号、姓名 、选课数、总成绩； select d.s#,max(d.sname),count(distinct s.c#),sum(s.score)from Student d,SC swhere d.s#=s.s#group by d.s#;-4、查询姓“李”的老师的个数； select count(0)from Teacher twhere t.tname like 李%;-5、查询没学过“叶平”老师课的同学的学号、姓名； select d.s#,d.snamefrom Student d where not exists ( select distinct(s.s#) from SC s,Teacher t,Course c where t.t#=c.t# and s.c#=c.c# and t.tname=叶平 and d.s#=s.s#);-6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名； select d.s#, d.snamefrom Student d, SC swhere s.s# = d.s# and s.c# = 001 and exists (select s1.s# from SC s1 where s1.s# = s.s# and s1.c# = 002); -7、查询学过“叶平”老师所教的所有课的同学的学号、姓名； select s.s#,max(d.sname)from SC s,Student d,Course c,Teacher twhere s.s#=d.s# and s.c#=c.c# and c.t#=t.t# and t.tname=叶平group by s.s#having count(distinct s.c#) = ( select count(0) from Course c,Teacher t where c.t#=t.t# and t.tname=叶平); -8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名； select d.s#,d.snamefrom SC s1,SC s2,Student dwhere s1.s#=s2.s# and s1.s#=d.s# and s1.c#=002and s2.c#=001 and s1.scores2.score;-9、查询所有课程成绩小于60分的同学的学号、姓名； select s.s#,max(d.sname)from Student d,SC swhere d.s#=s.s# and s.score60group by s.s#having count(0) = (select count(0) from SC s1 where s.s#=s1.s#);-10、查询没有学全所有课的同学的学号、姓名； select s.s#,max(d.sname)from Student d,SC swhere d.s#=s.s#group by s.s#having count(distinct s.c#) (select count(0) from Course);-11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；select distinct s.s#,d.snamefrom Student d,SC swhere d.s#=s.s# and s.s#1001 and exists( select s1.c# from SC s1 where s1.s#=1001 and s.c#=s1.c# );-12、查询至少学过学号为“1010”同学所有一门课的其他同学学号和姓名； select distinct s.s#,d.snamefrom Student d,SC swhere d.s#=s.s# and s.s#1010 and s.c# in( select s1.c# from SC s1 where s1.s#=1010);-13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩； update SC sset s.score = (select avg(s1.score) from SC s1 where s.c# = s1.c#)where exists (select s.c# from Course c, Teacher t where s.c# = c.c# and c.t# = t.t# and t.tname = 叶平); -14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名； select s.s#,max(d.sname)from Student d,SC swhere d.s#=s.s# and s.s#1002and exists( select c# from SC where s#=1002 and s.c#=c#)group by s.s#having count(0)=(select count(0) from SC where s#=1002)and count(select count(0) from SC where s#=s.s#)=(select count(0) from SC where s#=1002);-15、删除学习“叶平”老师课的SC表记录； delete SC s where exists(select s.c# from Course c,Teacher t where s.c#=c.c# and c.t#=t.t# and t.tname=叶平); -16、向SC表中插入一些记录，这些记录要求符合以下条件：- 没有上过编号“003”课程的同学学号、002号课的平均成绩;insert into SC (select d.s#,002,(select round(avg(score),2) from SC where c#=002)from Student d where not exists (select distinct s.s# from SC s where s.s#=d.s# and s.c#=003);-17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，- 按如下形式显示： 学生ID,数据库,企业管理,英语,有效课程数,有效平均分;select s.s# as 学生ID, (select avg(s1.score) from SC s1,Course c where s1.c#=c.c# and s.s#=s1.s# and ame=Java编程思想 ) as Java编程思想, (select avg(s1.score) from SC s1,Course c where s1.c#=c.c# and s.s#=s1.s# and ame=Struts2基础 ) as Struts2基础, (select avg(s1.score) from SC s1,Course c where s1.c#=c.c# and s.s#=s1.s# and ame=Spring基础 ) as Spring基础, count(0) as 有效课程数,round(avg(s.score),2) as 有效平均分from SC s,Course c where s.c#=c.c# and ame in(Java编程思想,Struts2基础,Spring基础) group by s.s#order by round(avg(s.score),2) desc;-18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 select s.c# as 课程ID,max(s.score) as 最高分,min(s.score) as 最低分from SC sgroup by s.c#;-19、按各科平均成绩从低到高和及格率的百分数从高到低顺序 select s.c# as 课程ID,round(avg(s.score),2) as 平均分, round(100*sum(case when s.score=60 then 1 else 0 end)/count(s.score),0)|% as 及格率 from SC sgroup by s.c#;-20、查询如下课程平均成绩和及格率的百分数(用1行显示): - 企业管理（001），马克思（002），OO&UML （003），数据库（004） select round(sum(case when ame=Java编程思想 then s.score else 0 end)/sum(case ame when Java编程思想 then 1 else 0 end),2) as Java编程思想, round(100*sum(case when s.score = 60 and ame=Java编程思想 then 1 else 0 end)/sum(case when ame=Java编程思想 then 1 else 0 end),0)|% as 及格率, round(sum(case when ame=Struts2基础 then s.score else 0 end)/sum(case ame when Struts2基础 then 1 else 0 end),2) as Struts2基础, round(100*sum(case when s.score = 60 and ame=Struts2基础 then 1 else 0 end)/sum(case when ame=Struts2基础 then 1 else 0 end),0)|% as 及格率from SC s,Course cwhere s.c#=c.c#;-21、查询不同老师所教不同课程平均分从高到低显示 - SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,- C.C# AS 课程,MAX(C.Cname) AS 课程名称,AVG(Score) AS 平均成绩 select t.t# as 教师ID, max(t.tname) as 教师名称, ame as 所教课程, round(avg(s.score), 2) as 平均分from Teacher t, Course c, SC swhere t.t# = c.t# and c.c# = s.c#group by t.t#, ameorder by t.t#, round(avg(s.score), 2) desc;-22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：- 企业管理（001），马克思（002），UML （003），数据库（004） select d.s#,d.sname,ame,s.score,s.rank1 as 名次from Student d,Course c, (select s#,c#,score,row_number() over(partition by c# order by score desc) rank1 from SC) swhere s.c#=c.c# and d.s#=s.s# and ame in(Java编程思想,Struts2基础,Hibernate基础,Spring基础) and (s.rank1 between 3 and 6)order by c.c#,s.rank1;-23、统计列印各科成绩,各分数段人数:课程ID,课程名称,100-85,85-70,70-60, 60 select s.c#,max(ame), sum(case when s.score between 85 and 100 then 1 else 0 end) as 分数85至100, sum(case when s.score between 70 and 85 then 1 else 0 end) as 分数70至85, sum(case when s.score between 60 and 70 then 1 else 0 end) as 分数60至70, sum(case when s.score between 0 and 60 then 1 else 0 end) as 小于60from SC s,Course cwhere s.c#=c.c#group by s.c#;-24、查询学生平均成绩及其名次 select d.s#,max(d.sname),round(avg(s.score),2) as 平均分, dense_rank() over(order by avg(s.score) desc) as 名次from Student d,SC swhere d.s#=s.s#group by d.s#;-25、查询各科成绩前三名的记录:(不考虑成绩并列情况) select c.c# as 课程编号,ame as 课程名称, d.sname as 学生名称,s.rank1 as 名次,s.score as 成绩from (select s#,c#,score, row_number() over(partition by c# order by score desc) rank1 from sc ) s,Course c,Student dwhere s.c#=c.c# and s.s#=d.s# and rank1 1;-31、1981年出生的学生名单(注：Student表中Sage列的类型是datetime) select *from Student dwhere to_char(d.sage,yyyy) = 1981;-32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列 select s.c#,round(avg(s.score),2) as 平均分from SC sgroup by s.c#order by avg(s.score),s.c# desc;-33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩 select s.s#,max(d.sname),round(avg(s.score),2) as 平均分from SC s,Student dwhere s.s#=d.s#group by s.s#having avg(s.score)85;-34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数select d.sname,s.scorefrom Student d,SC s,Course cwhere d.s#=s.s# and s.c#=c.c# and ame=Java编程思想 and s.score70group by s.s#,s.scorehaving count(0) = (select count(s1.c#) from SC s1 where s.s#=s1.s#);-37、查询不及格的课程，并按课程号从大到小排列 select s.s#,ame,s.scorefrom SC s,Course cwhere s.c#=c.c# and s.score80;-39、求选了课程的学生人数 select count(distinct s.s#) as 选课的人数from SC s;-40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩select sname,score from (select d.sname,s.score,row_number() over(order by s.score desc) as rank1 from Student d,SC s,Course c,Teacher t where d.s#=s.s# and s.c#=c.c# and c.t#=t.t# and t.tname=叶平)where rank1=1;-41、查询各个课程及相应的选修人数 select s.c#,count(distinct s.s#) as 选修人数 from SC sgroup by s.c#;-42、查询不同课程成绩相同的学生的学号、课程号、学生成绩 select *from SC s1,SC s2where s1.c#s2.c# and s1.score=s2.score and s1.s#s2.s#;-43、查询每门功成绩最好的前两名 select d.s#,d.sname,s.c#,s.score,s.rank1 from (select s1.s#,s1.c#,s1.score, row_number() over(partition by s1.c# order