当前位置:首页 >> 电脑基础知识 >>

六道经典SQL语句题完全掌握SQL语句


学生表 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] view plaincopyprint? 1. --第 1 题 2. select td_b.SNO 学号, td_b.SNAME 姓名, td_b.SHIGH 身高, td_a.avgGrade 平均成绩 from 3. (select S.SNO,avg(SCgrade) as avgGrade from S,SC 4. where 5. S.SNO = SC.SNO and 6. S.Shigh>170 7. group by S.SNO 8. having 9. avg(SCgrade)>60) td_a 10.left join( 11. 12. 13. 14. select * from S where not exists ( select a.sno from S a, SC b where a.sno = b.sno and b.scgrade<60

15. 16.

group by a.sno having count(*) < 3)

17.) td_b 18.on td_a.SNO = td_b.SNO 19.--第 2 题 20.--第 3 题 21.select distinct S1.SNO as 学号, S1.SNAME as 姓名, 22.(select SC.SCgrade from SC,C where SC.CNO=C.CNO and C.CNAME='离散数学 ' and SC.SNO = SC1.SNO) as 离散数学, 23.(select SC.SCgrade from SC,C where SC.CNO=C.CNO and C.CNAME='组成原理 ' and SC.SNO = SC1.SNO) as 组成原理, 24.(select SC.SCgrade from SC,C where SC.CNO=C.CNO and C.CNAME='高等数学 ' and SC.SNO = SC1.SNO) as 高等数学 25.from SC SC1, S S1 26.where 27.SC1.SNO = S1.SNO 28.and S1.SNO in 29.( 30.select S.SNO from S,C,SC 31.where 32.S.SNO = SC.SNO and 33.SC.CNO = C.CNO and 34.C.CNAME='离散数学' and 35.S.SNO in (select t1.SNO from SC t1, C t2 where t1.CNO=t2.CNO and t2.cname='组成原理') and 36.S.SNO in (select t3.SNO from SC t3, C t4 where t3.CNO=t4.CNO and t4.Cname='高等数学 'and t3.scgrade>80) 37.) 38.----另解 39.select distinct s.sno,s.sname, 40.(select scgrade from sc left join c on sc.cno=c.cno where c.cname='离散数学' and sc.sno=sc1.sno)" 离散数学", 41.(select scgrade from sc left join c on sc.cno=c.cno where c.cname='组成原理' and sc.sno=sc1.sno) " 组成原理", 42.(select scgrade from sc left join c on sc.cno=c.cno where c.cname='高等数学' and sc.sno=sc1.sno) " 高等数学"

43.from s,sc sc1,C 44.where sc1.sno=s.sno 45.and s.sno in 46.( 47.select sno from sc left join c on sc.cno=c.cno where c.cname='离散数学' intersect 48.select sno from sc left join c on sc.cno=c.cno where c.cname='组成原理' intersect 49.select sno from sc left join c on sc.cno=c.cno where c.cname='高等数学' and sc.scgrade>80) 50. 51.--第 4 题 52.select C.CNAME as 课程名, 53. max(case when ro=1 then '班级:'||td_b.class||' 姓名:'||S.SNAME||' 成 绩:'||td_b.SCgrade else null end) as 第一名, 54. max(case when ro=2 then '班级:'||td_b.class||' 姓名:'||S.SNAME||' 成 绩:'||td_b.SCgrade else null end) as 第二名, 55. max(case when ro=3 then '班级:'||td_b.class||' 姓名:'||S.SNAME||' 成 绩:'||td_b.SCgrade else null end) as 第三名 56.from 57.( 58.select td_a.CNO, td_a.SNO, td_a.class, td_a.SCgrade, row_number() over(partition by CNO order b y SCgrade desc) ro 59.from 60. (select CNO, S.SNO, Class, SCgrade 61. from S inner join SC 62. on S.SNO = SC.SNO 63. group by CNO, S.SNO, Class, SCgrade 64. order by S.SNO)td_a 65.)td_b , C, S 66.where td_b.cno = C.CNO and td_b.sno = S.SNO 67.group by CNAME 68.--第 5 题 69.select c.cname "课程号", 70.sum(case when (scgrade<60) then 1 else 0 end) "60 分以下", 71.sum(case when (scgrade<70 and scgrade>=60) then 1 else 0 end) "[60,70)", 72.sum(case when (scgrade<85 and scgrade>=70) then 1 else 0 end) "[70,85)", 73.sum(case when (scgrade<=100 and scgrade>=85) then 1 else 0 end) "[85,100]"

74.from sc,c 75.where sc.cno=c.cno 76.group by c.cname 77.order by c.cname 78.--第 6 题 79.select CNAME 课程名, 80. 81. 82. 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) 第三

83.from 84.( 85.select td_a.CNO, td_a.Class, td_a.K_avg, row_number() over(partition by CNO order by K_avg desc )ro 86.from 87. (select distinct CNO, Class, avg(SCgrade)K_avg 88. from S inner join SC 89. on S.SNO = SC.SNO 90. group by CNO, class 91. order by class)td_a 92.)td_b, C 93.where td_b.CNO = C.CNO 94.group by CNAME 95.order by CNAME 96.--第 7 题 97.----没有 max 选出来有三行数据(td_e 有三行数据),有了 max 就只有一行数据了正为所有数据 98.select max(case when rownum=1 then td_d.class else null end) 第一名, 99. 100. 101. 102. 103. 104. 105. 106. 107. 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,

108. 109. 110. 111. 112. 113.

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 114. 115. 116. 117. 118. 119. 120. 121. 122. 123. 124. 125. 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测试题.doc
六道 成的 65 254 人民教育出 1 版社 类别表 标号 1 2 名称 校园小说 ...sql试题与答案 5页 免费 11-SQL语句测试题3 6页 免费 sql 试卷B 4页 免费...