Comprehensive SQL Database Training Guide: HR Management System with Attendance and Payroll

Database Overview

I'll create a detailed HR database system for attendance and payroll management with 12 tables, sample data, and comprehensive examples for SQL practice.

-- Database Creation
CREATE DATABASE HR_Management;
USE HR_Management;

Table Structure and Sample Data

1. Departments Table

CREATE TABLE Departments (
    DepartmentID INT IDENTITY(1,1) PRIMARY KEY,  
    DepartmentName VARCHAR(100) NOT NULL UNIQUE,
    DepartmentHeadID INT NULL,
    Location VARCHAR(100) NULL,
    Budget DECIMAL(15,2) NULL,
    EstablishedDate DATE NULL,
    Description VARCHAR(MAX) NULL, -- TEXT is deprecated in SQL Server
    IsActive BIT DEFAULT 1, -- BOOLEAN replaced with BIT
    CreatedAt DATETIME DEFAULT GETDATE(),
    UpdatedAt DATETIME DEFAULT GETDATE()
);

INSERT INTO Departments (DepartmentName, DepartmentHeadID, Location, Budget, EstablishedDate, Description) VALUES
('Human Resources', 101, 'Floor 3, Building A', 500000.00, '2020-01-15', 'Handles recruitment, employee relations, and benefits administration'),
('Information Technology', 102, 'Floor 5, Building B', 1200000.00, '2020-02-10', 'Manages technology infrastructure and software development'),
('Finance', 103, 'Floor 2, Building A', 800000.00, '2020-01-20', 'Handles accounting, budgeting, and financial reporting'),
('Marketing', 104, 'Floor 4, Building C', 750000.00, '2020-03-05', 'Responsible for brand management and marketing campaigns'),
('Operations', 105, 'Floor 1, Building B', 950000.00, '2020-02-15', 'Manages daily business activities and processes');

2. Employees Table

CREATE TABLE Employees (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) NOT NULL UNIQUE,
    Phone VARCHAR(20) NULL,
    DateOfBirth DATE NULL,
    HireDate DATE NOT NULL,
    JobTitle VARCHAR(100) NULL,
    DepartmentID INT NULL,
    ManagerID INT NULL,
    Salary DECIMAL(10,2) NULL,
    Status VARCHAR(20) DEFAULT 'Active',
    BankAccountNumber VARCHAR(50) NULL,
    NationalID VARCHAR(50) UNIQUE,
    CreatedAt DATETIME DEFAULT GETDATE(),
    UpdatedAt DATETIME DEFAULT GETDATE(),
    CONSTRAINT FK_Employees_Departments FOREIGN KEY (DepartmentID)
        REFERENCES Departments(DepartmentID)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    CONSTRAINT FK_Employees_Manager FOREIGN KEY (ManagerID)
        REFERENCES Employees(EmployeeID)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);


INSERT INTO Employees (FirstName, LastName, Email, Phone, DateOfBirth, HireDate, JobTitle, DepartmentID, ManagerID, Salary, BankAccountNumber, NationalID) VALUES
('John', 'Smith', 'john.smith@company.com', '555-0101', '1985-03-15', '2020-05-10', 'HR Manager', 1, NULL, 85000.00, 'ACC001234567', 'NID001'),
('Sarah', 'Johnson', 'sarah.johnson@company.com', '555-0102', '1990-07-22', '2020-06-15', 'IT Manager', 2, NULL, 95000.00, 'ACC001234568', 'NID002'),
('Michael', 'Brown', 'michael.brown@company.com', '555-0103', '1982-11-05', '2020-05-20', 'Finance Manager', 3, NULL, 90000.00, 'ACC001234569', 'NID003'),
('Emily', 'Davis', 'emily.davis@company.com', '555-0104', '1988-09-12', '2020-07-01', 'Marketing Manager', 4, NULL, 88000.00, 'ACC001234570', 'NID004'),
('Robert', 'Wilson', 'robert.wilson@company.com', '555-0105', '1979-12-30', '2020-06-01', 'Operations Manager', 5, NULL, 92000.00, 'ACC001234571', 'NID005'),
('Jennifer', 'Miller', 'jennifer.miller@company.com', '555-0106', '1992-04-18', '2020-08-15', 'HR Specialist', 1, 1, 60000.00, 'ACC001234572', 'NID006'),
('David', 'Taylor', 'david.taylor@company.com', '555-0107', '1991-06-25', '2020-09-10', 'Software Developer', 2, 2, 75000.00, 'ACC001234573', 'NID007');
-- Additional 15 employees would be added here

3. AttendanceRecords Table

CREATE TABLE AttendanceRecords (
    AttendanceID INT IDENTITY(1,1) PRIMARY KEY, -- AUTO_INCREMENT equivalent
    EmployeeID INT NOT NULL,
    [Date] DATE NOT NULL, -- reserved keyword, so use []
    ClockIn TIME NULL,
    ClockOut TIME NULL,
    HoursWorked DECIMAL(4,2) NULL,
    Status VARCHAR(20) DEFAULT 'Present', -- ENUM replaced with VARCHAR
    Notes VARCHAR(MAX) NULL, -- TEXT replaced with VARCHAR(MAX)
    CreatedAt DATETIME DEFAULT GETDATE(),
    UpdatedAt DATETIME DEFAULT GETDATE(),
    CONSTRAINT FK_Attendance_Employees FOREIGN KEY (EmployeeID)
        REFERENCES Employees(EmployeeID)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT UQ_Attendance_EmployeeDate UNIQUE (EmployeeID, [Date])
);

INSERT INTO AttendanceRecords (EmployeeID, Date, ClockIn, ClockOut, HoursWorked, Status, Notes) VALUES
(1, '2023-10-01', '08:58:00', '17:02:00', 8.07, 'Present', NULL),
(1, '2023-10-02', '09:05:00', '17:10:00', 8.08, 'Late', 'Traffic delay'),
(2, '2023-10-01', '08:45:00', '17:15:00', 8.50, 'Present', NULL),
(2, '2023-10-02', '08:50:00', '17:05:00', 8.25, 'Present', NULL),
(3, '2023-10-01', '09:15:00', '16:45:00', 7.50, 'Late', 'Doctor appointment'),
(3, '2023-10-02', '08:55:00', '17:10:00', 8.25, 'Present', NULL);
-- Additional records for all employees for multiple dates would be added

4. LeaveTypes Table

CREATE TABLE LeaveTypes (
    LeaveTypeID INT IDENTITY(1,1) PRIMARY KEY, -- AUTO_INCREMENT equivalent
    LeaveName VARCHAR(50) NOT NULL UNIQUE,
    Description VARCHAR(MAX) NULL, -- TEXT replaced with VARCHAR(MAX)
    MaxDays INT NOT NULL,
    IsPaid BIT DEFAULT 1, -- BOOLEAN replaced with BIT (1 = TRUE, 0 = FALSE)
    RequiresApproval BIT DEFAULT 1,
    CreatedAt DATETIME DEFAULT GETDATE()
);


INSERT INTO LeaveTypes (LeaveName, Description, MaxDays, IsPaid, RequiresApproval) VALUES
('Annual Leave', 'Paid time off work', 20, TRUE, TRUE),
('Sick Leave', 'Leave for health reasons', 10, TRUE, TRUE),
('Maternity Leave', 'Leave for childbirth', 90, TRUE, TRUE),
('Paternity Leave', 'Leave for new fathers', 10, TRUE, TRUE),
('Bereavement Leave', 'Leave for family loss', 5, TRUE, TRUE),
('Unpaid Leave', 'Leave without pay', 30, FALSE, TRUE);

5. LeaveApplications Table

CREATE TABLE LeaveApplications (
    LeaveID INT IDENTITY(1,1) PRIMARY KEY, -- AUTO_INCREMENT equivalent
    EmployeeID INT NOT NULL,
    LeaveTypeID INT NOT NULL,
    StartDate DATE NOT NULL,
    EndDate DATE NOT NULL,
    DaysRequested INT NULL,
    Reason VARCHAR(MAX) NULL, -- TEXT replaced with VARCHAR(MAX)
    Status VARCHAR(20) DEFAULT 'Pending', -- ENUM replaced with VARCHAR
    ApprovedBy INT NULL,
    ApprovedDate DATE NULL,
    CreatedAt DATETIME DEFAULT GETDATE(),
    UpdatedAt DATETIME DEFAULT GETDATE(),
    CONSTRAINT FK_LeaveApplications_Employee FOREIGN KEY (EmployeeID)
        REFERENCES Employees(EmployeeID)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT FK_LeaveApplications_LeaveType FOREIGN KEY (LeaveTypeID)
        REFERENCES LeaveTypes(LeaveTypeID)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT FK_LeaveApplications_ApprovedBy FOREIGN KEY (ApprovedBy)
        REFERENCES Employees(EmployeeID)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);


INSERT INTO LeaveApplications (EmployeeID, LeaveTypeID, StartDate, EndDate, DaysRequested, Reason, Status, ApprovedBy, ApprovedDate) VALUES
(1, 1, '2023-10-10', '2023-10-12', 3, 'Family vacation', 'Approved', 1, '2023-10-05'),
(2, 2, '2023-10-05', '2023-10-06', 2, 'Flu', 'Approved', 1, '2023-10-04'),
(3, 1, '2023-10-15', '2023-10-20', 5, 'Personal time off', 'Pending', NULL, NULL),
(4, 3, '2023-11-01', '2024-01-29', 90, 'Childbirth', 'Approved', 1, '2023-09-20');

6. PayrollPeriods Table

