CUNY Medgar Evers College NoSQL MongoDB Worksheet

Follow the below steps:

1. Start off by deleting the entire collection cars.Take a screenshot of the query as well as the list of your collections in Atlas to be sure this collection has been deleted.
2. Next, run the following query to recreate the cars collection.The following includes more cars than before.

db.cars.insertMany([ { make: “Hyundai”, model: “Santa Fe”, price: 8000, year: 2003, used: true, color: “Black” }, { make: “BMW”, model: “ALPINA B6 Gran Coupe”, price: 124300, year: 2017, used: false, color: “Mediterranean Blue Metallic” }, { make: “Subaru”, model: “Crosstrek 2.0i Premium”, price: 22595, year: 2014, used: true, color: “Sunshine Orange” }, { make: “Ford”, model: “F-350 XL”, price: 33705, year: 2017, used: false, color: “Race Red” }, { make: “Toyota”, model: “Acura MDX”, price: 28800, year: 2014, used: true, color: “Graphite Luster Metallic” }, { make: “BMW”, model: “5 Series 535i Sedan”, price: 18995, year: 2013, used: true, color: “Space Gray Metallic” }, { make: “Ford”, model: “Escape”, price: 7480, year: 2011, used: true, color: “Sterling Grey Metallic” }, { make: “Subaru”, model: “Impreza”, price: 18495, year: 2018, used: false, color: “Crimson Red Pearl” }, { make: “Toyota”, model: “Yaris”, price: 15635, year: 2018, used: false, color: “Super White” }, { make: “Honda”, model: “Civic LX”, price: 14999, year: 2016, used: true, color: “Crystal Black Pearl” }, { make: “Volkswagen”, model: “Jetta 1.4T S”, price: 19495, year: 2018, used: false, color: “Silk Blue Metallic” }]);

1. Create an index on the price field.
2. Create an index on the used field for the cars collection.
3. Find and delete all documents with a year before 2012.Be sure to do a find with your filtering criteria first to be sure you’re about to delete the correct documents.
4. Delete the first document that is a BMW.
5. Drop the index created on the non-used cars created above.

Part 2

Below is a real-life scenario. Please read this scenario and run the appropriate queries needed.

