~_CourseDesignModule_StudentPutInFile_2012022624109

1、通过工人名查询工人相关信息create proc proc_Worker1WorkerName varchar(8)asselect WorkerNo 职工号,WoerkerAge 年龄,WorkerSex性别,WorkerType工种,WorkerNo车间号from workerwhere WorkerName=WorkerNameproc_Worker1 刘德华通过产品号查询产品相关信息create proc proc_Product1ProductName varchar(20)asselect product.ProductNo产品号,ProductPrice产品价格,WorkNo车间

2、号,WareNo仓库号,Snuml产品库存量from product,Wapwhere product.ProductNo=Wap.ProductNo and product.ProductName=ProductNamedrop proc proc_Product1select * from productproc_Product1 电风扇通过零件号查询零件相关信息create proc proc_component1ComNo varchar(10)asselect ComPrice零件价格,ComWeight重量,WareNo仓库号,Snum2零件库存量from component,Wa

3、cwhere component.ComNo=Wac.ComNo and component.ComNo=ComNodrop proc proc_component1proc_component1 10通过产品名查询产品生产相关信息create proc proc_pc1productName varchar(20)asselect product.ProductNo产品号,ComNo所需零件号,ComNum2零件数from product,PCwhere product.ProductNo=PC.ProductNo and ProductName=productNamedrop proc p

4、roc_pc1proc_pc1 电冰箱通过车间号查询车间信息create proc proc_workshop1workNo varchar(10)asselect CNo车间主任编号,WorkAddress地址,Workphone电话,FacName厂名from Workshopwhere WorkNO=workNoproc_workshop1 2通过生产日期查询零件生产信息create proc proc_wc1Timel varchar(10)asselect WorkNo车间号,ComNo零件号,ComNuml零件数from WCwhere Timel=Timelproc_wc1 20

5、12drop proc proc_wc1create proc proc_wap1Time2 varchar(10)asselect WareNo车间号,ProductNo产品号,Snuml产品数from Wapwhere Time2=Time2proc_wap1 2012-仓库零件触发器create trigger TnsertWC_triggeron WCfor insertas declare a intdeclare b varchar(10)declare c varchar(10)select a=ComNuml from insertedselect b=ComNo from i

6、nsertedselect c=WorkNo from insertedupdate Wacset Snum2=Snum2+awhere Wac.ComNo=b and Wac.WorkNo=cgodrop trigger InsertPC_triggercreate trigger InsertPC_triggeron PCfor insertas declare a intdeclare b varchar(10)select a=ComNum2 from insertedselect b=ProductNo from insertedupdate Wapset Snuml=Snuml+a

7、where Wap.ProductNo=bgoinsert into PC VALUES(2013,1001,10,300,30)drop trigger TnsertWC_triggerinsert into WC values(2012,2,10,1000)delete from WCupdate WCset ComNuml=1500where ComNo=11insert into wac values(200,10,1000)select * from waccreate trigger insertWap_triggeron Wapfor insertasupdate Wacset

8、Snum2=Snum2-(select Snumlfrom inserted)*(select ComNum2from PCwhere ProductNo in(select ProductNofrom Inserted)and PC.ComNo=Wac.ComNo)CREATE table factory(FacName varchar(30) primary key,FMname char(8) not null,)create table workshop(WorkNO varchar(10)primary key,CNo varchar(8),Workphone varchar(12)

9、,WorkAddress varchar(20),FacName varchar(30)not null,foreign key(FacName)references factory(FacName);create table worker(WorkerNo varchar(10) primary key,WorkerName varchar(8),WoerkerAge int,WorkerSex varchar(2) check(WorkerSex in (男,女),WorkerType varchar(20),WorkNo varchar(10)not null,foreign key(W

10、orkNo)references Workshop(WorkNo);create table product(ProductNo varchar(10)primary key,ProductName varchar(20),ProductPrice numeric(5,1) default 0,check(ProductPrice0),WorkNo varchar(10)not null,foreign key(WorkNo)references workshop(WorkNo);create table component(ComNo varchar(10)primary key,ComPr

11、ice numeric(5,1)default 0,check(ComPrice0),ComWeight int);create table Warehouse (WareNo varchar(10)primary key, WNo varchar(8), Warephone varchar(12), FacName varchar(30)not null, foreign key(FacName)references factory(FacName) ); create table WC (Timel char(10) not null, WorkNo varchar(10) not nul

12、l, ComNo varchar(10) not null, ComNuml int, primary key(Timel,WorkNo, ComNo), foreign key(WorkNo)references workshop(WorkNo), foreign key(ComNo)references component(ComNo), ); drop table WC create table PC (Time2 char(10) not null, ProductNo varchar(10) not null, ComNo varchar(10) not null, ComNum2

13、int, ComNum1 int, primary key(Time2,ProductNo, ComNo), foreign key(ProductNo)references product(ProductNo), foreign key(ComNo)references component(ComNo), ); drop table PC create table Wap (WareNo varchar(10) not null,ProductNo varchar(10) not null,Snuml int,primary key(WareNo, ProductNo), foreign k

14、ey(ProductNo)references product(ProductNo), foreign key(WareNo)references warehouse(WareNo) ); drop table Wap create table Wac (WareNo varchar(10) not null,ComNo varchar(10) not null,Snum2 int,primary key(WareNo, ComNo), foreign key(WareNo)references warehouse(WareNo), foreign key(ComNo)references c

15、omponent(ComNo) ); drop table Waccreate proc proc_FacFacName varchar(30),FMname char(8)asinsert into factoryvalues(FacName,FMname);CREATE proc proc_workshopWorkNo varchar(10),CNo varchar(8),Workphone varchar(12),WorkAddress varchar(20),FacName varchar(30)asinsert into workshopvalues(WorkNo,CNo,Workp

16、hone,WorkAddress,FacName)create proc proc_workerWorkerNo varchar(10),WorkerName varchar(8),WoerkerAge int,WorkerSex varchar(2),WorkType varchar(20),WorkNo varchar(10)asinsert into workervalues(WorkerNo,WorkerName,WoerkerAge,WorkerSex,WorkType,WorkNo)create proc proc_productProductNo varchar(10),Pros

17、uctName varchar(20),ProductPrice numeric(5,1),WorkNo varchar(10)asinsert into productvalues(ProductNo,ProsuctName,ProductPrice,WorkNo)create proc proc_componentComNo varchar(10),ComPrice numeric(5,1),ComWeight intasinsert into componentvalues(ComNo,ComPrice,ComWeight)create proc proc_warehouseWareNo

18、 varchar(10),WNo varchar(8),Warephone varchar(12),FacName varchar(30)asinsert into Warehousevalues(WareNo,WNo,Warephone,FacName)create proc proc_WCTime1 char(10),WorkNo varchar(10),ComNo varchar(10),ComNum1 intasinsert into WCvalues(Time1,WorkNo,ComNo,ComNum1)drop proc proc_WCCREATE PROC proc_PCTime

19、2 char(10),ComNo varchar(10),ProductNo varchar(10),ComNum2 int,ProNum intasinsert into PCVALUES(Time2,ProductNo,ComNo,ComNum2,ProNum)drop proc proc_pccreate proc proc_WacWareNo varchar(10),ComNo varchar(10),Snum2 intasinsert into Wacvalues(WareNo,ComNo,Snum2)create proc proc_WapProductNo varchar(10),WareNo varchar(10),Snum1 intasinsert into Wapvalues(WareNo,ProductNo,Snum1)drop proc proc_Wapproc_Fac 通程电器,张三proc_workshop 1,10000,12345,帝王大厦,通程电器proc_workshop 2,10001,1