Question

PLEASE NOTE BEFORE YOU ANSWER. THIS IS NOT MYSQL, IT IS MONGODB. IF YOU DO NOT KNOW MANGODB, PLEASE DO NOT ANSWER THIS QUESTION. LEAVE IT FOR SOMEONE WHO KNOWS IT.

PART 1

Create a collection that will hold the data in avgprice kwh state.json. This .json file contains the annual data from 1990-2012 on the average energy price per kilowatt hour (KwH) by state and provider type. Implement the following queries:

1. Print each document that has Oklahoma as state, order the result by year in descending order.

2. Print the average residential, commercial, industrial, and transportation price for the state of Texas from the year 1990-2012.

PART 1

Create collections to hold the data in Masters.json, which contains a master list of players and their information, Teams.json (Teams data), Batting.json (batting statistics) and Salaries.json (salary statistics). Load these files into mongodb and implement the following queries:

1. List of top 50 players by highest number of home runs in the 2012 season. List the players by Player ID, First Name and Last Name, number of home runs and season year.

2. List average salaries for each team in 2012. List the teams by Team ID, League ID, Team Name, and average salary amount in descending order.

NOTE: You should use the aggregate() method to answer the questions where required. I just need the query statements. THIS IS IN MONGO DB. if you need more clarifications, ask me on the comments.

Answer 1

PART 1

assuming state is collection name
and db as database
state_name is key having state names like texas

1. Print each document that has Oklahoma as state, order the result by year in descending order.

db.state.find( { state_name: "Oklahoma" } ).sort( { year: -1 } )

2. Print the average residential, commercial, industrial, and transportation price for the state of Texas from the year 1990-2012.

db.state.aggregate(
[
{ $match: { state_name: "Texas" , year: { $lte: 2012 , $gte : 1990}} },
{
$group:
{
_id: "$state_name",
avg_residential: { $avg: "$residential" },
avg_commercial: { $avg: "$commercial" }
avg_industrial: { $avg: "$industrial" }
avg_transportation: { $avg: "$transportation" }
}
}
]
)

PART 2

assuming batting collection having all data related each player.

1. List of top 50 players by highest number of home runs in the 2012 season. List the players by Player ID, First Name and Last Name, number of home runs and season year.

db.batting.find( { year: "2012" } , { player_id: 1, first_name: 1, last_name: 1, home_runs: 1, season_year: 1 } ).sort( { runs: 1 } ).limit(50)

2. List average salaries for each team in 2012. List the teams by Team ID, League ID, Team Name, and average salary amount in descending order.

db.salaries.aggregate(
[
{ $match: { season_year: "2012"} },
{
$group:
{
_id: "$team_id, $",
avg_salary: { $avg: "$salary" }
}
},
   {$sort: {avg_salary: -1}},
   { $project : { team_id : 1 , leage_id : 1 , team_name : 1 , avg_salary : 1    } }
]
)