讲稿技术-sql语句

createtablesnovarchar2(10)primarykey,snamevarchar2(20),ssexcreatetabletnovarchar2(10)primarykey,tnamevarchar2(20)createtablecourse(varchar2(10),cnamevarchar2(20),tnoconstraintpk_courseprimarykeycreatetablesc(snovarchar2(10),cnoconstraintpk_scprimarykey insertintostudentvalues('s001','',23,'男');insertintostudentvalues('s002','',23,'男');insertintostudentvalues('s003','',25,'男');insertintostudentvalues('s004','',20,'女');insertintostudentvalues('s005','',20,'女');insertintostudentvalues('s006','',21,'男');insertintostudentvalues('s007','',21,'男');insertintostudentvalues('s008','',21,'女');insertintostudentvalues('s009','',23,'女');insertintostudentvalues('s010','',22,'女');insertintoteachervalues('t001','');insertintoteachervalues('t002','');insertintoteachervalues('t003','胡');insertintocoursevalues('c001','J2SE','t002');insertintocoursevalues('c002','JavaWeb','t002');insertintocoursevalues('c003','SSH','t001');insertintocoursevalues('c004','Oracle','t001');insertintocoursevalues('c005','SQLSERVER2005','t003');insertintocoursevalues('c006','C#','t003');insertintocoursevalues('c007','JavaScript','t002');insertintocoursevalues('c008','DIV+CSS','t001');insertintocoursevalues('c009','PHP','t003');insertintocoursevalues('c010','EJB3.0','t002');insertintoscvalues('s001','c001',78.9);insertintoscvalues('s002','c001',80.9);insertintoscvalues('s003','c001',81.9);insertintoscvalues('s004','c001',60.9);insertintoscvalues('s001','c002',82.9);insertintoscvalues('s002','c002',72.9);insertintoscvalues('s003','c002',81.9);insertintoscvalues('s001','c003','59');注意：以下练习中的数据是根据初始化到中的数据来写SQL意15、删除学习“”老师课的SC表记录16、向SC“c002”课程的同学学号、程],[<27、1981年出生的学生(注：Student表中Sage列的类型是34、查询课程编号为c001且课程成绩在80分以上的学生的学号和selecta.*(select*fromscawhereo='c001')a,(select*fromscbwhereo='c002')bwherea.sno=b.snoanda.score>b.score;select*fromsca andexists(select*fromscbwhere o='c002'anda.score>b.scoreanda.sno=b.sno)selectsno,avg(score)fromscgroupbysnohavingselecta.*,s.snamefrom(selectsno,sum(score),count(cno)fromscgroupbysno)a,studentswherea.sno=s.snoselectcount(*fromteacherwheretnamelikeselecta.sno,a.snamefromstudentawherea.snonot(selectdistincts.snofromscs,(selectfromcoursec,(selecttnofromteachertwheretname='')twherec.tno=t.tno)b o oselect*fromstudentstwherest.snonot(selectdistinctsnofromscsjoincoursecon jointeachertonc.tno=t.tnowheretname='')selectst.*fromscajoinscbona.sno=b.snojoinstudentston o='c002'andselectst.*fromstudentstjoinscsonst.sno=s.snojoincoursecon jointeachertonc.tno=t.tnowheret.tname=''select*fromstudentstjoinscaonst.sno=a.snojoinscbon o='c002' o='c001'anda.score<selectst.*,s.scorefromstudentstjoinscsonst.sno=s.snojoincoursecon wheres.score<60selectstu.sno,stu.sname,count( o)fromstudentstuleftjoinsconstu.sno=sc.snogroupbyhaving select*fromstudentwheresnoin(selectsnofrom ofromstudentstucrossjoincoursec ofrom)selectst.*fromstudentst,(selectdistincta.snofrom(select*fromsc)a,(select*fromscwheresc.sno='s001')b o)hwherest.sno=h.snoandselect*fromscleftjoinstudentstonst.sno=sc.sno oin(selectcnofromscwhereupdatesccsetscore=(selectavg(c.score)fromcoursea,teacherwherea.tno=b.tnoandb.tname='' groupbywherecnoselectcnofromcoursea,teacherbwherea.tno=b.tnoandb.tname='select*fromscwheresno<>'s001'(select*fromscselect*fromscwhere)deletefromsc selectcnofromcourseleftjointeachertonc.tno=t.tnowheret.tname='')insertintosc( selectdistinct o,(selectavg(score)fromscwherecno='c002')fromstudentst,scwherenot(select*fromscwherecno='c002'andsc.sno=st.sno) selectcno,max(score),min(score)fromscgroupbyselectcno,avg(score),sum(casewhenscore>=60then1else0end)/count(*)as及格率fromscgroupbyorderbyavg(score格率selectmax(t.tno),max(t.tname),max( o,avg(score)fromsc,coursec,teachert groupby orderbyavg(score) sum(casewhenscorebetween85and100then1else0end)AS"[100-sum(casewhenscorebetween70and85then1else0end)AS"[85-sum(casewhenscorebetween60and70then1else0end)AS"[70-sum(casewhenscore<60then1else0end)AS"[<60]"fromsc,coursec group select*from o,score,row_number()over(partitionbycnoorderbyscoredesc)rnfromsc)wherern<4selectcno,count(sno)fromscgroupbyselectsc.sno,st.sname,count(cno)fromstudentstleftjoinscongroupbyst.sname,sc.snohavingselectssex,count(*)fromstudentgroupbyselect*fromstudentwheresnamelikeselectsname,count(*)fromstudentgroupbysnamehavingselectcno,avg(score)fromscgroupbycnoorderby oselectst.sno,st.sname,avg(score)fromstudentstleftjoinscongroupbyst.sno,st.snamehavingselectsname,scorefromstudentst,sc,course o ame='Oracle'andselectst. amefromstudentst,sc,coursecwheresc.sno=st.snoand ame,sc.scorefromstudentst,sc,coursecwheresc.sno=st.snoand oandsc.score>70 oandsc.score<60order oselectst.sno,st.sname,sc.scorefromsc,studentstwheresc.sno=st.snoandcno='c001'andscore>80;selectcount(distinctsno)fromsc;selectst.sname,scorefromstudentst,sc,coursec,teachertst.sno=sc.snoand oandc.tno=t.tnoandt.tname=''andsc.score=(selectmax(score)fromsc selectcno,count(sno)fromscgroupbyselecta.*fromsca,scbwherea.score=b.score select*from( o,score,row_number()over(partitionbycnoorderbyscoredesc)my_rnfromsc)whereselectcno,count(sno)fromscgroupbycnohavingcount(sno)>10orderby oselectsnofromscgroupbysnohavingselectsnofromscgroupbysnohavingselectdistinct( amefromcoursec,sc o amefromcourse o(selectcnofromscgroupbyselectst.snamefromstudentstwherest.snonotin(selectdistinctsc.snofromsc,coursec,teacher oandc.tno=t.tnoandt.tname='selectsno,avg(score)fromscwheresnoin(selectsnofromscwheresc.score<60groupbysnohavingcount(sno)>1)groupbyselectsnofromscwherecno='c004'andscore<90orderbyscoredeletefromscwheresno='s002'and我的答案-- fromscwheresc.sno='s002'and o='c001'select*fromsc;deletefromscwheresc.sno='s001'andcno='c006'rollbackselect*from--45检索“c003”课程分数小于60，按分数降序排列的同学学

sc.wheresc.snoin(o='c003')andsc.score<60orderbysc.sno;44、查询两门以上不及格课程的同学的学号及其平均成绩

sc.sno,avg(sc.score)sc.score<60groupbysc.snohavingcount(sc.--43、查询没学过“”老师讲授的任一门课程的学生

student.snonot

o

o

)

teacher.tname=')42、查询全部学生都选修的课程的课程号和课程名

(select

o,o )select(select0from

o,course.o=sc.)41、检索至少选修两门课程的学生学号sc.1=1groupbysc.snohaving 40、统计每门课程的学生选修人数（10人的课程才统。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列o,count(sc.sno)ohavingcount(sc.sno)>1orderbycodesc,oasc;39、查询每门功课成绩最好的前两--比它scwhereselectcount(*)fromscsc_2wheresc_1.score<sc_2.scoreandsc_2. o)<2(selectcount(*)fromscsc_3wheresc_1.score<sc_3.scoreandsc_1. o)>=0*(

orderbysc_1.cnodesc,sc_1.score

from

o,row_number()over(partitionbycnoorderbysc.score)where38、查询不同课程成绩相同的学生的学号、课程号、学生成绩[成绩相同分组]--错误:selectsc.scorefromscwhere1=1groupbysc.scorehavingcount( orderbysc.score只能求出成绩，不能定位组织groupselecta.*fromsca,scbwherea.score=o!=b.37、查询各个课程及相应的选修人数

groupby--36、查询选修“”老师所授[课程]的学生中，成绩最高的学生及其成绩[不区分--构建一张表createviewt

sc.snosno_,ocno_,oino

course.tno=(selefromteacherwhereteacher.tname='))-select*fromt;dropviewt;

t.sno_, andt.score_=(selectmax(t.score_)fromt)----区分课程st.sname,fromstudentst,sc,coursec,teacherst.sno=sc.snoandsc. oandc.tno=t.tnoandt.tname=''andsc.score=(selectmax(score)fromsc o=c.

student,teacher,scsc_1,coursestudent.sno=sc_1.snoo=sc_1.cnoteacher.tname='andsc_1.scoreselectmax(sc_2.score)fromscsc_2wheresc_2. )35、求选了课程的学生人selectcount(distinctsno)from--34查询课程编号为c001且课程成绩在80分以上的学生的学号和

student.sno,o=course.o='sc.s33、查询不及格的课程，并按课程号从大到小

o,sc.score, o=course.sc.orderbysc.score--32查询任何一门课程成绩在70分以上的、课程名称和分数

student.sname,sc.score, o=osc.score>7031、查询所有学生的选课情况；

student.sno,studen o=o30、查询课程名称为“数据库”，且分数低于60的学生姓名和分

o=course.ame='数据库'sc.---29、查询平均成绩大于85的所有学生的学号、和平均成--成绩有可能会为null[平均成绩包含null]

avg(sc.score),student.snostudentleftjoinscgroupbystudent.snohavingavg(sc.score)>858、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

oorderbyavg(sc.score) o--27、1981年出生的学生(注：Student表中Sage列的类型是to_char按照格式转换成数student.sno,student.snamefromstudentwhereto_char(sysdate,'yyyy')---26、查询同名同性学生，并统计同名人

studentstudentwherestudent_1.sname=student_2.sname)selectstudent.sname,count(*)from studentgroupbystudent.snamehavingcount(*)>125、查询姓“张”的学生select*fromstudentwherestudent.snamelike'张%'--24、查询男生、人selectcount(*)fromstudentgroupby23、查询出只选修了一门课程的全部学生的学号和selectsc.sno,student.snamefromsc,studentwhere1=1andstudent.sno=sc.snogroupbysc.sno,student.snamehavingcount( --一样结selectsc.sno,st.sname,count(cno)fromstudentstleftjoinscongroupbyst.sname,sc.snohavingcount(22、查询每门课程被选修的学生数o o)fromscgroupby--21、查询各科成绩前三名的记录:(不考虑成绩并列情况--排序构造一张--重点 from( ocno_,sc.scorescore_,row_number()over(oorderbysc.scoredesc)from)where20、统计列印各科成绩,各分数段人数:课程ID,课程名称,[10085],[8570],[70-60],[<--循环统

sum(casewhensc.scorebetween85and100then1else0end)as"[100-sum(casewhensc.scorebetween70and85then1else0end)as"[85-sum(casewhensc.scorebetween60and70then1else0end)as"[70-60]",sum(casewhensc.score<60then1else0end) o=ogroupby 19、查询不同老师所教不同课程平均分从高到低显示max()占max( o),max( ame),max(course.tno),max(teacher.tname),avg(sc.score)sc,coursewhereteacher.tno=course.tnoand o=oorderbyavg(sc.score)desc--18、按各科平均成绩从低到高和及格率的百分数从高到低顺序 o,avg(sc.score),sum(casewhensc.score>=60then1else0end)/count(*)as及格率fromgroupbysc.orderbysccno,及格率17、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低selectmin(sc.score),max(sc.score),max( o)fromscgroupbysc.--15、删除学习“”老师课的SC表记录； o course.tno=(selectteacher.tnofromteacherwher