// Exp 8 - MongoDB commands
// start shell: mongosh   (old VMs: mongo)
// whole file at once:  mongosh < commands.txt

// ---------- database / collection ----------
show dbs                                  // list databases
use college                               // switch to db (created on first insert)
db.students.drop()                        // clean start (ignore if not there)

db.students.insertOne({ roll: 1, name: "Tanvi", dept: "COMP", marks: 82, city: "Mumbai" })
db.students.insertMany([
  { roll: 2, name: "Omkar",  dept: "IT",   marks: 67, city: "Thane" },
  { roll: 3, name: "Riya",   dept: "COMP", marks: 91, city: "Pune" },
  { roll: 4, name: "Yash",   dept: "EXTC", marks: 58, city: "Mumbai" },
  { roll: 5, name: "Aarav",  dept: "IT",   marks: 74 }          // no city - schema less
])
show collections

// ---------- read ----------
db.students.find()                                        // all docs
db.students.find().pretty()                               // formatted (old shell)
db.students.findOne({ roll: 3 })                          // first match
db.students.find({ marks: { $gt: 70 } }, { name: 1, marks: 1, _id: 0 })   // filter + projection
db.students.find({ dept: "COMP", marks: { $gte: 85 } })   // AND (comma)
db.students.find({ $or: [{ dept: "EXTC" }, { marks: { $lt: 60 } }] })      // OR
db.students.find({ dept: { $in: ["IT", "EXTC"] } })       // value in list
db.students.find({ city: { $exists: false } })            // docs without city
db.students.find({ name: /^A/ })                          // regex: name starts with A
db.students.find().sort({ marks: -1 }).limit(3)           // top 3 (-1 = desc)
db.students.find().skip(1).limit(2)                       // paging
db.students.countDocuments({ dept: "IT" })                // count (very old shell: .count())
db.students.distinct("dept")                              // unique values

// ---------- update ----------
db.students.updateOne({ roll: 4 }, { $set: { marks: 63 } })              // change a field
db.students.updateMany({ dept: "IT" }, { $inc: { marks: 2 } })           // +2 to all IT
db.students.updateOne({ roll: 1 }, { $push: { skills: "python" } })      // add to array
db.students.updateOne({ roll: 1 }, { $unset: { city: "" } })             // remove field
db.students.updateOne({ roll: 9 }, { $set: { name: "Neel", dept: "IT", marks: 70 } }, { upsert: true })  // insert if missing
db.students.find({}, { _id: 0 })

// ---------- aggregation ----------
db.students.aggregate([
  { $match: { marks: { $gte: 60 } } },
  { $group: { _id: "$dept", avgMarks: { $avg: "$marks" }, total: { $sum: "$marks" }, count: { $sum: 1 } } },
  { $sort: { avgMarks: -1 } }
])

// ---------- index ----------
db.students.createIndex({ roll: 1 })      // 1 = ascending
db.students.getIndexes()

// ---------- delete ----------
db.students.deleteOne({ roll: 9 })
db.students.deleteMany({ marks: { $lt: 65 } })          // removes Yash
db.students.countDocuments()

// db.students.drop()        // delete collection
// db.dropDatabase()         // delete current db

/* viva
 - document db, data stored as BSON (binary json) docs inside collections
 - sql -> mongo : table = collection, row = document, column = field, primary key = _id
 - schema less: docs in one collection can have different fields (Aarav has no city)
 - every doc gets unique _id (ObjectId) if we dont give one
 - replication = copies for availability (replica set), sharding = split data across servers
 - index avoids full collection scan
*/
