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

Need a 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

Need a 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

Need a 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

Need a hint?

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