Open MongoDB Shell using mongosh and create a database named practiceDB.

mongosh

use practiceDB

Create a collection named students:

db.createCollection("students")

Insert the following sample records:

db.students.insertMany([
    {
        student_id: 101,
        name: "Rahul",
        age: 20,
        course: "Java",
        city: "Patna",
        marks: 85
    },
    {
        student_id: 102,
        name: "Priya",
        age: 21,
        course: "Python",
        city: "Gaya",
        marks: 92
    },
    {
        student_id: 103,
        name: "Amit",
        age: 19,
        course: "Java",
        city: "Patna",
        marks: 76
    },
    {
        student_id: 104,
        name: "Neha",
        age: 22,
        course: "PHP",
        city: "Delhi",
        marks: 88
    },
    {
        student_id: 105,
        name: "Ravi",
        age: 20,
        course: "Python",
        city: "Gaya",
        marks: 67
    }
])

Run the sample insertion only once. If you repeat it, MongoDB may store duplicate student records because student_id is not yet unique.

Assignment tasks:

1. Display all students.
2. Display only student names and courses.
3. Find the student whose ID is 103.
4. Find all students from Patna.
5. Find all students enrolled in Python.

Complete the following tasks:

1. Create a database named schoolDB.
2. Create a collection named teachers.
3. Create a collection named courses.
4. Insert at least five teacher documents.
5. Display all collections in the database.
6. Display all documents in the teachers collection.
7. Delete a test collection that you created for practice.

Useful commands:

use schoolDB

db.createCollection("teachers")

db.createCollection("courses")

show collections

db.teachers.find()

Remember that switching to a database with use does not necessarily create it immediately. MongoDB creates it when data is stored or a collection is created.

1. Insert one new student using insertOne().
2. Insert three students using insertMany().
3. Change Rahul's city to Gaya.
4. Update Priya's marks to 95.
5. Change Amit's course to JavaScript.
6. Increase Ravi's marks to 75.
7. Add a new field named email to one student.
8. Use $unset to remove a field from a test document.

Example syntax:

db.students.updateOne(
    { student_id: 101 },
    {
        $set: {
            city: "Gaya"
        }
    }
)

Use $set to modify selected fields without replacing the entire document.

1. Delete one test student using deleteOne().
2. Delete all students from a test city in a separate practice collection.
3. Delete all documents from a temporary collection.
4. Drop a temporary collection.
5. Explain the difference between deleteMany() and drop().

Example syntax:

db.students.deleteOne({
    student_id: 105
})

Be careful with delete operations. Always check the filter and use a practice collection when testing commands that delete many documents.

Write queries to solve these problems:

1. Find students whose age is greater than 20.
2. Find students whose marks are less than 80.
3. Find students whose marks are greater than or equal to 85.
4. Find students whose age is less than or equal to 20.
5. Find students whose marks are not equal to 92.
6. Find students whose marks are between 70 and 90, inclusive.
7. Find students whose course is Java or Python using $in.
8. Find students whose course is neither Java nor PHP using $nin.

Example:

db.students.find({
    marks: { $gte: 80 }
})

1. Find students from Patna who are older than 19.
2. Find students with marks above 90 or age below 20.
3. Find students who are not from Delhi.
4. Find students whose marks are at least 80 and age is at most 21.
5. Find students from Gaya or students enrolled in Java.
6. Use $and to combine three conditions.
7. Use $or to combine three conditions.

Example:

db.students.find({
    $and: [
        { city: "Patna" },
        { marks: { $gt: 75 } }
    ]
})

1. Sort students by marks in ascending order.
2. Sort students by marks in descending order.
3. Display the top three students by marks.
4. Display only two students.
5. Skip the first two documents and display the next two.
6. Display only name, age, and city.
7. Exclude _id from the projected results.
8. Sort students by city and then by marks in descending order.

Example:

