大型数据库课堂测验答案

课堂测验

一、程序设计题

1. 创建一个自定义函数Date_Now来显示当前日期和时间，可使用GETDATE()

和DATENAME()函数。例如当前时间是：2010-11-20 23:45:40，函数返回显示：“今天是2010年11月20日23时45分40秒星期六”。

create function Date_Now() returns table as

return(SELECT '今天是'+DATENAME(yy,getDate())+'年' + DATENAME(mm,getDate())+'月' + DATENAME(dd,getDate())+'日' + DATENAME(hh,getDate())+'时' + DATENAME(mi,getDate())+'分' + DATENAME(ss,getDate())+'秒' + DATENAME(dw,getDate()) as 返回 )

select * from Date_Now()

2. 下面是一段程序代码，请回答问题。

CREATE FUNCTION mydefine_fun (@var1 int,@var2 int,@var3 int) RETURNS int AS

BEGIN

DECLARE @result int

SET @result=(@var1+@var2)*@var3 RETURN @result END

试分析程序的功能是什么？并解释参数和返回值的作用？调用程序代码如下 select dbo.mydefine_fun(1,2,3) ，返回显示结果为 9 。

二、触发器设计题

有一数据库，包含两张表：学生（学号，姓名，专业名称，性别，课程号，成绩） 课程（课程号，课程名称，课程类型，学分）表中数据如下： 学生表 学号 10201 10201 10203 10204 课程表 课程号 1 2 3

姓名 王停 王停 李小宏 张忠 课程名称 高等数学 专业名称 英语 英语 电子商务 生物工程 课程类型 必修 性别 女 女 男 男 学分 2 2 1 课程号 1 3 2 2 成绩 75 87 81 70 翻译与写作 必修 政治与形势 选修

1. 为学生表创建触发器S_insert，当向学生表中插入数据时，要求学号必须以

“10”开头，且课程号必须在课程表中，否则取消插入操作。

当向学生表中插入数据时：

INSERT INTO 学生 VALUES('00702','秦永','机械工程','男',2,78) INSERT INTO 学生 VALUES('10702','秦永','机械工程','男',12,78) 它们的运行结果分别是什么？

CREATE TRIGGER S_insert ON 学生

FOR INSERT AS

DECLARE @S_no VARCHAR(4), @S_cno INT SELECT @S_no= 学号, @S_cno=课程号 FROM INSERTED

IF (LEFT(@S_no,2)!='10') BEGIN

ROLLBACK TRANSACTION RAISERROR('输入的学号：%s不是10级的学生，请确认后重新录入！',16,1, @S_no) END

IF(@S_cno NOT IN (SELECT CNO FROM COURSE)) BEGIN

ROLLBACK TRANSACTION

RAISERROR('输入的课程号：%d在课程表中不存在，请确认后重新录入！',16,1, @S_cno) END

INSERT INTO 学生 VALUES('00702','秦永','机械工程','男',2,78)

由于插入数据的学号为“00702”，并不是以“10”开头，所以执行S_insert触发器时，将执行“ROLLBACK TRANSACTION”语句，取消完成的工作，并执行RAISERROR语句给出错误信息。运行结果如下。

输入的学号：00702不是10级的学生，请确认后重新录入！

INSERT INTO 学生 VALUES('10702','秦永','机械工程','男',12,78)

由于课程号12在课程表中不存在，所以执行S_insert触发器时，将执行“ROLLBACK TRANSACTION”语句，取消工作，并执行RAISERROR语句给出错误信息。运行结果：

输入的课程号：12在课程表中不存在，请确认后重新录入！

2. 为学生表创建DELETE触发器S_delete，当一次删除的记录大于一行或者删

除记录的课程为必修课时，取消删除操作。部分代码如下：

CREATE TRIGGER S_delete ON 学生

FOR DELETE AS

DECLARE @rowcount int

SELECT @rowcount=@@ROWCOUNT --@@ROWCOUNT返回受上一语句影响的行数

IF @rowcount>1 BEGIN

Rollback transaction

RAISERROR('当前要删除的记录数为%d,一次只允许删除一行记录！',16,1, @rowcount) END

DECLARE @S_cno INT SELECT @S_cno=课程号 FROM DELETED

IF( @S_cno in (select 课程号 from 课程 where 课程类型=’必修’) ) BEGIN

ROLLBACK TRANSACTION

RAISERROR('删除记录的课程为必修课，不允许删除！',16,1) END

当通过如下语句从学生表中删除数据时： DELETE 学生 WHERE 学号=’10201’

DELETE 学生 WHERE 学号=’10201’ AND 课程号=1 它们的运行结果是分别是什么？

DELETE 学生 WHERE 学号=’10201’，结果如下： 当前要删除的记录数为2,一次只允许删除一行记录！

DELETE 学生 WHERE 学号=’10201’ AND 课程号=1 ，结果如下 删除记录的课程为必修课，不允许删除！

说明 在SQL Server中执行TRUNCATE TABLE语句，从表中删除所有的行时，不会触发DELETE触发器。

3. 为课程表创建UPDATE触发器C_update，当课程表的课程号和课程名称列（第

1、2列）被更新时，触发器给出提示信息，该两列不能被更新，并回滚事务。当其余的列（第3、4列）被更新时，触发器将更新前后的数据写入C_UpInfo表中。实例代码如下。

（1）．创建跟踪信息表C_UpInfo CREATE TABLE C_UpInfo (

Oper_Time smalldatetime,--更新时间

Date_Type CHAR(3),--更新类型：值只能为old或new CNO int ,--课程号

CNAME char(30),--课程名称 CTYPE char(4),--课程类型 SCOUNT int,--学分 )

（2）．创建UPDATE触发器C_update

CREATE TRIGGER C_update ON 课程

