Question

Need the mongodb queuries translated into sql. All files are From stocks except the first is from zips. Will give thumbs up!

db.zips.aggregate([
{$match: {state: "IL"}},
{$group: {_id: "$city", totalzips: {$sum: 1}}},
{$match: {totalzips: {$gte: 3}}},
{$sort: {totalzips: -1}}
])

db.stocks.aggregate([
{$match: {"Sector": "Information Technology", "Dividend Yield": {$gt: 0}}},
{$project: {_id: 0, Name: 1}}
])

db.stocks.aggregate([
{$match: {Sector: "Health Care"}},
{$sort: {"Earnings/Share": -1}},
{$limit: 10},
{$project: {_id: 0, Name: 1, "Earnings/Share": 1}}
])

db.stocks.aggregate([
{$match: {Sector: "Health Care"}},
{$group: {_id: "$Sector", TotalEarnings: {$sum: "$EBITDA"}}}
])

db.stocks.aggregate([
{$match: {Sector: "Industrials"}},
{$project: {_id: 0, Name: 1, Symbol: 1, OutstandingShares:{$divide: ["$Market Cap", "$Price"]}}},
{$sort: {OutstandingShares: -1}}
])

thank you so much!

Answer 1

MongoDB :db.zips.aggregate([
{$match: {state: "IL"}},
{$group: {_id: "$city", totalzips: {$sum: 1}}},
{$match: {totalzips: {$gte: 3}}},
{$sort: {totalzips: -1}}

])

SQL:

SELECT city,Sum(1) AS totalzips FROM Zips

WHERE state="IL" GROUP BY city

HAVING SUM(city) >=3

ORDER BY totalzips DESC;

EXPALANTION:-

Here, we are fetching data from the Zips collection.We are considering collection as a table here.

Grouping is done on city and city is being taken as _id meaning we will be displaying city with the aggregated data.

Match is used to filter data in mongo on the basis of state="IL" .So ,we are using WHERE clause.

Match is also used with totalZips ,this is aggregated data so in SQL we use HAVING clause.

Sort is done using totalZips ,here -1 means in descending order so we did same in SQL.

MongoDB:

db.stocks.aggregate([
{$match: {"Sector": "Information Technology", "Dividend Yield": {$gt: 0}}},
{$project: {_id: 0, Name: 1}}
])

SQL:

SELECT Name FROM Stocks

WHERE Sector= "Information Technology" AND [Dividend Yield] >0;

EXPALANTION:-

we are using aggregate on stocks collection .

Match is used for filtering data with two conditions to be satisfied ,so in SQL we are using WHERE clause with AND operator.

gt here means greater than .

project means getting particular field only so {$project: {_id: 0, Name: 1}} here ,0 means that field will not be selected and 1 means that field will be selected.

MongoDB: db.stocks.aggregate([
{$match: {Sector: "Health Care"}},
{$sort: {"Earnings/Share": -1}},
{$limit: 10},
{$project: {_id: 0, Name: 1, "Earnings/Share": 1}}
])

SQL:

SELECT Name,[Earnings/Share] FROM Stocks

WHERE Sector= "Health Care"

ORDER BY [Earnings/Share] DESC

LIMIT 10;

EXPALANTION:-

we are using aggregate on stocks collection .

Match is used for filtering data with one conditions to be satisfied ,so in SQL we are using WHERE clause.

We are sorting by Earning/share field in descending order.

We are limiting data to 10 object so LIMIT 10 is used.

project means getting particular field only so {$project: {_id: 0, Name: 1, "Earnings/Share": 1}} ,0 means that field will not be selected and 1 means that field will be selected.

MongoDB: db.stocks.aggregate([
{$match: {Sector: "Health Care"}},
{$group: {_id: "$Sector", TotalEarnings: {$sum: "$EBITDA"}}}
])

SQL:

SELECT Sector,SUM(EBITDA) AS TotalEarnings FROM Stocks

WHERE Sector= 'Health Care'

GROUP BY sector;

EXPALANTION:-

Here, we are fetching data from the Stocks collection.We are considering collection as a table here.

Grouping is done on sector and sector is being taken as _id meaning we will be displaying sector with the aggregated data.

Match is used to filter data in mongo on the basis of sector so we are using WHERE clause.

AS(ALIAS) is used for naming the resultant column.

MongoDB: db.stocks.aggregate([
{$match: {Sector: "Industrials"}},
{$project: {_id: 0, Name: 1, Symbol: 1, OutstandingShares:{$divide: ["$Market Cap", "$Price"]}}},
{$sort: {OutstandingShares: -1}}
])

SQL:

SELECT   Name, Symbol,([Market Cap]/Price) AS OutstandingShares FROM Stocks

WHERE Sector= "Industrials"

ORDER BY OutstandingShares DESC;

EXPALANTION:-

we are using aggregate on stocks collection .

Match is used for filtering data with one conditions to be satisfied ,so in SQL we are using WHERE clause.

We are sorting by OutstandingShares field in descending order.

project means getting particular field only so {$project: {_id: 0, Name: 1, Symbol: 1, OutstandingShares:{$divide: ["$Market Cap", "$Price"]}}} ,0 means that field will not be selected and 1 means that field will be selected.

{$divide: ["$Market Cap", "$Price"]} means dividing [market cap] column by price column

AS(ALIAS) is used for naming the resultant column.