新SQL——SERVER实验练习答案(共30页).doc

精选优质文档-倾情为你奉上SQL-Server实验答案上海师范大学 计算机系 目 录第一部分 企业管理器的使用第二部分 SQL语言试验一 数据库创建目的：1掌握利用SQL语言进行数据库的创建、维护。 2 sp_helpdb 命令要求：1 创建数据库 2 修改数据库 3 删除数据库一 建立school 数据库1 使用查询分析器创建数据库 school Create DataBase school2 使用 SP_helpdb 查询数据库 School 的信息3 使用SQL-Server 的企业管理器查看数据库 school 的信息。 4 记录：1）school 数据库文件所在的文件夹。 2）school 数据库的文件名二 删除School数据库1 使用查询分析器删除数据库 school DROP DATABASE school2 使用SQL-Server 的企业管理器删除数据库 school 。 三 create Database 深入研究1 建立school数据库,要求数据库存储在c:data文件夹下，初始大小为5MB ,增量为 1MB。 CREATE DATABASE school ON( Name = school_dat, Filename = 'c:sqldataschool.mdf',SIZE = 5, FILEGROWTH = 1 )2使用SQL-Server 的企业管理器，将数据库的每次增量改为20%。试验二 创建表目的：1 掌握利用SQL语言创建表的方法。 2 sp_help 命令要求：1 创建表 2 修改表结构 3 删除表一 写出使用 Create Table 语句创建表 student , sc,course 的SQL语句。学生表、课程表、选课表属于数据库 School ，其各自得数据结构如下：学生 Student (Sno,Sname,Ssex,Sage,Sdept)序号列名含义数据类型长度1Sno学号字符型(char)62Sname姓名字符型(varchar)83Ssex性别字符型(char)24Sage年龄整数 (smallint)5sdept系科字符型(varchar)15课程表 course(Cno,Cname,Cpno,Ccredit)序号列名含义数据类型长度1Cno课程号字符型(char)42cname课程名字符型(varchar)203Cpno先修课字符型(char)44Ccredit学分短整数 (tinyint)学生选课 SC(Sno,Cno,Grade)序号列名含义数据类型长度1Sno学号字符型(char)42Cno课程名字符型(char)63Grade成绩小数(decimal)12,1二 把创建表的sql 语句的脚本存储到文件 school.sql 。create table Student ( Sno char(6) , Sname char(10) , Ssex char(2) , Sage smallint , Sdept char(10) ,) create table course( Cno char(4) , Cname char(16) , Cpno char(4) , Ccredit int,)create table SC( Sno char(6), Cno char(4) , Grade int )三 使用 SP_HELP 查看表 student 的表结构 利用企业管理器查看表 sc 的表结构四 利用 sql 语句表结构修改1 在student 表中添加列：家庭地址 address 长度为 60 varchar 型入学日期 inDate 日期型 ALTER TABLE student ADD address varchar(60) ALTER TABLE student ADD inDate datetime完成后用sp_help 查看是否成功。2 将家庭地址 address 长度为 50 ALTER TABLE student ALTER COLUMN varchar(50) 完成后用sp_help 查看是否成功。3 删除 student 表的 inDate 列 ALTER TABLE student DROP COLUMN inDate五 删除表 1 删除表 sc 2 删除表 student 3 删除表 course试验三 创建数据完整性目的：1掌握创建数据完整性约束的命令。 2 掌握完整性约束的修改、删除。要求：1 能建立完整性约束 2 修改完整性约束 3 删除完整性约束一 写出带有完整性约束的 Create Table 命令建立表student、course、sc 。要求：1 Student表的主码：sno student 的约束： l 姓名不可为空，且唯一l 性别 不能为空且取值范围为男，女l 年龄大于16岁l sdept 默认为 JSJ 系 2Course表的主码：cno course 的约束： l Ccredit 取值范围 0 ,1,2,3,4,5 l 课程表的每一行的 Cno 与 cpno 不可相同3 Sc表的主码：sno，cno 。主码名为 PK_SCSc的外码：l 外码：SC 表的sno 参照表 student 的 snol 外码：sc 表的Cno 参照表 course 的 cno 4 把上述创建表的sql 语句的脚本存储到文件 createSchool.sql 。create table Student ( Sno char(6) , Sname char(10) not null unique , Ssex char(2) check (ssex='男' or ssex='女') , Sage smallint check(sage>16) , Sdept char(10) not null default 'JSJ' , primary key (sno) ) create table course( Cno char(4) , Cname char(16) , Cpno char(4) , Ccredit int check (Ccredit >=0 and Ccredit<=5), check( cno<>cpno) , -约束 primary key (cno) )create table SC( Sno char(6), Cno char(4) , Grade int check(grade<=100) , constraint pk_sc primary key (sno,cno), foreign key (sno) references student(sno) , foreign key (cno) references course(cno) , ) 二 使用 SP_HELP 查看表 student 的主码名，约束名，并记录。使用 SP_HELP 查看表 sc 的主码名，外码名，并记录。 三 利用alter table 添加、删除完整性约束1 删除 SC 的主码，sc表的主码名为 pk_sc ALTER TABLE sc DROP pk_sc复习在 Sql-Server 企业管理器中如何完成。2 删除 SC 表参照 course 表的外码。如何知道 SC表参照 course 表的外码的名字。 1) SP_HELP course 找到外码名 ,假设为： fk_sc_cno_02938 2) ALTER TABLE sc DROP fk_sc_cno_029383 添加 SC 表的主码。主码名为 PK_SC ALTER TABLE sc ADD CONSTRAINT pk_sc PRIMARY KEY (sno,cno) 4 添加 SC 表的Cno 的外码 ，参照表Course的Cno.ALTER TABLE sc ADD FOREIGN KEY (cno) REFERENCES course(cno)5加自定义约束：表 SC的成绩只能在 0 100分之间。 ALTER TABLE sc ADD check(grade>=0 and grade<=100)四 使用 Sql-Server 企业管理器完成：1 删除 SC 表参照 course 表的外码。 2 建立 SC 表的Cno 的外码 ，参照表Course的Cno. *使该外码具有级联修改的功能。3 删除表 SC的成绩只能在 0 100分之间的约束.3加自定义约束：表 SC的成绩只能在 0 100分之间。五 使用 select * from student 查看信息 select * from course 查看信息select * from sc 查看信息 试验四 数据完整性试验目的：1理解实体完整性、参照完整性、用户自定义完整性的作用 2 特别掌握外码的作用。要求：记录试验中遇到的问题，并写出原因。实验前需要利用试验三完成的脚本文件 createSchool.sql，重新建立数据库 school 。一 实体完整性1 student 表数据输入 学号姓名性别年龄系科3001赵达男20SX3002杨丽女21JSJ3001李寅女21SXl 输入上述数据，记录出现的问题，说明原因。 第三行不能输入。 Student 的主码为 sno , 因此 sno 列的值不能重复。 l select * from student 查看你输入了几行数据。2 course 表数据的输入CnoCnameCpnoCcredit1081电子商务43 SC 表数据的输入SnoCnoGrade30011081903001108179输入上述数据，记录出现的问题，说明原因。二 用户自定义完整性约束表student 有用户自定义约束：性别 不能为空且取值范围为男，女年龄大于16岁表course 的自定义约束：Ccredit 取值范围 0 ,1,2,3,4,5 课程表的每一行的 Cno 与 cpno 不可相同1 student 表数据输入 学号姓名性别年龄系科3005赵达男14SX3006杨丽南21JSJl 输入上述数据，记录出现的问题，说明原因。3005 学生不能输入，年龄问题，有约束 sage>163006学生不能输入，性别不对。l select * from student 查看你输入了那些数据。2 course 表数据的输入CnoCnameCpnoCcredit1085C+91086语文10863l 输入上述数据，记录出现的问题，说明原因。1086 不能输入，因为有约束 check(cno<>cpno) l select * from student 查看你输入了那些数据。3 SC 表数据的输入SnoCnoGrade30021081128l 输入上述数据，记录出现的问题，说明原因。3002 这条数据不能输入，因为 grade不能大于 100分l select * from student 查看你输入了那些数据。三 参照完整性约束l 掌握表之间建立外码后，对被参照表的如下操作会有何影响：修改主码、插入新行、删除新行？l 对参照表添加新行、删除行、修改外码值有何影响？l 掌握级联修改、级联删除的概念。注意：表SC的 Sno是外码，参照student的sno。表SC的 Cno是外码，参照course的cno。1 输入实验前的数据学生表 StudentSnoSnameSsexSageSdept4001赵尹男20SX4002杨开女20JSJ课程表 course CnoCnameCpnoCcredit1088Java51089数学3学生选课 SC SnoCnoGrade400110889040021088862 试验过程 1) 在SC表中添加新行：SnoCnoGrade4001106676 记录试验结果.,写出出现此结果的原因. 不能添加，因为在 cno是外码，参照course的 cno , 但在course 中没有 1066课程。2) 在student表中添加新行 SnoSnameSsexSageSdept4003赵辉男21SX 记录试验结果.,写出出现此结果的原因.可以输入3) 删除student 表的 4001 ，4002学生 记录试验结果.,写出出现此结果的原因. 两个学生不能被删除，因为sc的外码 sno 参照student的sno, sc中已经有4001，4002学生的数据，因此不能删除。思考：l 删除SC表的记录有限制吗？没有 l 采取什么技术能使不能成功执行的命令变得可以执行，且使数据库保持数据完整性。 级联删除4) 把 student 表的学号 4003 改为 4018 , 4001改为4021。 记录试验结果.,写出出现此结果的原因.4003 可以改为 4018, 4001不能改为 4021 因为sc的外码 sno 参照student的sno, sc中已经有4001的数据，但没有 4003的选课数据。 思考：采取什么技术能使本题不能执行的命令可以执行，且使数据库保持数据完整性。 级联修改5) 把sc表中的如下记录的学号从4001改为4011。SnoCnoGrade4001108890 记录试验结果.,写出出现此结果的原因.不能修改，因为sc的外码 sno 参照student的sno, 4011在 student中不存在。如不成功，则可以采取什么方法来实现此要求。 需要在student表中添加 4011学生。l 如不成功，那么把4001修改为4003，能成功吗？能成功！思考: 参照完整性规则中,外码可以为空, 但SC表中的外码可以为空吗?为什么?举一个外码可以为空的例子。不可以，因为 sc表的主码为 sno+cno, 即sno,cno为主属性，所以不能为空。试验五 索引目的：掌握索引的建立、删除的方法。一 创建索引1 建 student 的索引为姓名建立索引，索引名：Ix_student_sname为系科建立索引，索引名：Ix_student_sdeptcreate index ix_student_sname ON student(sname)create index ix_student_sdept ON student(sdept)2 SC 的索引为课程号建立索引： ix_sc_cno create index ix_sc_cno ON sc(cno)3 Course 的索引为课程名建立唯一性索引 ：Ix_course_cname create unique index ix_course_cname ON course( cname)4 如何 SP_HELP 查看索引刚才建立的索引？ 如何在企业管理器中查看索引？ 二 删除索引 course 表的索引 IX_course_cname DROP INDEX course.ix_course_cname三 思考：如何把索引 IX_student_sname 修改为唯一性索引？ 可以使用企业管理器 或先删除索引，再重新建立。 *四 思考建立索引的目的1 输入下列存储过程，该程序生成大量数据供测试：create procedure usp_makedata asdeclare nCnt int , sNo varchar(6) , sname varchar(8)set nCnt =12000 -计数器 while nCnt<begin set nCnt = nCnt + 1 set sNo = convert(varchar(6) ,nCnt) set sName = '张'+sno insert into student (sno,sname,ssex,sage) values ( sno,sname,'男',20)endreturn 2 exec usp_makedata -生成测试数据 3 输入下述测试程序：create procedure usp_test as declare nCount int ,data intset nCount=0while nCount<100begin select data=count(*) from student where sname <'张3800' or sname>'张8800' set nCount =nCount + 1end 4 测试1）建立姓名的索引，查看运行时间(8秒). create index ix_student_sname on student(sname) -建立索引 exec usp_test2) 删除姓名索引，查看运行时间（2分11秒），比较与1）的时间长短。drop index student.ix_student_sname -删除索引 exec usp_test试验六 更新数据目的：掌握insert,update ,delete 语句的使用。一 insert 1 写出把下述学生的信息添加到student表中的命令。 学号姓名性别年龄系科4001赵茵男20SX4002杨华女21Insert into student (sno,sname,ssex,sage,sdept) values (4001,赵茵,男,20,SX)Insert into student (sno,sname,ssex,sage) values (4002,杨华,女,21)2 批量插入数据1） 建立一个新表 sc_name ，有属性 sno , sname , ssex , cno , grade 。 CREATE TABLE sc_name (Sno char(6) , Sname varchar(20),Ssex char(2) ,cno char(4) ,grade int )2） 把 SX 系学生的sno,sname,ssex, cno , grade 插入到表 sc_name 中。 Insert into sc_name (sno,sname,ssex,cno , grade) select student.sno,sname , ssex,cno,grade from student,sc where student.sno=sc.sno and sdept=SX 3） 察看 sc_name 表的数据 select * from sc_name二 Update1 修改 0001 学生的系科为: JSJUpdate student set sdept=JSJ where sno=00012 把陈小明的年龄加1岁，性别改为女。Update student set sage=sage+1 , ssex=女 where sname= 陈小明3 修改李文庆的1001课程的成绩为 93 分update sc set grade=93 where cno=1001 and sno in ( select sno from student where sname= 李文庆)4 把“数据库原理”课的成绩减去1分 update sc set grade=grade - 1 where cno in ( select cno from course where cname=数据库原理 )三 Delete1 删除所有 JSJ 系的男生 delete from student where sdept=JSJ2 删除“数据库原理”的课的选课纪录 Delete from sc where cno in (select cno from course where cname=数据库原理 ) 思考：修改数据的命令与修改表结构的命令有何区别？ 试验七 Sql 查询语句目的: 掌握 Select 查询语句。一 单表1查询年龄在19至21岁之间的女生的学号,姓名,年龄,按年龄从大到小排列。select sno,sname,sage from student where sage between 19 and 21 and ssex=女 order by sage desc2查询姓名中第戎2个字为“明”字的学生学号、性别。select sname ,ssex from student where sname like _明%3查询 1001课程没有成绩的学生学号、课程号select sno,cno from sc where grade is null and cno=10014查询JSJ 、SX、WL 系的学生学号,姓名，结果按系及学号排列select sno,sname from student where sdept in (JSJ,SX,WL)order by sdept,sno5按10分制查询学生的sno,cno,10分制成绩 （1-10分 为1 ，11-20分为2 ，30-39分为3，。90-100为10） select sno , cno , grade/10.0+1 as level from sc6查询 student 表中的学生共分布在那几个系中。（distinct） select distinct sdept from student 7查询0001号学生1001，1002课程的成绩。 Select cno from sc where sno=0001 and (cno=1001 or cno=1002)二 统计1查询姓名中有“明”字的学生人数。select count(*) from student where sname like %明%2计算JSJ系的平均年龄及最大年龄。 Select avg(sage) , max(sage) from student Where sdept=JSJ3计算每一门课的总分、平均分，最高分、最低分，按平均分由高到低排列 select cno,sum(grade),avg(grade),max(grade),min(grade) from sc group by cno order by avg(grade) desc4 计算 1001,1002 课程的平均分。Select cno , avg(grade) from sc where cno in (1001,1002) Group by cno5 查询平均分大于80分的学生学号及平均分 select sc.sno , avg(grade) from scgroup by sc.snohaving avg(grade)>806 统计选修课程超过 2 门的学生学号 select sno from sc group by sno having count(*)>27 统计有10位成绩大于85分以上的课程号。Select cno from sc where grade>85 group by cno having count(*) =108 统计平均分不及格的学生学号select sno from sc group by sno having avg(grade)<609 统计有大于两门课不及格的学生学号select sno from sc where grade<60 group by sno having count(*) >2三 连接 1查询 JSJ 系的学生选修的课程号 select cno from student,sc where student.sno=sc.sno and sdept=JSJ2查询选修1002 课程的学生的学生姓名 (不用嵌套及嵌套2种方法）a: select sname from student,sc where student.sno = sc.sno and cno=1002b: select sname from student where sno in (select sno from sc where cno=1002)3查询数据库原理不及格的学生学号及成绩select sno,grade from sc ,course where o=o and cname=数据库原理4查询选修“数据库原理”课且成绩 80 以上的学生姓名(不用嵌套及嵌套2种方法）a: select sname from student , sc , course where student.sno=sc.sno and o = o and grade>80 and cname=数据库原理 b: select sname from student where sno in ( select sno from sc where grade>80 and cno in ( select cno from course where cname=数据库原理) )5查询平均分不及格的学生的学号，姓名,平均分。select sno, max(sname) , avg(grade) as avggrade from sc , student where student.sno=sc.sno group by student.snohaving avg(grade) <60 6查询女学生平均分高于75分的学生姓名。A: Select sname from student where ssex=女 and sno in ( Select sno from sc group by sno having avg(grade)>75)B: Select max(sname ) from sc,student where student.sno=sc.sno and Ssex=女 Group by student.sno having avg(grade)>757查询男学生学号、姓名、课程号、成绩。(一门课程也没有选修的男学生也要列出，不能遗漏)select student.sno,sname,cno,grade from student left join sc ON student.sno=sc.sno and ssex=男四 嵌套、相关及其他1 查询平均分不及格的学生人数select count(*) from student where sno in ( select sno from sc group by sno having avg(grade)<60 )2 查询没有选修1002 课程的学生的学生姓名 select sname from student where sno not in( select sno from sc where cno=1002)student0001 aa X0002 bb à0003 cc Xsc 0001 10010001 10020002 10010003 1002 select sname from student where not exists (select * from sc where cno=1002 and sc.sno=student.sno)3 查询平均分最高的学生学号及平均分 （2种方法 TOP , any , all）a: select top 1 sno,avg(grade) from sc group by sno order by avg(grade) descB: select sno,avg(grade) from sc group by sno having avg(grade) = (select top 1 avg(grade) from scgroup by sno order by avg(grade) desc )c: select sno,avg(grade) from sc group by sno having avg(grade) >=all ( select avg(grade) from sc group by sno )*4 查询没有选修1001，1002课程的学生姓名。 Select sname from student where not exists ( Select * from course where cno in (1001,1002) and Not exists ( select * from sc where sno=student.sno and cno=o ) )5 查询1002课程第一名的学生学号（2种方法）a: select top 1 sno from sc cno=1002 order by grade descb: select sno from sc where cno=1002 and grade >=all (select grade from sc where cno=1002)6 查询平均分前三名的学生学号select top 3 sno from sc group by sno order by avg(grade) desc7 查询 JSJ 系的学生与年龄不大于19岁的学生的差集a: select * from student where sdept=JSJ and sage>19b: select * from student where sdept=JSJ except select * from student where sage<19 8 查询1001号课程大于90分的学生学号、姓名及平均分大于85分的学生学号、姓名select student.sno,sname from student,sc where cno=1001 and grade>90 union select sno,sname from student where sno in ( select sno from sc group by sno having avg(grade)>85 )9 查询每门课程成绩都高于该门课程平均分的学生学号select sno from student where sno not in (select sno from sc X where grade<( select avg(grade) from sc Y where Y.sno=X.sno) select sno from student where sno not in (select sno from sc X where grade < (