Cartesian product and joins in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Cartesian Product in SQL

What is the result of performing a Cartesian product (cross join) between two tables, each having 3 rows?

A A table with 6 rows

B A table with 9 rows

C A table with 3 rows

D A table with 0 rows

💡 Hint

Remember, Cartesian product pairs every row of the first table with every row of the second table.

Cartesian product multiplies the number of rows from both tables. So 3 rows × 3 rows = 9 rows.

📋 Factual

intermediate

Identifying Join Types

Which type of join returns only the rows where there is a match in both joined tables?

A INNER JOIN

B FULL OUTER JOIN

C RIGHT JOIN

D LEFT JOIN

💡 Hint

This join excludes rows without matching keys in either table.

INNER JOIN returns only rows with matching values in both tables.

🚀 Application

advanced

Predicting Output of a Join Query

Given two tables:
Table A has 2 rows with IDs 1 and 2.
Table B has 3 rows with IDs 2, 3, and 4.
What will be the number of rows returned by the query: SELECT * FROM A LEFT JOIN B ON A.ID = B.ID;?

A 5 rows

B 3 rows

C 2 rows

D 6 rows

💡 Hint

LEFT JOIN returns all rows from the left table, matching rows from the right, or NULL if no match.

Table A has 2 rows, so LEFT JOIN returns 2 rows. One row matches ID 2 in B, the other has NULLs for B columns.

🔍 Analysis

advanced

Analyzing Join Result Differences

What is the main difference between INNER JOIN and FULL OUTER JOIN results?

A INNER JOIN returns all rows from both tables, FULL OUTER JOIN returns only matching rows

B INNER JOIN and FULL OUTER JOIN return the same results

C INNER JOIN returns rows with NULLs, FULL OUTER JOIN excludes NULLs

D INNER JOIN returns only matching rows, FULL OUTER JOIN returns all rows from both tables with NULLs where no match

💡 Hint

Think about how unmatched rows are handled in each join type.

INNER JOIN returns only rows with matches in both tables. FULL OUTER JOIN returns all rows from both tables, filling NULLs where no match exists.

❓ Reasoning

expert

Determining Result Size of Complex Join

Consider two tables:
- Table X has 4 rows.
- Table Y has 5 rows.
If you perform a CROSS JOIN between X and Y, then INNER JOIN the result with a third table Z having 3 rows on a condition that matches exactly 2 rows from the CROSS JOIN result per row in Z, how many rows will the final result have?

A 6 rows

B 60 rows

C 20 rows

D 12 rows

💡 Hint

Calculate the size of the CROSS JOIN first, then multiply by the matching rows per Z row.

CROSS JOIN of X and Y produces 4 × 5 = 20 rows.
Each of the 3 rows in Z matches exactly 2 rows from this 20-row set.
So final rows = 3 × 2 = 6 rows.
The INNER JOIN filters to only these matching rows.