Full Practical Solution | SQL JOINs vs MongoDB Referencing

 Here’s the full solution to the Practice Assignment on SQL JOINs vs MongoDB Referencing, including expected outputs and explanations.

---

✅ Solution: SQL JOINs vs MongoDB Referencing

---

🔹 Part A: SQL Solution

✅ 1. Tables Created

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)
);

---

✅ 2. Sample Data Inserted

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);

---

✅ 3. Query Using JOIN

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

📤 Expected Output:

Title	Name
1984	George Orwell
Animal Farm	George Orwell
Harry Potter	J.K. Rowling

🔍 This result shows book titles along with the author's name using a JOIN between Books and Authors.

---

🔹 Part B: MongoDB Solution

Note: MongoDB doesn’t support traditional SQL-style JOINs. Instead, you can simulate them using referencing (multiple queries) or the $lookup operator in aggregation pipelines.

✅ 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 }
]);

---

✅ 2. Simulating a JOIN

Step 1: Find Author Document

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

🔁 Expected Output:

{
  "_id": 1,
  "name": "George Orwell"
}

---

Step 2: Find Books by Author ID

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

🔁 Expected Output:

[
  { "title": "1984", "author_id": 1 },
  { "title": "Animal Farm", "author_id": 1 }
]

📝 To match SQL output, you'd display:

Title	Author Name
1984	George Orwell
Animal Farm	George Orwell

---

🔸 Optional: Embedded Model Query

If books were stored like this:

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

Then your query:

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

🔁 Would return documents with both book title and embedded author name directly, simulating a denormalized JOIN.

---

✅ Summary

Concept	SQL (JOIN)	MongoDB (Referencing)
How data is linked	Foreign key + JOIN	Manual field referencing (or embedding)
Queries	One SQL JOIN query	Two MongoDB queries (or use $lookup)
Output	Title + Author from joined tables	Title + Author via multiple reads or embed
Query Complexity	Moderate (JOIN syntax)	Simple (but more steps)

---

Note: MongoDB’s $lookup can perform JOIN-like operations in a single aggregation query, but it's more complex and typically used for reporting or complex analytics.

---

• Move to Part 10: Transactions and Consistency

← Previous Next →