Database Topics Question Bank

November 20: Quiz: MongoDB Concepts and Shell Operations

Question 1: JSON Data Stores Concepts

Describe the features and advantages of using JSON data stores like MongoDB for application development.
Lecture Answer:
JSON (JavaScript Object Notation) data stores like MongoDB provide a flexible, schema-less data model, which allows developers to store records without a pre-defined structure. This can greatly simplify the process of data ingestion, especially when dealing with diverse datasets or rapidly evolving data models.
The main features and advantages of using JSON data stores include:
Flexibility: Since there's no predefined schema, you can easily change the structure of your data as your application evolves without the need for data migrations.
Because JSON is text: You can programmatically generate text => change the shape of your Data Container programmatically, dynamically at runtime.
Scalability: MongoDB can handle a large volume of data and offers horizontal scalability with its sharding feature.
Developer-friendly: The JSON format is native to JavaScript and user-friendly, making it a good choice for developers, especially those working with JavaScript frameworks.
Rich Query Language: MongoDB provides a powerful query language that supports CRUD operations and data aggregation pipelines
High Performance: Indexing, replication, and ad-hoc queries in MongoDB contribute to high performance.
Consider MongoDB for scenarios where your data is document-centric, semi-structured, or when rapid development and iteration are required.

Question 2: BSON in MongoDB

Explain the term BSON and how it relates to JSON and MongoDB.
Lecture Answer:
BSON stands for Binary JSON and is a binary-encoded serialization of JSON-like documents. MongoDB uses BSON as its data storage and network transfer format, enabling it to support additional data types, such as date and binary types, which are not natively in JSON. BSON is designed to be efficient in space, but also traversable and queryable. While JSON is human-readable, BSON is optimized for speed, space, and flexibility.
Here's how BSON relates to JSON and MongoDB:
BSON extends the JSON model to provide additional data types and to be efficient for encoding and decoding within different languages.
MongoDB stores data records as BSON documents, which provides a powerful way to store data that can vary in structure without sacrificing data access speed.

Question 3: MongoDB Shell Query

Write a MongoDB shell command to find all documents in the customers collection where the age is greater than 25.
Lecture Code Example:
db.customers.find({ age: { $gt: 25 } });
This command queries the customers collection, using the $gt (greater than) operator to filter the documents, returning only those where the age field value is greater than 25.

Question 4: Indexing in MongoDB

What is indexing in MongoDB, and why is it important? Provide an example of creating an index on a lastName field in a users collection.
Lecture Answer:
Indexing in MongoDB serves the same purpose as in other database systems: it improves the efficiency of operations by allowing the database to search and sort through the documents more quickly. Without indexing, MongoDB would have to perform a collection scan, which is inefficient for large datasets.
Indexes are important for:
Performance: They help increase the speed of search operations in a database.
Query Optimization: Indexes can significantly reduce the amount of data that needs to be scanned for a query.
Sorting: Indexes can handle the sorting of query results, reducing the need for in-memory sorting.
Lecture Code Example:
db.users.createIndex({ lastName: 1 });
The code above creates an ascending index on the lastName field of the users collection, which assists in quickly locating and sorting documents by the lastName field.

Question 5: Aggregation Operations

Using the MongoDB shell, write an aggregation pipeline to count the number of documents in the orders collection for each unique customerId.
Lecture Code Example:

{ $group: { _id: "$customerId", totalOrders: { $sum: 1 } } }
This aggregation pipeline groups the documents in the orders collection by customerId, and counts the number of orders for each customerId using the $sum accumulator.

Question 6: MongoDB Server Operation

Describe the process of initiating and connecting to a MongoDB server instance using the MongoDB shell. Include the steps to start the server on a custom port and to connect using the MongoDB shell.
Lecture Answer:
Starting a MongoDB server involves running the mongod executable, optionally specifying configuration parameters such as the data directory and port number.
To start the server on a custom port, use the --port option:
mongod --port 12345 --dbpath /path/to/data/directory
This command starts the MongoDB server mongod and sets it to listen on port 12345. The --dbpath option is used to specify the data directory where MongoDB will store its files.
To connect to the MongoDB server using the shell, you use the mongo command along with the custom port:
mongo --port 12345
This command launches the MongoDB shell and connects it to the server running on port 12345. Once connected, you can start issuing commands to interact with the database.
The steps above are crucial for managing MongoDB server instances, whether for development, testing, or production environments. They provide the control needed to start, manage, and connect to MongoDB.
1. What is the primary purpose of using JOINs in SQL?
A. To increase the performance of the database.
B. To combine rows from two or more tables based on a related column to create new information from the facts implicit in the tables.
C. To filter out unwanted data from a single table.
D. To create a backup of the existing data.
Explanation: JOINs are used in SQL to combine rows from two or more tables based on a related column between them. This allows for data from multiple tables to be queried simultaneously.
2. Which type of JOIN returns only the rows where there is a match in both the tables?
D. INNER JOIN: this is all you have seen up to now.
Explanation: An INNER JOIN returns rows when there is a match in both tables. If there's no match, the row is not included in the result set.
3. If you want to retrieve all rows from the left table and only the matching rows from the right table, which JOIN would you use?
Explanation: A LEFT JOIN returns all rows from the left table and the matching rows from the right table. If there's no match, the result is NULL for columns from the right table.
4. In a correlated subquery, what distinguishes it from a regular subquery?
A. It can only be used with the JOIN clause.
B. It references columns from the outer query.
C. It can only return a single value.
D. It doesn't require any relationship between tables.
Explanation: A correlated subquery is defined by its reference to columns from the outer query. For each row processed by the outer query, the subquery is executed using the outer query's current row values.
5. Which JOIN would you use if you wanted to display all records from both tables, even if there's no match between the columns?
Explanation: A FULL JOIN returns all rows when there's a match in one of the tables. It combines the results of both LEFT and RIGHT JOINs.
6. When using a correlated subquery, for each row processed by the outer query, how often does the subquery execute?
A. Only once for the first row.
B. Only once for the last row.
C. Once for each row.
D. It doesn't execute at all.
Explanation: For each row processed by the outer query, the correlated subquery is executed once, making it dependent on the outer query for its values.
7. If you have a table of employees and a table of departments, and you want to list all departments and any employees in them (including departments with no employees), which JOIN would be most appropriate?
Explanation: A LEFT JOIN with departments on the left would return all departments and any matching employees. Departments without employees would still be listed, but with NULL values for employee columns.
8. Which of the following is NOT a type of JOIN in SQL?
Explanation: There's no such thing as a MIDDLE JOIN in SQL. OUTER JOIN is a general term that encompasses LEFT, RIGHT, and FULL JOINs.
9. What is the primary advantage of using a correlated subquery over a regular subquery?
A. It executes faster.
B. It allows for more complex conditions based on the outer query's data.
C. It can be used with multiple tables.
D. It simplifies the SQL syntax.
Explanation: The main advantage of correlated subqueries is their ability to reference columns from the outer query, allowing for more intricate conditions based on the outer query's data.
10. If you want to find out which products in an e-commerce database have never been ordered, which SQL construct would be most helpful?
A. INNER JOIN with the products and orders tables.
B. LEFT JOIN with the products table on the left.
C. Correlated subquery with the NOT EXISTS condition.
D. RIGHT JOIN with the products table on the right.
Explanation: Using a correlated subquery with the NOT EXISTS condition allows you to identify products that don't have corresponding entries in the orders table, indicating they've never been ordered.
Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
) instead.