数据库与软件工程上机实验答案

第一篇：数据库与软件工程上机实验答案

《数据库与软件工程》上机实验答案

实验三 SQL语言的DDL

3．CREATE TABLE aa（Bb1 VARCHAR(30),Bb2 INT,Bb3 DECIMAL(6,2))

4． ALTER TABLE aa ADD Bb4 varchar(20)

5.DROP TABLE AA

6． CREATE VIEWReaderView(借阅者)

ASSELECT DISTINCT 读者编号

FROM 借阅

7． Drop ViewReaderView。

8.CREATE INDEX INDEX1 ON 读者(姓名 ASC，单位 ASC)

CREATE INDEX INDEX3 ON 借阅(借阅日期 DESC)

9． DROP INDEX 读者.INDEX1

CREATE INDEX INDEX1 ON 读者(姓名 ASC)

实验四 SQL语言的DML初步

1． CREATE DEFAULT ZEROVALUE AS 0

sp_bindefault ZEROVALUE, '图书.借出否'

CREATE DEFAULT CURTIME AS getdate()

sp_bindefault CURTIME, '借阅.借阅日期'

2． CREATE RULE gender_rule

AS@value in('男','女')

EXEC sp_bindrule 'gender_rule', '读者.性别'

CREATE RULE lenddate_rule

AS@value > '2004-1-1'

EXEC sp_bindrule 'lenddate_rule', '借阅.归还日期'

3． 用INSERT语句对“图书”表插入6条记录

INSERT INTO 图书(书号,类别,出版社,作者,书名,定价)

VALUES('0001','计算机类','清华出版社','严蔚敏','数据结构', 20.11)INSERT INTO 图书(书号,类别,出版社,作者,书名,定价)

VALUES('0002','计算机类','清华出版社','苗雪兰','数据库技术及应用',29.00)

INSERT INTO 图书(书号,类别,出版社,作者,书名,定价)

VALUES('0003','计算机类','清华出版社','李建中','软件工程',48.00)INSERT INTO 图书(书号,类别,出版社,作者,书名,定价)

VALUES('0004','计算机类','电子工业出版社','汤惟','WEB技术',32.00)

INSERT INTO 图书(书号,类别,出版社,作者,书名,定价)

VALUES('0005','自动化类','机械工业出版社','胡寿松','自动控制原理',52.00)

INSERT INTO 图书(书号,类别,出版社,作者,书名,定价)

VALUES('0006','自动化类','机械工业出版社','郑大钟','线性控制理论',32.00)用INSERT语句对“读者”表插入4条记录

INSERT INTO 读者(读者编号,姓名,单位,性别,电话)

VALUES('10001','张三','东华大学','男','67792312')

INSERT INTO 读者(读者编号,姓名,单位,性别,电话)

VALUES('10002','李四','东华大学','女','67792312')

INSERT INTO 读者(读者编号,姓名,单位,性别,电话)

VALUES('10003','王五','东华大学','男','67792312')

INSERT INTO 读者(读者编号,姓名,单位,性别,电话)

VALUES('10004','李明','东华大学','女','67792312')

用INSERT语句对“借阅”表插入7条记录

INSERT INTO 借阅(书号,读者编号,借阅日期,归还日期)

VALUES('0001','10001','2006-04-19 09:58:03','2006-05-19 8:38:23')

INSERT INTO 借阅(书号,读者编号,借阅日期,归还日期)

VALUES('0006','10002','2006-07-01 15:28:12','2006-07-18 9:14:02')

INSERT INTO 借阅(书号,读者编号,借阅日期,归还日期)

VALUES('0002','10002','2006-07-01 15:28:30','2006-07-17 19:10:32')

INSERT INTO 借阅(书号,读者编号,借阅日期,归还日期)

VALUES('0002','10003','2006-07-03 15:28:30','2006-08-09 15:28:30')

INSERT INTO 借阅(书号,读者编号,借阅日期,归还日期)

VALUES('0004','10002','2007-05-01 11:28:24','2007-08-01 8:09:04')

INSERT INTO 借阅(书号,读者编号,借阅日期,归还日期)

VALUES('0005','10002','2007-11-01 10:43:12','2007-12-01 14:09:56')

INSERT INTO 借阅(书号,读者编号,借阅日期,归还日期)

VALUES('0003','10004','2007-12-01 16:06:02','2007-12-29 13:17:09')

4． UPDATE 借阅

SET 借阅日期='2006-08-03 15:28:30'

WHERE 读者编号='10003' AND

