DBMS Chapter3

1, Salvaging_抢修工程计划表

create table Salvaging(
    --列级完整性约束，prj_num是主码
    prj_num char(8) primary key,
    prj_name varchar(50),
    start_date datetime,
    end_date datetime,
    prj_status bit,
    
    --表级完整性约束
    check(end_date >= start_date)
    --系统自动给起名字
);

2, Stock_配电物资库存记录表

create table Stock(
    mat_num char(8) primary key,
    mat_name varchar(50) not null,
    speci varchar(50) not null,
    warehouse varchar(50),
    amount int check(amount > 0),
    unit decimal(18, 2),
    total as ([amount] * [unit])
    --as为自动计算字段，不能输入值，表示：总金额=数量*单价
);

3、Out_stock_配电抢修物资领料出库表

create table Out_stock(
    prj_num char(8),
    mat_num char(8),
    amount int,
    get_date datetime,
    department varchar(100),
    primary key(prj_num, mat_num),
    
    --主码由2个属性构成，必须作为表级完整性约束
    foreign key (prj_num) references Salvaging(prj_num),
    foreign key (mat_num) references Stock(mat_num)
);

1, 向 Salavging_抢修工程计划表, 添加数据

insert
into salvaging(prj_num, prj_name, start_date, end_date, prj_status)
values
('20100015', '220kV清经线接地箱及接电线被盗抢修', '2010-10-12', '2010-10-13',1),
('20100016', '沙河站2#公变出线电缆老化烧毁抢修', '2010-11-05', '2010-11-05',1),
('20100001', '西丽站电缆短路烧毁抢修工程', '2011-01-03', '2011-01-03',1),
('20100002', '西丽站电缆接地抢修', '2011-01-03', '2011-11-05',1),
('20100003', '观澜站光缆抢修', '2011-02-10', '2011-02-11',1),
('20100004', '小径墩低压线被盗抢修', '2011-02-15', '2011-02-15',1),
('20100005', '明珠立交电缆沟盖板破损抢修', '2011-03-02', '2011-03-05',0),
('20100010', '朝阳围公变低压线被盗抢修', '2011-03-08', '2011-03-10',0);

2, 向stock_配电物资库存记录表, 添加数据

insert
into Stock(mat_num, mat_name, speci, warehouse, amount, unit)
values
('m001', '护套绝缘电线', 'BVV-120', '供电局1#仓库', 220, 89.80),
('m002', '架空绝缘导线', '10kV-150', '供电局1#仓库', 30, 17.00),
('m003', '护套绝缘电线', 'BVV-35', '供电局2#仓库', 80, 22.80),
('m004', '护套绝缘电线', 'BVV-50', '供电局2#仓库', 283, 32.00),
('m005', '护套绝缘电线', 'BVV-70', '供电局2#仓库', 130, 40.00),
('m006', '护套绝缘电线', 'BVV-150', '供电局3#仓库', 46, null),
('m007', '架空绝缘导线', '10kV-120', '供电局3#仓库', 85, 14.08),
('m009', '护套绝缘电线', 'BVV-16', '供电局3#仓库', 90, null),
('m011', '护套绝缘电线', 'BVV-95', '供电局3#仓库', 164, null),
('m012', '交联聚乙烯绝缘电缆', 'YJV22-15KV', '供电局4#仓库', 45, 719.80),
('m013', '户外真空断路器', 'ZW12-12', '供电局4#仓库', 1, 13600.00);

3, 向out_stock_配电抢修物资领料出库表, 添加数据

insert
into Out_stock(prj_num, mat_num, amount, get_date, department)
values
('20100015', 'm001', 2, '2010-10-12', '工程1部'),
('20100015', 'm002', 1, '2010-10-12', '工程1部'),
('20100016', 'm001', 3, '2010-11-05', '工程1部'),
('20100016', 'm003', 10, '2010-11-05', '工程1部'),
('20100001', 'm001', 2, '2011-01-03', '工程2部'),
('20100002', 'm001', 1, '2011-01-03', '工程2部'),
('20100002', 'm013', 1, '2011-01-03', '工程2部'),
('20100003', 'm001', 5, '2011-02-11', '工程1部'),
('20100003', 'm012', 1, '2011-02-11', '工程1部'),
('20100004', 'm001', 3, '2011-02-15', '工程1部'),
('20100004', 'm004', 20, '2011-02-15', '工程1部'),
('20100005', 'm001', 2, '2011-03-02', '工程1部'),
('20100005', 'm003', 10, '2010-10-12', '工程2部'),
('20100005', 'm006', 3, '2010-10-12', '工程2部'),
('20100010', 'm001', 5, '2010-10-12', '工程1部');

