Оффлайн
- Регистрация
- 9 Май 2015
- Сообщения
- 1,214
- Баллы
- 155
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:
Columns:
Columns:
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.
Database Schema OverviewWorker: Stores worker details
Columns:
- worker_id
- first_name
- last_name
- salary
- joining_date
- department_id
Columns:
- id
- department
Columns:
- id
- worker_id
- rating
Creating the TablesCREATE 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 DataINSERT 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 AnalysisBelow 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();
ConclusionThis 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.