Cartesian product and joins in DBMS Theory - Mini Project: Build & Apply

Cartesian Product and Joins in SQL

📖 Scenario: You are working with two simple tables in a small bookstore database. One table lists authors and the other lists books. You want to understand how to combine these tables using SQL to see all possible pairs of authors and books, and then how to join them based on matching author IDs.

🎯 Goal: Build SQL queries that first create a Cartesian product of the authors and books tables, and then perform an inner join to match books with their authors.

📋 What You'll Learn

Create two tables named authors and books with exact columns and data

Write a SQL query to produce the Cartesian product of authors and books

Write a SQL query to perform an inner join between authors and books on author_id

Use exact table and column names as specified

💡 Why This Matters

🌍 Real World

Understanding Cartesian products and joins is essential for combining data from multiple tables in databases, which is common in business, research, and web applications.

💼 Career

Database administrators, data analysts, and backend developers frequently write join queries to retrieve meaningful combined data from relational databases.

1

Create the authors and books tables with data

Write SQL statements to create a table called authors with columns author_id (integer) and author_name (text). Insert these exact rows: (1, 'Alice Walker'), (2, 'Mark Twain'). Then create a table called books with columns book_id (integer), title (text), and author_id (integer). Insert these exact rows: (101, 'The Color Purple', 1), (102, 'Adventures of Huckleberry Finn', 2).

DBMS Theory

-- Create authors table and insert data
-- Create books table and insert data

Hint

Use CREATE TABLE statements for both tables. Then use INSERT INTO to add the exact rows.

2

Write a query to produce the Cartesian product of authors and books

Write a SQL query that selects all columns from both authors and books tables without any join condition, producing the Cartesian product. Use SELECT * FROM authors, books exactly.

DBMS Theory

CREATE TABLE authors (
  author_id INTEGER,
  author_name TEXT
);

INSERT INTO authors (author_id, author_name) VALUES
  (1, 'Alice Walker'),
  (2, 'Mark Twain');

CREATE TABLE books (
  book_id INTEGER,
  title TEXT,
  author_id INTEGER
);

INSERT INTO books (book_id, title, author_id) VALUES
  (101, 'The Color Purple', 1),
  (102, 'Adventures of Huckleberry Finn', 2);

-- Write a query to select all columns from authors and books without join condition
-- Your code here

Hint

Use a simple SELECT * FROM authors, books to get the Cartesian product.

3

Write a query to perform an inner join on author_id

Write a SQL query that selects author_name and title from authors joined with books where authors.author_id = books.author_id. Use the exact syntax: SELECT author_name, title FROM authors INNER JOIN books ON authors.author_id = books.author_id.

DBMS Theory

CREATE TABLE authors (
  author_id INTEGER,
  author_name TEXT
);

INSERT INTO authors (author_id, author_name) VALUES
  (1, 'Alice Walker'),
  (2, 'Mark Twain');

CREATE TABLE books (
  book_id INTEGER,
  title TEXT,
  author_id INTEGER
);

INSERT INTO books (book_id, title, author_id) VALUES
  (101, 'The Color Purple', 1),
  (102, 'Adventures of Huckleberry Finn', 2);

SELECT * FROM authors, books;

-- Write a query to select author_name and title using INNER JOIN on author_id
-- Your code here

Hint

Use INNER JOIN with ON authors.author_id = books.author_id to match authors with their books.

4

Add an alias to improve readability in the join query

Modify the previous join query by adding table aliases a for authors and b for books. Select a.author_name and b.title. Use the exact syntax: SELECT a.author_name, b.title FROM authors AS a INNER JOIN books AS b ON a.author_id = b.author_id.

DBMS Theory

CREATE TABLE authors (
  author_id INTEGER,
  author_name TEXT
);

INSERT INTO authors (author_id, author_name) VALUES
  (1, 'Alice Walker'),
  (2, 'Mark Twain');

CREATE TABLE books (
  book_id INTEGER,
  title TEXT,
  author_id INTEGER
);

INSERT INTO books (book_id, title, author_id) VALUES
  (101, 'The Color Purple', 1),
  (102, 'Adventures of Huckleberry Finn', 2);

SELECT * FROM authors, books;

SELECT author_name, title FROM authors INNER JOIN books ON authors.author_id = books.author_id;

-- Rewrite the join query using aliases a and b for authors and books
-- Your code here

Hint

Use AS to assign aliases and refer to columns with these aliases.

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