Welcome to Part 2 of our SQL Subqueries series, where we dive deeper into leveraging subqueries to unlock powerful insights from an HR database.
In this blog, we focus on a practical employee management system with tables for Employees and Departments. We’ll walk through the schema, insert sample data, and demonstrate 42 advanced SQL queries, emphasizing subqueries to address complex HR and business scenarios. This guide is perfect for data analysts, HR professionals, or developers looking to sharpen their SQL skills with real-world examples.
🧩 Database Schema Overview
tbl_emp
: Stores employee details
Columns:
emp_id
emp_name
street_address
city
salary
commission
job
deptno
hiredate
company_name
tbl_department
: Contains department information
Columns:
deptno
dept_name
🏗️ Creating the Tables
CREATE TABLE tbl_department (
deptno INT PRIMARY KEY,
dept_name VARCHAR(50)
);
CREATE TABLE tbl_emp (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
street_address VARCHAR(100),
city VARCHAR(50),
salary DECIMAL(10,2),
commission DECIMAL(10,2),
job VARCHAR(50),
deptno INT,
hiredate DATE,
company_name VARCHAR(100),
FOREIGN KEY (deptno) REFERENCES tbl_department(deptno)
);
📝 Inserting Sample Data
INSERT INTO tbl_department (deptno, dept_name) VALUES
(10, 'Sales'),
(20, 'HR'),
(30, 'IT'),
(40, 'Finance'),
(50, 'Marketing');
INSERT INTO tbl_emp (emp_id, emp_name, street_address, city, salary, commission, job, deptno, hiredate, company_name) VALUES
(1, 'Amit Sharma', '123 MG Road', 'Delhi', 12000.00, 500.00, 'Salesman', 10, '2015-06-10', 'First Bank Corporation'),
(2, 'Priya Verma', '456 Park Avenue', 'Mumbai', 25000.00, 2000.00, 'Manager', 20, '2012-08-15', 'First Bank Corporation'),
(3, 'Rajesh Kumar', '789 Green Street', 'Bangalore', 18000.00, 1500.00, 'Salesman', 30, '2016-02-20', 'Second Bank Corporation'),
(4, 'Anjali Gupta', '321 Ocean Drive', 'Chennai', 9500.00, 0.00, 'Clerk', 10, '2018-07-10', 'First Bank Corporation'),
(5, 'Vikram Singh', '654 Palm Street', 'Delhi', 22000.00, 3000.00, 'Salesman', 10, '2014-04-01', 'First Bank Corporation'),
(6, 'Rina Patel', '987 Sunset Boulevard', 'Ahmedabad', 13000.00, 0.00, 'Clerk', 20, '2017-05-23', 'First Bank Corporation'),
(7, 'Manoj Desai', '852 Elm Street', 'Mumbai', 30000.00, 3500.00, 'Manager', 30, '2010-01-12', 'Second Bank Corporation'),
(8, 'Sonia Reddy', '741 Maple Lane', 'Hyderabad', 15000.00, 1000.00, 'Salesman', 30, '2019-11-02', 'First Bank Corporation'),
(9, 'Sandeep Jain', '258 High Street', 'Kolkata', 20000.00, 2500.00, 'Manager', 40, '2011-03-22', 'First Bank Corporation'),
(10, 'Neha Kapoor', '963 River Road', 'Pune', 27000.00, 1500.00, 'Clerk', 50, '2013-09-14', 'Second Bank Corporation');
🔍 Advanced SQL Subqueries for Practice
1. Employees at First Bank Corporation Earning Over $10,000
SELECT * FROM tbl_emp
WHERE company_name = 'First Bank Corporation' AND salary > 10000;
2. Select the employees in department 30
SELECT * FROM tbl_emp
WHERE deptno = 30;
3. List the names, numbers and departments of all clerks
SELECT emp_name, emp_id, deptno FROM tbl_emp
WHERE job = 'Clerk';
4. Department numbers and names of employees in departments with deptno > 20
SELECT DISTINCT d.deptno, d.dept_name
FROM tbl_emp e
JOIN tbl_department d ON e.deptno = d.deptno
WHERE d.deptno > 20;
5. Employees whose commission is greater than their salaries
SELECT * FROM tbl_emp
WHERE commission > salary;
6. Employees with Commission Greater Than 60% of Salary
SELECT * FROM tbl_emp
WHERE commission > salary * 0.6;
7. List name, job and salary of all employees in department 20 who earn more than 2000/-
SELECT emp_name, job, salary FROM tbl_emp
WHERE deptno = 20 AND salary > 2000;
8. Salesmen in Department 30 with Salary Over $1,500
SELECT * FROM tbl_emp
WHERE deptno = 30 AND job = 'Salesman' AND salary > 1500;
9. Employees Who Are Managers or Presidents
SELECT * FROM tbl_emp
WHERE job='Manager' or job='President';
10. Managers Not in Department 30
SELECT * FROM tbl_emp
WHERE job = 'Manager' AND deptno != 30;
11. Managers and Clerks in Department 10
SELECT * FROM tbl_emp
WHERE deptno = 10 AND job IN ('Manager', 'Clerk');
12. Managers (Any Dept) and Clerks in Department 20
SELECT * FROM tbl_emp
WHERE job = 'Manager' OR (job = 'Clerk' AND deptno = 20);
13. Find the details of all the managers in dept. 10 and all clerks in dept 20 and all employees who are neither managers nor clerks but whose salary is more than or equal to 2000/-
SELECT * FROM tbl_emp
WHERE (job = 'Manager' AND deptno = 10)
OR (job = 'Clerk' AND deptno = 20)
OR (job NOT IN ('Manager', 'Clerk') AND salary >= 2000);
14. Find the names of anyone in dept. 20 who is neither manager nor clerks
SELECT emp_name FROM tbl_emp
WHERE deptno = 20 AND job NOT IN ('Manager', 'Clerk');
15. Employees earning between 1200 and 1400
SELECT * FROM tbl_emp
WHERE salary BETWEEN 1200 AND 1400;
16. Find the employees who are clerks, analysts or salesmen
SELECT * FROM tbl_emp
WHERE job IN ('Clerk', 'Analyst', 'Salesman');
17. Find the employees who are not clerks, analysts or salesmen
SELECT * FROM tbl_emp
WHERE job NOT IN ('Clerk', 'Analyst', 'Salesman');
18. Find the employees who do not receive commission
SELECT * FROM tbl_emp
WHERE commission = 0;
19. Different jobs of employees receiving commission
SELECT DISTINCT job FROM tbl_emp
WHERE commission > 0;
20. Employees with no commission or commission < 100
SELECT * FROM tbl_emp
WHERE commission = 0 OR commission < 100;
21. If all the employees not receiving commission is entitles to a bonus of Rs. 250/- show the net earnings of all the employees
SELECT emp_name, salary + commission + IF(commission = 0, 250, 0) AS net_earning
FROM tbl_emp;
22. Find all the employees whose total earning is greater than 2000/-
SELECT emp_name
FROM tbl_emp
WHERE salary + commission > 2000;
23. Find all the employees whose name begins or ends with 'M'
SELECT emp_name
FROM tbl_emp
WHERE emp_name LIKE 'M%' OR emp_name LIKE '%M';
24. Find all the employees whose names contain the letter 'M' in any case
SELECT emp_name
FROM tbl_emp
WHERE emp_name LIKE '%M%' OR emp_name LIKE '%m%';
25. Find all the employees whose names are upto 15 character long and have letter 'R' as 3rd character of their names
SELECT emp_name
FROM tbl_emp
WHERE LENGTH(emp_name) <= 15 AND emp_name LIKE '__R%';
26. Find all the employees who were hired in the month of February (of any year)
SELECT emp_name
FROM tbl_emp
WHERE MONTH(hiredate) = 2;
27. Find all the employees who were hired on last day of the month
SELECT emp_name
FROM tbl_emp
WHERE LAST_DAY(hiredate) = hiredate;
28. Find all the employees who were hired more than 2 years ago
SELECT emp_name
FROM tbl_emp
WHERE hiredate < CURDATE() - INTERVAL 2 YEAR;
29. Find the managers hired in the year 2003
SELECT emp_name
FROM tbl_emp
WHERE job = 'Manager' AND YEAR(hiredate) = 2003;
30. Display the names and jobs of all the employees separated by a space
SELECT CONCAT(emp_name, ' ', job) AS name_and_job
FROM tbl_emp;
31. Display the names of all the employees right aligning them to 15 characters
SELECT LPAD(emp_name, 15, ' ') FROM tbl_emp;
32. isplay the names of all the employees padding them to the right up to 15 characters with '*'
SELECT RPAD(emp_name, 15, '*') FROM tbl_emp;
33. Display the names of all the employees without any leading 'A'
SELECT TRIM(LEADING 'A' FROM emp_name) FROM tbl_emp;
34. Display the names of all the employees without any trailing 'R'
SELECT TRIM(TRAILING 'R' FROM emp_name) FROM tbl_emp;
35. Show the first 3 and last 3 characters of the names of all the employees
SELECT CONCAT(LEFT(emp_name, 3), RIGHT(emp_name, 3)) AS first_last_chars
FROM tbl_emp;
36. Display the names of all the employees replacing 'A' with 'a'
SELECT REPLACE(emp_name, 'A', 'a') FROM tbl_emp;
37. Display the names of all the employees and position where the string 'AR' occurs in the name
SELECT emp_name, POSITION('AR' in emp_name) AS position
FROM tbl_emp;
38. Show the salary of all the employees, rounding it to the nearest Rs. 1000/-
SELECT emp_name, ROUND(salary, -3) AS rounded_salary
FROM tbl_emp;
39. Display the names, jobs and salaries of employees, sorting on job and salary
SELECT emp_name, job, salary
FROM tbl_emp
ORDER BY job, salary;
40. Display the names, jobs and salaries of employees, sorting on descending order of job and within job sorted on salary
SELECT emp_name, job, salary
FROM tbl_emp
ORDER BY job DESC, salary ASC;
41. List the employee names, department names and salary for those employees who have completed 1 year of service
SELECT e.emp_name, d.dept_name, e.salary
FROM tbl_emp e
JOIN tbl_department d ON e.deptno = d.deptno
WHERE e.hiredate <= DATE_SUB(CURDATE(), INTERVAL 1 YEAR);
42. List the employee names, department names and hiredate for those employees who have joined in 2003 . Sort your output in the order of joining date
SELECT e.emp_name, d.dept_name, e.hiredate
FROM tbl_emp e
JOIN tbl_department d ON e.deptno = d.deptno
WHERE YEAR(e.hiredate) = 2003
ORDER BY e.hiredate;
🏁 Conclusion
This second part of our SQL Subqueries series has showcased advanced queries to extract critical HR insights, with a focus on subqueries for complex filtering and comparisons.
From analyzing employee earnings to tracking hire dates and name patterns, these queries demonstrate SQL’s power in HR data management.
Use these examples as a foundation to build your own queries and drive informed business decisions.
Top comments (0)