《数据库原理与应用》实验题目及答案

GO

Select * Into sc1 from sc --复制表 use ST

IF EXISTS(SELECT name FROM sysobjects

WHERE name = 'sc2' AND type = 'U') DROP TABLE sc2 GO

Select * into sc2 from sc --复制表结构 Where not exists (select * from sc)

运行结果:

(6 行受影响)

(0 行受影响)

--[3_46] 合并表 ⑴先向表sc2输入数据 ⑵再将sc2合并到sc1中。insert into sc2 values('201313103','2',92); insert into sc2 values('201313102','5',75); insert into sc2 values('201313104','3',68); insert into sc2 values('201313102','7',80); insert into sc2 values('201313101','6',47); select * from sc1 union all select * from sc2

运行结果:

sno cno grade --------- ---- ------------------------- 201313101 1 92.0 201313101 2 35.0 201313101 3 88.0 201313102 2 90.0 201313102 3 50.0 201313103 2 92.0 201313102 5 75.0 201313104 3 68.0 201313102 7 80.0 201313101 6 47.0

--[3_47] 将信息系所有学生的年龄增加1岁。

UPDATE student

16

SET sage=sage+1 where sdept='IS';

运行结果: (1 行受影响)

--[3_48] 对计算机科学系(cs)全体学生选修'2'号课程分数>=36的按10*sqrt(grade)计算。

update sc

set grade=10*sqrt(grade) where cno='2' and grade>=36 and 'cs'=(select sdept from student

where student.sno=sc.sno) --相关子查询

运行结果: (1 行受影响)

--[3_49] 删除计算机科学系所有学生的选课记录。

DELETE FROM sc

WHERE 'cs'=(SELECT sdept FROM student

WHERE student.sno=sc.sno);

运行结果: (5 行受影响)

--[3_50] 删除未选修课程的学生。

use ST

IF EXISTS(SELECT name FROM sysobjects

WHERE name = 'student1' AND type = 'U') DROP TABLE student1 GO

Select * Into student1 from student --先复制表 --删除student1中未选修课程的学生 DELETE FROM student1 WHERE sno NOT IN (SELECT sno FROM sc)

运行结果:

(4 行受影响)

(3 行受影响)

--[3_51] 删除只选1门课且成绩不及格学生的选课记录。

17

DELETE FROM sc WHERE sno in (SELECT sno FROM sc

WHERE grade<60

GROUP BY sno HAVING COUNT(*)=1)

运行结果: (0 行受影响)

--[3_52] 删除计算机系只选1门课且成绩不及格学生的选课记录。

DELETE FROM sc WHERE sno IN (SELECT sno FROM student

WHERE sdept='cs' AND sno IN (SELECT sno FROM sc

WHERE grade<60 AND student.sno=sc.sno GROUP BY sno HAVING COUNT(*)=1))

运行结果: (0 行受影响)

(二) 自定内容

五、出现的问题及解决方法

实验4 索引与视图

完成以下实验报告:

《数据库原理与应用》实验报告

院名 学号 专业 姓名 年级 教师 班级 成绩 一、实验题目 实验4 索引与视图 二、实验环境

操作系统:Windows XP。

数据库管理系统:MS SQL Server 2000 或MS SQL Server 2008。 三、实验目的

1.理解索引的目的、维护与使用,唯一与非唯一索引、聚簇与非聚簇索引、单索引与复合索引的区别。

2.掌握建立与删除索引的方法。 3.掌握建立与删除视图的方法。

4.掌握对视图表进行查询与更新的操作方法。 四、实验内容

18

(一) 规定内容

1. 建立与删除索引

--[4_1] 查询姓“李”的学生,为其建立相应的索引。

if exists (select name from sysindexes where name='index_sname') drop index student.index_sname create index index_sname on student(sname)

--[4_2] 查询年龄20岁的女生,为其建立相应的索引。

if exists (select name from sysindexes where name='index_sageSex ') drop index student.index_sageSex

create index index_sageSex ON student(sage,ssex DESC)

--[4_3] 建立“教工(教工号,姓名,性别)”表结构,按“教工号”为该表建立聚簇索引,按“姓名”、“性别”为该表建立唯一值复合索引。

if exists (select name from sysobjects where name='教工') drop table 教工 create table 教工 (教工号 char(5), 姓名 char(8), 性别 char(2) )

create clustered index index_tno on 教工(教工号)

create unique index index_TnameSex on 教工(姓名, 性别) --[4_4] 删除“教工”表上建立的唯一值复合索引。 drop index 教工.index_TnameSex

--[4_5] (2004秋数据库系统工程师下午试题)

设有如下关系模式: 职工(职工号,姓名,年龄,月工资,部门号,电话,办公室) 假定分别在“职工”关系中的“年龄”和“月工资”字段上创建了索引,如下的Select查询语句可能不会促使查询优化器使用索引,从而降低查询效率,请写出既可以完成相同功能又可以提高查询效率的SQL语句。

Select 姓名,年龄,月工资 from 职工 Where 年龄>45 or 月工资<1000;

解:

Select 姓名,年龄,月工资 from 职工

Where 年龄>45; UNION

Select 姓名,年龄,月工资 from 职工

Where 月工资<1000;

2. 建立与删除视图

--[4_6] 建立信息系学生视图,含学号、姓名、姓别等列。 use ST

IF EXISTS (SELECT name FROM sysobjects WHERE name='V_is') drop view V_is --若V_is已存在,则删除 go

CREATE VIEW V_is

19

AS

SELECT sno,sname,ssex --行列子集视图 FROM student WHERE sdept='is'

--[4_7] 建立信息系学生的视图,并要求透过该视图进行的更新操作只涉及信息系学生。

use ST

IF EXISTS (SELECT name FROM sysobjects WHERE name='V_is1') drop view V_is1 --若V_is已存在,则删除 go

CREATE VIEW V_is1 AS

SELECT *

FROM student WHERE sdept='is'

WITH CHECK OPTION --WITH CHECK OPTION的视图 --[4_8] 建立信息系选修了1号课程的学生视图。

IF EXISTS (SELECT name FROM sysobjects WHERE name='V_is2') drop view V_is2 --若V_is已存在,则删除 go

CREATE VIEW V_is2(sno,sname,cno,grade) AS

SELECT student.sno,sname,cno,grade

FROM student,sc --基于多个基表的视图 WHERE student.sno=sc.sno AND sdept='is' AND sc.cno='1'

--[4_9] 建立信息系选修了1号课程且成绩在90分以上的学生的视图。 IF EXISTS (SELECT name FROM sysobjects WHERE name='V_is3') drop view V_is3 --若V_is已存在,则删除 go

CREATE VIEW V_is3 AS

SELECT sno,sname,grade

FROM V_is2 --基于视图的视图 WHERE grade>=90

--[4_10] 先建立高考(考号,姓名,语文,数学,英语,综合)基本表, 在此基础上建立含总分列的视图。

IF EXISTS(SELECT name FROM sysobjects WHERE name = 'GK') drop table GK --若GK已存在,则删除 go

create table GK

(考号 char(5) primary key, 姓名 char(8),

语文 decimal(5,1) check(语文 between 0 and 150), 数学 decimal(5,1) check(数学 between 0 and 150),

20

联系客服:779662525#qq.com(#替换为@) 苏ICP备20003344号-4