Interview Q&A

dvantages:

• Scalability: MongoDB scales horizontally through sharding, which can handle large

datasets across distributed clusters.

• Flexibility: Schema-less design allows for dynamic data models and easier iteration

during development.

• Performance: It can perform high-throughput reads and writes for large datasets.
• High Availability: Through replica sets, MongoDB ensures data availability and fault

tolerance.

Disadvantages:

• Consistency: By default, MongoDB uses eventual consistency, which may not be

suitable for all applications (though it supports ACID transactions in some cases).

• Complexity in Transactions: While MongoDB now supports multi-document

transactions, working with them is more complex compared to SQL.

• Data Duplication: The flexibility can sometimes lead to data duplication and

inconsistency if not properly managed.

Answer: Database: A container for collections. MongoDB allows you to have multiple databases within the same instance. Collection: A group of MongoDB documents. Collections are similar to tables in SQL but don’t have a fixed schema.

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

Create:

db.collection.insertOne({ name: "Alice", age: 25 });

db.collection.insertMany([{ name: "Bob", age: 30 }, { name:

"Charlie", age: 35 }]);

• Read:

db.collection.find({ age: { $gt: 30 } });

db.collection.findOne({ name: "Alice" });

• Update:

db.collection.updateOne({ name: "Alice" }, { $set: { age: 26 } });

db.collection.updateMany({ age: { $lt: 30 } }, { $set: { status:

"young" } });

• Delete:

db.collection.deleteOne({ name: "Alice" });

db.collection.deleteMany({ age: { $lt: 30 } });

Answer: You can use the updateOne(), updateMany(), or replaceOne() methods to update data. Example: db.collection.updateOne({ name: "Alice" }, { $set: { age: 27 } }); $set updates the value of a field. $inc increments a field's value.

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

MongoDB provides multi-document transactions starting with version 4.0. This allows you

to execute multiple operations in a transaction, ensuring ACID properties (Atomicity,

Consistency, Isolation, Durability) across multiple documents and collections.

Example:

const session = client.startSession();

try {

session.startTransaction();

db.collection1.updateOne({ _id: 1 }, { $set: { status: "completed"

} }, { session });

db.collection2.updateOne({ _id: 2 }, { $set: { status: "shipped" }

}, { session });

session.commitTransaction();

} catch (error) {

session.abortTransaction();

} finally {

session.endSession();

}

In MongoDB, relationships can be handled in two ways:

• Embedded documents: Storing related data within a single document (best for

one-to-many relationships).

Example: A blog post with comments as an embedded array.

• Referenced documents: Storing related data in separate collections and using

references (best for many-to-many or large datasets).

Example: A customer document may reference an order document by storing the

order’s _id in the customer document.

Data validation in MongoDB can be implemented using JSON Schema or custom validation

rules to ensure data consistency.

Example (using JSON Schema):

db.createCollection("products", {

validator: {

$jsonSchema: {

bsonType: "object",

required: ["product_name", "price"],

properties: {

product_name: { bsonType: "string" },

price: { bsonType: "double", minimum: 0 }

}
}
}

});

• Indexing: Create appropriate indexes to optimize query performance.
• Sharding: Distribute large datasets across multiple servers.
• Caching: Cache frequently accessed data.
• Aggregation optimization: Use $match early in the aggregation pipeline.
• Avoid joins: MongoDB performs best when data is denormalized or embedded.

Database Security

ccess controls.

• Privilege Escalation: Attackers gaining elevated permissions or access levels.
• Malicious Insiders: Employees or authorized users intentionally or unintentionally

leaking or modifying data.

• Denial of Service (DoS): Attackers overwhelming the database with requests,

making it unavailable to legitimate users.

• Backup Exposure: Unencrypted or improperly secured backup files that are

ccessible to unauthorized users.

Common database security risks include:

• SQL Injection: Malicious queries that can compromise the database if the input is

not properly validated.

• Unauthorized Access: Users gaining access to sensitive data without permission.
• Data Breaches: Exposing sensitive data due to insufficient encryption or weak

access controls.

• Privilege Escalation: Attackers gaining elevated permissions or access levels.
• Malicious Insiders: Employees or authorized users intentionally or unintentionally

