Java常用面试题之数据库篇（十二）

基本表结构：

    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），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常用面试题之Java多线程（十三）