Question

Translating MongoDB Queries to SQL - WILL UPVOTE!!

1. Write the equivalent sql queries for all mongoDB queries.

The following is an example of what you are asked to do. Assume the collection name is the table name in an sql database.

Example:

MongoDB Query:

db.zips.aggregate([
    {$match: {state: "MO"}},
    {$group: {_id: "$city", totalPop: {$sum: "$pop"}}},
    {$sort: {totalPop: -1}},
    {$limit: 10}
])

SQL Result:

Select city, SUM(pop) AS "totalPop"
From zips
Where state = "MO"
Group By city
Order By SUM(pop) DESC
Limit 10;

Queries:

1. db.zips.distinct("state", ({"pop": {$gte: 90000}}))

2. db.zips.aggregate([

{$match: {state: "MN"}},

{$group: {_id: "$state", totalPop: {$sum: "$pop"}}},

])

3. db.zips.aggregate([

{$match: {state: "MO"}},

{$group: {_id: "$city", TotalPopulation: {$sum: "$pop"}}},

{$sort: {TotalPopulation: -1}}

])

4. db.zips.aggregate([

{$match: {state: "NY", city: "NEW YORK"}},

{$group: {_id: "$city", totalPop: {$sum: "$pop"}}}

])

5. db.zips.aggregate([

{$match: {state: "IL"}},

{$group: {_id: "$city", totalzips: {$sum: 1}}},

{$match: {totalzips: {$gte: 3}}},

{$sort: {totalzips: -1}}

])

6. db.zips.aggregate([

{$group: {_id: "$state", numZips: {$sum: 1}}},

{$sort : { numZips: 1 }},

{$limit: 1}

])

7. db.zips.aggregate([

{$group: {_id: "$city", totalPop: {$sum: "$pop"}}},

{$sort: {totalPop: -1}},

{$limit: 1}

])

8. db.zips.aggregate([

{$group: {_id: "$city", totalPop: {$sum: "$pop"}}},

{$sort: {totalPop: 1}},

{$limit: 1}

])

9. db.zips.aggregate([

{$group: {_id: "$city", totalPop: {$sum: "$pop"}}},

{$sort: {totalPop: -1}},

{$limit: 20}

])

10. db.stocks.aggregate([

{$sort: {Price: -1}},

{$limit: 10},

{$project: {_id: 0, Symbol: 1, Name: 1, Price: 1}}

])

11. db.stocks.aggregate([

{$group: {_id: "$Sector", TotalEarnings: {$sum: "$EBITDA"}}},

])

12. db.stocks.aggregate([

{$group: {_id: "$Sector", AvgEarnings: {$avg: "$EBITDA"}}},

])

13. db.stocks.aggregate([

{$match: {Sector: "Health Care"}},

{$sort: {"EBITDA": -1}},

{$limit: 5},

{$project: {_id: 0, Symbol: 1, Name: 1}}

])

14. db.stocks.aggregate([

{$match: {"Sector": "Information Technology", "Dividend Yield": {$gt: 0}}},

{$project: {_id: 0, Name: 1}}

])

15. db.stocks.aggregate([

{$match: {Sector: "Health Care"}},

{$sort: {"Earnings/Share": -1}},

{$limit: 10},

{$project: {_id: 0, Name: 1, "Earnings/Share": 1}}

])

16. db.stocks.aggregate([

{$match: {Sector: "Health Care"}},

{$group: {_id: "$Sector", TotalEarnings: {$sum: "$EBITDA"}}}

])

17. db.stocks.aggregate([

{$match: {Sector: "Industrials"}},

{$project: {_id: 0, Name: 1, Symbol: 1, OutstandingShares:{$divide: ["$Market Cap", "$Price"]}}},

{$sort: {OutstandingShares: -1}}

])

Answer 1

1.SELECT DISTINCT state FROM

Zips WHERE pop >= 90000;

EXPLANATION:-

Here Distinct() is used to get the distinct value in which wefirst have colums or fields and then we have the query which is pop >= 90000 here.

2.SELECT state ,SUM(pop) AS totalPop FROM Zips

