『SQL Server』考二

没有8，9，至10（含）

第一套

商品表（商品号，商品名，单价）
销售表（销售单据号，商品号，销售时间，销售数量，销售单价）。
商品号，单价，销售数量，销售单价均为整型
请编写查询某年某商品的销售总毛利的存储过程，毛利=销售数量（销售单价-单价）
要求商品号和年份为输入参数，总毛利用输出参数返回。
CREATE PROC p_name @商品号 int,@年份 int,@毛利 int output
AS
DECLARE @销售量 int,@单价 int,@销售单价 int
BEGIN
SELECT @单价 = 单价 FROM 商品表 WHERE 商品号=@商品号
SELECT @销售单价= 销售单价, @销售量= sum(销售数量)FROM 销售表 WHERE
@商品号=商品号 AND 销售时间=@年份
IF @单价 IS NULL THEN /有？/
ROLLBACK;
RETURN;
END IF
IF @销售单价 IS NULL THEN/卖？/
ROLLBACK;
RETURN;
END IF
SET @毛利=(@销售单价-@单价)@销售量
end

第二套

在SQL Server 2008中，设某数据库中有商品表（商品号，商品名，进货价格)，商品号为主码;销售表（商品号，销售时间，销售数量，销售价格，本次利润)，商品号和销售时间为主码，销售价格为本次销售商品的单价。现要求每当在销售表中插入前4列数据时（假设—次只插入一行数据)，系统自动计算本次销售产生的利润，并将该利润赋给销售表的第5列”本次利润”。请编写实现上述功能的后触发型触发器代码。(10分)
CREATE TRIGGER tri_name ON 销售表 FOR INSERT
AS
DECLARE @进价 money, @销量 int,@售价 money,@利润 money
SElECT @进价 = 进货价格 FROM 商品表
SELECT @销量 = 销售数量,@售价 = 销售价格 FROM inserted
SET @利润 = @销量*(@售价-@进价)
UPDATE 销售表 SET 本次利润 = @利润
WHERE 商品号 IN(SELECT 商品号 FROM inserted)
AND 销售时间 IN(SELECT 销售时间 FROM inserted)
END

第三套

设在SQL Server 2008某数据库中，已建立了四个文件组: fg1、fg2、fg3和fg4，以及一个分区函数RangePF1。RangePF1的定义代码如下:
CREATE PARTITION FUNCTION RangePF1(int)AS RANGE LEFT FOR VALUES(100,200,300)
(1)请基于RangePF1创建一个分区方案RangePS1，每个分区对应一个文件组。(5分)
(2）请使用RangePS1创建一个分区表PartitionT(Gid, GoodName, Price)，该表基于Gid列创建分区。其中Gid: int类型，取值大于等于1; GoodName:统一字符编码可变长类型，最多可存储20个汉字;Price:定点小数类型，小数部分1位，整数部分到千位。(5分)
[][创建分区方案 方案名 as 分区 函数名 to （文件组）]
[][CREATE TABLE tb_name （）on 方案名（Gid）]
1:
create partition scheme RangePS1
as partition RangePF1
to(fg1,fg2,fg3,fg4)
2:
create table orders
(
GID int identity(1,1) primary key,
GoodName varchar(40),
Price numeric(5,1)
)
on RangePS1(GID)

第五套

设有图书管理数据库，包含三张表:图书明细表(图书编号，图书类别，图书名称，作者，出版社，出版日期，定价);读者表（借书证号，姓名，系别，办证日期);借出信息表（借出编号，借书证号，图书编号，借书日期);完成下列操作:(10分)定义一个多语句表值函数，用于查询学生借书情况，只需提供参数:借书证号，就可以通过调用函数返回此学生的借书情况，若有借书籍，则返回所借书籍的编号、书籍名称、定价和借书日期;若没有在借书籍，则返回记录为空。设函数名为: f_BorrowBook(@jszh char(20))。

