HR Analytic Project

You must execute the code again to save those values in the database.

-- Dataset

-- Team Table
CREATE TABLE Team (
  team_id INT PRIMARY KEY,
  team_name VARCHAR(50)
);

-- Employee Table
CREATE TABLE Employee (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(255) UNIQUE,
    hire_date DATE,
    team_id INT,
    FOREIGN KEY (team_id) REFERENCES team(team_id)
);

-- Salary Table
CREATE TABLE Salary (
  employee_id INT PRIMARY KEY,
  role VARCHAR(50),
  salary INTEGER
);

-- INSERT INTO Table
INSERT INTO employee (employee_id, first_name, last_name, email, hire_date, team_id)
VALUES (1, 'Warde', 'Remon', 'wremon0@zimbio.com','1-Jun-23', 1),
(2, 'Orlando', 'Groneway', 'ogroneway1@wikipedia.org', '22-Jun-23', 1),
(3, 'Amalita', 'Shorland', 'ashorland2@npr.org', '25-Mar-23', 1),
(4, 'Reinwald', 'Pickersail', 'rpickersail3@skyrock.com', '24-Jun-23', 1),
(5, 'Ilario', 'Anfrey', 'ianfrey4@google.com','2-Jan-23',1),
(6,'Davey', 'Frowen', 'dfrowen5@nsw.gov.au', '3-Mar-23', 6),
(7, 'Leigha', 'Randlesome', 'lrandlesome6@alibaba.com', '15-Oct-22', 6),
(8,'Junia', 'Yakovliv', 'jyakovliv7@artisteer.com', '31-Jul-23', 6),
(9, 'Rochell', 'Waggatt', 'rwaggatt8@opera.com', '15-Apr-23', 6),
(10, 'Moises', 'Ardley', 'mardley9@webnode.com', '21-Jun-23', 6);

INSERT INTO Team (team_id, team_name)
VALUES (1, 'plan_A'),
(6, 'plan_B');

INSERT INTO Salary (employee_id, role, salary)
VALUES (1, 'Sales', 40153),
(2, 'Marketing', 46670),
(3, 'Legal', 71983),
(4, 'Sales', 15857),
(5, 'Software Engineer', 95174),
(6, 'Sales', 27819),
(7, 'Legal', 75323),
(8, 'Software Engineer', 90525),
(9, 'Tax Accountant', 85946),
(10, 'Marketing', 56575);

.mode box
.header on
SELECT * FROM Team;
SELECT * FROM Employee;
SELECT * FROM Salary;

-- Challenge start !!

-- Q1: Query each employee's first, last name, email address, date of employment, title, and salary.
SELECT 'Q1: Query each employees first, last name, email address, date of employment, title, and salary.' AS Section;

SELECT E.first_name, E.last_name, E.email, E.hire_date, S.role, S.salary
FROM Employee E
LEFT JOIN Salary S 
ON E.employee_id = S.employee_id;

-- Q2: Who are the three most recently hired employees? What team are they on? and What day do they come to work?
-- Show Team ID
SELECT 'Q2: Who are the three most recently hired employees? What team are they on? and What day do they come to work? [Show Team ID]' AS Section;

SELECT team_id, first_name, last_name, email, hire_date, 
RANK() OVER (ORDER BY hire_date) AS hire_date_rank
FROM employee
LIMIT 3;

-- OR (Show Team Name)
SELECT 'Q2: Who are the three most recently hired employees? What team are they on? and What day do they come to work? [Show Team Name]' AS Section;

SELECT t.team_name, e.first_name, e.last_name, e.email, e.hire_date,
RANK() OVER (ORDER BY e.hire_date ASC) AS hire_date_rank
FROM 
    employee e
JOIN 
    team t 
ON 
    e.team_id = t.team_id
LIMIT 3;

-- Q3
-- Q3.1: find the average salary of employees in each position.
SELECT 'Q3.1: find the average salary of employees in each position.' AS Section;

SELECT role, AVG(salary) AS average_salary
FROM Salary
GROUP BY role;

