#### Section 1: Database and Collection Creation
1. **Create a Database**
- Create a database named `School`.
2. **Create Collections**
- Create a collection named `Students` with the following fields:
- `StudentID` (Integer, unique)
- `FirstName` (String)
- `LastName` (String)
- `DateOfBirth` (Date)
3. **Create another collection named `Courses` with the following fields:**
- `CourseID` (Integer, unique)
- `CourseName` (String)
- `Credits` (Integer)
4. **Create a collection named `Enrollments` with the following fields:**
- `EnrollmentID` (Integer, unique)
- `StudentID` (Integer)
- `CourseID` (Integer)
- `EnrollmentDate` (Date)
#### Section 2: Inserting Data
5. **Insert Data into `Students` Collection**
- Insert at least 3 students into the `Students` collection.
6. **Insert Data into `Courses` Collection**
- Insert at least 3 courses into the `Courses` collection.
7. **Insert Data into `Enrollments` Collection**
- Enroll the students in different courses with appropriate dates.
#### Section 3: Basic Queries
8. **Retrieve all Students**
- Write a query to retrieve all documents from the `Students` collection.
9. **Retrieve all Courses**
- Write a query to retrieve all documents from the `Courses` collection.
10. **Retrieve Student Enrollments**
- Write a query to retrieve the `StudentID` and `CourseID` for all enrollments.
11. **Join Query**
- Write an aggregation pipeline to join `Students` and `Enrollments` collections and retrieve the `FirstName`,
`LastName`, and `CourseID` for all students along with their enrollments.
12. **Count Enrollments**
- Write a query to count the number of students enrolled in each course.
13. **Filter by Date**
- Write a query to find all students enrolled after a specific date.
#### Section 4: Updating and Deleting Data
14. **Update Student Information**
- Write a query to update the last name of a student.
15. **Delete a Document**
- Write a query to delete a student from the `Students` collection.
16. **Remove Course Enrollment**
- Write a query to remove a specific course enrollment.
---
### Module 4: MongoDB Functions and Operators
17. **String Functions**
- Write a query to concatenate the first and last names of students.
- Write a query to find the length of the first name of students.
18. **Numeric Functions**
- Write a query to round the credits of courses to the nearest whole number.
- Write a query to find the absolute value of a number and the remainder of a division.
19. **Date and Time Functions**
- Write a query to extract the year from the `DateOfBirth` of students.
- Write a query to find the current date and time.
20. **Aggregate Functions**
- Write a query to count the number of students.
- Write a query to find the average credits of all courses.
21. **Conditional Functions**
- Write a query to use the `$cond` operator to categorize students based on their age.
- Write a query to use `$ifNull` to handle NULL values in a field.
---
### Module 5: Working with Multiple Collections
22. **Understanding Joins**
- Write an aggregation pipeline to perform an INNER JOIN between `Students` and `Enrollments`.
- Write an aggregation pipeline to perform a LEFT JOIN between `Courses` and `Enrollments`.
23. **Combining Results**
- Write a query to combine results of two find operations using `$unionWith`.
- Write a query to find common elements between two collections using `$lookup` and `$match`.
24. **Nested Queries and Subqueries**
- Write a subquery to find students enrolled in a specific course.
- Write a nested query to find the student with the maximum enrollment date.
25. **Set Operations**
- Write a query to use `$unionWith` to combine results of two find operations.
- Write a query to use `$setDifference` to find records in one collection but not in another.
---
### Module 6: MongoDB Constraints and Indexes
26. **Defining Constraints**
- Write a query to add a unique index on the `Email` field in the `Students` collection.
- Write a query to add a validation rule to ensure credits are positive in the `Courses` collection.
27. **Implementing Indexes**
- Write a query to create an index on the `LastName` field in the `Students` collection.
- Write a query to drop an existing index.
28. **Working with Views**
- Write a query to create a view that lists all student enrollments.
- Write a query to alter the view created in the previous question.
29. **Sequences and Identity Columns**
- Describe how to implement a sequence-like behavior in MongoDB for generating unique `StudentID`.
- Describe how to use an identity column for auto-incrementing `CourseID`.
---
### Module 7: Advanced MongoDB Topics
30. **Stored Procedures and Functions**
- Write a function to insert a new student into the `Students` collection.
- Write a function to calculate the age of a student based on their `DateOfBirth`.
31. **Triggers**
- Write a trigger to automatically update a student's enrollment count upon a new enrollment.
- Write a trigger to log changes in the `Courses` collection.
32. **Cursors**
- Write a query to declare a cursor for retrieving student names.
- Write a query to use the cursor to fetch and print student names.
33. **Handling Errors and Exceptions**
- Write a query to handle exceptions in a function.
- Write a query to use try...catch to handle division by zero errors.
34. **Dynamic Queries and Parameterized Queries**
- Write a query to execute dynamic MongoDB queries.
- Write a parameterized query to select students based on a given age.
---
### Module 8: Data Control and Security
35. **User Management**
- Write a query to create a new user with a password.
- Write a query to delete an existing user.
36. **Granting and Revoking Permissions**
- Write a query to grant read permission on the `Students` collection to a user.
- Write a query to revoke insert permission on the `Courses` collection from a user.
37. **Implementing Security Policies**
- Describe how to create a security policy that restricts access to students' data based on their age.
38. **Auditing Database Activities**
- Write a query to enable auditing for the `Students` collection.
- Write a query to retrieve audit logs.
---
### Module 9: Database Design and Optimization
39. **Normalization and Database Design Principles**
- Describe the process of designing a normalized database schema in MongoDB.
40. **Indexing Strategies and Performance Tuning**
- Write a query to analyze the impact of an index on query performance.
41. **Analyzing Query Execution Plans**
- Write a query to generate an execution plan for a complex find operation.
42. **Utilizing Explain and Analyzing Query Performance**
- Write a query to use the `explain` method to understand query performance issues.
