Homework: Homework 5.2 (Hands On) , Crunching the Zipcode dataset , calculate the average population of cities in California (abbreviation CA) and New York (NY) (taken together) with populations over 25,000.

db.grades.aggregate([{$match:{state:{$in:["CA","NY"]}}},{$group:{_id:{city:"$city",state:"$state"},pop:{$sum:"$pop"}}},{$match:{"pop":{$gt:25000}}},{$group:{_id:null,total:{$avg:"$pop"}}}])

Homework: Homework 5.4 , Removing Rural Residents , calculate the number of people who live in a zip code in the US where the city starts with a digit

 db.zips.aggregate([{$project:{city:{$substr:["$city",0,1]},pop:"$pop",_id:0}},{$match:{city:{$regex:'[0-9]'}}},{$group:{_id:null,pop:{$sum:"$pop"}}}])

week 5, , Homework: Homework 5.1 (Hands On) , aswers

db.posts.aggregate([{$unwind:"$comments"},{$group:{"_id":"$comments.author",total_comments:{$sum:1}}},{$sort:{"total_comments":-1}},{$limit:1}])

Write a program in the language of your choice that will remove the grade of type "homework" with the lowest score for each student from the dataset that you imported in HW 2.1. Since each document is one grade, it should remove one document per student.

package com.tengen;

import java.net.UnknownHostException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import com.mongodb.AggregationOutput;
import com.mongodb.BasicDBObject;
import com.mongodb.DB;
import com.mongodb.DBCollection;
import com.mongodb.DBCursor;
import com.mongodb.DBObject;
import com.mongodb.MongoClient;
import com.mongodb.ServerAddress;

public class Homework23 {

public static void main(String[] args) throws UnknownHostException {
MongoClient client = new MongoClient(new ServerAddress("localhost",
27017));

DB db = client.getDB("students");
DBCollection collection = db.getCollection("grades");

BasicDBObject bdb = new BasicDBObject("type", "homework");

BasicDBObject sort = new BasicDBObject("student_id", 1).append("score",
1);

// DBCursor cur = collection.find(bdb).sort(sort);

List<DBObject> pipeline = new ArrayList<DBObject>();

pipeline.add(new BasicDBObject("$group", new BasicDBObject("_id",
"$student_id")));

AggregationOutput aoup = collection.aggregate(pipeline);

Iterable<DBObject> results = aoup.results();

Iterator<DBObject> itr = results.iterator();
DBCursor cur = null;
try {

while (itr.hasNext()) {
DBObject agdb = itr.next();

bdb.append("student_id", agdb.get("_id"));

cur = collection.find(bdb).sort(sort);

cur.hasNext();

DBObject docToRemove = cur.next();

Double minScore = (Double) docToRemove.get("score");

while (cur.hasNext()) {

DBObject doc1 = cur.next();

if (minScore > (Double) doc1.get("score")) {
minScore = (Double) doc1.get("score");
docToRemove = doc1;
}

}

System.out.println("*********************");
System.out.println(docToRemove);
System.out.println("*********************");

collection.remove(docToRemove);

}

}

finally {
if (cur != null)
cur.close();
}

}

}

What is the student_id of the lowest exam score above 65?

For "What is the student_id of the lowest exam score above 65?"

run the below query you will get the answer

db.grades.aggregate({$group:{_id:{studentId:"$student_id",'typ1':"$type"},'average':{$avg:'$score'}}},{"$sort":{"average":1}},{$match:{"_id.typ1":"exam","average":{$gte:65}}},{"$limit":1});