-- Q3.2 Find the salary of each position as a proportion of the total employee salary.
SELECT 'Q3.2 Find the salary of each position as a proportion of the total employee salary. [Pattern 1]' AS Section;

SELECT 
  role, 
  SUM(salary) * 1.0 / (SELECT SUM(salary) FROM Salary) AS proportion
FROM Salary
GROUP BY role;

-- OR
SELECT 'Q3.2 Find the salary of each position as a proportion of the total employee salary. [Pattern 2]' AS Section;

SELECT 
    S.role,
    SUM(S.salary) * 1.0 / (SELECT SUM(salary) FROM Salary) AS proportion
FROM 
    Team T
JOIN 
    Employee E ON T.team_id = E.team_id
JOIN 
    Salary S ON E.employee_id = S.employee_id
GROUP BY 
  S.role;

-- Q4: Employees with a salary higher than 75,000 are called Managers, salaries from 45,000 to 75,000 are called Seniors, and those with salaries less than that are called Staff.
SELECT 'Q4: Employees with a salary higher than 75,000 are called Managers, salaries from 45,000 to 75,000 are called Seniors, and those with salaries less than that are called Staff.' AS Section;

SELECT
  e.employee_id,
  e.first_name,
  e.last_name,
  s.salary,
  CASE
    WHEN s.salary > 75000 THEN 'Manager'
    WHEN s.salary > 45000 AND s.salary <= 75000 THEN 'Senior'
    ELSE 'Staff'
  END AS employee_category
FROM Employee e
JOIN Salary s ON e.employee_id = s.employee_id;

-- Q5: If an employee comes on 14-Oct-23 for the Marketing position with a salary of 45,000 baht, what will the latest average salary for the Marketing position?

-- Insert the 11th new employee in the employee and salary tables.

INSERT INTO employee (employee_id, first_name, last_name, email, hire_date, team_id)
VALUES (1, 'Warde', 'Remon', 'wremon0@zimbio.com','1-Jun-23', 1),
(2, 'Orlando', 'Groneway', 'ogroneway1@wikipedia.org', '22-Jun-23', 1),
(3, 'Amalita', 'Shorland', 'ashorland2@npr.org', '25-Mar-23', 1),
(4, 'Reinwald', 'Pickersail', 'rpickersail3@skyrock.com', '24-Jun-23', 1),
(5, 'Ilario', 'Anfrey', 'ianfrey4@google.com','2-Jan-23',1),
(6,'Davey', 'Frowen', 'dfrowen5@nsw.gov.au', '3-Mar-23', 6),
(7, 'Leigha', 'Randlesome', 'lrandlesome6@alibaba.com', '15-Oct-22', 6),
(8,'Junia', 'Yakovliv', 'jyakovliv7@artisteer.com', '31-Jul-23', 6),
(9, 'Rochell', 'Waggatt', 'rwaggatt8@opera.com', '15-Apr-23', 6),
(10, 'Moises', 'Ardley', 'mardley9@webnode.com', '21-Jun-23', 6),
(11, , , , '11-Oct-23', );

-- Find the latest average salary for Marketing.
SELECT 'Q5: If an employee comes on 14-Oct-23 for the Marketing position with a salary of 45,000 baht, what will the latest average salary for the Marketing position?' AS Section;

SELECT 
    role,
    AVG(salary) AS average_salary
FROM 
    salary
GROUP BY role
HAVING role = 'Marketing';

-- Thank you Datayolk Facebook page🙏
-- Run on Replit

team_id	team_name
1	plan_A
6	plan_B

employee_id	first_name	last_name	email	hire_date	team_id
1	Warde	Remon	[email protected]	1-Jun-23	1
2	Orlando	Groneway	[email protected]	22-Jun-23	1
3	Amalita	Shorland	[email protected]	25-Mar-23	1
4	Reinwald	Pickersail	[email protected]	24-Jun-23	1
5	Ilario	Anfrey	[email protected]	2-Jan-23	1
6	Davey	Frowen	[email protected]	3-Mar-23	6
7	Leigha	Randlesome	[email protected]	15-Oct-22	6
8	Junia	Yakovliv	[email protected]	31-Jul-23	6
9	Rochell	Waggatt	[email protected]	15-Apr-23	6
10	Moises	Ardley	[email protected]	21-Jun-23	6

