//Open Mongo Shell and Execute below commands


db.record.insert({ "_id" : "01001", "city" : "AGAWAM", "loc" : [ -72.622739, 42.070206 ], "pop" : 15338, "state" : "MA" })
db.record.insert({ "_id" : "01002", "city" : "CUSHMAN", "loc" : [ -72.51564999999999, 42.377017 ], "pop" : 36963, "state" : "MA" })
db.record.insert({ "_id" : "01005", "city" : "BARRE", "loc" : [ -72.10835400000001, 42.409698 ], "pop" : 4546, "state" : "MA" })
db.record.insert({ "_id" : "01007", "city" : "BELCHERTOWN", "loc" : [ -72.41095300000001, 42.275103 ], "pop" : 10579, "state" : "MA" })
db.record.insert({ "_id" : "01008", "city" : "BLANDFORD", "loc" : [ -72.936114, 42.182949 ], "pop" : 1240, "state" : "MA" })
db.record.insert({ "_id" : "01010", "city" : "BRIMFIELD", "loc" : [ -72.188455, 42.116543 ], "pop" : 3706, "state" : "MA" })
db.record.insert({ "_id" : "01011", "city" : "CHESTER", "loc" : [ -72.988761, 42.279421 ], "pop" : 1688, "state" : "MA" })
db.record.insert({ "_id" : "01012", "city" : "CHESTERFIELD", "loc" : [ -72.833309, 42.38167 ], "pop" : 177, "state" : "MA" })
db.record.insert({ "_id" : "01013", "city" : "CHICOPEE", "loc" : [ -72.607962, 42.162046 ], "pop" : 23396, "state" : "MA" })
db.record.insert({ "_id" : "01020", "city" : "CHICOPEE", "loc" : [ -72.576142, 42.176443 ], "pop" : 31495, "state" : "MA" })
db.record.insert({ "_id" : "01022", "city" : "WESTOVER AFB", "loc" : [ -72.558657, 42.196672 ], "pop" : 1764, "state" : "MA" })
db.record.insert({ "_id" : "01026", "city" : "CUMMINGTON", "loc" : [ -72.905767, 42.435296 ], "pop" : 1484, "state" : "MA" })
db.record.insert({ "_id" : "01027", "city" : "MOUNT TOM", "loc" : [ -72.67992099999999, 42.264319 ], "pop" : 16864, "state" : "MA" })
db.record.insert({ "_id" : "01028", "city" : "EAST LONGMEADOW", "loc" : [ -72.505565, 42.067203 ], "pop" : 13367, "state" : "MA" })
db.record.insert({ "_id" : "01030", "city" : "FEEDING HILLS", "loc" : [ -72.675077, 42.07182 ], "pop" : 11985, "state" : "MA" })
db.record.insert({ "_id" : "01031", "city" : "GILBERTVILLE", "loc" : [ -72.19858499999999, 42.332194 ], "pop" : 2385, "state" : "MA" })
db.record.insert({ "_id" : "01032", "city" : "GOSHEN", "loc" : [ -72.844092, 42.466234 ], "pop" : 122, "state" : "MA" })
db.record.insert({ "_id" : "01033", "city" : "GRANBY", "loc" : [ -72.52000099999999, 42.255704 ], "pop" : 5526, "state" : "MA" })
db.record.insert({ "_id" : "01034", "city" : "TOLLAND", "loc" : [ -72.908793, 42.070234 ], "pop" : 1652, "state" : "MA" })
db.record.insert({ "_id" : "01035", "city" : "HADLEY", "loc" : [ -72.571499, 42.36062 ], "pop" : 4231, "state" : "MA" })
db.record.insert({ "_id" : "01036", "city" : "HAMPDEN", "loc" : [ -72.43182299999999, 42.064756 ], "pop" : 4709, "state" : "MA" })
db.record.insert({ "_id" : "01038", "city" : "HATFIELD", "loc" : [ -72.61673500000001, 42.38439 ], "pop" : 3184, "state" : "MA" })
db.record.insert({ "_id" : "01039", "city" : "HAYDENVILLE", "loc" : [ -72.70317799999999, 42.381799 ], "pop" : 1387, "state" : "MA" })
db.record.insert({ "_id" : "01040", "city" : "HOLYOKE", "loc" : [ -72.626193, 42.202007 ], "pop" : 43704, "state" : "MA" })
db.record.insert({ "_id" : "01050", "city" : "HUNTINGTON", "loc" : [ -72.873341, 42.265301 ], "pop" : 2084, "state" : "MA" })
db.record.insert({ "_id" : "01053", "city" : "LEEDS", "loc" : [ -72.70340299999999, 42.354292 ], "pop" : 1350, "state" : "MA" })
db.record.insert({ "_id" : "01054", "city" : "LEVERETT", "loc" : [ -72.499334, 42.46823 ], "pop" : 1748, "state" : "MA" })
db.record.insert({ "_id" : "01056", "city" : "LUDLOW", "loc" : [ -72.471012, 42.172823 ], "pop" : 18820, "state" : "MA" })
db.record.insert({ "_id" : "01057", "city" : "MONSON", "loc" : [ -72.31963399999999, 42.101017 ], "pop" : 8194, "state" : "MA" })
db.record.insert({ "_id" : "01060", "city" : "FLORENCE", "loc" : [ -72.654245, 42.324662 ], "pop" : 27939, "state" : "MA" })
db.record.insert({ "_id" : "01068", "city" : "OAKHAM", "loc" : [ -72.051265, 42.348033 ], "pop" : 1503, "state" : "MA" })
db.record.insert({ "_id" : "01069", "city" : "PALMER", "loc" : [ -72.328785, 42.176233 ], "pop" : 9778, "state" : "MA" })
db.record.insert({ "_id" : "01070", "city" : "PLAINFIELD", "loc" : [ -72.918289, 42.514393 ], "pop" : 571, "state" : "MA" })
db.record.insert({ "_id" : "01071", "city" : "RUSSELL", "loc" : [ -72.840343, 42.147063 ], "pop" : 608, "state" : "MA" })
db.record.insert({ "_id" : "01072", "city" : "SHUTESBURY", "loc" : [ -72.421342, 42.481968 ], "pop" : 1533, "state" : "MA" })
db.record.insert({ "_id" : "01073", "city" : "SOUTHAMPTON", "loc" : [ -72.719381, 42.224697 ], "pop" : 4478, "state" : "MA" })
db.record.insert({ "_id" : "01075", "city" : "SOUTH HADLEY", "loc" : [ -72.581137, 42.237537 ], "pop" : 16699, "state" : "MA" })
db.record.insert({ "_id" : "02921", "city" : "CRANSTON", "loc" : [ -71.506102, 41.761357 ], "pop" : 6502, "state" : "RI" })
db.record.insert({ "_id" : "03031", "city" : "AMHERST", "loc" : [ -71.607536, 42.856944 ], "pop" : 8998, "state" : "NH" })




