數(shù)據(jù)庫實驗及數(shù)據(jù)庫實驗二

實驗三建表、修改表、刪除表1.建立數(shù)據(jù)庫jxgl在jxgl數(shù)據(jù)庫中建立學生表student,課程表course,選修表sc，建表過程有如下方式1）以圖形界面操作2）以SQL操作（以下腳本可直接在查詢分析器中執(zhí)行）CreateTableStudent(SnoCHAR(5)NOTNULLPRIMARYKEY(Sno),SnameVARCHAR(20),SageSMALLINTCHECK(Sage>=15ANDSage<=45),SsexCHAR(2)DEFAULT'男'CHECK(Ssex='男'ORSsex='女'),SdeptCHAR(2));CreateTableCourse(CnoCHAR(2)NOTNULLPRIMARYKEY(Cno),CnameVARCHAR(20),CpnoCHAR(2),CcreditSMALLINT);CreateTableSC(SnoCHAR(5)NOTNULLCONSTRAINTS_FFOREIGNKEYREFERENCESStudent(Sno),CnoCHAR(2)NOTNULL,GradeSMALLINTCHECK((GradeISNULL)OR(GradeBETWEEN0AND100)),PRIMARYKEY(Sno,Cno),CONSTRAINTC_FFOREIGNKEY(Cno)REFERENCESCourse(Cno));INSERTINTOStudentVALUES('98001','錢橫',18,'男','CS');INSERTINTOStudentVALUES('98002','王林',19,'女','CS');INSERTINTOStudentVALUES('98003','李民',20,'男','IS');INSERTINTOStudentVALUES('98004','趙三',16,'女','MA');INSERTINTOCourseVALUES('1','數(shù)據(jù)庫系統(tǒng)','5',4);INSERTINTOCourseVALUES('2','數(shù)學分析',null,2);INSERTINTOCourseVALUES('3','信息系統(tǒng)導(dǎo)論','1',3);INSERTINTOCourseVALUES('4','操作系統(tǒng)原理','6',3);INSERTINTOCourseVALUES('5','數(shù)據(jù)結(jié)構(gòu)','7',4);INSERTINTOCourseVALUES('6','數(shù)據(jù)處理基礎(chǔ)',null,4);INSERTINTOCourseVALUES('7','C語言','6',3);INSERTINTOSCVALUES('98001','1',87);INSERTINTOSCVALUES('98001','2',67);INSERTINTOSCVALUES('98001','3',90);INSERTINTOSCVALUES('98002','2',95);INSERTINTOSCVALUES('98002','3',88);課程表Course（課程號Cno,課程名Cname,先修課號Cpno,學分Ccredit）學生表Student（學號Sno,姓名Sname,年齡Sage,性別Ssex,所在系Sdept）學生選課表SC（學號Sno,課程號Cno,成績Grade)

