Cartesian product and joins in DBMS Theory - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the SQL query to select all columns from two tables using a Cartesian product.

DBMS Theory

SELECT * FROM employees [1] departments;

Drag options to blanks, or click blank then click option'

AJOIN

BLEFT JOIN

CINNER JOIN

DCROSS JOIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using INNER JOIN without an ON condition causes an error.

Using LEFT JOIN or JOIN without specifying join conditions.

The CROSS JOIN keyword produces the Cartesian product of the two tables, combining every row of the first table with every row of the second table.

2fill in blank

medium

Complete the SQL query to join two tables on a matching column.

DBMS Theory

SELECT * FROM orders [1] customers ON orders.customer_id = customers.id;

Drag options to blanks, or click blank then click option'

AINNER JOIN

BCROSS JOIN

CFULL JOIN

DRIGHT JOIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using CROSS JOIN when a matching condition is needed.

Using FULL JOIN without understanding it returns all rows from both tables.

The INNER JOIN keyword returns rows when there is a match in both tables based on the join condition.

3fill in blank

hard

Fix the error in the SQL join query by completing the missing keyword.

DBMS Theory

SELECT * FROM products [1] ON products.category_id = categories.id;

Drag options to blanks, or click blank then click option'

ACROSS JOIN

BINNER JOIN

CJOIN

DLEFT JOIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using CROSS JOIN with an ON condition causes syntax errors.

Using JOIN without specifying INNER or LEFT can cause ambiguity.

The INNER JOIN keyword is required here to join the tables based on the condition after ON. Using just JOIN is ambiguous in some SQL dialects.

4fill in blank

hard

Fill both blanks to create a query that selects employee names and department names using an INNER JOIN.

DBMS Theory

SELECT employees.name, departments.name FROM employees [1] departments [2] employees.department_id = departments.id;

Drag options to blanks, or click blank then click option'

AINNER JOIN

BON

CWHERE

DCROSS JOIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using WHERE instead of ON for join conditions.

Using CROSS JOIN when a condition is specified.

The INNER JOIN keyword joins the tables, and the ON keyword specifies the join condition.

5fill in blank

hard

Fill all three blanks to write a query that selects all orders and their customers, including orders without customers, using a LEFT JOIN.

DBMS Theory

SELECT orders.id, customers.name FROM orders [1] customers [2] orders.customer_id = customers.id [3];

Drag options to blanks, or click blank then click option'

ALEFT JOIN

BON

CORDER BY orders.id

DWHERE customers.id IS NULL

Attempts:

3 left

💡 Hint

Common Mistakes

Using INNER JOIN excludes orders without customers.

Using WHERE to filter join results instead of ON.

The LEFT JOIN returns all orders even if there is no matching customer. The ON specifies the join condition. ORDER BY sorts the results by order ID.

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