Welcome to our SQL Basic Queries series, where we explore essential SQL techniques for analyzing HR data. In this blog, we focus on a worker management database with a single Worker table containing key employee details. We’ll walk through 54 SQL queries to perform fundamental operations like filtering, sorting, grouping, and data manipulation. These queries are ideal for beginners, data analysts, or HR professionals looking to build a strong SQL foundation for workforce management.
🧩 Database Schema Overview
Worker: Stores worker details
Columns:
worker_idfirst_namelast_namesalaryjoining_datedepartment
🏗️ Creating the Table
CREATE TABLE worker ( worker_id INT PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(64), last_name VARCHAR(64), salary FLOAT, joining_date DATETIME DEFAULT CURRENT_TIMESTAMP, department ENUM('HR', 'IT', 'ADMIN', 'ACOOUNTS', 'SALES') ); 📝 Inserting Sample Data
INSERT INTO worker (worker_id, first_name, last_name, salary, joining_date, department) VALUES (1, 'Monika', 'Arora', 100000, '2014-02-20 09:00:00', 'HR'), (2, 'Niharika', 'Verma', 80000, '2014-06-11 09:00:00', 'Admin'), (3, 'Vishal', 'Singhal', 300000, '2014-02-20 09:00:00', 'HR'), (4, 'Amitabh', 'Singh', 500000, '2014-02-20 09:00:00', 'Admin'), (5, 'Vivek', 'Bhati', 500000, '2014-06-11 09:00:00', 'Admin'), (6, 'Vipul', 'Diwan', 200000, '2014-06-11 09:00:00', 'Account'), (7, 'Satish', 'Kumar', 75000, '2014-01-20 09:00:00', 'Account'), (8, 'Geetika', 'Chauhan', 90000, '2014-04-11 09:00:00', 'Admin'), (9, 'Anil', 'Sharma', 120000, '2015-03-15 09:00:00', 'IT'), (10, 'Pooja', 'Mehta', 95000, '2016-01-22 09:00:00', 'HR'), (11, 'Rohit', 'Yadav', 150000, '2015-05-10 09:00:00', 'Admin'), (12, 'Swati', 'Gupta', 110000, '2016-02-18 09:00:00', 'HR'), (13, 'Rajesh', 'Joshi', 70000, '2016-04-01 09:00:00', 'IT'), (14, 'Priya', 'Kapoor', 80000, '2017-08-19 09:00:00', 'Account'), (15, 'Arjun', 'Deshmukh', 140000, '2018-06-23 09:00:00', 'IT'), (16, 'Meena', 'Reddy', 100000, '2018-09-12 09:00:00', 'Sales'), (17, 'Suresh', 'Nair', 105000, '2019-01-15 09:00:00', 'Sales'), (18, 'Kavita', 'Iyer', 95000, '2019-05-03 09:00:00', 'IT'), (19, 'Deepak', 'Mishra', 120000, '2019-12-07 09:00:00', 'Admin'), (20, 'Sneha', 'Shah', 115000, '2020-03-11 09:00:00', 'HR'), (21, 'Akash', 'Bhardwaj', 90000, '2020-07-14 09:00:00', 'IT'), (22, 'Lakshmi', 'Menon', 85000, '2021-01-25 09:00:00', 'Account'), (23, 'Gaurav', 'Tiwari', 95000, '2021-06-10 09:00:00', 'IT'), (24, 'Simran', 'Malhotra', 102000, '2021-11-20 09:00:00', 'Sales'), (25, 'Anjali', 'Pandey', 108000, '2022-03-18 09:00:00', 'HR'), (26, 'Aditya', 'Kulkarni', 98000, '2022-07-22 09:00:00', 'IT'), (27, 'Rani', 'Patel', 89000, '2023-01-05 09:00:00', 'Account'), (28, 'Rahul', 'Choudhary', 150000, '2023-05-19 09:00:00', 'Admin'), (29, 'Manish', 'Dutta', 115000, '2023-09-30 09:00:00', 'Sales'), (30, 'Neha', 'Bajaj', 93000, '2024-01-12 09:00:00', 'Admin'); 🔍 Basic SQL Queries for Worker Analysis
Below are 54 SQL queries with explanations, designed to help you master basic SQL operations for HR data analysis.
1. Fetch FIRST_NAME from the Worker table using the alias name as worker_name
SELECT first_name AS worker_name FROM worker; 2. Fetch FIRST_NAME from the Worker table in uppercase
SELECT UPPER(first_name) FROM worker; 3. Fetch unique values of DEPARTMENT from the Worker table
SELECT DISTINCT department FROM worker; 4. Fetch the first three characters of FIRST_NAME from the Worker table
SELECT LEFT(first_name, 3) FROM worker; 5. Fetch unique values of DEPARTMENT from the Worker table and print their lengths
SELECT DISTINCT department, LENGTH(department) FROM worker; 6. Print the FIRST_NAME after replacing 'a' with 'A'
SELECT REPLACE(first_name, 'a', 'A') FROM worker; 7. Concatenate FIRST_NAME and LAST_NAME into a single column COMPLETE_NAME, separated by a space
SELECT CONCAT(first_name, ' ', last_name) AS complete_name FROM worker; 8. Print all Worker details ordered by FIRST_NAME in ascending order
SELECT * FROM worker ORDER BY first_name ASC; 9. Print all Worker details ordered by FIRST_NAME in ascending order and DEPARTMENT in descending order
SELECT * FROM worker ORDER BY first_name ASC, department DESC; 10. Print details of Workers with the FIRST_NAME as "Vipul" or "Satish"
SELECT * FROM worker WHERE first_name IN ('Vipul', 'Satish'); 11. Print details of Workers excluding those with FIRST_NAME as "Vipul" or "Satish"
SELECT * FROM worker WHERE first_name NOT IN ('Vipul', 'Satish'); 12. Print details of Workers with DEPARTMENT name as "Admin"
SELECT * FROM worker WHERE department = 'Admin'; 13. Print details of Workers whose FIRST_NAME contains the letter 'a'
SELECT * FROM worker WHERE first_name LIKE '%a%'; 14. Print details of Workers whose FIRST_NAME ends with the letter 'a'
SELECT * FROM worker WHERE first_name LIKE '%a'; 15. Print details of Workers whose FIRST_NAME ends with 'h' and has six characters
SELECT * FROM worker WHERE first_name LIKE '_____h' AND LENGTH(first_name)=6; 16. Print details of Workers whose SALARY lies between 100000 and 500000
SELECT * FROM worker WHERE salary BETWEEN 100000 AND 500000; 17. Print details of Workers who joined in February 2014
SELECT * FROM worker WHERE MONTH(joining_date) = 2 AND YEAR(joining_date) = 2014; 18. Fetch the count of employees working in the department "Admin"
SELECT COUNT(*) FROM worker WHERE department = 'Admin'; 19. Fetch FIRST_NAME of Workers with SALARY >= 50000 and <= 100000
SELECT first_name FROM worker WHERE salary >= 50000 AND salary <= 100000; 20. Fetch the odd rows of a table
SELECT * FROM worker WHERE worker_id % 2 <> 0; Explanation: Uses MOD to select rows where worker_id is odd.
21. Fetch the top 5 records of a table
SELECT * FROM worker LIMIT 5; 22. Fetch the departments with less than five employees
SELECT department, COUNT(*) AS emp_count FROM worker GROUP BY department HAVING COUNT(*) < 5; 23. Fetch all departments along with the number of employees in each department
SELECT department, COUNT(*) AS emp_count FROM worker GROUP BY department; 24. Calculate the average SALARY of employees in each department
SELECT department, AVG(salary) AS avg_salary FROM worker GROUP BY department; 25. Fetch Workers who joined before January 2014
SELECT * FROM worker WHERE joining_date < '2014-01-01'; 26. Fetch Workers in the "Admin" department with SALARY above 80000
SELECT * FROM worker WHERE department = 'Admin' AND salary > 80000; 27. Fetch Workers whose FIRST_NAME contains 'i'
SELECT * FROM worker WHERE first_name LIKE '%i%'; 28. Fetch details of Workers whose SALARY lies between 75000 and 200000
SELECT * FROM worker WHERE salary > 75000 AND salary < 200000; 29. Fetch details of Workers who have 'Singh' in their LAST_NAME
SELECT * FROM worker WHERE last_name = 'Singh'; 30. Group workers by their DEPARTMENT and count the number of workers in each department
SELECT department, COUNT(*) FROM worker GROUP BY department; 31. Find the average salary for each DEPARTMENT and only include departments with an average salary greater than 100,000
SELECT department, AVG(salary) FROM worker GROUP BY department HAVING AVG(salary) > 100000; 32. Display the department name and the total salary of workers in each department, but only include departments where the total salary is greater than 400,000
SELECT department, SUM(salary) FROM worker GROUP BY department HAVING SUM(salary) > 400000; 33. Group workers by DEPARTMENT and find the maximum salary in each department. Only include departments with a maximum salary greater than 200,000
SELECT department, MAX(salary) FROM worker GROUP BY department HAVING MAX(salary) > 200000; 34. Display the department name and the number of workers in each department, but only include departments with more than 3 workers
SELECT department, COUNT(worker_id) FROM worker GROUP BY department HAVING COUNT(worker_id) > 3; 35. Group workers by DEPARTMENT and calculate the total salary of workers in each department. Show only the departments where the total salary is less than 1,000,000
SELECT department, SUM(salary) FROM worker GROUP BY department HAVING SUM(salary) < 1000000; 36. Find departments where the number of workers is less than 4, using HAVING
SELECT department, COUNT(worker_id) FROM worker GROUP BY department HAVING COUNT(worker_id) < 4; 37. Find the departments where the number of workers is greater than 5, and their average salary is above 100,000
SELECT department, COUNT(*), AVG(salary) FROM worker GROUP BY department HAVING COUNT(*) > 5 AND AVG(salary) > 100000; 38. Group workers by DEPARTMENT and fetch departments that have more than 2 workers with a salary greater than 90,000
SELECT department FROM worker WHERE salary > 90000 GROUP BY department HAVING COUNT(*) > 2; 39. Display the department and the total number of workers who earn a salary greater than 80,000
SELECT department, COUNT(worker_id) FROM worker WHERE salary > 80000 GROUP BY department; 40. Display departments with the minimum salary greater than 60,000 using HAVING
SELECT department, MIN(salary) FROM worker GROUP BY department HAVING MIN(salary) > 60000; 41. Group workers by their DEPARTMENT and show only departments where the average salary is above 150,000
SELECT department, AVG(salary) FROM worker GROUP BY department HAVING AVG(salary) > 150000; 42. Group workers by DEPARTMENT and show the departments where the total salary is greater than 500,000 but less than 1,000,000
SELECT department, SUM(salary) FROM worker GROUP BY department HAVING SUM(salary) > 500000 AND SUM(salary) < 1000000; 43. Display the DEPARTMENT name and the sum of salaries where the sum is greater than 800,000
SELECT department, SUM(salary) FROM worker GROUP BY department HAVING SUM(salary) > 800000; 44. Find departments that have at least 3 workers and have a salary greater than 100,000
SELECT department FROM worker WHERE salary > 100000 GROUP BY department HAVING COUNT(*) >= 3; 45. Calculate the total number of workers in each department and show only those departments with a count greater than 1
SELECT department, COUNT(*) AS worker_count FROM worker GROUP BY department HAVING COUNT(*) > 1; 46. Display departments with an average salary above 70,000 and group them by DEPARTMENT
SELECT department, AVG(salary) AS avg_salary FROM worker GROUP BY department HAVING AVG(salary) > 70000; 47. Group workers by DEPARTMENT and fetch the minimum salary of workers in each department. Only include departments with a minimum salary above 50,000
SELECT department, MIN(salary) AS min_salary FROM worker GROUP BY department HAVING MIN(salary) > 50000; 48. Find departments with a salary total greater than 300,000 and a count of workers greater than 2
SELECT department, SUM(salary) AS total_salary, COUNT(*) AS worker_count FROM worker GROUP BY department HAVING SUM(salary) > 300000 AND COUNT(*) > 2; 49. Group workers by DEPARTMENT and only include departments where the number of workers is exactly 2
SELECT department, COUNT(*) AS worker_count FROM worker GROUP BY department HAVING COUNT(*) = 2; 50. Calculate the average salary for each DEPARTMENT and show departments where the average salary is below 80,000
SELECT department, AVG(salary) AS avg_salary FROM worker GROUP BY department HAVING AVG(salary) < 80000; 51. Find the total salary and average salary for each DEPARTMENT and display only departments where the total salary is greater than 400,000
SELECT department, SUM(salary) AS total_salary, AVG(salary) AS avg_salary FROM worker GROUP BY department HAVING SUM(salary) > 400000; 52. Cast SALARY as an integer and display the result as SALARY_INT for each worker in the Worker table
SELECT worker_id, first_name, last_name, CAST(salary AS INT) AS SALARY_INT FROM worker; 53. Display the sum of SALARY as a FLOAT value
SELECT SUM(salary) AS total_salary FROM worker; 🎉 Conclusion
This blog has showcased 54 SQL queries to perform essential operations on a worker database, covering filtering, sorting, grouping, and data transformation. These foundational SQL techniques empower HR professionals and analysts to efficiently manage and analyze workforce data. Practice these queries to strengthen your SQL skills and apply them to real-world HR challenges.
Keep exploring, and happy querying!
Top comments (0)