mongoDB练习题

曾经课上的ADB查询练习，做个备份。
(然后发现自己依然有两个不会写[捂脸])
//
// Using the video.movieDetails collection, write the MongoDB queries that return the information below:
//

Query 01
// the number of document in the collection

db.movieDetails.find().count()

Query 02
// the first 20 documents in the collection (plain and pretty printing)

db.movieDetails.find().limit(20).pretty()

Query 03
// the first 10 documents in the collection

db.movieDetails.find().limit(10).pretty()

Query 04
// the id and the title of the first 5 movies

db.movieDetails.find({type:"movie"},{title:true,"imdb.id":true}).limit(5).pretty()

Query 05
// the title and the director of the first 5 movies

db.movieDetails.find({type:"movie"},{title:true,director:true}).limit(5).pretty()

Query 06
// the movies whose runtime is exactly 1 hour (60 minutes) - 2 methods

db.movieDetails.find({type:"movie",runtime:60},{title:true,runtime:true}).pretty()
db.movieDetails.find({type:"movie",runtime:{$eq:60}},{title:true,runtime:true}).pretty()

Query 07
// the movies whose runtime is greater than or equal to 1 hour

db.movieDetails.find({type:"movie",runtime:{$gte:60}},{title:true,runtime:true}).pretty()

Query 08
// the movies whose runtime is between 1:00 and 1:30 (included)

db.movieDetails.find({type:"movie",runtime:{$gte:60,$lte:90}},{title:true,runtime:true}).pretty()

Query 09
// the movies directed by Sergio Leone

db.movieDetails.find({type:"movie",director:"Sergio Leone"},{title:true,director:true}).pretty()

Query 10
// the movies directed by Sergio Leone with a runtime of 229 minutes - 2 methods

db.movieDetails.find({type:"movie",director:"Sergio Leone",runtime:229},{title:true,runtime:true,director:true}).pretty()
db.movieDetails.find({type:"movie",director:"Sergio Leone",runtime:{$eq:229}}},{title:true,runtime:true,director:true}).pretty()

Query 11
// the movies whose title contains a ‘t’

db.movieDetails.find({type:"movie",title:/c/},{title:true}).pretty()

Query 12
// the movies whose title contains a ‘t’ in the 4th position

db.movieDetails.find({type:"movie",title:/4t/},{title:true}).pretty()

Query 13
// the movies whose title begins with ‘T’ and ends with with ‘c’

db.movieDetails.find({type:"movie",title:/^T/,title:/c$/},{title:true}).pretty()

Query 14
// the movies whose title begins with ‘T’ and ends with with ‘c’ and contains a ‘B’ followed by a ‘i’

db.movieDetails.find({type:"movie",title:/^T/,title:/c$/,title:/Bi/},{title:true}).pretty()

Query 15
// the movies whose duration is less than 1 hour or were released before 1968

db.movieDetails.find({type:"movie",$or:[runtime:{$lte:60},year:{$lte:1968}]},{title:true,runtime:true,year:true}).pretty()

Query 16
// the movies with exactly 3 actors

db.movieDetails.find({type:"movie",actors:{$size:3}},{title:true}).pretty()

Query 17
// the movies featuring Harrison Ford

db.movieDetails.find({actors:"Harrison Ford"},{title:true,actors:true}).pretty()

Query 18
// the movies staring Harrison Ford (i.e. he’s the first in list)

db.movieDetails.find({"actors.0":"Harrison Ford"},{title:true,actors:true}).pretty()

Query 19
// the movies with Harrison Ford as the second actor

db.movieDetails.find({"actors.1":"Harrison Ford"},{title:true,actors:true}).pretty()

Query 20
// the movies featuring both Harrison Ford and Paul Freeman

db.movieDetails.find({actors:{$eq:"Harrison Ford",$eq:"Paul Freeman"}},{title:true,actors:true}).pretty()

Query 21
// the movies featuring Harrison Ford but not Carrie Fisher

db.movieDetails.find({actors:"Harrison Ford",actors:{$ne:"Carrie Fisher"}},{title:true,actors:true}).pretty()

Query 22
// the movies featuring actors whose name a ‘a’ and a ‘r’

