Joins in SQL in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding INNER JOIN behavior

Given two tables, Employees and Departments, what will be the result of an INNER JOIN on the department ID if some employees do not belong to any department?

DBMS Theory

SELECT Employees.name, Departments.name FROM Employees INNER JOIN Departments ON Employees.dept_id = Departments.id;

A Only departments with employees will appear, excluding employees without departments.

B Only employees who have a matching department will appear in the result.

C All employees will appear, with NULL for department names if no match exists.

D All departments will appear, with NULL for employee names if no match exists.

💡 Hint

Think about what INNER JOIN does when there is no matching record in the other table.

INNER JOIN returns only rows where there is a match in both tables. Employees without a department will be excluded.

📋 Factual

intermediate

Identifying LEFT JOIN output

What does a LEFT JOIN return when joining Customers with Orders on customer ID, if some customers have no orders?

DBMS Theory

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

A All orders appear; orders without customers show NULL for customer name.

B Only customers with orders appear; others are excluded.

C All customers appear; customers without orders show NULL for order_id.

D Only customers without orders appear.

💡 Hint

LEFT JOIN keeps all rows from the left table regardless of matches.

LEFT JOIN returns all rows from the left table (Customers). If no matching order exists, order columns are NULL.

🔍 Analysis

advanced

Determining output of FULL OUTER JOIN

Consider two tables: Authors and Books. What will a FULL OUTER JOIN on author ID return if some authors have no books and some books have no authors?

DBMS Theory

SELECT Authors.name, Books.title FROM Authors FULL OUTER JOIN Books ON Authors.id = Books.author_id;

A All authors and all books appear; unmatched authors or books show NULL in the other column.

B Only authors with books appear; unmatched authors or books are excluded.

C Only books with authors appear; unmatched authors or books are excluded.

D Only authors without books appear.

💡 Hint

FULL OUTER JOIN combines LEFT and RIGHT JOIN results.

FULL OUTER JOIN returns all rows from both tables. Where no match exists, NULL fills the missing side.

❓ Comparison

advanced

Comparing CROSS JOIN and INNER JOIN

What is the main difference between a CROSS JOIN and an INNER JOIN when joining two tables without any ON condition?

DBMS Theory

SELECT * FROM TableA CROSS JOIN TableB;
SELECT * FROM TableA INNER JOIN TableB;

A CROSS JOIN returns the Cartesian product; INNER JOIN without ON is invalid syntax.

B Both return the Cartesian product of the two tables.

C INNER JOIN returns only matching rows; CROSS JOIN returns all rows from the first table.

D INNER JOIN returns all rows; CROSS JOIN returns only matching rows.

💡 Hint

Think about the requirement of ON clause in INNER JOIN.

INNER JOIN requires an ON condition to match rows. Without it, the query is invalid. CROSS JOIN returns all combinations.

❓ Reasoning

expert

Predicting number of rows after multiple joins

Given three tables: Students (10 rows), Enrollments (15 rows), and Courses (5 rows). Enrollments links Students and Courses by IDs. If you perform an INNER JOIN of Students to Enrollments, then INNER JOIN the result to Courses, how many rows will the final result have?

DBMS Theory

SELECT Students.name, Courses.title FROM Students INNER JOIN Enrollments ON Students.id = Enrollments.student_id INNER JOIN Courses ON Enrollments.course_id = Courses.id;

A 5 rows, equal to the number of courses.

B 10 rows, equal to the number of students.

C 30 rows, product of students and courses.

D 15 rows, equal to the number of enrollments.

💡 Hint

Enrollments represent each student-course pair; joins filter to matching pairs.

The join chains filter to enrollments linking students and courses. The final row count matches enrollments (15).