《SQLServer实验指导》课后作业.docx

1、SQLServer实验指导课后作业SQL Server2000 实验指导课程作业课程：SQL Server 2000实验班级：信息管理与信息系统06-2班姓名：陈龙学号：2006133305目录创建数据库jxsk： - 1 -创建jxsk中的表： - 1 -插入jxsk库中各表值： - 1 -创建数据库jiaoxuedb： - 3 -创建jiaoxuedb中的表： - 3 -插入jiaoxuedb库中各表值： - 3 -实验4：习题 - 5 -实验5：习题 - 7 -实验6：习题 - 7 -实验7：习题 - 8 -实验8：习题 - 12 -实验9：习题 - 17 -实验10：习题 - 20 -

2、实验11：习题 - 23 -实验12：习题 - 26 -实验13：习题 - 38 -创建数据库jxsk：CREATE DATABASE jxskON(NAME=jxsk_dat,FILENAME=d:jxsk_dat.mdf)LOG ON(NAME=jxsk_log,FILENAME=d:jxsk_log.ldf)GO创建jxsk中的表：USE jxskCREATE TABLE S( SNO CHAR(2) NOT NULL, SN CHAR(8) NOT NULL, SEX CHAR(2) NULL, AGE TINYINT NULL, DEPT CHAR(10) NULL)CREATE T

3、ABLE T( TNO CHAR(2) NOT NULL, TN CHAR(8) NOT NULL, SEX CHAR(2) NULL, AGE TINYINT NULL, PROF CHAR(10) NULL, SAL SMALLINT NULL, COMM SMALLINT NULL, DEPT CHAR(10) NULL)CREATE TABLE C( CNO CHAR(2) NOT NULL, CN CHAR(10) NOT NULL, CT TINYINT NULL, ID_TC TINYINT NULL)CREATE TABLE SC( SNO CHAR(2) NOT NULL,

4、CNO CHAR(2) NOT NULL, SCORE TINYINT NULL)CREATE TABLE TC( TNO CHAR(2) NOT NULL, CNO CHAR(2) NOT NULL)插入jxsk库中各表值：USE jxskINSERT INTO S VALUES(S2,钱尔,男,18,信息)INSERT INTO S VALUES(S3,孙姗,女,20,信息)INSERT INTO S VALUES(S4,李思,男,21,自动化)INSERT INTO S VALUES(S5,周武,男,19,计算机)INSERT INTO S VALUES(S6,吴丽,女,20,自动化)I

5、NSERT INTO S VALUES(S7,李奇,男,18,外语)INSERT INTO S VALUES(S8,古明,男,NULL,计算机)INSERT INTO S VALUES(S9,赵亦,女,17,计算机)INSERT INTO T VALUES(T5,张兰,女,39,副教授,1300,2000,信息)INSERT INTO T VALUES(T4,张雪,女,51,教授,1600,3000,自动化)INSERT INTO T VALUES(T3,刘伟,男,30,讲师,900,1200,计算机)INSERT INTO T VALUES(T2,王平,女,28,讲师,800,1200,信息

6、)INSERT INTO T VALUES(T1,李力,男,47,教授,1500,3000,计算机)INSERT INTO C VALUES(C1,程序设计,60,1)INSERT INTO C VALUES(C2,微机原理,80,2)INSERT INTO C VALUES(C3,数字逻辑,60,3)INSERT INTO C VALUES(C4,数据结构,80,4)INSERT INTO C VALUES(C5,数据库,60,5)INSERT INTO C VALUES(C6,编译原理,60,6)INSERT INTO C VALUES(C7,操作系统,60,7)INSERT INTO S

7、C VALUES(S2,C5,57)INSERT INTO SC VALUES(S2,C6,80)INSERT INTO SC VALUES(S2,C7,NULL)INSERT INTO SC VALUES(S3,C1,75)INSERT INTO SC VALUES(S3,C2,70)INSERT INTO SC VALUES(S3,C4,85)INSERT INTO SC VALUES(S4,C1,93)INSERT INTO SC VALUES(S4,C2,85)INSERT INTO SC VALUES(S4,C3,83)INSERT INTO SC VALUES(S5,C2,89)I

