Jemmy Dalsaniya

Posted on Jun 30

Mastering SQL Join Queries: HR Worker Data Analysis

#sql #dbms #database #tutorial

Welcome to our SQL Join Queries series, where we dive into advanced techniques for analyzing HR data using join operations. This blog focuses on a worker management database with Worker, Department, and Worker_rating tables. We’ll present 45 SQL queries leveraging various join types (INNER, LEFT, etc.) to tackle complex HR scenarios. These queries are perfect for data analysts, HR professionals, or developers aiming to master SQL joins for workforce insights.

🧩 Database Schema Overview

`Worker`: Stores worker details

Columns:

worker_id
first_name
last_name
salary
joining_date
department_id

`Department`: Contains department information

Columns:

id
department

`Worker_rating`: Tracks worker performance ratings

Columns:

id
worker_id
rating

🏗️ Creating the Tables

CREATE TABLE Department (
    id INT PRIMARY KEY AUTO_INCREMENT,
    department VARCHAR(50) NOT NULL
);

CREATE TABLE Worker (
    worker_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    salary INT NOT NULL,
    joining_date DATE NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES Department(id)
);

CREATE TABLE Worker_rating (
    id INT PRIMARY KEY AUTO_INCREMENT,
    worker_id INT,
    rating INT NOT NULL,
    FOREIGN KEY (worker_id) REFERENCES Worker(worker_id)
);

📝 Inserting Sample Data

INSERT INTO Department (id, department) VALUES
(1, 'HR'),
(2, 'Admin'),
(3, 'Account'),
(4, 'Sales');

INSERT INTO Worker (worker_id, first_name, last_name, salary, joining_date, department_id) VALUES
(1, 'Monika', 'Arora', 100000, '1982-02-20', 1),
(2, 'Niharika', 'Verma', 80000, '2014-06-11', 2),
(3, 'Vishal', 'Singhal', 300000, '2015-02-20', 1),
(4, 'Amitabh', 'Singh', 500000, '2014-02-20', 2),
(5, 'Vivek', 'Bhati', 500000, '2016-06-11', 2),
(6, 'Vipul', 'Diwan', 200000, '2014-06-11', 3),
(7, 'Satish', 'Kumar', 75000, '1985-01-20', 4),
(8, 'Geetika', 'Chauhan', 90000, '1988-04-11', 2),
(9, 'Ramesh', 'Sharma', 120000, '2018-03-15', 1),
(10, 'Suresh', 'Yadav', 95000, '2017-07-01', 3),
(11, 'Anjali', 'Kapoor', 85000, '2013-01-25', 4),
(12, 'Pooja', 'Malhotra', 110000, '2016-12-10', 2),
(13, 'Deepak', 'Gupta', 140000, '2019-09-05', 1),
(14, 'Rohit', 'Jain', 70000, '2020-01-20', 3),
(15, 'Sneha', 'Mehra', 80000, '2012-06-25', 4),
(16, 'Arjun', 'Chopra', 130000, '2021-03-14', 1),
(17, 'Kiran', 'Reddy', 120000, '2022-05-18', 2),
(18, 'Meena', 'Naik', 110000, '2020-08-21', 1),
(19, 'Rajesh', 'Pillai', 95000, '2018-11-30', 4),
(20, 'Bhavna', 'Iyer', 105000, '2015-07-22', 3),
(21, 'Prakash', 'Joshi', 98000, '2016-04-25', 2),
(22, 'Gaurav', 'Patel', 88000, '2019-10-15', 3),
(23, 'Manisha', 'Desai', 102000, '2014-11-20', 2),
(24, 'Ajay', 'Khan', 92000, '2021-01-12', 4),
(25, 'Sanjay', 'Ghosh', 135000, '2017-03-08', 1),
(26, 'Priya', 'Das', 80000, '2018-05-14', 2),
(27, 'Anil', 'Nair', 125000, '2020-09-16', 3),
(28, 'Siddharth', 'Rao', 140000, '2022-11-10', 4),
(29, 'Radha', 'Kulkarni', 95000, '2015-12-23', 2),
(30, 'Tanya', 'Mishra', 89000, '2019-06-29', 3);

INSERT INTO Worker_rating (id, worker_id, rating) VALUES
(1, 1, 3),
(2, 1, 2),
(3, 2, 4),
(4, 3, 5),
(5, 4, 2),
(6, 2, 1),
(7, 3, 3),
(8, 5, 2),
(9, 6, 5),
(10, 7, 4),
(11, 8, 3),
(12, 9, 4),
(13, 10, 3),
(14, 11, 4),
(15, 12, 2),
(16, 13, 5),
(17, 14, 3),
(18, 15, 4),
(19, 16, 5),
(20, 17, 2),
(21, 18, 3),
(22, 19, 4),
(23, 20, 5),
(24, 21, 2),
(25, 22, 4),
(26, 23, 3),
(27, 24, 2),
(28, 25, 5),
(29, 26, 3),
(30, 27, 4);

🔍 Advanced SQL Join Queries for Worker Analysis