CREATE TABLE PayrollPeriods (
    PeriodID INT IDENTITY(1,1) PRIMARY KEY, -- AUTO_INCREMENT equivalent
    PeriodName VARCHAR(50) NOT NULL,
    StartDate DATE NOT NULL,
    EndDate DATE NOT NULL,
    PayDate DATE NOT NULL,
    Status VARCHAR(20) DEFAULT 'Open', -- ENUM replaced with VARCHAR
    CreatedAt DATETIME DEFAULT GETDATE(),
    UpdatedAt DATETIME DEFAULT GETDATE()
);


INSERT INTO PayrollPeriods (PeriodName, StartDate, EndDate, PayDate, Status) VALUES
('October 2023', '2023-10-01', '2023-10-31', '2023-11-05', 'Processed'),
('November 2023', '2023-11-01', '2023-11-30', '2023-12-05', 'Open'),
('December 2023', '2023-12-01', '2023-12-31', '2024-01-05', 'Open');

7. SalaryComponents Table

CREATE TABLE SalaryComponents (
    ComponentID INT IDENTITY(1,1) PRIMARY KEY, -- AUTO_INCREMENT equivalent
    ComponentName VARCHAR(100) NOT NULL UNIQUE,
    ComponentType VARCHAR(20) NOT NULL, -- ENUM replaced with VARCHAR
    Description VARCHAR(MAX) NULL, -- TEXT replaced with VARCHAR(MAX)
    IsActive BIT DEFAULT 1, -- BOOLEAN replaced with BIT
    IsTaxable BIT DEFAULT 1,
    CreatedAt DATETIME DEFAULT GETDATE(),
    CONSTRAINT CK_SalaryComponents_ComponentType CHECK (ComponentType IN ('Earning', 'Deduction'))
);


INSERT INTO SalaryComponents (ComponentName, ComponentType, Description, IsTaxable) VALUES
('Basic Salary', 'Earning', 'Base monthly salary', TRUE),
('House Rent Allowance', 'Earning', 'Allowance for housing', TRUE),
('Transport Allowance', 'Earning', 'Allowance for transportation', TRUE),
('Overtime Pay', 'Earning', 'Payment for extra hours worked', TRUE),
('Bonus', 'Earning', 'Performance bonus', TRUE),
('Provident Fund', 'Deduction', 'Retirement savings deduction', FALSE),
('Income Tax', 'Deduction', 'Tax deduction', TRUE),
('Health Insurance', 'Deduction', 'Medical insurance premium', FALSE);

8. EmployeeSalary Table

