Why normalization matters in SQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all columns from the table named 'employees'.

SQL

SELECT [1] FROM employees;

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

Aemployees

BALL

C*

Dcolumns

Attempts:

3 left

💡 Hint

Common Mistakes

Using the table name instead of '*' to select all columns.

Using 'ALL' which is not valid in this context.

The asterisk (*) selects all columns from the table.

2fill in blank

medium

Complete the code to find duplicate employee names in the 'employees' table.

SQL

SELECT name, COUNT(*) FROM employees GROUP BY name HAVING COUNT(*) [1] 1;

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

A=

B>

C<=

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' which only finds names appearing exactly once.

Using '<=' which includes names appearing once or less.

We use '>' to find names that appear more than once, indicating duplicates.

3fill in blank

hard

Fix the error in the query to select unique department names from 'employees'.

SQL

SELECT DISTINCT [1] FROM employees;

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

Adepartment-name

Bdepartment name

CdepartmentName

Ddepartment_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using column names with spaces or dashes without quotes.

Using camelCase when the column uses underscores.

Column names cannot have spaces or dashes unless quoted. 'department_id' is a valid column name.

4fill in blank

hard

Fill both blanks to create a query that lists employee names and their department names by joining 'employees' and 'departments'.

SQL

SELECT employees.name, departments.[1] FROM employees JOIN departments ON employees.[2] = departments.id;

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

Aname

Bdepartment_id

Cid

Ddept_name

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong column names for join or select.

Mixing up foreign key and primary key columns.

The department name column is 'dept_name'. The join uses 'department_id' from employees to match 'id' in departments.

5fill in blank

hard

Fill all three blanks to write a query that counts employees per department, showing department name and count, only for departments with more than 5 employees.

SQL

SELECT departments.[1], COUNT(employees.[2]) FROM employees JOIN departments ON employees.[3] = departments.id GROUP BY departments.[1] HAVING COUNT(employees.[2]) > 5;

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

Adept_name

Bid

Cdepartment_id

Dname

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong columns for counting or joining.

Not grouping by department name.

We select 'dept_name' from departments, count employee 'id's, and join on 'department_id'.

Practice

1. Why is normalization important in a database?

easy

A. It automatically creates backups of the database.

B. It makes the database run faster by using more memory.

C. It allows storing data in one big table without keys.

D. It reduces data duplication and improves data integrity.

Solution

1. Step 1: Understand what normalization does

   Normalization organizes data to reduce repetition and avoid errors.

2. Step 2: Identify the main benefit

   Reducing duplication helps keep data accurate and easier to update.

3. Final Answer:

   It reduces data duplication and improves data integrity. -> Option D

4. Quick Check:

   Normalization = less duplication [OK]

Hint: Normalization means less repeated data and fewer errors [OK]

Common Mistakes:

• Thinking normalization speeds up all queries
• Believing normalization stores everything in one table
• Confusing normalization with backup processes

2. Which of the following is the correct way to represent a normalized table relationship?

easy

A. Separate tables for customers and orders linked by a customer ID.

B. One table with repeated customer addresses for each order.

C. All data combined in a single table without keys.

D. Storing all orders in a text file outside the database.

Solution

1. Step 1: Recall normalization structure

   Normalization splits data into related tables connected by keys.

2. Step 2: Identify correct table design

   Separate tables for customers and orders linked by customer ID avoid duplication.

3. Final Answer:

   Separate tables for customers and orders linked by a customer ID. -> Option A

4. Quick Check:

   Normalized tables use keys to link data [OK]

Hint: Normalized tables use keys to connect related data [OK]

Common Mistakes:

• Repeating data in one big table
• Ignoring foreign keys between tables
• Storing data outside the database

3. Given two tables:
Customers(customer_id, name, address)
Orders(order_id, customer_id, product)
What happens if you update a customer's address in the Customers table?

medium

A. The address updates in all related orders automatically.

B. The address in Orders table also changes because it stores addresses.

C. Only the address in the Customers table changes; orders remain linked by customer_id.

D. The database throws an error because address is duplicated.

Solution

1. Step 1: Understand table roles

   Customers table stores addresses; Orders table links by customer_id only.

2. Step 2: Effect of updating address

   Updating address changes it only in Customers; Orders remain linked by customer_id without address duplication.

3. Final Answer:

   Only the address in the Customers table changes; orders remain linked by customer_id. -> Option C

4. Quick Check:

   Normalized data updates in one place [OK]

Hint: Update data in one table; linked tables stay consistent [OK]

Common Mistakes:

• Assuming address is stored in Orders table
• Thinking all related tables update automatically
• Believing address duplication causes errors

4. You have a table storing customer orders with repeated customer names and addresses. What is the main problem with this design?

medium

A. It improves query speed by avoiding joins.

B. It wastes space and risks inconsistent data.

C. It ensures data is always backed up.

D. It automatically normalizes data.

Solution

1. Step 1: Identify issue with repeated data

   Repeating customer info wastes space and can cause inconsistent updates.

2. Step 2: Understand normalization benefit

   Normalization avoids repetition, saving space and keeping data consistent.

3. Final Answer:

   It wastes space and risks inconsistent data. -> Option B

4. Quick Check:

   Repeated data = waste and errors [OK]

Hint: Repeated data wastes space and causes errors [OK]

Common Mistakes:

• Thinking repeated data speeds up queries
• Confusing normalization with backup
• Believing database auto-fixes repetition

5. You have a table with columns: OrderID, CustomerName, CustomerAddress, Product. To normalize it, which step should you take?

hard

A. Split into two tables: Customers (CustomerID, CustomerName, CustomerAddress) and Orders (OrderID, Product, CustomerID).

B. Add more columns to store multiple addresses per customer.

C. Combine all data into one table without keys.

D. Store customer addresses as a comma-separated list in Orders.

Solution

1. Step 1: Identify repeated data

   CustomerName and CustomerAddress repeat for each order, causing duplication.

2. Step 2: Apply normalization

   Split data into Customers table (with unique customer info) and Orders table linked by CustomerID.

3. Final Answer:

   Split into two tables: Customers (CustomerID, CustomerName, CustomerAddress) and Orders (OrderID, Product, CustomerID). -> Option A

4. Quick Check:

   Normalization splits repeated data into related tables [OK]

Hint: Split repeated data into separate tables linked by keys [OK]

Common Mistakes:

• Adding columns instead of splitting tables
• Storing multiple addresses in one field
• Ignoring keys to link tables