Denormalization and when to use it in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all columns from the denormalized table.

SQL

SELECT [1] FROM sales_summary;

Drag options to blanks, or click blank then click option'

ACOLUMNS

BALL

C*

DEVERYTHING

Attempts:

3 left

💡 Hint

Common Mistakes

Using keywords like ALL or COLUMNS which are not valid in SELECT.

Trying to write out all column names manually.

Using * selects all columns from the table.

2fill in blank

medium

Complete the code to add a denormalized column that stores total sales.

SQL

ALTER TABLE sales_summary ADD COLUMN total_sales [1];

Drag options to blanks, or click blank then click option'

ADECIMAL(10,2)

BTEXT

CVARCHAR(255)

DDATE

Attempts:

3 left

💡 Hint

Common Mistakes

Using VARCHAR or TEXT which store strings, not numbers.

Using DATE which is for dates, not numbers.

The DECIMAL(10,2) type is used for precise numbers with two decimal places, suitable for money.

3fill in blank

hard

Fix the error in the query that tries to join normalized tables but misses the join condition.

SQL

SELECT orders.order_id, customers.name FROM orders [1] customers;

Drag options to blanks, or click blank then click option'

AINNER JOIN

BINNER JOIN ON

CJOIN

DLEFT JOIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using JOIN without specifying ON clause causes error.

Using INNER JOIN ON without the ON condition is invalid syntax.

The INNER JOIN keyword joins tables but requires an ON clause separately. The code is missing the ON clause, but the question asks to fix the join keyword only.

4fill in blank

hard

Fill both blanks to create a denormalized table that stores customer name and total orders.

SQL

CREATE TABLE customer_orders AS SELECT customers.name AS [1], SUM(orders.amount) AS [2] FROM customers JOIN orders ON customers.id = orders.customer_id GROUP BY customers.name;

Drag options to blanks, or click blank then click option'

Acustomer_name

Btotal_amount

Corder_count

Dcustomer_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using ambiguous or incorrect aliases that confuse the data meaning.

Using customer_id instead of customer_name for the name column.

The alias customer_name clearly labels the customer's name, and total_amount represents the sum of orders, which fits the denormalized data purpose.

5fill in blank

hard

Fill all three blanks to write a query that selects denormalized data with a condition on total sales.

SQL

SELECT [1], [2] FROM sales_summary WHERE [3] > 1000;

Drag options to blanks, or click blank then click option'

Acustomer_name

Btotal_sales

Dorder_count

Attempts:

3 left

💡 Hint

Common Mistakes

Using order_count in the WHERE clause instead of total_sales.

Selecting columns that do not exist in the denormalized table.

Select the customer name and total sales columns, and filter where total sales is greater than 1000.

Practice

1. What is the main purpose of denormalization in a database?

easy

A. To split data into many small tables

B. To remove all duplicate data for storage efficiency

C. To enforce strict data integrity rules

D. To add redundant data to speed up read operations

Solution

1. Step 1: Understand denormalization goal and compare options

   Denormalization means adding repeated data to reduce complex joins and speed up reading. Only "To add redundant data to speed up read operations" describes adding redundant data to improve read speed, which matches denormalization.

2. Final Answer:

   To add redundant data to speed up read operations -> Option D

3. Quick Check:

   Denormalization = add redundant data for faster reads [OK]

Hint: Denormalization means adding data to read faster, not removing it [OK]

Common Mistakes:

• Confusing denormalization with normalization
• Thinking denormalization removes duplicates
• Believing denormalization enforces stricter rules

2. Which of the following best represents a denormalized table structure?

easy

A. Separate tables for customers and orders linked by customer_id

B. A table with repeated customer address fields in each order record

C. A table with only unique keys and no repeated data

D. A table with foreign keys but no repeated columns

Solution

1. Step 1: Identify denormalized structure and match to options

   Denormalized tables store repeated data, like customer address repeated in each order. "A table with repeated customer address fields in each order record" shows repeated fields in one table, which is denormalization. Others show normalized forms.

2. Final Answer:

   A table with repeated customer address fields in each order record -> Option B

3. Quick Check:

   Repeated fields in one table = denormalized [OK]

Hint: Look for repeated fields in one table to spot denormalization [OK]

Common Mistakes:

• Choosing normalized table structures
• Confusing foreign keys with denormalization
• Ignoring repeated data presence

3. Given a denormalized table Orders with columns OrderID, CustomerName, and CustomerAddress repeated in every row, what is a likely effect when updating a customer's address?

medium

A. You must update the address in every order row for that customer

B. Updating one row automatically updates all related rows

C. No update is needed because address is stored separately

D. The database prevents duplicate addresses

Solution

1. Step 1: Understand denormalized update effects and analyze options

   In denormalized tables, repeated data means updates must be applied to all copies to keep consistency. Only "You must update the address in every order row for that customer" correctly states that all rows must be updated manually.

2. Final Answer:

   You must update the address in every order row for that customer -> Option A

3. Quick Check:

   Repeated data requires multiple updates [OK]

Hint: Repeated data means multiple updates needed for consistency [OK]

Common Mistakes:

• Assuming one update changes all rows automatically
• Thinking address is stored separately in denormalized tables
• Believing database prevents duplicates in denormalized data

4. You have a denormalized table where customer phone numbers are repeated in many rows. You run this update:

UPDATE Orders SET CustomerPhone = '123-4567' WHERE CustomerID = 5;

but some rows still show old phone numbers. What is the most likely problem?

medium

A. The WHERE clause did not match all rows for CustomerID 5

B. Denormalization prevents updates to repeated data

C. CustomerPhone is a computed column and cannot be updated

D. The database automatically updates all rows, so this is impossible

Solution

1. Step 1: Analyze update query symptom and identify cause

   The update targets rows with CustomerID = 5, but some rows still have old data, meaning some rows were missed. Most likely, some rows have incorrect or missing CustomerID values, so the WHERE clause did not match them.

2. Final Answer:

   The WHERE clause did not match all rows for CustomerID 5 -> Option A

3. Quick Check:

   Update misses rows if WHERE condition is incomplete [OK]

Hint: Check WHERE clause matches all repeated rows in denormalized tables [OK]

Common Mistakes:

• Thinking denormalization blocks updates
• Assuming computed columns without checking
• Believing database auto-updates all duplicates

5. You want to speed up reporting queries that join Orders and Customers tables frequently. Which denormalization approach is best to improve read speed while managing data consistency?

hard

A. Remove customer details from Orders and query Customers each time

B. Keep separate tables and add more indexes on join keys

C. Add customer name and address columns directly into the Orders table

D. Duplicate Orders table for each customer

Solution

1. Step 1: Identify denormalization for faster reads and evaluate options

   Adding customer info directly into Orders reduces joins, speeding up queries. "Add customer name and address columns directly into the Orders table" adds repeated data for speed and requires careful updates, fitting denormalization best.

2. Final Answer:

   Add customer name and address columns directly into the Orders table -> Option C

3. Quick Check:

   Denormalize by adding repeated data to reduce joins [OK]

Hint: Denormalize by adding repeated fields to reduce joins for faster reads [OK]

Common Mistakes:

• Thinking indexing replaces denormalization
• Removing data increases joins and slows queries
• Duplicating entire tables causes complexity