2023年数据库实验报告14

实验内容与规定请有选择地实践以下各题。(1)基于“教学管理”数据库ｊｘgl，使用SＱL的查询语句表达下列查询：①检索年龄大于23岁的男学生的学号和姓名;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;);③检索王同学不学的课程的课程号;ＳEＬECTCno;FＲOＭCourseＷHEＲEＣnｏNOTIN（ SＥＬEＣTCnｏFROMStudeｎt,SＣＷHERＥSnａmｅlikｅ’王%’ANDStuｄenｔ.Ｓｎｏ＝SC．Ｓｎo);④检索至少选修两门课程的学生学号；ＳELEＣTDISTＩＮCTSnoＦROMSCGROＵPＢＹＳｎoＨAVINGcｏunt（*）＞=2;⑤检索所有学生都选修的课程的课程号与课程名；Ｓ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学分课程的学生学号;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.Cno)）;基于“教学管理”数据库jｘgｌ，使用SＱL的查询语句表达下列查询：①记录有学生选修的课程门数;ﻩSEＬECTｃount(DISＴINＣTＣｎo)FROＭＳC；②求选修4号课程的学生的平均年龄；ﻩSEＬＥCTAVG(Sａge)FROMStudｅnt，SCＷHERECno=4AＮＤSｔudｅｎt.Sｎo=SＣ．Sno;③求学分为3的每门课程的学生平均成绩;SELＥCTAVG(Ｇradｅ)ＦROMCｏurｓe,SCＷHERECｃredｉt=3ＡNDCｏurse.Cｎo=SＣ.ＣｎｏＧＲOＵPＢYＳC.Ｃno;④记录每门课程的学生选修人数，规定超过3人的课程才记录，规定输出课程号和选修人数,查询结果按人数降序排列，若人数相同，按课程号升序排列；SＥLＥCTCno,ｃount（Sｎo）FＲＯMSＣGROＵＰＢYＣnoＨＡVINGcouｎｔ(Sno)>3ORDERBYcｏｕｎt(Sno)DESC,CnoASＣ；⑤检索学号比“王菲”同学大而年龄比他小的学生姓名；Ｓ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ｏ)）;⑥检索姓名以“王”打头的所有学生的姓名和年龄;SELECＴSname,SageFＲOMStuｄentWHＥＲESnameLIKE‘王%’；⑦在SC中检索成绩为空置的学生学号和课程号；SELECTSnｏ，CnoＦＲＯMＳCWHEREGraｄｅisNULL;⑧求年龄大于女同学平均年龄的男学生姓名和年龄;SELECTSnａmｅ,SagｅFRＯMStuｄeｎtXＷＨＥRESｓｅx＝’男’ＡＮDSagｅ>( SELEＣTＡVG（Sage)FROMStｕdeｎｔWHERESseｘ=’女’ANDX．Sno=Ｙ.Ｓｎo);⑨求年龄大于所有女同学年龄的男同学姓名和年龄；ＳＥLＥCＴSname,SageFROMＳｔｕdenｔXWHERESsex=’男’ＡNDSage>( SELＥCTMＡX(Sａｇe)FROＭStｕｄｅｎtYＷHＥRＥSsex=’女’ANＤX.Sno=Y．Sｎo)；⑩检索所有比“王华”年龄大的学生姓名，年龄和性别;ＳELECTSname，Sage,SｓexＦRＯMStｕdentXWHERESaｇe>(ﻩSELECTSagｅFＲOMＳtudｅntYWHEＲESｎamｅ=’王华’ANDＸ．Sno=Y.Ｓｎo);①检索选修“2”课程的学生中成绩最高的学生和学号;SＥLＥCTSname,SC.SnoFROＭStudenｔ,SCＷHERECｎo=２AＮDSｔudeｎt.Snｏ=SC.Sｎo;②检索学生姓名和其所选修课程的课程号和成绩；SELECTSnamｅ,Ｃno,ＧraｄeＦROＭStudeｎt,SCＷHEREStｕｄeｎt.Ｓｎｏ＝SＣ.ＳｎoGＲOUPＢYSname;③检索选修4门以上课程的学生总成绩(不记录不及格的课程),并规定按总成绩的降序排列出来;Ｓ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Ｃ；设有表4-1~表4-4的4个基本表（表结构于表内容是假设的）,请先创建数据库及根据表内容创建表结构，并添加表记录,写出实现以下各题功能的SＱL语句：创建以下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)，FOREIGNＫ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)，FORＥIＧNKEYCＮOREFEＲENCESCOURSＥ(ＣＮO)）；插入数据:IＮＳＥRTINＴＯSTUDEＮTＶＡLUES(‘9801０1’,’李华’,‘男’,1９,’９80１’)；.．...．同上方法依次插入数据①查询选修课程“8１0５”且成绩在８0到90之间的所有记录;SＥLＥＣT*FＲＯＭＳCWHEＲEＣＮO=’８10５’ANDＧRADＥBETWEEＮ80AND9０;②查询成绩为79,8９或99的记录;ＳELECT＊FROMSCWHEREＧＲADEIN(７9,89,9９)；③查询“９8０３”班的学生人数;SEＬECＴｃount(SNＯ)FROMＳTＵDENTWHEＲEＳNＯＬIKＥ‘9８0３%’;④查询至少有２0名学生选修的并且课程号以８开头的课程及平均成绩;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３”班的学生所选各课程的课程号及平均成绩；SELECTCNO,AVG（GRADＥ)FＲＯＭSＣＷＨＥREＳNOLIＫE‘98０3’GＲOＵPＢＹCＮＯ；⑦查询选修“8１０5”课程的成绩高于“98０30２”号同学成绩的所有同学的记录；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);⑧查询与学号为“980103”的同学同岁的所有学生的SNO,ＳNAME和AGE；SELＥCTSＮO，SＮAME,ＡGEＦROMSTUDENTXＷHEREＡＧＥ＝（SELECＴAGEFROMSTＵDENTＹＷＨEＲＥSＮO＝’９80１03’ANDＸ.SNO＝Ｙ.SNＯ);⑨查询“钱军”教师任课的课程号,以及选修其课程学生的学号和成绩；SEＬEＣTSC．CNO,ＳNO，GRADEFROMSC,TEAＣＨRＴ,COUＲSEWHEＲETNAMＥ＝’钱军’ANDTEＡCHEＲ.TNO=COUＲＳE.TＮＯANＤSC.ＣＯＮ=COURSE.ＣOＮ;⑩查询选修某课程的学生人数多于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”课程成绩的同学的Ｓ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５”课程且成绩高于所有选修编号为“８２４５”课程成绩的同学的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列出所有教师和同学的姓名,SEX,AＧE;SEＬECTTNAMＥ,TEＡＣHER.SEX，ＴEACＨＥR.AGE,SNAＭE,ＳTUDＥNT.SEX,STUDENＴ.AGEFRＯＭＴEＡＣHEＲ，ＳC１4查询成绩比该课程平均成绩高的学生的成绩表;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查询不姓“张”的学生记录；SＥLECＴ*ＦＲＯMSTUDENＴWHERESNAMENOTＬIKＥ‘张％’;19查询每门课最高分的学生的ＳNO,CＮO,GRADE;SEＬECＴSNO,CNO,GRＡDEFRＯMSCGＲOＵPBＹＣNOHAVIＮＧGRADE＝MAX（ＧＲADE）;20查询与“李华”同性并同班的同学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＝’女’;２２查询选修“数据库系统”课程的“男”同学的成绩表;SELEＣT*ＦRＯMＳC,CＯURSＥ,ＳTUDENTＷHEREＳTＵDENT.ＳＮO＝SC．SNＯAＮDC