借阅日期 BETWEEN '2006-07-03' AND '2006-07-04' AND 书号='0002'

5． DELETE FROM 借阅

WHERE 借阅日期<'2006-06-01'

1． SELECT 书号,书名 as 名称,定价 as 价格 FROM 图书

WHERE 类别='自动化类'

2． 方法1：

SELECT * FROM 图书

WHERE 类别='计算机类'

UNION

SELECT * FROM 图书

WHERE 出版社='电子工业出版社'

方法2：

SELECT * FROM 图书

WHERE 类别='计算机类' OR 出版社='电子工业出版社'

3． SELECT * INTO 计算机图书表 FROM 图书

WHERE 类别='计算机类'

4． SELECT DISTINCT 读者.* FROM 读者,借阅

WHERE 读者.读者编号=借阅.读者编号

5． SELECT 读者.读者编号,姓名,单位,图书.书号,书名,借阅日期

FROM 读者,借阅,图书

WHERE 读者.读者编号=借阅.读者编号 AND 图书.书号=借阅.书号

6． SELECT 图书.书号,书名,类别,借阅日期 FROM 读者,借阅,图书

WHERE 读者.读者编号=借阅.读者编号 AND 图书.书号=借阅.书号

AND 姓名='张三'

ORDER BY 借阅日期 DESC

7． SELECT 读者.读者编号,姓名,书名,DATEDIFF(day,借阅日期,归还日期)as 借阅时间

FROM 读者,借阅,图书

WHERE 读者.读者编号=借阅.读者编号 AND 图书.书号=借阅.书号

AND DATEDIFF(day,借阅日期,归还日期)>20

order by 借阅时间

8． SELECT * FROM 读者

WHERE NOT EXISTS（SELECT * FROM 图书

WHERE 类别='自动化类' and NOT EXISTS（SELECT * FROM 借阅

WHERE 读者编号=读者.读者编号 and 书号=图书.书号))

1． SELECT 类别,COUNT(*)AS 数量, AVG(定价)AS平均价格,SUM(定价)AS 总价

FROM 图书

GROUP BY 类别

ORDER BY 类别

2． SELECT YEAR(借阅日期)as 年份,书号,COUNT(*)AS 借阅次数

FROM 借阅

GROUP BY YEAR(借阅日期),书号

ORDER BY 借阅次数 DESC

3． SELECT 读者.读者编号,COUNT(书号)AS 借阅次数 FROM 读者

LEFT JOIN

(SELECT * FROM 借阅 WHERE 借阅日期 BETWEEN '2006-7-1' AND '2007-10-1')A

ON 读者.读者编号=A.读者编号

GROUP BY 读者.读者编号

4． SELECT 读者编号,COUNT(*)AS 次数 FROM 借阅,图书

WHERE 借阅.书号=图书.书号 AND 书名='数据库技术及应用'

GROUP BY 读者编号

ORDER BY 次数 DESC

5． 方法1：

SELECT MAX(次数)AS 最多,MIN(次数)AS 最少,AVG(次数*1.0)AS平均

FROM（SELECT 图书.书号,COUNT(*)AS 次数 FROM 图书,借阅

WHERE 图书.书号=借阅.书号 AND 类别='计算机类'

GROUP BY 图书.书号)A

方法2：

SELECT 图书.书号,COUNT(*)*1.0 AS 次数 FROM 图书,借阅

WHERE 图书.书号=借阅.书号 AND 类别='计算机类'

GROUP BY 图书.书号

COMPUTE MAX(COUNT(*)*1.0),MIN(COUNT(*)*1.0),AVG(COUNT(*)*1.0)

6． SELECT 类别 FROM 图书

GROUP BY 类别

HAVING MAX(定价)>=ALL(SELECT 2*AVG(定价)FROM 图书 GROUP BY 类别)

7． SELECT 书号,书名,定价,出版社 FROM 图书

WHERE 类别='计算机类'

ORDER BY 出版社 DESC

COMPUTE COUNT(书号)BY 出版社

COMPUTE COUNT(书号)

实验七 存储过程、触发器和数据库恢复

1．（1）

CREATE PROCEDURE 借阅情况

@Readerno VARCHAR(8)

AS

SELECT 读者.读者编号,姓名,图书.书号,书名,借阅日期, 借出否 AS 归还否FROM 读者,借阅,图书

WHERE 读者.读者编号=借阅.读者编号 AND 图书.书号=借阅.书号AND 读者.读者编号=@Readerno

EXEC 借阅情况 ‘10004’

