SQL WindowsFunctions - MySql Example

#1 - Create Database Schema

create database orgmanagement;

use orgmanagement;

CREATE TABLE Departments (

    department_id INT PRIMARY KEY,

    department_name VARCHAR(100),

    location VARCHAR(100)

);

CREATE TABLE Positions (

    position_id INT PRIMARY KEY,

    position_title VARCHAR(100),

    salary DECIMAL(10, 2),

    department_id INT,

    FOREIGN KEY (department_id) REFERENCES Departments(department_id)

);

CREATE TABLE Employees (

    employee_id INT PRIMARY KEY,

    first_name VARCHAR(50),

    last_name VARCHAR(50),

    email VARCHAR(100),

    phone_number VARCHAR(20),

    hire_date DATE,

    department_id INT,

    position_id INT,

    manager_id INT,

    FOREIGN KEY (department_id) REFERENCES Departments(department_id),

    FOREIGN KEY (position_id) REFERENCES Positions(position_id),

    FOREIGN KEY (manager_id) REFERENCES Employees(employee_id)

);

CREATE TABLE Salaries (

    salary_id INT PRIMARY KEY,

    employee_id INT,

    salary DECIMAL(10, 2),

    effective_date DATE,

    FOREIGN KEY (employee_id) REFERENCES Employees(employee_id)

);

CREATE TABLE Attendance (

    attendance_id INT PRIMARY KEY,

    employee_id INT,

    check_in DATETIME,

    check_out DATETIME,

    FOREIGN KEY (employee_id) REFERENCES Employees(employee_id)

);

CREATE TABLE Leaves(

    leave_id INT PRIMARY KEY,

    employee_id INT,

    leave_type VARCHAR(50),

    start_date DATE,

    end_date DATE,

    status VARCHAR(20),

    manager_comment VARCHAR(255),

    FOREIGN KEY (employee_id) REFERENCES Employees(employee_id)

);

#2 : Insert Test Data

-- Inserting departments

INSERT INTO Departments (department_id, department_name, location) VALUES

(1, 'Engineering', 'New York'),

(2, 'Marketing', 'Los Angeles'),

(3, 'Finance', 'Chicago');

-- Inserting positions

INSERT INTO Positions (position_id, position_title, salary, department_id) VALUES

(1, 'Software Engineer', 80000.00, 1),

(2, 'Marketing Manager', 90000.00, 2),

(3, 'Financial Analyst', 85000.00, 3);

-- Inserting employees

INSERT INTO Employees (employee_id, first_name, last_name, email, phone_number, hire_date, department_id, position_id, manager_id) VALUES

 (1, 'John', 'Doe', 'john.doe@example.com', '123-456-7890', '2020-01-01', 1, 1, NULL),

(2, 'Jane', 'Smith', 'jane.smith@example.com', '234-567-8901', '2020-01-02', 1, 1, 1),

-- Continue with similar inserts for remaining employees

-- Employee 51-100

(51, 'Michael', 'Johnson', 'michael.johnson@example.com', '345-678-9012', '2020-06-01', 2, 2, NULL),

(52, 'Emily', 'Brown', 'emily.brown@example.com', '456-789-0123', '2020-06-02', 2, 2, 51);

-- Continue with similar inserts for remaining employees

INSERT INTO Employees (employee_id, first_name, last_name, email, phone_number, hire_date, department_id, position_id, manager_id) VALUES

(3, 'John3', 'Doe', 'john.doe@example.com', '123-456-7890', '2020-01-01', 1, 1, 1),

(4, 'Jane4', 'Smith', 'jane.smith@example.com', '234-567-8901', '2020-01-02', 3, 1, 1),

-- Continue with similar inserts for remaining employees

-- Employee 51-100

(5, 'Michael5', 'Johnson', 'michael.johnson@example.com', '345-678-9012', '2020-06-01', 3, 2, 4),

(6, 'Emily6', 'Brown', 'emily.brown@example.com', '456-789-0123', '2020-06-02', 3, 2, 5);

-- Inserting salaries

-- For simplicity, let's assume all employees have the same starting salary

INSERT INTO Salaries (salary_id, employee_id, salary, effective_date) VALUES

-- Employee 1-100

(1, 1, 80000.00, '2020-01-01'),

(2, 2, 80000.00, '2020-01-02');

INSERT INTO Salaries (salary_id, employee_id, salary, effective_date) VALUES

-- Employee 1-100

(3, 3, 40000.00, '2020-01-01'),

(4, 4, 60000.00, '2020-01-01'),

(5, 5, 90000.00, '2020-01-01'),

(6, 6, 100000.00, '2020-01-01'),

(7, 51, 60000.00, '2020-01-01'),

(8, 52, 70000.00, '2020-01-02');

-- Continue with similar inserts for remaining employees

-- Inserting attendance (assuming random check-in and check-out times)

INSERT INTO Attendance (attendance_id, employee_id, check_in, check_out) VALUES

-- Employee 1-100

(1, 1, '2020-01-01 08:00:00', '2020-01-01 17:00:00'),

(2, 2, '2020-01-02 08:15:00', '2020-01-02 17:15:00');

-- Continue with similar inserts for remaining employees

-- Inserting leave requests (assuming random leave types and dates)

INSERT INTO Leaves (leave_id, employee_id, leave_type, start_date, end_date, status, manager_comment) VALUES

-- Employee 1-100

(1, 1, 'Vacation', '2020-01-05', '2020-01-07', 'Approved', 'Enjoy your vacation!'),

(2, 2, 'Sick Leave', '2020-01-10', '2020-01-12', 'Approved', 'Get well soon!');

-- Continue with similar inserts for remaining employees

#3 - Write Windows Functions

-- Find the sum of all salary per department

select e.first_name, d.department_name, sum(s.salary) over( partition by d.department_name) from employees e join salaries s on e.employee_id= s.employee_id join departments d 

on e.department_id=d.department_id;

-- Find the max salary of an employee per department

select  d.department_name, max(s.salary) over ( partition by d.department_name) as max_salary from employees e join salaries s on e.employee_id= s.employee_id join departments d 

on e.department_id=d.department_id;

-- Find the min salary of an employee per department

select  e.first_name,department_name, min(s.salary) over ( partition by d.department_name) as max_salary from employees e join salaries s on e.employee_id= s.employee_id join departments d 

on e.department_id=d.department_id;

-- Find the rank of employees based on the highest salary.  Note: John and Jane both have the same salaries so the rank is the same and the next rank is skipped to 3.

select  e.first_name,e.last_name,department_name, s.salary, rank() over ( partition by d.department_name order by s.salary desc) as rankbysal from employees e join salaries s on e.employee_id= s.employee_id join departments d 

on e.department_id=d.department_id;

-- Find the dense rank of employees with the highest salary.  Note: Jon and Jane both are in the same rank and the next rank is not skipped for John3.

select  e.first_name,e.last_name,department_name, s.salary, dense_rank() over ( partition by d.department_name order by s.salary desc) as denserankbysal from employees e join salaries s on e.employee_id= s.employee_id join departments d 

on e.department_id=d.department_id;