數(shù)據(jù)庫第二次作業(yè)

數(shù)據(jù)庫第二次作業(yè)數(shù)據(jù)庫第二次作業(yè)數(shù)據(jù)庫第二次作業(yè)資料僅供參考文件編號：2022年4月數(shù)據(jù)庫第二次作業(yè)版本號：A修改號：1頁次：1.0審核：批準:發(fā)布日期：《數(shù)據(jù)庫第二次作業(yè)——上機實驗題》實驗一：交互式SQL的使用實驗要求：1,創(chuàng)建Student數(shù)據(jù)庫，包括Students,Courses,SC表，表結構如下：Students(SNO,SNAME,SEX,BDATE,HEIGHT,DEPARTMENT)Courses(CNO,CNAME,LHOUR,CREDIT,SEMESTER)SC(SNO,CNO,GRADE)(注：下劃線表示主鍵，斜體表示外鍵)，并插入一定數(shù)據(jù)。

答：

createtableStudents(SNOvarchar(100)primarykey,SNAMEvarchar(100)null,SEXvarchar(100)null,BDATEdatetimenull,HEIGHTdecimalnull,DEPARTMENTvarchar(100)null)gocreatetableCourses(CNOvarchar(100)primarykey,CNAMEvarchar(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_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]2．完成如下的查詢要求及更新的要求。（1）查詢身高大于的男生的學號和姓名；答:

selectSNO,SNAMEfromStudentswhereHEIGHT>查詢計算機系秋季所開課程的課程號和學分數(shù)；

答：

selectCNO,CREDITfromCourseswhereSEMESTER=’秋季’查詢選修計算機系秋季所開課程的男生的姓名、課程號、學分數(shù)、成績；

答：

select,,,fromstudentss

innerjoinSCon=

innerjpinCoursescon=c,CNO

where=’計算機系’and=’男’and=’秋季’查詢至少選修一門電機系課程的女生的姓名（假設電機系課程的課程號以EE開頭）；

答：

selectdistinctfromStudentss,scwhere=and=’女’andlike’EE%’查詢每位學生已選修課程的門數(shù)和總平均成績；

答：

selectcountas課程門數(shù),avgas總平均成績fromstudentss

innerjoinSCon=

innerjoinCoursescon=

group?by?（6）查詢每門課程選課的學生人數(shù),最高成績,最低成績和平均成績；

答：

select?cname,count(cno),max(grade),min(grade),avg(grade)

from?students?natural?join?sc?natural?join?courses

group?by?chane;（7）查詢所有課程的成績都在80分以上的學生的姓名、學號、且按學號升序排列；

答：

select?sname,sno?from?students?natural?join?(select?(grade)asminifrom?sc?group?by?sno)where?mini>80?order?by(sno);（8）查詢?nèi)背煽兊膶W生的姓名，缺成績的課程號及其學分數(shù)；

答：

selectsname,,creditfromstudents,courses,scwhere=and=andgradeisnull;查詢有一門以上(含一門)三個學分以上課程的成績低于70分的學生的姓名；

答：

selectsnamefromstudents,courses,sc

where=and=andcredit>=3andgrade<70;（10）查詢1984年~1986年出生的學生的姓名,總平均成績及已修學分數(shù)。

答：

selectsname,avg(grade),sum(credit)

fromstudentsnaturaljoinscnaturaljoincourses

wherebdatebetween‘1984-00-00’and‘1987-00-00’

groudbysname（11）在STUDENT和SC關系中，刪去SNO以’01’開關的所有記錄。

答：

deletescwhereSNOlike’%01%’

deleteStudentswhereSNOlike’%01%’（12）在ＳＴＵＤＥＮＴ關系中增加以下記錄：<0409101何平女1987-03-02><0408130向陽男1986-12-11>

答：

insertintostudentsvalues<’0409101’,’何平’,’女’,’1987-03-03’,’’’>

insertintostudentsvalues<’0408130’,’向陽’,’男’,’1986-12-11’,’’’>將課程CS-221的學分數(shù)增為３，講課時數(shù)增為６０

答：

updatecoursessetcredit=3wherecno=’CS-211’

updatecoursesset1hour=60wherecno=’CS-211’3．補充題：(1)統(tǒng)計各系的男生和女生的人數(shù)。

答:

selectdepartment,sum(casewgensex=’男’then1else0end),

sum(casewgensex=’女’then1else0end),

