華工數(shù)據(jù)庫實(shí)驗(yàn)題

華工數(shù)據(jù)庫實(shí)驗(yàn)題創(chuàng)建Student數(shù)據(jù)庫,包括Students,Courses,SC表，表結(jié)構(gòu)如下:Students(SNO,SNAME,SEX,BDATE,HEIGHT,DEPARTMENT)Courses(CNO,CNAME,LHOUR,CREDIT,SEMESTER)SC(SNO,CN,GRADE)(注：下劃線表示主鍵，斜體表示外鍵)，并插入一定數(shù)據(jù)。createtableStudents(SNOvarchar(100)primarykeySNAMEvarchar(100)null,SEXvarchar(100)null,BDATEdatetimenull,HEIGHTdecimalnull,DEPARTMENTvarchar(100)null)gocreatetableCourses(CNOvarchar(100)primarykeyCNAMEvarchar(100)null,LHOURintnull,CREDITintnull,SEMESTERvarchar(100)null)goCREATETABLE[dbo].[SC]([SNO]varchar(100)NOTNULL,[CNO]varchar(100)NOTNULL,[GRADE][int]NULL,CONSTRAINT[PK_SC]PRIMARYKEYCLUSTERED([SNO]ASC,[CNO]ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY])ON[PRIMARY]GOALTERTABLE[dbo].[SC]WITHCHECKADDCONSTRAINT[FK_SC_Courses]FOREIGNKEY([CNO])REFERENCES[dbo].[Courses]([CNO])GOALTERTABLE[dbo].[SC]CHECKCONSTRAINT[FK_SC_Courses]GOALTERTABLE[dbo].[SC]WITHCHECKADDCONSTRAINT[FK_SC_Students]FOREIGNKEY([SNO])REFERENCES[dbo].[Students]([SNO])GOALTERTABLE[dbo].[SC]CHECKCONSTRAINT[FK_SC_Students]完成如下的查詢要求及更新的要求。查詢身高大丁1.80m的男生的學(xué)號(hào)和姓名；答：selectSNO,SNAMEfromStudentswhereHEIGHT>1.8查詢計(jì)算機(jī)系秋季所開課程的課程號(hào)和學(xué)分?jǐn)?shù)；答：selectCNO,CREDITfromCourseswhereSEMESTER='秋季'查詢選修計(jì)算機(jī)系秋季所開課程的男生的姓名、課程號(hào)、學(xué)分?jǐn)?shù)、成績；答：selects.SNAME,SC.CNO,c.CREDIT,SC.GRADEfromstudentssinnerjoinSConsc.SNO=s.SNOinnerjoinCoursesconsc.CNO=c.CNOwheres.DEPARTMENTS計(jì)算機(jī)系'ands.SEX='男'andc.SEMESTER='秋季'查詢至少選修一門電機(jī)系課程的女生的姓名(假設(shè)電機(jī)系課程的課程號(hào)以EE開頭)；答：selectdistincts.snamefromStudentss,scwheres.sno=sc.snoands.sex='女’olike'EE%'查詢每位學(xué)生已選修課程的門數(shù)和總平■均成績；答：selectcount(c.CNC)as課程門數(shù)，avg(SC.GRADE)as總平均成績fromstudentssinnerjoinSConsc.SNO=s.SNOinnerjoinCoursesconsc.CNO=c.CNOgroupbys.SNO查詢每門課程選課的學(xué)生人數(shù)，最高成績，最低成績和平均成績；答：selectcname,count(cno),max(grade),min(grade),avg(grade)fromstudentsnaturaljoinscnaturaljoincoursesgroupbychane;查詢所有課程的成績都在80分以上的學(xué)生的姓名、學(xué)號(hào)、且按學(xué)號(hào)升序