8、NSERT INTO SC VALUES(S9,C1,90)INSERT INTO SC VALUES(S9,C2,85)INSERT INTO TC VALUES(T2,C5)INSERT INTO TC VALUES(T3,C1)INSERT INTO TC VALUES(T3,C5)INSERT INTO TC VALUES(T4,C2)INSERT INTO TC VALUES(T4,C3)INSERT INTO TC VALUES(T5,C5)INSERT INTO TC VALUES(T5,C7)INSERT INTO TC VALUES(T1,C1)INSERT INTO TC

9、VALUES(T1,C4)创建数据库jiaoxuedb：CREATE DATABASE jiaoxuedbON(NAME=jiaoxuedb_dat,FILENAME=d:jiaoxuedb_dat.mdf)LOG ON(NAME=jiaoxuedb_log,FILENAME=d:jiaoxuedb_log.ldf)GO创建jiaoxuedb中的表：USE jiaoxuedbCREATE TABLE Student( Sno CHAR(10) NOT NULL, Sname CHAR(10) NOT NULL, Sex CHAR(2) NULL, Age TINYINT NULL, Dept

10、CHAR(10) NULL)CREATE TABLE Teacher( Tno CHAR(10) NOT NULL, Tname CHAR(10) NOT NULL, Sex CHAR(2) NULL, Age TINYINT NULL, Prof CHAR(10) NULL, Sal SMALLINT NULL, Comm SMALLINT NULL, Dept CHAR(10) NULL)CREATE TABLE Course( Cno CHAR(10) NOT NULL, Cname CHAR(10) NOT NULL, Chour TINYINT NULL, Ccredit TINYI

11、NT NULL)CREATE TABLE SC( Sno CHAR(10) NOT NULL, Cno CHAR(10) NOT NULL, SCORE TINYINT NULL)CREATE TABLE TC( Tno CHAR(10) NOT NULL, Cno CHAR(10) NOT NULL)插入jiaoxuedb库中各表值：USE jiaoxuedb/*Insert values to Student Table*/INSERT INTO Student VALUES(001101,宋大方,男,19,计算机)INSERT INTO Student VALUES(002102,李王,

12、男,20,信息)INSERT INTO Student VALUES(991101,张彬,男,18,计算机)INSERT INTO Student VALUES(991102,王蕾,女,19,计算机)INSERT INTO Student VALUES(991103,张建国,男,18,计算机)INSERT INTO Student VALUES(991104,李平方,男,18,计算机)INSERT INTO Student VALUES(991201,陈东辉,男,19,计算机)INSERT INTO Student VALUES(991202,葛鹏,男,21,计算机)INSERT INTO S

13、tudent VALUES(991203,潘桃芝,女,19,计算机)INSERT INTO Student VALUES(991204,姚一峰,男,18,计算机)INSERT INTO Student VALUES(001102,许辉,女,22,计算机)INSERT INTO Student VALUES(001201,王一山,男,20,计算机)INSERT INTO Student VALUES(001202,牛莉,女,19,计算机)INSERT INTO Student VALUES(002101,李丽丽,女,19,信息)/*Insert values to Teacher Table*/

14、INSERT INTO Teacher VALUES(000006,许红霞,女,39,讲师,1100,1200,计算机)INSERT INTO Teacher VALUES(000007,许永军,男,57,教授,2000,3000,计算机)INSERT INTO Teacher VALUES(000008,李贵青,女,65,教授,2000,3000,计算机)INSERT INTO Teacher VALUES(000009,王一凡,女,43,讲师,1200,1200,计算机)INSERT INTO Teacher VALUES(000010,田峰,男,33,助教,500,800,信息)INSE