leaking or modifying data.

• Denial of Service (DoS): Attackers overwhelming the database with requests,

making it unavailable to legitimate users.

• Backup Exposure: Unencrypted or improperly secured backup files that are

accessible to unauthorized users.

Answer: uthorized users based on roles. Each role defines a set of permissions (what actions can be performed on which database objects). Steps to implement RBAC:

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

Answer: Role-Based Access Control (RBAC) is a method for restricting system access to authorized users based on roles. Each role defines a set of permissions (what actions can be performed on which database objects). Steps to implement RBAC:

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

nd SQL statements in precompiled code, making it harder for attackers to inject malicious

code.

Roles in database security:

• Input Validation: Stored procedures allow you to validate user inputs at the

database level, preventing malicious input from being executed.

• Preventing Direct Access: By using stored procedures, users can be given

permissions to execute specific procedures rather than direct access to the

underlying tables.

• Encapsulation of Business Logic: The logic within stored procedures is not visible

to the end-user, reducing the attack surface.

• Audit Logging: Stored procedures can include logic to log user activity for auditing

nd compliance purposes.

Example: Instead of allowing users to execute arbitrary INSERT or UPDATE statements, you

can give them permission to execute a specific stored procedure that does the necessary

validation and modification of data.

CREATE PROCEDURE update_salary(IN emp_id INT, IN new_salary DECIMAL)

BEGIN

IF new_salary > 0 THEN
UPDATE employees SET salary = new_salary WHERE id = emp_id;

END IF;

END;

Stored Procedures can help improve database security by encapsulating business logic

and SQL statements in precompiled code, making it harder for attackers to inject malicious

code.

Roles in database security:

• Input Validation: Stored procedures allow you to validate user inputs at the

database level, preventing malicious input from being executed.

• Preventing Direct Access: By using stored procedures, users can be given

permissions to execute specific procedures rather than direct access to the

underlying tables.

• Encapsulation of Business Logic: The logic within stored procedures is not visible

to the end-user, reducing the attack surface.

• Audit Logging: Stored procedures can include logic to log user activity for auditing

and compliance purposes.

Example: Instead of allowing users to execute arbitrary INSERT or UPDATE statements, you

can give them permission to execute a specific stored procedure that does the necessary

validation and modification of data.

CREATE PROCEDURE update_salary(IN emp_id INT, IN new_salary DECIMAL)

BEGIN

IF new_salary > 0 THEN

UPDATE employees SET salary = new_salary WHERE id = emp_id;

END IF;

END;

LTER DATABASE MyDatabase SET ENCRYPTION ON;

• Column-Level Encryption: For encrypting specific columns.

CREATE CERTIFICATE MyCertificate WITH SUBJECT = 'My Certificate';

CREATE SYMMETRIC KEY MySymmetricKey WITH ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE MyCertificate;

OPEN SYMMETRIC KEY MySymmetricKey DECRYPTION BY CERTIFICATE

MyCertificate;

• PostgreSQL:

pgcrypto extension provides functions for encrypting data.

SELECT pgp_sym_encrypt('Sensitive Data', 'encryption_key');

• MySQL:

Encryption Functions: MySQL provides functions like AES_ENCRYPT and AES_DECRYPT.

SELECT AES_ENCRYPT('Sensitive Data', 'encryption_key');

SELECT AES_DECRYPT(encrypted_data, 'encryption_key');

• For all databases, consider using SSL/TLS for encrypting data in transit.

Encryption is essential for protecting sensitive data both at rest (on disk) and in transit

(during communication).

SQL Server:

Transparent Data Encryption (TDE): Encrypts data at rest without requiring application

changes.

CREATE DATABASE ENCRYPTION KEY;

ALTER DATABASE MyDatabase SET ENCRYPTION ON;

Column-Level Encryption: For encrypting specific columns.

CREATE CERTIFICATE MyCertificate WITH SUBJECT = 'My Certificate';

CREATE SYMMETRIC KEY MySymmetricKey WITH ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE MyCertificate;

OPEN SYMMETRIC KEY MySymmetricKey DECRYPTION BY CERTIFICATE

MyCertificate;

PostgreSQL:

pgcrypto extension provides functions for encrypting data.