join(selectsno.min(grade)as答：selectsname,snofromstudentsnaturalminifromscgroupbysno)join(selectsno.min(grade)as查詢?nèi)背煽兊膶W(xué)生的姓名，缺成績的課程號(hào)及其學(xué)分?jǐn)?shù);selectsname,coursescno,creditfromstudents,courses,scwherestudents.sno=o=scselectsname,coursescno,creditfromstudents,courses,scwherestudents.sno=o=oandgradeisnull;(9)名；selectsnamefromstudents,courses,sc(9)名；selectsnamefromstudents,courses,scwherestudents.sno=o=sc.choandcredit>=3andgrade<70;查詢有一門以上(含一門)三個(gè)學(xué)分以上課程的成績低丁70分的學(xué)生的姓(10)查詢1984年~1986年出生的學(xué)生的姓名，總平■均成績及已修學(xué)分?jǐn)?shù)。selectsmane,avg(grade),sun(credit)fromstudentsnaturaljoinscnaturaljoincourseswhereselectfromstudentsnaturaljoinscnaturaljoincourseswherebdatebetween'1984-00-00'and'1987-00-00'groupbysname;(11)deletescwhereSNOlike'%01%'deleteStudentswhereSNOlike'%01%'(12)<0409101<0408130在STUDENT關(guān)系中增加以下記錄:何平向陽1987-03-021986-12-11insertintostudents(11)deletescwhereSNOlike'%01%'deleteStudentswhereSNOlike'%01%'(12)<0409101<0408130在STUDENT關(guān)系中增加以下記錄:何平向陽1987-03-021986-12-11insertintostudentsvalues('0409101'insertintostudentsvalues('0408130'(13)將課程CS-221的學(xué)分?jǐn)?shù)增為3,1.62>1.75>,'何平','女','1987-03-02'，'向陽','男','1986-12-11',1.62,,1.75,講課時(shí)數(shù)增為60在STUDEN和SC關(guān)系中，刪去SNC^'01'開關(guān)的所有記錄。updateupdatecoursessetcredit=3wherecno='GS-221updatecoursessetcredit=60wherecno='GS-221'補(bǔ)充題：(1)統(tǒng)計(jì)各系的男生和女生的人數(shù)orderbydepartmentTOC\o"1-5"\h\zselectdepartment,sum(casewgensex='男,sum(casewgensex='女,count(sno)fromstudentsthen1else0end),then1else0end),groupbydepartmentorderbydepartment(2)列出學(xué)習(xí)過’編譯原理’，’數(shù)據(jù)庫’或’體系結(jié)構(gòu)’課程，且這些課程的成績之一在90分以上的學(xué)生的名字。答：selectsnamefromstudentsnaturaljoinscnaturaljoincourseswherecname='編譯原理'orcname='數(shù)據(jù)庫'orcname='體系結(jié)構(gòu)'andgrade>90;⑶列出未修選’電子技術(shù)’課程，但選修了’數(shù)字電路’或’數(shù)字邏輯’課程的學(xué)生數(shù)。答：selectcount(distincisc.sno)fromcourses,sc.snonotin(o=oandcname='電子技術(shù)')oin(selectcnofromcourseswherecname='數(shù)字邏輯'orcname='數(shù)字電路')(4)按課程排序列出所有學(xué)生的成績，尚無學(xué)生選修的課程，也需要列出，相關(guān)的學(xué)生成績用NULL^示。答：o,ame,sno,gradefromcoursesleftjoinscon(o)o,ame,sno,o,ame,sno,grade;⑸列出平均成績最高的學(xué)生名字和成績。(SELECT^中不得使用TOPn子句)答：selectsname,rfrom(selectsname,avg(grade)asfromstudents,scwherestudents.sno=sc.snogroupbysname,students.snoorderbyrdesc)whererownum=1;選做題：對每門課增加“先修課程”的屆性，用來表示某一門課程的先修課程，每門課程應(yīng)可記錄多丁一門的先修課程。要求：修改表結(jié)構(gòu)的定義，應(yīng)盡量避免數(shù)據(jù)冗余，建立必要的主鍵，外鍵。設(shè)計(jì)并插入必要的測試數(shù)據(jù)，完成以下查詢：列出有資格選修數(shù)據(jù)庫課程的所有學(xué)生。(該學(xué)生已經(jīng)選修過數(shù)據(jù)庫課程的所有先修課，并達(dá)到合格成績。)INSERTS句插入表中注意：須設(shè)計(jì)每個(gè)查詢的測試數(shù)據(jù)，并在查詢之前用實(shí)驗(yàn)二：數(shù)據(jù)庫的安全和完整性約束INSERTS句插入表中1.采用實(shí)驗(yàn)一的建庫腳本和數(shù)據(jù)插入腳本創(chuàng)建Student數(shù)據(jù)庫，并完成以下操新增表Credits(SNO,SumCredit,NoPass),表示每學(xué)生已通過選修課程的合計(jì)學(xué)分?jǐn)?shù)，以及不及格的課程數(shù)。答：createtableCredits(SNOvarchar(100),SumCreditint,NoPassint)創(chuàng)建視圖Student_Grade(Sname,Cname,Grade),表示學(xué)生選修課程及成績的詳細(xì)信息。答：createviewStudent_Gradeasselects.SNAME,c.CNAME,SC.GRADEfromstudentssinnerjoinSConsc.SNO=s.SNOinnerjoinCoursesconsc.CNO=c.CNO在數(shù)據(jù)庫中創(chuàng)建以下觸發(fā)器：Upd_Credit要求：當(dāng)在SC表中插入一條選課成績，自動(dòng)觸發(fā)Upd_Credit,完成在Credits表中修改該學(xué)生的合計(jì)學(xué)分?jǐn)?shù)和不及格的課程數(shù)。答：createtriggerUpd_CreditonSCforinsertasdeclare@SNOvarchar(100),@CNOvarchar(100),@GRADEint,@NoPassint,@CREDITintselect@SNO=SNO,@CNO=CNQ@GRADE=GRADE,@NoPass=(casewhenGRADE<60then1else0end)frominsertedselect@CREDIT=CREDITupdateCreditssetSumCredit=SumCreditNoPass=NoPass+@NoPasswhereSNO=@SNOfromCourseswhereCNO=@CNO+@CREDIT,2)Upd_StuView(Insteadof觸發(fā)器)要求：當(dāng)對視圖Student_Grade作插入數(shù)據(jù)項(xiàng)操作時(shí)，自動(dòng)觸發(fā)Upd_StuView,完成對SC表的插入操作。如：當(dāng)執(zhí)行InsertintoStudent_Gradevalues('王剛'，'數(shù)據(jù)庫'，54)則觸發(fā)器完成另一插入操作：InsertintoSCvalues('980201','CS-110',54)另夕卜，需要檢查當(dāng)前插入的學(xué)生和課程是否已在Students,和Courses表中存在，如不存在，不執(zhí)行任何操作，并提示用戶錯(cuò)誤信息。答：createtriggerUpd_StuViewonStudent_Gradeforinsertasdeclare@SNAMEvarchar(100),@CNAMEvarchar(100),@GRADEintselect@SNAME=SNAME,@CNAM巨CNAME@GRADE=GRADEfrominsertedIF(EXISTS(SELECT*FROMStudentsWHERESNAME=@SNAMEANDEXISTS(SELECT*FROMCoursesWHERECNAME=@CNAME)BEGINInsertintoSCselect(SELECTTOP1SNOFROMStudentsWHERESNAME=@SNAME,(SELECTTOP1CNOFROMCoursesWHERECNAME=@CNAME@GRADEENDPK_SC,FK_SC_SNO,FK_SC_CNO)(選做)要求：首*刪除SC『所有主面日外鍵定義，用觸發(fā)器實(shí)現(xiàn)表SC上的主鍵(SNOCNO和夕卜鍵SNOCNO勺約束定義。3.為Student數(shù)據(jù)庫設(shè)計(jì)安全機(jī)制。要求：在該數(shù)據(jù)庫系統(tǒng)中，有三類用戶：學(xué)生，權(quán)限包括：查詢所有的課程信息，根據(jù)學(xué)號(hào)和課程號(hào)來查詢成績。但不允許修改任何數(shù)據(jù)。(必做)只能查詢自己的成績，不能查詢別人的成績。(選做)老師：權(quán)限包括：查詢有關(guān)學(xué)生及成績的所有信息，有關(guān)課程的所有信息，但不允許修改任何數(shù)據(jù)。答：CREATETRIGGERsecure_studentBEFOREUPDATEORDELETEONdatabaseBEGINIF((selectuserfromdual)='老師')THENRAISE_APPLICATION_ERROR(-20506,'您沒有權(quán)限對學(xué)生表進(jìn)行修改.’)教務(wù)員：權(quán)限包括：查詢和修改任何有關(guān)學(xué)生和課程的信息，但不允許查詢和修改數(shù)據(jù)庫中其它任何表，視圖等數(shù)據(jù)庫對象。答：CREATETRIGGERsecure_studentBEFOREUPDATEORDELETEONdatabaseEXCEPTStudentsBEGINIF((selectuserfromdual)='教務(wù)員')THENRAISE_APPLICATION_ERROR(-20506,'您沒有權(quán)限進(jìn)行修改.')要求：安全控制必須僅由數(shù)據(jù)庫一端來實(shí)現(xiàn)，不考慮由應(yīng)用程序來控制。為此，需要?jiǎng)?chuàng)建三個(gè)用戶，登錄時(shí)密碼驗(yàn)證；分別授予各類權(quán)限，并測試權(quán)限的控制是否有效。實(shí)驗(yàn)三：SQL編程實(shí)驗(yàn)要求：1.采用實(shí)驗(yàn)一的建庫腳本和數(shù)據(jù)插入腳本創(chuàng)建Student數(shù)據(jù)庫。在數(shù)據(jù)庫中創(chuàng)建以下存儲(chǔ)過程：Add_Student(SNO,SNAME,SEX,BIRTHDAY,HEIGHT,DEPT)要求：根據(jù)輸入?yún)?shù)，插入一條學(xué)生記錄。答：createprocedureAdd_Student@SNOvarchar(100),@SNAMEvarchar(100),@SEXvarchar(10),@BIRTHDAYdatetime,@HEIGHTdecimal,@DEPTvarchar(100)asinsertintoStudentsvalues(@SNOSNO,@SNAME@SEX@BIRTHDAY,@HEIGHT,@DEPT)Upd_Grade(SNO,CNO,GRADE)要求：根據(jù)輸入?yún)?shù)，修改某學(xué)生選課的成績。答：createprocedureUpd_Grade@SNOvarchar(100),@CNOvarchar(100),@GRADEINTasUPDATESCsetSNO=@SNOCNO=@CNOGRADE=@GRADEwhereSNO=@SNOandCNO=@CNODisp_Student(SNO,SUM_CREDIToutputAVG_GRADEoutput)要求：根據(jù)SNO^數(shù)顯示該學(xué)生的有關(guān)信息，包括：學(xué)號(hào)，姓名，性別，年齡，身高，系別，所有選修的課程及成績；顯示輸出參數(shù)SUM_CREDI寂示選修課程的總學(xué)分)及AVG_GRAD或示3學(xué)分以上的課程的平&成績)。答：createprocedureUpd_Grade@SNOvarchar(100),@SUM_CREDITINToutput,@AVG_GRADEintoutputasselectinner*fromStudentssjoinSConsc.SNO=s.SNOinnerwherejoinCoursesconsc.CNO=c.CNOs.SNO=@SNOselectwhere@SUM_CREDIT=SumCreditfromCreditsSNO=@SNOselectinnerwheregroup@AVG_GRADE=avg(SC.GRADE)fromStudentssjoinSConsc.SNO=s.SNOs.SNO=@SNOandSC.GRADE>3bys.SNOCAL_GPA(SN,OGPAoutput)要求：根據(jù)SNC^數(shù)，輸出并顯示該學(xué)生的GPAS。計(jì)算方法如下：GRADE(G)GRADEPOINT(GP)TOC\o"1-5"\h\zG>=85485>G>=75375>G>=60260>G1GPA=(￡GP*CREDIT)/工CREDIT)答：createprocedureCAL_GPA@SNOvarchar(100),@GPAdecimaloutputasde