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

1、1,創(chuàng)建Student 數(shù)據(jù)庫(kù)，包括Students,Courses,SC 表，表結(jié)構(gòu)如下:Students(SNO,SNAME,SEX,BDATE,HEIGHT,DEPARTMENT)Courses(CNO,CNAME,LHOUR,CREDIT,SEMESTER)SC(SNO,CNO ,GRADE)(注：下劃線表示主鍵，斜體表示外鍵)，并插入一定數(shù)據(jù)。答：create table Students(SNO varchar(100) primary key ,SNAME varchar(100) null,SEX varchar(100) null,BDATE datetime null,HE

2、IGHT decimal null,DEPARTMENT varchar(100) null)gocreate table Courses(CNO varchar(100) primary key ,CNAME varchar(100) null,LHOUR int null,CREDIT int null,SEMESTER varchar(100) null)goCREATE TABLE dbo.SC(SNO varchar(100) NOT NULL,CNO varchar(100) NOT NULL,GRADE int NULL,CONSTRAINT PK_SC PRIMARY KEY

3、CLUSTERED(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 PRIMARYGOALTER TABLE dbo.SC WITH CHECK ADD CONSTRAINT FK_SC_Courses FOREIGNKEY(CNO)REFERENCES dbo.Courses (CNO)GOALTER TABLE dbo.SC CHECK CO

4、NSTRAINT FK_SC_CoursesGOALTER TABLE dbo.SCWITH CHECK ADDCONSTRAINT FK_SC_Students FOREIGNKEY(SNO)REFERENCES dbo.Students (SNO)GOALTER TABLE dbo.SC CHECK CONSTRAINT FK_SC_Students2 完成如下的查詢要求及更新的要求。1）查詢身高大于1.80m 的男生的學(xué)號(hào)和姓名；select SNO,SNAME from Students where HEIGHT>1.8秋季 '2）查詢計(jì)算機(jī)系秋季所開(kāi)課程的課程號(hào)和學(xué)分?jǐn)?shù)

5、；select CNO,CREDIT from Courses where SEMESTER='3）查詢選修計(jì)算機(jī)系秋季所開(kāi)課程的男生的姓名、課程號(hào)、學(xué)分?jǐn)?shù)、成績(jī)；select s.SNAME,SC.CNO,c.CREDIT,SC.GRADE from students s inner join SC on sc.SNO=s.SNO inner join Courses c on sc.CNO=c.CNOwhere s.DEPARTMENT=' 計(jì)算機(jī)系' and s.SEX=' 男 ' and c.SEMESTER=' 秋季 '4）查詢

6、至少選修一門(mén)電機(jī)系課程的女生的姓名（假設(shè)電機(jī)系課程的課程號(hào)以女 ' and oEE開(kāi)頭）；select distinct s.sname from Students s,sc where s.sno=sc.sno and s.sex= like 'EE%'5）查詢每位學(xué)生已選修課程的門(mén)數(shù)和總平均成績(jī)；select count(c.CNO) as 課程門(mén)數(shù),avg(SC.GRADE) as 總平均成績(jī)from students sinner join SC on sc.SNO=s.SNOinner join Courses c on sc.CNO=c.CNOgroup b

7、y s.SNO6）查詢每門(mén)課程選課的學(xué)生人數(shù),最高成績(jī),最低成績(jī)和平均成績(jī)；select cname,count(cno),max(grade),min(grade),avg(grade) from students natural join sc natural join courses group by chane;（ 7）查詢所有課程的成績(jī)都在80 分以上的學(xué)生的姓名、學(xué)號(hào)、且按學(xué)號(hào)升序排列；select sname,sno from students natural join (select sno.min(grade)as mini from sc group by sno)wher

8、e mini>80 order by(sno);(8)查詢?nèi)背煽?jī)的學(xué)生的姓名，缺成績(jī)的課程號(hào)及其學(xué)分?jǐn)?shù)；答：select sname,o,creditfrom students,courses,scwhere students.sno=sc.sno and o=o and grade is null;(9)查詢有一門(mén)以上(含一門(mén))三個(gè)學(xué)分以上課程的成績(jī)低于70 分的學(xué)生的姓名；答：select sname from students,courses,scwhere students.sno=sc.sno and o=sc.cho and credit>=3 and grade<

