HW EXERCISES: PROCEDURES, FUNCTIONS, TRIGGERS

In this exercise you will create scripts in the Murach College database.

$1.$ Write a script that creates and calls a stored procedure named spInsertDepartment. First, code a statement that creates a procedure that adds a new row to the Departments table. To do that, this procedure should have one parameter for the department name.

Please paste your SQL stored procedure here.

Code at least two EXEC statements that test this procedure. $($Note that this table doesn$$t allow duplicate department names.

$2.$ Write a script that creates and calls a function named fnStudentUnits that calculates the total course units of a student in the StudentCourses table. To do that, this function should accept one parameter for the student ID$,$ and it should return the sum of the course units for the student.

The SELECT statement that calls the function should return the StudentID from the StudentCourses table, the CourseNumber and CourseUnits from the Courses table, and the value return by the fnStudentUnits function for that student.

Please paste your script for your function here.

Please paste your SELECT statement here.

$3.$ Write a script that creates and calls a function named fnTuition that calculates the total tuition for a student. To do that, this function should accept one parameter for the student ID$,$ it should use the fnStudentUnits function that you created in step $2,$ and it should return the value of the tuition for that student depending on whether the student is fulltime $($more than nine units$)$ or parttime $($nine or fewer units$).($Hint: Use a cross join to work with data in the Students and Tuition tables.$)$

The SELECT statement that calls the function should return the StudentID from the Students table, the value returned by the fnStudentUnits function for that student, and the value returned by the fnTuition function for that student. Return only rows for students taking one or more units.

Please paste your script for your function here.

Please paste your SELECT statement here.

$4.$ Write a script that creates and calls a stored procedure named spInsertInstructor that inserts a row into the Instructors table. This stored procedure should accept one parameter for each of these columns: LastName, FirstName, Status, DepartmentChairman, AnnualSalary, and DepartmentID.

This stored procedure should set the HireDate column to the current date.

If the value for the AnnualSalary column is a negative number, the stored procedure should raise an error that indicates that this column doesn$$t accept negative numbers.

Code at least two EXEC statements that test this procedure.

Please paste the script for your procedure here.

$5.$ Write a script that creates and calls a stored procedure named spUpdateInstructor that updates the AnnualSalary column in the Instructors table. This procedure should have one parameter for the instructor ID and another for the annual salary.

If the value for the AnnualSalary column is a negative number, the stored procedure should raise an error that indicates that the value for this column must be a positive number.

Code at least two EXEC statements that test this procedure.

Please paste your stored procedure here.

$6.$ Create a trigger named Instructors$\_$UPDATE that checks the new value for the AnnualSalary column of the Instructors table. This trigger should raise an appropriate error if the annual salary is greater than $120,000$ or less than $0.$

If the new annual salary is between $0$ and $12,000,$ this trigger should modify the new annual salary by multiplying it by $12.$ That way, a monthly salary of $5,000$ becomes an annual salary of $60,000.$

Test this trigger with an appropriate UPDATE statement

Please paste your trigger here.

$7.$ Create a trigger named Instructors$\_$INSERT that inserts the current date for the HireDate column of the Instructors table if the value for that column is null.

Test this trigger with an appropriate INSERT statement.

Please paste your trigger here.

Please paste your INSERT statement here.

$8.$ Create a table named InstructorsAudit. This table should have all columns of the Instructors table. Also, it should have an AuditID column for its primary key and a DATETIME$2$ column named DateUpdated.

Create a trigger named Instructors$\_$UPDATE. This trigger should insert the old data about the instructor into the InstructorsAudit table after the row is updated and set the DateUpdated column to the current date and time. Then, test this trigger with an appropriate UPDATE statement.

Please the code for creating the table here.

Please paste the code for your trigger here.

Please paste your UPDATE statement here.