Below are 45 SQL queries with explanations, leveraging join operations to combine data across the Worker, Department, and Worker_rating tables for actionable HR insights.

1. Retrieve the first and last names of all workers along with their department names

SELECT w.first_name, w.last_name, d.department
FROM Worker w
JOIN Department d ON w.department_id = d.id;

2. Retrieve the total salary of workers in each department

SELECT d.department, SUM(w.salary) AS total_salary
FROM Department d
JOIN Worker w ON d.id = w.department_id
GROUP BY d.department;

3. Fetch the department-wise worker count

SELECT d.department, COUNT(w.worker_id) AS number_of_workers
FROM Worker w
JOIN Department d ON w.department_id = d.id
GROUP BY d.department
HAVING AVG(w.salary) > 100000;

4. Fetch the department-wise highest salary record

SELECT d.department, MAX(w.salary) AS highest_salary
FROM Worker w
JOIN Department d ON w.department_id = d.id
GROUP BY d.department;

5. Fetch the average rating with worker details whose rating is greater than or equal to 4

6. Get the list of workers with their department name and salary who have a rating of 3 or more

SELECT w.first_name, w.last_name, d.department, w.salary
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating >= 3;

7. Show the department-wise average salary of workers who have been rated 4 or higher

SELECT d.department, AVG(w.salary) AS average_salary
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating >= 4
GROUP BY d.department;

8. Display the number of workers in each department where the average salary is greater than 100,000

SELECT d.department, COUNT(w.worker_id) AS worker_count
FROM Worker w
JOIN Department d ON w.department_id = d.id
GROUP BY d.department
HAVING AVG(w.salary) > 100000;

9. Get the list of workers who joined before 2015, along with their department names

SELECT w.first_name, w.last_name, d.department
FROM Worker w
JOIN Department d ON w.department_id = d.id
WHERE YEAR(w.joining_date) < 2015;

10. Fetch data on workers who joined in 2014–02

SELECT w.first_name, w.last_name, w.joining_date
FROM Worker w
WHERE YEAR(w.joining_date) = 2014 AND MONTH(w.joining_date) = 2;

11. Find the workers hired in the 80s (1980 to 1989)

SELECT w.first_name, w.last_name, w.joining_date
FROM Worker w
WHERE YEAR(w.joining_date) BETWEEN 1980 AND 1989;

12. Display the total number of ratings for each worker and their respective department

13. Show the workers whose salary is greater than or equal to 500,000 along with their department

SELECT w.first_name, w.last_name, d.department, w.salary
FROM Worker w
JOIN Department d ON w.department_id = d.id
WHERE w.salary >= 500000;

14. Find the number of workers in each department with a rating of less than 3

SELECT d.department, COUNT(DISTINCT w.worker_id) AS worker_count
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating < 3
GROUP BY d.department;

15. Retrieve the department and average rating for workers in each department

SELECT d.department, AVG(r.rating) AS average_rating
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
GROUP BY d.department;

16. Display the department names where the total salary is more than 1,000,000

SELECT d.department, SUM(w.salary) AS total_salary
FROM Worker w
JOIN Department d ON w.department_id = d.id
GROUP BY d.department
HAVING SUM(w.salary) > 1000000;

17. Get the workers who have a rating of 1 and their department name

SELECT w.first_name, w.last_name, d.department
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating = 1;

18. Get the count of workers joining year-wise

SELECT YEAR(w.joining_date) AS join_year, COUNT(w.worker_id) AS worker_count
FROM Worker w
GROUP BY YEAR(w.joining_date);

19. Fetch data whose joining in the month of February

SELECT w.first_name, w.last_name, w.joining_date
FROM Worker w
WHERE MONTH(w.joining_date) = 2;

20. Find the workers who joined the company after the 15th date

SELECT w.first_name, w.last_name, w.joining_date
FROM Worker w
WHERE DAY(w.joining_date) > 15;

21. Find the average salary of workers who have been rated 5 in each department

SELECT d.department, AVG(w.salary) AS average_salary
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating = 5
GROUP BY d.department;

22. List the departments where the number of workers with a rating of 4 or more exceeds 3

SELECT d.department
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating >= 4
GROUP BY d.department
HAVING COUNT(DISTINCT w.worker_id) > 3;

23. Show the workers who have a salary greater than 200,000 and were rated 3 or higher

SELECT w.first_name, w.last_name, w.salary, r.rating
FROM Worker w
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE w.salary > 200000 AND r.rating >= 3;

24. Retrieve the department name and the total salary of workers in that department where the average rating is below 3

SELECT d.department, SUM(w.salary) AS total_salary
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
GROUP BY d.department
HAVING AVG(r.rating) < 3;

25. Display the departments with more than 2 workers who have been rated 2 or higher

SELECT d.department, COUNT(DISTINCT w.worker_id) AS worker_count
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating >= 2
GROUP BY d.department
HAVING COUNT(DISTINCT w.worker_id) > 2;

26. Get the department-wise count of workers who joined before 2014