You are currently working for a car dealership. They sell both used and new cars. The company would like to easily and efficiently search through their cars using the “make” of the car. Recently, they made the searching efficient using the price of the car, but that is no longer needed since they will now be using the make of the vehicles. Please reflect that in the database. Also, the company has decided to no longer sell Volkswagens and has already sold the last Volkswagen on the lot so they would like you to reflect that in the database as well. Part 1:
•
Find all movies that are longer than 2 hours
•
Find all movies that do not have Tom Hanks as a lead actor
•
Find all movies that were directed by Mel Brooks
•
Find all movies that are not considered Drama
•
Find all movies that are longer than 90 minutes but less than 2 hours
•
Find all movies that are considered Action or Adventure and return only the title
of the movie
Part 2:
Below is a real-life scenario. Please read this scenario and run the appropriate
queries needed.
The company you work for is having a movie night for all employees, and they are
trying to find the perfect movie for movie night. The problem is, so many people
have different opinions, and they want to make everyone happy. Sally has very
poor eyesight so cannot watch any foreign films but likes comedies and dramas.
Robert hates Jodie Foster and refuses to watch any movies with her in it, but likes
comedies and horror films. Stewart has somewhere to be after movie night, so he
can’t watch a movie longer than 2 hours; he prefers thrillers and comedies.
After running your query(ies), what movie is going to be played at movie night?
db.createCollection(“appusers”);
db.appusers.find({ _id : { $eq : 1} })
db.appusers.find({ _id : { $ne : 1} })
db.appusers.find({ _id : { $gt : 1} })
db.appusers.find({ _id : { $gte : 2} })
db.appusers.find({ _id : { $lt : 2} })
db.appusers.find({ _id : { $lte : 2} })
db.inventory.insertMany([
{ item: “journal”, qty: 25, size: { h: 14, w: 21, uom: “cm” }, status: “A”,
price: 16.49, sale: true },
{ item: “spiral notebook”, qty: 50, size: { h: 8.5, w: 11, uom: “in” }, status:
“A”, price: 4.79, sale: false },
{ item: “paper”, qty: 100, size: { h: 8.5, w: 11, uom: “in” }, status: “D”,
price: 6.99, sale: true },
{ item: “day planner”, qty: 75, size: { h: 22.85, w: 30, uom: “cm” }, status:
“D”, price: 25.29, sale: true },
{ item: “calendar”, size: { h: 21.75, w: 17, uom: “in” }, status: “B”, price:
5.99, sale: false },
{ item: “scissor”, qty: 15, size: { h: 8, w: 4.25, uom: “in” }, status: “B”,
price: 11.59, sale: true },
{ item: “tape dispenser”, qty: 5, size: { h: 3, w: 7, uom: “in” }, status: “A”,
price: 3.09, sale: true }
]);
db.inventory.find( { qty: { $in: [ 5, 15 ] } } )
db.inventory.find( { qty: { $nin: [ 5, 15 ] } } )
db.inventory.find( { $and: [ { price: { $ne: 6.99 } }, { price: { $exists: true }
} ] } )
db.inventory.find( { $or: [ { qty: { $lt: 20 } }, { price: 16.49 } ] } )
db.inventory.find( {
$and : [
{ $or : [ { price : { $eq : 4.79 } }, { price : { $eq : 3.09 } } ] },
{ $or : [ { sale : true }, { qty : { $lt : 20 } } ] }
]
} )
db.inventory.find( { price: { $not: { $gt: 4.80 } } } )
db.inventory.find( { $nor: [ { price: { $gte : 5.99 } }, { sale: { $eq : true } }
] } )
{ field: { $exists: } }
db.inventory.find( { qty: { $exists: true, $nin: [ 5, 15 ] } } )
{ field: { $type: | } }
//Array Operators
//$all
{ tags: { $all: [ “ssl” , “security” ] } }
{ $and: [ { tags: “ssl” }, { tags: “security” } ] }
//$elemMatch
{ results: { $elemMatch: { $gte: 80, $lt: 85 } } }
//$size
db.collection.find( { field: { $size: 2 } } );
//Projection Document
db.collectionName.find({query, projection})
db.collection.find( { query }, { field: , field : , etc} )
db.appusers.find( { _id : 2}, { firstName: 1, lastName: 0 })
db.appusers.find( { _id : 25}, { firstName: 1 })
db.appusers.find({}, {firstName : 1, lastName : 1})
db.appusers.find({}, { _id : 0})
//Key Terms
// db.collectionName.find({})
Basic query that will find all documents and
their fields in the specified collection.
// Query
Specifies a selection filter using query operators and is optional.
// Projection
Specifies certain fields to return in the documents that match
the query filter and is optional. To return all fields in the matching documents,
omit this parameter.
// Cursor
A pointer to the result set of a query. This is the output of
whatever documents you are finding and selecting from.
// $eq This query operator will query documents that are equal to the specified
value.
// $ne Matches all documents that are not equal to the specified value.
// $gt Matches all documents that are greater than the specified value.
// $gte Matches all documents that are greater than or equal to the specified
value.
// $lt Matches all documents that are less than the specified value.
// $lte Matches all documents that are less than or equal to the specified value.
// $in Matches any of the values within an array.
// $nin Matches none of the values within a specified array.
// $and Performs a logical AND operation on an array of two or more expressions
and selects the documents that satisfy all the expressions in the array.
// $or Performs a logical OR operation on an array of two or more expressions
and selects the documents that satisfy at least one of the expressions.
// $not Performs a logical NOT operation on the specified operator-expression and
selects the documents that do not match the operator-expression, including
documents that do not contain the field.
// $nor Performs a logical NOR operation on an array of one or more query
expression and selects the documents that fail all the query expressions in the
array.
// $exists Matches documents that contain a certain field, including documents
that are null.
// $type
Returns documents where the BSON type of the field matches the BSON
type passed.
// $all Matches all documents where the value of a field is an array that
contains all the specified elements.
// $elemMatch
Matches documents that contain an array field with at least one
element that matches all the specified query criteria.
// $size
Selects documents that have an array with a specified size.
// Projection Document Allows the returning of only some of the fields in the
document, rather than the entire thing. Defined by either 1, which indicates that
that field should be returned, or 0, which indicates to not return that field.
db.movies.insertMany([
{_id: 1, title: “Wonder Woman”, director: “Patty Jenkins”, yearReleased:
2017, leadActors: [“Gal Gadot”, “Chris Pine”, “Robin Wright”], lengthInMin: 141,
genre: [“Action”, “Adventure”, “Fantasy”], MPAA: “PG-13” },
{_id: 2, title: “Cloud Atlas”, director: “Tom Tykwer”, yearReleased:
2012, leadActors: [“Tom Hanks”, “Halle Berry”, “Hugh Grant”, “Jim Broadbent”,
“Jim Sturgess”, “Hugo Weaving”], lengthInMin: 172, genre: [“Action”, “Drama”,
“Mystery”], MPAA: “R” },
{_id: 3, title: “Pan’s Labyrinth”, director: “Guillermo del Torro”,
yearReleased: 2006, leadActors: [“Ivana Baquero”, “Ariadna Gil”, “Sergi Lopez”],
lengthInMin: 118, genre: [“Drama”, “Fantasy”, “War”, “Foreign”], MPAA: “R” },
{_id: 4, title: “Gone With the Wind”, director: “Victor Fleming”,
yearReleased: 1939, leadActors: [“Clark Gable”, “Vivien Leigh”, “Thomas
Mitchell”, “Olivia de Havilland”, “Leslie Howard”], lengthInMin: 238, genre:
[“Drama”, “History”, “Romance”], MPAA: “G” },
{_id: 5, title: “Spaceballs”, director: “Mel Brooks”, yearReleased: 1987,
leadActors: [“Mel Brooks”, “John Candy”, “Rick Moranis”, “Bill Pullman”, “Daphne
Zuniga”], lengthInMin: 96, genre: [“Adventure”, “Comedy”, “Sci-fi”], MPAA: “PG”
},
{_id: 6, title: “Silence of the Lambs”, director: “Jonathan Demme”,
yearReleased: 1991, leadActors: [“Jodie Foster”, “Anthony Hopkins”, “Anthony
Heald”], lengthInMin: 118, genre: [“Crime”, “Drama”, “Thriller”], MPAA: “R” },
{_id: 7, title: “American History X”, director: “Tony Kaye”,
yearReleased: 1998, leadActors: [“Edward Norton”, “Edward Furlong”, “Beverly
D’Angelo”, “Ethan Suplee”], lengthInMin: 119, genre: [“Crime”, “Drama”], MPAA:
“R” },
{_id: 8, title: “Psycho”, director: “Alfred Hitchcock”, yearReleased:
1960, leadActors: [“Anthony Perkins”, “Vera Miles”, “John Gavin”, “Janet Leigh”],
lengthInMin: 109, genre: [“Horror”, “Mystery”, “Thriller”], MPAA: “R” },
{_id: 9, title: “The Pianist”, director: “Roman Polanski”, yearReleased:
2002, leadActors: [“Adrien Brody”, “Emilia Fox”, “Michal Zebrowski”, “Ed
Stoppard”], lengthInMin: 150, genre: [“Biography”, “Drama”, “Music”], MPAA: “R”
},
{_id: 10, title: “Gladiator”, director: “Ridley Scott”, yearReleased:
2000, leadActors: [“Russell Crowe”, “Joaquin Phoenix”, “Connie Nielsen”, “Oliver
Reed”], lengthInMin: 155, genre: [“Action”, “Adventure”, “Drama”], MPAA: “R” },
])
//Find movies that are 2 hours long:
db.movies.find({ lengthInMin: { $gt : 120 } });
//Find movies that do not have Tom Hanks
db.movies.find({ leadActors: { $ne : “Tom Hanks”} });
//Find movies that was directed by Mel Brooks:
db.movies.find({ director: “Mel Brooks” });
//Find all movies that are not drama:
db.movies.find({ genre: { $ne : “drama”} });
//Find all movies that are longer than 90 minutes but less than 2 hours:
db.movies.find({ lengthInMin: { $gt: 90, $lt: 120} });
//Find all movies that are considered Action or Adventure and return only the
title of the movie:
db.movies.find(
{ $and: [{ genre: “Action” }, { genre : “Adventure” }] },
{ title: 1, _id: 0 }
);
//Part B:
db.movies.find( {
$and: [ { lengthInMin: { $lt:120 } }, { leadActors: { $ne: “Jodie Foster”} },
{genre: {$ne: “Foreign”} }, {genre: {$in: [“Comedy”, “Drama”, “Thriller”,
“Horror”] } } ] } );
//Updating Documents:
db.[collection].updateOne({filter}, {update}, {options})
db.appusers.updateOne({“_id” : 1}, { $set : { “middleName” : “Gertrude”}}, {
upsert : true})
//Update Many Documents:
db.appusers.updateMany({“firstName” : “Tommy”}, { $set : { “middleName” :
“Henry”}}, { upsert : true})
//Using Update()
db.[collection].update( {filter}, {update}, {upsert : true/false, multi:
true/false})
//Update Options:
db.[collection].updateOne({filter}, {update}, {options})
//Upsert
db.appusers.updateOne({“firstName” : “Tommy”}, { $set : { “middleName” :
“Henry”}}, { upsert : true})
db.appusers.updateOne({“firstName” : “Coderboy”}, { $set : { “middleName” :
“Cole”}}, { upsert : true})
//Multi
db.[collection].update( {filter}, {update}, {upsert : true/false, multi:
true/false})
//Update Operators: $currentDate:
{ $currentDate : { : , … } }
db.appusers.updateOne({ _id: 1 }, { $currentDate: { signedUp: true } });
//$inc:
{ $inc: { : , : , … } }
{
_id: 1,
quantity: 10,
metrics: {
orders: 2,
ratings: 3.5
}
}
db.products.update({ _id: 1 }, { $inc: { quantity: -2, ‘metrics.orders’: 1 } });
db.products.update({ _id: 1 }, { $inc: { quantity: -2, orders: 1 } });
//$min:
{ $min: { : , …} }
{ _id: 1, highScore: 800, lowScore: 200 }
db.scores.update({ _id: 1 }, { $min: { lowScore: 150 } });
{ _id: 1, highScore: 800, lowScore: 150 }
db.scores.update({ _id: 1 }, { $min: { lowScore: 250 } });
//$max:
{ $max: { : , …} }
db.scores.update({ _id: 1 }, { $max: { highScore: 950 } });
{ _id: 1, highScore: 950, lowScore: 200 }
//$mul:
{ $mul: { field: } }
db.inventory.updateMany({ status: ‘A’ }, { $mul: { qty: 2 } });
//$set:
{ $set: { : , … } }
db.inventory.update({ item: ‘journal’ }, { $set: { status: ‘B’ } });
//$unset:
{ $unset: { : “”, … } }
db.inventory.update({ item: ‘journal’ }, { $unset: { status: ” } });
//rename:
{$rename: { : , : , … } }
db.inventory.updateMany({}, { $rename: { qty: ‘quantity’ } })
//Delete All Documents:
db.cars.insertMany([
{
make: ‘Hyundai’,
model: ‘Santa Fe’,
price: 8000,
year: 2003,
used: true,
color: ‘Black’
},
{
make: ‘BMW’,
model: ‘ALPINA B6 Gran Coupe’,
price: 124300,
year: 2017,
used: false,
color: ‘Mediterranean Blue Metallic’
},
{
make: ‘Subaru’,
model: ‘Crosstrek 2.0i Premium’,
price: 22595,
year: 2014,
used: true,
color: ‘Sunshine Orange’
},
{
make: ‘Ford’,
model: ‘F-350 XL’,
price: 33705,
year: 2017,
used: false,
color: ‘Race Red’
},
{
make: ‘Toyota’,
model: ‘Acura MDX’,
price: 28800,
year: 2014,
used: true,
color: ‘Graphite Luster Metallic’
},
{
make: ‘Volkswagen’,
model: ‘Jetta 1.4T S’,
price: 19495,
year: 2018,
used: false,
color: ‘Silk Blue Metallic’
}
]);
db.cars.deleteMany({})
//Delete Documents with Filter:
db.cars.deleteMany({ used : true })
db.cars.deleteMany({ price: { $lt : 30000 }})
//Delete One Document:
db.cars.deleteOne({ used : true })
db.cars.deleteOne({ price: { $lt : 30000 }})
//Find and Delete:
db.cars.findOneAndDelete({ price: 8000 });
//Delete a Collection:
db.collectionName.drop();
db.products.drop();
//INDEXES:
//Create an Index:
db.collection.createIndex( , )
//Single Field Indexing:
db.records.createIndex( { score: 1 } )
db.records.createIndex( { score: -1 } )
//Compund Indexing:
db.collection.createIndex( { : , : , … } )
{
“_id”: ObjectId(…),
“item”: “Banana”,
“category”: [“food”, “produce”, “grocery”],
“location”: “4th Street Store”,
“stock”: 4,
“type”: “cases”
}
db.products.createIndex( { item: 1, stock: 1 } )
db.products.find( { item: “Banana” } )
db.products.find( { item: “Banana”, stock: { $gt: 5 } } )
//Create Index on Embedded Field:
{
“_id”: ObjectId(“570c04a4ad233577f97dc459”),
“score”: 1034,
“location”: { state: “NY”, city: “New York” }
}
db.records.createIndex( { location.state: 1 } )
//Create Index on Embedded Document:
{
“_id”: ObjectId(“570c04a4ad233577f97dc459”),
“score”: 1034,
“location”: { state: “NY”, city: “New York” }
}
db.records.createIndex( { location: 1 } )
db.records.find( { location: { city: “New York”, state: “NY” } } )
//Multikey Indexes:
[‘Apples’, ‘Cherries’, ‘Pineapple’];
[
{ item: ‘Picture Frame’, price: 3 },
{ item: ‘Canvas’, price: 15 },
{ item: ‘Pencils’, price: 4 },
];
//Viewing your Indexes:
db.cars.createIndex({ make: 1, model: -1 });
db.cars.getIndexes();
//Dropping Indexes:
db.collectionName.dropIndex()
db.collectionName.dropIndex( { } )
db.collectionName.dropIndexes();
// db.collectionName.deleteMany({}) Will remove all documents from a collection.
Can also delete many documents from a collection based on the filter.
// db.collectionName.deleteOne({ })
Will remove one document from a
collection based on the filter.
// db.collectionName.findOneAndDelete({ }) Will return the current
document before deleting based on a filter.
// db.collectionName.drop() Drops the collection specified.
// db.collectionName.createIndex( ,
)
This query will only create an index if an index with the same specifications
does not exist already.
// db.collectionName.getIndexes()
Returns an array of all indexes in the
specified collection.
// db.collectionName.dropIndex()
Drops an index in a specified collection. Can
use either the index name or the specifications on which the index was created.
// db.collectionName.dropIndexes() Drops all indexes in the specified
collection.
db.cars.insertMany([
{
make: “Hyundai”,
model: “Santa Fe”,
price: 8000,
year: 2003,
used: true,
color: “Black”
},
{
make: “BMW”,
model: “ALPINA B6 Gran Coupe”,
price: 124300,
year: 2017,
used: false,
color: “Mediterranean Blue Metallic”
},
{
make: “Subaru”,
model: “Crosstrek 2.0i Premium”,
price: 22595,
year: 2014,
used: true,
color: “Sunshine Orange”
},
{
make: “Ford”,
model: “F-350 XL”,
price: 33705,
year: 2017,
used: false,
color: “Race Red”
},
{
make: “Toyota”,
model: “Acura MDX”,
price: 28800,
year: 2014,
used: true,
color: “Graphite Luster Metallic”
},
{
make: “BMW”,
model: “5 Series 535i Sedan”,
price: 18995,
year: 2013,
used: true,
color: “Space Gray Metallic”
},
{
make: “Ford”,
model: “Escape”,
price: 7480,
year: 2011,
used: true,
color: “Sterling Grey Metallic”
},
{
make: “Subaru”,
model: “Impreza”,
price: 18495,
year: 2018,
used: false,
color: “Crimson Red Pearl”
},
{
make: “Toyota”,
model: “Yaris”,
price: 15635,
year: 2018,
used: false,
color: “Super White”
},
{
make: “Honda”,
model: “Civic LX”,
price: 14999,
year: 2016,
used: true,
color: “Crystal Black Pearl”
},
{
make: “Volkswagen”,
model: “Jetta 1.4T S”,
price: 19495,
year: 2018,
used: false,
color: “Silk Blue Metallic”
}
]);
db.cars.deleteMany({})
// Create an index on the price field.
//INDEXES:
//Create an Index:
// db.collection.createIndex( , )
db.Cars.createIndex({ “Price”: 8000 })
db.Cars.createIndex({ “Price”: 124300})
db.Cars.createIndex({ “Price”: 22595})
db.Cars.createIndex({ “Price”: 33705})
db.Cars.createIndex({ “Price”: 28800})
db.Cars.createIndex({ “Price”: 18995})
db.Cars.createIndex({ “Price”: 18495})
db.Cars.createIndex({ “Price”: 18995})
db.Cars.createIndex({ “Price”: 15635})
db.Cars.createIndex({ “Price”: 14999})
db.Cars.createIndex({ “Price”: 19495})
// Create an index on the used field for the cars collection.
// Find and delete all documents with a year before 2012.
// Be sure to do a find with your filtering criteria first to be sure you’re
about to delete the correct documents.
// Delete the first document that is a BMW.
// Drop the index created on the non-used cars created above