CREATE TABLE EmployeeSalary (
    SalaryID INT IDENTITY(1,1) PRIMARY KEY, -- AUTO_INCREMENT equivalent
    EmployeeID INT NOT NULL,
    ComponentID INT NOT NULL,
    Amount DECIMAL(10,2) NOT NULL,
    EffectiveDate DATE NOT NULL,
    EndDate DATE NULL,
    CreatedAt DATETIME DEFAULT GETDATE(),
    UpdatedAt DATETIME DEFAULT GETDATE(),
    CONSTRAINT FK_EmployeeSalary_Employee FOREIGN KEY (EmployeeID)
        REFERENCES Employees(EmployeeID)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT FK_EmployeeSalary_Component FOREIGN KEY (ComponentID)
        REFERENCES SalaryComponents(ComponentID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);


INSERT INTO EmployeeSalary (EmployeeID, ComponentID, Amount, EffectiveDate) VALUES
(1, 1, 60000.00, '2023-01-01'),
(1, 2, 15000.00, '2023-01-01'),
(1, 3, 10000.00, '2023-01-01'),
(1, 6, 6000.00, '2023-01-01'),
(1, 7, 12000.00, '2023-01-01'),
(2, 1, 65000.00, '2023-01-01'),
(2, 2, 16000.00, '2023-01-01'),
(2, 3, 10000.00, '2023-01-01'),
(2, 6, 6500.00, '2023-01-01'),
(2, 7, 13000.00, '2023-01-01');

9. PayrollTransactions Table

CREATE TABLE PayrollTransactions (
    TransactionID BIGINT IDENTITY(1,1) PRIMARY KEY, -- AUTO_INCREMENT equivalent
    EmployeeID INT NOT NULL,
    PeriodID INT NOT NULL,
    ComponentID INT NOT NULL,
    Amount DECIMAL(10,2) NOT NULL,
    Description VARCHAR(MAX) NULL, -- TEXT replaced with VARCHAR(MAX)
    CreatedAt DATETIME DEFAULT GETDATE(),
    CONSTRAINT FK_PayrollTransactions_Employee FOREIGN KEY (EmployeeID)
        REFERENCES Employees(EmployeeID)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT FK_PayrollTransactions_Period FOREIGN KEY (PeriodID)
        REFERENCES PayrollPeriods(PeriodID)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT FK_PayrollTransactions_Component FOREIGN KEY (ComponentID)
        REFERENCES SalaryComponents(ComponentID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);


INSERT INTO PayrollTransactions (EmployeeID, PeriodID, ComponentID, Amount, Description) VALUES
(1, 1, 1, 60000.00, 'Basic Salary for October 2023'),
(1, 1, 2, 15000.00, 'HRA for October 2023'),
(1, 1, 3, 10000.00, 'Transport Allowance for October 2023'),
(1, 1, 4, 5000.00, 'Overtime for 10 hours'),
(1, 1, 6, 6000.00, 'PF deduction'),
(1, 1, 7, 12000.00, 'Income Tax deduction'),
(2, 1, 1, 65000.00, 'Basic Salary for October 2023'),
(2, 1, 2, 16000.00, 'HRA for October 2023'),
(2, 1, 3, 10000.00, 'Transport Allowance for October 2023'),
(2, 1, 6, 6500.00, 'PF deduction'),
(2, 1, 7, 13000.00, 'Income Tax deduction');

10. OvertimeRequests Table

CREATE TABLE OvertimeRequests (
    OvertimeID INT IDENTITY(1,1) PRIMARY KEY, -- AUTO_INCREMENT equivalent
    EmployeeID INT NOT NULL,
    RequestDate DATE NOT NULL,
    Hours DECIMAL(4,2) NOT NULL,
    Reason VARCHAR(MAX) NULL, -- TEXT replaced with VARCHAR(MAX)
    Status VARCHAR(20) DEFAULT 'Pending', -- ENUM replaced with VARCHAR
    ApprovedBy INT NULL,
    ApprovedDate DATE NULL,
    CreatedAt DATETIME DEFAULT GETDATE(),
    UpdatedAt DATETIME DEFAULT GETDATE(),
    CONSTRAINT FK_OvertimeRequests_Employee FOREIGN KEY (EmployeeID)
        REFERENCES Employees(EmployeeID)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT FK_OvertimeRequests_ApprovedBy FOREIGN KEY (ApprovedBy)
        REFERENCES Employees(EmployeeID)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);


INSERT INTO OvertimeRequests (EmployeeID, RequestDate, Hours, Reason, Status, ApprovedBy, ApprovedDate) VALUES
(1, '2023-10-15', 3.5, 'Project deadline', 'Approved', 1, '2023-10-14'),
(2, '2023-10-20', 4.0, 'System migration', 'Approved', 2, '2023-10-19'),
(3, '2023-10-25', 5.0, 'Month-end closing', 'Pending', NULL, NULL);

11. TaxConfigurations Table

CREATE TABLE TaxConfigurations (
    TaxConfigID INT IDENTITY(1,1) PRIMARY KEY, -- AUTO_INCREMENT equivalent
    TaxYear INT NOT NULL, -- YEAR type replaced with INT
    LowerLimit DECIMAL(10,2) NOT NULL,
    UpperLimit DECIMAL(10,2) NULL,
    TaxRate DECIMAL(5,2) NOT NULL,
    DeductionAmount DECIMAL(10,2) NOT NULL DEFAULT 0,
    Description VARCHAR(MAX) NULL, -- TEXT replaced with VARCHAR(MAX)
    CreatedAt DATETIME DEFAULT GETDATE()
);


INSERT INTO TaxConfigurations (TaxYear, LowerLimit, UpperLimit, TaxRate, DeductionAmount, Description) VALUES
(2023, 0, 250000, 0, 0, 'No tax for income up to 2.5L'),
(2023, 250000, 500000, 5, 0, '5% tax for income between 2.5L-5L'),
(2023, 500000, 750000, 10, 12500, '10% tax for income between 5L-7.5L'),
(2023, 750000, 1000000, 15, 37500, '15% tax for income between 7.5L-10L'),
(2023, 1000000, 1250000, 20, 75000, '20% tax for income between 10L-12.5L'),
(2023, 1250000, 1500000, 25, 125000, '25% tax for income between 12.5L-15L'),
(2023, 1500000, NULL, 30, 187500, '30% tax for income above 15L');

12. EmployeeDocuments Table

CREATE TABLE EmployeeDocuments (
    DocumentID INT IDENTITY(1,1) PRIMARY KEY, -- AUTO_INCREMENT equivalent
    EmployeeID INT NOT NULL,
    DocumentType VARCHAR(50) NOT NULL, -- ENUM replaced with VARCHAR
    DocumentName VARCHAR(255) NOT NULL,
    FilePath VARCHAR(500) NOT NULL,
    IssueDate DATE NULL,
    ExpiryDate DATE NULL,
    Description VARCHAR(MAX) NULL, -- TEXT replaced with VARCHAR(MAX)
    UploadedAt DATETIME DEFAULT GETDATE(),
    CONSTRAINT FK_EmployeeDocuments_Employee FOREIGN KEY (EmployeeID)
        REFERENCES Employees(EmployeeID)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT CK_EmployeeDocuments_DocumentType CHECK (DocumentType IN ('Contract', 'ID Proof', 'Degree', 'Certificate', 'Other'))
);


INSERT INTO EmployeeDocuments (EmployeeID, DocumentType, DocumentName, FilePath, IssueDate, ExpiryDate, Description) VALUES
(1, 'Contract', 'Employment Contract', '/documents/1/contract.pdf', '2020-05-10', '2023-05-10', 'Initial employment contract'),
(1, 'ID Proof', 'Passport', '/documents/1/passport.pdf', '2018-03-15', '2028-03-15', 'Passport copy'),
(2, 'Contract', 'Employment Contract', '/documents/2/contract.pdf', '2020-06-15', '2023-06-15', 'Initial employment contract'),
(2, 'Degree', 'Master Degree', '/documents/2/degree.pdf', '2015-05-20', NULL, 'Masters in Computer Science');

SQL Practice Exercises

Table Operations

-- 1. Create a new table for training records
CREATE TABLE TrainingRecords (
    TrainingID INT IDENTITY(1,1) PRIMARY KEY, -- AUTO_INCREMENT equivalent
    EmployeeID INT NOT NULL,
    TrainingName VARCHAR(255) NOT NULL,
    TrainingDate DATE NOT NULL,
    DurationHours DECIMAL(4,2) NULL,
    Provider VARCHAR(100) NULL,
    Cost DECIMAL(10,2) NULL,
    Status VARCHAR(20) DEFAULT 'Planned', -- ENUM replaced with VARCHAR
    CreatedAt DATETIME DEFAULT GETDATE(),
    CONSTRAINT FK_TrainingRecords_Employee FOREIGN KEY (EmployeeID)
        REFERENCES Employees(EmployeeID)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT CK_TrainingRecords_Status CHECK (Status IN ('Planned', 'Completed', 'Cancelled'))
);

-- 2. Alter table to add a new column
ALTER TABLE TrainingRecords
ADD CertificateIssued BIT DEFAULT 0; -- BOOLEAN replaced with BIT

-- 3. Drop a column
ALTER TABLE TrainingRecords
DROP COLUMN CertificateIssued;

-- 4. Rename a column
EXEC sp_rename 'TrainingRecords.DurationHours', 'Duration', 'COLUMN';

-- 5. Add a foreign key constraint (already added during create, but syntax for adding separately)
ALTER TABLE TrainingRecords
ADD CONSTRAINT FK_TrainingRecords_Employee2
FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
ON DELETE CASCADE
ON UPDATE CASCADE;

-- 6. Create an index on frequently queried columns
CREATE INDEX idx_employee_department ON Employees(DepartmentID);
CREATE INDEX idx_attendance_date ON AttendanceRecords([Date]); -- Date is a reserved word
CREATE INDEX idx_payroll_period ON PayrollTransactions(PeriodID);

-- 7. Drop a table if exists
IF OBJECT_ID('TrainingRecords', 'U') IS NOT NULL
    DROP TABLE TrainingRecords;

Data Manipulation Language (DML)

-- 1. Insert new employee
INSERT INTO Employees (FirstName, LastName, Email, Phone, DateOfBirth, HireDate, JobTitle, DepartmentID, Salary, BankAccountNumber, NationalID)
VALUES ('James', 'Anderson', 'james.anderson@company.com', '555-0123', '1993-08-14', '2023-10-01', 'Marketing Specialist', 4, 55000.00, 'ACC001234599', 'NID023');

-- 2. Update employee salary
UPDATE Employees 
SET Salary = Salary * 1.05
WHERE DepartmentID = 2 AND Status = 'Active';

-- 3. Delete an employee (set to Terminated instead of actual delete)
UPDATE Employees
SET Status = 'Terminated', UpdatedAt = GETDATE()
WHERE EmployeeID = 10;

-- 4. Insert multiple attendance records
INSERT INTO AttendanceRecords (EmployeeID, [Date], ClockIn, ClockOut, HoursWorked, Status) VALUES
(5, '2023-10-03', '08:55:00', '17:05:00', 8.17, 'Present'),
(6, '2023-10-03', '09:00:00', '17:00:00', 8.00, 'Present'),
(7, '2023-10-03', '08:50:00', '17:10:00', 8.33, 'Present');

-- 5. Update leave application status
UPDATE LeaveApplications
SET Status = 'Approved', ApprovedBy = 1, ApprovedDate = CAST(GETDATE() AS DATE)
WHERE LeaveID = 3;

-- 6. Delete specific attendance record
DELETE FROM AttendanceRecords
WHERE EmployeeID = 5 AND [Date] = '2023-10-03';

-- 7. Truncate table (removes all data)
TRUNCATE TABLE OvertimeRequests;

Data Query Language (DQL) - SELECT Queries

-- 1. Basic select with filtering
SELECT * 
FROM Employees 
WHERE DepartmentID = 1 AND Status = 'Active';

-- 2. Select specific columns with aliases
SELECT 
    EmployeeID AS ID,
    FirstName + ' ' + LastName AS FullName, -- CONCAT in SQL Server
    Email,
    Salary
FROM Employees
WHERE Salary > 70000;

-- 3. Using aggregate functions
SELECT 
    DepartmentID,
    COUNT(*) AS EmployeeCount,
    AVG(Salary) AS AverageSalary,
    MAX(Salary) AS MaxSalary,
    MIN(Salary) AS MinSalary
FROM Employees
WHERE Status = 'Active'
GROUP BY DepartmentID;

-- 4. Date functions
SELECT 
    EmployeeID,
    [Date],
    ClockIn,
    ClockOut,
    DATEDIFF(SECOND, ClockIn, ClockOut)/3600.0 AS TimeWorked, -- TIMEDIFF equivalent in hours
    DATENAME(WEEKDAY, [Date]) AS DayOfWeek -- DAYNAME equivalent
FROM AttendanceRecords
WHERE [Date] BETWEEN '2023-10-01' AND '2023-10-07';

-- 5. Case statements
SELECT 
    EmployeeID,
    [Date],
    Status,
    CASE 
        WHEN Status = 'Present' THEN 'Worked'
        WHEN Status = 'Absent' THEN 'Missing'
        WHEN Status = 'Late' THEN 'Delayed'
        ELSE 'Other'
    END AS StatusDescription
FROM AttendanceRecords;

-- 6. Subqueries
SELECT 
    FirstName + ' ' + LastName AS EmployeeName,
    Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

-- 7. Window functions
SELECT 
    DepartmentID,
    FirstName + ' ' + LastName AS EmployeeName,
    Salary,
    RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS SalaryRank
FROM Employees
WHERE Status = 'Active';

JOIN Operations with Business Cases

-- 1. INNER JOIN: Get employees with their department details
SELECT 
    e.EmployeeID,
    e.FirstName + ' ' + e.LastName AS EmployeeName, -- CONCAT replaced
    e.JobTitle,
    d.DepartmentName,
    d.Location
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE e.Status = 'Active';

-- 2. LEFT JOIN: Get all employees and their attendance for a specific date
SELECT 
    e.EmployeeID,
    e.FirstName + ' ' + e.LastName AS EmployeeName,
    a.[Date],
    a.ClockIn,
    a.ClockOut,
    a.Status
FROM Employees e
LEFT JOIN AttendanceRecords a ON e.EmployeeID = a.EmployeeID AND a.[Date] = '2023-10-01'
WHERE e.Status = 'Active';

-- 3. RIGHT JOIN: Get all departments and their employees (even if no employees)
SELECT 
    d.DepartmentName,
    d.Location,
    COUNT(e.EmployeeID) AS EmployeeCount
FROM Employees e
RIGHT JOIN Departments d ON e.DepartmentID = d.DepartmentID AND e.Status = 'Active'
GROUP BY d.DepartmentID, d.DepartmentName, d.Location;

-- 4. FULL OUTER JOIN simulation (using UNION of LEFT and RIGHT joins)
SELECT 
    e.EmployeeID,
    e.FirstName + ' ' + e.LastName AS EmployeeName,
    d.DepartmentName
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID
UNION
SELECT 
    e.EmployeeID,
    e.FirstName + ' ' + e.LastName AS EmployeeName,
    d.DepartmentName
FROM Employees e
RIGHT JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- 5. SELF JOIN: Get employees and their managers
SELECT 
    e.EmployeeID,
    e.FirstName + ' ' + e.LastName AS EmployeeName,
    e.JobTitle,
    m.EmployeeID AS ManagerID,
    m.FirstName + ' ' + m.LastName AS ManagerName,
    m.JobTitle AS ManagerJobTitle
FROM Employees e
LEFT JOIN Employees m ON e.ManagerID = m.EmployeeID;

-- 6. Multiple JOINs: Get payroll details for employees
SELECT 
    e.EmployeeID,
    e.FirstName + ' ' + e.LastName AS EmployeeName,
    p.PeriodName,
    sc.ComponentName,
    sc.ComponentType,
    pt.Amount
FROM Employees e
INNER JOIN PayrollTransactions pt ON e.EmployeeID = pt.EmployeeID
INNER JOIN PayrollPeriods p ON pt.PeriodID = p.PeriodID
INNER JOIN SalaryComponents sc ON pt.ComponentID = sc.ComponentID
WHERE p.PeriodName = 'October 2023'
ORDER BY e.EmployeeID, sc.ComponentType DESC, sc.ComponentName;

-- 7. Complex JOIN with aggregation: Calculate total earnings and deductions for each employee
SELECT 
    e.EmployeeID,
    e.FirstName + ' ' + e.LastName AS EmployeeName,
    p.PeriodName,
    SUM(CASE WHEN sc.ComponentType = 'Earning' THEN pt.Amount ELSE 0 END) AS TotalEarnings,
    SUM(CASE WHEN sc.ComponentType = 'Deduction' THEN pt.Amount ELSE 0 END) AS TotalDeductions,
    SUM(CASE WHEN sc.ComponentType = 'Earning' THEN pt.Amount ELSE 0 END) - 
    SUM(CASE WHEN sc.ComponentType = 'Deduction' THEN pt.Amount ELSE 0 END) AS NetPay
FROM Employees e
INNER JOIN PayrollTransactions pt ON e.EmployeeID = pt.EmployeeID
INNER JOIN PayrollPeriods p ON pt.PeriodID = p.PeriodID
INNER JOIN SalaryComponents sc ON pt.ComponentID = sc.ComponentID
WHERE p.PeriodName = 'October 2023'
GROUP BY e.EmployeeID, e.FirstName, e.LastName, p.PeriodName
ORDER BY NetPay DESC;

Advanced Queries and Business Cases

-- 1. Calculate employee attendance summary for a month
SELECT 
    e.EmployeeID,
    e.FirstName + ' ' + e.LastName AS EmployeeName,
    COUNT(CASE WHEN a.Status = 'Present' THEN 1 END) AS PresentDays,
    COUNT(CASE WHEN a.Status = 'Absent' THEN 1 END) AS AbsentDays,
    COUNT(CASE WHEN a.Status = 'Late' THEN 1 END) AS LateDays,
    COUNT(CASE WHEN a.Status = 'HalfDay' THEN 1 END) AS HalfDays,
    SUM(a.HoursWorked) AS TotalHours
FROM Employees e
LEFT JOIN AttendanceRecords a 
    ON e.EmployeeID = a.EmployeeID 
    AND a.[Date] BETWEEN '2023-10-01' AND '2023-10-31'
WHERE e.Status = 'Active'
GROUP BY e.EmployeeID, e.FirstName, e.LastName;

-- 2. Find employees with excessive absenteeism
SELECT 
    e.EmployeeID,
    e.FirstName + ' ' + e.LastName AS EmployeeName,
    d.DepartmentName,
    COUNT(CASE WHEN a.Status = 'Absent' THEN 1 END) AS AbsentDays,
    COUNT(a.AttendanceID) AS TotalWorkingDays
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
LEFT JOIN AttendanceRecords a 
    ON e.EmployeeID = a.EmployeeID 
    AND a.[Date] BETWEEN '2023-10-01' AND '2023-10-31'
WHERE e.Status = 'Active'
GROUP BY e.EmployeeID, e.FirstName, e.LastName, d.DepartmentName
HAVING COUNT(CASE WHEN a.Status = 'Absent' THEN 1 END) > 3;

-- 3. Calculate payroll for a specific period with tax calculation
WITH EmployeeEarnings AS (
    SELECT 
        pt.EmployeeID,
        SUM(CASE WHEN sc.ComponentType = 'Earning' THEN pt.Amount ELSE 0 END) AS GrossEarnings,
        SUM(CASE WHEN sc.ComponentType = 'Deduction' AND sc.ComponentName != 'Income Tax' THEN pt.Amount ELSE 0 END) AS OtherDeductions
    FROM PayrollTransactions pt
    INNER JOIN SalaryComponents sc ON pt.ComponentID = sc.ComponentID
    INNER JOIN PayrollPeriods pp ON pt.PeriodID = pp.PeriodID
    WHERE pp.PeriodName = 'October 2023'
    GROUP BY pt.EmployeeID
),
TaxCalculation AS (
    SELECT 
        ee.EmployeeID,
        ee.GrossEarnings,
        ee.OtherDeductions,
        (ee.GrossEarnings - ee.OtherDeductions) AS TaxableIncome,
        tc.TaxRate,
        tc.DeductionAmount,
        ((ee.GrossEarnings - ee.OtherDeductions - tc.LowerLimit) * tc.TaxRate/100) + tc.DeductionAmount AS CalculatedTax
    FROM EmployeeEarnings ee
    CROSS JOIN TaxConfigurations tc
    WHERE (ee.GrossEarnings - ee.OtherDeductions) >= tc.LowerLimit 
        AND (ee.GrossEarnings - ee.OtherDeductions) < ISNULL(tc.UpperLimit, 999999999)
        AND tc.TaxYear = 2023
)
SELECT 
    e.EmployeeID,
    e.FirstName + ' ' + e.LastName AS EmployeeName,
    tc.GrossEarnings,
    tc.OtherDeductions,
    tc.TaxableIncome,
    tc.CalculatedTax AS IncomeTax,
    (tc.GrossEarnings - tc.OtherDeductions - tc.CalculatedTax) AS NetPay
FROM TaxCalculation tc
INNER JOIN Employees e ON tc.EmployeeID = e.EmployeeID
ORDER BY e.EmployeeID;

-- 4. Find employees who worked overtime in a specific period
SELECT 
    e.EmployeeID,
    e.FirstName + ' ' + e.LastName AS EmployeeName,
    d.DepartmentName,
    SUM(CASE WHEN a.HoursWorked > 8 THEN a.HoursWorked - 8 ELSE 0 END) AS OvertimeHours,
    ROUND(SUM(CASE WHEN a.HoursWorked > 8 THEN a.HoursWorked - 8 ELSE 0 END) * es.Amount, 2) AS OvertimePay
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
INNER JOIN AttendanceRecords a ON e.EmployeeID = a.EmployeeID 
    AND a.[Date] BETWEEN '2023-10-01' AND '2023-10-31'
INNER JOIN EmployeeSalary es ON e.EmployeeID = es.EmployeeID
INNER JOIN SalaryComponents sc ON es.ComponentID = sc.ComponentID
WHERE e.Status = 'Active' 
    AND sc.ComponentName = 'Overtime Pay'
    AND a.HoursWorked > 8
GROUP BY e.EmployeeID, e.FirstName, e.LastName, d.DepartmentName, es.Amount
HAVING SUM(CASE WHEN a.HoursWorked > 8 THEN a.HoursWorked - 8 ELSE 0 END) > 0;

-- 5. Employee leave balance report
SELECT 
    e.EmployeeID,
    e.FirstName + ' ' + e.LastName AS EmployeeName,
    d.DepartmentName,
    lt.LeaveName,
    lt.MaxDays AS AnnualEntitlement,
    COUNT(la.LeaveID) AS LeavesTaken,
    (lt.MaxDays - COUNT(la.LeaveID)) AS RemainingBalance
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
CROSS JOIN LeaveTypes lt
LEFT JOIN LeaveApplications la 
    ON e.EmployeeID = la.EmployeeID 
    AND lt.LeaveTypeID = la.LeaveTypeID 
    AND la.Status = 'Approved'
    AND YEAR(la.StartDate) = 2023
WHERE e.Status = 'Active'
    AND lt.RequiresApproval = 1 -- BOOLEAN in SQL Server is BIT
GROUP BY e.EmployeeID, e.FirstName, e.LastName, d.DepartmentName, lt.LeaveTypeID, lt.LeaveName, lt.MaxDays
ORDER BY e.EmployeeID, lt.LeaveName;

Assignment Questions
Create a report showing all employees who were late more than 3 times in October 2023.
Calculate the total payroll cost for each department for the last processed period.
Find employees who have taken more than their entitled leave days for any leave type.
Create a query to show employee attendance patterns by day of the week.
Develop a query to identify employees who have not taken any leave in the current year.
Calculate the average time between clock-in and clock-out for each employee.
Create a report showing employees with their managers and their manager's manager (up to 2 levels).
Find departments where the average salary is higher than the company's average salary.
Create a query to show employee turnover rate by department.
Develop a function to calculate income tax based on the tax configuration table.
Create a report showing employees with expired documents or documents expiring in the next 30 days.
Calculate the overtime cost for each department for the last month.
Find employees who have changed departments in the last year.
Create a query to show employee salary history and percentage changes.
Develop a query to identify employees who are eligible for promotion based on tenure and performance.
Calculate the cost of absenteeism for each department.
Create a report showing leave utilization by leave type and department.
Find employees with the longest tenure in each department.
Develop a query to forecast payroll expenses for the next quarter.
Create a comprehensive employee profile report with all related information.
 
Employee & HR SQL Practical Assignments
1. List all active employees.
SELECT * FROM Employees WHERE Status = 'Active';

2. Show full names and email of all employees in the Marketing department.
SELECT FirstName + ' ' + LastName AS FullName, Email
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE d.DepartmentName = 'Marketing';

3. Count the number of employees in each department.
SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID;

4. Find the highest paid employee in each department.
SELECT DepartmentID, MAX(Salary) AS MaxSalary
FROM Employees
GROUP BY DepartmentID;

5. List employees whose salary is above the average salary.
SELECT FirstName + ' ' + LastName AS EmployeeName, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

6. Calculate total hours worked for each employee in October 2023.
SELECT EmployeeID, SUM(HoursWorked) AS TotalHours
FROM AttendanceRecords
WHERE [Date] BETWEEN '2023-10-01' AND '2023-10-31'
GROUP BY EmployeeID;

7. Show employees with more than 3 absent days in October 2023.
SELECT e.EmployeeID, e.FirstName + ' ' + e.LastName AS EmployeeName, COUNT(*) AS AbsentDays
FROM AttendanceRecords a
INNER JOIN Employees e ON a.EmployeeID = e.EmployeeID
WHERE a.Status = 'Absent' AND a.[Date] BETWEEN '2023-10-01' AND '2023-10-31'
GROUP BY e.EmployeeID, e.FirstName, e.LastName
HAVING COUNT(*) > 3;

8. List employees and their managers.
SELECT e.FirstName + ' ' + e.LastName AS EmployeeName,
       m.FirstName + ' ' + m.LastName AS ManagerName
FROM Employees e
LEFT JOIN Employees m ON e.ManagerID = m.EmployeeID;

9. Get employees with their department details using INNER JOIN.
SELECT e.EmployeeID, e.FirstName + ' ' + e.LastName AS EmployeeName, d.DepartmentName, d.Location
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

10. Show all employees even if they have no attendance record using LEFT JOIN.
SELECT e.EmployeeID, e.FirstName + ' ' + e.LastName AS EmployeeName, a.[Date], a.Status
FROM Employees e
LEFT JOIN AttendanceRecords a ON e.EmployeeID = a.EmployeeID;

11. Calculate total earnings and deductions per employee for October 2023.
SELECT e.EmployeeID, SUM(CASE WHEN sc.ComponentType='Earning' THEN pt.Amount ELSE 0 END) AS TotalEarnings,
       SUM(CASE WHEN sc.ComponentType='Deduction' THEN pt.Amount ELSE 0 END) AS TotalDeductions
FROM PayrollTransactions pt
INNER JOIN SalaryComponents sc ON pt.ComponentID = sc.ComponentID
INNER JOIN Employees e ON pt.EmployeeID = e.EmployeeID
INNER JOIN PayrollPeriods p ON pt.PeriodID = p.PeriodID
WHERE p.PeriodName='October 2023'
GROUP BY e.EmployeeID;

12. Show employees who worked overtime (>8 hours) in October 2023.
SELECT e.EmployeeID, SUM(a.HoursWorked - 8) AS OvertimeHours
FROM AttendanceRecords a
INNER JOIN Employees e ON a.EmployeeID = e.EmployeeID
WHERE a.HoursWorked > 8 AND a.[Date] BETWEEN '2023-10-01' AND '2023-10-31'
GROUP BY e.EmployeeID;

13. Find employees who never took any leave in 2023.
SELECT e.EmployeeID, e.FirstName + ' ' + e.LastName AS EmployeeName
FROM Employees e
LEFT JOIN LeaveApplications la ON e.EmployeeID = la.EmployeeID AND YEAR(la.StartDate)=2023
WHERE la.LeaveID IS NULL;

14. List leave balance for each leave type per employee.
SELECT e.EmployeeID, lt.LeaveName, lt.MaxDays - COUNT(la.LeaveID) AS RemainingBalance
FROM Employees e
CROSS JOIN LeaveTypes lt
LEFT JOIN LeaveApplications la 
  ON e.EmployeeID = la.EmployeeID AND la.LeaveTypeID = lt.LeaveTypeID AND la.Status='Approved' AND YEAR(la.StartDate)=2023
GROUP BY e.EmployeeID, lt.LeaveName, lt.MaxDays;

15. Show top 3 highest paid employees.
SELECT TOP 3 EmployeeID, FirstName + ' ' + LastName AS EmployeeName, Salary
FROM Employees
ORDER BY Salary DESC;

16. Display employees with their total number of trainings attended.
SELECT e.EmployeeID, e.FirstName + ' ' + e.LastName AS EmployeeName, COUNT(t.TrainingID) AS TrainingsCompleted
FROM Employees e
LEFT JOIN TrainingRecords t ON e.EmployeeID = t.EmployeeID AND t.Status='Completed'
GROUP BY e.EmployeeID, e.FirstName, e.LastName;

17. Find employees with duplicate email addresses (data quality check).
SELECT Email, COUNT(*) AS CountEmails
FROM Employees
GROUP BY Email
HAVING COUNT(*) > 1;

18. Calculate net pay including tax for October 2023.
WITH EmployeeEarnings AS (
    SELECT pt.EmployeeID, SUM(CASE WHEN sc.ComponentType='Earning' THEN pt.Amount ELSE 0 END) AS GrossEarnings,
           SUM(CASE WHEN sc.ComponentType='Deduction' AND sc.ComponentName != 'Income Tax' THEN pt.Amount ELSE 0 END) AS OtherDeductions
    FROM PayrollTransactions pt
    INNER JOIN SalaryComponents sc ON pt.ComponentID = sc.ComponentID
    INNER JOIN PayrollPeriods pp ON pt.PeriodID = pp.PeriodID
    WHERE pp.PeriodName='October 2023'
    GROUP BY pt.EmployeeID
)
SELECT e.EmployeeID, e.FirstName + ' ' + e.LastName AS EmployeeName,
       GrossEarnings, OtherDeductions, (GrossEarnings - OtherDeductions) AS NetPay
FROM EmployeeEarnings ee
INNER JOIN Employees e ON ee.EmployeeID = e.EmployeeID;

19. Rank employees by salary within their department.
SELECT DepartmentID, EmployeeID, FirstName + ' ' + LastName AS EmployeeName,
       RANK() OVER(PARTITION BY DepartmentID ORDER BY Salary DESC) AS SalaryRank
FROM Employees;

20. Show employees whose overtime pay is greater than 1000.
SELECT e.EmployeeID, e.FirstName + ' ' + e.LastName AS EmployeeName, SUM(a.HoursWorked - 8) * es.Amount AS OvertimePay
FROM Employees e
INNER JOIN AttendanceRecords a ON e.EmployeeID = a.EmployeeID
INNER JOIN EmployeeSalary es ON e.EmployeeID = es.EmployeeID
INNER JOIN SalaryComponents sc ON es.ComponentID = sc.ComponentID
WHERE sc.ComponentName='Overtime Pay' AND a.HoursWorked>8
GROUP BY e.EmployeeID, e.FirstName, e.LastName, es.Amount
HAVING SUM(a.HoursWorked-8)*es.Amount > 1000;

21. List employees with their total leave days taken in 2023.
SELECT e.EmployeeID, e.FirstName + ' ' + e.LastName AS EmployeeName, COUNT(la.LeaveID) AS LeavesTaken
FROM Employees e
LEFT JOIN LeaveApplications la ON e.EmployeeID = la.EmployeeID AND la.Status='Approved' AND YEAR(la.StartDate)=2023
GROUP BY e.EmployeeID, e.FirstName, e.LastName;

22. Find the department with maximum number of employees.
SELECT TOP 1 DepartmentID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID
ORDER BY EmployeeCount DESC;

23. Show employees who have not completed any training.
SELECT e.EmployeeID, e.FirstName + ' ' + e.LastName AS EmployeeName
FROM Employees e
LEFT JOIN TrainingRecords t ON e.EmployeeID = t.EmployeeID AND t.Status='Completed'
WHERE t.TrainingID IS NULL;

24. Find employees whose salary increased by more than 5% recently.
-- Assuming we have historical salary table or EffectiveDate in EmployeeSalary
SELECT e.EmployeeID, e.FirstName + ' ' + e.LastName AS EmployeeName, es.Amount AS CurrentSalary
FROM EmployeeSalary es
INNER JOIN Employees e ON es.EmployeeID = e.EmployeeID
WHERE es.Amount > e.Salary * 1.05;

25. List employees and the number of documents uploaded.
SELECT e.EmployeeID, e.FirstName + ' ' + e.LastName AS EmployeeName, COUNT(ed.DocumentID) AS DocumentCount
FROM Employees e
LEFT JOIN EmployeeDocuments ed ON e.EmployeeID = ed.EmployeeID
GROUP BY e.EmployeeID, e.FirstName, e.LastName;

26. Show employees with tax configurations applied (tax > 0).
SELECT e.EmployeeID, e.FirstName + ' ' + e.LastName AS EmployeeName, tc.TaxRate, tc.DeductionAmount
FROM Employees e
CROSS JOIN TaxConfigurations tc
WHERE tc.TaxRate > 0;

27. Find employees with multiple managers (data check).
SELECT ManagerID, COUNT(*) AS NumReports
FROM Employees
WHERE ManagerID IS NOT NULL
GROUP BY ManagerID
HAVING COUNT(*) > 1;

28. Show payroll transactions for a specific component (Bonus).
SELECT e.EmployeeID, e.FirstName + ' ' + e.LastName AS EmployeeName, pt.Amount, sc.ComponentName
FROM PayrollTransactions pt
INNER JOIN Employees e ON pt.EmployeeID = e.EmployeeID
INNER JOIN SalaryComponents sc ON pt.ComponentID = sc.ComponentID
WHERE sc.ComponentName='Bonus';

29. Show employees who joined after 1st Jan 2023.
SELECT EmployeeID, FirstName + ' ' + LastName AS EmployeeName, HireDate
FROM Employees
WHERE HireDate > '2023-01-01';

30. Find employees who have more than 2 types of leave in 2023.
SELECT e.EmployeeID, e.FirstName + ' ' + e.LastName AS EmployeeName, COUNT(DISTINCT la.LeaveTypeID) AS LeaveTypesTaken
FROM Employees e
INNER JOIN LeaveApplications la ON e.EmployeeID = la.EmployeeID AND la.Status='Approved' AND YEAR(la.StartDate)=2023
GROUP BY e.EmployeeID, e.FirstName, e.LastName
HAVING COUNT(DISTINCT la.LeaveTypeID) > 2;

Comprehensive SQL Assignments with Answers
A. DDL (Data Definition Language) Assignments
1. Create a table for projects.
CREATE TABLE Projects (
    ProjectID INT IDENTITY(1,1) PRIMARY KEY,
    ProjectName VARCHAR(100) NOT NULL UNIQUE,
    StartDate DATE,
    EndDate DATE,
    Budget DECIMAL(12,2),
    Status VARCHAR(20) DEFAULT 'Planned'
);

2. Add a column ProjectManagerID to the Projects table.
ALTER TABLE Projects
ADD ProjectManagerID INT;

3. Modify Budget column to allow NULLs.
ALTER TABLE Projects
ALTER COLUMN Budget DECIMAL(12,2) NULL;

4. Drop a column EndDate from Projects.
ALTER TABLE Projects
DROP COLUMN EndDate;

5. Rename column Status to ProjectStatus.
EXEC sp_rename 'Projects.Status', 'ProjectStatus', 'COLUMN';

6. Create a table with foreign key reference.
CREATE TABLE ProjectAssignments (
    AssignmentID INT IDENTITY(1,1) PRIMARY KEY,
    ProjectID INT NOT NULL,
    EmployeeID INT NOT NULL,
    Role VARCHAR(50),
    AssignedDate DATE DEFAULT GETDATE(),
    FOREIGN KEY (ProjectID) REFERENCES Projects(ProjectID),
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);

7. Create a table with CHECK constraint and DEFAULT.
CREATE TABLE Benefits (
    BenefitID INT IDENTITY(1,1) PRIMARY KEY,
    BenefitName VARCHAR(100) NOT NULL,
    IsTaxable BIT DEFAULT 1,
    BenefitType VARCHAR(20) CHECK (BenefitType IN ('Health', 'Transport', 'Food'))
);

8. Create a table with composite primary key.
CREATE TABLE EmployeeSkills (
    EmployeeID INT NOT NULL,
    SkillName VARCHAR(100) NOT NULL,
    ProficiencyLevel VARCHAR(20),
    PRIMARY KEY (EmployeeID, SkillName),
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);

9. Create a view for active employees with salary > 50,000.
CREATE VIEW vw_HighSalaryEmployees AS
SELECT EmployeeID, FirstName + ' ' + LastName AS FullName, Salary, DepartmentID
FROM Employees
WHERE Status='Active' AND Salary > 50000;

10. Drop the view.
DROP VIEW vw_HighSalaryEmployees;


B. DML (Data Manipulation Language) Assignments
11. Insert new employee record.
INSERT INTO Employees (FirstName, LastName, Email, HireDate, JobTitle, DepartmentID, Salary)
VALUES ('Alice', 'Johnson', 'alice.johnson@company.com', '2023-09-01', 'HR Manager', 3, 75000);

12. Update employee salary by 10%.
UPDATE Employees
SET Salary = Salary * 1.1
WHERE DepartmentID = 3;

13. Delete employees who are terminated.
DELETE FROM Employees
WHERE Status='Terminated';

14. Insert multiple attendance records.
INSERT INTO AttendanceRecords (EmployeeID, [Date], ClockIn, ClockOut, HoursWorked, Status)
VALUES
(1, '2023-09-01', '09:00', '17:00', 8, 'Present'),
(2, '2023-09-01', '09:10', '17:05', 7.92, 'Late');

15. Update leave application status.
UPDATE LeaveApplications
SET Status='Approved', ApprovedBy=1, ApprovedDate=GETDATE()
WHERE LeaveID=5;

16. Insert salary component.
INSERT INTO SalaryComponents (ComponentName, ComponentType, IsActive, IsTaxable)
VALUES ('Transport Allowance', 'Earning', 1, 0);

17. Bulk update: Increase salary for employees with more than 5 years.
UPDATE Employees
SET Salary = Salary * 1.05
WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 5;

18. Insert project assignment.
INSERT INTO ProjectAssignments (ProjectID, EmployeeID, Role)
VALUES (1, 2, 'Developer');

19. Delete specific project assignments.
DELETE FROM ProjectAssignments
WHERE ProjectID=3 AND EmployeeID=5;

20. Update multiple columns in employee table.
UPDATE Employees
SET JobTitle='Senior Developer', Salary=Salary*1.15
WHERE EmployeeID=2;


C. DQL (Data Query Language) Assignments
21. Select employees with salary > 60000.
SELECT EmployeeID, FirstName + ' ' + LastName AS FullName, Salary
FROM Employees
WHERE Salary > 60000;

22. Count employees per department.
SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID;

23. Find the highest salary per department.
SELECT DepartmentID, MAX(Salary) AS MaxSalary
FROM Employees
GROUP BY DepartmentID;

24. Display employees and manager names.
SELECT e.EmployeeID, e.FirstName + ' ' + e.LastName AS EmployeeName,
       m.FirstName + ' ' + m.LastName AS ManagerName
FROM Employees e
LEFT JOIN Employees m ON e.ManagerID = m.EmployeeID;

25. Calculate total hours worked in a month.
SELECT EmployeeID, SUM(HoursWorked) AS TotalHours
FROM AttendanceRecords
WHERE [Date] BETWEEN '2023-09-01' AND '2023-09-30'
GROUP BY EmployeeID;

26. Find employees with no attendance records.
SELECT e.EmployeeID, e.FirstName + ' ' + e.LastName AS EmployeeName
FROM Employees e
LEFT JOIN AttendanceRecords a ON e.EmployeeID = a.EmployeeID
WHERE a.AttendanceID IS NULL;

27. Rank employees by salary in each department.
SELECT DepartmentID, EmployeeID, FirstName + ' ' + LastName AS EmployeeName,
       RANK() OVER(PARTITION BY DepartmentID ORDER BY Salary DESC) AS SalaryRank
FROM Employees;

28. Display total earnings and deductions for each employee.
SELECT e.EmployeeID, SUM(CASE WHEN sc.ComponentType='Earning' THEN pt.Amount ELSE 0 END) AS TotalEarnings,
       SUM(CASE WHEN sc.ComponentType='Deduction' THEN pt.Amount ELSE 0 END) AS TotalDeductions
FROM PayrollTransactions pt
INNER JOIN SalaryComponents sc ON pt.ComponentID = sc.ComponentID
INNER JOIN Employees e ON pt.EmployeeID = e.EmployeeID
GROUP BY e.EmployeeID;

29. Show employees with leave balance > 5 days.
SELECT e.EmployeeID, lt.LeaveName, (lt.MaxDays - COUNT(la.LeaveID)) AS RemainingBalance
FROM Employees e
CROSS JOIN LeaveTypes lt
LEFT JOIN LeaveApplications la ON e.EmployeeID=la.EmployeeID AND la.LeaveTypeID=lt.LeaveTypeID AND la.Status='Approved'
GROUP BY e.EmployeeID, lt.LeaveName, lt.MaxDays
HAVING (lt.MaxDays - COUNT(la.LeaveID)) > 5;

30. Find projects with no employees assigned.
SELECT p.ProjectID, p.ProjectName
FROM Projects p
LEFT JOIN ProjectAssignments pa ON p.ProjectID=pa.ProjectID
WHERE pa.AssignmentID IS NULL;


D. Advanced / DCL (Data Control Language) Assignments
31. Create a new user.
CREATE LOGIN HRUser WITH PASSWORD='StrongPass@123';
CREATE USER HRUser FOR LOGIN HRUser;

32. Grant SELECT on Employees to HRUser.
GRANT SELECT ON Employees TO HRUser;

33. Grant INSERT, UPDATE on AttendanceRecords to HRUser.
GRANT INSERT, UPDATE ON AttendanceRecords TO HRUser;

34. Revoke DELETE on Employees.
REVOKE DELETE ON Employees FROM HRUser;

35. Grant EXECUTE on stored procedure (example).
GRANT EXECUTE ON sp_UpdateSalary TO HRUser;
36. Begin a transaction and rollback.
BEGIN TRANSACTION;
UPDATE Employees SET Salary = Salary*1.2 WHERE EmployeeID=3;
ROLLBACK;

37. Commit a transaction after multiple operations.
BEGIN TRANSACTION;
UPDATE Employees SET Salary = Salary*1.1 WHERE DepartmentID=2;
INSERT INTO AttendanceRecords(EmployeeID,[Date],Status) VALUES(3,'2023-09-15','Present');
COMMIT;

38. Create a trigger to update UpdatedAt on employee update.
CREATE TRIGGER trg_UpdateEmployee
ON Employees
AFTER UPDATE
AS
BEGIN
    UPDATE Employees
    SET UpdatedAt = GETDATE()
    FROM Employees e
    INNER JOIN inserted i ON e.EmployeeID=i.EmployeeID;
END;

39. Create a stored procedure to add leave application.
CREATE PROCEDURE sp_AddLeaveApplication
    @EmployeeID INT,
    @LeaveTypeID INT,
    @StartDate DATE,
    @EndDate DATE,
    @Reason VARCHAR(MAX)
AS
BEGIN
    INSERT INTO LeaveApplications(EmployeeID, LeaveTypeID, StartDate, EndDate, Reason, Status, CreatedAt)
    VALUES(@EmployeeID, @LeaveTypeID, @StartDate, @EndDate, @Reason, 'Pending', GETDATE());
END;

40. Execute the stored procedure.
EXEC sp_AddLeaveApplication 1, 2, '2023-10-10', '2023-10-12', 'Personal Work';

Tricky & Confusing SQL Assignments with Answers

1. Find employees whose salary is above the department average.


SELECT EmployeeID, FirstName + ' ' + LastName AS EmployeeName, Salary, DepartmentID
FROM Employees e
WHERE Salary > (
    SELECT AVG(Salary)
    FROM Employees
    WHERE DepartmentID = e.DepartmentID
);

Tricky point: Correlated subquery per department.

2. Get the second highest salary in the company.


SELECT MAX(Salary) AS SecondHighestSalary
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);

Alternative: Use ROW_NUMBER().

3. Find employees who have the same manager as EmployeeID=5.


SELECT EmployeeID, FirstName + ' ' + LastName AS EmployeeName
FROM Employees
WHERE ManagerID = (SELECT ManagerID FROM Employees WHERE EmployeeID=5)
AND EmployeeID != 5;

Tricky point: Exclude the reference employee.

4. Count employees in each department including departments with 0 employees.


SELECT d.DepartmentID, d.DepartmentName, COUNT(e.EmployeeID) AS EmployeeCount
FROM Departments d
LEFT JOIN Employees e ON d.DepartmentID = e.DepartmentID
GROUP BY d.DepartmentID, d.DepartmentName;

Tricky point: Use LEFT JOIN for zero-count rows.

5. Find employees whose salary is neither highest nor lowest in their department.


SELECT e.EmployeeID, e.FirstName + ' ' + e.LastName AS EmployeeName, e.Salary, e.DepartmentID
FROM Employees e
WHERE e.Salary NOT IN (
    SELECT MAX(Salary) FROM Employees WHERE DepartmentID = e.DepartmentID
    UNION
    SELECT MIN(Salary) FROM Employees WHERE DepartmentID = e.DepartmentID
);

Tricky point: Exclude both extremes per department.

6. Identify employees with duplicate emails (data integrity check).


SELECT Email, COUNT(*) AS Occurrences
FROM Employees
GROUP BY Email
HAVING COUNT(*) > 1;

Tricky point: Grouping and HAVING.

7. List employees who never took leave.


SELECT e.EmployeeID, e.FirstName + ' ' + e.LastName AS EmployeeName
FROM Employees e
LEFT JOIN LeaveApplications l ON e.EmployeeID = l.EmployeeID
WHERE l.LeaveID IS NULL;

Tricky point: LEFT JOIN + IS NULL.

8. Find employees whose salary is above average in all departments.


SELECT e.EmployeeID, e.FirstName + ' ' + e.LastName AS EmployeeName
FROM Employees e
WHERE Salary > ALL (
    SELECT AVG(Salary)
    FROM Employees
    GROUP BY DepartmentID
);

Tricky point: Use of ALL with aggregation.

9. Find department with maximum total salary.


SELECT TOP 1 DepartmentID, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY DepartmentID
ORDER BY TotalSalary DESC;

Tricky point: Aggregation + TOP 1 + ORDER BY.

10. Find employees with more than one manager (invalid data scenario).


SELECT EmployeeID, COUNT(DISTINCT ManagerID) AS ManagerCount
FROM Employees
GROUP BY EmployeeID
HAVING COUNT(DISTINCT ManagerID) > 1;

Tricky point: Detect inconsistent foreign key relationships.

11. Calculate running total salary by department.


SELECT DepartmentID, EmployeeID, Salary,
       SUM(Salary) OVER(PARTITION BY DepartmentID ORDER BY Salary DESC) AS RunningTotal
FROM Employees;

Tricky point: Window function with partitioning.

12. Rank employees by salary but skip ties in numbering.


SELECT EmployeeID, FirstName + ' ' + LastName AS EmployeeName, Salary,
       DENSE_RANK() OVER(ORDER BY Salary DESC) AS SalaryRank
FROM Employees;

Tricky point: DENSE_RANK vs RANK vs ROW_NUMBER.

13. Find departments where every employee earns more than 50,000.


SELECT DepartmentID
FROM Employees
GROUP BY DepartmentID
HAVING MIN(Salary) > 50000;

Tricky point: Use MIN in HAVING instead of multiple joins.

14. Identify employees with attendance gap more than 3 days in a month.


WITH EmployeeDates AS (
    SELECT EmployeeID, [Date], LEAD([Date]) OVER(PARTITION BY EmployeeID ORDER BY [Date]) AS NextDate
    FROM AttendanceRecords
)
SELECT EmployeeID, [Date], NextDate, DATEDIFF(DAY, [Date], NextDate) AS Gap
FROM EmployeeDates
WHERE DATEDIFF(DAY, [Date], NextDate) > 3;

Tricky point: LEAD/LAG for gap analysis.

15. List employees with same salary as someone in another department.


SELECT e1.EmployeeID, e1.FirstName + ' ' + e1.LastName AS EmployeeName, e1.Salary, e1.DepartmentID
FROM Employees e1
WHERE EXISTS (
    SELECT 1 FROM Employees e2
    WHERE e2.Salary = e1.Salary AND e2.DepartmentID != e1.DepartmentID
);

Tricky point: Self-comparison with EXISTS.

16. Delete duplicate attendance records, keeping only earliest.


WITH CTE AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY EmployeeID, [Date] ORDER BY ClockIn) AS rn
    FROM AttendanceRecords
)
DELETE FROM CTE WHERE rn > 1;