9、;70;( 10 )查詢 1984 年 1986 年出生的學(xué)生的姓名,總平均成績(jī)及已修學(xué)分?jǐn)?shù)。答：select smane,avg(grade),sun(credit)from students natural join sc natural join courseswhere bdate between '1984-00-00' and '1987-00-00'group by sname;(11)在STUDENT和SC關(guān)系中，刪去SNO以01'開(kāi)關(guān)的所有記錄delete sc where SNO like '%01% delete Stude

10、nts where SNO like '%01%(12)在S TUD ENT關(guān)系中增加以下記錄：<0409101何平女1987-03-021.62>何平 ',' 女 ','1987-03-02',1.62,'')','男 ','1986-12-11',1.75,'')<0408130向陽(yáng)男1986-12-111.75>insert into students values('0409101', insert into students

11、values('0408130',(13)將課程CS-221的學(xué)分?jǐn)?shù)增為3 ,講課時(shí)數(shù)增為6 0update courses set credit=3 where cno='GS-221 update courses set credit=60 where cno='GS-221'3 補(bǔ)充題：(1) 統(tǒng)計(jì)各系的男生和女生的人數(shù)。select department, sum(case wgen sex=' 男 ' then 1 else 0 end),sum(case wgen sex=' 女 ' then 1 else 0

12、 end), count(sno) from students group by department order by department;(2) 列出學(xué)習(xí)過(guò)編譯原理， 數(shù)據(jù)庫(kù)或體系結(jié)構(gòu)課程，且這些課程的成績(jī)之一在90 分以上的學(xué)生的名字。select sname from students natural join sc natural join courseswhere cname=' 編譯原理' or cname=' 數(shù)據(jù)庫(kù) ' or cname=' 體系結(jié)構(gòu)' and grade>90;(3) 列出未修選電子技術(shù)課程，但選修了數(shù)

13、字電路或數(shù)字邏輯課程的學(xué)生數(shù)。')數(shù)字邏輯' or cname=' 數(shù)字電路')select count(distinci sc.sno) from courses,sc.sno not in (select sc.sno from o=o and cname= and o in (select cno from courses where cname=(4) 按課程排序列出所有學(xué)生的成績(jī)，尚無(wú)學(xué)生選修的課程，也需要列出，相關(guān)的學(xué)生成績(jī)用NULL 表示。select distinct o,ame,sno,grade from courses left join

14、sc on (o-o) group by o,ame,sno,grade order by o,ame,sno,grade;(5)列出平均成績(jī)最高的學(xué)生名字和成績(jī)。(SELECT句中不得使用TOP n子句)答：select sname,rfrom(select sname,avg(grade)as from students,sc where students.sno=sc.sno group bysname,students.sno order by r desc)where rownum=1;4選做題：對(duì)每門(mén)課增加“先修課程”的屬性，用來(lái)表示某一門(mén)課程的先修課程，每門(mén)課程應(yīng)可記錄多于一門(mén)的

15、先修課程。要求：1) 修改表結(jié)構(gòu)的定義，應(yīng)盡量避免數(shù)據(jù)冗余，建立必要的主鍵，外鍵。2) 設(shè)計(jì)并插入必要的測(cè)試數(shù)據(jù)，完成以下查詢：列出有資格選修數(shù)據(jù)庫(kù)課程的所有學(xué)生。(該學(xué)生已經(jīng)選修過(guò)數(shù)據(jù)庫(kù)課程的所有先修課，并達(dá)到合格成績(jī)。)注意：須設(shè)計(jì)每個(gè)查詢的測(cè)試數(shù)據(jù)，并在查詢之前用INSERT語(yǔ)句插入表中。實(shí)驗(yàn)二：數(shù)據(jù)庫(kù)的安全和完整性約束實(shí)驗(yàn)要求 ：Student 數(shù)據(jù)庫(kù) ,并完成以下操1 采用實(shí)驗(yàn)一的建庫(kù)腳本和數(shù)據(jù)插入腳本創(chuàng)建作：1 )新增表Credits(SNO,SumCredit,NoPass) ，表示每學(xué)生已通過(guò)選修課程的合計(jì)學(xué)分?jǐn)?shù)，以及不及格的課程數(shù)。答： create table Credit

