codesyllabus - Learn to Programming and coding

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)

El Problema: Queremos saber cuántos empleados trabajan en cada departamento y cuál es el salario promedio en ese departamento.

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;

Fíjate que usé LEFT JOIN en lugar de INNER JOIN. Esto asegura que si un departamento no tiene empleados todavía, igual aparecerá en la lista (con un total de 0), en vez de desaparecer del reporte. Además, ROUND(..., 2) limita los decimales del promedio a dos.

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