资源描述：
实验七数据安全性实验实验内容：1.建立教学数据库teachingcreatedatabaseteachingonprimary(name=teaching1,filename='g:teaching1t.mdf',size=10mb,filegrowth=10%)logon(name='teaching1_log',filename='g:teaching1_log.ldf',size=1mb,maxsize=5mb,filegrowth=1mb)2.建立教学数据库的三个基本表：S(S#,SNAME,AGE,SEX)学生（学号，姓名，年龄，性别）SC(S#,C#,GRADE)选修（|学号，课程号，成绩）C(C#,CNAME,TEACHER)课程（课程号，课程名，任课教师）useteachingCREATETABLEC(C#CHAR(10),CNAMECHAR(10),TAECHERCHAR(10),constraintC#_fkforeignkey(C#)referencesSC(C#)
1S表,SC表略.3.输入数据useteachingINSERTINTOS(S#,SNAME,AGE,SEX)VALUES('S1','WANG',20,'M');INSERTINTOS(S#,SNAME,AGE,SEX)VALUES('S2','LIU',19,'M');IN|SERTINTOS(S#,SNAME,AGE,SEX)VALUES('S3','CHEN',22,'M');INSERTINTOS(S#,SNAME,AGE,SEX)VALUES('S4','WU',19,'M');INSERTINTOS(S#,SNAME,AGE,SEX)VALUES('S5','LOU',21,'F');INSERTINTOS(S#,SNAME,AGE,SEX)VALUES('S8','DONG',18,'F');
2INSERTINTOSC(S#,C#,GRADE)VALUES('S1','C1',80);INSERTINTOSC(S#,C#,GRADE)VAL|UES('S1','C2',70);INSERTINTOSC(S#,C#,GRADE)VALUES('S1','C3',85);INSERTINTOSC(S#,C#,GRADE)VALUES('S1','C4',90);INSERTINTOSC(S#,C#,GRADE)VALUES('S1','C5',70);INSERTINTOSC(S#,C#,GRADE)VALUES('S2','C1',85);INSERTINTOSC(S#,C#)VALUES('S2','C2');INSERTINTOSC(S#,C#)VALUES('S2','C3');INSERTINTOSC(S#,C#)|VALUES('S2','C4');INSERTINTOSC(S#,C#)VALUES('S2','C5');INSERTINTOSC(S#,C#,GRADE)VALUES('S3','C1',90);INSERTINTOSC(S#,C#,GRADE)VALUES('S3','C2',85);INSERTINTOSC(S#,C#,GRADE)VALUES('S3','C3',95);INSERTINTOSC(S#,C#)VALUES('S3','C4');INSERTINTOSC(S#,C#)VALUES('S3','C5');INSERTINTOSC(S#,C#,GRADE)VAL|UES('S4','C1',70);INSERTINTOSC(S#,C#,GRADE)VALUES('S4','C2',60);INSERTINTOSC(S#,C#,GRADE)VALUES('S4','C3',80);INSERTINTOSC(S#,C#)VALUES('S4','C4');INSERTINTOSC(S#,C#,GRADE)VALUES('S4','C5',65);INSERTINTOSC(S#,C#,GRADE)VALUES('S5','C1',90);INSERTINTOSC(S#,C#)VALUES('S5','C2');INSERTINTOSC(S#,C#,|GRADE)VALUES('S5','C3',90);INSERTINTOSC(S#,C#)VALUES('S5','C4');INSERTINTOSC(S#,C#)VALUES('S5','C5');
3useteachingINSERTINTOC(C#,CNAME,TAECHER)VALUES('C2','MATHS','MA');INSERTINTOC(C#,CNAME,TAECHER)VALUES('C4','PHYSICS','SHI');INSERTINTOC(C#,CNAME,TAECHER)VALUES('C3','CHEMISTRY','ZHOU');INSERTI|NTOC(C#,CNAME,TAECHER)VALUES('C1','DB','LI');INSERTINTOC(C#,CNAME,TAECHER)VALUES('C5','OS','WEN');
44.数据修改、删除1)把C2课程的非空成绩提高10％。USETEACHINGupdateSCsetGRADE=GRADE*1.1WHEREC#='C2';2)在SC表中删除课程名为PHYSICS的成绩的元组。USETEACHINGDELETEFROMSCWHEREC#IN(SELECTC#FROMCWHERECNAME='PHYSICS');
53)在S和SC表中删除学号为S8的所有数据|。USETEACHINGdeleteFROMSWHERES#='S8';实现的级联删除5.计算每个学生有成绩的课程门数、平均成绩。useteaching
6SELECTS#,COUNT(C#)'SUM',AVG(GRADE)'AVG'FROMSCWHEREGRADE!=0GROU