Tricky point: ROW_NUMBER with CTE for deletion.

17. Find top 2 earners in each department.


SELECT EmployeeID, FirstName + ' ' + LastName AS EmployeeName, DepartmentID, Salary
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY DepartmentID ORDER BY Salary DESC) AS rn
    FROM Employees
) t
WHERE rn <= 2;

Tricky point: Partitioned ranking.

18. Pivot attendance status count per employee.


SELECT EmployeeID,
       SUM(CASE WHEN Status='Present' THEN 1 ELSE 0 END) AS PresentCount,
       SUM(CASE WHEN Status='Absent' THEN 1 ELSE 0 END) AS AbsentCount,
       SUM(CASE WHEN Status='Late' THEN 1 ELSE 0 END) AS LateCount
FROM AttendanceRecords
GROUP BY EmployeeID;

Tricky point: Conditional aggregation.

19. Find employees who never worked on Fridays.


SELECT EmployeeID
FROM AttendanceRecords
GROUP BY EmployeeID
HAVING SUM(CASE WHEN DATENAME(WEEKDAY,[Date])='Friday' THEN 1 ELSE 0 END) = 0;

Tricky point: WEEKDAY function + HAVING.

20. Show employee name with maximum total overtime pay in a month.


SELECT TOP 1 e.EmployeeID, e.FirstName + ' ' + e.LastName AS EmployeeName, SUM(a.HoursWorked-8)*es.Amount AS OvertimePay
FROM AttendanceRecords a
INNER JOIN Employees e ON a.EmployeeID=e.EmployeeID
INNER JOIN EmployeeSalary es ON e.EmployeeID=es.EmployeeID
INNER JOIN SalaryComponents sc ON es.ComponentID=sc.ComponentID
WHERE a.HoursWorked>8 AND sc.ComponentName='Overtime Pay'
AND a.[Date] BETWEEN '2023-10-01' AND '2023-10-31'
GROUP BY e.EmployeeID, e.FirstName, e.LastName, es.Amount
ORDER BY OvertimePay DESC;

