SQL server training

create database swapnil
use swapnil

create table emp(empid int,ename varchar(30),eadd varchar(30),city varchar(20),
pin int , join_dt datetime, mobileno int
)

Alter table emp add constraint Pk_empno  primary key (empid)

Alter table emp alter column empid int not null
ALTER TABLE Po_detail  column Po_Pono TO Pd_Pono

Alter table Po_detail Add constraint Pk_Pono_Itemcd primary key(Po_Pono,Pd_itemcd)

Alter table Vendor alter column vn_vendcd int not null
Alter table Vendor Add constraint Pk_vencd primary key(vn_vendcd)

ALTER TABLE Vendor
DROP CONSTRAINT  Pk_vencd
drop table Vendor primary constraint key Pk_vencd

Alter table Item alter column Im_itemcd numeric(18,2) not null
Alter table Item Add constraint Pk_itemcd primary key(Im_itemcd)
Alter table Vendor alter column vn_vendcd numeric(18,2) not null
Alter table PO_Head alter column Ph_vendcd numeric(18,2) not null

Alter table PO_Head add constraint fk_vendc foreign key (Ph_vendcd) references vendor (vn_vendcd)

Alter table emp alter column mobileno int not null

Alter table emp add constraint Uk_mobileno  unique (mobileno)
Alter table emp alter column city varchar(20) not null


ALTER TABLE emp
Add constraint Df_city DEFAULT 'city' for city

Alter table Vendor alter column Im_rate numeric(18,2) not null

insert into Vendor values(1,'Mahalaxmi tradors')
insert into Vendor values(2,'Vikhroli tradors')
insert into Vendor values(3,'bhandup tradors')
insert into Vendor values(4,'Elphiston tradors')
insert into Vendor values(5,'kanjur tradors')

insert into Item values (1,'item1',15)
insert into Item values (2,'item2',20)
insert into Item values (3,'item3',21)
insert into Item values (4,'item4',22)
insert into Item values (5,'item5',23)
insert into Item values (6,'item6',14)
insert into Item values (7,'item7',13)
insert into Item values (8,'item8',12)
insert into Item values (9,'item9',17)
insert into Item values (10,'item10',18)

Alter table Po_detail alter column Pd_value numeric(18,0)  null
insert into Po_detail values(1,1,10,null,null )
insert into Po_detail values(1,2,20 ,null,null)
insert into Po_detail values(1,3,30,null,null )
insert into Po_detail values(2,4,10,null,null )
insert into Po_detail values(2,5,10 ,null,null)
insert into Po_detail values(2,6,10 ,null,null)
insert into Po_detail values(3,7,50,null,null )
insert into Po_detail values(3,8,60,null,null )
insert into Po_detail values(3,9,70,null,null )

insert into Po_detail values(4,10,50,null,null )
insert into Po_detail values(4,11,60,null,null )
insert into Po_detail values(4,12,70,null,null )

insert into Po_detail values(5,13,50 ,null,null)
insert into Po_detail values(5,14,60 ,null,null)
insert into Po_detail values(5,15,70,null,null )


Alter table PO_Head alter column Ph_TaxAmt numeric(18,0)  null
insert into PO_Head values(1,GETDATE(),1,null,null )
insert into PO_Head values(2,GETDATE(),2,null,null )
insert into PO_Head values(3,GETDATE(),3,null,null )
insert into PO_Head values(4,GETDATE(),4,null,null )
insert into PO_Head values(5,GETDATE(),5,null,null )


select * from PO_Head
select * from Vendor
select * from Item
select * from Po_detail

Update Po_detail set Pd_rate =( select im_rate from Item where Im_itemcd = Pd_itemcd)
Update Po_detail set Pd_value = Pd_qty * Pd_rate
Update PO_Head set Ph_Poamnt = (select sum(pd_value)from po_detail  where Ph_Pono = po_pono)