//Now Run Below commands in Query Analyzer one by 


SELECT "city" AS "hometowns" from "record" where "city" like 'H%'
GO

SELECT FLATTEN "_id", "city", "loc", "pop", "state" 
	FROM "record"
GO


SELECT FLATTEN  "pop", "state" 
	FROM "record"
GO


SELECT FLATTEN * FROM "record" where "city" LIKE 'C%'
Go

SELECT DISTINCT "state" FROM "record"
Go

SELECT COUNT(DISTINCT "state") FROM "record"
Go

SELECT "city" FROM "record" WHERE "_id"= '01001'
GO

SELECT "city" FROM "record" WHERE "_id"= '01001' OR "_id" = '01005'
GO


SELECT * FROM "record" WHERE "city" ='AGAWAM'  AND "pop" = 15338
GO

SELECT "city","loc"  FROM "record" WHERE "pop" > 10000 GROUP BY "loc","city"
GO


SELECT "city",SUM(pop) AS pop FROM "record" GROUP BY "city" HAVING "pop" > 10000 
GO


SELECT "city","state" FROM "record" WHERE "pop" > 30000 ORDER BY "city" DESC  
GO


select count(*) from "record" where "pop" > 20000 and state not in ('MA')
go


select count(*) from "record" where "pop" < 20000 and state not in ('MA')
go


select count(*) from "record" where "pop" < 20000 and state  in ('MA')
go


select count(*) from "record" where "pop" > 50000 and (100 % 100 < 1.5) 
go

//insert

INSERT INTO "record"("_id", "city", "loc", "pop", "state")
  VALUES('03000', 'EHERST', [ -81.907536, 22.856944 ], 11998.0, 'CH')
GO

INSERT INTO "record"("_id", "city", "loc", "pop", "state")
  VALUES('03054', 'DRRST', [ -61.907536, 22.776944 ], 21998.0, 'kH')
GO


INSERT INTO "record"("_id",  "loc", "pop")
  VALUES('08500', [ -81.907536, 22.856944 ], 11998.0)
GO

INSERT INTO "record"("_id",  "loc", "pop")
  VALUES('08520', [ -21.907536, 12.856944 ], 20998.0)
GO

//update

UPDATE "record" 
	SET "loc" = [ -13.1234,63.1234 ],"state" = 'BA'
	WHERE "city" = 'GOSHEN'
GO

UPDATE "record" 
	SET "loc" = [ -92.1234,59.1234 ],"state" = 'UA'
	WHERE "city" = 'MOUNT TOM'
GO

UPDATE "record" 
	SET "city" = 'SOUTH B', "loc" = [ -71.3442,71.3442 ], "pop" = 62763
	WHERE "state"= 'CH'
GO

REPLACE INTO "record" 
   VALUE({"city": "TOLLAND", "loc": [-82.533765, 30.025565], "pop": 86, "state": "LB"}) 
   WHERE "city"='TOLLAND'


//delete

DELETE FROM "record" WHERE "city" = 'GRANBY'
GO


//User and Roles 


USE admin
GO
CREATE USER "android1" IDENTIFIED BY '1234'
GO
GRANT ROLE "dbAdmin" DB "admin" TO USER "android1"
GO
GRANT ROLE "dbAdminAnyDatabase" DB "admin" TO USER "android1"


USE admin
GO
CREATE USER "android2" IDENTIFIED BY '1234'
GO

ALTER USER "android2" IDENTIFIED BY '12345'

DROP USER "android2"



CREATE ROLE "apple" ROLE "dbOwner" DB "admin"
GO
GRANT ROLE "dbAdminAnyDatabase" DB "admin" TO ROLE "apple"
GO
GRANT PRIVILEGE CLUSTER ACTIONS "authSchemaUpgrade", "cleanupOrphaned" TO ROLE "apple"



CREATE ROLE "apple1" ROLE "dbOwner" DB "admin"
GO
GRANT ROLE "dbAdminAnyDatabase" DB "admin" TO ROLE "apple1"
GO
GRANT PRIVILEGE CLUSTER ACTIONS "authSchemaUpgrade", "cleanupOrphaned" TO ROLE "apple1"