150 mountaindale rd yonkers ny 10710

NoSQL Queries-MongoDB

Database Foundations for Business Analytics

ASSIGNMENT 4

Due Date: November 22, 2020 (11:59 pm)

In this assignment, you will use MongoDB query language to read, process, and extract data from MongoDB collections. The first part of the assignment involves writing “simple MongoDB queries” using the find( ) method. The second part of the assignment involves writing “advanced MongoDB queries” using the MongoDB aggregation pipeline.

For each question, your answer should be a single MongoDB statement (i.e., there should be a piece of code starting with db.collection.method() and ending with a semi-column).

Save your answers to a text file. Do NOT forget to write your group number and the names of all the members in your group at the top the file using comment lines (/* Group x*/)

(/* Member 1 Name*/) (/* Member 2 Name*/)

Use a comment line to indicate which question the subsequent query answers (such as /*Answer to Question X*/).

Submit a soft copy of your assignment (one submission per group) by the due date (refer to the class syllabus for late submission penalties). You can make only ONE attempt. Therefore, do NOT submit your assignment prematurely if you think that you can make changes on your solution later.

Good luck!

We have a collection of researchers named “scientists” (see the scientists.json file). This collection keeps track of basic information about researchers, like their name, birth date, death date (if any), contributions to the scientific world, awards received for their contributions. Each document has a unique _id field and looks like this:

{

_id : 1,

name : {

first : "John",

last : "Backus"

},

birth : ISODate("1924-12-03T05:00:00Z"),

death : ISODate("2007-03-17T04:00:00Z"),

contribs : [

"Fortran",

"ALGOL",

"FP"

],

awards : [

{

award : "W.W. McDowell Award",

year : 1967,

by : "IEEE Computer Society"

},

{

award : "National Medal of Science",

year : 1975,

by : "National Science Foundation"

},

{

award : "Turing Award",

year : 1977,

by : "ACM"

}

]

}

1. Find the list of researchers who were born before 1935?

2. Find the list of researchers who have received the "National Medal of Science" award since 1970.

3. Display only the last name and the first two contributions of all scientists. Sort the list in ascending order by the last name.

4. Find the list of researchers who are still alive. Limit the result set to 3 researchers.

5. Find all researchers who have made contributions to both “Fortran” and “Java”.

We have a collection of populations by postal code named “codes” (see the codes.json file). The postal codes are in the _id field, and are therefore unique. Documents look like this:

{

_id : "35045",

city : "CLANTON",

loc : [-86.64, 32.84],

pop: 13990,

state : "AL"

}

Note that there are 50 non-overlapping states in the US with two letter abbreviations such as NY and CA. In addition, the capital of Washington is within an area designated the District of Columbia, and carries the abbreviation DC. For purposes of the mail, the postal service considers DC to be a "state." So in this dataset, there are 51 states. A city may overlap several postal codes.

Write an aggregation query to answer the following questions. in the result set is used a placeholder for the correct answer.

6. Find the population of each state. The output should look like this:

{

_id : "CA",

population :

},

{

_id : "MT",

population :

},...

7. Find the population of the postal code in each state with the highest population. The output should look like this:

{

state : "WI",

population :

},

{

state : "WV",

population :

}, ...

8. Find the population of each city in the state of New York (NY) and list the postal codes that are included in each city. The output should look like this:

{

_id : "ELMIRA",

population : ,

postal_codes : [

,

,

]

},

{

_id : "WHITESVILLE",

population : ,

postal_codes : [

]

},...

9. Find the largest city in each state. The output should look like this:

{

_id : "WI",

city : ,

population :

},

{

_id : "VT",

city : ,

population :

}, ...

10. Calculate the average population of cities in California (CA) and New York (NY) with populations over 30,000. The output should look like this:

{

pop :

}

1

