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.

Practice

1. What does a Cartesian product between two tables produce?

easy

A. Only rows with matching values in both tables

B. All possible pairs of rows from both tables

C. Only rows from the first table

D. Only rows from the second table

Solution

1. Step 1: Understand Cartesian product definition

   A Cartesian product combines every row of the first table with every row of the second table.

2. Step 2: Identify the result of combining rows

   This means the total rows in the result equal the number of rows in the first table multiplied by the number of rows in the second table.

3. Final Answer:

   All possible pairs of rows from both tables -> Option B

4. Quick Check:

   Cartesian product = all pairs [OK]

Hint: Cartesian product pairs every row with every other row [OK]

Common Mistakes:

• Confusing Cartesian product with INNER JOIN
• Thinking it returns only matching rows
• Assuming it returns rows from only one table

2. Which SQL keyword is used to combine rows from two tables based on a matching condition?

easy

A. GROUP BY

B. CROSS JOIN

C. UNION

D. INNER JOIN

Solution

1. Step 1: Identify join types

   INNER JOIN combines rows from two tables where a specified condition matches.

2. Step 2: Match keyword to function

   CROSS JOIN produces Cartesian product, UNION combines result sets vertically, GROUP BY groups rows. Only INNER JOIN matches rows based on condition.

3. Final Answer:

   INNER JOIN -> Option D

4. Quick Check:

   Matching rows use INNER JOIN [OK]

Hint: INNER JOIN returns only matching rows [OK]

Common Mistakes:

• Choosing CROSS JOIN for matching rows
• Confusing UNION with JOIN
• Using GROUP BY to combine tables

3. Consider two tables:
Table A: IDs 1, 2
Table B: IDs 3, 4
What is the number of rows returned by SELECT * FROM A CROSS JOIN B;?

medium

A. 4

B. 0

C. 1

D. 2

Solution

1. Step 1: Count rows in each table

   Table A has 2 rows, Table B has 2 rows.

2. Step 2: Calculate Cartesian product size

   CROSS JOIN returns all pairs: 2 rows * 2 rows = 4 rows.

3. Final Answer:

   4 -> Option A

4. Quick Check:

   2 * 2 = 4 rows [OK]

Hint: Multiply row counts for CROSS JOIN result size [OK]

Common Mistakes:

• Counting only rows from one table
• Confusing CROSS JOIN with INNER JOIN
• Assuming zero rows if no matching IDs

4. Identify the error in this SQL query:
SELECT * FROM Employees INNER JOIN Departments;

medium

A. SELECT * is invalid syntax

B. INNER JOIN cannot be used with two tables

C. Missing ON clause for join condition

D. Departments table does not exist

Solution

1. Step 1: Review INNER JOIN syntax

   INNER JOIN requires an ON clause to specify how tables relate.

2. Step 2: Check query for ON clause

   The query lacks ON condition, so SQL cannot match rows properly.

3. Final Answer:

   Missing ON clause for join condition -> Option C

4. Quick Check:

   INNER JOIN needs ON clause [OK]

Hint: Always include ON clause with INNER JOIN [OK]

Common Mistakes:

• Omitting ON clause in JOIN
• Thinking INNER JOIN works without condition
• Assuming SELECT * is incorrect

5. You have two tables:
Students (id, name)
Grades (student_id, score)
Which join will return all students and their scores, showing NULL if a student has no grade?

hard

A. LEFT JOIN

B. INNER JOIN

C. RIGHT JOIN

D. CROSS JOIN

Solution

1. Step 1: Understand join types and requirements

   LEFT JOIN returns all rows from the left table (Students) and matching rows from the right (Grades). If no match, NULL appears.

2. Step 2: Match requirement to join type

   We want all students even if they have no grades, so LEFT JOIN is correct.

3. Final Answer:

   LEFT JOIN -> Option A

4. Quick Check:

   All left table rows with NULL for no match = LEFT JOIN [OK]

Hint: LEFT JOIN keeps all left table rows, fills NULL if no match [OK]

Common Mistakes:

• Using INNER JOIN which excludes unmatched rows
• Choosing RIGHT JOIN which keeps right table rows
• Confusing CROSS JOIN with conditional joins