CREATE FUNCTION f_BorrowBook(@jszh char(20))
RETURNS @jsqkb TABLE(书籍编号 char(20),书籍名称 char(50),定价 float,借书日期 datetime)
AS
BEGIN
INSERT INTO @jsqkb
SELECT图书明细表.图书编号,图书名称,定价,借出信息表.借书日期 FROM 借出信息表,图书明细表
WHERE 借出信息表.图书编号=图书明细表.图书编号 AND 借出信息表.借书证号=@jszh
RETURN
END

第七套

BORROWER：
借书证号 姓名 系名 班级
12011106 张三 计算机 12-1
12011107 李四 计算机 12-1
12012113 王五 语文 12-2

LOANS??
借书证号 图书馆登记号 借书日期
12011106 T001001 2012.01.02
12012113 T001026 2013.02.06

BOOKS??
索书号 书名 作者 图书登记号 出版社 价格
TP311.1 数据库 李明 T001001 人民 19.00
TP311.2 二级C语言 王珊 T001026 科学 32.00
1.检索至少借了五本书的同学借书证号，姓名，系名，借书数量
2.检索借书和王五同学所借图书的任意一本相同的学牲姓名，系名，书名和借书日期
SELECT LOANS.借书证号,姓名,系名,COUNT() AS 借书数量
FROM BORROWER,LOANS
WHERE BORROWER.借书证号=LOANS.借书证号
GROUP BY LOANS.借书证号,姓名,系名
HAVING COUNT()>=5;

SELECT 姓名,系名,书名,借书日期
FROM BORROWER,LOANS , BOOKS
WHERE
BORROWER.借书证号=LOANS.借书证号 AND LOANS.图书馆登记号=BOOKS.图书馆登记号
AND 索书号 IN
(SELECT 索书号 FROM BORROWER,LOANS,BOOKS
WHERE BORROW.借书证号=LOANS.借书证号
AND LOANS.图书馆登记号=BOOKS.图书馆登记号
AND 姓名=”王五”)
group by 姓名,系名,书名,借书日期
having 姓名 !=’王五’

??????????????????????????????????????塢?????????????????? ?洢????

CREATE TABLE 商品表(
商品号 char(10) PRIMARY KEY,
商品名 varchar(40),
类别 varchar(20),
进货单价 int,
销售总量 int default 0)
CREATE TABLE 销售表(
商品号 char(10) REFERENCES 商品表(商品号),
销售时间 datetime,
销售数量 int,
销售单价 int,
PRIMARY KEY(商品号,销售时间))
9.12.创建存储过程:指定类别的每种商品今年的销售总数量

create proc p_name @lb varchar(20)
as
select 商品名, (select sum(销售数量) from 销售表 a 
where a.商品号=b.商品号 and year(销售时间)=year(getdate())) as 销售总数量
from 商品表 b
where b.类别=@lb

8.11.创建存储过程:指定类别的每种商品今年的销售总金额

create proc p_name @lb varchar(20)
as
select 商品名, (select sum(销售数量*销售单价) from 销售表 a
where a.商品号=b.商品号 and year(销售时间)=year(getdate())) as 销售总金额
from 商品表 b
where b.类别=@lb

13.创建存储过程:指定类别的每种商品去年的销售总数量

create proc p_name @lb varchar(20)
as
select a.商品号,商品名,sum(销售数量) as 销售总数量
from 商品表 a join 销售表 b
on a.商品号=b.商品号
where year(销售时间)=year(dateadd(year,-1,getdate()))
and 类别=@lb
group by a.商品号,商品名

17.创建存储过程:指定日期范围每种商品的销售总数量,包括未销售的商品.

create proc p1 @start_date date, @end_date date
as
select 商品表.商品号,sum(销售数量) from 商品表
left join 销售表 on 商品表.商品号=销售表.商品号
where 销售时间 between @start_date and @end_date
group by 商品表.商品号

19.创建存储过程:指定商品号的商品销售总数量

create proc p1 @model varchar(10),@total int output
as
set @total=(select sum(销售数量) from 销售表 s 
join 商品表 c on s.商品号=c.商品号
where 商品号=@model)