WHERE state='MN'

GROUP BY state;

EXPLANATION:-

As explained in the example query , here we are using aggregate along with $group.

$sum is used inside $group.

Here, SUM() aggregate function with group by can be used .

As the result here is producing list of cities with sum of poplation in each of them.

$match is used for filtering data ,so WHERE can be used in SQL.

The resultant have field name as Totalpop ,so we used AS for renaming.

3.

SELECT city,SUM(pop) AS TotalPopulationFROM Zips

WHERE state='MO'

GROUP BY city

ORDER BY TotalPopulation DESC;

EXPLANATION:-

This one similar to 2nd query so query is almost similar only WHERE clause and grouping is done by city not state.

Also, $sort is used in mongo query so we are using ORDER BY with totalPopulation .

As ,it is {$sort: {TotalPopulation: -1}}

Here ,-1 means in descending order.

4.

SELECT city,SUM(pop) AS TotalPopulation FROM Zips

WHERE state='NY' AND city ='NEW YORK'

GROUP BY city

ORDER BY TotalPopulation DESC;

EXPLANATION:-

Similar to earlier one we are with addition change in the $match which have two conditions to be satisfies.

So, we used AND in the WHERE clause.

5.

SELECT city,COUNT(city) AS totalzips FROM Zips

WHERE state='IL'

GROUP BY city

HAVING SUM(city) >=3

ORDER BY totalzips DESC;

EXPALANTION:-

It is little different from earlier queries ,we are using { totalzips: {$sum: 1}} which means we are basically counting the number of times a city appears.

AND also we have {$match: {totalzips: {$gte: 3}}} , which makes us check the aggregated value in SQL whch we ca check only using HAVING Clause.

6.

SELECT state,COUNT(state) AS numZips FROM Zips

GROUP BY state

ORDER BY numZips

LIMIT 1;

EXPLANTION:-

Similar , to 6th query without HAVING clause and addition of LIMIT clause as we are limiting our result in mongo query.

7.

.SELECT city,SUM(pop) AS totalPop FROM Zips

GROUP BY city

ORDER BY totalPop DESC

LIMIT 1;

8.

.SELECT city,SUM(pop) AS totalPop FROM Zips

GROUP BY city

ORDER BY totalPop

LIMIT 1;

9.

.SELECT city,SUM(pop) AS totalPop FROM Zips

GROUP BY city

ORDER BY totalPop DESC

LIMIT 20;

10. SELECT Symbol, Name, Price FROM Stocks

ORDER BY Price DESC

LIMIT 10;

11. SELECT Sector, SUM(EBITDA) as TotalEarnings FROM

stocks GROUP BY Sector;

12.

SELECT Sector, AVG(EBITDA) as AvgEarnings FROM

stocks GROUP BY Sector;

13.

SELECT Symbol, Name FROM Stocks

WHERE Sector= 'Health Care'

ORDER BY EBITDA DESC

LIMIT 5;

14.

SELECT Name FROM Stocks

WHERE Sector= 'Information Technology' AND 'Dividend Yield' >0;

15.

SELECT Name,[Earnings/Share] FROM Stocks

WHERE Sector= 'Health Care'

ORDER BY [Earnings/Share] DESC

LIMIT 10;

16.

SELECT Sector,SUM(EBITDA) AS TotalEarnings FROM Stocks

WHERE Sector= 'Health Care'

GROUP BY sector;

17.

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

WHERE Sector= 'Industrials'

ORDER BY OutstandingShares DESC;

EXPLANATION for 6 to 17:-

Almost same kind of queries with little change in $sum so for $sum :1 we have used COUNT() aggregate function as simiply we are counting the number.

For rest $sum:$fieldname we used SUM() aggregate function.

For $avg we have used AVG() aggregate function in SQL along with GROUP BY.

In 17 we are getting - OutstandingShares:{$divide: ["$Market Cap", "$Price"]}

Here , "Market Cap" field is getting divided by Price field and the resultand column is named as OutstandingShares so we used ("Market Cap"/Price) AS OutstandingShares in our SQL.