Solving SQL Queries Using ER Diagrams: A Simple 4-Step Approach
- Nivedita Chitale
- Jul 1
- 5 min read
When working with databases that contain multiple related tables, it can be overwhelming to identify relationships, decide where to start and understand how tables connect. Many learners struggle with SQL not because of the syntax itself, but because they find it difficult to interpret the database schema.
I faced the same challenge while working on a SQL assignment. That's when I realized that solving SQL problems using ER diagrams isn't random, it's a structured process. Once you follow a structured approach, even complex multi-table queries become much easier to solve.
In this blog, I'll share a simple 4-step method that can help you confidently solve SQL queries using ER diagrams. By following this approach, you'll be able to break down complex problems, identify the required joins and write accurate SQL queries with confidence.
Before writing SQL queries, it's also important to understand the database schema. A database schema is the blueprint of a database that defines its tables, columns, data types and relationships. An ER (Entity Relationship) diagram is a visual representation of that schema, making it easier to understand how tables are connected and which keys are used to link them. It shows how data is structured and connected at a logical level.
An ER (Entity Relationship) diagram represents this schema visually. Instead of going through table definitions one by one, it allows you to quickly see how tables are linked and which keys connect them.
Why ER Diagrams Matter:
An ER (Entity Relationship) diagram represents the structure of a database in a visual format:
Entities --> Tables
Relationships --> How tables are connected
Keys --> Primary keys and foreign keys used to link tables
Think of an ER diagram as a map. Your SQL query is the route and the relationships between tables are the roads connecting your destination. To write an efficient query, start with the table that contains your desired output and trace the shortest and most appropriate path through the related tables to reach the data you need.
The 4-Step Method
Step 1: Understand the Question
Before looking at the ER diagram, focus on the requirements of the question:
What output is expected?
Which columns are required?
Are there any filters or conditions?
Is aggregation needed?
Once you clearly understand the requirement, move on to the schema.
Step 2: Identify the Starting Table
Start with the table that contains the information you ultimately want to display in your output.
This table will usually provide the final output.
It becomes the foundation of your query.
Step 3: Follow the Relationship Path and Convert It into JOINs
Examine the ER diagram and identify how the tables are connected.
Trace primary and foreign keys.
Identify intermediate tables if required. (Intermediate tables are the tables that connect your starting table to the table containing the required data.)
Convert each relationship into a JOIN statement.
At this stage, your goal is to trace the path from the table containing the required output to the table containing the data you need. Once the relationship path is clear, each connection can be translated into a SQL JOIN.
Step 4: Apply Aggregation, Filtering, and Grouping
Finally, determine whether the query requires:
Aggregation (COUNT, SUM, AVG, MAX, MIN)
Filtering (WHERE)
Grouping (GROUP BY)
Sorting (ORDER BY)
At this stage, constructing the final query becomes much easier.
Example 1: DVD Rental Database - Find the city with the maximum number of staff members.
Step 1: Understand the Requirement
We need to:
Find the city
Count the number of staff members in each city
Return the city with the highest count
Step 2: Identify the Starting Table
Since the required output is the city name, the starting table is: city
Step 3: Follow the Relationship Path and Convert It into JOINs
Suppose the ER diagram shows the following relationship path:
city → address → staff
This tells us that to reach the Staff table, we must first go through the Address table.
Relationships:
city.city_id = address.city_id
address.address_id = staff.address_id
SQL joins:
FROM city c
JOIN staff s ON a.address_id = s.address_id
Step 4: Apply Aggregation and Sorting
We need to:
Count staff members using COUNT()
Group results by city
Sort by staff count in descending order
Return the top result
Final query:
SELECT c.city,
COUNT(s.staff_id) AS staff_count
FROM city c
JOIN staff s ON a.address_id = s.address_id
GROUP BY c.city
ORDER BY staff_count DESC
LIMIT 1;
Why this works:
Each staff member is linked to an address.
Each address belongs to a city.
Grouping by city allows us to count the number of staff members in each city.
Sorting the counts in descending order and limiting the result to one row returns the city with the highest number of staff members.
Now let's apply the same four-step approach to a more complex query involving multiple intermediate tables.
Example 2: DVD Rental Database - Find the category with the highest number of rentals
Step 1: Understand the question
We need:
Category name
Number of rentals per category
Category with the highest rental count
Step 2: Identify the starting table
Since the required output is the category, the starting table is: category
Step 3: Follow the Relationship Path and Convert It into JOINs
Suppose the ER diagram shows the following relationship path:
category → film_category → film → inventory → rental
Relationships:
category.category_id = film_category.category_id
film_category.film_id = film.film_id
film.film_id = inventory.film_id
inventory.inventory_id = rental.inventory_id
Step 4: Apply aggregation
We need to:
Count rentals using COUNT()
Group results by category
Sort by rental count in descending order
Return the top result
Final Query:
SELECT c.name,
COUNT(r.rental_id) AS rental_count
FROM category c
JOIN film_category fc
ON c.category_id = fc.category_id
JOIN film f
ON fc.film_id = f.film_id
JOIN inventory i
ON f.film_id = i.film_id
JOIN rental r
ON i.inventory_id = r.inventory_id
GROUP BY c.name
ORDER BY rental_count DESC
LIMIT 1;
Why this works:
• Each rental is linked to an inventory item.
• Each inventory item belongs to a film.
• Each film is associated with a category through the film_category table.
• Grouping by category allows us to count the number of rentals for each category.
• Sorting the counts in descending order and limiting the result to one row returns the category with the highest number of rentals.
Note: In this case, film_category and inventory can also be joined directly using film_id, eliminating need to join the film table. However, when learning to read ER diagrams and understand relationships, it's helpful to follow the complete relationship path before applying such optimizations.
Common Mistakes Beginners Make
When solving SQL questions using ER diagrams, learners often:
Start with the wrong table instead of identifying the table that contains the required output.
Skip intermediate tables required to connect two tables.
Forget to use GROUP BY when working with aggregate functions.
Write joins before tracing the relationship path in the ER diagram.
Conclusion
ER diagrams provide a visual roadmap of how tables are connected within a database. By understanding the question, Identifying the starting table,tracing the relationship path and applying aggregation, filtering and grouping you can approach SQL problems in a structured and repeatable way.
With practice, this method becomes a powerful framework for analyzing database schemas and writing SQL queries. What once felt like guesswork becomes a systematic process to solve SQL queries with confidence.