【例3.1】 建立一个"抢修工程计划表"Salvaging, 它由工程项目编号prj_num, 工程项目名称prj_name, 工程开始日期 start_date, 工程结束日期end_date, 是否按期完成prj_status, 共5个属性组成

create table Salvaging(
    prj_num char(8) primary key,
    prj_name varchar(50),
    start_date datetime,
    end_date datetime,
    prj_status big,
);

【例3.2】建立一个"配电物资库存记录表"Stock

create table Stock(
    mat_num char(8) primary key,
    mat_name varchar(50) not null,
    speci varchar(20) not null,
    warehouse cahr(20),
    amount int,
    unit decimal(18, 2),
    total as (amount * unit)
);

【例3.3】建立一个"配电物资领料出库表"Out_stock

create table Out_stock(
    prj_num char(8),
    mat_num char(8),
    amount int,
    get_date datetime default getdate(),
    department char(20),
    primary key(prj_num, mat_num),
    foreign key(prj_num) references Salvaging(prj_num),
    foreign key(mat_num) references Stock(mat_num)
);

【例3.4】 向抢修工程计划表 Salvaging 中增加 "工程项目负责人"列, 数据类型为 字符型

alter table Salvaging add prj_director varchar(10);

【例3.5】 删除抢修工程计划表Salvaging中"工程项目负责人"的属性列

alter table Salvaging drop column prj_director;

3.6, 将配电物资领料出库表Out_stock中领取数量的数据类型由字符型(假设原来的数据类型是字符型)改为正数

alter table Out_stock alter column amount int;

3.7, 删除配电物资领料出库表Out_stock

drop table Out_stock;

3,8 查询所有配电物资的物资编号, 物资名称, 规格

select mat_num, mat_name, speci
from Stock;

3.9, 查询所有配电物资的物资名称, 物资编号, 规格和所在仓库名称

select mat_name, mat_num, speci, warehouse
from Stock;

3.10, 查询所有配电物资的记录

select *
from Stock;

3.11, 查询所有抢修工程的抢修天数

select prj_num, start_date, end_date, datediff(day, start_date, end_date)
from Salvaging;

3.12, 查询所有抢修工程的抢修天数, 并在实际抢修天数列前加入一个列, 此列的每行数据均为"抢修天数"常量值

select prj_name, '抢修天数', datediff(day, start_date, end_date)
from Salvaing;

3.13, 在配电物资库存记录表中查询出所有仓库的名称

select distinct warehouse
from Stock;

3,14, 查询供电局1#仓库中存放的所有物资编号, 物资名称, 规格以及数量

select mat_num, mat_name, speci, amount
from Stock
where warehouse = '供电局1#仓库';

3.15, 查询所有单价小于80的物资名称, 数量 及 其单价

select mat_name, amount, unit
from Stock
where unit < 80;

3.16, 查询单价在50-100的物资名称, 数量及其单价

select mat_name, amount, speci
from Stock
where unit between 50 and 100;

3.17, 查询单价不在50-100的物资名称, 数量及其单价

select mat_name, amount, unit
from Stock
where unit not between 50 and 100;

3.18, 查询存放在供电局1#仓库 和 供电路2#仓库的物资名称, 规格及其数量

select mat_name, speci, amount
from Stock
where warehouse in('供电局1#仓库', '供电局2#仓库');

select mat_name, speci, amount
from Stock
where warehouse = '供电局1#仓库' or warehouse = '供电局2#仓库';

3.19, 查询既没有存放在供电局1#仓库, 也没有存放在供电局2#仓库的物资名称, 规格及其数量

select mat_name, speci, amount
from Stock
where warehouse not in('供电局1#仓库', '供电局2#仓库');

select mat_name, speci, amount
from Stock
where warehouse != '供电局1#仓库' and warehouse != '供电局2#仓库';

