Project Title: Employee Management System in SQL Server
Objective:
Create a database to manage employees and their departments. Implement tables,
relationships, and various SQL operations like joins, views, functions, stored procedures, and
security roles.
Scenario Description
A company has multiple departments, and each department has several employees. You are
tasked to create an SQL Server database with the following requirements:
1. Create a database named CompanyDB.
2. Create two tables:
○ Departments: To store department details (DepartmentID, DepartmentName).
○Employees: To store employee details (EmployeeID, FirstName, LastName,
DepartmentID, Salary).
3. Add constraints:
○ Use DepartmentID as a primary key in the Departments table.
○ Use EmployeeID as a primary key in the Employees table.
○ Establish a foreign key relationship between Employees.DepartmentID and
Departments.DepartmentID.
4. Insert sample data into both tables.
5. Perform queries:
○ Use joins to retrieve employee names along with their department names.
○ Use OFFSET FETCH for pagination.
6. Create a view that displays high-salaried employees (salary > 5000).
7. Create a user-defined function to calculate annual salary.
8. Create a stored procedure to insert a new employee record.
9. Work with server and database roles:
○ Create a database role and assign permissions to view data.
○ Assign a server role to manage database backup.
Practical Implementation
Step 1: Create Database
CREATE DATABASE CompanyDB;
GO
USE CompanyDB;
GO
Step 2: Create Tables
-- Create Departments Table
CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName NVARCHAR(50) NOT NULL
);
-- Create Employees Table
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
DepartmentID INT NOT NULL,
Salary DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);
Step 3: Insert Sample Data
-- Insert data into Departments
INSERT INTO Departments (DepartmentID, DepartmentName)
VALUES
(1, 'HR'),
(2, 'IT'),
(3, 'Finance');
-- Insert data into Employees
INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID,
Salary)
VALUES
(101, 'John', 'Doe', 1, 4500),
(102, 'Jane', 'Smith', 2, 6000),
(103, 'Sam', 'Wilson', 2, 5500),
(104, 'Anna', 'Taylor', 3, 7000);
Step 4: Perform Joins
-- Join to retrieve employee names and department names
SELECT
E.FirstName + ' ' + E.LastName AS EmployeeName,
D.DepartmentName
FROM
Employees E
JOIN
Departments D ON E.DepartmentID = D.DepartmentID;
Step 5: Use OFFSET FETCH
: Retrieve 2 records starting from the second record
SELECT
EmployeeID, FirstName, LastName, Salary
FROM
Employees
ORDER BY
Salary DESC
OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY;
Step 6: Create a View
-- View for high-salaried employees
CREATE VIEW HighSalariedEmployees AS
SELECT
EmployeeID, FirstName, LastName, Salary
FROM
Employees
WHERE
Salary > 5000;
Step 7: Create a Function
-- Function to calculate annual salary
CREATE FUNCTION GetAnnualSalary (@EmployeeID INT)
RETURNS DECIMAL(10, 2)
AS
BEGIN
DECLARE @AnnualSalary DECIMAL(10, 2);
SELECT @AnnualSalary = Salary * 12 FROM Employees WHERE EmployeeID
= @EmployeeID;
RETURN @AnnualSalary;
END;
-- Test the function
SELECT dbo.GetAnnualSalary(102) AS AnnualSalary;
Step 8: Create a Stored Procedure
-- Stored procedure to insert a new employee
CREATE PROCEDURE InsertEmployee
@EmployeeID INT,
@FirstName NVARCHAR(50),
@LastName NVARCHAR(50),
@DepartmentID INT,
@Salary DECIMAL(10, 2)
AS
BEGIN
INSERT INTO Employees (EmployeeID, FirstName, LastName,
DepartmentID, Salary)
VALUES (@EmployeeID, @FirstName, @LastName, @DepartmentID,
@Salary);
END;
-- Execute the procedure
EXEC InsertEmployee 105, 'Mark', 'Lee', 1, 4800;
Step 9: Server Roles and Database Roles
Database Role:
-- Create a database role and assign SELECT permission
CREATE ROLE DataViewer;
GRANT SELECT ON Employees TO DataViewer;
-- Assign the role to a user
ALTER ROLE DataViewer ADD MEMBER [username];
Server Role:
-- Add a user to a server role (e.g., db_backupoperator for backups)
ALTER SERVER ROLE db_backupoperator ADD MEMBER [username];