（2）

CREATE PROCEDURE 借出情况

@Bookno VARCHAR(10)

AS

SELECT 书名,姓名,借阅日期,归还日期

FROM 读者,借阅,图书

WHERE 读者.读者编号=借阅.读者编号 AND 图书.书号=借阅.书号AND 图书.书号=@Bookno

EXEC 借出情况 '0004'

2．CREATE TRIGGER lendbookinsert ON 借阅

FOR INSERT

AS

IF(SELECT 借出否 FROM 图书 WHERE 书号 IN

(SELECT 书号 FROM INSERTED))=1

BEGIN

PRINT '该书已经借出'

ROLLBACK TRANSACTION

END

ELSE

BEGIN

UPDATE 图书

SET 借出否=1

WHERE 书号 IN(SELECT 书号 FROM INSERTED)

END

第二篇：数据库上机实验

创建数据表与数据输入

Part I.使用SQL Server Management Studio创建数据表和输入数据 1.在SQL Server Management Studio中创建数据表 P69 1.（1）-（6）

2.为数据表输入数据 P76 4.（1）-（4）

3.数据浏览

P77

1.（1）-（2）

2.（1）-（3）

Part II.使用SQL语句创建数据表和输入数据 1.使用SQL语句创建数据表 P72.例3-2

2．使用SQL语句输入数据 P82.例3.8

习题：P.105（1）（2）insert 数据操作Insert、Update、Delete

P105 3.上机练习题（2）（4）

Insert(100001, 1000, 2002-12-18 0:00:00)(100002, 2000, 2010-3-20 0:00:00)

Update(100001, 1500, 2002-12-18 0:00:00)(100002, 2000, 2012-9-25 0:00:00)

Delete Transact-SQL语句基础1 1.将teaching数据库中score表的studentno列设置为引用表student的外键。ALTER TABLE Score ADD CONSTRAINT FK_score_student FOREIGN KEY(studentno)REFERENCES student(studentno)2.将teaching数据库中class表的classname创建UNIQUE约束。ALTER TABLE class ADD CONSTRAINT UQ_class UNIQUE(classname)执行如下插入语句，查看提示信息

INSERT INTO class VALUES(‘090602’, ’计算机0902’, ’计算机学院’, ’马文斐’)3.为teaching数据库中student表的birthday列创建CHECK约束，规定学生的年龄在17-25岁之间。

ALTER TABLE student ADD CONSTRAINT CK_birthday CHECK(YEAR(GETDATE())-YEAR(birthday))BETWEEN 17 AND 25 执行如下插入语句，查看提示信息

INSERT INTO student(studentno, sname, sex, birthday, classno)VALUES(‘0922221328’, ’张源’, ’男’, ’1983-04-05’, ’090501’)提示：表达式YEAR(GETDATE())-YEAR(birthday)4.为teaching数据库创建规则prof_rule，规定教师职称取值只能为’助教’,’讲师’,’副教授’,’教授’，并将其绑定到teacher表的prof列上。

CREATE RULE prof_rule AS @prof IN(’助教’,’讲师’,’副教授’,’教授’)EXEC sp_bindrule ‘prof_rule’, ‘teacher.prof’

执行如下插入语句，查看提示信息

INSERT INTO teacher VALUES(‘t05002’, ’张源’, ’软件工程’, ’工程师’, ’计算机学院’)提示：表达式IN(职称列表)5.编写程序，输出在1-3000之间能被17整除的最大数值

提示：可使循环控制变量从最大值开始，逐步减少，第一个满足被17整除的数值即为所求解的结果，可通过BREAK语句跳出循环。（如果使循环控制变量从小到大逐步增加，则循环次数将大大增加，程序执行效率将下降。）

PRINT ‘1-3000之间能被17整除的最大数值为：’ +CAST(@i AS CHAR(4))DECLARE @s INT, @i INT SELECT @s=0, @i=3000 WHILE @i>=1 BEGIN

IF @i%17=0

BEGIN

PRINT ‘1-3000之间能被17整除的最大数值为：’ + CAST(@i AS CHAR(4))

BREAK

END @i = @i-1 END Transact-SQL语句基础2 1.利用Transact-SQL语句声明一个长度为16的nchar型变量bookname，并赋初值为“SQL Server数据库编程”，打印该变量。

2.编程求50-100之间的所有能被3整除的奇数之和。

3.编写程序，声明CHAR型变量@ch，并赋初值。判断字符变量@ch中存放的是字母、数字字符还是其他字符，并输出相关信息。（提示：UPPER函数可以将小写字母转换为大写字母）

