頁腳內(nèi)容頁腳內(nèi)容PRIMARYKEY(secno,tno,cno),FOREIGNKEY(tno)REFERENCESTEACHER(tno),FOREIGNKEY(cno)REFERENCESCOURSE(cno));CREATETABLEENROLL(cnoCHAR(10),seconCHAR(2),snoCHAR(10),gradeINT,PRIMARYKEY(cno,sno),FOREIGNKEY(sno)REFERENCESSTUDENT(sno),FOREIGNKEY(cno)REFERENCESCOURSE(cno));完成數(shù)據(jù)的錄入COURSE表錄入數(shù)據(jù)INSERTINTOCOURSEVALUES('450','WesternCivilization','History','3');INSERTINTOCOURSEVALUES('730','CalculusIv','Math','4');INSERTINTOCOURSEVALUES('290','EnglishComposition','English','3');INSERTINTOCOURSEVALUES('480','CompilerWriting','ComputerScience','3');SELECT*FROMCOURSESTUDENT表錄入數(shù)據(jù)INSERTINTOSTUDENTVALUES('148','Susanpowell','534EastRiverDr','19041','Haverford','PA','F');INSERTINTOSTUDENTVALUES('210','BobDawson','120SouthJefferson','02891','Newport','RI','M');INSERTINTOSTUDENTVALUES('298','HowardMansfield','290WynkoopDrive','22180','Vienna','VA','M');INSERTINTOSTUDENTVALUES('348','SusanPugh','534EastHamptonDr','06107','Hartford','CN','F');INSERTINTOSTUDENTVALUES('349','JoeAdams','73EmmersonStreet','19702','Newark','DE','M');INSERTINTOSTUDENTVALUES('354','JanetLadd','44110thStreet','18073','Pennsburg','PA','F');INSERTINTOSTUDENTVALUES('410','BillJone','120SouthHarrison','92660','Newport','CA','M');INSERTINTOSTUDENTVALUES('473','CarolDean','983ParkAvenue','02169','Boston','MA','F');INSERTINTOSTUDENTVALUES('548','Allenthomas','238WestOxRoad','60624','Chicago','IL','M');INSERTINTOSTUDENTVALUES('558','ValShipp','238WestportRoad','60556','Chicago','IL','F');INSERTINTOSTUDENTVALUES('649','JohnAnderson','473EmmoryStreet','10008','NewYork','NY','M');INSERTINTOSTUDENTVALUES('654','JanetYhomas','4416thStreet','16510','Erie','PA','F');SELECT*FROMSTUDENTTEACHER錄入數(shù)據(jù)INSERTINTOTEACHERVALUES('303','Dr.Horn','257-3049',27540.00);INSERTINTOTEACHERVALUES('290','Dr.Lowe','257-2390',31450.00);INSERTINTOTEACHERVALUES('430','Dr.Engle','56-4621',38200.00);INSERTINTOTEACHERVALUES(180,'Dr.Cooke','257-8088',29560.00);INSERTINTOTEACHERVALUES(560,'Dr.Olsen','257-8086',31778.00);INSERTINTOTEACHERVALUES(784,'Dr.Scango','257-3046',32098.00);SELECT*FROMTEACHERSECTION表錄入數(shù)據(jù)INSERTINTOSECTIONVALUES('1','303','450',2);INSERTINTOSECTIONVALUES('1','290','730',6);INSERTINTOSECTIONVALUES('1','430','290',3);INSERTINTOSECTIONVALUES('1','180','480',3);INSERTINTOSECTIONVALUES('2','560','450',2);INSERTINTOSECTIONVALUES('2','784','480',2);SELECT*FROMSECTIONENROLL表錄入數(shù)據(jù)INSERTINTOENROLLVALUES('730','1','148','3');INSERTINTOENROLLVALUES('450','2','210','3');INSERTINTOENROLLVALUES('730','1','210','1');INSERTINTOENROLLVALUES('290','1','298','3');INSERTINTOENROLLVALUES('480','2','298','3');INSERTINTOENROLLVALUES('730','1','348','2');INSERTINTOENROLLVALUES('290','1','349','4');INSERTINTOENROLLVALUES('480','1','348','4');INSERTINTOENROLLVALUES('480','1','410','2');INSERTINTOENROLLVALUES('450','1','473','2');INSERTINTOENROLLVALUES('730','1','473','3');INSERTINTOENROLLVALUES('480','2','473','0');INSERTINTOENROLLVALUES('290','1','548','2');INSERTINTOENROLLVALUES('730','1','558','3');INSERTINTOENROLLVALUES('730','1','649','4');INSERTINTOENROLLVALUES('480','1','649','4');INSERTINTOENROLLVALUES('450','1','654','4');INSERTINTOENROLLVALUES('450','2','548','1');SELECT*FROMENROLLCOURSEenocnamedepartmentcredit1EnglishCompositionEnglish32WesterriCivilizationHistory33側(cè)CompilerWritingComputerScience34734)匚bIcliIusIvMath4STUDENT