FOR UPDATE AS

IF（COLUMNS_UPDATED()&3）>0 --第1、2列被更新时 BEGIN

ROLLBACK TRANSACTION

RAISERROR('课程表的课程号和课程名中的数据不允许被更新！',16,1) END

IF（COLUMNS_UPDATED()&12）>0 --第3、4列被更新时 BEGIN

--将更新前的数据插入到C_UpInfo表中（deleted表）

Insert into C_UpInfo(Oper_Time,Date_Type,CNO,CNAME,CTYPe,SCOUNT_) Select getdate(),’old’,d.课程号,d.课程名称,d.课程类型,d.学分 From Deleted as d

--将更新后的数据插入到C_UpInfo表中（inserted表）

Insert into C_UpInfo(Oper_Time,Date_Type,CNO,CNAME,CTYPe,SCOUNT_) Select getdate(),’new’,i.课程号,i.课程名称,i.课程类型,i.学分 From Inserted as i END

当通过下面的代码更新课程表的课程名称列的值： UPDATE 课程

SET 课程名 = '线性代数' WHERE 课程号 = 2 运行后的结果是什么？

课程表的课程号和课程名中的数据不允许被更新！

当更新课程号和课程名称以外的值： UPDATE 课程

SET 课程类型=选修,学分=1 WHERE 课程号 = 2

这时使用 select * from C_UpInfo运行后的结果是什么？ 2010-11-21 old 2 高等数学 必修 2 2010-11-21 New 2 高等数学 选修 1 三、存储过程设计题

产品销售数据库中包含如下几张表：product(产品表)、employee(员工表)、Customer(顾客表)、categories(产品类别表)、orders(订单表)、 orderDetail(订单详表)等。其中，product表结构为：产品编号、产品名、类别号、单价、库存量、产品描述。Employee表结构为：员工编号、姓名、性别、入厂时间、电话号码、地址。Customer表结构为：顾客编号、姓名、性别、联系电话、地址。Categories表结构为：类别号、类别名称、详细描述。 Orders表结构为：订单号、顾客编号、员工编号、订单日期、发货日期、订单描述。orderDetail表结构为：订单号、产品编号、单价、数量、折扣、备注。

1. 试分析orders表和orderDetail表之间的关系。(一对多)

2. 创建一个存储购物车信息的表ShoppingCart，然后创建一个存储过程

ShoppingCartAddItem，将参数指定的产品ID及要选购的产品数量添加到参数指定的购物车中。 创建购物车的代码如下： Create table ShoppingCart(

cartID varchar(20) not null,--购物车编号 quantity int default(1) not null,--选购数量

productID int not null foreign key references product(产品编号), dateCreated datetime default(getdate()) not null,--选购日期 constraint PK_ShoppingCart primary key (cartID,productID)—主键 )

创建存储过程的部分代码如下：

Create procedure ShoppingCartAddItem( @CartID varchar(20), @ProductID int, @Quantity int=1) As If Exists( Select productID from ShoppingCart where productID=@ProductID and cartID=@CartID )

/*购物车中有指定的产品，则更新该产品的数量*/

Begin

Declare @countItems int

Select @countItems =ShoppingCart.quantity From ShoppingCart

Where productID=@ProductID and cartID=@CartID

Update shoppingCart set quantity=(@Quantity+@countItems) Where productID=@ProductID and cartID=@CartID End Else

/*购物车中没有指定的产品，则向购物车添加一条该产品的记录*/

Insert into ShoppingCart( cartID,Quantity,productID) Values(@CartID,@Quantity,@ProductID) Go

3. 创建存储过程ShoppingCartEmpty来清空购物车。 Create procedure ShoppingCartEmpty( @CartID varchar(20) ) As

Detele from ShoppingCart where cartID=@CartID Go

4. 创建存储过程ShoppingCartTotal返回购物车中产品的总价格。 Create procedure ShoppingCartTotal( @CartID varchar(20), @TotalCost money OUTPUT ) As

Select @TotalCost=sum(product.单价*ShoppingCart.quantity) From product,ShoppingCart

Where product.产品编号=ShoppingCart.productID and ShoppingCartID=@CartID Go

5. 创建存储过程ShoppingCartRemoveItem删除购物车中指定的产品。 Create procedure ShoppingCartRemoveItem( @CartID varchar(20), @ProductID int ) As

Delete from ShoppingCart

Where cartID=@CartID and productID=@ProductID

6. 创建存储过程OrdersList获取指定用户的订单列表。 Create procedure OrdersList (

@CustomerID int ) As

Select orders.订单号,订单日期，发货日期，orderDetail.产品编号，数量 From orders,orderDetail

Where orders.订单号=orderDetail.订单号 and Orders.顾客编号=@CustomerID

4. 创建存储过程ShoppingCartTotal返回购物车中产品的总价格。 Create procedure ShoppingCartTotal( @CartID varchar(20), @TotalCost money OUTPUT ) As

Select @TotalCost=sum(product.单价*ShoppingCart.quantity) From product,ShoppingCart

Where product.产品编号=ShoppingCart.productID and ShoppingCartID=@CartID Go

5. 创建存储过程ShoppingCartRemoveItem删除购物车中指定的产品。 Create procedure ShoppingCartRemoveItem( @CartID varchar(20), @ProductID int ) As

Delete from ShoppingCart

Where cartID=@CartID and productID=@ProductID

6. 创建存储过程OrdersList获取指定用户的订单列表。 Create procedure OrdersList (

@CustomerID int ) As

Select orders.订单号,订单日期，发货日期，orderDetail.产品编号，数量 From orders,orderDetail

Where orders.订单号=orderDetail.订单号 and Orders.顾客编号=@CustomerID

本文来源：https://www.bwwdw.com/article/rgpt.html