3.20, 查询存放在供电局1#仓库的物资的详细情况

select *
from Stock
where warehouse = '供电局1#仓库';

3.21, 查询所有绝缘电线的物资编号, 名称和规格

select mat_num, mat_name, speci
from Stock
where mat_name like '%绝缘电线';

3.22, 查询物资名称中第三, 四个字为 "绝缘"的物资编号, 名称 和 规格

select mat_num, mat_name, speci
from Stock
where mat_name like '__绝缘%';

3.23, 查询所有不带 "绝缘"两个字的物资编号, 名称 和 规格

select mat_num, mat_name, speci
from Stock
where mat_name not like '%绝缘%';

3.24, 查询物资名称含有 “户外_真空” 字样的物资信息

select *
from Stock
where mat_name like '%户外_真空%';

sql server express2019, 不用再加 escapte '\'了, 加上, 反而报错

3.25, 查询无库存单价的物资编号及其名称

select *
from Stock
where unit is null;

3.26, 查询规格为 BVV-120 的护套绝缘电线的物资编号, 库存数量及库存地点

select mat_num, amount, warehouse
from Stock
where speci = 'BVV-120' and mat_name = '护套绝缘电线';

3.27, 查询"护套绝缘电线"的物资名称及其单价, 查询结果按单价降序排列

select mat_name, unit
from Stock
where mat_name = '护套绝缘电线'
order by unit desc;

3.28, 查询所有物资的信息, 查询结果按所在仓库名降序排列, 同一仓库的物资的物资按库存量升序排列

select *
from Stock
order by warehouse desc, amount asc;

3.29, 显示Stock表中库存量最大的两条记录

select top 2 *
from Stock
order by amount desc;

3.30, 显示Stock表中库存量最小的前30%的物资编号, 名称, 规格和库存数量

select top 30 percent mat_num, mat_name, speci, amount
from Stock
order by amount asc;

3.31, 统计领取了物资的抢修工程项目数

自己写的, 好像不对的写法

select prj_num, count(*)
from Out_stock
where amount is not null
group by prj_num;

正确写法

select count(distinct prj_num)
from Out_stock;

3.32, 查询使用m001号物资的抢修工程的最高领取数量, 最低领取数量以及平均领取数量

select max(amount) 最高领取数量, min(amount) 最低领取数量, avg(amount) 平均领取数量
from Out_stock
where mat_num = 'm001';

3.33, 查询每个抢修工程项目号及使用的物资种类数目

select prj_num, count(*) 每个项目使用的物资种类数目
from Out_stock
group by prj_num;

3.34, 查询使用了两种及两种以上物资的抢修工程项目号

select prj_num
from Out_stock
group by prj_num having count(*) >= 2;

3.35, 按工程部门 及 物资编号 统计 其抢修的项目个数 以及 对应的领取数量

select department, mat_num, count(distinct prj_num) 项目个数, sum(amount) 领取数量
from Out_stock
group by department, mat_num;

自己写的是

select department, mat_num, count(*) 项目个数, sum(amount) 领取数量
from Out_stock
group by department, mat_num;

有时间的话, 体会, 一下有必要加那个 distinct,

这个还真花了些时间, 写出来的, 还画了图, 同时也体会到了, 数据尽可能真实的重要性, 对理解题目有帮助

这个题目, 有点, 通过 department, mat_num, 确定, 前面的意思

2次分组, 同时也体会到了 group by 和 order by的区别

3.36, 按工程部门 及 物资编号 统计其抢修的项目个数 以及 对应的领取总量, 要求带 rollup 关键字

select department, mat_num, count(distinct prj_num) 项目个数, sum(amount) 领取总量
from Out_stock
group by department, mat_num
with rollup;

3.37, 按工程部门 及 物资编号 统计其抢修的项目个数 以及 对应的领取总量, 要求带 cube 关键字

select department, mat_num, count(distinct prj_num) 项目个数, sum(amount) 领取总量
from Out_stock
group by department, mat_num
with cube;

3.38, 统计存放于供电局2#仓库的所有物资的总价值

自己的实现, 好像没有太满足题目要求, 但基本满足了

select warehouse, sum(total)
from Stock
where warehouse = '供电局2#仓库'
group by warehouse;

