🧪 Practice Assignment: SQL JOINs vs MongoDB Referencing

Here's a clear and practical assignment to help readers compare SQL JOINs with MongoDB referencing. This exercise is designed to reinforce how both systems manage relationships through hands-on tasks.

🎯 Objective:

Understand how to build and query related data using:

SQL with JOINs
MongoDB with manual referencing

📚 Scenario:

You’re building a simple Library System with:

A list of Authors
A list of Books written by those authors

Each book belongs to one author, and each author can write multiple books (One-to-Many relationship).

🔹 Part A: SQL – Using Foreign Keys & JOINs

✅ Step 1: Create Tables

CREATE TABLE Authors (
  AuthorID INT PRIMARY KEY,
  Name VARCHAR(100)
);

CREATE TABLE Books (
  BookID INT PRIMARY KEY,
  Title VARCHAR(150),
  AuthorID INT,
  FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

✅ Step 2: Insert Data

INSERT INTO Authors (AuthorID, Name) VALUES
(1, 'George Orwell'),
(2, 'J.K. Rowling');

INSERT INTO Books (BookID, Title, AuthorID) VALUES
(101, '1984', 1),
(102, 'Animal Farm', 1),
(103, 'Harry Potter', 2);

✅ Step 3: Write a JOIN Query

Write a SQL query to display all book titles along with their author names.

Example:

SELECT Books.Title, Authors.Name
FROM Books
JOIN Authors ON Books.AuthorID = Authors.AuthorID;

🔹 Part B: MongoDB – Manual Referencing

✅ Step 1: Insert Documents

Authors Collection:

db.authors.insertMany([
  { _id: 1, name: "George Orwell" },
  { _id: 2, name: "J.K. Rowling" }
]);

Books Collection:

db.books.insertMany([
  { title: "1984", author_id: 1 },
  { title: "Animal Farm", author_id: 1 },
  { title: "Harry Potter", author_id: 2 }
]);

✅ Step 2: Query to Simulate a JOIN

Write MongoDB queries to show book titles and their author names:

Step 1: Get author document

const author = db.authors.findOne({ name: "George Orwell" });

Step 2: Find all books by that author

db.books.find({ author_id: author._id });

🔸 Optional Challenge: Try Embedding in MongoDB

Instead of referencing, restructure books with embedded author info:

db.books.insertOne({
  title: "1984",
  author: {
    id: 1,
    name: "George Orwell"
  }
});

Then query:

db.books.find({ "author.name": "George Orwell" });

🧠 Reflection Questions

Which method (JOIN or referencing) felt more intuitive to you?
How does MongoDB referencing differ from SQL JOINs?
What trade-offs do you notice in terms of query performance or complexity?

Click Next to view solution with expected outputs

Feel free to share your thoughts in the comments!

← Previous Next →

Learn Python

Menu