One-to-one relationship design in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a one-to-one join query

Given two tables Users and Profiles with a one-to-one relationship on user_id, what is the output of the following query?

SELECT u.user_id, u.name, p.bio FROM Users u JOIN Profiles p ON u.user_id = p.user_id ORDER BY u.user_id;

Tables:

Users
user_id | name
1 | Alice
2 | Bob
3 | Carol

Profiles
user_id | bio
1 | Loves cats
3 | Enjoys hiking

SQL

SELECT u.user_id, u.name, p.bio FROM Users u JOIN Profiles p ON u.user_id = p.user_id ORDER BY u.user_id;

A

1, Alice, Loves cats
2, Bob, NULL
3, Carol, Enjoys hiking

B

1, Alice, NULL
3, Carol, Enjoys hiking

C

1, Alice, Loves cats
2, Bob, 
3, Carol, Enjoys hiking

D

1, Alice, Loves cats
3, Carol, Enjoys hiking

💡 Hint

Remember that an INNER JOIN only returns rows with matching keys in both tables.

The JOIN returns only users who have matching profiles. Bob has no profile, so he is excluded.

🧠 Conceptual

intermediate

Identifying one-to-one relationship design

Which of the following best describes a one-to-one relationship in database design?

A Each row in Table A matches one or more rows in Table B

B Each row in Table A matches zero or more rows in Table B

C Each row in Table A matches exactly one row in Table B, and vice versa

D Each row in Table A matches zero or one row in Table B, but Table B can have many matches

💡 Hint

Think about the meaning of 'one-to-one' literally.

A one-to-one relationship means each row in one table corresponds to exactly one row in the other table, and vice versa.

📝 Syntax

advanced

Correct syntax for creating one-to-one tables

Which SQL statement correctly creates two tables with a one-to-one relationship using a foreign key?

A

CREATE TABLE Users (user_id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE Profiles (profile_id INT PRIMARY KEY, user_id INT, bio TEXT, FOREIGN KEY (user_id) REFERENCES Users(user_id));

B

CREATE TABLE Users (user_id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE Profiles (profile_id INT PRIMARY KEY, user_id INT UNIQUE, bio TEXT, FOREIGN KEY (user_id) REFERENCES Users(user_id));

C

CREATE TABLE Users (user_id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE Profiles (profile_id INT PRIMARY KEY, user_id INT NOT NULL, bio TEXT);

D

CREATE TABLE Users (user_id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE Profiles (profile_id INT PRIMARY KEY, user_id INT UNIQUE NOT NULL, bio TEXT);

💡 Hint

One-to-one requires the foreign key to be unique in the child table.

Option B defines user_id in Profiles as UNIQUE and a foreign key referencing Users, enforcing one-to-one.

❓ optimization

advanced

Optimizing queries on one-to-one tables

You have two tables Employees and EmployeeDetails in a one-to-one relationship. Which query is the most efficient to get all employees with their details?

A SELECT * FROM Employees e JOIN EmployeeDetails d ON e.emp_id = d.emp_id;

B SELECT * FROM Employees e RIGHT JOIN EmployeeDetails d ON e.emp_id = d.emp_id;

C SELECT * FROM Employees e, EmployeeDetails d WHERE e.emp_id = d.emp_id;

D SELECT * FROM Employees e LEFT JOIN EmployeeDetails d ON e.emp_id = d.emp_id;

💡 Hint

Consider that every employee has exactly one detail record.

Since the relationship is one-to-one and every employee has details, an INNER JOIN (option A) is most efficient and clear.

🔧 Debug

expert

Debugging one-to-one relationship constraint violation

Given these tables:

CREATE TABLE Customers (customer_id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE CustomerProfiles (profile_id INT PRIMARY KEY, customer_id INT UNIQUE, details TEXT, FOREIGN KEY (customer_id) REFERENCES Customers(customer_id));

When inserting this data:

INSERT INTO Customers VALUES (1, 'John');
INSERT INTO CustomerProfiles VALUES (10, 1, 'Likes sports');
INSERT INTO CustomerProfiles VALUES (11, 1, 'Likes music');

What error will the last insert cause?

A Unique constraint violation on customer_id

B Primary key violation on profile_id

C Foreign key constraint violation

D No error, insert succeeds

💡 Hint

Think about the UNIQUE constraint on customer_id in CustomerProfiles.

The second insert tries to add a row with the same customer_id 1, violating the UNIQUE constraint that enforces one-to-one.