SELECT pgp_sym_encrypt('Sensitive Data', 'encryption_key');

MySQL:

Encryption Functions: MySQL provides functions like AES_ENCRYPT and AES_DECRYPT.

SELECT AES_ENCRYPT('Sensitive Data', 'encryption_key');

SELECT AES_DECRYPT(encrypted_data, 'encryption_key');

For all databases, consider using SSL/TLS for encrypting data in transit.

Answer: Backup and restore security involves ensuring that backup files are stored securely and that access to backup data is tightly controlled. Steps for Secure Backup:

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

There are three primary types of database backups:

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

SQL Server:

To back up a database in SQL Server, you can use the BACKUP DATABASE command.

• - Full Backup

BACKUP DATABASE MyDatabase TO DISK = 'C:\Backups\MyDatabase.bak';

• - Transaction Log Backup

BACKUP LOG MyDatabase TO DISK = 'C:\Backups\MyDatabase_log.trn';

PostgreSQL:

In PostgreSQL, you can use the pg_dump command for backing up the database.

• - Full Backup

pg_dump mydatabase > /path/to/backup/mydatabase.sql

For backing up with compression or other options:

pg_dump -Fc mydatabase > /path/to/backup/mydatabase.dump

For transactional backups, you can use WAL (Write-Ahead Logging) archiving, typically

managed through archive_mode and archive_command settings in the

postgresql.conf.

MySQL:

In MySQL, you can use the mysqldump command for backing up the database.

• - Full Backup

mysqldump -u username -p mydatabase > /path/to/backup/mydatabase.sql

For binary logging (transaction logs), MySQL uses binlog:

• - Enabling binary log

[mysqld]

log-bin=mysql-bin

SQL Server:

To restore a full backup in SQL Server:

• - Full Restore

RESTORE DATABASE MyDatabase FROM DISK = 'C:\Backups\MyDatabase.bak';

• - Transaction Log Restore

RESTORE LOG MyDatabase FROM DISK = 'C:\Backups\MyDatabase_log.trn';

To restore a database to a specific point in time (using point-in-time recovery), you would

restore the full backup and apply transaction logs.

RESTORE DATABASE MyDatabase FROM DISK = 'C:\Backups\MyDatabase.bak';

RESTORE LOG MyDatabase FROM DISK = 'C:\Backups\MyDatabase_log.trn'

WITH STOPAT = '2023-09-14T15:30:00'; -- Restore to a specific time

PostgreSQL:

To restore a backup from a pg_dump:

• - Full Restore from SQL dump

psql -U username -d mydatabase < /path/to/backup/mydatabase.sql

For binary dump (pg_dump -Fc):

pg_restore -U username -d mydatabase /path/to/backup/mydatabase.dump

MySQL:

To restore a MySQL database:

• - Full Restore from SQL dump

mysql -u username -p mydatabase < /path/to/backup/mydatabase.sql

For restoring binary logs:

mysqlbinlog /path/to/binlog/mysql-bin.000001 | mysql -u username -p

Answer: utomating backups ensures that backups are performed regularly and without manual intervention. You can use the following methods:

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

Answer: Database replication is the process of copying and maintaining database objects in multiple databases to ensure high availability and fault tolerance. Types of Replication:

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

Answer: Sharding and partitioning are techniques used to distribute data across multiple servers or tables to improve performance and scalability.

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

Answer: Monitoring database performance is crucial for identifying bottlenecks and ensuring optimal functioning. The following methods are commonly used:

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

connection pool is a cache of database connections that are maintained so that

connections can be reused when future requests to the database are made. Instead of

opening and closing a new connection each time a query is executed, the application can

reuse existing connections from the pool.

• How it helps with scaling:
• Improves efficiency: Reusing connections reduces the overhead of

establishing new connections, leading to faster query execution.

• Reduces resource consumption: By limiting the number of connections to

the database, the pool avoids overloading the database with too many open

connections.

• Improves scalability: Connection pools help handle a large number of

requests without overwhelming the database. The pool can be scaled by

djusting the maximum number of connections based on the workload.

Example: A connection pool size can be set to 100, meaning up to 100 connections to the

database can be active at once, with others waiting in line for an available connection.