数据库原理与应用课程 实验指导书(附答案) 下载本文

数据库原理与应用课程实验指导书

2、用户自定义数据类型定义、使用和删除

用SQL命令定义一名为Customer_id的用户自定义数据类型,要求char(6),NOT NULL, 并把该自定义数据类型用来定义XSS表中的客户编号,然后删除该自定义数据类型,请叙述该过程,并写出相关语句。

sp_addtype 'Customer_id','char(6)','not null' sp_droptype 'Customer_id'

先用创建语句自定义数据类型,然后打开“数据库-CPXSB-表”,右击XSS,选择“设计表”,将客户编号的数据类型 改为Customer_id,保存即可

将客户编号的数据类型改回char(6),然后用第二个语句删除该用户自定义数据类型

3、T-SQL语言编程

(1)用T-SQL语言编程输出3~300之间能被7整除的数。 declare @i int,@j int set @i=3 set @j=7

while @i<=300 begin

if(@i%@j)=0 print @i

set @i=@i+1 end

(2)用T-SQL语言编程输出3~100之内的素数。 declare @i int,@j int,@t int set @i=3

while @i<100 begin

set @j=2 while @j<@i begin

set @t=@i%@j if @t=0 break set @j=@j+1

45

if(@j>=@i) print @i end

set @i=@i+1 end

实验十

1、函数的定义

对于CPXS数据库,定义完成如下功能的函数: (1)据产品名称,查询该产品的相关信息;(函数名为FU_CP) create function FU_CP(@产品名称 char(30)) returns table as return

select * from CP where 产品名称=@产品名称

(2)按某年某季度统计给定产品名称的销售数量及销售金额;分别用名为FU1_CPXS内嵌表值函数和名为FU2_CPXS的多语句表值函数。

create function FU1_CPXS(@year int,@quarter int,@产品名称 char(30)) returns table as return

select 产品名称,sum(数量) as 销售数量,sum(销售额) as 销售金额 from CP,CPXSB

where CP.产品编号=CPXSB.产品编号 and datepart(yy,销售日期)=@year and datepart(qq,销售日期)=@quarter and 产品名称=@产品名称 group by 产品名称

create function FU2_CPXS(@year int,@quarter int,@产品名称 char(30)) returns @f2 table (

产品名称 char(30), 销售数量 int, 金额 real ) as begin

insert into @f2

select 产品名称,sum(数量) as 销售数量,sum(销售额) as 金额 from CP,CPXSB

where CP.产品编号=CPXSB.产品编号 and datepart(yy,销售日期)=@year

46

数据库原理与应用课程实验指导书

and datepart(qq,销售日期)=@quarter and 产品名称=@产品名称 group by 产品名称 return end

(3)根据销售商名称,统计其在某年某季度内销售商品名称、数量及金额。(函数名为FU3_CPXS)

create function FU3_CPXS(@客户名称 char(30),@year int,@quarter int) returns table as return

select 客户名称,产品名称 as 销售商品名称,数量,销售额 as 金额 from CP,XSS,CPXSB

where CPXSB.产品编号=CP.产品编号 and CPXSB.客户编号=XSS.客户编号

and 客户名称=@客户名称 and datepart(yy,销售日期)=@year and datepart(qq,销售日期)=@quarter

2、函数的调用

(1)对函数FU_CP,查询产品名称为“mp3”的产品情况; select * from FU_CP('MP3')

(2)对函数FU1_CPXS,查询2004年第3季度彩色电视机的销售数量和销售金额; select * from FU1_CPXS(2004,3,'彩色电视机')

(3)对函数FU2_CPXS,查询2004年第1季度洗衣机的销售数量和销售金额;

select * from FU2_CPXS(2004,1,'洗衣机')

(4)对函数FU3_CPXS,查询广电公司2004年第1季度销售的产品名称、销售数量和销售金额。

select * from FU3_CPXS('广电公司',2004,1)

47

实验十一 对于CPXS数据库,完成如下操作: 1、索引的创建和删除

(1)对CP表,在产品名称上定义一个唯一非聚簇的索引ind_cp。 create unique nonclustered index on CP(产品名称)

(2)先创建各客户购买产品的情况VIEW1视图,包括客户编号、客户名称、产品编号、产品名称、

价格,购买日期、购买数量,然后在客户编号+产品编号+购买日期定义一个唯一聚簇索引ind_view1。(请注意7个SET选项设置)。 create view view1 with schemabinding as

select XSS.客户编号,客户名称,CP.产品编号,产品名称,价格,销售日期 as 购买日期,数量 as 购买数量

from dbo.CP,dbo.XSS,dbo.CPXSB

where XSS.客户编号=CPXSB.客户编号 and CP.产品编号=CPXSB.产品编号

SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET ARITHABORT ON

SET CONCAT_NULL_YIELDS_NULL ON SET QUOTED_IDENTIFIER ON

SET NUMERIC_ROUNDABORT OFF

create unique clustered index ind_view1 on dbo.view1(客户编号,产品编号,购买日期)

2、索引的删除

删除ind_view1索引。; drop index view1.ind_view1

48