Exercises

CRUD

insert() exercises

Exercise

  • Insert one document to pypeople collection

    • _id:0, name: Jisu, age: 24

  • 결과

{'_id': 0, 'age': 24, 'name': 'Jisu’}

  • Insert multiple document

    • _id: 1, name: Deayoung, age: 51

    • _id: 2, name: Youngjin, age: 24

    • Print all documents

  • 결과

 [{'_id': 0, 'age': 24, 'name': 'Jisu’},
  {'_id': 1, 'age': 51, 'name': 'Deayoung’},
  {'_id': 2, 'age': 24, 'name': 'Youngjin'}]

find() Exercises

Exercise 1

Preliminary

  • Import people.json into lab4.people (i.e. database name is lab4 and collection name is people)

Questions

  • Find the documents whose age under 22.

  • Find the documents whose age is between 22 and 26 (inclusive).

  • Find the document whose name is “Lee” suppressing the profile.

  • Find the documents whose name is not Lee.

  • Find the documents whose name is “Lee” or “Park”.

  • Find the documents whose name is not “Kim” or “Park” or “Lee”.

Exercise 2

Preliminary

  • Import inventory.json into lab4.inventory

Questions

  • Find documents where the tags field contains below elements.

    • appliance, school, and book.

    • Hint: recall $all operator

  • Find all documents but select at most three items in the tags field.

    • Hint: recall $slice operator

  • Find all documents but select items from the second to the fourth in the tags field.

    • Hint: recall $slice operator with [skip, limit]

  • Find documents that have brown color in the qty field.

    • Hint: recall $elemMatch operator

Exercise 3

Preliminary

  • Import store.json to the store collection

Questions

  • Find documents where buyer's name is "J.S. Lee"

    • Hint: Querying embedded document.

  • Print purchased items where a name in a document is "iPhone Xs"

    • Hint: Querying embedded document, Projection

  • Print the buyer’s purchase method where items contain the document that price is more than 2000$

    • Hint: < array.field >, Projection

  • Find documents that don’t have carrier as key in attribute where the name is "Apple Watch Series 4"

    • Hint: $elemMatch, $exists

Exercise 4

Preliminary

  • Import store.json to the store collection

    • Same with Exercise 3!

Questions

  • Find documents where buyer's name is "J.S. Lee"

    • Hint: Querying embedded document.

  • Print purchased items where a name in a document is "iPhone Xs".

    • Hint: Querying embedded document, Projection

  • Print the buyer’s purchase method where items contain the document that price is more than 2000$.

    • Hint: <array.field>, Projection

  • Find documents that don’t have carrier as key in attribute where the name is "Apple Watch Series 4"

    • Hint: $elemMatch, $exists

Exercise 5

Preliminary

  • Import store.json to the store collection

    • Same with Exercise 3!

Questions

  • Get the first three documents and print buyers field only.

  • Skip a document and then print two documents

  • Count documents in the store collection.

Exercise 6

Preliminary

  • Using grade.json (import it using mongoimport)

Questions

  • The grade’s document can have among three types:

    • quiz, exam or homework

  • Student id (sid) is from 0 to 99

  • A document is lost, which contains a quiz score 1. print lost document’s sid. 2. Insert the quiz score (80) to the corresponding sid 3. print the three students that have the highest quiz score.

  • A simple example of the dataset looks like ...

Exercise 7

Preliminary

  • Using grade.json (import it using mongoimport)

    • Same with exercise 6

Questions

  • Write the score searching script

    • Search documents by filtering type and score fields

Results

update() Exercise

Set a 'location' field to 'Seoul' to a document.

  • The document has a 'name‘, 'Deayoung‘.

  • Set a 'location' field to 'Suwon' to documents.

    • The documents have a 'age‘, 24.

delete() exercise

Delete documents

    • Remove documents that have a 'location‘, 'Suwon‘.

  • Print all documents

​

explain() Exercise

  • Print a query plan using executionStats corresponding to the query that finds writer, Kim.

  • Using blog.json

CRUD(Advanced)

  • Use a ex1 database and imdb collection.

  • We are going to make a movie database like IMDb.

  • Each movie has following information.

    • title: String

    • director: String

    • genre: List(String)

    • score: Double

  • Insert a movie

    • Write a script that inserts movie information to a movie collection.

    • Type the movie information as follows:

    • Movie Title: Avengers: Infinity War

      Director: Anthony Russo and Joe Russo

      Genre: Action, Adventure, Drama

      score: 8.6

      [INFO] Success to insert!

    • Plain: Terminal output

    • Bold: User input

    • Hint: use a input() function

  • Confirm that the insertion is operated properly.

  • Print only movie’s title(s)

  • Insert three movies more. Use insert_many

    • Movie Title: Bohemian Rhapsody

      Director: Bryan Singer

      Genre: Biography, Drama, Music

      score: 8.1

      Quit (Y/n)? n

    • Movie Title: Happy Death Day 2U

      Director: Christopher Landon

      Genre: Drama, Horror, Mystery

      score: 6.6

      Quit (Y/n)? n

    • Movie Title: Spider-Man: Into the Spider-Verse

      Director: Bob Persichetti, Peter Ramsey

      Genre: Animation, Action, Adventure

      score: 8.6

      Quit (Y/n)? Y

      [INFO] Success to insert!

  • Confirm that the insertion is operated properly.

  • Print only movie’s title(s)

  • Update movie information

    • Querying movie title and update the information

    • [INFO] Enter the information which you want to edit

      Movie Title: Avengers: Infinity War

      Director: Anthony Russo and Joe Russo

      Genre: Action, Adventure, Drama

      score: 8.6

      [INFO] Success to update!

    • Plain: Terminal output

    • Bold: query

    • Italic: update

  • Print all documents

  • Remove movie (Plain: Output, Bold: User input)

  • Remove a document

    • [INFO] Enter the title which you want to remove

      Movie Title: Happy Death Day 2U

      [INFO] Are you sure? (Y/n) Y

      [INFO] Deleted

  • Result

Index

Exercise 1

  1. Create an index in ascending order for the writer field and get the index information. 😀

  2. Again, print a query plan using executionStats corresponding to the query that finds a writer, Kim.

  3. Create the compound index on id in descending order and writer in ascending order.

  4. Drop all indexes on the blog collection.

Exercise 2

  • Index 생성

    • Create a unique index to doc_id in ascending order

    • Try to build a unique index to line_num

    • Create a sparse index to intersect in ascending order

    • Create a unique and sparse index to intersect_id in ascending order

    • Check the index information

  • Compare the results below two cases. (metro collection)

    • Show the first two documents, sorting with doc_id in ascending order.

    • Using intersect index by hint(), show the first two documents

    • Sort doc_id in ascending order

    • Use explain() above each case (executionStats).

    • No Index

    • Indexed

GeoSpatial Index

Exercise 1

  • Create a geospatial index (2dsphere) for the states collection

    • Get information on states collection’s indexes

  • Remove the geospatial index from the states collection

    • Get information on states collection’s indexes

Exercise 2

  • Use the restaurant collection in the geo database

  1. Create a valid query and print the query plan using executionStats•Find documents between '10200‘ and '10280‘ in zipcode

  2. Create an index to zipcode (Hint: Embedded Document)

  3. Execute #1, again.

  • No Index

  • Indexed

  • Use the restaurant collection in the geo database

  1. Create a query and print the query plan using executionStats•Find documents that have at least the grade is ‘B’ in the grades.grade field.

  2. Create an index to grade (Hint: Indexing on Array)

  3. Execute #1, again.

  • No Index

  • Indexed

Exercise 3

  • Find international airports in the state that has the above zip code (10044)

    • Sorting the name in ascending order

    • Print state’s name and airport name each.

      • Hint: states and small_zip collections

  • Find Korean restaurants that are located at less 2km from international airports.

    • Warn: should create 2dsphere index to address for restaurants collection.

  • Find states that are intersect to the flight path (trajectory).

    • Print names including source and destination states.

  • Make Geojson format whose type is LineString.

    • Find the two coordinates (LAX, DTW) Result

  • Result

Exercise 4

  • Using the restaurants collection.

  • Find restaurants whose names include "Kimchi".

  • Print only name, zipcodeand address

  • Using the restaurants and states collections.

  • Find restaurants that have "NolbuRestaurant＂(restaurants collection)

  • Print the state (states collection)

  • Using restaurants.json

  • Among the "Korean" restaurants, find a restaurant that has "Ramen"but "Izakaya“.

  • Then, find a restaurant near above restaurant in 150m.

Text Index

Exercise 1

  • Using restaurants

  • Build a text index to the 'name' field.

  • Find restaurants whose names include 'Kyochon‘ Use text index

  • Print only borough and name fields

Exercise 2

  • Take a look the index information in map and countries collections.

  • Find universities in Korea.

    • Get geometry information in the countries collection.

    • keyword -properties.admin: “South Korea”

  • Return ten documents with sorting by “properties.name”, and show the name only.

    • Note that there is no ‘properties.name’ in a document; therefore, remove the document.

  • Search the ‘properties.name’ that includes ‘서울대입구’

  • Using regular expression ($regex) or a text index.

  • Sort properties.name in ascending order and display only a name field as below.

Aggregation Framework

Exercise 1

  • Use restaurants.json

  • Using aggregation framework, print documents below.

    1. Find Korean cuisine

    2. Group by borough and count the cuisines.

Exercise 2

  • Using aggregation framework, print documents below.

    1. Find Korean cuisine

    2. Unwind grades

    3. Group by borough and grade in grades, and count the document

    4. Sort by count and print five documents

Exercise 3

  • Find international airports in California using aggregate framework

  1. states collection

    • Get the California location (Polygon)

  2. airports collection

    • Find “intl” in name field (text)

    • Find airports in the California

    • Project name, type and code (w/o _id)

    • Sort by name (ascending order) and code (descending order)

Exercise 4

  • Find Korean restaurants that are located at less 2km from international airports (use the type field).

    • Warn: should create 2dsphere index to address for restaurants collection.

Exercise 5

  • Print the average, minimum, and maximum of passengers(ride_pasgr_numfield) of each subway line.

  • Hint: group by the ‘line_num’ field and use $max, $min, and $avg.

  • Result

Exercise 6

  • Print the total number of passengers (ride_pasgr_numfield) during 2017-12-21 ~ 2017-12-28.

  • ‘use_dt’ is a stringtype.

  • Hint: use $match,$gt$lt, $sum, $sort

  • Result

PreviousAggregation FrameworkNextAdministration

Last updated

Was this helpful?