15、RT INTO Teacher VALUES(000001,李英,女,39,副教授,1500,2000,信息)INSERT INTO Teacher VALUES(000002,张雪,女,51,教授,1900,3000,信息)INSERT INTO Teacher VALUES(000003,张朋,男,30,讲师,1000,1200,计算机)INSERT INTO Teacher VALUES(000004,王平,女,28,讲师,850,1200,信息)INSERT INTO Teacher VALUES(000005,李力,男,47,教授,1800,3000,计算机)/*Insert val

16、ues to Course Table*/INSERT INTO Course VALUEs(01001,计算机基础,60,3)INSERT INTO Course VALUEs(01002,程序设计,80,5)INSERT INTO Course VALUEs(02003,数据结构,60,6)INSERT INTO Course VALUEs(02001,数据库,80,6)INSERT INTO Course VALUEs(02002,计算机网络,60,6)INSERT INTO Course VALUEs(01003,微机原理,60,8)INSERT INTO Course VALUEs(

17、02004,操作系统,60,6)INSERT INTO Course VALUEs(03001,软件工程,60,3)INSERT INTO Course VALUEs(03002,大型数据库,48,2)INSERT INTO Course VALUEs(03003,图像处理,48,2)/*Insert values to SC Table*/INSERT INTO SC VALUES(991101,01001,88)INSERT INTO SC VALUES(991102,01001,93)INSERT INTO SC VALUES(991103,01001,90)INSERT INTO SC

18、 VALUES(991101,01002,90)INSERT INTO SC VALUES(991102,01002,98)INSERT INTO SC VALUES(991103,01002,74)INSERT INTO SC VALUES(991104,01002,85)INSERT INTO SC VALUES(001201,01002,64)INSERT INTO SC VALUES(991104,02001,33)INSERT INTO SC VALUES(991104,01001,35)INSERT INTO SC VALUES(991201,01001,76)/*Insert v

19、alues to TC Table*/INSERT INTO TC VALUES(000001,02001)INSERT INTO TC VALUES(000008,02002)INSERT INTO TC VALUES(000003,02001)INSERT INTO TC VALUES(000011,02003)INSERT INTO TC VALUES(000001,01001)INSERT INTO TC VALUES(000002,01002)INSERT INTO TC VALUES(000002,01003)INSERT INTO TC VALUES(000004,02002)I

20、NSERT INTO TC VALUES(000005,01001)INSERT INTO TC VALUES(000006,01002)INSERT INTO TC VALUES(000003,01003)实验4：习题基于jiaoxuedb实验1、 对个表创建实体完整性；USE jiaoxuedbALTER TABLE Student ADD CONSTRAINT PK_SNO PRIMARY KEY(Sno)ALTER TABLE Teacher ADD CONSTRAINT PK_TNO PRIMARY KEY(Tno)ALTER TABLE Course ADD CONSTRAINT

21、PK_CNO PRIMARY KEY(Cno)ALTER TABLE Student ADD CONSTRAINT UNIQUE_S UNIQUE(Sno)ALTER TABLE Teacher ADD CONSTRAINT UNIQUE_T UNIQUE(Tno)ALTER TABLE Course ADD CONSTRAINT UNIQUE_C UNIQUE(Cno)2、 创建表之间的参照完整性。USE jiaoxuedbALTER TABLE SC WITH NOCHECK ADD CONSTRAINT FK_S_SC FOREIGN KEY(Sno) REFERENCES Studen

22、t(Sno) ON DELETE CASCADEALTER TABLE SC WITH NOCHECK ADD CONSTRAINT FK_C_SC FOREIGN KEY(Cno) REFERENCES Course(Cno) ON DELETE CASCADEALTER TABLE TC WITH NOCHECK ADD CONSTRAINT FK_T_TC FOREIGN KEY(Tno) REFERENCES Teacher(Tno) ON DELETE CASCADEALTER TABLE TC WITH NOCHECK ADD CONSTRAINT FK_C_TC FOREIGN

