DEV Community

Cover image for Mastering SQL Basics: (Part 2)
Jemmy Dalsaniya
Jemmy Dalsaniya

Posted on

Mastering SQL Basics: (Part 2)

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)
);

Enter fullscreen mode Exit fullscreen mode

📝 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');

Enter fullscreen mode Exit fullscreen mode

🔍 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;
Enter fullscreen mode Exit fullscreen mode

2. Select the employees in department 30

SELECT * FROM tbl_emp
WHERE deptno = 30;
Enter fullscreen mode Exit fullscreen mode

3. List the names, numbers and departments of all clerks

SELECT emp_name, emp_id, deptno FROM tbl_emp
WHERE job = 'Clerk';
Enter fullscreen mode Exit fullscreen mode

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;

Enter fullscreen mode Exit fullscreen mode

5. Employees whose commission is greater than their salaries

SELECT * FROM tbl_emp
WHERE commission > salary;
Enter fullscreen mode Exit fullscreen mode

6. Employees with Commission Greater Than 60% of Salary

SELECT * FROM tbl_emp
WHERE commission > salary * 0.6;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

8. Salesmen in Department 30 with Salary Over $1,500

SELECT * FROM tbl_emp
WHERE deptno = 30 AND job = 'Salesman' AND salary > 1500;
Enter fullscreen mode Exit fullscreen mode

9. Employees Who Are Managers or Presidents

SELECT * FROM tbl_emp
WHERE job='Manager' or job='President';
Enter fullscreen mode Exit fullscreen mode

10. Managers Not in Department 30

SELECT * FROM tbl_emp
WHERE job = 'Manager' AND deptno != 30;
Enter fullscreen mode Exit fullscreen mode

11. Managers and Clerks in Department 10

SELECT * FROM tbl_emp
WHERE deptno = 10 AND job IN ('Manager', 'Clerk');
Enter fullscreen mode Exit fullscreen mode

12. Managers (Any Dept) and Clerks in Department 20

SELECT * FROM tbl_emp
WHERE job = 'Manager' OR (job = 'Clerk' AND deptno = 20);
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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');
Enter fullscreen mode Exit fullscreen mode

15. Employees earning between 1200 and 1400

SELECT * FROM tbl_emp
WHERE salary BETWEEN 1200 AND 1400;
Enter fullscreen mode Exit fullscreen mode

16. Find the employees who are clerks, analysts or salesmen

SELECT * FROM tbl_emp
WHERE job IN ('Clerk', 'Analyst', 'Salesman');
Enter fullscreen mode Exit fullscreen mode

17. Find the employees who are not clerks, analysts or salesmen

SELECT * FROM tbl_emp
WHERE job NOT IN ('Clerk', 'Analyst', 'Salesman');
Enter fullscreen mode Exit fullscreen mode

18. Find the employees who do not receive commission

SELECT * FROM tbl_emp
WHERE commission = 0;
Enter fullscreen mode Exit fullscreen mode

19. Different jobs of employees receiving commission

SELECT DISTINCT job FROM tbl_emp
WHERE commission > 0;
Enter fullscreen mode Exit fullscreen mode

20. Employees with no commission or commission < 100

SELECT * FROM tbl_emp
WHERE commission = 0 OR commission < 100;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

22. Find all the employees whose total earning is greater than 2000/-

SELECT emp_name
FROM tbl_emp
WHERE salary + commission > 2000;
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

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%';
Enter fullscreen mode Exit fullscreen mode

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%';
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

29. Find the managers hired in the year 2003

SELECT emp_name
FROM tbl_emp
WHERE job = 'Manager' AND YEAR(hiredate) = 2003;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

31. Display the names of all the employees right aligning them to 15 characters

SELECT LPAD(emp_name, 15, ' ') FROM tbl_emp;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

33. Display the names of all the employees without any leading 'A'

SELECT TRIM(LEADING 'A' FROM emp_name) FROM tbl_emp;
Enter fullscreen mode Exit fullscreen mode

34. Display the names of all the employees without any trailing 'R'

SELECT TRIM(TRAILING 'R' FROM emp_name) FROM tbl_emp;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

36. Display the names of all the employees replacing 'A' with 'a'

SELECT REPLACE(emp_name, 'A', 'a') FROM tbl_emp;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

🏁 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.

Warp.dev image

Warp is the highest-rated coding agent—proven by benchmarks.

Warp outperforms every other coding agent on the market, and gives you full control over which model you use. Get started now for free, or upgrade and unlock 2.5x AI credits on Warp's paid plans.

Download Warp

Top comments (0)

Warp.dev image

Warp is the highest-rated coding agent—proven by benchmarks.

Warp outperforms every other coding agent on the market, and gives you full control over which model you use. Get started now for free, or upgrade and unlock 2.5x AI credits on Warp's paid plans.

Download Warp

👋 Kindness is contagious

Take a moment to explore this thoughtful article, beloved by the supportive DEV Community. Coders of every background are invited to share and elevate our collective know-how.

A heartfelt "thank you" can brighten someone's day—leave your appreciation below!

On DEV, sharing knowledge smooths our journey and tightens our community bonds. Enjoyed this? A quick thank you to the author is hugely appreciated.

Okay