實驗四Select查詢操作練習（1）基于“教學管理”數(shù)據(jù)庫jxgl，使用SQL的查詢語句表示下列查詢：①檢索年齡大于23歲的男學生的學號和姓名；selectSno,SnamefromStudentwhereSage>'23'ANDSsex='男';②檢索至少選修一門課程的女學生的姓名；selectSnamefromStudent,SCwhereSsex='女'ANDStudent.Sno=SC.SnogroupbyStudent.Snamehavingcount(*)>=1;或者SelectSnameFromStudentWhereSsex='女'ANDSnoin(selectsnofromSCgroupbysnohavingcount(*)>=1);③檢索王同學不學的課程的課程號；selectCnofromCoursewhereCourse.Cnonotin(selectCnofromSC,StudentwhereSC.Sno=Student.SnoANDSnameLIKE'王%');④檢索至少選修兩門課程的學生學號；selectDISTINCTStudent.SnofromStudent,SCWHEREStudent.Sno=SC.SnoGROUPBYStudent.SnoHAVINGCOUNT(*)>=2;⑤檢索全部學生都選修的課程的課程號與課程名；SELECTCno,CnamefromCoursewherenotexists(select*fromstudentwherenotexists(select*fromSCwhereSC.sno=Student.SnoANDSC.Cno=Course.Cno))或者假設(shè)所有學生只有兩人SELECTCno,CnamefromCourseWHERECourse.Cnoin(selectCnofromSCgroupbySC.Cnohavingcount(Sno)=(selectcount(*)fromStudent));SELECTCno,CnamefromCourseWHERECourse.Cnoin(selectCnofromSCgroupbySC.Cnohavingcount(Sno)=2);⑥檢索選修了所有3學分課程的學生學號。selectdistinctStudent.SnofromStudent,SCwhereexists(select*fromCoursewhereCcredit='3'ANDStudent.Sno=SC.SnoANDSC.Cno=Course.Cno);基于“教學管理”數(shù)據(jù)庫jxgl,試用SQL的查詢語句表達下列查詢：①統(tǒng)計有學生選修的課程門數(shù)；selectcount(distinctSC.Cno)FROMSC;②求選修4號課程的學生的平均年齡；SELECTavg(Student.Sage)fromStudent,SCwhereStudent.Sno=SC.SnoANDCno='4';SELECTavg(Student.Sage)as平均年齡fromStudent,SCwhereStudent.Sno=SC.SnoANDCno='3';③求學分為3的每門課程的學生平均成績；SELECTavg(SC.Grade)fromCourse,SC,StudentwhereStudent.Sno=SC.SnoANDCourse.Ccredit='3'groupbySC.Cno用groupby語句以課程號分組注意，如果程序是這樣的：SELECTavg(SC.Grade)fromCourse,SC,StudentwhereStudent.Sno=SC.SnoANDCourse.Ccredit='3'ANDCourse.Cno=SC.Cno;只顯示一門課程的成績?、芙y(tǒng)計每門課程的學生選修人數(shù)，要求超過3人的課程才統(tǒng)計，要求輸出課程號和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號升序排列；SELECTCno,count(Sno)fromSCGROUPBYCnoHAVINGCount(Sno)>3orderbycount(sno)DESC,Cno;SELECTCno,count(Sno)as選修人數(shù)fromSCGROUPBYCnoHAVINGCount(Sno)>1orderbycount(sno)DESC,Cno;⑤檢索學號比“王林”同學大而年齡比他小的學生姓名；SELECTSnameFROMStudentwhereSno>(selectSnofromStudentwhereSname='王林')ANDSage<(selectSagefromStudentwhereSname='王林');或者也可以selectX.snamefromStudentasX,StudentasYwhereX.Sno>Y.SnoANDX.Sage<Y.SageANDY.Sname='王林';⑥檢索姓名以“王”開頭的所有學生的姓名和年齡；selectSname,SagefromStudentwhereStudent.Snamelike'王%';注意：like那里不能用等號“=”⑦在SC中檢索成績?yōu)榭罩档膶W生學號和課程號；SELECTSno,CnofromSCwhereGradeisnull注意：isnull那里不能用等號“=”⑧求年齡大于女學生平均年齡的男學生姓名和年齡；SELECTSname,SagefromStudentwhereSage>(selectavg(Sage)fromstudentwhereSsex='女')ANDSsex='男';⑨求年齡大于所有女學生年齡的男學生姓名和年齡；SELECTSname,SagefromStudentwhereSage>(SELECTMAX(Sage)fromStudentwhereSsex='女')ANDSsex='男';⑩檢索所有比“王林”年齡大的學生姓名、年齡和性別；SELECTSname,Sage,SsexfromStudentwhereSage>(selectSagefromStudentwhereSname='王林')⑾檢索選修“2”課程的學生中成績最高的學生的學號；SELECTSno,GradefromSCwhereGrade=(selectMAX(Grade)fromSCwhereCno='2');注意：不能寫成Grade=MAX(Grade)的形式，因為不存在！Count,min,avg也是⑿檢索學生姓名以及所選修課程的課程號和成績；selectSname,Cno,GradefromSC,StudentwhereStudent.Sno=SC.Sno;⒀檢索選修4門以上課程的學生總成績（不統(tǒng)計不及格的課程），并要求按總成績的降序排列出來。選擇4門以上selectsno,sum(Grade)sumfromSCwheregrade>=60ANDSnoin(selectSnofromSCgroupbySnohavingcount(Sno)>4)groupbysnoorderbysum(grade)desc注意：選擇2門課（包括兩門）selectsno,sum(Grade)fromSCwheregrade>=60ANDSnoin(selectSnofromSCgroupbySnohavingcount(Sno)>=2)groupbysnoorderbysum(grade)desc再注意：Selectsno,sum(grade)FromSCWheregrade>=60Groupbysnohavingcount(sno)>1Orderbysum(grade)desc因為輸入的數(shù)據(jù)分數(shù)都大于60，所以結(jié)果與上面的例子相同。但是，當分數(shù)小于60時，有課不及格的同學的學號會被刪除，統(tǒng)計不完整。任課教師：《數(shù)據(jù)庫原理及應(yīng)用》實驗報告學號：姓名：班級：

