SQL Practices

Mastering SQL: Essential Employee & Join Queries Explained (With Interactive Examples)

Whether you are starting your database management journey or brushing up for a technical interview, understanding how to query single tables and combine relational data using Joins is essential.

This guide covers individual employee table operations as well as multi-table combinations using an Employees table and a Departments table.

What You'll Learn in This Guide:

• Basic Record Retrieval & Filtering: Fetching boundary rows, numerical ranges, and wildcard string matches.
• Aggregation & Data Cleanup: Grouping counts and removing duplicates.
• Advanced Subqueries & Window Functions: Using DENSE_RANK(), comparing against department averages, and manager relationships.
• SQL Joins: Combining data using INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and CROSS JOIN.

---

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

Departments table

DeptID	DepartmentName	Location	Budget
1	HR	New York	500000.00
2	IT	San Francisco	1500000.00
3	Sales	Chicago	800000.00
4	Marketing	Austin	600000.00

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: Sorts records in ascending order by Employee ID and uses LIMIT 1 to return the 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: Sorts the table in descending order so the highest Employee ID appears first, isolating it with LIMIT 1.

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 rows to include salaries 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: Uses the LIKE operator with a wildcard (A%) to match names starting with "A".

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: Groups rows by department, counts entries, and sorts them from largest to smallest.

SQL Code:

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

Explanation: Retains records with the minimum EmpID per email address and clears out matching duplicate rows.

SQL Code:

SELECT DISTINCT e1.salary FROM Employees e1 WHERE 3 - 1 = (SELECT COUNT(DISTINCT e2.salary) FROM Employees e2 WHERE e2.salary > e1.salary);

Output:

salary
70000.00

Explanation: Uses a correlated subquery to count distinct higher salaries, targeting the 3rd highest.

SQL Code:

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

Output:

salary
70000.00

Explanation: Skips the top 2 records via OFFSET 2 and pulls the 3rd via LIMIT 1.

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;

Output:

salary
70000.00

Explanation: Uses the window function DENSE_RANK() to rank unique salaries sequentially without gaps.

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: Compares each employee's salary against their respective department's mean average.

SQL Code:

SELECT e.EmpName, e.Department, d.Location FROM Employees e INNER JOIN Departments d ON e.Department = d.DepartmentName;

Output:

EmpName	Department	Location
Alice Vance	HR	New York
Bob Smith	IT	San Francisco
Charlie Day	IT	San Francisco
Diana Prince	Sales	Chicago
Evan Wright	IT	San Francisco
Fiona Gallagher	HR	New York
Bob Smith	IT	San Francisco

Explanation: An INNER JOIN returns only matching records present in both the Employees and Departments tables.

SQL Code:

SELECT e.EmpName, e.Department, d.Budget FROM Employees e LEFT JOIN Departments d ON e.Department = d.DepartmentName;

Output:

EmpName	Department	Budget
Alice Vance	HR	500000.00
Bob Smith	IT	1500000.00
Charlie Day	IT	1500000.00
Diana Prince	Sales	800000.00
Evan Wright	IT	1500000.00
Fiona Gallagher	HR	500000.00
Bob Smith	IT	1500000.00

Explanation: A LEFT JOIN keeps every record from the left table (Employees), returning matching department metrics or null values if unavailable.

SQL Code:

SELECT d.DepartmentName, d.Location, e.EmpName FROM Employees e RIGHT JOIN Departments d ON e.Department = d.DepartmentName;

Output:

DepartmentName	Location	EmpName
HR	New York	Alice Vance
HR	New York	Fiona Gallagher
IT	San Francisco	Bob Smith
IT	San Francisco	Charlie Day
IT	San Francisco	Evan Wright
IT	San Francisco	Bob Smith
Sales	Chicago	Diana Prince
Marketing	Austin	NULL

Explanation: A RIGHT JOIN returns all records from the right table (Departments), showcasing empty units like Marketing.

SQL Code:

SELECT e.EmpName, d.DepartmentName, d.Budget FROM Employees e FULL OUTER JOIN Departments d ON e.Department = d.DepartmentName;

Output:

EmpName	DepartmentName	Budget
Alice Vance	HR	500000.00
Bob Smith	IT	1500000.00
Charlie Day	IT	1500000.00
Diana Prince	Sales	800000.00
Evan Wright	IT	1500000.00
Fiona Gallagher	HR	500000.00
Bob Smith	IT	1500000.00
NULL	Marketing	600000.00

Explanation: A FULL OUTER JOIN combines records from both sides, populating unmatched data fields with NULL.

SQL Code:

SELECT e.EmpName, d.DepartmentName FROM Employees e CROSS JOIN Departments d;

Output:

EmpName	DepartmentName
Alice Vance	HR
Alice Vance	IT
Alice Vance	Sales
Alice Vance	Marketing
... (24 total rows)	...

Explanation: A CROSS JOIN pairs every row from the first table with every row of the second table.