employee_id	role	salary
1	Sales	40153
2	Marketing	46670
3	Legal	71983
4	Sales	15857
5	Software Engineer	95174
6	Sales	27819
7	Legal	75323
8	Software Engineer	90525
9	Tax Accountant	85946
10	Marketing	56575

Section
Q1: Query each employees first, last name, email address, date of employment, title, and salary.

first_name	last_name	email	hire_date	role	salary
Warde	Remon	[email protected]	1-Jun-23	Sales	40153
Orlando	Groneway	[email protected]	22-Jun-23	Marketing	46670
Amalita	Shorland	[email protected]	25-Mar-23	Legal	71983
Reinwald	Pickersail	[email protected]	24-Jun-23	Sales	15857
Ilario	Anfrey	[email protected]	2-Jan-23	Software Engineer	95174
Davey	Frowen	[email protected]	3-Mar-23	Sales	27819
Leigha	Randlesome	[email protected]	15-Oct-22	Legal	75323
Junia	Yakovliv	[email protected]	31-Jul-23	Software Engineer	90525
Rochell	Waggatt	[email protected]	15-Apr-23	Tax Accountant	85946
Moises	Ardley	[email protected]	21-Jun-23	Marketing	56575

Section
Q2: Who are the three most recently hired employees? What team are they on? and What day do they come to work? [Show Team ID]

team_id	first_name	last_name	email	hire_date	hire_date_rank
1	Warde	Remon	[email protected]	1-Jun-23	1
6	Rochell	Waggatt	[email protected]	15-Apr-23	2
6	Leigha	Randlesome	[email protected]	15-Oct-22	3

Section
Q2: Who are the three most recently hired employees? What team are they on? and What day do they come to work? [Show Team Name]

team_name	first_name	last_name	email	hire_date	hire_date_rank
plan_A	Warde	Remon	[email protected]	1-Jun-23	1
plan_B	Rochell	Waggatt	[email protected]	15-Apr-23	2
plan_B	Leigha	Randlesome	[email protected]	15-Oct-22	3

Section
Q3.1: find the average salary of employees in each position.

role	average_salary
Legal	73653.0
Marketing	51622.5
Sales	27943.0
Software Engineer	92849.5
Tax Accountant	85946.0

Section
Q3.2 Find the salary of each position as a proportion of the total employee salary. [Pattern 1]

role	proportion
Legal	0.243069180314343
Marketing	0.17036425890021
Sales	0.138325976651128
Software Engineer	0.306421352254445
Tax Accountant	0.141819231879873

Section
Q3.2 Find the salary of each position as a proportion of the total employee salary. [Pattern 2]

role	proportion
Legal	0.243069180314343
Marketing	0.17036425890021
Sales	0.138325976651128
Software Engineer	0.306421352254445
Tax Accountant	0.141819231879873

Section
Q4: Employees with a salary higher than 75,000 are called Managers, salaries from 45,000 to 75,000 are called Seniors, and those with salaries less than that are called Staff.

employee_id	first_name	last_name	salary	employee_category
1	Warde	Remon	40153	Staff
2	Orlando	Groneway	46670	Senior
3	Amalita	Shorland	71983	Senior
4	Reinwald	Pickersail	15857	Staff
5	Ilario	Anfrey	95174	Manager
6	Davey	Frowen	27819	Staff
7	Leigha	Randlesome	75323	Manager
8	Junia	Yakovliv	90525	Manager
9	Rochell	Waggatt	85946	Manager
10	Moises	Ardley	56575	Senior

Section
Q5: If an employee comes on 14-Oct-23 for the Marketing position with a salary of 45,000 baht, what will the latest average salary for the Marketing position?

role	average_salary
Marketing	51622.5