爱分享666

当前位置:首页 >面试题及技巧>java面试题

来源微信号:Java知音专注于Java领域,每天推送全网高质量技术文章,热门开源项目,实用视频教程等。致力打造一个有实用,有情怀的Java技术文章推送平台!

基本表结构:

        student(sno,sname,sage,ssex)学生表
        course(cno,cname,tno) 课程表
        sc(sno,cno,score) 成绩表

        teacher(tno,tname) 教师表


111、把“sc”表中“王五”所教课的成绩都更改为此课程的平均成绩
sc set score = ( 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,

sno
from sc
where sno <> 2
group by sno
having sum(cno) = ( sum(cno) from sc where sno = 2)

2,
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) = ( sum(cno) from sc where sno = 2)


113、删除学习“王五”老师课的sc表记录
sc from course, teacher
where course.cno = sc.cno and course.tno = teacher.tno and tname = '王五'


114、向sc表中插入一些记录,这些记录要求符合以下条件:
将没有课程3成绩同学的该成绩补齐, 其成绩取所有学生的课程2的平均成绩

sc sno, 3, ( avg(score) from sc where cno = 2)
from student
where sno not in ( sno from sc where cno = 3)


115、按平平均分从高到低显示所有学生的如下统计报表:
-- 学号,企业管理,马克思,UML,数据库,物理,课程数,平均分

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、查询各科成绩最高分和最低分:

以如下形式显示:课程号,最高分,最低分
cno as 课程号, max(score) as 最高分, min(score) 最低分
from sc group by cno

  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) 
 
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、查询不同老师所教不同课程平均分, 从高到低显示
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

或者:
r.tname as '教师',r.rname as '课程' , AVG(score) as '平均分'
from sc,
(  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,数据库,平均成绩

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 

( top 2 sno from sc where cno = 1 order by score desc)
  and a.sno not in ( top 2 sno from sc where cno = 2 order by scoredesc)
  and a.sno not in ( top 2 sno from sc where cno = 3 order by scoredesc)
  and a.sno not in ( top 2 sno from sc where cno = 4 order by scoredesc)
  and a.sno = b.sno
group by a.sno

上一篇:

java面试题及答案---2018核心Java面试问题与解答(二)

下一篇:

Java中级面试题及答案解析_Java知识分享(13)

2 +1
打赏 ×

如果网站能给予您帮助,欢迎给网站捐助,给我打赏个吧!
您的支持是我的动力,让网站能一直陪伴着大家,共同学习进步。
捐助费用将用于网站日常运营(服务器租费、域名租费等)
捐助者请发送邮箱提供姓名至 zhaoqn@163.com 留言以表感谢。

网友评论


  • 验证码:

热门评论

本月热门

推荐资料

精彩评论

回到顶部