16、s (SNO varchar(100),SumCredit int,NoPass int)2)創(chuàng)建視圖Student_Grade(Sname,Cname,Grade) ，表示學(xué)生選修課程及成績(jī)的詳細(xì)信息。答： create view Student_Gradeasselect s.SNAME,c.CNAME,SC.GRADE from students sinner join SC on sc.SNO=s.SNOinner join Courses c on sc.CNO=c.CNO2 . 在數(shù)據(jù)庫(kù)中創(chuàng)建以下觸發(fā)器：1 )Upd_Credit要求：當(dāng)在SC 表中插入一條選課成績(jī)，自動(dòng)觸發(fā)Upd

17、_Credit ，完成在Credits 表中修改該學(xué)生的合計(jì)學(xué)分?jǐn)?shù)和不及格的課程數(shù)。答： create trigger Upd_Credit on SC for insert asdeclare SNO varchar(100),CNO varchar(100),GRADE int ,NoPassint ,CREDIT intselect SNO=SNO,CNO=CNO,GRADE=GRADE,NoPass=(case whenGRADE<60 then 1 else 0 end) from insertedselect CREDIT=CREDIT from Courses where

18、CNO=CNOupdate Creditsset SumCredit=SumCredit+CREDIT,NoPass=NoPass+NoPasswhere SNO =SNO2 )Upd_StuView ( Instead of 觸發(fā)器)要 求 ： 當(dāng) 對(duì) 視 圖 Student_Grade 作 插 入 數(shù) 據(jù) 項(xiàng) 操 作 時(shí) ， 自 動(dòng) 觸 發(fā)Upd_StuView ,完成對(duì)SC表的插入操作。如：當(dāng)執(zhí)行Insert into Student_Grade values( 王剛 ， 數(shù)據(jù)庫(kù)， 54)則觸發(fā)器完成另一插入操作：Insert into SC values( 980201 ， CS-11

19、0 ， 54)另外，需要檢查當(dāng)前插入的學(xué)生和課程是否已在Students ，和 Courses表中存在，如不存在，不執(zhí)行任何操作，并提示用戶錯(cuò)誤信息。答： create trigger Upd_StuView on Student_Grade for insertasdeclare SNAME varchar(100),CNAME varchar(100),GRADE intselect SNAME=SNAME,CNAME=CNAME,GRADE=GRADE from insertedIF(EXISTS(SELECT * FROM Students WHERE SNAME=SNAME) AND

20、EXISTS (SELECT * FROM Courses WHERE CNAME=CNAME)-可編輯修改-BEGINInsert into SCselect (SELECT TOP 1 SNO FROM Students WHERE SNAME=SNAME),(SELECT TOP 1 CNO FROM Courses WHERE CNAME=CNAME),GRADEEND3 )PK_SC,FK_SC_SNO,FK_SC_CNO) (選做)要求：首先刪除SC中所有主鍵和外鍵定義，用觸發(fā)器實(shí)現(xiàn)表SC上的主鍵(SNO, CNO)和外鍵SNO, CNO的約束定義。4 .為Student數(shù)據(jù)庫(kù)設(shè)計(jì)

