//  HW #8 Spring 2025

//
// [1] Show the first name, last name, and deptCode of all assistant professors as a JSON array.
//

use toyu

//  Run as script.
// db = connect( 'mongodb://localhost/toyu' );

db.faculty.find(
	{ "rank": "Assistant Professor" },
	{ "fname": 1, "lname": 1, "deptCode": 1, "_id": 0,  }	
)

//
// [2] Show the name, deptCode and rank for all CSCI or CINF tenured or tenure track faculty (i.e. Professor, Associate Professor or Assistant Professor) in the following format.
//
result = db.faculty.find(
	{ "deptCode": {"$in": ["CSCI", "CINF"] },
	"rank": {"$in": ["Professor", "Associate Professor", "Assistant Professor"] }},
	{ "fname": 1, "lname": 1, "deptCode": 1, "rank": 1, "_id": 0 }	
).toArray()

console.log('CSCI/CINF tenured or tenure-track faculty members')
result.forEach((x,i) => console.log('  [' + String(i+1) + ']  ' +  x["fname"] + ' ' + x["lname"] + ': ' + x["deptCode"] + ' ' + x["rank"]))

//
//  [3] Show the major code and the average number of credits of students enrolled in the major in the following manner.
//
db.student.aggregate(
	[	
		{ $group: { "_id": "$major", "average": {$avg: "$ach"}} },
		{ $project: { "major": "$_id" , "average # credits": "$average",  "_id": 0 }}
	]
)

//
// [4] Show the faculty id of advisors of CSCI students with the number of CSCI advisees in the following format.
//

db.student.aggregate(
	[
		{  $match: { "major": 'CSCI' }},
		{  $group: { "_id": "$advisor", "count": {$sum:1}} },	
		{  $project: { "Advisor facId": "$_id" , "number of advisees": "$count",  "_id": 0}}
	]
)