1、按照要求編寫相應代碼，執行結果后，并刪除對象：（以學號+題號做文件名提交：990101_1）1、 運用存儲過程創建STUDENT、GRADE、COURSE表（要求實現完整性）。按照要求編寫相應代碼，執行結果后，并刪除對象：（以學號+題號做文件名提交：990101_1）2、 運用存儲過程創建STUDENT、DEPARTMENT、DORM表（要求實現完整性）。按照要求編寫相應代碼，執行結果后，并刪除對象：（以學號+題號做文件名提交：990101_1）3、 運用存儲過程對STUDENT、DEPARTMENT、DORM表插入一條符合完整性的記錄。(內容自己定義)按照要求編寫相應代碼，執行結果后，并刪除

2、對象：（以學號+題號做文件名提交：990101_1）4、 編寫帶參的存儲過程：實現刪除01課程數據庫原理的相關信息。按照要求編寫相應代碼，執行結果后，并刪除對象：（以學號+題號做文件名提交：990101_1）5、 編寫帶參的存儲過程：實現刪除990101學生原野的相關信息。按照要求編寫相應代碼，執行結果后，并刪除對象：（以學號+題號做文件名提交：990101_1）6、 編寫帶參的存儲過程：實現宿舍號從2101修改為3101。按照要求編寫相應代碼，執行結果后，并刪除對象：（以學號+題號做文件名提交：990101_1）7、 編寫帶參的存儲過程：實現查詢宿舍號“2505”的相關信息。按照要求編寫相應

3、代碼，執行結果后，并刪除對象：（以學號+題號做文件名提交：990101_1）8、 運用觸發器對GRADE實現插入記錄“980101，08，99”（相關信息自己定義）。按照要求編寫相應代碼，執行結果后，并刪除對象：（以學號+題號做文件名提交：990101_1）9、 運用觸發器對STUDENT表中的SEX實現數據范圍檢查，若出錯，提示信息。按照要求編寫相應代碼，執行結果后，并刪除對象：（以學號+題號做文件名提交：990101_1）10、運用觸發器對STUDENT表的操作只能在工作時間：9-15，每周一到五，實現數據安全性控制。答案：1、 用存儲過程創建STUDENT、GRADE、COURSE表（要

