Assignment $3$: Testing Advanced MS SQL Stored Procedure Creation $(7\mathrm{.}5\%)$

Individual Assignment

Objective:

This advanced lab assignment aims to test your skills in creating and working with advanced

stored procedures in MS SQL$.$ You will create a database named AdvancedDB, add tables,

insert data into these tables, and then write advanced stored procedures based on the given

questions.

Step $1$: Create Database

$--$ Create database

CREATE DATABASE AdvancedDB;

GO

$--$ Use the database

USE AdvancedDB;

GO

Step $2$: Create Tables

Create the following tables: Employees, Departments, Projects, and Assignments.

$--$ Create Employees table

CREATE TABLE Employees $($

EmployeeID INT PRIMARY KEY,

FirstName NVARCHAR$(50),$

LastName NVARCHAR$(50),$

HireDate DATE,

DepartmentID INT

$)$;

GO

$--$ Create Departments table

Assignment $3$: Testing Advanced MS SQL Stored Procedure Creation $(7\mathrm{.}5\%)$

Individual Assignment

CREATE TABLE Departments $($

DepartmentID INT PRIMARY KEY,

DepartmentName NVARCHAR$(100)$

$)$;

GO

$--$ Create Projects table

CREATE TABLE Projects $($

ProjectID INT PRIMARY KEY,

ProjectName NVARCHAR$(100),$

StartDate DATE,

EndDate DATE

$)$;

GO

$--$ Create Assignments table

CREATE TABLE Assignments $($

AssignmentID INT PRIMARY KEY,

EmployeeID INT,

ProjectID INT,

AssignmentDate DATE,

Role NVARCHAR$(50),$

FOREIGN KEY $($EmployeeID$)$ REFERENCES Employees$($EmployeeID$),$

FOREIGN KEY $($ProjectID$)$ REFERENCES Projects$($ProjectID$)$

$)$;

Assignment $3$: Testing Advanced MS SQL Stored Procedure Creation $(7\mathrm{.}5\%)$

Individual Assignment

GO

Step $3$: Insert Data

Insert sample data into the Employees, Departments, Projects, and Assignments tables.

$--$ Insert data into Departments table

INSERT INTO Departments $($DepartmentID$,$ DepartmentName$)$

VALUES

$(1,$ 'Human Resources'$),$

$(2,$ 'Engineering'$),$

$(3,$ 'Marketing'$)$;

GO

$--$ Insert data into Employees table

INSERT INTO Employees $($EmployeeID$,$ FirstName, LastName, HireDate,

DepartmentID$)$

VALUES

$(1,$ 'Alice', 'Johnson', $\text{'}2020-01-15\text{'},2),$

$(2,$ 'Bob', 'Smith', $\text{'}2019-02-20\text{'},1),$

$(3,$ 'Charlie', 'Brown', $\text{'}2021-03-25\text{'},3)$;

GO

$--$ Insert data into Projects table

INSERT INTO Projects $($ProjectID$,$ ProjectName, StartDate, EndDate$)$

VALUES

$(101,$ 'Project Alpha', $\text{'}2023-01-01\text{'},\text{'}2023-12-31\text{'}),$

$(102,$ 'Project Beta', $\text{'}2023-02-01\text{'},\text{'}2023-11-30\text{'})$;

Assignment $3$: Testing Advanced MS SQL Stored Procedure Creation $(7\mathrm{.}5\%)$

Individual Assignment

GO

$--$ Insert data into Assignments table

INSERT INTO Assignments $($AssignmentID$,$ EmployeeID, ProjectID,

AssignmentDate, Role$)$

VALUES

$(1,1,101,\text{'}2023-01-10\text{'},$ 'Developer'$),$

$(2,2,102,\text{'}2023-02-15\text{'},$ 'Manager'$),$

$(3,3,101,\text{'}2023-03-20\text{'},$ 'Tester'$)$;

GO

Step $4$: Create below Advanced Stored Procedure using the above DB

$1.$ Create a stored procedure named GetProjectAssignments that retrieves all

assignments for a specific project, including employee details and role.

$2.$ Create a stored procedure named UpdateProjectEndDate that updates the end

date of a project and logs the change in a new table ProjectLog.

$3.$ Create a stored procedure named GetDepartmentEmployeeCount that

retrieves the number of employees in each department.

$4.$ Create a stored procedure named InsertNewEmployee that inserts a new

employee and their initial assignment within a transaction.

$5.$ Create a stored procedure named GetEmployeesWithMultipleAssignments that

retrieves employees who have more than one assignment.

$6.$ Create a stored procedure named UpdateEmployeeDepartment that updates

an employee's department and logs the change in a new table EmployeeLog.

$7.$ Create a stored procedure named GetProjectsWithNoCurrentEmployees that

retrieves projects with no current employees assigned.

$8.$ Create a stored procedure named GetEmployeeAssignmentHistory that

retrieves the assignment history of a specific employee.

$9.$ Create a stored procedure named GetOverdueProjects that retrieves projects

whose end date has passed but are not yet completed.

$10.$ Create a stored procedure named GetEmployeesByRole that retrieves all

employees assigned to a specific role.

Assignment $3$: Testing Advanced MS SQL Stored Procedure Creation $(7\mathrm{.}5\%)$

Individual Assignment

$11.$ Create a stored procedure named GetEmployeeCountByRole that retrieves the

number of employees for each role.

$12.$ Create a stored procedure named GetEmployeesWithNoCurrentAssignments

that retrieves employees who do not have any current assignments.

$13.$ Create a stored procedure named GetProjectAssignmentStatistics that

retrieves statistics on the number of assignments per project.

Submission Requirements:

$$ Combine all SQL scripts and screenshots of the output into a single document.

$$ Ensure that each task's script and corresponding output screenshot are clearly

labeled.

Grading Criteria:

$$ Correctness of SQL scripts and procedures.

$$ Accuracy of the output.

$$ Completeness of the submission $(5$ tasks completed$).$

$$ Proper formatting and organization of the docum