4.编写程序，求解如下分数序列的前20项之和并打印输出结果。

S=2/1+3/2+5/3+8/5+13/8+21/13+…

分析数列的规律：后一项的分子为前一项的分子和分母之和，后一项的分母则为前一项的分子，通过循环即可实现累加。注意：声明分子和分母为浮点型数据NUMERIC(20,7).5.查询编号为c06108, c08106, c05109课程的学生总评成绩等级，平时成绩usually*0.2+期末成绩final*0.8>=90的为优秀，80-90为良好，70-80为中等，60-70为及格，<60为不及格。（提示：使用CASE …END语句）数据检索1 1.查询course表中所有的记录。2.查询student表中女生的人数。

3.查询teacher表中每一位教授的教师号、姓名和专业名称。

4.利用现有的表生成新表，新表中包括学号、学生姓名、课程号和总评成绩。其中：总评成绩=final*0.8+usually*0.2 5.查询student表中所有年龄大于20岁的男生的姓名和年龄。6.查询计算机学院教师的专业名称。

7.查询Email使用126邮箱的所有学生的学号、姓名和电子邮箱地址。8.查询score表中选修’c05109’或’c05103’课程，并且课程期末成绩在90~100分之间的学生姓名和期末成绩。

9.查询student表中所有学生的基本信息，查询结果按班级号classno升序排列，同一班级中的学生按入学成绩point降序排列。10.查询选修’c05109’课程，并且期末成绩在前5名的学生学号、课程号和期末成绩。（提示：TOP 5）数据检索2

1.按性别分组，求出student表中每组学生的平均年龄。2.统计每个学生期末成绩的平均分。

3.输出student表中没有职称的职工的教师号、姓名、专业和部门。4.查询选修课程且期末成绩不为空的学生人数。

5.查询每名学生的学号、选修课程数目、总成绩，并将查询结果存放到生成的“学生选课统计表”中。6.查询各班学生的人数。

7.查询各课程期末成绩的最高分和最低分。

8.查询教两门及以上课程的教师编号、任课班级数。

9.查询课程编号以’c05’开头、被3名及以上学生选修且期末成绩的平均分高于75分的课程号、选修人数和期末成绩平均分，并按平均分降序排序。10.查询所有08级学生的期末成绩平均分，要求利用COMPUTE BY方法显示每一名学生的学生编号、课程号、期末成绩的明细表，以及期末成绩平均分的汇总表。

11.查询所有女生入学成绩的最高分，要求利用COMPUTE BY方法既显示明细又显示汇总结果。SQL语句的高级应用1

1.查询每一位教授的教师号、姓名和讲授的课程名称。表：teacher,course,teach_class 2.分别统计每个学生期末成绩高于75分的课程门数。表：student,score 3.计算每个学生获得的学分。表：student,score,course 4.获取入学时间在2008年到2009年之间的所有学生中入学年龄小于19岁的学生的学号、姓名及所修课程的课程名称。表：student, stu_course 5.查询09级学生的学号、姓名、课程及学分。表：student,stu_course 6.查询所有班级的期末成绩平均分，并按照平均分降序排列。表：score,student 7.查询教师基本信息和教授课程信息，其中包括未分配课程的教师信息。表：teacher,teach_class 8.查询’090501’班级中选修了’韩晋升’老师讲授的课程的学生的学号、姓名、课程名和期末成绩。表：score,student,course,teach_class,teacher 9.查询每门课程的课程号、课程名和选修该课程的学生人数，并按所选人数升序排序。表：score,course 10.查询两门及以上课程的期末成绩超过80分的学生的姓名及其平均成绩。表：student,score SQL语句的高级应用2 – 使用子查询

1.输出student表中年龄大于女生平均年龄的男生的所有信息。2.查询入学考试成绩最高的学生的学号、姓名和入学成绩。3.查询所有教授’c05127’号课程的教师信息。

4.查询同时教授’c05127’和’c05109’号课程的教师信息。

5.查询至少选修了姓名为’韩吟秋’的学生所选修课程中的一门课的学生的学号和姓名。

6.查询没有被任何学生选修的课程编号、课程名称和学分。

7.查询’C语言’课程期末成绩比’电子技术’课程期末成绩高的所有学生的学号和姓名。

8.查询所有班级期末平均成绩的最高分，并将其赋值给变量，通过PRINT语句输出。视图与索引

使用SQL语言

