SQL Exercises Solved Step by Step: Practical Guide | Blog

Learning database theory is great, but the only way to truly master SQL (Structured Query Language) is by practicing. Solving real problems helps you internalize the logic of queries, joins, and aggregations.

In this article, we'll walk through several practical SQL exercises, from the most basic level to slightly more advanced queries. I’ll explain the reasoning behind each solution step by step so you can apply it in your own projects or technical interviews.

The Scenario: Our Database

For these exercises, imagine we’re working with two very common tables in any company: employees and departments.

Table departments: has columns department_id and department_name.
Table employees: has columns id_employee, name, salary and department_id.

Exercise 1: Basic Filtering (WHERE)

The Problem: We need to get the name and salary of all employees earning more than 3000 euros per month.

Step-by-step: First specify which columns we want using SELECT. Then indicate which table the data comes from with FROM. Finally apply the condition with WHERE.


SELECT nombre, salario
FROM empleados
WHERE salario > 3000;

Exercise 2: Joining Tables (INNER JOIN)

The Problem: Show a list with each employee's name and the name of the department they work in.

Step-by-step: Here’s a small challenge. The employee name lives in one table, but the department name lives in another. We need to use an INNER JOIN to link them using the column they share: department_id.


SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d
  ON e.department_id = d.department_id;

Note: we use the aliases "e" and "d" so we don’t have to type the full table name repeatedly. It’s a great habit to keep your code clean.

Exercise 3: Grouping and Aggregation (GROUP BY)

The Problem: We want to know how many employees work in each department and what the average salary is in that department.

Step-by-step: Whenever you hear phrases like "for each" or "per", immediately think of GROUP BY. Here we’ll group by department name and use aggregate functions such as COUNT() to count employees and AVG() to compute the average salary.


SELECT 
  d.department_name, 
  COUNT(e.id_employee) AS total_employees, 
  ROUND(AVG(e.salary), 2) AS average_salary
FROM departments d
LEFT JOIN employees e
  ON d.department_id = e.department_id
GROUP BY d.department_name;

Notice that I used LEFT JOIN instead of INNER JOIN. This ensures that if a department doesn't have any employees yet, it will still show up in the list (with a total of 0) instead of disappearing from the report. Also, ROUND(..., 2) limits the average's decimals to two.

Exercise 4: Filtering Groups (HAVING)

The Problem: Based on the previous query, return only those departments that have more than 5 employees.

Step-by-step: A very common beginner mistake is trying to use WHERE for this. But WHERE is applied before grouping. To filter a result after you’ve grouped (like counting the number of employees), you must use HAVING.


SELECT 
  d.department_name, 
  COUNT(e.id_employee) AS total_employees
FROM departments d
INNER JOIN employees e
  ON d.department_id = e.department_id
GROUP BY d.department_name
HAVING COUNT(e.id_employee) > 5;

Conclusion

SQL can seem intimidating at first because of its uppercase keywords, but as you can see, it's an extremely logical language. Once you understand the order of execution (FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT), writing complex queries becomes like solving a puzzle step by step.