Tricky point: Multi-table join + calculation + TOP 1.

1. Find the employees who earn more than their manager.


SELECT e.EmployeeID, e.FirstName + ' ' + e.LastName AS EmployeeName, e.Salary, e.ManagerID
FROM Employees e
JOIN Employees m ON e.ManagerID = m.EmployeeID
WHERE e.Salary > m.Salary;

Tricky point: Self join to compare hierarchical data.

2. Find the longest streak of consecutive attendance for each employee.


WITH CTE AS (
    SELECT EmployeeID, [Date],
           ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY [Date]) - 
           ROW_NUMBER() OVER(PARTITION BY EmployeeID, Status ORDER BY [Date]) AS grp
    FROM AttendanceRecords
    WHERE Status='Present'
)
SELECT EmployeeID, COUNT(*) AS LongestStreak
FROM CTE
GROUP BY EmployeeID, grp
ORDER BY LongestStreak DESC;

Tricky point: Using gap-and-island problem with row numbers.

3. Find employees whose salary is above 90% percentile in their department.


SELECT EmployeeID, FirstName + ' ' + LastName AS EmployeeName, DepartmentID, Salary
FROM (
    SELECT *, PERCENT_RANK() OVER(PARTITION BY DepartmentID ORDER BY Salary) AS pr
    FROM Employees
) t
WHERE pr >= 0.9;