Query 23
// same question as above, this time using 2 single-letter regular expression

Query 24
// the various ratings (field ‘rated’) in the collection

db.movieDetails.distinct("rated")

Query 25
// the movies with no awards specification

db.movieDetails.find({awards:null,{title:true,awards:true}).pretty()

Query 26
// the movies with over 10 award nominations

db.movieDetails.find({"awards.nominations":{$gt:10},{title:true,"awards.nominations":true}).pretty()

Query 27
// the greatest movie runtime (output only the runtime, not the movie description)

db.movieDetails.find({},{runtime:true,_id:false}).sort({"runtime":-1}).limit(1)

Query 28
// the smallest, average and greatest movie runtime (output only the runtime, not the movie description)

db.movieDetails.aggregate([{$group:{_id:"$type",avg:{$avg:"$runtime"},max:{$max:"$runtime"},min:{$min:"$runtime"}}}]);

Query 29
// the title of the movie with the greatest runtime, and its runtime

db.movieDetails.find({},{title:true,runtime:true,_id:false}).sort({"runtime":-1}).limit(1)

Query 30
// for each director, his/her name and the number of movies he/she directed

db.movieDetails.aggregate([{$group:{_id:"$director",sum:{$sum:1}}}]);

Query 31
// the number of movies by Sergio Leone

db.movieDetails.find({director:"Sergio Leone"}).count()

Query 32
// the number of movies whose rating (field ‘rated’) is not specified, i.e. is either missing or null

db.movieDetails.find({rated:{$exists:true,$ne:null}},{title:true,rated:true})

Query 33
// the movies sorted by rating (alphabetical order) and then runtime (longest movie first)

db.movieDetails.find({rated:{$exists:true,$ne:null}},{title:true,rated:true,runtime:true}).sort(rated:1,runtime:-1)

Query 34
// the shortest movie runtime - 2 methods

db.movieDetails.find({},{runtime:true,_id:false}).sort({"runtime":1}).limit(1)
db.movieDetails.aggregate([{$sort:{runtime:-1}},{ $limit : 1 }]);

Query 35
// the smallest, average and greatest movie runtime

db.movieDetails.aggregate([{$group:{_id:"$type",avg:{$avg:"$runtime"},max:{$max:"$runtime"},min:{$min:"$runtime"}}}]);

Query 36
// the smallest, average and greatest runtime of the movies directed by Steven Spielberg

db.movieDetails.aggregate([{$match:{director:"Steven Spielberg"}},{$group:{_id:"$type",avg:{$avg:"$runtime"},max:{$max:"$runtime"},min:{$min:"$runtime"}}}]);

Query 37
// for each director, his/her name and the number of movies he/she directed, along with the total and average movie runtime for that director

db.movieDetails.aggregate([{$group:{_id:"$director",number:{$sum:1},sum:{$sum:"$runtime"},avg:{$avg:"$runtime"}}}]);

Query 38
// same question as above, with the results sorted by the number of movies (descending), then the average runtime (ascending)

db.movieDetails.aggregate([{$group:{_id:"$director",number:{$sum:1},sum:{$sum:"$runtime"},avg:{$avg:"$runtime"}}},{$sort:{number:-1,avg:1}}])

Query 39
// the non-null ratings assigned to 20 movies or more - 2 methods (which one is best?)

db.movieDetails.aggregate([{$match:{rated:{$exists:true,$ne:null} ,runtime:{$exists:true,$ne:null}}},{$group:{_id:"rated",sum:{$sum:1}}},{$match:{sum:{$gte:20}}}])

Query 40
// the largest per-rating average runtime (nulls omitted)

db.movieDetails.aggregate([ {$match: {rated:{$exists:true,$ne:null},runtime:{$exists:true,$ne:null}}},{$group:{_id:"rated",runtime_avg_max:{$avg:"$runtime"}}},{$sort:{number:-1}},{$limit:1}])

Query 41
// the ratings assigned to the largest number of movies (nulls omitted)

db.movieDetails.aggregate([{$match:{rated:{$exists:true,$ne:null}}},{$group:{_id:"$rated",number:{$sum:1}}},{$sort:{number:-1}},{$limit:1}])