Interview Q&A

visible to other transactions.

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: matched rows from the left table. If there’s no match, NULL is returned for the left table. Use case: When you want all records from the right table.

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.

Trade-offs:

• CA (Consistency and Availability): Systems that prioritize consistency and

availability will fail during network partitions.

• CP (Consistency and Partition Tolerance): Systems that prioritize consistency and

partition tolerance may not be available during network issues.

• AP (Availability and Partition Tolerance): Systems that prioritize availability and

partition tolerance may return stale data.

Answer: Use ETL (Extract, Transform, Load) tools to clean, transform, and shape the data as required.

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: Set up continuous integration (CI) pipelines to test schema migrations on different environments to ensure smooth deployment.

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.

• Each tenant gets its own database instance.
• Pros: Complete isolation, better security, and individual scaling.
• Cons: Higher overhead for database management and maintenance.

Example: SaaS applications often use multi-tenancy to serve multiple clients with the same

infrastructure while keeping data separated.

• A flexible schema design used for scenarios where attributes of entities are

dynamic and can vary over time.

• Example: A Product might have a variety of attributes like size, color, and

weight, but some products may not have all attributes, so you store them as

key-value pairs.

Answer: Use pagination techniques (LIMIT, OFFSET, or ROWNUM) to retrieve data in chunks rather than all at once. Avoid fetching unnecessary columns or rows.

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: Track buffer cache hit ratio, transaction log size, locks and deadlocks, and cache usage to monitor the internal database performance.

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.

• Horizontal scaling involves adding more database servers to distribute the

load, typically using replication, sharding, or clustering.

• Advantages: It can handle much larger datasets and traffic since the

workload is distributed across multiple servers.

• Disadvantages: More complex to implement and maintain, requires

specialized techniques like load balancing and data partitioning.

Answer: Data is written to both the primary and secondary database simultaneously. Advantages: Ensures data consistency between all replicas. Disadvantages: May incur performance overhead due to the need to wait for 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.

• A transaction log backup records all the changes made to the database since

the last transaction log backup. It allows point-in-time recovery.

• Advantages: Enables recovery of the database to any specific point in time

(assuming all previous logs are available).

• Disadvantages: Requires continuous backups of transaction logs to maintain

full recovery.

• Example: After a transaction log backup, you can restore to a specific

moment in time by replaying the logs up to that point.

files. Use access controls, roles, and policies to restrict access to 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.

responsibilities.

Example (SQL Server):

• - Create a role

CREATE ROLE Manager;

• - Grant permissions to the role

GRANT SELECT, UPDATE ON Employees TO Manager;

• - Assign the role to a user

EXEC sp_addrolemember 'Manager', 'john_doe';

In PostgreSQL or MySQL, you would follow similar steps using GRANT statements and role

management commands.

• Customers: Contains customer details (name, address, etc.).
• Orders: Contains order details and references customers.
• Order_Items: A join table between Orders and Products.
• Products: Product details (name, description, price, etc.).
• Inventory: Tracks stock levels.

same type of data (e.g., storing multiple phone numbers in separate columns).

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.

on another non-key attribute). Ensure that no non-key attribute is dependent on

another non-key attribute.

For example:

• Original Table:

Employee_ID Employee_Nam

Department Department_Manager

1 Alice HR John

2 Bob IT Sarah

• 1NF: Remove repeating groups and ensure atomic values.
• 2NF: Remove partial dependencies. In this case, split into separate tables for

employees and departments.

• 3NF: Remove transitive dependencies. The Department_Manager is a dependency

on Department, not directly on Employee, so it should be moved to a separate table

for Departments.

Answer: to retrieve data. To resolve this, indexes can be reorganized or rebuilt to improve their performance.

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.

I/O, CPU, etc.) and chooses the one with the lowest cost.

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.

likely to improve performance when indexed.

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.

the database can use the index to quickly find matching rows.

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.

• PostgreSQL: PostgreSQL doesn’t have a built-in debugger, but you can use RAISE

NOTICE for debugging or use third-party tools like pgAdmin for debugging.

• MySQL: MySQL Workbench provides a simple debugging interface that allows

stepping through the stored procedure and monitoring execution.

Example (PostgreSQL):

RAISE NOTICE 'Debugging variable: %', my_variable;

CID stands for:

• Atomicity: Ensures that all operations in a transaction are completed successfully, or

none are. If one part of a transaction fails, the whole transaction fails.

• Consistency: Guarantees that the database is always in a valid state, adhering to all

rules, including constraints, after a transaction.

• Isolation: Ensures that transactions are executed in isolation from each other, so

one transaction does not interfere with another.

• Durability: Ensures that once a transaction is committed, it is permanent, even in the

case of system failures.

table can have the same primary key value. It ensures entity integrity.

Example: user_id in a users table.

• Foreign Key: A field (or a combination of fields) in one table that uniquely identifies a

row of another table. It establishes a relationship between two tables and enforces

referential integrity.

Example: user_id in an orders table linking to user_id in the users table.

permanent.

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.