Tricky point: Percentile calculation with window function.

4. Identify employees who took leave immediately after a holiday.


SELECT l.EmployeeID, CONCAT(e.FirstName,' ',e.LastName) AS EmployeeName, l.StartDate
FROM LeaveApplications l
JOIN Employees e ON l.EmployeeID = e.EmployeeID
JOIN Holidays h ON l.StartDate = DATEADD(DAY, 1, h.HolidayDate);

Tricky point: Date calculation + join with holiday table.

5. Find departments where at least one employee is the top 3 earners globally.


WITH Top3 AS (
    SELECT EmployeeID, DepartmentID, Salary,
           ROW_NUMBER() OVER(ORDER BY Salary DESC) AS rn
    FROM Employees
)
SELECT DISTINCT DepartmentID
FROM Top3
WHERE rn <= 3;

Tricky point: Window function + global ranking.

6. Recursive query: Display management hierarchy for a given employee.


WITH RECURSIVE Hierarchy AS (
    SELECT EmployeeID, ManagerID, FirstName + ' ' + LastName AS Name
    FROM Employees
    WHERE EmployeeID = 5
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.FirstName + ' ' + e.LastName
    FROM Employees e
    JOIN Hierarchy h ON e.EmployeeID = h.ManagerID
)
SELECT * FROM Hierarchy;