23、KEY(Cno) REFERENCES Course(Cno) ON DELETE CASCADE3、 用不同的方法创建下面约束。（1） “姓名”字段取唯一值：USE jiaoxuedbALTER TABLE Student ADD CONSTRAINT UNIQUE_SNA UNIQUE(Sname)ALTER TABLE Teacher ADD CONSTRAINT UNIQUE_TNA UNIQUE(Tname)(2) “性别”字段取值为：男或女；默认值：男；USE jiaoxuedbALTER TABLE Teacher WITH CHECK ADD CONSTRAINT CHECK_

24、T_SEX CHECK(SEX=男 OR SEX=女)ALTER TABLE Teacher ADD CONSTRAINT DF_T_SEX DEFAULT 男 FOR SexALTER TABLE Student WITH CHECK ADD CONSTRAINT CHECK_S_SEX CHECK(SEX=男 OR SEX=女)ALTER TABLE Student ADD CONSTRAINT DF_S_SEX DEFAULT 男 FOR Sex（3）“年龄”和“分数”字段取值限定为：0100之间。USE jiaoxuedbALTER TABLE Student WITH CHECK A

25、DD CONSTRAINT CHECK_S_AGE CHECK(AGE0 AND AGE0 AND AGE100)实验5：习题1、 对教师表Teacher中的教师号Tno创建聚族索引，并按降序排列。USE jiaoxuedbCREATE CLUSTERED INDEX IND_TNO ON Teacher(Tno DESC)2、 对学生选课表SC，先按课号Cno升序排列，再按成绩Score降序排列。Cno升序：USE jiaoxuedbCREATE CLUSTERED INDEX IND_SC_CNO ON SC(Cno ASC)Score降序：USE jiaoxuedbCREATE CLUS

26、TERED INDEX IND_SC_SCORE ON SC(Score DESC)3、 对表Student中的学号Sno创建唯一索引，并按升序排列。USE jiaoxuedbCREATE UNIQUE INDEX IND_S_SNO ON Student(Sno ASC)实验6：习题基于jiaoxuedb实验1、创建成绩视图SCORE_VIEW,包含学号SNO,姓名SN，课程名CN，成绩SCORE。USE jiaoxuedbGOCREATE VIEW SCORE_VIEW AS SELECT Student.Sno,Sname,Cname,Score FROM Student,Course,

27、SC WHERE Student.Sno=SC.Sno AND SC.Cno=Course.CnoGO2、创建一个计算机系学生名单视图S_VIEW，包含学号SNO，姓名SN，性别SEX。USE jiaoxuedbGOCREATE VIEW S_VIEW AS SELECT Sno,Sname,Sex FROM Student WHERE Dept=计算机GO4、 通过上面的视图，修改学号为991102，课号为01001的成绩记录。USE jiaoxuedbUPDATE SCORE_VIEW SET Score=100WHERE Sno=991102 AND Cname= (SELECT Cna

28、me FROM Course WHERE Cno=01001)GO5、创建一个视图，计算机系学生的成绩单SCORE_VIEW_CDEPT,版含学号SNO，姓名SN，课程名CN，成绩SCORE。USE jiaoxuedbGOCREATE VIEW SCORE_VIEW_CDEPT AS SELECT Student.Sno,Sname,Cname,Score FROM Student,Course,SC WHERE Student.Dept=计算机 AND Student.Sno=SC.Sno AND SC.Cno=Course.CnoGO6、给视图S_VIEW增加一个年龄AGE字段。USE jiaoxuedbGOALTER VIEW S_VIEW AS SELECT Sno,Sname,Sex,Age FROM Student WHERE Dept=计算机GO7、创建一个教师工资表视图SAL_VIEW，版含字段教师名Tname，性别Sex，职称Prof，工资总和SALSUM，系别Dept。USE jiaoxuedbGOCREATE VIEW SAL_VIEW AS SELECT Tname,Sex,Prof,Sal+Comm AS S