// 2024_4_16.js

// Mongosh
mongosh.exe

// Set up toyu DB in Mongo.
mongorestore --archive="toyu-db.gz" --gzip --nsFrom='toyu.*' --nsTo='toyu.*'

// [1] Show all student document
// within mongosh

use toyu
// default db: toyu; student: collection: find(): find documents: no condition.
db.student.find()

// Getting rid of _id:
// db.collection.find(query, projection, options)
// query: {}: no query condition: "_id": 0  =. don't include _id
// projection: { "_id": 0 } 
db.student.find({},
   { "_id": 0 }  
)

db.student.find({},
   { "_id": false }  
)

students = db.student.find({},
   { "_id": false }  
)

// [2] // Show all information of students majoring in 'CINF'.
// query: {"major": "CINF"}
db.student.find({"major": "CINF"},
    { "_id": 0 }
)

[3] Show all student names and majors. Return an array of student objects.
db.student.find({},
   { "fname": 1, "lname":1, "major": true, "_id": 0 }  
)

db.student.find({"major": "CINF"},
   { "fname": 1, "lname":1, "major": true, "_id": 0 }  
)

[4] Show all student names in this format:
student #0: Tony Hawk
student #1: Mary Hawk
student #2: David Hawk
student #3: Catherine Lim
student #4: Larry Johnson
student #5: Linda Johnson
student #6: Lillian Johnson
student #7: Ben Zico
student #8: Bill Ching
student #9: Linda King
Solution:
result = db.student.find({},
   { "fname": 1, "lname":1, "_id": 0 }  
).toArray()

// May not always work as toArray() returns a promise,
// which may not be ready for use.
result.forEach((x,i) => console.log('student #' + String(i) + ': ' + x["fname"] + ' ' + x["lname"]))

result.forEach((x,i) => console.log('student #' + String(i+1) + ': ' + x["fname"] + ' ' + x["lname"]))

// [5] Show the names and credits (ach) of students majoring in 'CSCI' and having 40 or more credits.
db.student.find(
   { "major": "CSCI", "ach" : {$gte: 40} },
   { "fname": 1, "lname":1, "ach":1, "_id": 0 }  
)

// [6] Show the first name and last name of students with a first name starting with a L or B, case insensitive.
db.student.find(
   { "fname": { $regex: /^[lb]/, $options: "i" } },
   { "fname": 1, "lname":1, "_id": 0 }  
)

// [7] Show the names and credits (ach) of students majoring in 'CSCI' and having 40 or more credits.
db.student.find(
   { "$and": [ { "major": "CSCI"}, { "ach": {"$gte": 40}} ] },
   { "fname": 1, "lname":1, "ach":1, "_id": 0 }  
)

// [8] Show the number of faculty in each department.
In SQL:

SELECT DISTINCT deptCode, Count(facId)
FROM faculty
GROUP BY deptCode;


// In MongoDB:
db.faculty.aggregate([
    {"$group" : {_id:"$deptCode", "count":{$sum:1}}}
])

db.faculty.aggregate([
    {"$group" : {_id:"deptCode", "count":{$sum:1}}}
])


db.faculty.aggregate(
   [  
      { $group: { "_id": "$deptCode", "count": {$sum:1}} },
      { $project: { "deptCode": "$_id" , "num_faculty": "$count",  "_id": 0}}
   ]
)

// [9] Show the names of students who have enrolled in 10000: joining two document.

This should have the similar effect of the SQL statement:

SELECT DISTINCT s.fname, s.lname
FROM student AS s, enroll AS e
WHERE s.stuId = e.stuId AND e.classId = 10000;

In MongoDB:

db.student.aggregate([
    {$lookup:
    {
      from: "enroll",
      let: {joinValue: '$stuId'}, // student.stuId
      pipeline: [
           { $match:
                 { $expr:
                    { $and:
                       [
                         { $eq: [ "$stuId",  "$$joinValue" ] },  // enroll.stuId = student.stuId
                         { $eq: [ "$classId", 10000 ] }
                       ]
                    }
                 }
            }    
        ],
        as: "enrollment"    
        }},
  { $match: {"enrollment":  { $ne: [] }}},  
  { $project: { "fname": 1, "lname": 1, "_id": 0}} 
])






result = db["faculty"].aggregate(
	[	
		{ $group: { "_id": "$deptCode", "count": {$count: {}} }},
		{ $match: { "count": { $gte: 2}}},
		{ $project: { "deptCode": "$_id" , "numFaculty": "$count",  "_id": 0 }},
		{ $sort: { "number of faculty": -1, "_id": 1 }}
	]
).toArray()

deptResult = db.department.find(
	{},
	{  "deptCode":1, "deptName":1, "numStaff": 1, "_id": 0 }	
).toArray();

var deptName = {};
var numStaff = {};
deptResult.forEach((x,i) => {
	deptName[x["deptCode"]] = x["deptName"];
	numStaff [x["deptCode"]] = x["numStaff"]
})

result.forEach((x,i) => console.log('  [' + String(i+1) + ']  ' + deptName[x["deptCode"]] +
	" (" + x["deptCode"] + '): number of faculty ' + x["numFaculty"] + '; number of staff ' + numStaff[x["deptCode"]]))