二結(jié)果消息sncisnameaddresscitystatesex114SSusanpowell554EastRiverDr1S041HaverfondPAF221DBobDawson120SouthJeFfersanNewportRlM3asHowandMansfield2S0WynkoopDrive221SCViennaVAM434SSusanPugh5-34EastHamptonDr06107HartfordC：NF5345JoeAdams73EmmersonStreet13702NewarkDEMG354JanetL^dd4411DthStreet1W3Pennsb...PAF7410BillJone120SouthHanisonNewportCAMS473CarolDean&S3ParkAvenueD2169BostonMAF954SAllenthornas23SWestObcRoadG0G24ChicagoILM1fl55SValShipp23SWestpartRoadG055€ChiciagoILF11G4SJohnAnderaon473EmmoiyStneert1DD&SNewYorkMYMTEACHERtnotnanephonemmlmryJ_130Dr.Cooke257-Bi}8S-29醐｝.噩2290Dr.Lowe257-239031450.DD33iJ3Dr.Horn257-3W27W.'SD4430Dr.Engle25t4C21iS200.DD5Dr.Olsen257-Bi^3177S.3DG734Dr.Sea...257-3MCJ2(m.'DDSECTIONsecnotnoenosqty1畫32129073063130324143&3525GD2627B44W2ENROLLCHOsecnosnograde12301爾32290134S43230154S2445022103545014732G450254S174501654434S02眈3CT1Osecnosnograde￡4SD229S334S0134S4104SD141D2114E02473D124W1閒413730114S314730121D115730121673U1473317730155B373U14檢索系名為“Math”和“English”的課程信息SELECT*FROMCOURSEWHEREdepartment='Math'ORdepartment='English'J結(jié)果,3消息enocnarnedepartmentcnedrtEnglish匚ompositionEnglish3273CCalculusIvMath4按字母順序列出教師姓名和電話號碼SELECTtname,phoneFROMTEACHERORDERBYtname檢索電話號碼不是以“257”打頭的教師姓名和電話號碼SELECTtname,phoneFROMTEACHERWHEREphonenotlike'257%'檢索數(shù)學(xué)系所有成績大于3的課程名、系名、學(xué)分SELECTcname,department,creditFROMCOURSEWHEREcnoIN(SELECTcnoFROMENROLLWHEREgrade>3ANDcno='730');cname1CalculuslvdepartmentcreditMath4檢索沒有選修任何課的學(xué)生姓名、學(xué)號SELECTsno,snameFROMSTUDENTWHERENOTEXISTS(SELECT10.10.檢索至少選修教師“Dr.Lowe”所開全部課程的學(xué)生學(xué)號SELECTDINSTINCTsnoFROMENROLLENROLLXWHERENOTEXISTS(SELECT*FROMENROLLENROLLYWHEREcnoin(SELECTcnoFROMTEACHER,SECTION,WHEREtname='Dr.Lowe'ANDTEACHER.tno=SECTION.tno)ANDNOTEXISTS(SELECT*FROMENROLLWHERESTUDENT.sno=ENROLL.sno);snameJJanetLadd曾童向已成功執(zhí)行°檢索沒有選修課程“CalculusIv”的學(xué)生學(xué)號SELECTsnoFROMSTUDENTWHEREsnoNOTIN(SELECTSTUDENT.snoFROMCOURSE,STUDENT,ENROLLWHEREcname='CalculusIv'ANDCOURSE.cno=ENROLL.cnoANDSTUDENT.sno=ENROLL.sno);

FROMENROLLENROLLZWHEREENROLLZ.sno=ENROLLX.snoANDENROLLZ.cno=ENROLLY.cno))（修改數(shù)據(jù)驗(yàn)證，該老師教授課程號為730和500，只有學(xué)號148的學(xué)生同時選擇了這兩門課）SECTIONsecnotnocnosc#y1184J4SD321SOD1312MJ煩641血450251434}沏362MU4502727342ENROLLli5呼1314114&3結(jié)果檢索每門課學(xué)生登記的人數(shù)、相應(yīng)的課程名、課程號、分組號SELECTDISTINCTsecno,ENROLL.cno,cname,sqtyFROMENROLL,COURSE,SECTIONWHERESECTION.cno=ENROLL.cnoANDCOURSE.cno=ENROLL.cnosecnoenocnarnesqty1250EnglishComposrtion3Z45DWestenGvilization2314BCCompilerWriting34173CCaletiluEIv652450WestenGvilizartion2￡24SCCompil&rWriting2檢索選修兩門以上課程的學(xué)生姓名SELECTsnameFROMSTUDENTWHEREsnoin(SELECTsnoFROMENROLLGROUPBYsnoHAVINGCOUNT(*)>2)sname1[CppqID*日n：檢索只有男生選修的課程和學(xué)生名SELECTcname,snameFROMCOURSE,STUDENT,ENROLLWHERESTUDENT.sno=ENROLL.snoANDCOURSE.cno=ENROLL.cnoANDCOURSE.cnoin(SELECTcnoFROMCOURSEWHEREcnonotin(SELECTDISTINCTcnoFROMSTUDENT,ENROLLWHEREsex='F'andSTUDENT.sno=ENROLL.sno))cnamesnarne1]EnglishCompositionHowardMansfield2EnglishCcmpasitionJoeAdams-EnglishCompositionAllenthonnas檢索所有學(xué)生都選修的課程名、學(xué)生名、授課教師名、該生成績SELECTcname,sname,tname,gradeFROMSTUDENT,TEACHER,SECTION,ENROLL,COURSEWHERECOURSE.cno=ENROLL.cnoANDSTUDENT.sno=ENROLL.snoANDTEACHER.tno=SECTION.tnoANDSECTION.cno=ENROLL.cnoANDSECTION.secno=ENROLL.seconANDCOURSE.cno=(SELECTcnoFROMENROLLGROUPBYcnoHAVINGCOUNT(*)=12)cnamesnametnamegrade-國查詢已成功執(zhí)丘刪去名為“JoeAdams”的所有記錄DELETEFROMSTUDENTWHEREsname='JoeAdams';SELECT*FROMSTUDENTsnoanameaddresscipcitystatesex114SSusanps^jell534EastRiverDr19D41HaverfandPAF2210BobDawson12DSouthJeHeraon02S31NewportRlH3298HawardMa...2SDWynkoopDrive221Sd)ViennaVAM434SSusanPugh534EastHampto...06107HartfordCNF5354JanetLadd4411EthStreet18073Pennsb...PAF410BillJone12)0SouthHarrisanNewportCAM7473CarolDean9S3ParkAvenueBostonMAFn.rjnAII■1rrn.4ilrni—'■LLC~?Ti■!-354SAllenthanas腳WestOxHoad?K24ChicagoILH9558Vai5hipp23SWestportRoadChicagoILF10649JohnAider...473EmmaryStreet10M8NewYorkNYH11654JanetYho...441QhStreet1GS10EriePAF把教師“Scango”的編號改為“666”UPGRADETEACHERSETtno='666'WHEREtname='Dr.Scango'SELECT*FROMTEACHER6666Dr.5ca...257-304^320&8.DD統(tǒng)計教師“Engle”教的英語課的學(xué)生平均分SELECTAVG(grade)AVGFROMENROLLWHEREgradein(SELECTgradeFROMTEACHER,SECTION,ENROLLWHEREtname='Dr.Engle'ANDTEACHER.tno=SECTION.tnoANDSECTION.cno=ENROLL.cnoANDENROLL.secno=SECTION.secno)1「3!I■=統(tǒng)計各門課程的選課人數(shù)SELECTCOURSE.cname,COUNT(ENROLL.sno)FROMENROLL,COURSEWHEREENROLL.cno=COURSE.cnoGROUPBYCOURSE.cname;19.輸出如下報表：學(xué)生名課程名教師名成績SELECTsname學(xué)生名，cname課程名，tname教師名，grade成績FROMENROLL,STUDENT,COURSE,TEACHER,SECTIONWHEREENROLL.sno=STUDENT.snoANDSECTION.cno=COURSE.cnoANDENROLL.secno=SECTION.secnoANDENROLL.cno=SECTION.cnoANDSECTION.tno=TEACHER.tno