count(sno)fromstudentsgroupbydepartmentorderbydeparment;(2)列出學習過‘編譯原理’，‘數(shù)據(jù)庫’或‘體系結構’課程，且這些課程的成績之一在90分以上的學生的名字。

答：

selectsnme

fromstudentsnaturaljoinscnaturaljoincourses

wherecname=’編輯原理’orcname=’體系結構’andgrade>90(3)列出未修選‘電子技術’課程，但選修了‘數(shù)字電路’或‘數(shù)字邏輯’課程的學生數(shù)。

答：

selectcount(distincifromcourses,notin(selectfrom=andcname=’電子技術’)andin(selectcnofromcourseswherecname=’數(shù)學邏輯’orcname=’數(shù)字電路’)(4)按課程排序列出所有學生的成績，尚無學生選修的課程，也需要列出，相關的學生成績用NULL表示。

答：

selectdistinct,,sno,gradefromcoursesleftjoinscon

groupby,,sno,grade

groupby,,sno,grade;(5)列出平均成績最高的學生名字和成績。(SELECT句中不得使用TOPn子句)

答：

selectsname,r

from(selectsname,avg(grade)asfromstudents,scwhere=groupbysname,orderbyrdesc)whererownum=1;4．選做題：對每門課增加“先修課程”的屬性，用來表示某一門課程的先修課程，每門課程應可記錄多于一門的先修課程。要求：1)修改表結構的定義，應盡量避免數(shù)據(jù)冗余，建立必要的主鍵，外鍵。2)設計并插入必要的測試數(shù)據(jù)，完成以下查詢：列出有資格選修數(shù)據(jù)庫課程的所有學生。(該學生已經(jīng)選修過數(shù)據(jù)庫課程的所有先修課，并達到合格成績。)注意：須設計每個查詢的測試數(shù)據(jù)，并在查詢之前用INSERT語句插入表中。實驗二：數(shù)據(jù)庫的安全和完整性約束實驗要求：1．采用實驗一的建庫腳本和數(shù)據(jù)插入腳本創(chuàng)建Student數(shù)據(jù)庫,并完成以下操作：1）新增表Credits(SNO,SumCredit,NoPass)，表示每學生已通過選修課程的合計學分數(shù)，以及不及格的課程數(shù)。

答：

createtableCredits(SNOvarchar(100),SumCreditint,NoPassint)

2）創(chuàng)建視圖Student_Grade(Sname,Cname,Grade)，表示學生選修課程及成績的詳細信息。

答：

createviewStudent_Grade

as

select,,fromstudentss

innerjoinSCon=

innerjoinCoursescon=在數(shù)據(jù)庫中創(chuàng)建以下觸發(fā)器：Upd_Credit要求：當在SC表中插入一條選課成績，自動觸發(fā)Upd_Credit，完成在Credits表中修改該學生的合計學分數(shù)和不及格的課程數(shù)。

答:

createtriggerUpd_CreditonSCforinsert

as

declare@SNOvarchar(100),@CNOvarchar(100),@GRADEint,@NoPassint,@CREDITint

select@SNO=SNO,@CNO=CNO,@GRADE=GRADE,@NoPass=(casewhenGRADE<60then1else0end)frominserted

updateCredits

setSumCredit=SumCredit+@CREDIT,

NoPass=NoPass+@NoPass

whereSNO=@SNOUpd_StuView（Insteadof觸發(fā)器）要求：當對視圖Student_Grade作插入數(shù)據(jù)項操作時，自動觸發(fā)Upd_StuView，完成對SC表的插入操作。

如：當執(zhí)行InsertintoStudent_Gradevalues(‘王剛’，’數(shù)據(jù)庫’，54)

則觸發(fā)器完成另一插入操作：InsertintoSCvalues(‘980201’，’CS-110’，54)另外，需要檢查當前插入的學生和課程是否已在Students，和Courses表中存在，如不存在，不執(zhí)行任何操作，并提示用戶錯誤信息。

答:

createtriggerUpd_StuViewonStudent_Gradeforinsert

as

declare@SNAMEvarchar(100),@CNAMEvarchar(100),@GRADEint

select@SNAME=SNAME,@CNAME=CNAME,@GRADE=GRADEfrominserted

