创建表与集合查询

一、创建Stock，salving，out_stock表

抢修工程计划表
由工程项目编号prj_num,工程项目名称pri_name,工程计划开始时间start_date,工程计划结束时间end_date,是否按期完成prj_status,五个属性组成。

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

配电物资库存记录表stock

create table Stock
(mat_num char(8) primary key,
mat_name varchar(50) not null,
speci varchar(20) not null,
warehouse char(20),
amount int,
unit decimal(18,2),
total as([amount]*[unit]),
check(mat_num like'[m][0-9][0-9][0-9]'),
);

配电物资领料出库表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(pri_num,mat_num),
foreign key(pri_num)references salvaging(prj_num);
foreign key(mat_num)references stock(mat_num);

二、插入数据

use master
INSERT 
INTO Salvaging
values(20100015,'220kv清经线接地箱及接地线被盗抢修',2010-10-12,2010-10-13,1),
      (20100016,'沙河站2#公变出线电缆老化烧毁抢修',2010-11-05,2010-11-05,1),
      (20110001,'西丽站电缆短路烧毁抢修工程',2011-01-03,2011-01-03,1),
	  (20110002,'西丽电缆站电缆接地抢修',2011-0103,2011-01-05,1),
	  (20110003,'观澜站光缆抢修',2011-02-10,2011-02-11,1),
	  (20110004,'小径墩低压线被盗抢修',2011-02-15,2011-02-15,1),
	  (20110005,'明珠立交电缆沟盖板破损抢修',2011-03-02,2011-03-05,0),
	  (20110010,'朝阳围公变低压线被盗抢修',2011-03-08,20111-03-10,0);

insert 
into stock
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);

insert 
into out_stock
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部'),
	  (20110001,'m001',2,2011-0103,'工程2部'),
	  (20110002,'m001',1,2011-01-03,'工程2部'),
	  (20110002,'m013',1,2011-01-03,'工程2部'),
	  (20110003,'m001',5,2011-02-11,'工程3部'),
	  (20110003,'m012',1,2011-02-11,'工程3部'),
	  (20110004,'m001',3,2011-02-15,'工程3部'),
	  (20110004,'m004',20,2011-02-15,'工程3部'),
	  (20110005,'m001',2,2011-03-02,'工程2部'),
	  (20110005,'m003',10,2011-03-02,'工程2部'),
	  (20110005,'m006',3,2011-03-02,'工程2部'),
	  (20110010,'m001',5,2011-03-09,'工程1部');

三、集合查询

1、并操作（union）
查询存放在供电局1#仓库的物资与单价不大于50的物资。
（使用union将多个查询结果合并起来的时候，系统会自动去掉重复的元组）

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

2、查询使用了物资编号为m001或m002的抢修工程的项目号。

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

3、交操作（INTERSECT）
查询存放在供电局1#仓库且单价不大于50的物资。

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

或

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

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

select prj_num from out_stock
where mat_num='m001'
intersect
select prj_num from out_stock
where mat_num='m002';

或

select distinct prj_num from out_stock
where mat_num='m001' and prj_num in
(select distinct prj_num from out_stock
where mat_num='m002' );

5、差操作（except）
查询存放在供电局1#仓库与单价不大于50的物资的差集。

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

或

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

或

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