講稿技術(shù)-sql語(yǔ)句

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');注意：以下練習(xí)中的數(shù)據(jù)是根據(jù)初始化到中的數(shù)據(jù)來寫SQL意15、刪除學(xué)習(xí)“”老師課的SC表記錄16、向SC“c002”課程的同學(xué)學(xué)號(hào)、程],[<27、1981年出生的學(xué)生(注：Student表中Sage列的類型是34、查詢課程編號(hào)為c001且課程成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和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”課程分?jǐn)?shù)小于60，按分?jǐn)?shù)降序排列的同學(xué)學(xué)

sc.wheresc.snoin(o='c003')andsc.score<60orderbysc.sno;44、查詢兩門以上不及格課程的同學(xué)的學(xué)號(hào)及其平均成績(jī)

sc.sno,avg(sc.score)sc.score<60groupbysc.snohavingcount(sc.--43、查詢沒學(xué)過“”老師講授的任一門課程的學(xué)生

student.snonot

o

o

)

teacher.tname=')42、查詢?nèi)繉W(xué)生都選修的課程的課程號(hào)和課程名

(select

o,o )select(select0from

o,course.o=sc.)41、檢索至少選修兩門課程的學(xué)生學(xué)號(hào)sc.1=1groupbysc.snohaving 40、統(tǒng)計(jì)每門課程的學(xué)生選修人數(shù)（10人的課程才統(tǒng)。要求輸出課程號(hào)和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號(hào)升序排列o,count(sc.sno)ohavingcount(sc.sno)>1orderbycodesc,oasc;39、查詢每門功課成績(jī)最好的前兩--比它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、查詢不同課程成績(jī)相同的學(xué)生的學(xué)號(hào)、課程號(hào)、學(xué)生成績(jī)[成績(jī)相同分組]--錯(cuò)誤:selectsc.scorefromscwhere1=1groupbysc.scorehavingcount( orderbysc.score只能求出成績(jī)，不能定位組織groupselecta.*fromsca,scbwherea.score=o!=b.37、查詢各個(gè)課程及相應(yīng)的選修人數(shù)

groupby--36、查詢選修“”老師所授[課程]的學(xué)生中，成績(jī)最高的學(xué)生及其成績(jī)[不區(qū)分--構(gòu)建一張表createviewt

sc.snosno_,ocno_,oino

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

t.sno_, andt.score_=(selectmax(t.score_)fromt)----區(qū)分課程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、求選了課程的學(xué)生人selectcount(distinctsno)from--34查詢課程編號(hào)為c001且課程成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和

student.sno,o=course.o='sc.s33、查詢不及格的課程，并按課程號(hào)從大到小

o,sc.score, o=course.sc.orderbysc.score--32查詢?nèi)魏我婚T課程成績(jī)?cè)?0分以上的、課程名稱和分?jǐn)?shù)

student.sname,sc.score, o=osc.score>7031、查詢所有學(xué)生的選課情況；

student.sno,studen o=o30、查詢課程名稱為“數(shù)據(jù)庫(kù)”，且分?jǐn)?shù)低于60的學(xué)生姓名和分

o=course.ame='數(shù)據(jù)庫(kù)'sc.---29、查詢平均成績(jī)大于85的所有學(xué)生的學(xué)號(hào)、和平均成--成績(jī)有可能會(huì)為null[平均成績(jī)包含null]

avg(sc.score),student.snostudentleftjoinscgroupbystudent.snohavingavg(sc.score)>858、查詢每門課程的平均成績(jī)，結(jié)果按平均成績(jī)升序排列，平均成績(jī)相同時(shí)，按課程號(hào)降序排列

oorderbyavg(sc.score) o--27、1981年出生的學(xué)生(注：Student表中Sage列的類型是to_char按照格式轉(zhuǎn)換成數(shù)student.sno,student.snamefromstudentwhereto_char(sysdate,'yyyy')---26、查詢同名同性學(xué)生，并統(tǒng)計(jì)同名人

studentstudentwherestudent_1.sname=student_2.sname)selectstudent.sname,count(*)from studentgroupbystudent.snamehavingcount(*)>125、查詢姓“張”的學(xué)生select*fromstudentwherestudent.snamelike'張%'--24、查詢男生、人selectcount(*)fromstudentgroupby23、查詢出只選修了一門課程的全部學(xué)生的學(xué)號(hào)和selectsc.sno,student.snamefromsc,studentwhere1=1andstudent.sno=sc.snogroupbysc.sno,student.snamehavingcount( --一樣結(jié)selectsc.sno,st.sname,count(cno)fromstudentstleftjoinscongroupbyst.sname,sc.snohavingcount(22、查詢每門課程被選修的學(xué)生數(shù)o o)fromscgroupby--21、查詢各科成績(jī)前三名的記錄:(不考慮成績(jī)并列情況--排序構(gòu)造一張--重點(diǎn) from( ocno_,sc.scorescore_,row_number()over(oorderbysc.scoredesc)from)where20、統(tǒng)計(jì)列印各科成績(jī),各分?jǐn)?shù)段人數(shù):課程ID,課程名稱,[10085],[8570],[70-60],[<--循環(huán)統(tǒng)

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、按各科平均成績(jī)從低到高和及格率的百分?jǐn)?shù)從高到低順序 o,avg(sc.score),sum(casewhensc.score>=60then1else0end)/count(*)as及格率fromgroupbysc.orderbysccno,及格率17、查詢各科成績(jī)最高和最低的分：以如下形式顯示：課程ID，最高分，最低selectmin(sc.score),max(sc.score),max( o)fromscgroupbysc.--15、刪除學(xué)習(xí)“”老師課的SC表記錄； o course.tno=(selectteacher.tnofromteacherwher