1.创建一个视图v_teacher，查询所有“计算机学院”的教师信息。

CREATE VIEW v_teacher AS SELECT * FROM teacher WHERE department='计算机学院' GO SELECT * FROM v_teacher

2.创建一个视图v_avgstu，查询每个学生的学号、姓名及平均分，并且按照平均分降序排列。

CREATE VIEW v_avgstu AS SELECT TOP 100 student.studentno,sname,avg(final)AS '平均分' FROM student JOIN score ON student.studentno=score.studentno GROUP BY student.studentno,sname ORDER BY avg(final)GO SELECT * FROM v_avgstu

3.修改v_teacher的视图定义，添加WITH CHECK OPTION选项。

ALTER VIEW v_teacher AS SELECT * FROM teacher WHERE department='计算机学院' WITH CHECK OPTION

4.通过视图v_teacher向基表teacher中分别插入数据(‘05039’, ‘张馨月’,’计算机应用’,’讲师’,’计算机学院’)和(‘06018’, ‘李诚’,’机械制造’,’副教授’,’机械学院’)，并查看插入数据情况。

INSERT INTO v_teacher VALUES('05039', '张馨月','计算机应用','讲师','计算机学院')GO SELECT * FROM teacher

消息550，级别16，状态1，第1 行

试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了WITH CHECK OPTION，而该操作的一个或多个结果行又不符合CHECK OPTION 约束。语句已终止。

5.通过视图v_teacher将基表teacher中教师编号为05039的教师职称修改为”副教授”。

UPDATE v_teacher SET prof='副教授' WHERE teacherno='05039' GO SELECT * FROM teacher

6.在course表的cname列上创建非聚集索引IDX_name。

CREATE NONCLUSTERED INDEX IDX_name ON course(cname)

7.在student表的studentno和classno列上创建唯一索引UQ_stu，若该索引已经存在，则删除后重建，并输出student表中的记录，查看输出结果的顺序。CREATE UNIQUE INDEX UQ_stu ON student(studentno,classno)

8.教材P.200-204（节7.5.2）使用SQL Server Management Studio创建视图，按照样图进行操作。

第三篇：数据库上机实验(二)

数据库上机实验内容及要求（第二部分）

1． 建立工厂管理数据库

工厂（包括厂名和厂长名）需要建立一个管理数据库存储以下信息：

（1）一个厂内有多个车间，每个车间有车间号、车间主任姓名、地址和联系电话；

（2）一个车间有多个工人，每个工人有职工号、姓名、年龄、性别和工种；

（3）一个车间生产多种产品，产品有产品号和价格；每种产品只能由一个车间生产；

（4）一个车间制造多种零件，一种零件也可能为多个车间制造。零件有零件号、重

量和价格；

（5）一种产品可由多种零件组成，一种零件也可以装配出多种产品；

（6）产品和零件均存入仓库；

（7）厂内有多个仓库，仓库有仓库号、仓库主任姓名和电话。

根据以上需求分析结果，按照下述要求，设计并建立工厂管理数据库。 分析实体及联系，设计E-R图。

 将E-R图转换成关系模式，并规范化到3NF。

 在Microsoft SQL Server2000中基于“企业管理器”建立数据库及相关对象（主

码，外码，索引，约束等）。

 测试数据入库

2． 基于“查询分析器”，完成并保存下述题目的SQL脚本

（1）建立“工种”是“钳工”的所有职工详细信息的视图；

（2）建立“车间号”是“CJ01”的钳工详细信息的视图；

（3）建立使用了“零件号”是“LJ0002”的所有产品详细信息的视图；

（4）查询使用了“零件号”是“LJ0002”的产品的生产车间号；

（5）对零件表按照“零件号”建立唯一索引；

（6）对职工表按照“性别”建立聚簇索引；

（7）查询“车间主任姓名”是“赵平”的“车间地址”和“联系电话”；

（8）查询“职工号”是“ZG0001”的职工所在车间的“车间主任姓名”和“联系电

话”；（连接查询实现）

（9）查询“产品号”是“CP0001”的产品的生产车间的“车间主任姓名”和“联系

电话”；（嵌套查询实现）

（10）查询使用了“零件号”是“LJ0002”的所有产品的“产品号”，且查询结果按照

“零件数量”降序排列；

（11）查询使用了“零件号”是“LJ0002”的所有产品的“产品号”和“产品价格”；

（12）查询使用了“零件号”是“LJ0002”的所有产品的生产车间的“车间主任姓名”

和“联系电话”；

