nosql期末考试题 sql考试题及答案

十一、综合题（每题20分，共40分）

1、某服装销售公司拟开发一套服装采购管理系统，以便对服装采购和库存进行管理。经过需求分析和概念设计、逻辑设计阶段最后得到的关系模式包括：

库管员（库管员编号，姓名，级别）Storekeeper（sno,sname,level）

仓库信息（仓库编号，仓库位置，仓库容量）Storehouse(stno,stadress,stvolume)

服装（服装编码，服装描述，服装类型，尺码，面料，销售价格）Dress(dno，ddescribe,dtype,dsize,dplus,dprice)供应商（供应商编码，供应商名称，地址，联系电话，企业法人）Supplier(suno,suname,suaddress,sutel,superson)关系模式之间的关联关系为：每个仓库有一个库管员，一个仓库管理员可以管理多个仓库；每种服装有一个供应商，每个供应商提供多种类型的衣服；每种衣服放在同一个仓库里，每个仓库里存放多种类型的衣服。

请完成以下题目：

1）请用把以上四个关系模式用SQL创建到数据中（每个创建表的语句2分，共计8分）

2）假设表中已经存在以下数据，请完成a)~e)中SQL语句的编写（每个2分，共12分）

库管表中的数据：

仓库表中的数据：

供应商表中的数据：

服装表中的数据：

1. 请查询存放在1号楼201仓库中中的服装信息
2. 请查询河北童泰服装厂生产的服装信息存储的仓库信息
3. 由于库存销售量上升，现在“女士古典旗袍”的尺码已经不全了，只剩下155~165的号了，请修改该服装的尺码信息
4. 库管员孙某某的离职，现在他的库管工作全部由新来的员工李爽承担，请将李爽的信息插入到库管员表中（李爽的等级是3级），并把原来的孙某某的库房指定给李爽管理。
5. 由于换季，现在女士连衣裙已经下架不再销售，请将服装中的女士连衣裙删除。

2、根据第一题中的需求描述，即某服装销售公司拟开发一套服装采购管理系统，编写以下数据库程序。

1）编写一个自定义函数实现按照某个的库管员查找其所管辖的仓库中的服装的供应商的联系人。（5分）

2）编写一个存储过程，将参数指定的服装信息插入到数据库dress表中。（5分）

3）定义一个触发器，在插入供应商信息的时候检查联系人不能为空值（5分）

4）定义一个游标，实现统计服装信息中价格在500元以内的服装数量。（5分）

答案：

1.  1)创建表的SQL语句：（每个创建表的SQL2分，共8分）

--创建库管员表
create table Storekeeper(
sno int primary key,
sname varchar(20),
level char(2)
)
--创建仓库表
create table Storehouse(
stno int primary key,
staddress varchar(100),
stvolume  int,
sno int,
foreign key (sno) references  Storekeeper(sno)
)
--创建供应商表
create table Supplier(
suno int primary key,
suname varchar(20),
suaddress varchar(100),
sutel varchar(20),
superson varchar(20)
)
--创建服装表
create table Dress(
 dno int primary key,
 ddescribe varchar(100),
 dtype varchar(20),
dsize varchar(20),
 dplus varchar(20),
 dprice int,
 stno int,
 suno int,
 foreign key (stno) references Storehouse(stno),
 foreign key (suno) references Supplier(suno) 
)
2)（共计12分）
a)：select dress.* from dress,storehouse
where dress.stno=storehouse.stno
and storehouse.staddress='1号楼'(2分)
b)： select distinct storehouse.* from storehouse,dress,supplier
where dress.stno=storehouse.stno and supplier.suno=dress.suno
and supplier.suname='河北童泰服装'（2分）
c)：update dress set dsize='155~165' where ddescribe='女士古典旗袍'（2分）
d): insert into storekeeper values(4,'李爽',3)（2分）
update storehouse set sno=4 where sno=3（2分）
e)：delete from dress where ddescribe='女士连衣裙'（2分）
2. 1）自定义函数：（5分）
create function fun1(@name varchar(20))
returns varchar(20)
as
begin
return 
 (select superson from supplier
 where suno in(
      select suno from dress,Storehouse
      where dress.stno=storehouse.stno
      and storehouse.sno=(select sno from storekeeper
        where sname=@name)
 ))
end 
2）自定义存储过程：（5分）
create proc myproc1
@dno int,
@ddescribe varchar(100),
@dsize varchar(20),
@dplus varchar(20),
@dprice int,
@dtype varchar(20),
@stno int,
@suno int
as
 begin
 insert into dress values(@dno,@ddescribe,@dsize,
                           @dplus,@dprice,@dtype,@stno,@suno)
 end
3）触发器：（5分）
create trigger trigger1 on supplier
for insert
as
 begin 
 declare @name varchar(20) 
      select @name=superson from supplier
      if(@name is null)
      RAISERROR ('联系人的名字不能为null', 16, 10)
         ROLLBACK TRANSACTION
 End
4）声明游标：（5分）
--声明游标
declare cursor1 cursor
for select dprice from dress
for read only
--打开游标
open cursor1
--定义变量
declare @num int,@price int
set @num=0
--取游标中的数据
fetch next from cursor1 into @price
--循环
 while(@@FETCH_STATUS=0)
 begin
      if(@price<500)
             set @num=@num+1;  
      fetch next from cursor1 into @price
 end
 print @num
--关闭游标
 close cursor1
--释放游标
 deallocate cursor1