SQL Practices

Mastering SQL: 11 Essential Employee Table Queries Explained (With Interactive Examples)

Whether you are just starting your journey into database management or looking for a quick refresher for your next technical interview, understanding how to manipulate and query relational data is a core skill.

To make learning SQL more practical and visual, this guide breaks down 11 essential SQL queries using a sample Employee Table. Each query comes complete with the exact syntax, expected output, and a clear explanation of how it works under the hood.

What You'll Learn in This Guide:

• Basic Record Retrieval: Learn how to quickly fetch the very first or last records from your table using ORDER BY and LIMIT.
• Filtering & Pattern Matching: Master the BETWEEN operator for ranges and the LIKE wildcard operator for string matching.
• Aggregation & Grouping: Use GROUP BY and COUNT() to summarize department-level data efficiently.
• Data Cleanup: Discover how to remove duplicate rows while preserving the primary record.
• Advanced Operations: Dive deep into complex scenarios like finding the Nth highest salary using subqueries, window functions (DENSE_RANK()), self-joins, and comparing employee salaries against department averages.

---

Employees table

EmpID	EmpName	Department	Salary	ManagerID	Email
101	Alice Vance	HR	70000.00	NULL	alice@company.com
102	Bob Smith	IT	95000.00	101	bob@company.com
103	Charlie Day	IT	82000.00	102	charlie@company.com
104	Diana Prince	Sales	60000.00	101	diana@company.com
105	Evan Wright	IT	95000.00	102	evan@company.com
106	Fiona Gallagher	HR	65000.00	101	fiona@company.com
107	Bob Smith	IT	95000.00	102	bob@company.com

SQL Code:

SELECT * FROM Employees ORDER BY EmpID ASC LIMIT 1;

Output:

EmpID	EmpName	Department	Salary	ManagerID	Email
101	Alice Vance	HR	70,000.00	NULL	alice@company.com

Explanation:

This query sorts all records in ascending order based on the Employee ID (EmpID ASC) and uses LIMIT 1 to return only the very first row.

SQL Code:

SELECT * FROM Employees ORDER BY EmpID DESC LIMIT 1;

Output:

EmpID	EmpName	Department	Salary	ManagerID	Email
107	Bob Smith	IT	95,000.00	102	bob@company.com

Explanation:

By sorting the table in descending order (EmpID DESC), the highest Employee ID appears first. The LIMIT 1 restriction then isolates and displays just that final record.

SQL Code:

SELECT * FROM Employees
WHERE Salary BETWEEN 50000 AND 100000;

Output:

EmpID	EmpName	Department	Salary	ManagerID	Email
102	Bob Smith	IT	95,000.00	101	bob@company.com
103	Charlie Day	IT	82,000.00	102	charlie@company.com
105	Evan Wright	IT	95,000.00	102	evan@company.com
107	Bob Smith	IT	95,000.00	102	bob@company.com

Explanation:

The BETWEEN operator filters the rows to include only those where the salary falls inclusively between 50,000 and 100,000.

SQL Code:

SELECT * FROM Employees
WHERE EmpName LIKE 'A%';

Output:

EmpID	EmpName	Department	Salary	ManagerID	Email
101	Alice Vance	HR	70,000.00	NULL	alice@company.com

Explanation:

The LIKE operator combined with the wildcard character (A%) matches any employee name that begins with the letter "A", followed by any sequence of characters.

SQL Code:

SELECT Department, COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY Department
ORDER BY TotalEmployees DESC;

Output:

Department	TotalEmployees
IT	4
HR	2
Sales	1

Explanation:

This groups the rows by department and counts how many employees belong to each (COUNT(*)). It then sorts the summary list from the largest department to the smallest using ORDER BY DESC.

SQL Code:

DELETE FROM Employees
WHERE EmpID NOT IN (
    SELECT MIN(EmpID)
    FROM Employees
    GROUP BY Email
);

Explanation:

This query finds duplicate records sharing the same email address. It preserves the row with the lowest EmpID (using MIN(EmpID)) and deletes any extra entries that do not match that ID.

SQL Code:

SELECT DISTINCT e1.salary
FROM Employees e1
WHERE 3 - 1 = (   -- Replace N with your number
    SELECT COUNT(DISTINCT e2.salary)
    FROM Employees e2
    WHERE e2.salary > e1.salary
);

Note: Works in all SQL databases but slow on large tables.

Output:

salary
70000.00

Explanation:

A correlated subquery counts how many distinct salaries are strictly greater than the current salary (e1.salary). By setting the condition to equal N - 1, it isolates the 3rd highest salary.

SQL Code:

SELECT DISTINCT salary FROM Employees
ORDER BY salary DESC LIMIT 1 OFFSET 2;

Note: Works in MySQL and PostgreSQL only. Very fast in performance.

Output:

salary
70000.00

Explanation:

This sorts unique salaries from highest to lowest. OFFSET 2 skips the top 2 highest salaries, and LIMIT 1 grabs the single record immediately following them (the 3rd highest).

SQL Code:

SELECT salary FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank 
    FROM Employees
) ranked_table
WHERE salary_rank = 3; -- Replace 3 with your Nth number

Note: Works in all modern SQL databases. Fast and optimized performance.

Output:

salary
70000.00

Explanation:

The window function DENSE_RANK() assigns a ranking number to each unique salary in descending order without gaps. The outer query then filters out everything except the rank matching 3.

SQL Code:

SELECT EmpName, Department, Salary
FROM Employees e
WHERE Salary > (
    SELECT AVG(Salary)
    FROM Employees
    WHERE Department = e.Department
);

Output:

EmpName	Department	Salary
Alice Vance	HR	70,000.00
Bob Smith	IT	95,000.00
Evan Wright	IT	95,000.00
Bob Smith	IT	95,000.00

Explanation:

The subquery calculates the mean salary specifically for each individual employee's department. The main query compares every person's salary against that department benchmark, returning only those who earn more.

SQL Code:

SELECT e.EmpName AS Employee, e.Salary, m.EmpName AS Manager, m.Salary AS ManagerSalary
FROM Employees e
INNER JOIN Employees m ON e.ManagerID = m.EmpID
WHERE e.Salary > m.Salary;

Output:

Employee	Salary	Manager	ManagerSalary
Bob Smith	95,000.00	Alice Vance	70,000.00

Explanation:

This performs a self-join by treating the Employees table as two entities (e for employee and m for manager) based on the ManagerID. The condition then filters pairs where the subordinate's salary exceeds their supervisor's.