Relational database concepts in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of JOIN with NULL values

Consider two tables Employees and Departments. Employees may or may not belong to a department. What is the output of this query?

SELECT e.name, d.department_name FROM Employees e LEFT JOIN Departments d ON e.department_id = d.id ORDER BY e.name;

MySQL

CREATE TABLE Employees (id INT, name VARCHAR(20), department_id INT);
INSERT INTO Employees VALUES (1, 'Alice', 10), (2, 'Bob', NULL), (3, 'Charlie', 20);
CREATE TABLE Departments (id INT, department_name VARCHAR(20));
INSERT INTO Departments VALUES (10, 'HR'), (20, 'IT');

SELECT e.name, d.department_name FROM Employees e LEFT JOIN Departments d ON e.department_id = d.id ORDER BY e.name;

A [{"name": "Alice", "department_name": "HR"}, {"name": "Charlie", "department_name": "IT"}]

B [{"name": "Alice", "department_name": "HR"}, {"name": "Bob", "department_name": ""}, {"name": "Charlie", "department_name": "IT"}]

C [{"name": "Alice", "department_name": null}, {"name": "Bob", "department_name": null}, {"name": "Charlie", "department_name": null}]

D [{"name": "Alice", "department_name": "HR"}, {"name": "Bob", "department_name": null}, {"name": "Charlie", "department_name": "IT"}]

💡 Hint

LEFT JOIN keeps all rows from the left table even if no matching row in the right table.

The LEFT JOIN returns all employees. For Bob, who has no department_id, the department_name is NULL.

🧠 Conceptual

intermediate

Understanding Primary Key Constraints

Which statement best describes the role of a primary key in a relational database table?

A It uniquely identifies each row and cannot contain NULL values.

B It allows duplicate values but must be indexed for faster queries.

C It is used to store large text data in the table.

D It automatically encrypts the data in the table.

💡 Hint

Think about what makes each row unique and how databases enforce uniqueness.

A primary key uniquely identifies each row and does not allow NULLs to ensure every record is distinct.

📝 Syntax

advanced

Identify the syntax error in the SQL query

Which option contains a syntax error when creating a table with a foreign key constraint?

MySQL

CREATE TABLE Orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES Customers(id)
);

A CREATE TABLE Orders (order_id INT PRIMARY KEY, customer_id INT, FOREIGN KEY customer_id REFERENCES Customers(id));

B CREATE TABLE Orders (order_id INT PRIMARY KEY, customer_id INT, FOREIGN KEY (customer_id) REFERENCES Customers(id));

C ;))di(sremotsuC SECNEREFER )di_remotsuc( YEK NGIEROF ,TNI di_remotsuc ,YEK YRAMIRP TNI di_redro( sredrO ELBAT ETAERC

D CREATE TABLE Orders (order_id INT PRIMARY KEY, customer_id INT, FOREIGN KEY (customer_id) REFERENCES Customers(id) ON DELETE CASCADE);

💡 Hint

Check the syntax for declaring foreign keys in SQL.

Option A misses parentheses around the foreign key column name, causing a syntax error.

❓ optimization

advanced

Optimizing a query with multiple JOINs

Given three tables: Customers, Orders, and Products, which query is optimized to retrieve customer names and product names for all orders without unnecessary data duplication?

A SELECT c.name, p.product_name FROM Customers c, Orders o, Products p WHERE c.id = o.customer_id AND o.product_id = p.id;

B SELECT DISTINCT c.name, p.product_name FROM Customers c JOIN Orders o ON c.id = o.customer_id JOIN Products p ON o.product_id = p.id;

C SELECT c.name, p.product_name FROM Customers c LEFT JOIN Orders o ON c.id = o.customer_id LEFT JOIN Products p ON o.product_id = p.id;

D SELECT c.name, p.product_name FROM Customers c JOIN Orders o ON c.id = o.customer_id JOIN Products p ON o.product_id = p.id;

💡 Hint

Consider how to avoid duplicate rows when joining multiple tables.

Using DISTINCT removes duplicate rows caused by multiple orders of the same product by the same customer.

🔧 Debug

expert

Debugging a foreign key constraint failure

You try to insert a row into the Orders table with customer_id = 5, but get a foreign key constraint error. Which is the most likely cause?

A The Orders table does not have a primary key defined.

B The customer_id column in Orders allows NULL values.

C There is no customer with id = 5 in the Customers table.

D The Products table is missing a foreign key constraint.

💡 Hint

Foreign key constraints require the referenced value to exist in the parent table.

A foreign key error on insert usually means the referenced key does not exist in the parent table.