21、安全機(jī)制。要求：在該數(shù)據(jù)庫(kù)系統(tǒng)中，有三類用戶：1)學(xué)生，權(quán)限包括：查詢所有的課程信息，根據(jù)學(xué)號(hào)和課程號(hào)來(lái)查詢成績(jī)。但不允許修改任何數(shù)據(jù)。(必做)只能查詢自己的成績(jī)，不能查詢別人的成績(jī)。(選做)2)老師：權(quán)限包括：查詢有關(guān)學(xué)生及成績(jī)的所有信息，有關(guān)課程的所有信息，但不允許修改任何數(shù)據(jù)。答：CREATE TRIGGER secure_studentBEFORE UPDATE OR DELETE ON databaseBEGINIF(select user from dual)=' 老師')THENRAISE_APPLICATION_ERROR(-20506,您沒(méi)有權(quán)限對(duì)學(xué)生表進(jìn)行修

22、改.)3)教務(wù)員：權(quán)限包括：查詢和修改任何有關(guān)學(xué)生和課程的信息，但不允許 查詢和修改數(shù)據(jù)庫(kù)中其它任何表，視圖等數(shù)據(jù)庫(kù)對(duì)象。答：CREATE TRIGGER secure_studentBEFORE UPDATE OR DELETE ON database EXCEPT StudentsBEGINIF(select user from dual)=' 教務(wù)員')THENRAISE_APPLICATION_ERROR(-20506,您沒(méi)有權(quán)限進(jìn)行修改.)要求：安全控制必須僅由數(shù)據(jù)庫(kù)一端來(lái)實(shí)現(xiàn)，不考慮由應(yīng)用程序來(lái)控制。為此，需要?jiǎng)?chuàng)建三個(gè)用戶，登錄時(shí)密碼驗(yàn)證；分別授予各類權(quán)限，并測(cè)試權(quán)

23、 限的控制是否有效。實(shí)驗(yàn)三：SQL編程實(shí)驗(yàn)要求：1 .采用實(shí)驗(yàn)一的建庫(kù)腳本和數(shù)據(jù)插入腳本創(chuàng)建Student數(shù)據(jù)庫(kù)。2 .在數(shù)據(jù)庫(kù)中創(chuàng)建以下存儲(chǔ)過(guò)程：1) Add_Student (SNO,SNAME,SEX,BIRTHDAY,HEIGHT,DEPT)要求：根據(jù)輸入?yún)?shù)，插入一條學(xué)生記錄。-可編輯修改-答： create procedure Add_Student SNO varchar(100),SNAME varchar(100),SEX varchar(10),BIRTHDAY datetime,HEIGHT decimal,DEPT varchar(100)asinsert into S

24、tudentsvalues(SNO,SNO,SNAME,SEX,BIRTHDAY,HEIGHT,DEPT)2) Upd_Grade (SNO, CNO, GRADE) 要求：根據(jù)輸入?yún)?shù)，修改某學(xué)生選課的成績(jī)。答： create procedure Upd_GradeSNO varchar(100), CNO varchar(100),GRADE INTasUPDATE SCset SNO=SNO,CNO=CNO,GRADE=GRADEwhere SNO=SNO and CNO=CNO3) Disp_Student (SNO ， SUM_CREDIT output ， AVG_GRADE ou

25、tput)要求：根據(jù)SNO 參數(shù)顯示該學(xué)生的有關(guān)信息，包括：a)學(xué)號(hào)，姓名，性別，年齡，身高，系別，所有選修的課程及成績(jī)；b)顯示輸出參數(shù)SUM_CREDIT(表示選修課程的總學(xué)分)及AVG_GRADE(表示3 學(xué)分以上的課程的平均成績(jī))。答： create procedure Upd_GradeSNO varchar(100),SUM_CREDIT INT output,AVG_GRADE int outputasselect * from Students sinner join SC on sc.SNO=s.SNO inner join Courses c on sc.CNO=c.CNO

26、where s.SNO=SNOselect SUM_CREDIT=SumCredit from Creditswhere SNO=SNOselect AVG_GRADE=avg(SC.GRADE) from Students sinner join SC on sc.SNO=s.SNOwhere s.SNO=SNO and SC.GRADE>3group by s.SNO4) CAL_GPA (SNO， GPA output)要求：根據(jù)SNO參數(shù)，輸出并顯示該學(xué)生的GPA值。計(jì)算方法如下:GRADE(G)GRADEPOINT(GP)G>=85485>G>=75375>G>=60260>G1GPA= (EGP*CREDIT)/ CREDIT)答： create procedure CAL_GPASNO varchar(100), GPA decimal outputasdeclare SUM_CREDIT int,AVG_GRADE int,All_CREDIT intselect SUM_CREDIT=SumCredit fro