-------------------------1

create database itemdetail

--------------------------2

create table item

(

itemcode char(5)primary key,

itemdesc char(30) not null,

qoh integer not null,

reordlvl integer,

price integer not null,

)

create table issuemaster

(

issuecode char(5) primary key,

issuedate datetime not null,

deptcode char(5) not null,

)

create table issuedetails

(

issuecode char(5),

itemcode char(5),

issueqty integer not null,

primary key(issuecode,itemcode)

)

----------------------------------------3a

insert item values('it001','refrigerator',50,400,4000)

insert item values('it002','televition',100,75,3000)

insert item values('it003','washing machine',250,250,2000)

insert item values('it004','micro wave',200,150,3500)

----------------------------------------3b

insert issuemaster values('is001','12-jan-2003','d001')

insert issuemaster values('is002','18-feb-2003','d003')

insert issuemaster values('is003','18-feb-2003','d008')

insert issuemaster values('is004','15-apr-2003','d002')

----------------------------------------3c

insert issuedetails values('is001','it001',15)

insert issuedetails values('is002','it003',5)

insert issuedetails values('is002','it004',2)

insert issuedetails values('is003','it002',1)

insert issuedetails values('is004','it004',5)

-----------------------------------------4a

select * from item

------------------------------------------4b

select itemcode as'itemcode',itemdesc as'item description',qoh as'quantity on hand',reordlvl as're-order level',price as'price per unit'from item

-------------------------------------------4c

select*from item where price<=2000

------------------------------------------4d

select im.issuecode,issuedate,deptcode as'departmentcode',itemdesc as'item description',qoh as'quantity on hand'from item it join

issuedetails id on it.itemcode=id.itemcode join issuemaster im on im.issuecode=im.issuecode

---------------------------------------------4dd

select im.issuecode,issuedate,deptcode as'deparment code',itemdesc as'item descript tion',issueqty as'quantity issued' from item it join

issuedetails idt on it.itemcode=idt.itemcode join issuemaster im on im.issuecode=im.issuecode

------------------------------------------4e

select *from item

where qoh=reordlvl

------------------------------------------5

alter trigger triger

on issuedetails

for insert

as

declare @issueqty int

if(select issueqty from inserted) >

(select qoh from item where itemcode=(select itemcode from inserted))

begin

print'error'

rollback tran

end

else

begin

select @issueqty =issueqty from inserted

update item set qoh=qoh-(@issueqty)

where itemcode=(select itemcode from inserted)

end

insert issuedetails values ('is005','it002',99)

------------------------------------------6

create proc increaseqoh

@itemcode char(5),

@amount int

as

if(not exists( select * from item where itemcode=@itemcode))

begin

print'không tim thay san pham nay'

end

else

begin

update item set qoh = qoh+@amount where itemcode=@itemcode

end

exec increaseqoh 'it001',50

------------------------------

select * from item

select *from issuedetails