Denormalization and when to use it in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why choose denormalization in a database?

Which of the following is the main reason to use denormalization in a database design?

A To reduce the number of joins and improve read query performance

B To eliminate data redundancy completely

C To enforce strict data integrity through foreign keys

D To make the database schema more complex and normalized

💡 Hint

Think about what happens when you want faster data retrieval at the cost of some redundancy.

Denormalization is used mainly to reduce the number of joins in queries, which improves read performance. It intentionally introduces some redundancy.

❓ query_result

intermediate

Output of a denormalized query

Given these two tables:

Customers(customer_id, name)
Orders(order_id, customer_id, order_date)

We create a denormalized table:
CustomerOrders(customer_id, name, order_id, order_date)

What will be the output of:

SELECT customer_id, name, COUNT(order_id) AS order_count FROM CustomerOrders GROUP BY customer_id, name;

A An error because of missing GROUP BY columns

B A list of customers with the count of their orders

C A list of orders with duplicated customer names

D A list of customers with total order amounts

💡 Hint

Grouping by customer_id and name counts orders per customer.

The query groups by customer and counts orders, showing how denormalized data can simplify queries.

📝 Syntax

advanced

Identify the error in denormalized table update

Consider a denormalized table storing product info and category name:

Products(product_id, product_name, category_id, category_name)

Which SQL statement will cause an error when updating category_name for all products in category 5?

SQL

UPDATE Products SET category_name = 'New Category' WHERE category_id = 5;

A UPDATE Products SET category_name = 'New Category' WHERE category_name = 5;

B UPDATE Products SET category_name = 'New Category' WHERE product_id = 5;

C UPDATE Products SET category_name = 'New Category';

D UPDATE Products SET category_name = 'New Category' WHERE category_id = 5;

💡 Hint

Check the data types in the WHERE clause.

Option A compares category_name (a string) to 5 (a number), causing a type mismatch error or no rows updated.

❓ optimization

advanced

Optimizing read performance with denormalization

You have a normalized database with tables for Users, Orders, and Products. You want to speed up a report that shows user names, order dates, and product names. Which denormalization strategy will best improve read speed?

A Add indexes on foreign keys in all tables

B Remove all foreign keys to speed up inserts

C Create a denormalized table combining user name, order date, and product name

D Split the Orders table into smaller tables by date

💡 Hint

Denormalization duplicates data to avoid joins in queries.

Combining related data into one table reduces joins and speeds up reads, which is the goal of denormalization.

🔧 Debug

expert

Troubleshooting stale data in denormalized tables

You have a denormalized table storing customer info and their latest order date. After updating the Orders table, the denormalized table shows old order dates. What is the most likely cause?

A The Orders table has a foreign key constraint preventing updates

B The database engine does not support denormalization

C The denormalized table has a syntax error in its definition

D The denormalized table is not updated automatically after Orders change

💡 Hint

Denormalized data must be refreshed or updated manually or via triggers.

Denormalized tables duplicate data and require explicit updates or triggers to stay current.