實驗二 數(shù)據(jù)庫的簡單查詢和高級查詢實驗?zāi)康模罕緦嶒灥哪康氖鞘箤W生掌握SQLServer查詢分析器的使用方法，加深對SQL和SQL語言的查詢語句的理解。熟練掌握簡單表的數(shù)據(jù)查詢、數(shù)據(jù)排序和數(shù)據(jù)連接查詢的操作方法，加深SQL語言的嵌套查詢語句的理解，熟練掌握數(shù)據(jù)查詢中的分組、統(tǒng)計、計算和集合的操作方法。二、實驗內(nèi)容（一）簡單查詢操作。該實驗包括投影、選擇條件表達，數(shù)據(jù)排序，使用臨時表等。具體完成以下題目，將它們轉(zhuǎn)換為SQL語句表示，在學生選課數(shù)據(jù)庫中實現(xiàn)其數(shù)據(jù)查詢操作。求數(shù)學系學生的學號和姓名求選修了課程的學生學號求選修課程號為‘1’的學生號和成績，并要求對查詢結(jié)果按成績的降序排列，如果成績相同按學號的升序排列。求選修課程號為‘1’且成績在80~90之間的學生學號和成績，并將成績乘以0.8輸出。求數(shù)學系或計算機系姓張的學生的信息。求缺少了成績的學生的學號和課程號（二）連接查詢操作。該實驗包括等值連接、自然連接、求笛卡兒積、一般連接、外連接、內(nèi)連接、左連接、右連接和自連接等。查詢每個學生的情況以及他所選修的課程求學生的學號、姓名、選修的課程及成績求選修課程號為‘1’且成績在90以上的學生學號、姓名和成績查詢每一門課程的間接先行課（即先行課的先行課）（三）嵌套查詢操作：在SQLServer查詢分析器中使用IN、比較符、ANY或ALL和EXISTS操作符進行嵌套查詢操作。具體完成以下各題。將它們用SQL語句表示，在學生選課中實現(xiàn)其數(shù)據(jù)嵌套查詢操作。求選修了高等數(shù)學的學號和姓名求‘1’課程的成績高于劉晨的學生學號和成績求其他系中比計算機系某一學生年齡小的學生（即年齡小于計算機系年齡最大者的學生）求其他系中比計算機系學生年齡都小的學生求選修了‘2’課程的學生姓名求沒有選修‘2’課程的學生姓名查詢選修了全部課程的學生姓名求至少選修了學號為“95002”（四）集合查詢和統(tǒng)計查詢：分組查詢實驗。該實驗包括分組條件表達、選擇組條件表達的方法。使用函數(shù)查詢的實驗。該實驗包括統(tǒng)計函數(shù)和分組統(tǒng)計函數(shù)的使用方法。集合查詢實驗。該實驗并操作UNION、交操作INTERSECT和差操作MINUS的實現(xiàn)方法。具體完成以下例題，將它們用SQL語句表示，在學生選課中實現(xiàn)其數(shù)據(jù)查詢操作。求學生的總?cè)藬?shù)求選修了課程的學生人數(shù)求課程和選修了該課程的學生人數(shù)求選修超過3門課的學生學號查詢計算機科學系的學生及年齡不大于19歲的學生查詢計算機科學系的學生與年齡不大于19歲的學生的交集查詢計算機科學系的學生與年齡不大于19歲的學生的差集查詢選修課程‘1’的學生集合與選修課程‘2’的學生集合的交集查詢選修課程‘1’的學生集合與選修課程‘2’的學生集合的差集三、實驗方法將查詢需求用SQL語言表示；在SQLServer查詢分析器的輸入?yún)^(qū)中輸入SQL查詢語句；設(shè)置查詢分析器的結(jié)果區(qū)為StandardExecute(標準執(zhí)行)或ExecutetoGrid（網(wǎng)格執(zhí)行）方式；發(fā)布執(zhí)行命令，并在結(jié)果區(qū)中查看查詢結(jié)果；如果結(jié)果不正確，要進行修改，直到正確為止。所使用的學生管理庫中的三張表為：STUDENT（學生信息表）SNO（學號）SNAME（姓名）SEX（性別）SAGE（年齡）SDEPT（所在系）95001李勇男20CS95002劉晨女19IS95003王名女18MA95004張立男19IS95005李明男22CS95006張小梅女23IS95007封曉文女20MACOURSE（課程表）CNO（課程號）CNAME（課程名）CPNO（先行課）CCREDIT（學分）1數(shù)據(jù)庫542數(shù)學23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理27PASCAL語言64SC（選修表）SNO（學號）CNO（課程號）Grade(成績)95001192950012859500138895002290950023809500317895003280950041909500446095005180950053899500638095007465四、實驗結(jié)果與分析（SQL代碼及查詢結(jié)果截圖）源代碼：1.1SELECTSNO,SNAMEFROMSTUDENTWHERESDEPT='MA';1.2SELECTDISTINCTSNOFROMSC;1.3SELECTSNO,GradeFROMSCWHERECNO='1'ORDERBYGradeDESC,SNO;1.4SELECTSNO,Grade*0.8FROMSCWHERECNO='1'ANDGradeBETWEEN80AND901.5SELECT*FROMSTUDENTWHERESDEPTIN('CS','MA')ANDSNAMELIKE'張%';1.6SELECTSNO,CNOFROMSCWHEREGradeISNULL;2.1SELECTSTUDENT.*,SC.*FROMSTUDENT,SCWHERESTUDENT.SNO=SC.SNO;2.2SELECTSTUDENT.SNO,SNAME,SC.CNO,GradeFROMSTUDENT,SCWHERESTUDENT.SNO=SC.SNO;2.3SELECTSTUDENT.SNO,SNAME,GradeFROMSTUDENT,SCWHERESTUDENT.SNO=SC.SNOANDSC.CNO='1'ANDS