Explore

Database Topics Question Bank

For final exam: You will not be asked about JSON or MONGODB

Final Exam: December 13 / 14 depending on which session you are in. You must be in the class! {No remote test writing} You must student ID card!!! I will post a list of who will write at 6 -> 6:50 who will write the test at 7 - 7:50 50 minutes MCQ How to study:

https://www.dropbox.com/scl/fi/zn0f1m9fyi8isc4li3eql/Peter-s-Book-on-Databases.pptx?rlkey=vxn3pnt2th7dgphm0y314k743&dl=0⁠

https://coda.io/@peter-sigurdson/database-topics-question-bank⁠

⁠

https://www.dropbox.com/scl/fi/daza7m7n7xrac5c0xdhly/Concepts-of-the-SQL-Database.xlsx?rlkey=4e7zzxfen78s8a5ie5kx3opwz&dl=0⁠

⁠

Practice with MySQL : know about the 3 kinds of SQL create a database : DDL put some data into it : DML (creating, deleting, modifying rowsets) do some queries: DQL Data Query Languages DCL : data control language Allow / Deny uses access to tables: We have not done this and you will not be tested on it.

You should know about the basic concepts of SQL and the Relational Database.

You should know about Codd's (13) Laws Codd says: If you want to create a relational database to model the data in a business domain: 1. Make 1 table per each the entities in that Domain: Students, Classes, Enrollments 2. Within each one of those entities: figure out what attributes describe that Data Record: those become the fields of the table 3. We can machine manufacture new knowledge from the data in those tables by making predicate joins to link the tables together. 4. Do sql pulls (Read of CRUD) with the select statement: - sort by - order by - filter Now let's make a simple with MySQL to practice this.

Here's a prompt that students can use to engage with their ChatGPT agent for a drill session on SQL, relational databases, DQL, DML, and DDL:

⁠

Prompt to ChatGPT:

"Hi ChatGPT! I'd like to practice and improve my understanding of SQL, relational databases, and their associated components including DQL (Data Query Language), DML (Data Manipulation Language), and DDL (Data Definition Language). Please ask me a series of questions on these topics, starting from basic to more advanced levels. After each question, I will provide an answer. I'd like you to give feedback on my responses to help me understand any mistakes or areas for improvement.

Start with simpler questions and gradually increase the complexity based on how well I'm answering. If I seem to struggle with a particular concept, please ask follow-up questions or provide a brief explanation to help me grasp the topic better. Continue this process in a loop.

I'll type 'done for now thank you' when I want to end the session. Let's begin!"

⁠

Using this prompt, the ChatGPT agent will initiate a question-and-answer loop, covering various aspects of SQL and relational databases. The AI will adapt the complexity of the questions based on the student's responses and provide instructional points and feedback as needed. This interactive approach allows for personalized learning and reinforcement of key concepts.

Final Exam Question:

Given this Table Schema, write a SQL Statement to output all the details of each order including customer name and book name:

select Books.bookid, Books.Title, Books.Author, Books.price, Books.Genre, Books.Publisher,

Customers.Firstname, Customer.Email, Orders.TotalAmount

from Customers, Books, Orders

where Books.BookID = Orders.BookID and Orders.CustomerID =Customer.CustomerID

INSERT INTO Books (BookID, Title, Author, Genre, Price, Publisher, PublicationDate, ISBN) VALUES (1, 'The Great Gatsby', 'F. Scott Fitzgerald', 'Novel', 15.20, 'Scribner', '1925-04-10', '9780743273565'), (2, '1984', 'George Orwell', 'Dystopian', 12.80, 'Secker & Warburg', '1949-06-08', '9780451524935'), (3, 'To Kill a Mockingbird', 'Harper Lee', 'Novel', 7.99, 'J. B. Lippincott & Co.', '1960-07-11', '9780446310789'), (4, 'The Catcher in the Rye', 'J.D. Salinger', 'Novel', 8.99, 'Little, Brown and Company', '1951-07-16', '9780316769488'), (5, 'Brave New World', 'Aldous Huxley', 'Dystopian', 9.99, 'Chatto & Windus', '1932-01-01', '9780060850524');

INSERT INTO Customers (CustomerID, FirstName, LastName, Email, Phone, Address) VALUES (1, 'Alice', 'Smith', 'alice.smith@email.com', '123-456-7890', '1234 Main St'), (2, 'Bob', 'Jones', 'bob.jones@email.com', '234-567-8901', '2345 First Ave'), (3, 'Carol', 'Johnson', 'carol.johnson@email.com', '345-678-9012', '3456 Second Blvd'), (4, 'David', 'Lee', 'david.lee@email.com', '456-789-0123', '4567 Third Dr'), (5, 'Emma', 'Brown', 'emma.brown@email.com', '567-890-1234', '5678 Fourth St'), (6, 'Frank', 'Davis', 'frank.davis@email.com', '678-901-2345', '6789 Fifth Ave'); INSERT INTO Orders (OrderID, CustomerID, BookID, OrderDate, TotalAmount, Status) VALUES (1, 1, 2, '2023-12-01', 12.80, 'Completed'), (2, 2, 3, '2023-12-02', 7.99, 'Completed'), (3, 3, 4, '2023-12-03', 8.99, 'Pending'), (4, 1, 5, '2023-12-04', 9.99, 'Completed'), (5, 5, 1, '2023-12-05', 15.20, 'Pending'); select * from orders;

November 20: Quiz: MongoDB Concepts and Shell Operations

December 6 Review Questions:

Codd's 12 Rules: Discuss each of Codd's rules, providing examples of how they are implemented in modern relational databases.

SQL and Codd's Principles: Explain how SQL commands like SELECT, INSERT, UPDATE, and DELETE align with Codd's relational model.

DDL vs. DML: Define DDL (e.g., CREATE TABLE) and DML (e.g., INSERT INTO) with examples of their usage.

Managing Relationships: Illustrate with examples how foreign keys link tables in a relational database.

Primary Keys: Use an example database table to show how a primary key uniquely identifies each record.

Normalization: Describe the process of normalization and its benefits with a practical example.

SQL Queries: Provide examples of SQL SELECT queries to demonstrate data retrieval.

Foreign Keys: Explain the concept of foreign keys with an example of related database tables.

SQL Transactions: Discuss transactions and their role in maintaining data integrity with an example.

Database Indexes: Describe how indexes improve search performance with a sample scenario.

Relational vs. Other Models: Compare the relational model with hierarchical or network models using examples.

Constraints in SQL: Explain constraints like UNIQUE and NOT NULL with examples showing their enforcement in a database.

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:

db.orders.aggregate([

{ $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?

A. LEFT JOIN

B. RIGHT JOIN

C. FULL JOIN

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?

A. INNER JOIN

B. LEFT JOIN

C. RIGHT JOIN

D. FULL JOIN

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?

A. INNER JOIN

B. LEFT JOIN

C. RIGHT JOIN

D. FULL JOIN

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?

A. INNER JOIN

B. LEFT JOIN

C. RIGHT JOIN

D. FULL JOIN

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?

A. OUTER JOIN

B. MIDDLE JOIN

C. LEFT JOIN

D. INNER JOIN

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 (

CtrlP

) instead.