Java面试题-数据库篇十二

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Java面试题-数据库篇十二，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4408字，纯文字阅读大概需要7分钟。

内容图文

111、把“sc”表中“王五”所教课的成绩都更改为此课程的平均成绩

update sc set score = (select avg(sc_2.score) from sc sc_2 wheresc_2.cno=sc.cno)

from course,teacher where course.cno=sc.cno and course.tno=teacher.tno andteacher.tname='王五'

112、查询和编号为2的同学学习的课程完全相同的其他同学学号和姓名

这一题分两步查：

1，

select sno

from sc

where sno <> 2

group by sno

having sum(cno) = (select sum(cno) from sc where sno = 2)

2，

select b.sno, b.sname

from sc a, student b

where b.sno <> 2 and a.sno = b.sno

group by b.sno, b.sname

having sum(cno) = (select sum(cno) from sc where sno = 2)

113、删除学习“王五”老师课的sc表记录

delete sc from course, teacher

where course.cno = sc.cno and course.tno = teacher.tno and tname = '王五'

114、向sc表中插入一些记录，这些记录要求符合以下条件：

将没有课程3成绩同学的该成绩补齐, 其成绩取所有学生的课程2的平均成绩

insert sc select sno, 3, (select avg(score) from sc where cno = 2)

from student

where sno not in (select sno from sc where cno = 3)

115、按平平均分从高到低显示所有学生的如下统计报表：

-- 学号,企业管理,马克思,UML,数据库,物理,课程数,平均分

select sno as 学号

,max(case when cno = 1 then score end) AS 企业管理

,max(case when cno = 2 then score end) AS 马克思

,max(case when cno = 3 then score end) AS UML

,max(case when cno = 4 then score end) AS 数据库

,max(case when cno = 5 then score end) AS 物理

,count(cno) AS 课程数

,avg(score) AS 平均分

FROM sc

GROUP by sno

ORDER by avg(score) DESC

116、查询各科成绩最高分和最低分：

以如下形式显示：课程号，最高分，最低分

select cno as 课程号, max(score) as 最高分, min(score) 最低分

from sc group by cno

select course.cno as '课程号'

,MAX(score) as '最高分'

,MIN(score) as '最低分'

from sc,course

where sc.cno=course.cno

group by course.cno

117、按各科平均成绩从低到高和及格率的百分数从高到低顺序

SELECT t.cno AS 课程号,

max(course.cname)AS 课程名,

isnull(AVG(score),0) AS 平均成绩,

100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/count(1) AS 及格率

FROM sc t, course

where t.cno = course.cno

GROUP BY t.cno

ORDER BY 及格率 desc

118、查询如下课程平均成绩和及格率的百分数(用"1行"显示):

企业管理（001），马克思（002），UML （003），数据库（004）

select

avg(case when cno = 1 then score end) as 平均分1,

avg(case when cno = 2 then score end) as 平均分2,

avg(case when cno = 3 then score end) as 平均分3,

avg(case when cno = 4 then score end) as 平均分4,

100 * sum(case when cno = 1 and score > 60 then 1 else 0 end) / sum(casewhen cno = 1 then 1 else 0 end) as 及格率1,

100 * sum(case when cno = 2 and score > 60 then 1 else 0 end) / sum(casewhen cno = 2 then 1 else 0 end) as 及格率2,

100 * sum(case when cno = 3 and score > 60 then 1 else 0 end) / sum(casewhen cno = 3 then 1 else 0 end) as 及格率3,

100 * sum(case when cno = 4 and score > 60 then 1 else 0 end) / sum(casewhen cno = 4 then 1 else 0 end) as 及格率4

from sc

119、查询不同老师所教不同课程平均分, 从高到低显示

select max(c.tname) as 教师, max(b.cname) 课程, avg(a.score) 平均分

from sc a, course b, teacher c

where a.cno = b.cno and b.tno = c.tno

group by a.cno

order by 平均分 desc

或者：

select r.tname as '教师',r.rname as '课程' , AVG(score) as '平均分'

from sc,

(select t.tname,c.cno as rcso,c.cname as rname

from teacher t ,course c

where t.tno=c.tno)r

where sc.cno=r.rcso

group by sc.cno,r.tname,r.rname

order by AVG(score) desc

120、查询如下课程成绩均在第3名到第6名之间的学生的成绩：

-- [学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩

select top 6 max(a.sno) 学号, max(b.sname) 姓名,

max(case when cno = 1 then score end) as 企业管理,

max(case when cno = 2 then score end) as 马克思,

max(case when cno = 3 then score end) as UML,

max(case when cno = 4 then score end) as 数据库,

avg(score) as 平均分

from sc a, student b

where a.sno not in

(select top 2 sno from sc where cno = 1 order by score desc)

and a.sno not in (select top 2 sno from sc where cno = 2 order by scoredesc)

and a.sno not in (select top 2 sno from sc where cno = 3 order by scoredesc)

and a.sno not in (select top 2 sno from sc where cno = 4 order by scoredesc)

and a.sno = b.sno

group by a.sno

欢迎工作一到五年的Java工程师朋友们加入Java架构师：697558955

群内提供免费的Java架构学习资料（里面有高可用、高并发、高性能及分布式、Jvm性能调优、Spring源码，MyBatis，Netty,Redis,Kafka,Mysql,Zookeeper,Tomcat,Docker,Dubbo,Nginx等多个知识点的架构资料）合理利用自己每一分每一秒的时间来学习提升自己，不要再用"没有时间“来掩饰自己思想上的懒惰！趁年轻，使劲拼，给未来的自己一个交代！

内容总结

以上是互联网集市为您收集整理的Java面试题-数据库篇十二全部内容，希望文章能够帮你解决Java面试题-数据库篇十二所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---