2023年數(shù)據(jù)庫實驗報告14

實驗內(nèi)容與規(guī)定請有選擇地實踐以下各題。(1)基于“教學(xué)管理”數(shù)據(jù)庫ｊｘgl，使用SＱL的查詢語句表達下列查詢：①檢索年齡大于23歲的男學(xué)生的學(xué)號和姓名;SELEＣＴSno,SｎamｅFＲＯＭStudentＷHERESsex=’男’AＮDSaｇe>23;②檢索至少選修一門課程的女生姓名;ＳＥLECTSｎａmｅFROMSｔudenｔWHEREＳsｅx=’女’ANDSnｏＩＮ（?SＥLECＴSnoFＲＯMSCGRＯＵPBYSnｏＨＡVIＮGｃoｕnt(＊)＞=1;);③檢索王同學(xué)不學(xué)的課程的課程號;ＳEＬECTCno;FＲOＭCourseＷHEＲEＣnｏNOTIN（ SＥＬEＣTCnｏFROMStudeｎt,SＣＷHERＥSnａmｅlikｅ’王%’ANDStuｄenｔ.Ｓｎｏ＝SC．Ｓｎo);④檢索至少選修兩門課程的學(xué)生學(xué)號；ＳELEＣTDISTＩＮCTSnoＦROMSCGROＵPＢＹＳｎoＨAVINGcｏunt（*）＞=2;⑤檢索所有學(xué)生都選修的課程的課程號與課程名；ＳELＥCTＣno,CｎameFＲＯＭCourseWＨＥRENOTEXＩSTＳ(?ＳＥLECT*ＦROMSｔuｄeｎtWHERENＯTEXISＴＳ( SELECT＊FＲOMＳCＷHEＲESC.Sｎo=Ｓtudent.ＳnｏＡNDSC.Cｎｏ=Ｃourse.Cno）);⑥檢索選修了所有3學(xué)分課程的學(xué)生學(xué)號;SELEＣＴDＩSTＩNCＴＳnoＦRＯMSCXWHERENOTEXISＴS(?SEＬECT*FROMCｏurseWHEREＣcredit＝3ANＤＮＯTEＸＩＳTS( SELECT*FＲOMSCYWＨＥRＥX.Sno=Y.SnoANDCouｒｓe．Cｎｏ＝Y(jié).Cno)）;基于“教學(xué)管理”數(shù)據(jù)庫jｘgｌ，使用SＱL的查詢語句表達下列查詢：①記錄有學(xué)生選修的課程門數(shù);?SEＬECTｃount(DISＴINＣTＣｎo)FROＭＳC；②求選修4號課程的學(xué)生的平均年齡；?SEＬＥCTAVG(Sａge)FROMStudｅnt，SCＷHERECno=4AＮＤSｔudｅｎt.Sｎo=SＣ．Sno;③求學(xué)分為3的每門課程的學(xué)生平均成績;SELＥCTAVG(Ｇradｅ)ＦROMCｏurｓe,SCＷHERECｃredｉt=3ＡNDCｏurse.Cｎo=SＣ.ＣｎｏＧＲOＵPＢYＳC.Ｃno;④記錄每門課程的學(xué)生選修人數(shù)，規(guī)定超過3人的課程才記錄，規(guī)定輸出課程號和選修人數(shù),查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號升序排列；SＥLＥCTCno,ｃount（Sｎo）FＲＯMSＣGROＵＰＢYＣnoＨＡVINGcouｎｔ(Sno)>3ORDERBYcｏｕｎt(Sno)DESC,CnoASＣ；⑤檢索學(xué)號比“王菲”同學(xué)大而年齡比他小的學(xué)生姓名；ＳELECTＳnaｍeFROMＳtudentXWHERESno>(?ＳELＥCＴSnoＦＲOMStudｅntＹWHEＲESｎame=’王菲’AＮDSagｅ＞(?SELＥCTSaｇeFROMＳtuｄｅntZWHＥRＥSnａｍe=’王菲’ANDX.Sno＝Ｚ．SｎoANＤY．Sno＝Z.Ｓnｏ)）;⑥檢索姓名以“王”打頭的所有學(xué)生的姓名和年齡;SELECＴSname,SageFＲOMStuｄentWHＥＲESnameLIKE‘王%’；⑦在SC中檢索成績?yōu)榭罩玫膶W(xué)生學(xué)號和課程號；SELECTSnｏ，CnoＦＲＯMＳCWHEREGraｄｅisNULL;⑧求年齡大于女同學(xué)平均年齡的男學(xué)生姓名和年齡;SELECTSnａmｅ,SagｅFRＯMStuｄeｎtXＷＨＥRESｓｅx＝’男’ＡＮDSagｅ>( SELEＣTＡVG（Sage)FROMStｕdeｎｔWHERESseｘ=’女’ANDX．Sno=Ｙ.Ｓｎo);⑨求年齡大于所有女同學(xué)年齡的男同學(xué)姓名和年齡；ＳＥLＥCＴSname,SageFROMＳｔｕdenｔXWHERESsex=’男’ＡNDSage>( SELＥCTMＡX(Sａｇe)FROＭStｕｄｅｎtYＷHＥRＥSsex=’女’ANＤX.Sno=Y．Sｎo)；⑩檢索所有比“王華”年齡大的學(xué)生姓名，年齡和性別;ＳELECTSname，Sage,SｓexＦRＯMStｕdentXWHERESaｇe>(?SELECTSagｅFＲOMＳtudｅntYWHEＲESｎamｅ=’王華’ANDＸ．Sno=Y.Ｓｎo);①檢索選修“2”課程的學(xué)生中成績最高的學(xué)生和學(xué)號;SＥLＥCTSname,SC.SnoFROＭStudenｔ,SCＷHERECｎo=２AＮDSｔudeｎt.Snｏ=SC.Sｎo;②檢索學(xué)生姓名和其所選修課程的課程號和成績；SELECTSnamｅ,Ｃno,ＧraｄeＦROＭStudeｎt,SCＷHEREStｕｄeｎt.Ｓｎｏ＝SＣ.ＳｎoGＲOUPＢYSname;③檢索選修4門以上課程的學(xué)生總成績(不記錄不及格的課程),并規(guī)定按總成績的降序排列出來;ＳELＥCTSno,SＵM(Ｇraｄe)FROMSCＸWＨERＥGrade>＝６0ＡNＤSｎｏIＮ( SＥLEＣTＳnoＦROMSＣYWHEREX.Snｏ=Y.SnoGROUPBYSｎoHAVINGｃoｕｎt(Cｎo)＞４)` ORDERBYＳUM(Gｒaｄｅ)DESＣ；設(shè)有表4-1~表4-4的4個基本表（表結(jié)構(gòu)于表內(nèi)容是假設(shè)的）,請先創(chuàng)建數(shù)據(jù)庫及根據(jù)表內(nèi)容創(chuàng)建表結(jié)構(gòu)，并添加表記錄,寫出實現(xiàn)以下各題功能的SＱL語句：創(chuàng)建以下4各表:CREATＥTAＢLESTUDENＴ(SNOCHAR(6)PＲIMＡRYKEY,SNＡMECHAＲ（２0)UNIQUE,SEＸCHＡR(2),AGEＳMALＬINT,CLASSCHＡＲ(4）)；CREＡTETＡBＬEＴEＡCＨER(TNOCＨAR(３)PRIMARYＫＥT,TNAMWＣHAR(２0）UNIQUE,SＥXCHＡR(2),AGＥSMALLINT,PROFCHAR（10）,DEPTCＨＡR（10));CＲEATETＡＢLEＣOURSE(CNＯCHAR(4)PRＩＭARYＫEY,CNAMEＣHAR(20)ＵNIQUE,TNOCＨAT(3)，F(xiàn)OREIGNＫEYTＮOＲEＦERENCESTＥACHER(TNO)）;CREＡTEＴABLＥＳC（SNOCHAＲ(6),CNOCHAR(4）,ＧRＡDESMALLINT，ＰRIMAＲYKEY(SNO,CNO）,FOREIGＮKEYＳＮOＲＥFEREＮCEＳSTUDENT(SNO)，F(xiàn)ORＥIＧNKEYCＮOREFEＲENCESCOURSＥ(ＣＮO)）；插入數(shù)據(jù):IＮＳＥRTINＴＯSTUDEＮTＶＡLUES(‘9801０1’,’李華’,‘男’,1９,’９80１’)；.．...．同上方法依次插入數(shù)據(jù)①查詢選修課程“8１0５”且成績在８0到90之間的所有記錄;SＥLＥＣT*FＲＯＭＳCWHEＲEＣＮO=’８10５’ANDＧRADＥBETWEEＮ80AND9０;②查詢成績?yōu)?9,8９或99的記錄;ＳELECT＊FROMSCWHEREＧＲADEIN(７9,89,9９)；③查詢“９8０３”班的學(xué)生人數(shù);SEＬECＴｃount(SNＯ)FROMＳTＵDENTWHEＲEＳNＯＬIKＥ‘9８0３%’;④查詢至少有２0名學(xué)生選修的并且課程號以８開頭的課程及平均成績;SELECＴCＮＯ,AVG(GRADE）FＲＯＭＳCWHＥRECＮOLＩＫE‘8%’GＲOUPBYCNO；HAVＩNGｃoｕnt（SＮＯ）>=20;⑤查詢最低分大于80,最高分小于95的SNO與平均分；SELＥＣTSNO,AVG(GRADE)ＦＲOMSCGRＯUPBYSNOHAＶＩNＧMIN（GRADE）>80ANDMＡX（GRＡＤE)＜９５；⑥查詢“980３”班的學(xué)生所選各課程的課程號及平均成績；SELECTCNO,AVG（GRADＥ)FＲＯＭSＣＷＨＥREＳNOLIＫE‘98０3’GＲOＵPＢＹCＮＯ；⑦查詢選修“8１０5”課程的成績高于“98０30２”號同學(xué)成績的所有同學(xué)的記錄；SELECT*FROＭSCＸWHERＥＣNＯ=’81０5’AＮDGRAＴE>(SELEＣＴGRATEFＲOMＳCＹＷＨERＥＣNO=’8１0５’ＡNDSNO=’9８030２’AＮDX.SNO=Y.SＮO);⑧查詢與學(xué)號為“980103”的同學(xué)同歲的所有學(xué)生的SNO,ＳNAME和AGE；SELＥCTSＮO，SＮAME,ＡGEＦROMSTUDENTXＷHEREＡＧＥ＝（SELECＴAGEFROMSTＵDENTＹＷＨEＲＥSＮO＝’９80１03’ANDＸ.SNO＝Ｙ.SNＯ);⑨查詢“錢軍”教師任課的課程號,以及選修其課程學(xué)生的學(xué)號和成績；SEＬEＣTSC．CNO,ＳNO，GRADEFROMSC,TEAＣＨRＴ,COUＲSEWHEＲETNAMＥ＝’錢軍’ANDTEＡCHEＲ.TNO=COUＲＳE.TＮＯANＤSC.ＣＯＮ=COURSE.ＣOＮ;⑩查詢選修某課程的學(xué)生人數(shù)多于20人的教師姓名；SEＬＥCTDISTINCTTNＡMEFＲOMTEＡＣHER，SC,COURSＥWＨEＲETＥACHER.TNO=COURＳE.TNＯANDSＣ.CＯN=ＣOURＳE.ＣONＧROUPBYＳC.CNOＨAVＩNGcｏunt(SNO)>20；1１查詢選修編號為“8１05”課程且成績至少高于其選修編號為“8２45”課程成績的同學(xué)的ＳNＯ及“810５”課程成績,并按成績從高到低依次排列;SELECTSNOＧRAＤEFROMSＣＸWＨEREＣNO=’8105’AＮDGＲADE>(SＥLECTGRAＤEFRＯMＳCYWHEＲECNO=’8245’AＮDX.SNO=Ｙ.SNO）ＯRDEＲＢYGＲＡDＥDEＳC；12查詢選修編號為“８10５”課程且成績高于所有選修編號為“８２４５”課程成績的同學(xué)的CNO、ＳNO、GRＡDE;SＥLECTCNO，SNＯ,GRADＥＦROMSCWＨEＲEＣＮO＝’8105’ANDGRADE＞（SELEＣTMAX(GRADE)FＲＯＭSCＷHＥRECＮO＝’8245’ANDＸ.CNO=Y.ＳＮＯ);13列出所有教師和同學(xué)的姓名,SEX,AＧE;SEＬECTTNAMＥ,TEＡＣHER.SEX，ＴEACＨＥR.AGE,SNAＭE,ＳTUDＥNT.SEX,STUDENＴ.AGEFRＯＭＴEＡＣHEＲ，ＳC１4查詢成績比該課程平均成績高的學(xué)生的成績表;SEＬECT*FＲOMＳC.ＸGROUPBＹＳNOＨAVＩNGGRＡＤE>( ＳELＥＣTAVG(GRＡＤE)FROMＳC.YＷHＥRＥX.CＮＯ=Y.CNOＧＲOUPBYCＮO);1５列出所有任課教師的ＴNAME和ＤEPT；ＳELECTTNAME,ＤEPＴFＲOＭTEＡCHEＲ,ＣOURSＥＷHERETＥACHER.TNO=COＵRSE.TNＯ1６列出所有未講課教師的TNAME和ＤEPT；SELECTTNAME,DＥPＹFROMTEＡCＨＥRWHERENOＴEXISTS(SＥLECT*FＲOＭCＯUＲSＥＷHＥRETEAＣHER.TNＯ=COＵRSE.TNＯ);17列出至少有４名男生的班號；SEＬECTCLＡＳＳFRＯＭSTUDEＮTGRＯUPＢYCLASSHAＶINGｃｏunt（SNO)＞=4;18查詢不姓“張”的學(xué)生記錄；SＥLECＴ*ＦＲＯMSTUDENＴWHERESNAMENOTＬIKＥ‘張％’;19查詢每門課最高分的學(xué)生的ＳNO,CＮO,GRADE;SEＬECＴSNO,CNO,GRＡDEFRＯMSCGＲOＵPBＹＣNOHAVIＮＧGRADE＝MAX（ＧＲADE）;20查詢與“李華”同性并同班的同學(xué)SNAME；SELECＴＳNAMＥFROMSＴＵDEＮTXWHEＲEＣLＡＳS=（SELEＣTCLASSFRＯＭSＴUDENTＹWＨERＥSNAMＥ=’李華’ＡNDSEＸ＝(ＳELSCＴSEXＦＲOMSTUＤEＮTＺＷＨERESNＡME=’李華’AＮDX.SNＯ＝Ｙ.ＳＮＯANDY.SＮO=Z.ＳNＯ)）;２1查詢“女”教師及其所上的課程；SELEＣTTNAＭE，CNO,CＮAMＥFＲOMTEACHEＲ,COURＳEWHERETEACＨＥR.ＴNO=CＯUＲSE.TＮＯ,SEX＝’女’;２２查詢選修“數(shù)據(jù)庫系統(tǒng)”課程的“男”同學(xué)的成績表;SELEＣT*ＦRＯMＳC,CＯURSＥ,ＳTUDENTＷHEREＳTＵDENT.ＳＮO＝SC．SNＯAＮDC