codes.json
{ "_id" : "01035", "city" : "HADLEY", "loc" : [ -72.571499, 42.36062 ], "pop" : NumberInt(4231), "state" : "MA" } { "_id" : "01056", "city" : "LUDLOW", "loc" : [ -72.471012, 42.172823 ], "pop" : NumberInt(18820), "state" : "MA" } { "_id" : "01109", "city" : "SPRINGFIELD", "loc" : [ -72.554349, 42.114455 ], "pop" : NumberInt(32635), "state" : "MA" } { "_id" : "01013", "city" : "CHICOPEE", "loc" : [ -72.607962, 42.162046 ], "pop" : NumberInt(23396), "state" : "MA" } { "_id" : "01005", "city" : "BARRE", "loc" : [ -72.108354, 42.409698 ], "pop" : NumberInt(4546), "state" : "MA" } { "_id" : "01027", "city" : "MOUNT TOM", "loc" : [ -72.679921, 42.264319 ], "pop" : NumberInt(16864), "state" : "MA" } { "_id" : "01010", "city" : "BRIMFIELD", "loc" : [ -72.188455, 42.116543 ], "pop" : NumberInt(3706), "state" : "MA" } { "_id" : "01201", "city" : "PITTSFIELD", "loc" : [ -73.247088, 42.453086 ], "pop" : NumberInt(50655), "state" : "MA" } { "_id" : "01071", "city" : "RUSSELL", "loc" : [ -72.840343, 42.147063 ], "pop" : NumberInt(608), "state" : "MA" } { "_id" : "01151", "city" : "INDIAN ORCHARD", "loc" : [ -72.505048, 42.153225 ], "pop" : NumberInt(8702), "state" : "MA" } { "_id" : "01033", "city" : "GRANBY", "loc" : [ -72.520001, 42.255704 ], "pop" : NumberInt(5526), "state" : "MA" } { "_id" : "01008", "city" : "BLANDFORD", "loc" : [ -72.936114, 42.182949 ], "pop" : NumberInt(1240), "state" : "MA" } { "_id" : "01012", "city" : "CHESTERFIELD", "loc" : [ -72.833309, 42.38167 ], "pop" : NumberInt(177), "state" : "MA" } { "_id" : "01070", "city" : "PLAINFIELD", "loc" : [ -72.918289, 42.514393 ], "pop" : NumberInt(571), "state" : "MA" } { "_id" : "01007", "city" : "BELCHERTOWN", "loc" : [ -72.410953, 42.275103 ], "pop" : NumberInt(10579), "state" : "MA" } { "_id" : "01030", "city" : "FEEDING HILLS", "loc" : [ -72.675077, 42.07182 ], "pop" : NumberInt(11985), "state" : "MA" } { "_id" : "01060", "city" : "FLORENCE", "loc" : [ -72.654245, 42.324662 ], "pop" : NumberInt(27939), "state" : "MA" } { "_id" : "01082", "city" : "WARE", "loc" : [ -72.258285, 42.261831 ], "pop" : NumberInt(9808), "state" : "MA" } { "_id" : "01036", "city" : "HAMPDEN", "loc" : [ -72.431823, 42.064756 ], "pop" : NumberInt(4709), "state" : "MA" } { "_id" : "01038", "city" : "HATFIELD", "loc" : [ -72.616735, 42.38439 ], "pop" : NumberInt(3184), "state" : "MA" } { "_id" : "01068", "city" : "OAKHAM", "loc" : [ -72.051265, 42.348033 ], "pop" : NumberInt(1503), "state" : "MA" } { "_id" : "01069", "city" : "PALMER", "loc" : [ -72.328785, 42.176233 ], "pop" : NumberInt(9778), "state" : "MA" } { "_id" : "01129", "city" : "SPRINGFIELD", "loc" : [ -72.487622, 42.122263 ], "pop" : NumberInt(6831), "state" : "MA" } { "_id" : "01104", "city" : "SPRINGFIELD", "loc" : [ -72.577769, 42.128848 ], "pop" : NumberInt(22115), "state" : "MA" } { "_id" : "01106", "city" : "LONGMEADOW", "loc" : [ -72.5676, 42.050658 ], "pop" : NumberInt(15688), "state" : "MA" } { "_id" : "01002", "city" : "CUSHMAN", "loc" : [ -72.51565, 42.377017 ], "pop" : NumberInt(36963), "state" : "MA" } { "_id" : "01077", "city" : "SOUTHWICK", "loc" : [ -72.770588, 42.051099 ], "pop" : NumberInt(7667), "state" : "MA" } { "_id" : "01096", "city" : "WILLIAMSBURG", "loc" : [ -72.777989, 42.408522 ], "pop" : NumberInt(2295), "state" : "MA" } { "_id" : "01057", "city" : "MONSON", "loc" : [ -72.319634, 42.101017 ], "pop" : NumberInt(8194), "state" : "MA" } { "_id" : "01081", "city" : "WALES", "loc" : [ -72.204592, 42.062734 ], "pop" : NumberInt(1732), "state" : "MA" } { "_id" : "01095", "city" : "WILBRAHAM", "loc" : [ -72.446415, 42.124506 ], "pop" : NumberInt(12635), "state" : "MA" } { "_id" : "01098", "city" : "WORTHINGTON", "loc" : [ -72.931427, 42.384293 ], "pop" : NumberInt(877), "state" : "MA" } { "_id" : "01118", "city" : "SPRINGFIELD", "loc" : [ -72.527445, 42.092937 ], "pop" : NumberInt(14618), "state" : "MA" } { "_id" : "01225", "city" : "CHESHIRE", "loc" : [ -73.157964, 42.561059 ], "pop" : NumberInt(3094), "state" : "MA" } { "_id" : "01053", "city" : "LEEDS", "loc" : [ -72.703403, 42.354292 ], "pop" : NumberInt(1350), "state" : "MA" } { "_id" : "01085", "city" : "MONTGOMERY", "loc" : [ -72.754318, 42.129484 ], "pop" : NumberInt(40117), "state" : "MA" } { "_id" : "01092", "city" : "WEST WARREN", "loc" : [ -72.203639, 42.20734 ], "pop" : NumberInt(4441), "state" : "MA" } { "_id" : "01128", "city" : "SPRINGFIELD", "loc" : [ -72.488903, 42.094397 ], "pop" : NumberInt(3272), "state" : "MA" } { "_id" : "01223", "city" : "BECKET", "loc" : [ -73.120325, 42.359363 ], "pop" : NumberInt(1070), "state" : "MA" } { "_id" : "01026", "city" : "CUMMINGTON", "loc" : [ -72.905767, 42.435296 ], "pop" : NumberInt(1484), "state" : "MA" } { "_id" : "01028", "city" : "EAST LONGMEADOW", "loc" : [ -72.505565, 42.067203 ], "pop" : NumberInt(13367), "state" : "MA" } { "_id" : "01031", "city" : "GILBERTVILLE", "loc" : [ -72.198585, 42.332194 ], "pop" : NumberInt(2385), "state" : "MA" } { "_id" : "01105", "city" : "SPRINGFIELD", "loc" : [ -72.578312, 42.099931 ], "pop" : NumberInt(14970), "state" : "MA" } { "_id" : "01119", "city" : "SPRINGFIELD", "loc" : [ -72.51211, 42.12473 ], "pop" : NumberInt(13040), "state" : "MA" } { "_id" : "01001", "city" : "AGAWAM", "loc" : [ -72.622739, 42.070206 ], "pop" : NumberInt(15338), "state" : "MA" } { "_id" : "01040", "city" : "HOLYOKE", "loc" : [ -72.626193, 42.202007 ], "pop" : NumberInt(43704), "state" : "MA" } { "_id" : "01011", "city" : "CHESTER", "loc" : [ -72.988761, 42.279421 ], "pop" : NumberInt(1688), "state" : "MA" } { "_id" : "01039", "city" : "HAYDENVILLE", "loc" : [ -72.703178, 42.381799 ], "pop" : NumberInt(1387), "state" : "MA" } { "_id" : "01072", "city" : "SHUTESBURY", "loc" : [ -72.421342, 42.481968 ], "pop" : NumberInt(1533), "state" : "MA" } { "_id" : "01080", "city" : "THREE RIVERS", "loc" : [ -72.362352, 42.181894 ], "pop" : NumberInt(2425), "state" : "MA" } { "_id" : "01103", "city" : "SPRINGFIELD", "loc" : [ -72.588735, 42.1029 ], "pop" : NumberInt(2323), "state" : "MA" } { "_id" : "01020", "city" : "CHICOPEE", "loc" : [ -72.576142, 42.176443 ], "pop" : NumberInt(31495), "state" : "MA" } { "_id" : "01032", "city" : "GOSHEN", "loc" : [ -72.844092, 42.466234 ], "pop" : NumberInt(122), "state" : "MA" } { "_id" : "01107", "city" : "SPRINGFIELD", "loc" : [ -72.606544, 42.117907 ], "pop" : NumberInt(12739), "state" : "MA" } { "_id" : "01220", "city" : "ADAMS", "loc" : [ -73.117225, 42.622319 ], "pop" : NumberInt(9901), "state" : "MA" } { "_id" : "01222", "city" : "ASHLEY FALLS", "loc" : [ -73.320195, 42.059552 ], "pop" : NumberInt(561), "state" : "MA" } { "_id" : "01054", "city" : "LEVERETT", "loc" : [ -72.499334, 42.46823 ], "pop" : NumberInt(1748), "state" : "MA" } { "_id" : "01073", "city" : "SOUTHAMPTON", "loc" : [ -72.719381, 42.224697 ], "pop" : NumberInt(4478), "state" : "MA" } { "_id" : "01022", "city" : "WESTOVER AFB", "loc" : [ -72.558657, 42.196672 ], "pop" : NumberInt(1764), "state" : "MA" } { "_id" : "01108", "city" : "SPRINGFIELD", "loc" : [ -72.558432, 42.085314 ], "pop" : NumberInt(25519), "state" : "MA" } { "_id" : "01089", "city" : "WEST SPRINGFIELD", "loc" : [ -72.641109, 42.115066 ], "pop" : NumberInt(27537), "state" : "MA" } { "_id" : "01034", "city" : "TOLLAND", "loc" : [ -72.908793, 42.070234 ], "pop" : NumberInt(1652), "state" : "MA" } { "_id" : "01050", "city" : "HUNTINGTON", "loc" : [ -72.873341, 42.265301 ], "pop" : NumberInt(2084), "state" : "MA" } { "_id" : "01075", "city" : "SOUTH HADLEY", "loc" : [ -72.581137, 42.237537 ], "pop" : NumberInt(16699), "state" : "MA" } { "_id" : "01339", "city" : "HAWLEY", "loc" : [ -72.880162, 42.621802 ], "pop" : NumberInt(1325), "state" : "MA" } { "_id" : "01226", "city" : "DALTON", "loc" : [ -73.160259, 42.475046 ], "pop" : NumberInt(7357), "state" : "MA" } { "_id" : "01245", "city" : "WEST OTIS", "loc" : [ -73.213452, 42.187847 ], "pop" : NumberInt(329), "state" : "MA" } { "_id" : "01235", "city" : "PERU", "loc" : [ -73.092433, 42.434604 ], "pop" : NumberInt(2559), "state" : "MA" } { "_id" : "01259", "city" : "SOUTHFIELD", "loc" : [ -73.260933, 42.078014 ], "pop" : NumberInt(622), "state" : "MA" } { "_id" : "01240", "city" : "LENOX", "loc" : [ -73.271322, 42.364241 ], "pop" : NumberInt(5001), "state" : "MA" } { "_id" : "01256", "city" : "SAVOY", "loc" : [ -73.023281, 42.576964 ], "pop" : NumberInt(632), "state" : "MA" } { "_id" : "01243", "city" : "MIDDLEFIELD", "loc" : [ -73.006226, 42.34795 ], "pop" : NumberInt(384), "state" : "MA" } { "_id" : "01230", "city" : "GREAT BARRINGTON", "loc" : [ -73.36065, 42.195922 ], "pop" : NumberInt(10603), "state" : "MA" } { "_id" : "01254", "city" : "RICHMOND", "loc" : [ -73.364457, 42.378398 ], "pop" : NumberInt(1134), "state" : "MA" } { "_id" : "01360", "city" : "NORTHFIELD", "loc" : [ -72.450995, 42.688705 ], "pop" : NumberInt(2829), "state" : "MA" } { "_id" : "01473", "city" : "WESTMINSTER", "loc" : [ -71.909599, 42.548319 ], "pop" : NumberInt(6191), "state" : "MA" } { "_id" : "01537", "city" : "NORTH OXFORD", "loc" : [ -71.885953, 42.16549 ], "pop" : NumberInt(3031), "state" : "MA" } { "_id" : "01562", "city" : "SPENCER", "loc" : [ -71.990617, 42.244103 ], "pop" : NumberInt(11598), "state" : "MA" } { "_id" : "01588", "city" : "WHITINSVILLE", "loc" : [ -71.664357, 42.115319 ], "pop" : NumberInt(8807), "state" : "MA" } { "_id" : "01608", "city" : "WORCESTER", "loc" : [ -71.800262, 42.262425 ], "pop" : NumberInt(3646), "state" : "MA" } { "_id" : "01612", "city" : "PAXTON", "loc" : [ -71.920234, 42.306646 ], "pop" : NumberInt(4047), "state" : "MA" } { "_id" : "01770", "city" : "SHERBORN", "loc" : [ -71.378717, 42.233088 ], "pop" : NumberInt(3998), "state" : "MA" } { "_id" : "01778", "city" : "WAYLAND", "loc" : [ -71.358781, 42.348629 ], "pop" : NumberInt(11874), "state" : "MA" } { "_id" : "01913", "city" : "AMESBURY", "loc" : [ -70.936681, 42.855879 ], "pop" : NumberInt(14970), "state" : "MA" } { "_id" : "01944", "city" : "MANCHESTER", "loc" : [ -70.767434, 42.57963 ], "pop" : NumberInt(5286), "state" : "MA" } { "_id" : "01945", "city" : "MARBLEHEAD", "loc" : [ -70.865291, 42.498431 ], "pop" : NumberInt(19971), "state" : "MA" } { "_id" : "01949", "city" : "MIDDLETON", "loc" : [ -71.013004, 42.594184 ], "pop" : NumberInt(4921), "state" : "MA" } { "_id" : "02035", "city" : "FOXBORO", "loc" : [ -71.244127, 42.064938 ], "pop" : NumberInt(14293), "state" : "MA" } { "_id" : "02048", "city" : "MANSFIELD", "loc" : [ -71.217775, 42.021238 ], "pop" : NumberInt(16676), "state" : "MA" } { "_id" : "02146", "city" : "BROOKLINE", "loc" : [ -71.128917, 42.339158 ], "pop" : NumberInt(56614), "state" : "MA" } { "_id" : "02151", "city" : "REVERE", "loc" : [ -71.005165, 42.413767 ], "pop" : NumberInt(42766), "state" : "MA" } { "_id" : "02189", "city" : "WEYMOUTH", "loc" : [ -70.931671, 42.211606 ], "pop" : NumberInt(14055), "state" : "MA" } { "_id" : "02351", "city" : "ABINGTON", "loc" : [ -70.954293, 42.116715 ], "pop" : NumberInt(13849), "state" : "MA" } { "_id" : "02633", "city" : "SOUTH CHATHAM", "loc" : [ -69.980758, 41.687634 ], "pop" : NumberInt(4744), "state" : "MA" } { "_id" : "02717", "city" : "EAST FREETOWN", "loc" : [ -70.967709, 41.763455 ], "pop" : NumberInt(4883), "state" : "MA" } { "_id" : "02724", "city" : "FALL RIVER", "loc" : [ -71.174822, 41.684975 ], "pop" : NumberInt(18141), "state" : "MA" } { "_id" : "02726", "city" : "SOMERSET", "loc" : [ -71.149206, 41.756012 ], "pop" : NumberInt(15117), "state" : "MA" } { "_id" : "02738", "city" : "MARION", "loc" : [ -70.761261, 41.709526 ], "pop" : NumberInt(4496), "state" : "MA" } { "_id" : "02762", "city" : "PLAINVILLE", "loc" : [ -71.327454, 42.012403 ], "pop" :