Why table design matters in SQL - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

Effect of missing primary key on query results

Consider a table Employees without a primary key. What will be the result of this query?

SELECT EmployeeID, COUNT(*) FROM Employees GROUP BY EmployeeID;

Assuming EmployeeID is not unique in the table.

SQL

CREATE TABLE Employees (EmployeeID INT, Name VARCHAR(100));
INSERT INTO Employees VALUES (1, 'Alice'), (1, 'Alice'), (2, 'Bob');
SELECT EmployeeID, COUNT(*) FROM Employees GROUP BY EmployeeID;

A Empty result set

B EmployeeID 1 with count 1, EmployeeID 2 with count 1

C Error due to missing primary key

D EmployeeID 1 with count 2, EmployeeID 2 with count 1

💡 Hint

Think about how GROUP BY works with duplicate rows.

Without a primary key, duplicate EmployeeID values can exist. GROUP BY counts all rows per EmployeeID.

🧠 Conceptual

intermediate

Why normalization improves data integrity

Which of the following best explains why normalization is important in table design?

A It reduces data duplication and prevents update anomalies

B It makes queries run slower but uses less disk space

C It allows storing multiple data types in one column

D It automatically creates indexes on all columns

💡 Hint

Think about what happens when the same data is stored in many places.

Normalization organizes data to reduce repetition and avoid inconsistencies when updating data.

📝 Syntax

advanced

Identify the correct SQL to add a foreign key

Which SQL statement correctly adds a foreign key constraint to the Orders table referencing Customers?

SQL

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

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

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

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

D ALTER TABLE Orders ADD CONSTRAINT fk_customer FOREIGN KEY (CustomerID) Customers(CustomerID);

💡 Hint

Check the syntax for adding constraints with parentheses.

The correct syntax requires parentheses around the column name and the REFERENCES clause.

❓ optimization

advanced

Choosing indexes for faster queries

You have a large Sales table with columns SaleID, ProductID, and SaleDate. Which index will speed up this query?

SELECT * FROM Sales WHERE ProductID = 101 AND SaleDate = '2024-01-01';

A CREATE INDEX idx_product ON Sales(ProductID);

B CREATE INDEX idx_product_date ON Sales(ProductID, SaleDate);

C CREATE INDEX idx_date ON Sales(SaleDate);

D CREATE INDEX idx_date_product ON Sales(SaleDate, ProductID);

💡 Hint

Think about which columns appear together in the WHERE clause and their order.

A composite index on (ProductID, SaleDate) matches the query filter and speeds up lookups.

🔧 Debug

expert

Diagnose data inconsistency from poor table design

A table Inventory stores product quantities but allows duplicate ProductID rows. What problem can this cause?

A The database will reject inserts with duplicate ProductID

B Queries will always return zero rows

C Total quantity calculations may be incorrect due to duplicates

D The table will automatically merge duplicate rows

💡 Hint

Think about what happens when the same product appears multiple times with quantities.

Duplicates cause sums or counts to be wrong because the same product is counted multiple times.