Database design best practices in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why use normalization in database design?

Which of the following best explains the main purpose of normalization in database design?

A To increase the number of tables for faster queries

B To store all data in a single table for simplicity

C To reduce data redundancy and improve data integrity

D To avoid using primary keys in tables

💡 Hint

Think about how normalization helps keep data consistent and organized.

Normalization organizes data to reduce duplication and ensures data integrity by structuring tables properly.

❓ query_result

intermediate

Identify the correct foreign key relationship

Given two tables, Orders and Customers, which SQL statement correctly creates a foreign key from Orders to Customers?

SQL

CREATE TABLE Customers (
  CustomerID INT PRIMARY KEY,
  Name VARCHAR(100)
);

CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  CustomerID INT,
  OrderDate DATE
);

A ALTER TABLE Orders ADD FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

B ALTER TABLE Customers ADD FOREIGN KEY (CustomerID) REFERENCES Orders(CustomerID);

C ALTER TABLE Orders ADD FOREIGN KEY (OrderID) REFERENCES Customers(CustomerID);

D ALTER TABLE Orders ADD FOREIGN KEY (CustomerID) REFERENCES Orders(OrderID);

💡 Hint

The foreign key should link the child table to the parent table's primary key.

The foreign key in Orders references the primary key CustomerID in Customers to link orders to customers.

📝 Syntax

advanced

Find the syntax error in table creation

Which option contains a syntax error when creating a table with a primary key and a unique constraint?

SQL

CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  Email VARCHAR(255) UNIQUE
);

A CREATE TABLE Employees (EmployeeID INT PRIMARY KEY, Email VARCHAR(255) UNIQUE);

B CREATE TABLE Employees (EmployeeID INT PRIMARY KEY, Email VARCHAR(255) UNIQUE NOT NULL);

C CREATE TABLE Employees (EmployeeID INT PRIMARY KEY, Email VARCHAR(255), UNIQUE (Email));

D CREATE TABLE Employees (EmployeeID INT PRIMARY KEY, Email VARCHAR(255) UNIQUE CONSTRAINT);

💡 Hint

Check the placement and usage of the UNIQUE keyword and CONSTRAINT keyword.

Option D incorrectly uses UNIQUE CONSTRAINT together without proper syntax, causing a syntax error.

❓ optimization

advanced

Choose the best indexing strategy

You have a large table with millions of rows and frequent queries filtering by the column last_name. Which indexing strategy improves query speed without wasting much space?

A Create a B-tree index on the last_name column

B Create a full-text index on the last_name column

C Create a unique index on the last_name column

D Create no index and rely on sequential scans

💡 Hint

Think about the type of queries and the best index type for filtering by a column.

B-tree indexes are efficient for filtering and sorting on columns like last_name, improving query speed.

🔧 Debug

expert

Identify the cause of data inconsistency

A database has two tables: Products and Orders. Sometimes, orders reference product IDs that do not exist in Products. What is the most likely cause?

A The Orders table has a primary key on product ID

B The foreign key constraint between Orders and Products is missing or disabled

C The Products table uses a composite primary key

D The primary key in Products is not unique

💡 Hint

Think about what enforces valid references between tables.

Without a foreign key constraint, the database does not enforce that product IDs in Orders must exist in Products, causing inconsistency.