使用书上的答案, 报错, 通过查询, 好像说是 compute从sqlserver2012, 就开始废弃了

select mat_name, speci, amount, unit, total
from Stock
where warehouse = '供电局2#仓库'
compute sum(total);

3.39, 统计存放于各个仓库的物资总价值, 并查询物资名称, 规格, 单价, 数量等, 按仓库分组显示查询结果

使用书上的答案, 还是报错

select mat_name, speci, amount, unit, total, warehouse
from Stock
order by warehouse
compute sum(total) by warehouse;

3.40, 查询每个抢修工程 及其 领料出库 的情况

select Salvaging.prj_num, Salvaging.prj_name, Out_stock.mat_num, Out_stock_amount, Out_stock.department
from Salvaging, Out_stock
where Salvaging.prj_num = Out_stock.prj_num;

3.41, 对例3.40 用自然连接完成

没怎么体会到, 考察什么呢, 和上面的有什么区别吗

select Salvaging.prj_num, prj_name, start_date, end_date, prj_status, mat_num, amount, get_date, department
from Salvaging, Out_stock
where Salvaging.prj_num = Out_stock.prj_num;

3.42, 把例3.41中的等值连接改为左外连接

select Salvaging.prj_num, prj_name, start_date, end_date, prj_status, mat_num, amount, get_date, department
from Salvaging left outer join Out_stock on(Salvaging.prj_num = Out_stock.prj_num);

3.43, 查询项目号为"20100015"的抢修项目所使用的物资编号, 物资名称, 规格和使用数量

selct Out_stock.prj_num, Out_stock.mat_num, Stock.mat_name, Stock.speci, Stock.amount
from Stock, Out_stock
where Stock.mat_num = Out_stock.mat_num and Out_stock.prj_num = '20100015';

3.44, 查询使用了护套绝缘电线的所有抢修项目编号及名称

select Out_stock.prj_num, Salvaging.prj_name
from Salvaging, Stock, Out_stock
where Salvaging.prj_num = Out_stock.prj_num and Stock.mat_num = Out_stock.mat_num
and Stock.mat_name = '护套绝缘电线';

3.45, 查询同时使用了物资编号为 m001 和 m002 的抢修工程的工程号

select A.prj_num
from Out_stock A, Out_stock B
where A.prj_num = B.prj_num 
and A.mat_num = 'm001' and B.mat_num = 'm002';

3.46, 查询与规格为 “BVV-120” 的 “护套绝缘电线” 在 同一个仓库存放的物资名称, 规格和数量

select mat_name, speci, amount
from Stock
where warehouse = (
    select warehouse
    from Stock
    where sepci = 'BVV-120' and mat_name = '护套绝缘电线';
);

其中, 上面的 warehouse = 换成 warehouse in 也可以

3.47, 查询工程项目 “观澜站光缆抢修” 所使用的物资编号和物资名称

连接实现

select Out_stock.mat_num, Stock.mat_name
from Salvaging, Stock, Out_stock
where Salvaging.prj_num = Out_stock.prj_num
and Stock.mat_num = Out_stock.mat_num
and prj_name = '观澜站光缆抢修';

有时间的话, 再用嵌套查询实现一下

因为当数据量多的时候, 连接查询可能需要的时间较多, 连接比较耗费时间

3.48, 查询出库存量超过该仓库物资平均库存量的物资编号, 名称, 规格 及 数量

select mat_num, mat_name, speci, amount
from Stock s1
where amount > (
    select avg(amount)
    from Stock s2
    where s1.warehouse = s2.warehouse
);

3.49, 查询其他仓库中比供电局1#仓库的某一物资库存量少的物资名称,规格和数量

select mat_name, speci, amount
from Stock
where warehouse <> '供电局1#仓库'
and amount < any (
    select amount
    from Stock
    where warehouse = '供电局1#仓库'
);

3.50, 查询其他仓库中比供电局1#仓库的所有物资库存量少的物资名称, 规格 和 数量

select mat_name, speci, amount
from Stock
where warehouse <> '供电局1#仓库'
and amount <all (
    select amount
    from Stock
    where warehouse = '供电局1#仓库'
);

3.51, 查询所有使用了 m001 号物资的工程项目名称

