SQL - Introduction to Conditional Statement and Having Syntax Script by HadiMaster

CREATE TABLE Employees (
    EmployeeID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50),
    Salary INT,
    YearsExperience INT
);

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary, YearsExperience) VALUES
(1, 'John', 'Doe', 'Sales', 50000, 3),
(2, 'Jane', 'Smith', 'Sales', 55000, 5),
(3, 'Michael', 'Johnson', 'Sales', 60000, 7),
(4, 'Emily', 'Brown', 'Marketing', 48000, 4),
(5, 'David', 'Jones', 'Marketing', 52000, 6),
(6, 'Sarah', 'Taylor', 'Marketing', 58000, 8),
(7, 'James', 'Wilson', 'Human Resources', 55000, 5),
(8, 'Jessica', 'Martinez', 'Human Resources', 60000, 6),
(9, 'Christopher', 'Garcia', 'Human Resources', 62000, 8),
(10, 'Melissa', 'Anderson', 'Finance', 60000, 4),
(11, 'Daniel', 'Hernandez', 'Finance', 65000, 6),
(12, 'Amanda', 'Lopez', 'Finance', 70000, 7),
(13, 'Robert', 'Perez', 'IT', 55000, 3),
(14, 'Jennifer', 'Gonzalez', 'IT', 60000, 5),
(15, 'William', 'Rodriguez', 'IT', 65000, 7),
(16, 'Linda', 'Lewis', 'Customer Service', 45000, 2),
(17, 'Kevin', 'Lee', 'Customer Service', 50000, 4),
(18, 'Ashley', 'Walker', 'Customer Service', 55000, 6),
(19, 'Thomas', 'Hall', 'Operations', 60000, 3),
(20, 'Karen', 'Allen', 'Operations', 65000, 5),
(21, 'Mark', 'Young', 'Operations', 70000, 7),
(22, 'Elizabeth', 'Harris', 'Logistics', 55000, 4),
(23, 'Ryan', 'Clark', 'Logistics', 60000, 6),
(24, 'Michelle', 'King', 'Logistics', NULL, 8),
(25, 'Steven', 'Scott', 'Logistics', 70000, 9);

-- assign 'Junior', 'Intermediate', and 'Senior' to each employee based on
-- YearsExperience
SELECT
    FirstName,
    LastName,
    YearsExperience,
    CASE
        WHEN YearsExperience <= 3 THEN 'Junior'
        WHEN YearsExperience > 3 AND YearsExperience <= 6 THEN 'Intermediate'
        ELSE 'Senior'
    END AS ExperienceLevel
FROM
    Employees;

-- assign 'Eligible for Bonus' and 'Not Eligible for Bonus' based on
-- Salary >= 60000 or below it
SELECT
    FirstName,
    LastName,
    Salary,
    CASE
        WHEN Salary >= 60000 THEN 'Eligible for Bonus'
        ELSE 'Not Eligible for Bonus'
    END AS BonusEligibility
FROM
    Employees;

-- assign 0 if Salary is null
SELECT
    EmployeeID,
    FirstName,
    LastName,
    COALESCE(Salary, 0) AS Salary
FROM
    Employees;


-- find departments with an average salary greater than 55000
SELECT
    Department,
    AVG(Salary) AS AvgSalary
FROM
    Employees
GROUP BY
    Department
HAVING
    AVG(Salary) > 55000;

-- find department that has more than 3 employees
SELECT
    Department,
    COUNT(*) AS NumEmployees
FROM
    Employees
GROUP BY
    Department
HAVING
    COUNT(*) > 3;