IF(EXISTS(SELECT*FROMStudentsWHERESNAME=@SNAME)ANDEXISTS(SELECT*FROMCoursesWHERECNAME=@CNAME))BEGINInsertintoSCselect(SELECTTOP1SNOFROMStudentsWHERESNAME=@SANME),(SELECTTOP1CNOFROMCoursesWHERECNAME=@CNAME),@GRADE ENDPK_SC,FK_SC_SNO,FK_SC_CNO)（選做）要求：首先刪除SC中所有主鍵和外鍵定義，用觸發(fā)器實現(xiàn)表SC上的主鍵（SNO，CNO）和外鍵SNO，CNO的約束定義。3．為Student數(shù)據(jù)庫設計安全機制。要求：在該數(shù)據(jù)庫系統(tǒng)中，有三類用戶：學生，權限包括：查詢所有的課程信息，根據(jù)學號和課程號來查詢成績。但不允許修改任何數(shù)據(jù)。(必做)只能查詢自己的成績，不能查詢別人的成績。（選做）

2）老師：權限包括：查詢有關學生及成績的所有信息，有關課程的所有信息，但不允許修改任何數(shù)據(jù)。

答:

CREATETRIGGRTsecure_student

BEFOREUPDATEORDELETEONdatabase

BEGIN

IF((selectuserfromdual)=’老師’)

THEN

RAISE_APPLICATION_ERROE(-20506,

‘您沒有權限對學生表進行修改?！?

3）教務員：權限包括：查詢和修改任何有關學生和課程的信息，但不允許查詢和修改數(shù)據(jù)庫中其它任何表，視圖等數(shù)據(jù)庫對象。

答:

CREATETRIGGERsecure_student

BEFOREUPDATEORDELETEONdatabaseEXCEPTStudents

BEGIN

IF((selectuserfromdual)=’教務員’)

THEN

RAISE_APPLICATION_ERROR(-20506,

‘您沒有權限進行修改?！?要求：安全控制必須僅由數(shù)據(jù)庫一端來實現(xiàn)，不考慮由應用程序來控制。為此，需要創(chuàng)建三個用戶，登錄時密碼驗證；分別授予各類權限，并測試權限的控制是否有效。實驗三：SQL編程實驗要求：1．采用實驗一的建庫腳本和數(shù)據(jù)插入腳本創(chuàng)建Student數(shù)據(jù)庫。2．在數(shù)據(jù)庫中創(chuàng)建以下存儲過程：Add_Student(SNO,SNAME,SEX,BIRTHDAY,HEIGHT,DEPT)要求：根據(jù)輸入?yún)?shù)，插入一條學生記錄。

答:

createprocedureAdd_Student

@SNOvarchar(100),

@SNAMEvarchar(100),

@SEXvarchar(10),

@BIRTHDAYdatetime,

@HEIGHTdecimal,

@DEPTvarchar(100)

as

insertintoStudents

values(

@SNO,

SNO,

@SNAME,@SEX,@BIRTHDAY,@HEIGHT,@DEPT)Upd_Grade(SNO,CNO,GRADE)要求：根據(jù)輸入?yún)?shù)，修改某學生選課的成績。

答:

createprocedureUpd_Grade

@SNOvarchar(100),

@CNOvarchar(100),@GRADEINTASUPDATESCsetSNO=@SNO,CNO=@CNO,GRADE=@GRADEwhereSNO=@SNOandCNO=@CNODisp_Student(SNO，SUM_CREDIToutput，AVG_GRADEoutput)要求：根據(jù)SNO參數(shù)顯示該學生的有關信息，包括：a)學號，姓名，性別，年齡，身高，系別，所有選修的課程及成績；b)顯示輸出參數(shù)SUM_CREDIT(表示選修課程的總學分)及AVG_GRADE(表示3學分以上的課程的平均成績)。

答:

createprocedureUpd_Grade

@SNOvarchar(100),

@SUM_CREDITINToutput,

@AVG_GRADEintoutput

as

select*fromStudentss

innerjoinSCon=

innerjoinCoursescon=

where=@SNO

select@SUM_CREDIT=SumCreditfromCredits

whereSNO=@SNO

select@AVG_GRADE=avgfromStudentss

innerjoinSCon=

where=@SNOand>3groupbyCAL_GPA(SNO，GPAoutput)要求：根據(jù)SNO參數(shù),輸出并顯示該學生的GPA值。計算方法如下：GRADE(G)GRADEPOINT(GP)G>=85485>G>=75375>G>=60260>G1GPA=(∑GP*CREDIT)/∑CREDIT)

答:

createprocedureCAL_GPA

@SNOvarchar(100),

@GPAdecimaloutput

as

de