Java常用面试题之数据库篇(十二)

这篇博客聚焦于Java面试中常见的数据库相关问题,涵盖了Spring、MySQL的使用和理解,以及与JavaScript和HTML5的交互知识点。
摘要由CSDN通过智能技术生成

基本表结构:

    student(sno,sname,sage,ssex)学生表
    course(cno,cname,tno) 课程表
    sc(sno,cno,score) 成绩表
    teacher(tno,tname) 教师表
        
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),UML003),数据库(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常用面试题之Java多线程(十三)

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值