学生表S (SNO学号,Sname姓名,Class班级,Shigh身高)
课程表C(CNO课程号,CName课程名,CPreNO预修课程编码)
成绩表SC(CNO课程号,SNO学号,SCgrade成绩)
1、体育生选拔,要求身高在170cm以上,考试不及格课程在3门以下(不包括3门)平均成绩在60分以上。
学号姓名身高平均成绩
2、所有预选课程都已经合格的学生(预选课程不存在嵌套循环的情况)。
3、同时选修了‘离散数学’与‘组成原理’的,且高等数学成绩大于80分的。
学号姓名离散数学组成原理高等数学
分数分数分数
4、列出各门课程前三名(成绩相同,学号小的排名在前)
第一名第二名第三名
课程名班级:姓名:成绩班级:姓名:成绩班级:姓名:成绩
5、统计各科的学习情况。
课程名60以下[60,70)[70,85)[85,100)
6、按班级统计各科平均成绩。
课程名第一第二第三
班级:平均成绩班级:平均成绩班级:平均成绩
7、按班级统计各科平均成绩后,第一名3分,第二名2分,第三名1分,其他计0.5分(可以并列名次),最后给班级排名。(最后成绩一样,按班级序号小的排前)
第一名第二名第三名
Sql代码 收藏代码
--第1题
select td_b.SNO 学号, td_b.SNAME 姓名, td_b.SHIGH 身高, td_a.avgGrade 平均成绩 from
(select S.SNO,avg(SCgrade) as avgGrade from S,SC
where
S.SNO = SC.SNO and
S.Shigh>170
group by S.SNO
having
avg(SCgrade)>60) td_a
left join(
select * from S where not exists (
select a.sno from S a, SC b where
a.sno = b.sno
and b.scgrade<60
group by a.sno
having count(*) < 3)
) td_b
on td_a.SNO = td_b.SNO
--第2题
--第3题
select distinct S1.SNO as 学号, S1.SNAME as 姓名,
(select SC.SCgrade from SC,C where SC.CNO=C.CNO and C.CNAME='离散数学' and SC.SNO = SC1.SNO) as 离散数学,
(select SC.SCgrade from SC,C where SC.CNO=C.CNO and C.CNAME='组成原理' and SC.SNO = SC1.SNO) as 组成原理,
(select SC.SCgrade from SC,C where SC.CNO=C.CNO and C.CNAME='高等数学' and SC.SNO = SC1.SNO) as 高等数学
from SC SC1, S S1
where
SC1.SNO = S1.SNO
and S1.SNO in
(
select S.SNO from S,C,SC
where
S.SNO = SC.SNO and
SC.CNO = C.CNO and
C.CNAME='离散数学' and
S.SNO in (select t1.SNO from SC t1, C t2 where t1.CNO=t2.CNO and t2.cname='组成原理') and
S.SNO in (select t3.SNO from SC t3, C t4 where t3.CNO=t4.CNO and t4.Cname='高等数学'and t3.scgrade>80)
)
----另解
select distinct s.sno,s.sname,
(select scgrade from sc left join c on sc.cno=c.cno where c.cname='离散数学' and sc.sno=sc1.sno)"离散数学",
(select scgrade from sc left join c on sc.cno=c.cno where c.cname='组成原理' and sc.sno=sc1.sno) "组成原理",
(select scgrade from sc left join c on sc.cno=c.cno where c.cname='高等数学' and sc.sno=sc1.sno) "高等数学"
from s,sc sc1,C
where sc1.sno=s.sno
and s.sno in
(
select sno from sc left join c on sc.cno=c.cno where c.cname='离散数学' intersect
select sno from sc left join c on sc.cno=c.cno where c.cname='组成原理' intersect
select sno from sc left join c on sc.cno=c.cno where c.cname='高等数学' and sc.scgrade>80)
--第4题
select C.CNAME as 课程名,
max(case when ro=1 then '班级:'||td_b.class||' 姓名:'||S.SNAME||' 成绩:'||td_b.SCgrade else null end) as 第一名,
max(case when ro=2 then '班级:'||td_b.class||' 姓名:'||S.SNAME||' 成绩:'||td_b.SCgrade else null end) as 第二名,
max(case when ro=3 then '班级:'||td_b.class||' 姓名:'||S.SNAME||' 成绩:'||td_b.SCgrade else null end) as 第三名
from
(
select td_a.CNO, td_a.SNO, td_a.class, td_a.SCgrade, row_number() over(partition by CNO order by SCgrade desc) ro
from
(select CNO, S.SNO, Class, SCgrade
from S inner join SC
on S.SNO = SC.SNO
group by CNO, S.SNO, Class, SCgrade
order by S.SNO)td_a
)td_b , C, S
where td_b.cno = C.CNO and td_b.sno = S.SNO
group by CNAME
--第5题
select c.cname "课程号",
sum(case when (scgrade<60) then 1 else 0 end) "60分以下",
sum(case when (scgrade<70 and scgrade>=60) then 1 else 0 end) "[60,70)",
sum(case when (scgrade<85 and scgrade>=70) then 1 else 0 end) "[70,85)",
sum(case when (scgrade<=100 and scgrade>=85) then 1 else 0 end) "[85,100]"
from sc,c
where sc.cno=c.cno
group by c.cname
order by c.cname
--第6题
select CNAME 课程名,
max(case when ro=1 then '班级:'||td_b.Class||' 平均成绩:'||td_b.K_avg else null end) 第一,
max(case when ro=2 then '班级:'||td_b.Class||' 平均成绩:'||td_b.K_avg else null end) 第二,
max(case when ro=3 then '班级:'||td_b.Class||' 平均成绩:'||td_b.K_avg else null end) 第三
from
(
select td_a.CNO, td_a.Class, td_a.K_avg, row_number() over(partition by CNO order by K_avg desc)ro
from
(select distinct CNO, Class, avg(SCgrade)K_avg
from S inner join SC
on S.SNO = SC.SNO
group by CNO, class
order by class)td_a
)td_b, C
where td_b.CNO = C.CNO
group by CNAME
order by CNAME
--第7题
----没有max选出来有三行数据(td_e有三行数据),有了max就只有一行数据了正为所有数据
select max(case when rownum=1 then td_d.class else null end) 第一名,
max(case when rownum=2 then td_d.class else null end) 第二名,
max(case when rownum=3 then td_d.class else null end) 第三名
from
(
select td_c.class, sum1+sum2+sum3+sum4 as tscore
from
(
select td_b.class,
sum(case when ro=1 then 3 else 0 end) as sum1,
sum(case when ro=2 then 2 else 0 end) as sum2,
sum(case when ro=3 then 1 else 0 end) as sum3,
sum(case when ro<>1 and ro<>2 and ro<>3 then 0.5 else 0 end) as sum4
from
(
select td_a.CNO, td_a.Class, td_a.K_avg, row_number() over(partition by CNO order by K_avg desc)ro
from
(select distinct CNO, Class, avg(SCgrade)K_avg
from S inner join SC
on S.SNO = SC.SNO
group by CNO, class
order by class)td_a
)td_b
group by td_b.class
order by td_b.class
)td_c
order by tscore desc
)td_d
分享到:
相关推荐
亚信笔试题!!!!!亚信笔试题!!!!!亚信笔试题!!!!!亚信笔试题!!!!!亚信笔试题!!!!!亚信笔试题!!!!!
这里面是我精心收集和整理出来的。所以资源分要的多一点。
常见的SQL笔试选择题含答案.docx,常见的SQL笔试选择题含答案.docx 常见的SQL笔试选择题含答案.docx,常见的SQL笔试选择题含答案.docx
sql语句 建表 增加、删除、复杂查询字段
经典sql练习,几道经典的SQL笔试题题目。sql就在于多多练习
常用的SQL面试题 SQL经典试题 SQL SQL常见笔试试题
自己整理的SQLserver笔试题。 当做知识点记了 内附答案
SQL_Server 面试笔试试题及答案 经典SQL数据库 DBA数据库管理员 数据库优化人员面试题
这个笔试题对于新手来说绝对是好东西,请大伙放心下载
sql经典笔试题三(含答案) sql经典笔试题三(含答案) sql经典笔试题三(含答案)
2022年SQL数据库经典面试题笔试题.pdf2022年SQL数据库经典面试题笔试题.pdf2022年SQL数据库经典面试题笔试题.pdf2022年SQL数据库经典面试题笔试题.pdf2022年SQL数据库经典面试题笔试题.pdf2022年SQL数据库经典面试题...
SQL笔试题库。
1到3年工作经验的 sql面试笔试题(通过面试360,华为,阿里收集)
2022年SQL数据库经典面试题笔试题 (2).pdf2022年SQL数据库经典面试题笔试题 (2).pdf2022年SQL数据库经典面试题笔试题 (2).pdf2022年SQL数据库经典面试题笔试题 (2).pdf2022年SQL数据库经典面试题笔试题 (2).pdf2022...
千橡笔试题 人人笔试题!千橡笔试题 人人笔试题!千橡笔试题 人人笔试题!千橡笔试题 人人笔试题!千橡笔试题 人人笔试题!
数据库知识SQL语句大全笔试能用的.pdf
跟笔试的时候碰到的sql题目差不多,比较经典
SQL数据库经典面试题(笔试题).doc.docxSQL数据库经典面试题(笔试题).doc.docxSQL数据库经典面试题(笔试题).doc.docxSQL数据库经典面试题(笔试题).doc.docxSQL数据库经典面试题(笔试题).doc.docxSQL数据库经典面试题...
是部分SQL数据库试题,好好做,在找工作笔试面试中用得到的。
sql经典笔试题(含答案) sql经典笔试题(含答案) sql经典笔试题(含答案)