Joining on primary key to foreign key in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of INNER JOIN on primary key to foreign key

Given two tables Authors and Books:

Authors(author_id, name)
Books(book_id, title, author_id)

What is the output of this query?

SELECT Authors.name, Books.title
FROM Authors
INNER JOIN Books ON Authors.author_id = Books.author_id
ORDER BY Authors.author_id, Books.book_id;

SQL

CREATE TABLE Authors (author_id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE Books (book_id INT PRIMARY KEY, title VARCHAR(100), author_id INT, FOREIGN KEY (author_id) REFERENCES Authors(author_id));

INSERT INTO Authors VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO Books VALUES (101, 'Book A', 1), (102, 'Book B', 1), (103, 'Book C', 2);

A [{"name": "Alice", "title": "Book A"}, {"name": "Alice", "title": "Book B"}, {"name": "Bob", "title": "Book C"}]

B [{"name": "Alice", "title": "Book A"}, {"name": "Bob", "title": "Book B"}, {"name": "Bob", "title": "Book C"}]

C []

D [{"name": "Alice", "title": "Book A"}, {"name": "Alice", "title": "Book B"}]

💡 Hint

INNER JOIN returns rows where the join condition matches in both tables.

The INNER JOIN matches each author with their books by matching author_id. Alice has two books, Bob has one.

🧠 Conceptual

intermediate

Understanding LEFT JOIN with primary key to foreign key

What does a LEFT JOIN do when joining a primary key to a foreign key?

A Returns all rows from the left table and matching rows from the right table; if no match, right columns are NULL.

B Returns only rows from the left table that have no matching foreign key in the right table.

C Returns all rows from the right table and matching rows from the left table; if no match, left columns are NULL.

D Returns only rows where the primary key matches the foreign key in both tables.

💡 Hint

Think about which table's rows are always included in the result.

LEFT JOIN keeps all rows from the left table, adding NULLs for right table columns when no match exists.

📝 Syntax

advanced

Identify the syntax error in JOIN query

Which option contains a syntax error in joining Orders and Customers on primary key to foreign key?

SELECT Orders.order_id, Customers.name
FROM Orders
JOIN Customers ON Orders.customer_id = Customers.customer_id;

A SELECT Orders.order_id, Customers.name FROM Orders LEFT JOIN Customers ON Orders.customer_id = Customers.customer_id;

B SELECT Orders.order_id, Customers.name FROM Orders JOIN Customers ON Orders.customer_id = Customers.customer_id;

C SELECT Orders.order_id, Customers.name FROM Orders INNER JOIN Customers ON Orders.customer_id = Customers.customer_id;

D SELECT Orders.order_id, Customers.name FROM Orders JOIN Customers WHERE Orders.customer_id = Customers.customer_id;

💡 Hint

Check the placement of the join condition.

JOIN requires the ON clause to specify the join condition; using WHERE instead causes syntax error.

❓ optimization

advanced

Optimizing JOIN performance on primary key to foreign key

Which option best improves performance when joining large tables on primary key to foreign key?

A Avoid using indexes to speed up sequential scans.

B Use SELECT * to retrieve all columns to avoid missing data.

C Ensure both primary key and foreign key columns are indexed.

D Join tables without specifying ON clause to let the database decide.

💡 Hint

Indexes help the database find matching rows faster.

Indexes on join columns allow the database to quickly locate matching rows, improving join speed.

🔧 Debug

expert

Why does this JOIN return fewer rows than expected?

Given tables Students and Enrollments with a primary key to foreign key relationship, this query returns fewer rows than expected:

SELECT Students.student_id, Enrollments.course_id
FROM Students
JOIN Enrollments ON Students.student_id = Enrollments.student_id
WHERE Enrollments.course_id IS NOT NULL;

What is the most likely reason?

A The JOIN condition is incorrect; it should be ON Enrollments.student_id = Students.student_id.

B Some students have no enrollments, so INNER JOIN excludes them.

C The WHERE clause filters out rows where course_id is NULL, but course_id is never NULL.

D The Students table has duplicate student_id values causing row loss.

💡 Hint

Think about what INNER JOIN does when no matching rows exist.

INNER JOIN only returns rows with matching keys in both tables. Students without enrollments are excluded.