4、求實現完整性）。 use student_datagoif exists (select name from sysobjectswhere name='cun' and type='P')drop procedure cungocreate procedure cunasbeginif not exists(select name from sysobjectswhere name='student' and type='U')begincreate table student(sno char(6)NOT null uniqu

5、e,sname char(8) not null,sex char(2),sage int,dno char(4),dormno char(5),constraint FK_DEP FOREIGN KEY (dno) references department(dno), constraint PK_student primary key(sno)insert into studentvalues('990101','原野','男',21,'1','2101');endif not exists(select na

6、me from sysobjectswhere name='grade' and type='U')begincreate table grade(sno char(6),cno char(2),score intconstraint pk_grade primary key (sno,cno);insert into gradevalues ('990101','01',85);endif not exists(select name from sysobjectswhere name='course' and

7、type='U')begincreate table course(cno char(2) NOT null unique,cname char(20) not null,cpno char(2),credit int,teacher char(8)constraint FK_CP foreign key (cpno) references dorm(cpno),constraint PK_COr primary key(cno)insert into coursevalues('01','數據庫原理','05',4,'王

8、凱');endendgocunselect *from student2、 運用存儲過程創建STUDENT、DEPARTMENT、DORM表（要求實現完整性）。3、 運用存儲過程對STUDENT、DEPARTMENT、DORM表插入一條符合完整性的記錄。(內容自己定義)use studentgoif exists (select name from sysobjectswhere name='cun' and type='P')drop procedure cungocreate procedure cunssno char(6),ssname char

9、(8),ssex char(2),ssage int,ddno char(4),ddorm char(5) ,dddno char(4),ddname char(8),hhead char(20),dddorno char(5),dddtel char(7)asbeginif not exists(select sno from student where sno=ssno)if exists (select dno from department where dno=ddno)If exists(select dormno from dorm where dormno=ddorm)inser

10、t into student (sno,sname,sex,sage,dno,dormno)values(ssno,ssname,ssex,ssage,ddno,ddorm)if not exists(select dno from department where dno=dddno)if not exists(select dname from department where dname=ddname)insert into departmentvalues(dddno,ddname,hhead)if not exists(select dormno from dorm where do

11、rmno=dddorno)if not exists(select tele from dorm where tele=dddtel)insert into dormvalues(dddorno,dddtel)endgouse studentgocun '990109','達到','男',19,'4','2404','5','地質','哈哈','6504','8306753'select *from studentselect *fro

12、m departmentselect *from studentselect *from dorm4、 編寫帶參的存儲過程：實現刪除01課程數據庫原理的相關信息。 if exists(select name from sysobjectswhere name='del_cname' and type='p')drop procedure del_cnamegocreate procedure del_cnamecname char(20)asbegindelete grade where cno =(select cno from course where cn

13、ame='數據庫原理') delete course where cname=cnameendgoselect * from courseselect * from gradeuse student_datagodel_cname '數據庫原理'godrop procedure del_cname5、 編寫帶參的存儲過程：實現刪除990101學生原野的相關信息。 use student_data /*打開數據庫*/goif exists(select name from sysobjectswhere name ='del_S'and type

14、='p')drop procedure del_Sgocreate procedure del_Sstu char(6)asbegindelete grade where sno=studelete student where sno=stuendgoselect * from gradeselect * from studentdel_S '990101'6、 編寫帶參的存儲過程：實現宿舍號從2101修改為3101。 use student_dataif exists (select name from sysobjectswhere name = '

15、adjust_roo' and type = 'P')drop procedure adjust_roogocreate procedure adjust_roodormnoo char(5) ,dormnoo1 char(5)asbeginupdate student set dormno=dormnoo1where dormno=dormnoodelete from dormwhere dormno=dormnooendgoadjust_roo '2101', '3101'goselect * from student7、 編寫帶參的

16、存儲過程：實現查詢宿舍號“2505”的相關信息。use studentgoif exists(select name from sysobjectswhere name='search_dorm' and type='p')drop procedure search_dormgocreate procedure search_dormdormno char(5)asbeginselect dorm.*, sno,sname from dorm,studentwhere student.dormno=dorm. dormnoand dormno=dormnoend

17、gouse studentexecsearch_dorm '2505'godrop procedure search_dorm8、 運用觸發器對GRADE實現插入記錄“980101，08，99”（相關信息自己定義）。 use student_dataif exists(select name from sysobjectswhere name ='SC'and type ='tr')drop trigger SCgocreate trigger SC on gradefor insertasdeclare Sno char(6)declare C

18、no char(2)declare msg1 char(100)declare msg2 char(100)declare msg3 char(100)declare Score intselect Sno= Sno from insertedselect msg1='沒有學生'if not exists(select * from studentwhere sno =Sno )beginprint msg1rollback transactionreturnendselect Cno=Cno from insertedselect msg2='沒有課程'if

19、not exists(select * from coursewhere cno =Cno )beginprint msg2rollback transactionreturnendselect Score=score from insertedselect msg3='成績不對'if (Score is not NULL and (Score<0 or Score>100) beginprint msg3rollback transactionendgoinsert into grade(sno,cno,score)values('990101',

20、'01',NULL)delete grade where sno='990101'9、 運用觸發器對STUDENT表中的SEX實現數據范圍檢查，若出錯，提示信息。 use student_dataif exists (select name from sysobjectswhere name='tr_ssex' and type='TR')drop trigger tr_ssexgocreate trigger tr_ssex on studentbefore update,insertasdeclare msg varchar(

21、100)declare vssex char(2)select msg='數據有誤,請檢查后,重新輸入!'select vssex=sex from insertedif (vssex<>'男'and vssex<>'女')beginprint msgrollback transactionendreturngoselect * from studentupdate student set sex='王'where sno='990101'update student set sex='女' where sno='990101'insert into student(sno,sname,sex)values('