（13）查询使用了“零件号”是“LJ0002”的产品数；

（14）查询“LJ0002”号零件装配产品的使用总量；

（15）查询使用了3种以上零件的产品号；

【注意】：下机时保存数据库文件（.mdf和.ldf）及SQL脚本文件到U盘。

第四篇：数据库上机实验总结(含代码)

实验一

（1）无条件单表查询

select sname NAME,'year of birth:' BIRTH,2004-sage BIRTHDAY,LOWER(sdept)DEPARTMENT FROM student;（2）有条件单表查询

SELECT sname,sdept,sage FROM student WHERE sage NOT BETWEEN 20 AND 23;（3）单表嵌套（一层）查询

SELECT sno,sname,sdept FROM student WHERE sdept IN(SELECT sdept FROM student WHERE sname='刘晨');（4）复合条件多表查询

SELECT student.sno,sname,cname,grade FROM student ,sc,course WHERE student.sno=sc.sno AND sc.cno=course.cno;（5）使用COUNT（）的单表查询 SELECT COUNT(*)FROM student;（6）使用AVG（）的单表查询

SELECT AVG(grade)'平均成绩' from SC where CNO='1';（7）查询结果分组

SELECT cno,COUNT(sno)'人数' FROM sc GROUP BY cno;（8）查询结果排序

SELECT * FROM student ORDER BY sdept,sage DESC;（9）使用通配符的查询

SELECT sname,sno,ssex FROM student WHERE sname NOT LIKE'刘%';（10）使用换码字符的单表查询

SELECT cno,ccredit FROM course WHERE cname LIKE 'DB_Design'ESCAPE'';（11）插入单个元组 插入一个新学生元组

Insert into student(sno,sname,ssex,sdept,sage)values('200215128','陈冬','男','IS',18)（12）插入子查询结果

对每一个系，求学生平均年龄，并把结果存入数据库 Create table dept_age(sdept char(15),avg_age int)Insert into dept_age(sdept,avg_age)select sdept,avg(sage)from student group by sdept（13）修改某个元组的值

将学生200215121的年龄改为22岁

Update student set sage=’22’ where sno=’200215121’（14）修改多个元组的值 将所有学生的年龄增加一岁 Update student set sage=sage+1（15）删除一个元组的值 删除学号为200215128的学生记录

delete from student where sno='200215128'（16）建立视图 建立信息系学生的视图

create view is_student as select sno,sname,sage from student where sdept='IS' ×（17）查询视图

查询选修了1号课程的信息系学生信息

Select is_student.sno,sname from is_student,sc where is_student.sno=sc.sno and sc.cno=’1’ ×（18）更新视图

将信息系学生视图is_student中学号为95001的学生姓名改为李楠 update is_student set sname='李楠' where sno='95002' 将下列问题用SQL命令表示：

1．查询‘IS’系学生的学号、姓名、性别。

SELECT sno,sname,ssex FROM student WHERE sdept='IS';2．查询‘IS’系年龄在20岁以下的学生。

SELECT * FROM student WHERE sdept='IS'AND sage<20;3．查询所有不姓‘刘’的学生的学号、姓名、性别。

SELECT sname,sno,ssex FROM student WHERE sname NOT LIKE'刘%';4.查询student表中学生的总人数。

SELECT COUNT(*)'总人数' FROM student;5.查询和‘李勇’同性别的所有同学的姓名。

SELECT sname from student where ssex in(select ssex from student where sname='李勇');6.查询和‘李勇’同性别并同系的所有同学的姓名。

Select sname from student where ssex in(select ssex from student where sname='李勇')and sdept in(select sdept from student where sname='李勇')7.查询选修2号课程的学生的学号。Select sno from sc where cno='2' 8.求3号课程的平均成绩。

Select avg(grade)from sc where cno=’3’ 9.查询选修2号课程的学生的最高分。Select max(grade)from sc where cno=’2’

10.按成绩降序排列，输出‘IS’系学生选修了2号课程的学生的姓名和成绩。