Tricky point: Recursive CTE for hierarchy traversal.

7. Detect overlapping leave requests for the same employee.


SELECT l1.EmployeeID, l1.LeaveID AS Leave1, l2.LeaveID AS Leave2
FROM LeaveApplications l1
JOIN LeaveApplications l2 ON l1.EmployeeID = l2.EmployeeID
   AND l1.LeaveID < l2.LeaveID
   AND l1.StartDate <= l2.EndDate
   AND l2.StartDate <= l1.EndDate;

Tricky point: Self join + overlap condition.

8. Get top 2 departments by total payroll including deductions.


SELECT TOP 2 DepartmentID, SUM(CASE WHEN sc.ComponentType='Earning' THEN pt.Amount ELSE -pt.Amount END) AS TotalPayroll
FROM PayrollTransactions pt
JOIN Employees e ON pt.EmployeeID=e.EmployeeID
JOIN SalaryComponents sc ON pt.ComponentID=sc.ComponentID
GROUP BY DepartmentID
ORDER BY TotalPayroll DESC;

Tricky point: Aggregation with conditional sum.

9. Find employees who worked overtime on weekends only.


SELECT e.EmployeeID, CONCAT(e.FirstName,' ',e.LastName) AS EmployeeName, SUM(a.HoursWorked-8) AS Overtime
FROM AttendanceRecords a
JOIN Employees e ON a.EmployeeID=e.EmployeeID
WHERE DATENAME(WEEKDAY,a.Date) IN ('Saturday','Sunday') AND a.HoursWorked>8
GROUP BY e.EmployeeID, e.FirstName, e.LastName;