SELECT d.department, COUNT(w.worker_id) AS worker_count
FROM Worker w
JOIN Department d ON w.department_id = d.id
WHERE YEAR(w.joining_date) < 2014
GROUP BY d.department;

27. Show the department-wise average salary of workers who have a rating of 3 or more

SELECT d.department, AVG(w.salary) AS average_salary
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating >= 3
GROUP BY d.department;

28. List the departments where the total salary of workers exceeds the department's average salary

SELECT d.department
FROM Worker w
JOIN Department d ON w.department_id = d.id
GROUP BY d.department
HAVING SUM(w.salary) > (SELECT AVG(salary) * COUNT(worker_id) FROM Worker WHERE department_id = d.id);

29. Show the department name along with the average joining date of workers in each department

SELECT d.department, AVG(w.joining_date) AS average_joining_date
FROM Worker w
JOIN Department d ON w.department_id = d.id
GROUP BY d.department;

30. Retrieve the department-wise count of workers who have been rated exactly 4

SELECT d.department, COUNT(w.worker_id) AS worker_count
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating = 4
GROUP BY d.department;

31. Display the department-wise count of workers whose salary is below 100,000

SELECT d.department, COUNT(w.worker_id) AS worker_count
FROM Worker w
JOIN Department d ON w.department_id = d.id
WHERE w.salary < 100000
GROUP BY d.department;

32. Get the total number of workers in each department with a salary greater than 150,000

SELECT d.department, COUNT(w.worker_id) AS worker_count
FROM Worker w
JOIN Department d ON w.department_id = d.id
WHERE w.salary > 150000
GROUP BY d.department;

33. List the departments that have workers with the highest salary greater than 300,000

SELECT d.department
FROM Worker w
JOIN Department d ON w.department_id = d.id
GROUP BY d.department
HAVING MAX(w.salary) > 300000;

34. Show the departments with workers who have an average rating of exactly 2

SELECT d.department
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
GROUP BY d.department
HAVING AVG(r.rating) = 2;

35. Get the departments where the average rating is less than 3 and total salary is greater than 1,000,000

SELECT d.department
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
GROUP BY d.department
HAVING AVG(r.rating) < 3 AND SUM(w.salary) > 1000000;

36. Retrieve the workers who have been rated 1 or 2 and their department names

SELECT w.first_name, w.last_name, d.department
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating = 1 OR r.rating = 2;

37. Find the department with the highest total salary

SELECT d.department, SUM(w.salary) AS total_salary
FROM Worker w
JOIN Department d ON w.department_id = d.id
GROUP BY d.department
ORDER BY total_salary DESC
LIMIT 1;

38. Find the department with the lowest average rating, excluding departments with no ratings

SELECT d.department, AVG(r.rating) AS average_rating
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
GROUP BY d.department
ORDER BY average_rating ASC
LIMIT 1;

39. Find the total salary and average rating for departments where the total salary is greater than 500,000

SELECT d.department, SUM(w.salary) AS total_salary, AVG(r.rating) AS average_rating
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
GROUP BY d.department
HAVING SUM(w.salary) > 500000;

40. Get department-wise worker names using GROUP_CONCAT

SELECT d.department, GROUP_CONCAT(CONCAT(w.first_name, ' ', w.last_name)) AS worker_names
FROM Worker w
JOIN Department d ON w.department_id = d.id
GROUP BY d.department;

41. Get workers who joined in the last 5 years

SELECT w.first_name, w.last_name, w.joining_date
FROM Worker w
WHERE w.joining_date >= DATE_SUB(CURDATE(), INTERVAL 5 YEAR);

42. Get the number of workers who joined each year

SELECT YEAR(w.joining_date) AS join_year, COUNT(w.worker_id) AS worker_count
FROM Worker w
GROUP BY YEAR(w.joining_date);

43. Get department-wise earliest and latest joining date

SELECT d.department, MIN(w.joining_date) AS earliest_joining, MAX(w.joining_date) AS latest_joining
FROM Worker w
JOIN Department d ON w.department_id = d.id
GROUP BY d.department;

44. Get workers who have been in the company for more than 10 years

SELECT w.first_name, w.last_name, w.joining_date
FROM Worker w
WHERE w.joining_date < DATE_SUB(CURDATE(), INTERVAL 10 YEAR);

45. Retrieve the workers who have a joining today

SELECT w.first_name, w.last_name, w.joining_date
FROM Worker w
WHERE DATE(w.joining_date) = CURDATE();

🎉 Conclusion

This blog has demonstrated 45 SQL queries leveraging join operations to extract actionable HR insights from a worker database. From analyzing salaries and ratings to tracking joining dates, these queries showcase the power of SQL joins in workforce management. Practice these examples to enhance your HR data analysis and drive informed decisions with efficient join-based queries.

Ship Faster, Stay Flexible.

DevCycle is the first feature flag platform with OpenFeature built-in to every open source SDK, designed to help developers ship faster while avoiding vendor-lock in.

Start shipping