https://www.coursehero.com/u/file/59976103/Creating-and-Managing-View-and-Triggers-Lab-1docx/?justUnlocked=1#doc/qa

The link attached above included the table

I need help with these questions please...

1.REATE the following tables -

a.Dept_triggers - Add Identity Column (1000,1)

b.Emp_triggers- Add Identity Column (1000,1)

c.Emphistory

dbo.emp_triggers

Columns

empid (PK, int, not null)

empname (varchar(50), null)

deptid (int, null)

Keys

dbo.emphistory

Columns

empid (int, null)

deptid (int, null)

isactive (int, null)

Keys

Constraints

2.TRIGGERS

a.Trigger 1 - Build a trigger on the emp table after insert that adds a record into the emp_History table and marks IsActive column to 1

b.Trigger 2 - Build a tirgger on the emp table after an update of the empname or deptid column - It updates the subsequent empname and/or deptid in the emp_history table.

c.Build a trigger on the emp table after delete that marks the isactive status = 0 in the emp_History table.

3.Run this script - Results should show 10 records in the emp history table all with an active status of 0

INSERT INTO dbo.emp_triggers

SELECT 'Ali',1000

INSERT INTO dbo.emp_triggers

SELECT 'Buba',1000

INSERT INTO dbo.emp_triggers

SELECT 'Cat',1001

INSERT INTO dbo.emp_triggers

SELECT 'Doggy',1001

INSERT INTO dbo.emp_triggers

SELECT 'Elephant',1002

INSERT INTO dbo.emp_triggers

SELECT 'Fish',1002

INSERT INTO dbo.emp_triggers

SELECT 'George',1003

INSERT INTO dbo.emp_triggers

SELECT 'Mike',1003

INSERT INTO dbo.emp_triggers

SELECT 'Anand',1004

INSERT INTO dbo.emp_triggers

SELECT 'Kishan',1004

DELETE FROM dbo.emp_triggers

4.Create 5 views - Each view will use 3 tables and have 9 columns with 3 coming from each table.

a.Create a view using 3 Human Resources Tables (Utilize the WHERE clause)

b.Create a view using 3 Person Tables (Utilize 3 system functions)

c.Create a view using 3 Production Tables (Utilize the Group By Statement)

d.Create a view using 3 Purchasing Tables (Utilize the HAVING clause)

e.Create a view using 3 Sales Tables (Utilize the CASE Statement)