Tricky point: Conditional aggregation + date functions.

10. Find employees with no salary components assigned.


SELECT e.EmployeeID, CONCAT(e.FirstName,' ',e.LastName) AS EmployeeName
FROM Employees e
LEFT JOIN EmployeeSalary es ON e.EmployeeID=es.EmployeeID
WHERE es.SalaryID IS NULL;

Tricky point: LEFT JOIN + NULL check.

11. Calculate cumulative net pay month-wise for each employee.


SELECT EmployeeID, PeriodID, SUM(Amount) OVER(PARTITION BY EmployeeID ORDER BY PeriodID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumulativeNet
FROM PayrollTransactions pt
JOIN SalaryComponents sc ON pt.ComponentID=sc.ComponentID
WHERE sc.ComponentType='Earning';

Tricky point: Window function with running total.

12. Identify employees who have taken more than 50% of their allowed leave in current year.


SELECT e.EmployeeID, CONCAT(e.FirstName,' ',e.LastName) AS EmployeeName, lt.LeaveName,
       COUNT(la.LeaveID)*1.0/lt.MaxDays AS UsedPercentage
FROM Employees e
JOIN LeaveTypes lt ON lt.RequiresApproval=1
LEFT JOIN LeaveApplications la ON e.EmployeeID=la.EmployeeID AND la.LeaveTypeID=lt.LeaveTypeID AND la.Status='Approved' AND YEAR(la.StartDate)=2023
GROUP BY e.EmployeeID, e.FirstName, e.LastName, lt.LeaveName, lt.MaxDays
HAVING COUNT(la.LeaveID)*1.0/lt.MaxDays > 0.5;

Tricky point: Aggregate with division for percentage.

13. Find departments where average salary increased more than 10% compared to last month.


WITH DeptSalary AS (
    SELECT DepartmentID, PeriodID, AVG(pt.Amount) AS AvgSalary
    FROM PayrollTransactions pt
    JOIN Employees e ON pt.EmployeeID=e.EmployeeID
    JOIN SalaryComponents sc ON pt.ComponentID=sc.ComponentID AND sc.ComponentType='Earning'
    GROUP BY DepartmentID, PeriodID
)
SELECT d1.DepartmentID
FROM DeptSalary d1
JOIN DeptSalary d2 ON d1.DepartmentID=d2.DepartmentID AND d1.PeriodID=d2.PeriodID+1
WHERE (d1.AvgSalary - d2.AvgSalary)/d2.AvgSalary > 0.1;

Tricky point: Self-join with lag calculation (monthly trend).

14. Detect employees with inconsistent attendance records (ClockOut < ClockIn).


SELECT EmployeeID, [Date], ClockIn, ClockOut
FROM AttendanceRecords
WHERE ClockOut < ClockIn;

Tricky point: Data validation logic.

15. Find employees who received the same net pay as another employee in the same period.


WITH NetPay AS (
    SELECT EmployeeID, PeriodID,
           SUM(CASE WHEN sc.ComponentType='Earning' THEN Amount ELSE -Amount END) AS NetAmount
    FROM PayrollTransactions pt
    JOIN SalaryComponents sc ON pt.ComponentID=sc.ComponentID
    GROUP BY EmployeeID, PeriodID
)
SELECT n1.EmployeeID, n1.PeriodID, n1.NetAmount
FROM NetPay n1
JOIN NetPay n2 ON n1.NetAmount=n2.NetAmount AND n1.EmployeeID<>n2.EmployeeID AND n1.PeriodID=n2.PeriodID;

Tricky point: Self-join with conditional equality.

16. Recursive query to find all subordinates under a manager.


WITH RECURSIVE Subordinates AS (
    SELECT EmployeeID, ManagerID, FirstName + ' ' + LastName AS Name
    FROM Employees
    WHERE ManagerID = 1
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.FirstName + ' ' + e.LastName
    FROM Employees e
    JOIN Subordinates s ON e.ManagerID=s.EmployeeID
)
SELECT * FROM Subordinates;

Tricky point: Hierarchy recursion.

17. Find employees who always come late in the last 10 working days.


SELECT EmployeeID
FROM (
    SELECT EmployeeID, ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY [Date] DESC) AS rn, Status
    FROM AttendanceRecords
) t
WHERE rn <= 10
GROUP BY EmployeeID
HAVING COUNT(CASE WHEN Status='Late' THEN 1 END) = 10;

Tricky point: Row filtering + HAVING condition.

18. Identify “orphan” payroll transactions (EmployeeID not in Employees table).


SELECT *
FROM PayrollTransactions pt
LEFT JOIN Employees e ON pt.EmployeeID=e.EmployeeID
WHERE e.EmployeeID IS NULL;

Tricky point: Data integrity check with LEFT JOIN + NULL.

19. Generate sequence of dates (calendar table) to join with attendance.


WITH Calendar AS (
    SELECT CAST('2023-10-01' AS DATE) AS Date
    UNION ALL
    SELECT DATEADD(DAY,1,Date) FROM Calendar WHERE Date<'2023-10-31'
)
SELECT c.Date, e.EmployeeID
FROM Calendar c
CROSS JOIN Employees e
OPTION (MAXRECURSION 0);

Tricky point: Recursive CTE + cross join to generate full attendance matrix.

20. Find employees whose salary has never decreased.


WITH SalaryHistory AS (
    SELECT EmployeeID, Amount, ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY EffectiveDate) AS rn
    FROM EmployeeSalary
)
SELECT EmployeeID
FROM SalaryHistory sh1
WHERE NOT EXISTS (
    SELECT 1
    FROM SalaryHistory sh2
    WHERE sh1.EmployeeID=sh2.EmployeeID AND sh2.rn>sh1.rn AND sh2.Amount<sh1.Amount
);

Tricky point: Detect decreasing sequence using self-existence check.

FreeLearning365 | Free Tech Tutorials, Coding Guides & Daily Learning Resources

freelearning365 - Comprehensive SQL Database Training Guide: HR Management System with Attendance and Payroll

Comprehensive SQL Database Training Guide: HR Management System with Attendance and Payroll

Database Overview

Table Structure and Sample Data

1. Departments Table

2. Employees Table

3. AttendanceRecords Table

4. LeaveTypes Table

5. LeaveApplications Table

6. PayrollPeriods Table

7. SalaryComponents Table

8. EmployeeSalary Table

9. PayrollTransactions Table

10. OvertimeRequests Table

11. TaxConfigurations Table

12. EmployeeDocuments Table

SQL Practice Exercises

Table Operations

Data Manipulation Language (DML)

Data Query Language (DQL) - SELECT Queries

JOIN Operations with Business Cases

Advanced Queries and Business Cases

Assignment Questions

Employee & HR SQL Practical Assignments

1. List all active employees.

2. Show full names and email of all employees in the Marketing department.

3. Count the number of employees in each department.

4. Find the highest paid employee in each department.

5. List employees whose salary is above the average salary.

6. Calculate total hours worked for each employee in October 2023.

7. Show employees with more than 3 absent days in October 2023.

8. List employees and their managers.

9. Get employees with their department details using INNER JOIN.

10. Show all employees even if they have no attendance record using LEFT JOIN.

11. Calculate total earnings and deductions per employee for October 2023.

12. Show employees who worked overtime (>8 hours) in October 2023.

13. Find employees who never took any leave in 2023.

14. List leave balance for each leave type per employee.

15. Show top 3 highest paid employees.

16. Display employees with their total number of trainings attended.

17. Find employees with duplicate email addresses (data quality check).

18. Calculate net pay including tax for October 2023.

19. Rank employees by salary within their department.

20. Show employees whose overtime pay is greater than 1000.

21. List employees with their total leave days taken in 2023.

22. Find the department with maximum number of employees.

23. Show employees who have not completed any training.

24. Find employees whose salary increased by more than 5% recently.

25. List employees and the number of documents uploaded.

26. Show employees with tax configurations applied (tax > 0).

27. Find employees with multiple managers (data check).

28. Show payroll transactions for a specific component (Bonus).

29. Show employees who joined after 1st Jan 2023.

30. Find employees who have more than 2 types of leave in 2023.

Comprehensive SQL Assignments with Answers

A. DDL (Data Definition Language) Assignments

1. Create a table for projects.

2. Add a column ProjectManagerID to the Projects table.

3. Modify Budget column to allow NULLs.

4. Drop a column EndDate from Projects.

5. Rename column Status to ProjectStatus.

6. Create a table with foreign key reference.

7. Create a table with CHECK constraint and DEFAULT.

8. Create a table with composite primary key.

9. Create a view for active employees with salary > 50,000.

10. Drop the view.

B. DML (Data Manipulation Language) Assignments

11. Insert new employee record.

12. Update employee salary by 10%.

13. Delete employees who are terminated.

14. Insert multiple attendance records.

15. Update leave application status.

16. Insert salary component.

17. Bulk update: Increase salary for employees with more than 5 years.

18. Insert project assignment.

19. Delete specific project assignments.

20. Update multiple columns in employee table.

C. DQL (Data Query Language) Assignments

21. Select employees with salary > 60000.

2. Add a column `ProjectManagerID` to the `Projects` table.

3. Modify `Budget` column to allow NULLs.

4. Drop a column `EndDate` from `Projects`.

5. Rename column `Status` to `ProjectStatus`.

38. Create a trigger to update `UpdatedAt` on employee update.