1SusanPughCompilerWritingEJr.Cooke42BillJone匚arripilerWriting□r.Cooke23JohnAnde...CompilerWritingDr.Cooke44Susanpo...CalculusIv□r.Lowe35BobDaws...CalculLis1軍Dr.LoweSusanPughCalculusIvEJr.Lawe:7CarolDeanCalculLis\vDr.LowemVbIShippCalculusIvDr.Lawe3學(xué)生容課程営埶師名成績9JohnAnde...Caluulu呂IvDr.Lqw@410CarolDeanWestenDvili...Dr.Hom211JanetYho...WesternOvili...Dr.Hom412HowardM...EnglishCamp...Dr.Engle313JaeAdamsEnglishComp...Dr.Engle斗14AllenthomasEnglishCximp...Dr.Engle215BobDaws...WesternGniL.Dr.Olsen316AllerthamasWesternDvili...Dr.Olsen117HowardM...CompilerWritingDr.Sea...1SCaralDeanCompilerWritingDr.Sea...*20.定義并驗(yàn)證觸發(fā)器，當(dāng)?shù)怯洷碓黾右粭l新的記錄時，自動在分組表中更新相應(yīng)屬性。CREATETRIGGERENROLL_1ONENROLLAFTERINSERTASUPDATESECTIONSETsqty=sqty+1FROMSECTION,insertedWHEREinserted.secno=SECTION.secnoANDo=SECTION.cno驗(yàn)證結(jié)果：INSERTINTOENROLLVALUES('450','2','354','1')SELECT*FROMSECTION原始數(shù)據(jù)

