//
// Database Systems Fall 2025 HW #8
//

use toyu
//
// [1] Show the stuId, classId and grade of all enrollment with a grade of "B+", "B", or "B-" in the following manner.
//

db["enroll"].find(
	{  "grade": {"$in": ["B+", "B", "B-"] }},
	{	"stuId": 1, "classId": 1, "grade": 1, "_id": 0,  }	
)

// [2] Show the name, rank, and department code of every CINF or ITEC faculty member in the following manner.

db.faculty.find(
	{  "deptCode": {"$in": ["CINF", "ITEC"] }},
	{	"faculty": {"$concat": ["$fname", " ", "$lname"]}, 
        "deptCode": 1, "rank": 1, "_id": 0,  }	
)

//
//  [3] Show the classId and the number of all students enrolled in the class in the following manner.
//

db["enroll"].aggregate(
	[	
		{ $group: { "_id": "$classId", "count": {$count: {}}} },
		{ $project: { "classId": "$_id" , "Number of students": "$count",  "_id": 0 }}
	]
)

//
// [4] Show the name, and deptCode of all faculty members who have the substring "an" in their first names or last names in JSON form in the following manner.
//

result = db.faculty.find(
	{ "$or": [ {"fname": { $regex: /an/, $options: "i" }},
	{ "lname": { $regex: /an/, $options: "i" }}]},
	{ "faculty": {"$concat": ["$fname", " ", "$lname"]},
						"deptCode": 1,  "_id": 0}
)

//
// [5] Show the name, and deptCode of all faculty members who have the substring "an" in their first names or last names in the following manner.
//

// use var to set result as a local variable to suppress printing out to mongosh.
var result = db.faculty.find(
	{ "$or": [ {"fname": { $regex: /an/, $options: "i" }},
	{ "lname": { $regex: /an/, $options: "i" }}]},
	{ "fname": 1, "lname": 1, "deptCode": 1, "_id": 0 }	
).toArray()
result.forEach((x,i) => console.log('[' + String(i+1) + ']  ' +  x["fname"] + ' ' + x["lname"] + ': ' + x["deptCode"]))