14.创建存储过程:指定类别的每个商品的销售总利润,包括未销售的(值为0)

create proc p_Profit @lb char(10)
as
select 商品表.商品号,
case
when 销售表.商品号 is null then 0
else sum((销售单价-进货单价)*数量)
end as 销售总利润
from 商品表 left join 销售表 on 商品表.商品号=销售表.商品号
where 类别=@lb
group by 商品表.商品号

16.标量函数:指定商品号返回销售总金额

create function dbo.f_TotalQty(@GoodID char)
returns int
as
begin
return(select sum(销售数量*销售单价)from 商品表 join 销售表
on 商品表.商品号=销售表.商品号
where 商品号=@GoodID)
end

4.多语句表值函数: 返回类别参数的每种商品在2012年的销售总利润,并降序输出

create function f_Profit(@lb char(10))
returns @ProfitTable table(商品号 char(10),总利润 int)
as
begin
insert into @ProfitTable
select a.商品号,sum(销售数量*(销售单价-进货单价)) as 总利润
from 销售表 a join 商品表 b on a.商品号=b.商品号
where a.商品号 in(select 商品号 from 商品表 where 类别=@lb)
group by a.商品号
order by 总利润 desc
return @ProfitTable
end

10.6.多语句表值函数：统计指定年份中每个商品的销售总额

create function fun_name(@year int)
returns @fun_name table(商品号 char(10),销售总金额 int)
as
begin
insert into @fun_name
select a.商品号,sum(a.销售单价*b.销售数量) from 商品表 a
join 销售表 b on a.商品号=b.商品号
where year(b.销售时间)=@year
group by a.商品号
return
end

15.18.创建触发器:每当在销售表中插入一条销售记录, 修改商品表中对应商品的销售总量

create trigger tri_name on 销售表 for insert
as
update 商品表
set 销售总量=商品表.销售总量+(select 销售数量 from inserted)
where 商品号 in(select 商品号 from inserted)

create trigger tri_name on 销售表 after insert
as
declare @Sales_Amount int
set @Sales_Amount=(select 销售数量 from inserted)
update 商品表 set 销售总量=商品表.销售总量+@Sales_Amount
where 商品号 in (select 商品号 from inserted)

第二十套

CREATE TABLE 出租表(
房屋号 char(10),
出租日期 datetime,
租期 int not null, –月数
到期日期 datetime,
PRIMARY KEY(房屋号,出租日期));
创建触发器:每当在出租表插入一行数据(房屋号,出租日期,租期),自动计算到期日期
CREATE TRIGGER tri ON 出租表 FOR insert
AS
update 出租表 SET 到期日期 = dateadd(month,租期,出租日期)
WHERE 房屋号 = (SELECT 房屋号 FROM inserted )
and 出租日期=(select 出租日期 from inserted)

?????????????? ????????洢????

create table 房屋表(
房屋号 char(10) primary key,
房屋地址 char(20) not null,
面积 int,
月租金 int
)
create table 租贷表(
房屋号 char(10),
租贷日期 datetime,
租贷月数 int not null,
本次总租金 int,
primary key(房屋号,租贷日期),
foreign key(房屋号) references 房屋表(房屋号)
)
创建触发器:每当在租贷表插入一行数据（房屋号，租贷日期，租贷月数），计算本次月租金

create trigger tri_name on 租贷表 for insert
as
declare @x int 
set @x=(select 月租金 from 房屋表 where 房屋号=
(select 房屋号 from inserted))
update 租贷表 set 本次月租金=租贷月数*@x
from 租贷表 a join 房屋表 b on a.房屋号=b.房屋号
and a.租贷日期=b.租贷日期

创建存储过程：输入房屋号，计算历史租金总和

create proc p1 @room char(10),@total int output
as
set @total=(select sum(租贷月数*月租金) from 房屋表
join 租贷表 on 房屋表.房屋号=租贷表.房屋号
where a.房屋号=@room)