自己用连接查询, 实现的, 和书上给出的 嵌套查询不一样, 但结果好像一样, 好像不对, 有时间, 再看一下

select Salvaging.prj_name
from Salvaging, Out_stock
where Salvaging.prj_num = Out_stock.prj_num
and mat_num = 'm001';

3.52, 查询所有没有使用m001号物资的工程项目编号 及 名称

select prj_num, prj_name
from Salvaging
where not exists(
    selct *
    from Out_stock
    where prj_num = Salvaging.prj_num and mat_num = 'm001'
);

3.53, 将例3.46改为带谓词exists的查询, 其SQL语句如下

select mat_name, speci, amount
from Stock s1
where exists (
    select *
    from Stock s2
    where s2.warehouse = s1.warehouse and speci = 'BVV-120' and mat_name = '护套绝缘电线'
);

3.54, 查询被所有的抢修工程都使用了的物资名称及规格

select mat_name, speci
from Stock
where not exists (
    select *
    from Salvaging
    where not exists (
        select *
        from Out_stock
        where mat_num = Stock.mat_num and prj_num = Salvaging.prj_num
    )
);

3.55, 查询所用物资包含抢修工程"20100016"所用物资的抢修工程号

select distinct prj_num
from Out_stock sx
where not exists (
    select *
    from Out_stock sy
    where sy.prj_num = '20100016' and not exists (
        select *
        from Out_stock sz
        where sz.mat_num = sy.mat_num and sz.prj_num = sx.prj_num
    )
);

3.56, 查询存放在 供电局1#仓库 的物资 及 单价 不大于50 的物资

select *
from Stock
where warehouse = '供电局1#仓库'
union
select *
from Stock
where unit <= 50;

3.57, 查询使用了物资编号为 m001 或 m002 的抢修工程的 工程号

select prj_num
from Out_stock
where mat_num = 'm001'
union
select prj_num
from Out_stock
where mat_num = 'm002';

如果使用下面这个, 会有重复(错误, 不满足题目要求)

select *
from Out_stock
where mat_num = 'm001' or mat_num = 'm002';

但是如果, 改成下面这个, 又好像对, 有时间测试一下

select prj_num
from Out_stock
where mat_num = 'm001' or mat_num = 'm002';

3.58, 查询存放在供电局1#仓库且单价不大于50的物资

书上的实现

select *
from Stock
where warehouse = '供电局1#仓库'
intersect
select *
from Stock
where unit <= 50;

自己的实现, 也对

select *
from Stock
where warehouse = '供电局1#仓库' and unit <= 50;

3.59, 查询同时使用了物资编号为 m001 和 m002 的抢修工程的工程号

select prj_num
from Out_stock
where mat_num = 'm001'
intersect
select prj_num
form Out_stock
where mat_num = 'm002';

3.60, 查询存放在供电局1#仓库的物资 与 单价不大于50的物资的 差集

select *
from Stock
where warehouse = '供电局1#仓库'
except
select *
from Stock
where unit <= 50;