Select sname,grade from student,sc where sdept='IS' and cno='2' and student.sno=sc.sno order by grade desc SQL查询分析器下建数据库的命令代码： create database 霍双双200826352 on(name='霍双双200826352_data',filename='E:

霍霍

双双

双双

***5

霍霍

双双

双双200826352_data.mdf',size=10mb,maxsize=50mb,filegrowth=10%)log on(name='霍双双200826352_log',filename='E:200826352_log.ldf',size=10mb,maxsize=50mb,filegrowth=10%)在查询分析器重建立各表的命令代码： 建立student表：

create table student(sno char(5)primary key,sname char(20),ssex char(2),sage int,sdept char(15))建立course表：

create table course(cno char(2)primary key,cname char(15),cpno char(2),ccredit int)建立cs表：

use 霍双双200826352 create table sc(sno char(5),cno char(2),grade smallint,primary key(sno,cno),foreign key(sno)references student(sno),foreign key(cno)references course(cno))实验二

T-SQL查询、存储过程、触发器、完整性上机作业题 第一部分 ：T-SQL程序设计

（1）.如果3号课程的平均成绩在80分以上，则输出“3号课程成绩良好”，否则输出“3号成绩一般” declare @avg float set @avg=(select avg(grade)from sc where cno='3')if @avg>80print'3号课程成绩良好'else print'3号成绩一般'（2）计算并输出95003号学生的平均成绩，若无该生信息，则显示“该生未选课”,提示信息.declare @avg float if(select count(*)from sc where sno='95003')=0 print '该生未选课' else begin select @avg=avg(grade)from sc where sno='95003' print'95003号学生平均成绩' print @avg end（3）.如果有成绩在90分以上的学生,则显示他的学号,课程和成绩,否则显示“没有学生的课程成绩在90分以上”提示信息

declare @text char(10)if exists(select grade from SC where grade>90)select Sno,Cno,Grade from SC where Grade>90 else begin set @text='没有学生的课程成绩在90分以上' print @text end ×(4).利用游标逐行显示student表中的记录。

declare stu cursor for select *from student open stu fetch next from stu while @@fetch_status=0 fetch next from stu close stu deallocate stu(5).用自定义函数计算全体男生的平均年龄

create function avg_age(@sex char(2))returns int as begin declare @aver int select @aver=(select avg(Sage)from Student where Ssex=@sex)return @aver end go declare @aver1 int,@sex char(2)set @sex='男' select @aver1=dbo.avg_age(@sex)select @aver1 as '全体男生的平均年龄' go(6).显示course表中课程名的前2个字符。select substring(Cname,1,2)from Course(7).在一列中显示student中各元组的学号中的年级，列名显示为“年级”；另一列中显示学号中的学生序列号，列名显示为“序号”。

select substring(Sno,1,2)年级,substring(Sno,3,len(Sno)-1)序号 from Student order by Sno(8).在选课表中显示学号、课程号，并根据成绩：0-59显示“不合格”；60-79显示“合格”；80-89显示“良好”；90-100显示“优秀。”

select Sno as '学号',Cno as '课程号', grade =case when Grade<=59 then '不合格' when Grade>=60 and Grade<=79 then '合格' when Grade>=80 and Grade<=89 then '良好' else '优秀' end from SC 第二部分 ：存储过程

（1）创建一个为worker表添加职工记录的存储过程Addworker go

if exists(select name from sysobjects where name='Addworker' and type='P')drop procedure Addworker go create proc Addworker @职工号 char(4),@姓名 char(8),@性别 char(2),@出生日期 datetime,@党员否 char(2),@参加工作 datetime,@部门号 char(4)as insert into worker(职工号,姓名,性别,出生日期,党员否,参加工作,部门号)values(@职工号,@姓名,@性别,@出生日期,@党员否,@参加工作,@部门号)go exec Addworker '16','王璐','女','1988-11-20','否','2010-08-21','11'（2）创建一个存储过程Delworker删除worker表中指定职工号的记录 go

if exists(select name from sysobjects where name='Delworker' and type='P')drop procedure Delworker go create procedure Delworker @职工号 char(4)as delete from worker where 职工号=@职工号

go exec Delworker '16'（3）显示存储过程Delworker的定义信息。Sp_helptext Delworker（4）删除存储过程Addworker和Delworker。drop procedure Addworker, Delworker（5）创建并执行以下存储过程：

a.从数据库表中查询，返回学生学号、姓名、课程名、成绩 use 霍双双200826351 go if exists(select name from sysobjects where name='select_stu' and type='P')drop procedure select_stu go create procedure select_stu as select SC.Sno,Sname,Cname,Grade from Student,SC,Course where SC.Sno=Student.Sno and SC.Cno=Course.Cno go exec select_stu b.从数据库表中查询指定学号的学生学号，姓名，该存储过程接受与传递参数，精确匹配的值 use 霍双双200826351 go if exists(select name from sysobjects where name='select_sno' and type='P')drop procedure select_sno go create procedure select_sno @Sno char(5)as select Sno,Sname from Student where Sno=@Sno go exec select_sno '95002' ×第三部分：触发器

(1)在表depart上创建一个触发器 depart_update , 当更改部门号时同步更改 worker表中对应的部门号。Go If exists(select name from sysobjects where name='depart_update'and type='tr')drop trigger depart_update go Create trigger depart_update on depart for update as set worker.部门号=（select 部门号 from inserted）where worker.部门号=(select 部门号from deleted)(2)在表worker上创建一个触发器worker_delete,当删除职工记录时同步删除salary表中对应的职工记录。Go If exists(select name from sysobjects where name='worker_delete'and type='tr')drop trigger worker_delete go create trigger worker_delete on worker for delete as delete salary where salary.职工号=(select 职工号 from deleted)(3)删除触发器depart_update(4)删除触发器worker_delete(5)在数据库中创建一个触发器，向选课表添加一条纪录时，检查该纪录的学号在学生表中是否存在，检查该纪录的课程号在课程表中是否存在，若其中有一项为否，则拒绝添加操作，并显示“违反数据一致性”提示信息。Go If exists(select name from sysobjects where name='add_student'and type='tr')drop trigger add_student go

create trigger add_student on sc for insert as go 第四部分：数据库完整性

1、实施worker表的“性别”字段默认值为“男”的约束 create default default_sex as '男' go sp_bindefault'default_sex','worker.性别'

2、实施salary表的“工资”字段值在0~9999的约束、create rule salary_rule as @salary='[0~9999]' go sp_bindrule 'salary_rule','salary.工资'

3、实施depart表的“部门号”字段值唯一的非聚集索引的约束

4、为worker表建立外键“部门号”，参考表depart的“部门号”列。

5、建立一个规则 sex：@性别=’男’OR @性别=’女’,将其绑定到worker表的“性别”列上。Create rule sex as @性别='男'OR @性别='女' Go Sp_bindrule 'sex','worker.性别'

6、删除1小题所建立的约束。

7、删除2小题所建立的约束。

8、删除3小题所建立的约束

9、删除4小题所建立的约束

10.解除5小题所建立的绑定并删除规则sex

第五篇：数据库上机实验8实验报告

上机实验八——完整性约束的实现

一、实习目的：

掌握SQL中实现数据完整性的方法，加深理解关系数据模型的三类完整性约束。

二、实习准备：

1.复习“完整性约束SQL定义”

2.完成习题四第10题中的各项操作的SQL语句。

3.了解SQL Server 中实体完整性、参照完整性和用户自定义完整性的实现手段

三、实习内容：

1.验证习题四第10题四个表结构的SQL语句。

表一：Sstudent CREATE TABLE Sstudent(Sno char(7)NOT NULL PRIMARY KEY, Sname VarChar(20)NOT NULL, Ssex Char(2)NOT NULL DEFAULT('男')check(Ssex IN('男','女')), Sage smallint check(Sage >14 AND Sage<65), Clno Char(5)NOT NULL REFERENCES Cclass(Clno)ON UPDATE CASCADE);

表二：Ccourse CREATE TABLE Ccourse(Cno Char(1)NOT NULL PRIMARY KEY, Cname VarChar(20)NOT NULL, Credit Smallint CHECK(Credit IN(1,2,3,4,5,6)));

表三：Cclass CREATE TABLE Cclass(Clno Char(5)NOT NULL PRIMARY KEY, Speciality VarChar(20)NOT NULL, Inyear Char(4)NOT NULL, Number Integer CHECK(Number>1 AND Number<100), Mointor Char(7)REFERENCES Student(Sno));

表四：Ggrade CREATE TABLE Ggrade(Sno Char(7)NOT NULL REFERENCES Student(Sno)ON DELETE CASCADE ON UPDATE CASCADE, Cno Char(1)NOT NULL REFERENCES Course(Cno)ON DELETE CASCADE ON UPDATE CASCADE, Gmark Numeric(4,1)CHECK(Gmark>0 AND Gmark<100), PRIMARY KEY(Sno,Cno));

2.SQL Server中提供了那些方法实现实体完整性、参照完整体和用户自定义完整性

答：实体完整性：是通过主码的定义（PRIMARY KEY）来实现的；

参照完整性：是利用外部码（REFERENCES）的说明，以限制相关表中某些属性的取值，当用户违反规则时，提供三种：RESTRICT（限制策略）,CASCADE(级联策略),SET NULL(置空策略)；

用户自定义完整性：check约束，对元组的CHECK约束