1匸［1SD1匸［1SD4802131沏73041303454}51O)290■62450727S44S0執(zhí)行結(jié)果四、課程設(shè)計小結(jié)在本次課設(shè)之前，我已經(jīng)在前段學(xué)習(xí)期間上機(jī)編寫過類似相關(guān)的SQL語句來完成對于學(xué)生-課程-選課的信息查詢，這次在原先實(shí)驗(yàn)的基礎(chǔ)上添加了分組和教師表，創(chuàng)建數(shù)據(jù)庫、基本表和錄入數(shù)據(jù)的過程與上機(jī)實(shí)驗(yàn)基本相同，因此很容易就完成了前三個步驟，在這個過程中對于數(shù)據(jù)庫、表的創(chuàng)建以及數(shù)據(jù)的輸入的操作都更加的熟練。題目4-19都是對于表中數(shù)據(jù)進(jìn)行查詢、修改、刪除等操作，并且運(yùn)用了ORDERBY、GROUPBY等功能短語以及COUNT、AVG等聚集函數(shù)。前兩題都是基本的查詢，比較簡單，但由于輸入數(shù)據(jù)時Dr.Engle中的'.'多按了一個空格，結(jié)果在調(diào)試的時候出現(xiàn)的是錯誤的結(jié)果，結(jié)｝。果不正確(見截圖｝。第10題是本次課設(shè)最難的題目，自己也嘗試著去做，但是由于理解的失誤以及數(shù)據(jù)的巧合性，錯誤的SQL語句得出的結(jié)果卻是正確的。這就導(dǎo)致了自己以為解決了這道難題，以至于在給老師檢查的時候被指出嚴(yán)重的錯誤，感到很羞愧。后來仔細(xì)分析題意，按照所有的關(guān)鍵字來編寫查詢語句，參照書上關(guān)于notexists語句以及嵌套循環(huán)的語句，用謂詞演算將題目轉(zhuǎn)換成邏輯運(yùn)算，等價為“沒有這樣課程y,老師Dr.Lowe教授了這門課，而學(xué)生X沒有選”p表示“老師教授了課程y”q表示“學(xué)生X選了課y”，等價于「3y(pAnq)。參照書上的例題，分層次地編寫功能語句。因?yàn)轭}目的巧合性所以在檢驗(yàn)時修改了數(shù)據(jù)，添加了該老師新開另一門課，只有學(xué)號148的學(xué)生同時選擇了這門老師的兩門課。然后再去驗(yàn)證結(jié)果發(fā)現(xiàn)是正確的。二結(jié)果消息sno1ius■第13題的題目剛開始理解的不透徹，所以直接查詢了男生選修的課程，得到的結(jié)果有10個，但照表檢查可以看出有些課程仍是有女生選的，與題目“只有男生選修”的條件不符，因此重新分析，發(fā)現(xiàn)可以使用嵌套查詢先用子查詢把女生選擇的課程選出來，然后父查詢在子查詢的結(jié)果之外(notin)查詢，這樣就把女生選修的課程出去，這樣就避免出現(xiàn)選擇的課程男、女生都選修的錯誤。(見截圖)-3結(jié)果消息cnamesnarne1[EnglishCompositionHmradMansfield2EnglishComposition■JoeAdams3Engli呂hComposrtiorijMlenthomas4WesternDvilizaticnBobDawson5WesternDrilizationjyienthomas6CompilerWritingHowandMansfield7CompilerWritin口BillJane涉直詢已成功執(zhí)行.第15、16題是對于學(xué)生、教師表的修改和刪除。在執(zhí)行過程中程序一直報錯，始終找不出問題所在。后來經(jīng)過詢問同學(xué)，發(fā)現(xiàn)SECTION和ENROLL表中引用了這兩個表中的sno和tno,由于其引用關(guān)系，所以無法對于這兩個表進(jìn)行修改。因此需要先刪除外碼的引用關(guān)系，然后才能對其進(jìn)行修改和刪除。第20題定義觸發(fā)器這個比較陌生，在學(xué)習(xí)的時候就不是很掌握，因此對于這個題目第一印象就是不想去做，我不會。但是后來通過翻閱書本并且詢問同學(xué)，并且自己不斷的摸索，雖然起初一直報錯，但是我并沒有氣餒，在各種幫助和努力下終于實(shí)現(xiàn)了最后一個功能。不過在給老師檢查的過程中，發(fā)現(xiàn)UPDATE語句后面自己添加了FROM的語句，雖然對于整個執(zhí)行結(jié)果沒有太大的影響，但這是一個常識性的錯誤，不應(yīng)該出現(xiàn)如此低級的錯誤，因?yàn)楫?dāng)時是想新表需要和SECTION、ENROLL表進(jìn)行連接,所以在寫的過程中直接添加了o二SECTION.cnoANDinserted.secno二SECTION.secno的錯誤語句，而且還誤將其中的SECTION寫成了ENROLL表，以至于在老師詢問的時候，自己也被自己的代碼弄得不知所措。后來修改的時候索性把所有的條件語句都刪除了，并沒有進(jìn)行臨時新表inserted與SECTION表的連接，以至于結(jié)果每一行的學(xué)生登記數(shù)量sqty都加一，得到錯誤的結(jié)果。過程比較艱辛但是最后完成的時候內(nèi)心的喜悅確實(shí)滿滿洋溢的。果然自己去努力實(shí)現(xiàn)的事情可以給自己帶來無限的快樂，自己從中收獲的經(jīng)驗(yàn)和知識也是更多的。不要因?yàn)樽约簩τ谀撤矫娌皇煜ぞ椭苯臃艞墸鴳?yīng)該努力去嘗試一下。只有試過了，才知道自己到底可不可以完成這個看似困難的問題！附表2課程（COURSE）課程號（eno）課程名（cname）系名(department)學(xué)分(credit)450WesternCiviliza