3.61, 将新的配电物资(物资编号: m020; 物资名称: 架空绝缘导线; 规格: 10KV-100; 仓库名称; 供电局1#仓库; 单价: 12.8; 库存数量: 50)插入配电物资库存记录表Stock表中

insert
into Stock(mat_num, mat_name, speci, warehouse, unit, amount)
values
('m020', '架空绝缘导线', '10KV-100', '供电局1#仓库', 12.8, 50);

3.62, 将新的抢修工程(20110011, 观澜站电缆接地抢修, 2011-2-3 0:00:00, 2011-2-5 12:00:00, 1)插入到抢修工程计划表Salvaging中

insert
into Salvaging(prj_num, prj_name, start_date, end_date, prj_status)
values
('20110011', '观澜站电缆接地抢修', '2011-2-3 0:00:00', '2011-2-5 12:00:00', 1);

3.63, 将多行数据记录插入领料出库表Out_stock中

insert
into Out_stock(prj_num, mat_num, amount, get_date, department)
values
('20110006', 'm001', 2, '2011-3-9', '工程4部'),
('20110006', 'm003', 3, '2011-3-9', '工程4部');

3.64, 对每一个抢修工程项目, 求其所用物资的总费用, 并把结果存入数据库

先创建Prj_cost表

create table Prj_cost(
    prj_num char(8) primary key,
    cost decimal(18, 2)
);

插入子查询数据

insert
into Prj_cost
    select prj_num, sum(Out_stock.amount * unit)
    from Stock, Out_stock
    where Stock.mat_num = Out_stock.mat_num
    group by prj_num;

3.65, 将编号为"m020"的物资单价改为44.5

update Stock
set unit = 44.5
where mat_num = 'm020';

3.66, 将所有物资的单价增加1

update Stock
set unit = unit + 1;

3.67, 将供电局1#仓库中的所有物资的领取置0

update Stock
set amount = 0
where mat_num in(
    select mat_num
    from Stock
    where warehouse = '供电局1#仓库'
);

3.68, 删除项目号为"20110001"的抢修工程领取的编号为"m001"的物资出库记录表

delete
from Out_stock
where prj_num = '20110001' and mat_num = 'm001'; 

3.69, 删除所有抢修工程的领料出库记录

delete
from Out_stock;

3.70, 删除"观澜站光缆抢修"工程项目的所有领料出库记录

书上的实现

delete
from Out_stock
where prj_num = (
    select prj_num
    from Salvaging
    where prj_name = '观澜站光缆抢修'
);

自己的实现, 有些复杂了

delete
from Out_stock
where prj_num in(
    select distinct Out_stock.prj_num
    from Salvaging, Out_stock
    where Salvaging.prj_num = Out_stock.prj_num
    and prj_name like '%观澜站光缆抢修%'
);

3.71, 建立供电局1#仓库所存放物资的视图

create view s1_stock
as
select mat_num, mat_name, speci, amount, unit
from Stock
where warehouse = '供电局1#仓库';

3.72, 建立供电局1#仓库所存放物资的视图, 并要求进行修改 和 插入操作时, 仍需保证该视图只有供电局1#仓库所存放的物资

create view s2_stock
as
select mat_num, mat_name, speci, amount, unit
from Stock
where warehouse = '供电局1#仓库'
with check option;

3.73, 建立抢修工程项目名称(prj_name), 出库物资名称(mat_name), 规格(speci), 及 领取数量的视图

create view s1_outstock
as
select prj_name, mat_name, speci, Out_stock.amount
from Salvaging, Stock, Out_stock
where Salvaging.prj_num = Out_stock.prj_num and Stock.mat_num = Out_stock.mat_num;

3.74, 建立供电局1#仓库所存放物资的库存数量不少于50的视图

create view s3_stock
as
select mat_num, mat_name, speci, amount
from s1_stock
where amount >= 50;

3.75, 建立一个体现抢修工程项目实际抢修天数的视图

create view s1_salvaging(prj_name, start_date, end_date, days)
as
select prj_name, start_date, end_date, datediff(day, start_date, end_date)
from Salvaging;

3.76, 将仓库名称与其仓库内所存放物资的种类数定义为一个视图

create view s4_stock(warehouse, counts)
as
select warehouse, count(mat_num)
from Stock
group by warehouse;

3.77, 将所有已按期完成的抢修工程定义为一个视图

create view s2_salvaging(prj_num, prj_name, start_date, end_date, prj_status)
as
select *
from Salvaging
where prj_status = 1;

3.78, 删除视图s1_stock

drop view s1_stock;

3.79, 在供电局1#仓库的物资视图s1_stock中找出单价小于20的物资名称, 规格 和 单价

select mat_name, speci, unit
from s1_stock
where unit < 20;

3.80, 查询使用了供电局1#仓库物资的抢修工程项目号

select distinct prj_num
from s1_stock, Out_stock
where s1_stock.mat_num = Out_stock.mat_num;

3.81, 查询所存物资种类大于2的仓库名称

select warehouse
from s4_stock
where counts > 2;

3.82, 将供电局1#仓库的物资视图s1_stock 中编号为m001的物资库存量改为100

update s1_stock
set amount = 100
where mat_num = 'm001';

3.83, 向供电局1#仓库的物资视图s1_stock中插入一个新的物资记录, 其中物资编号为"m022", 物资名称为"护套绝缘电线", 规格为"BVV-150", 数量为100, 单价为14.5

insert
into s1_stock(mat_num, mat_name, speci, amount, unit)
values
('m022', '护套绝缘电线', 'BVV-150', 100, 14.5);

3.84, 删除供电局1#仓库的物资视图s1_stock中编号为m001的物资的记录

delete
from s1_stock
where mat_num = 'm001';

4.1, 在电力抢修工程数据库中, 如果Stock表中存在库存量低于1的物资, 就显示文本"the amount is not enough!", 否则显示所有物资信息.

if exists(select * from Stock where amount < 1)
	print 'the amount is not enough!'
else
	begin
	select *
	from Stock
	end

4.2, 将Stock表中所有物资的单价增加10%, 直到有一个物资单价超过15000, 或 单价总和超过50000为止, 其语法形式如下

while(select sum(unit) from Stock) < 5000
begin
	update Stock
	set unit = unit * 1.1
	if exists(select * from Stock where unit > 15000)
		break
	else
		continue
end

4.3, 使用 waitfor语句表示等待一分钟后显示 Stock表, 等到中午 12:00:00时, 显示 Salvaging表

waitfor delay '00:01:00'
select *
from Stock

waitfor time '12:00:00'
select *
from Salvaing

4.4, 用case语句的格式一实现: 在对Stock表的查询中, 当仓库号的值是"供电局1#仓库", “供电局2#仓库”, "供电局3#仓库"时, 分别返回 “北京”, “上海”, “广州”, 否则返回未知

select mat_num, mat_name, speci, warehouse =
case warehouse
	when '供电局1#仓库' then '北京'
	when '供电局2#仓库' then '上海'
	when '供电局3#仓库' then '广州'
	end
,amount, unit, total
from Stock

4.5, 用case语句的格式二实现, 在对Stock表的查询中, 当仓库号的值是 “供电局1#仓库”, “供电局2#仓库”, “供电局3#仓库”, 时, 分别返回
“北京”, “上海”, “广州”, 否则返回"未知"

select mat_num, mat_name, speci, warehouse = case
where warehouse = '供电局1#仓库' then '北京'
where warehouse = '供电局2#仓库' then '上海'
where warehouse = '供电局3#仓库' then '广州'
else '未知'
, amount, unit, total
from Stock

4.6, 创建一个最简单的存储过程, 用于查看Stock表中的所有记录

create procedure exp1
as
select *
from Stock
go

执行

execute exp1

或者

exec exp1

4.7, 创建一个带输入参数的存储过程, 向Stock表中添加一个新的数据行

if exists(select name from sysobjects where name = 'exp2' and type = 'P')
	drop procedure exp2
go
create procedure exp2
	@mno char(8), @mname varchar(50), @mspeci varchar(20)
as
	insert into Stock(mat_num, mat_name, speci)
	values(@mno, @mname, @mspeci)
go

执行

execute exp2 'm030', '护套绝缘电线', 'BVV-35'

execute exp2 @mno = 'm030', @mname = '护套绝缘电线', @mspeci = 'BVV-35'

4.8, 创建一个带默认参数的存储过程, 通过传递的参数查询对应的物资名称, 规格, 项目名称, 是否按期完工等信息,
如果没有提供参数, 则使用预设的默认值

if exists(select name from sysobjects where name = 'exp3' and type = 'P')
	drop procedure exp3
go
create procedure exp3
	@mname varchar(50) = '绝缘', @pno char(8) = '20110005'
as
    select mat_name, speci, prj_name, prj_status
    from Salvaging, Stock, Out_stock
    where Salvaging.prj_num = Out_stock.prj_num
    and Stock.mat_num = Out_stock.mat_num
    and mat_name like @mname
    and Salvaging.prj_num = @pno
go

执行

execute exp3

execute expe '%绝缘电线'

execute exp3 @pno = '20110001'

execute exp3 '护套绝缘电线', '20110001'

4.9, 创建一个存储过程, 根据输入的抢修工程项目号统计其领取物资的总数量, 并要求输出

if exists(select name from sysobjects where name = 'exp4' and type = 'P')
	drop procedure exp4
go
create procedure exp4
	@pn char(8), @sum int output
as
	select @sum = sum(amount)
	from Out_stock
	where prj_num = @pn
go

declare @total int
execute exp4 '20110001', @total output
print '该项目领取物资总量为:' + cast(@total as varchar(20))

4,10, 创建一个存储过程, 根据输入的 工程部门 及 起始时间段
统计汇总 该部门在 对应时间段内 所参与抢修的 工程项目总数 以及 领取物资的总成本

if exists(select name from sysobjects where name = 'exp5' and type = 'P')
	drop procedure exp5
go
create procedure exp5
	@department varchar(50), @start_date datetime, @end_date datetime, 
	@count_prj int output, @sum_cost decimal(18, 2) output
as
select @count_prj = count(Salvaging.prj_num), @sum_cost = sum(Out_stock.amount * Stock.unit)
from Salvaging, Stock, Out_stock
where Salvaging.prj_num = Out_stock.prj_num
and Stock.mat_num = Out_stock.mat_num
and department = @department
and get_date between @start_date and end_date
go

declare @prjcounts int, @sumcosts decimal(18, 2)
exec exp5 '工程2部', '2011-1-1', '2011-1-31', @prjcounts output, @sumcosts output
print '该部门参与抢修工程项目' + cast(@prjcounts as varchar(20)) + '个, 总成本为' + cast(@sumcosts as varchar(20))

4.11, 嵌套调用存储过程, 查看使用抢修物资总数最多的工程项目信息

create procedure exp6
	@prj_no char(8) output
as
	select top 1 @prj_no = prj_num
	from Out_stock
	group by prj_num
	order by sum(amount) desc
go
create procedure exp7
as
	declare @prj_id char(8)
	exec exp6 @prj_id output
	select *
	from Salvaging
	where prj_num = @prj_id
go

execute exp7

4.12, 创建一个insert触发器, 在对表Stock进行插入后, 验证库存量的大小, 若库存量小于1, 则撤销该插入操作

create trigger tr1_stock
	on Stock
	after insert
as
	declare @amount int
	select @amount = amount
	from inserted
	if @amount < 1
	begin
		rollback tran
		raiserror('Amount must be greater than 1!', 16, 10)
	end
go

insert
into Stock(mat_num, mat_name, speci, warehouse, amount, int)
values('m030', '护套绝缘电线', 'BVV-120', '供电局1#仓库', 2, 100)

insert
into Stock(mat_num, mat_name, speci, warehouse, amount, int)
values('m031', '护套绝缘电线', 'BVV-120', '供电局1#仓库', 0, 100)

4.13, 创建一个insert触发器, 在对表Out_stock插入一条记录后, 更改对应物资在Stock表中的库存数量, 完成级联更改操作

create trigger tr1_outstock
on Out_stock
after insert
as
begin
	declare @m_num char(8), @m_amount int
	select @m_num = mat_num, @m_amount = amount
	from inserted
	update Stock
	set amount = amount - @m_amount
	where mat_num = @m_num
end
go

insert
into Out_stock
values('20110006', 'm001', 10, '2011-3-8', '工程部');

4.14, 创建一个delete触发器, 当用户从Stock表中删除数据时同时将 Out_stock表中相关物资的出库情况一并删除

create trigger tr2_stock
	on Stock
	after delete
as
begin transaction
	declare @mat_num char(8)
	select @mat_num = mat_num
	from deleted
	delete
	from Out_stock
	where mat_num = @mat_num
	commit transaction
go

4.15, 创建一个update触发器, 当用户更新Stock表中的数据时,
从inserted表中红读取修改的新的amount值, 如果该值小于1, 将撤销更新操作, 即触发器从deleted表中查询修改前的值, 将其重新更新到Stock表中
(也可采用事务回滚的方式撤销更新操作)

create trigger tr3_stock
	on Stock
	after insert
as
	declare @amount_new int, @amount_old int, @mat_num char(10)
	select @amount_new = amount, @mat_num = mat_num
	from inserted
	if @amount_new < 1
		begin
			select @amount_old = amount
			from deleted
			update Stock
			set amount = @amount_old
			where mat_num = @mat_num
			print 'The row can not be be updated!'
		end
go

4.16, 修改前面创建的update触发器, 使其先检测更新的列, 当更新warehouse列时, 禁止更新;
当更新库存量amount列时, 设置更新规则, 若更新后的值小于1, 则撤销该更新操作