sql练习作业Word格式文档下载.docx
- 文档编号:5589436
- 上传时间:2023-05-05
- 格式:DOCX
- 页数:15
- 大小:83.15KB
sql练习作业Word格式文档下载.docx
《sql练习作业Word格式文档下载.docx》由会员分享,可在线阅读,更多相关《sql练习作业Word格式文档下载.docx(15页珍藏版)》请在冰点文库上搜索。
end))enterprise,
英语'
end))english,
(selectavg(sct2.score)fromtblscoresct2wheresct2.stuid=s.stuid)score
fromtblstudents,tblscoresct
wheres.stuid=sct.stuid
groupbys.stuid
orderbyscoredesc;
--18、查询各科成绩最高和最低的分:
以如下形式显示:
课程id,最高分,最低分
selectc.courseid,max(sct.score),min(sct.score)
fromtblscoresct,tblcoursec
wherec.courseid=sct.courseid
groupbyc.courseid;
--19、按各科平均成绩从低到高和及格率的百分数从高到低顺序(百分数后如何格式化为两位小数?
?
selecttblscore.courseid,
avg(tblscore.score),
(convert(varchar(10),
((selectcount(*)
fromtblscoresc
Wheresc.courseid=cs.courseid
andsc.score>
=60)*10000/
(selectcount(*)
Wheresc.courseid=cs.courseid))/100)+'
%'
fromtblscorecsastmp
groupby课程id,
平均分,
及格率orderby平均分,
convert(float,substring(及格率,1,len(及格率)-1))desc;
--20、查询如下课程平均成绩和及格率的百分数(用"
1行"
显示):
企业管理(001),马克思(002),oo&
uml(003),数据库(004)
selectsct.courseid,
avg(score),
fromtblscoresct2
wheresct2.courseid=sct.courseid
andsct2.score>
=60)/
wheresct2.courseid=sct.courseid)pass
fromtblscoresct
wheresct.courseidin('
001'
'
004'
groupbysct.courseid
orderbypassdesc;
--21、查询不同老师所教不同课程平均分从高到低显示
selectt.teaname,c.coursename,avg(sct.score)avgscore
fromtblteachert,tblcoursec,tblscoresct
wheret.teaid=c.teaid
andc.courseid=sct.courseid
groupbyt.teaname,c.coursename
orderbyavgscoredesc;
--22、查询如下课程成绩第3名到第6名的学生成绩单:
企业管理(001),马克思(002),uml(003),数据库(004)格式:
[学生id],[学生姓名],企业管理,马克思,uml,数据库,平均成绩
selects.stuid,s.stuname,"
企业管理"
"
马克思"
v.uml,"
数据库"
v.avgscorefrom
(selectrownumid,s.stuid,s.stuname,v.mng"
v.mks"
v.uml,v.db"
tblstudents,
(selects.stuid,sum((casewhensct.courseid=001thensct.scoreelse0end))mng,
sum((casewhensct.courseid=002thensct.scoreelse0end))mks,
sum((casewhensct.courseid=003thensct.scoreelse0end))uml,
sum((casewhensct.courseid=004thensct.scoreelse0end))db,
(sum((casewhensct.courseid=001thensct.scoreelse0end))+
sum((casewhensct.courseid=002thensct.scoreelse0end))+
sum((casewhensct.courseid=003thensct.scoreelse0end))+
sum((casewhensct.courseid=004thensct.scoreoelse0end)))/4avgscore
from
scts,tblscoresctwheres.stuid=sct.stuidgroupbys.stuid)v
wheres.stuid=v.stuid
orderbyavgscoredesc)
whereidbetween3and6;
withs1as(selectt.stuid,t.courseid,t.score,t.rankfrom
(selects2.*,rank()over(partitionbycourseidorderbyscoredesc)rank
fromtblscores2wherecourseidin('
'
))t
wheret.rankbetWeen3and6),
s2as(selectstuid,sum(decode(courseid,'
score))cj1,sum(decode(courseid,'
score))cj2,
sum(decode(courseid,'
score))cj3,sum(decode(courseid,'
score))cj4fromtblscoregroupbystuid),
s3as(selectstuid,avg(nvl(score,0))avgsfromtblscoregroupbystuid)
selectdistincts1.stuid,s4.stuname,s2.cj1,s2.cj2,s2.cj3,s2.cj4,s3.avgsfroms1,s2,s3,tblstudents4wheres1.stuid=s2.stuidands1.stuid=s3.stuidands1.stuid=s4.stuid;
--23、统计列印各科成绩,各分数段人数:
课程id,课程名称,[100-85],[85-70],[70-60],[<
60]
selectc.courseid课程id,
c.coursename课程名称,
v.a"
[<
60]"
v.b"
[60-70]"
v.c"
[70-85]"
v.d"
[85-100]"
fromtblcoursec,
(selectsct.courseid,
sum(case
whensct.score<
60then
1
end)a,
whensct.score>
=60andsct.score<
70then
end)b,
=70andsct.score<
85then
end)c,
=85andsct.score<
100then
end)d
groupbysct.courseid)v
wherec.courseid=v.courseid;
--24、查询学生平均成绩及其名次
s.stuname,
v.avgscore,
from(selectsct.stuid,avg(sct.score)avgscore
groupbysct.stuid)v2
wherev.avgscore<
v2.avgscore)+1seqno
fromtblstudents,
(selectsct.stuid,avg(sct.score)avgscore
groupbysct.stuid)v
wheres.stuid=v.stuid
orderbyseqno;
--25、查询各科成绩前三名的记录:
(不考虑成绩并列情况)
SELECTt1.stuidasstudentID,t1.courseidasclassID,Scoreasscore
FROMSCt1
WHEREscoreIN
(SELECTTOP3scoreFROMSCWHEREt1.Courseid=CourseidORDERBYscoreDESC)
ORDERBYt1.Ccourseid;
--26、查询每门课程被选修的学生数
selectcourseid,count(stuid)fromtblscoregroupbycourseid;
--27、查询出只选修了一门课程的全部学生的学号和姓名
selecttblscore.stuid,tblstudent.stuname,count(courseid)
fromtblscore,tblstudent
wheretblscore.stuid=tblstudent.stuid
groupbytblscore.stuid,tblstudent.stuname
havingcount(courseid)=1;
--28、查询男生、女生人数
selectstusex,count(*)fromtblstudentgroupbystusex;
--29、查询姓“张”的学生名单
selectstunamefromtblstudentwherestunamelike'
张%'
;
--30、查询同名同性学生名单,并统计同名人数
selectstuname,count(*)fromtblstudentgroupbystunamehavingcount(*)>
--31、1981年出生的学生名单(注:
student表中sage列的类型是datetime)
selectstuname,stuagefromtblstudentwhereto_char(sysdate,'
yyyy'
)-stuage='
1981'
--32、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列
selectcourseid,avg(score)fromtblscore
groupbycourseid
orderbyavg(score),courseiddesc;
--33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
selectstuname,tblscore.stuid,avg(score)
fromtblstudent,tblscore
wheretblstudent.stuid=tblscore.stuid
groupbytblscore.stuid,stuname
havingavg(score)>
85;
--34、查询课程名称为“数据库”,且分数低于60的学生姓名和分数
selectstuname,score
fromtblstudent,tblscore,tblcourse
andtblscore.courseid=tblcourse.courseid
andtblcourse.coursename='
andscore<
60;
--35、查询所有学生的选课情况;
selecttblscore.stuid,tblscore.courseid,stuname,coursename
fromtblscore,tblstudent,tblcourse
wheretblscore.stuid(+)=tblstudent.stuid
andtblscore.courseid=tblcourse.courseid;
--36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;
selectdistincttblstudent.stuid,
tblstudent.stuname,
tblscore.courseid,
c.coursename,
tblscore.score
fromtblstudent,tblscore,tblcoursec
wheretblscore.score>
=70
andtblscore.stuid=tblstudent.stuid
andc.courseid=tblscore.courseid;
--37、查询不及格的课程,并按课程号从大到小排列
select*fromtblscoreWherescore<
60orderbycourseiddesc
--38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名;
selecttblscore.stuid,tblstudent.stuname
andscore>
80
andcourseid='
--39、求选了课程的学生人数
selectcount(distinctstuid)fromtblscore;
--40、查询选修“叶平”老师所授课程的学生中,成绩最高的学生姓名及其成绩
selecttblstudent.stuname,score
fromtblstudent,tblscore,tblcourse,tblteacher
andtblcourse.teaid=tblteacher.teaid
andtblteacher.teaname='
叶平'
andtblscore.score=
(selectmax(score)fromtblscorewherecourseid=tblcourse.courseid);
--41、查询各个课程及相应的选修人数(有些课程可能没有人选)
selectcount(distinctstuid)fromtblscoregroupbycourseid;
--42、查询不同课程成绩相同的学生的学号、课程号、学生成绩
Select学号=StuId,课程号=CourseId,成绩=Score
FromtblScoresc
WhereExists(Select*
FromtblScore
WhereScore=sc.Score
AndStuId=sc.StuId
AndCourseId<
>
sc.CourseId)
Orderby学号,成绩
--43、查询每门功成绩最好的前两名
selecttblcourseid,
第1名=(selecttop1stuidfromtblscorewherecourseid=cs.courseidorderbyscoredesc),
第2名=(selecttop1stuidfrom(selecttop2stuid,scorefromtblscorewherecourseid=cs.courseidorderbyscoredesc)astmporderbyscore)
fromtblcoursecs;
--44、统计每门课程的学生选修人数(超过10人的课程才统计)。
要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
selectcourseid,count(*)cfromtblscoregroupbycourseidhavingcount(*)>
10orderbycdesc,courseid;
--45、检索至少选修两门课程的学生学号
selectdistinctstuidfromtblscoregroupbystuidhavingcount(courseid)>
1;
--46、查询全部学生都选修的课程的课程号和课程名
selectcourseid,coursename
fromtblcourse
wherecourseidin
(selectcourseid
fromtblscore
havingcount(distinctstuid)=(selectcount(stuid)fromtblstudent));
--47、查询没学过“叶平”老师讲授的任一门课程的学生姓名
selectdistinctstuname
fromtblstudentst,tblscore
wheretblscore.stuid=st.stuid
andcourseidnotin(selectdistinctcourseid
fromtblteachert,tblcoursec
andteaname='
--48、查询两门以上不及格课程的同学的学号及其平均成绩
selectstuid,avg(score)
where(selectcount(*)
fromtblscores1
wheres1.stuid=sc.stuid
60)>
=2groupbystuid;
--49、检索“004”课程分数小于60,按分数降序排列的同学学号(ok)
selectstuid,score
wherecourseid='
60
--50、删除“002”同学的“001”课程的成绩
deletefromtblscorewherestuid='
1002'
andcourseid='
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- sql 练习 作业