db.students.find(
    {},
    {
        _id: 0,
        name: 1,
        marks: 1
    }
).sort({
    marks: -1
}).limit(3)

Create a collection named studentDetails:

db.studentDetails.insertMany([
    {
        student_id: 201,
        name: "Karan",
        skills: ["HTML", "CSS", "JavaScript"],
        address: {
            city: "Patna",
            state: "Bihar"
        }
    },
    {
        student_id: 202,
        name: "Simran",
        skills: ["Python", "MongoDB"],
        address: {
            city: "Gaya",
            state: "Bihar"
        }
    }
])

Assignment tasks:

1. Find students who have JavaScript in their skills array.
2. Find students who have MongoDB in their skills array.
3. Find students whose address city is Patna.
4. Add a skill to one student's array using $push.
5. Add a skill only if it is not already present using $addToSet.
6. Remove a skill using $pull.
7. Update a student's address city using dot notation.

Example:

db.studentDetails.find({
    "address.city": "Patna"
})

1. Create an index on the name field.
2. Create an index on the city field.
3. Create a unique index on student_id.
4. Create a compound index on course and marks.
5. Display all indexes.
6. Use explain("executionStats") to inspect a query.
7. Drop a non-essential index by its actual index name.

Example:

db.students.createIndex({
    student_id: 1
})

Before creating a unique index, check for duplicate values. If duplicates exist, resolve them first.

1. Count the number of students in each course.
2. Calculate the average marks of all students.
3. Calculate the highest marks.
4. Calculate the lowest marks.
5. Calculate total marks for each course.
6. Find the average marks for each city.
7. Filter students with marks above 80 before grouping.
8. Sort course groups by average marks.
9. Display only the top two course groups.
10. Calculate the number of students in each city.

Example: count students in each course.

db.students.aggregate([
    {
        $group: {
            _id: "$course",
            totalStudents: {
                $sum: 1
            }
        }
    },
    {
        $sort: {
            totalStudents: -1
        }
    }
])

Example: calculate average marks by course.

db.students.aggregate([
    {
        $group: {
            _id: "$course",
            averageMarks: {
                $avg: "$marks"
            }
        }
    },
    {
        $sort: {
            averageMarks: -1
        }
    }
])

Create a courses collection:

db.courses.insertMany([
    {
        course_id: 1,
        course_name: "Java",
        fee: 8000
    },
    {
        course_id: 2,
        course_name: "Python",
        fee: 9000
    },
    {
        course_id: 3,
        course_name: "PHP",
        fee: 7000
    }
])

Assignment tasks:

1. Explain the difference between embedding and referencing.
2. Design a student document that references a course using a course ID.
3. Use $lookup to combine students with course details.
4. Display student names and matching course names.
5. Explain why MongoDB does not automatically enforce foreign keys.

For a successful $lookup, both collections must contain matching join fields with compatible values and types.

1. Compare a query plan before and after creating an index.
2. Inspect the number of documents examined by a query.
3. Create a compound index for a common filter and sort pattern.
4. Optimize a query using projection.
5. Use an early $match in an aggregation pipeline.
6. Explain how a replica set provides redundancy.
7. Explain the purpose of sharding.
8. Explain when a multi-document transaction is useful.
9. Describe how you would investigate a slow query.
10. Explain how indexes affect write performance.

Example performance investigation:

db.students.find({
    course: "Java"
}).explain("executionStats")

Build a student management database with these requirements:

• Create a database named studentDB.
• Create collections for students, courses, and payments.
• Store each student's ID, name, mobile, course, and admission date.
• Store course names, fees, and duration.
• Record payments with student ID, amount, and payment date.
• Search students by ID, name, and course.
• Update student details and course information.
• Calculate total fees paid by each student.
• Calculate course-wise student counts.
• Generate a monthly payment report using aggregation.
• Create appropriate indexes.
• Test common queries using explain().

Complete the project using MongoDB Shell or MongoDB Compass. Later, you can connect the database to an application using a MongoDB driver.