Queries to answer the questions in the project relating to the non-relational students / courses MongoDb database.
Average Age of Students
Give the MongoDB command to find the average age of students.
This is the answer that is expected:
{ "Average" : 33 }
My answer uses the following:
Using $project to get rid of the id
db.docs.aggregate([{$group:{_id:null,"Average":{$avg:"$details.age"}}},{ $project: {"_id":0} }])
{ "Average" : 33 }
Alternatively using $unset
db.docs.aggregate([{$group:{_id:null,"Average":{$avg:"$details.age"}}},{ $unset: ["_id"] }])
{ "Average" : 33 }
Honours Level
Give the MongoDB command to show the name of each course and Honours which has the value true if the course level is 8 or higher, otherwise false. The output should be sorted by name.
{ "name" : "B.A.", "Honours" : true }
{ "name" : "B.Eng.", "Honours" : false }
{ "name" : "H. Dip. in Data Analytics", "Honours" : true }
{ "name" : "H. Dip. in SW Devel", "Honours" : true }
My answer:
db.docs.aggregate([{$match: {level:{$exists:true}}}, {$project:{_id:0, name:1, "Honours":{ $eq:["$level", 8]}}},{$sort:{name:1}}])
{ "name" : "B.A.", "Honours" : true }
{ "name" : "B.Eng.", "Honours" : false }
{ "name" : "H. Dip. in Data Analytics", "Honours" : true }
{ "name" : "H. Dip. in SW Devel", "Honours" : true }
Qualified Students
Give the MongoDB command to show the number of Qualified Students i.e. those documents with a qualifications field.
{ "Qualified Students" : 6 }
my answer:
db.docs.aggregate( [ {$match: {qualifications:{$exists:true}}},
{ $group: { _id: null, "Qualified Students": {$sum: 1} } }, {$project: {_id:0}} ])
{ "Qualified Students" : 6 }
{$count: "Qualified Students"} is equivalent way to using { $group: { _id: null, "Qualified Students": {$sum: 1} } }, {$project: {_id:0}}
db.docs.aggregate([{$match: { qualifications: {$exists: true}}},
{$count: "Qualified Students"}] )
{ "Qualified Students" : 6 }
Students and their Qualifications
Give the MongoDB command to show the name of each Student and his/her qualifications. The output should be in alphabetical name order.
If the student has no qualifications the word “None” should appear:
{ "details" : { "name" : "Alan Higgins" }, "qualifications" : [ "ENG, SW" ] }
{ "details" : { "name" : "Bernie Lynch" }, "qualifications" : [ "ARTS" ] }
{ "details" : { "name" : "Brian Collins" }, "qualifications" : [ "ENG", "SW" ] }
{ "details" : { "name" : "John Smith" }, "qualifications" : [ "ARTS", "DATA" ] }
{ "details" : { "name" : "Mary Murphy" }, "qualifications" : [ "ARTS" ] }
{ "details" : { "name" : "Mick O'Hara" }, "qualifications" : [ "ENG", "DATA", "SW" ] }
{ "details" : { "name" : "Tom Kenna" }, "qualifications" : "None" }
My answer:
db.docs.aggregate([{$match: {details:{$exists:true}}},
{$project:{"details.name":1,_id:0,qualifications:{ $ifNull:["$qualifications","None"]}}},
s{$sort:{"details.name": 1}}])
```json
{ "details" : { "name" : "Alan Higgins" }, "qualifications" : [ "ENG, SW" ] }
{ "details" : { "name" : "Bernie Lynch" }, "qualifications" : [ "ARTS" ] }
{ "details" : { "name" : "Brian Collins" }, "qualifications" : [ "ENG", "SW" ] }
{ "details" : { "name" : "John Smith" }, "qualifications" : [ "ARTS", "DATA" ] }
{ "details" : { "name" : "Mary Murphy" }, "qualifications" : [ "ARTS" ] }
{ "details" : { "name" : "Mick O'Hara" }, "qualifications" : [ "ENG", "DATA", "SW" ] }
{ "details" : { "name" : "Tom Kenna" }, "qualifications" : "None" }
