数据库实验前几个解答.doc

上机实验一数据库的管理-创建数据库CREATE DATABASE teachingDataAON PRIMARY(name=teachingDataA,filename=e:teachingDataA.mdf)LOG ON(name=teachingDataA_log,filename=e:teachingDataA_log.ldf)-删除数据库-DROP DATABASE teachingDataA实验二 数据表的管理(一) USE teachingDataA-自定义数据类型myIDCREATE TYPE myID FROM char(6) NOT NULL-自定义数据类型myName1CREATE TYPE myName1 FROM nvarchar(10) NOT NULL-自定义数据类型myName2CREATE TYPE myName2 FROM char(20) NOT NULL-自定义数据类型myDeptCREATE TYPE myDept FROM char(50)-创建数据表TInfoCREATE TABLE TInfo(TID myID PRIMARY KEY,TName myName1,Sex char(2) DEFAULT 男 CHECK(sex=男 or sex=女),Dept myDept,Birthday smalldatetime,Title varchar(10),homeTel nchar(8),MPhone nchar(11)-或/*CREATE TABLE TInfo(TID myID PRIMARY KEY,TName myName1,Sex char(2) DEFAULT 男,Dept myDept,Birthday smalldatetime,Title varchar(10),homeTel nchar(8),MPhone nchar(11),CONSTRAINT CK_SEX CHECK(sex=男 or sex=女)*/-创建学生表SInfoCREATE TABLE SInfo(SID myID PRIMARY KEY,SName myName1,Sex char(2) DEFAULT 男 CHECK(sex=男 or sex=女),Birthday smalldatetime,Dept myDept,major varchar(30),Class nchar(18),IDCardNo char(18)-创建课程表CInfoCREATE TABLE CInfo(CID myID PRIMARY KEY,CName myName2,CCredit tinyint,CProperty varchar(10)-创建选修表ScoreInfoCREATE TABLE ScoreInfo(CID myID FOREIGN KEY REFERENCES CInfo(CID),SID myID FOREIGN KEY REFERENCES SInfo(SID),TID myID,Score numeric(3,1),Schyear char(9),term char(1),FOREIGN KEY(TID) REFERENCES TInfo(TID),PRIMARY KEY(CID,SID)-修改表SInfo表的表结构，设置IDCardNo值为唯一的。ALTER TABLE SInfoADD CONSTRAINT UQ_Card UNIQUE(IDCardNo)-修改表ScoreInfo的表结构，设置term值只能为1或2，默认值为1。ALTER TABLE ScoreInfoADD CONSTRAINT CK_term CHECK(term=1 OR term=2)ALTER TABLE ScoreInfoADD CONSTRAINT DF_term DEFAULT(1) FOR term-在表SInfo中添加一字段EntrData(即入学日期)，要求该输入范围在1900-1-1至2079-12-31。之间ALTER TABLE SInfoADD EntrDate SmallDateTime-修改表TInfo的表结构，要求删除其中的Birthday字段。ALTER TABLE TInfoDROP COLUMN Birthday-修改表TInfo表的表结构，其中的字段homeTel只允许为数字，且第1位数不能为0。ALTER TABLE TInfoADD CONSTRAINT CK_ThomeTel CHECK(homeTel like 1-90-90-90-90-90-90-90-9)-修改表TInfo的表结构，增加Birthday字段。ALTER TABLE TInfoADD Birthday datetime-向教师表插入数据INSERT INTO TInfo(TID, TName, sex, Dept, Birthday, Title, homeTel)VALUES(101001,施华,男,1962-5-1,教授,43562634)-利用T-SQL命令语句在表TInfo中输入数据，当TID大于101000且小于101999时，其所在部门Dept为信息管理，当TID大于102000且小于102999时，其所在部门Dept为电子商务。UPDATE TInfoSET Dept=信息管理 where TID=101001 and TID=102001 and TID=70-或SELECT Sname, scoreFROM StuInfo,ScoreInfowhere StuInfo.SID=ScoreInfo.SID ANDScore=70ANDCID = (SELECT CID FROM CourseInfoWHERE CName = 数据库原理与应用)-(五)嵌套查询-查询与“周志成”同学在同一个班同一个年级的学生姓名。SELECT SnameFROM StuInfowhere class=(SELECT class FROM StuInfoWHERE Sname = 周志成)AND grade=(SELECT grade FROM StuInfoWHERE Sname = 周志成)实验五 视图 -PPT中例题-1、建立物电学院选修了1101号课程的学生视图S_1。CREATE VIEW S_1(SID, Sname, Score) AS SELECT StuInfo.SID, StuInfo.Sname, ScoreInfo.score FROM StuInfo,ScoreInfo WHERE StuInfo.Dept= 物电学院 AND StuInfo.SID = ScoreInfo.SID AND ScoreInfo.CID= 1101;-2、创建学生成绩stu_scoreA视图，要求其中包括字段Sid, Sname, Cname, score.CREATE VIEW S_2(SID, Sname, CName, Score) AS SELECT StuInfo.SID, StuInfo.Sname, CourseInfo.CName,ScoreInfo.score FROM StuInfo,ScoreInfo,CourseInfo WHERE ScoreInfo.CID = CourseInfo.CID AND StuInfo.SID = ScoreInfo.SID;-3、建立计算机学院学生的学号、姓名和专业的视图student_1。CREATE VIEW Student_1(学号,姓名,专业) AS SELECT SID, Sname, Major FROM StuInfo WHERE Dept= 计算机学院-4、建立计物电学院的学号、姓名和专业的视图Student_2，并要求通过该视图进行的更新操作只涉及物电学院学生。CREATE VIEW Student_2 AS SELECT SID,Sname, Major FROM StuInfo WHERE Dept= 物电学院 WITH CHECK OPTION;-5、定义一个反映学生年龄的视图StuAge。CREATE VIEW StuAge(SID, Sname, Sage) AS SELECT SID, Sname, year(getdate()-year(birthday) FROM StuInfo-6、将学生的学号及他的平均成绩定义为一个视图ScoreAvg 。CREATE VIEW ScoreAvg(学号,平均成绩) AS SELECT SID, AVG(Score) FROM ScoreInfo GROUP BY SID; -7、建立物电学院选修了1101号课程的学生成绩视图StuCourse1 。CREATE VIEW StuCourse1(学号, 姓名, 成绩) AS SELECT StuInfo.SID, Sname, Score FROM StuInfo,ScoreInfo WHERE Dept= 物电学院 AND StuInfo.SID=ScoreInfo.SID AND ScoreInfo.CID= 1101;-8、建立物电学院选修了1101号课程且成绩在60分以上的学生的视图。 CREATE VIEW Stu_Pass1 AS SELECT 学号, 姓名, 成绩 FROM StuCourse1 WHERE 成绩=60-9、使用T-SQL语句来查看teachindData数据库的student_1视图的定义。USE teachingData;EXEC sp_helptext student_1;-10、从视图Student_1查询计算机学院学生的学号、姓名和专业的信息。Use teachingData;SELECT * FROM Student_1;-11、查询视图stu_scoreA,要求按课程名称进行排序。select *from stu_scoreBorder by Cname-12、修改视图“Student_1”为查看计算机学院学生的学号、姓名、性别和专业。ALTER VIEW Student_1 AS SELECT SID, sname, sex, Major FROM StuInfo WHERE Dept= 计算机学院;-13、将视图student_1更名为“student_computer”。 Use teachingDataGoEXEC sp_rename student_1, student_computer-14、利用视图StuCourse1为物电学院中每位学生的数据库原理与应用成绩减少5分。Update StuCourse1Set 成绩=成绩-5-15、从teachingData数据库中删除视图student_2和sc_1。 USE teachingData GoDrop View student_2, sc_1-其他-1、在teachingData数据库中建立视图score_top，要求在该视图中显示各门学科的CID和score_max（即学科的最高分）。CREATE VIEW score_top AS SELECT CID, MAX(Score) 最高分 FROM ScoreInfoGROUP BY CID-2、利用teachingData数据库中的相关的数据表和视图score_top新建一视图top_stu，要求在该视图中显示各门学科的学科名cname，取得最高分的学生名sname和成绩score。CREATE VIEW top_stu AS SELECT CName, SName, Score FROM ScoreInfo,StuInfo,CourseInfo,score_topWHERE ScoreInfo.CID = score_top.CID ANDScoreInfo.score = score_top.最高分 ANDScoreInfo.SID=StuInfo.SID ANDScoreInfo.CID=CourseInfo.CID;-3、要求用T-SQL语句新建一视图stu_comp，要求该视图中只包括物电学院学生的学号sid、姓名sname、年龄sage和班级class视图。create view stu_compasselect sid,sname,year(getdate()-year(birthday) as sage,classfrom stuinfowhere dept=物电学院-4、试用T-SQL语句修改视图stu_comp，要求该视图中只包括2009级物电学院学生的学号sid、姓名sname、年龄sage、班级class。ALTER view stu_compasselect sid,sname,year(getdate()-year