1. Create stored procedures for inserting records into both tables.

 

create database c

use c

create table department1(dep_id int not null,dep_name nvarchar(100))

create table employee1(emp_id int not null,emp_name nvarchar(100),dep_id int not null,emp_salary nvarchar(100),

emp_dbo nvarchar(100),emp_gender nvarchar(100),emp_phone nvarchar(100),emp_age nvarchar(100),emp_grad nvarchar(100));

go

create proc insert_data_department

@dep_id int,

@dep_name nvarchar(100)

as

begin

insert into department1(dep_id,dep_name) values (@dep_id,@dep_name)

end

go

exec insert_data_department @dep_id=1,@dep_name='IT'

exec insert_data_department @dep_id=2,@dep_name='HR'

exec insert_data_department @dep_id=3,@dep_name='PAYROLL'

 

go

 

 

 

 

create proc insert_data_employee

@emp_id int ,

@emp_name nvarchar(100),

@dep_id int ,

@emp_salary nvarchar(100),

@emp_dbo nvarchar(100),

@emp_gender nvarchar(100),

@emp_phone nvarchar(100),

@emp_age nvarchar(100),

@emp_grad nvarchar(100)

as

begin

insert into employee1(emp_id,emp_name,dep_id,emp_salary,emp_dbo,emp_gender,emp_phone,emp_age,emp_grad)

values(@emp_id,@emp_name,@dep_id,@emp_salary,@emp_dbo,@emp_gender,@emp_phone,@emp_age,@emp_grad)

end

go

exec insert_data_employee @emp_id=1,@emp_name='mark',@dep_id=1,@emp_salary='200',@emp_dbo='jan',@emp_gender='male',

@emp_phone='121',@emp_age='25',@emp_grad='16'

exec insert_data_employee @emp_id=1,@emp_name='john',@dep_id=2,@emp_salary='300',@emp_dbo='feb',@emp_gender='male',

@emp_phone='05121',@emp_age='23',@emp_grad='12'

exec insert_data_employee @emp_id=1,@emp_name='mike',@dep_id=3,@emp_salary='150',@emp_dbo='march',@emp_gender='male',

@emp_phone='34121',@emp_age='22',@emp_grad='19'

exec insert_data_employee @emp_id=2,@emp_name='marry',@dep_id=4,@emp_salary='834',@emp_dbo='jan',@emp_gender='male',

@emp_phone='12190',@emp_age='20',@emp_grad='16'

exec insert_data_employee @emp_id=3,@emp_name='stacy',@dep_id=5,@emp_salary='121',@emp_dbo='dec',@emp_gender='male',

@emp_phone='09121',@emp_age='27',@emp_grad='17'

select *from employee1


2. Create a view that shows only the following information from both the tables

 go

create view data1

as

select employee1.emp_name,department1.dep_name

from employee1,department1

 

select *from data1

 

 With the help of a trigger, delete the record of a particular employee from the view.