Interview Q&A

Short answer: Eventual consistency is a consistency model used in distributed systems where updates to data will propagate and eventually become consistent across all nodes, but not necessarily immediately.

Explain a bit more

How it works: In an eventually consistent system, updates are made to a node and eventually, that update will be propagated to all other nodes. It allows for temporary inconsistencies, but ensures that the system will converge to a consistent state over time. Use cases: Suitable for systems that can tolerate a delay in consistency, like NoSQL databases (Cassandra, DynamoDB) and systems dealing with high availability and massive scale.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: The DISTINCT keyword is used to return only unique values in the result set, removing any duplicate rows.

Example code

SELECT DISTINCT Country FROM Customers; This will return a list of unique countries from the Customers table.

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: The UNION operator combines the result sets of two or more SELECT queries into a single result set and removes duplicate records. All SELECT queries must have the same number of columns with compatible data types.

Example code

SELECT Name FROM Employees UNION SELECT Name FROM Customers;

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: CHAR: A fixed-length string data type. It always reserves the same amount of space regardless of the string's length. Use case: When the length of the string is known and consistent. VARCHAR: A variable-length string data type. It only uses as much space as needed to store the string. Use case: When the string length can vary.

Example code

CREATE TABLE Example ( fixed_char CHAR(10), variable_char VARCHAR(10) );

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: The LIMIT clause is used to specify the number of records returned by a SELECT query. It is commonly used to restrict the number of rows, especially for pagination.

Example code

SELECT * FROM Employees LIMIT 5; This will return only the first 5 rows from the Employees table.

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: NULL represents the absence of a value in a column. It is not the same as an empty string or zero. Handling NULL: To check for NULL, use IS NULL or IS NOT NULL. To handle NULL in queries, use COALESCE() (returns the first non-NULL value) or IFNULL().

Example code

SELECT Name, IFNULL(Salary, 0) FROM Employees;

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: And does not log individual row deletions.

Explain a bit more

Cannot be rolled back (in most databases). And does not log individual row deletions. Cannot be rolled back (in most databases). nd does not log individual row deletions. Cannot be rolled back (in most databases). Resets auto-increment counters. DELETE: Deletes rows based on a condition, and can be rolled back (if using transactions). Slower compared to TRUNCATE. DELETE FROM Employees WHERE Age < 18; -- Deletes only specific rows TRUNCATE TABLE Employees; -- Deletes all rows nd does not log individual row deletions. Cannot be rolled back (in most databases). Resets auto-increment counters. DELETE: Deletes rows based on a condition, and can be rolled back (if using transactions). Slower compared to TRUNCATE.

Example code

DELETE FROM Employees WHERE Age < 18; -- Deletes only specific rows TRUNCATE TABLE Employees; -- Deletes all rows

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: TRUNCATE: Deletes all rows in a table, but the table structure remains. It is faster and does not log individual row deletions. Cannot be rolled back (in most databases). Resets auto-increment counters. DELETE: Deletes rows based on a condition, and can be rolled back (if using transactions). Slower compared to TRUNCATE.

Example code

DELETE FROM Employees WHERE Age < 18; -- Deletes only specific rows TRUNCATE TABLE Employees; -- Deletes all rows

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: An Index is a database object that speeds up the retrieval of rows from a table by creating a data structure (often a B-tree). Indexes improve the performance of SELECT queries but can slow down INSERT, UPDATE, and DELETE operations.

Example code

CREATE INDEX idx_employee_name ON Employees(Name);

Real-world example (ShopNest)

ShopNest adds an index on Orders(CustomerId, CreatedAt) because “my recent orders” is queried constantly.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: The ACID properties ensure reliable processing of database transactions:

Real-world example (ShopNest)

Checkout wraps stock decrement + order insert in a transaction so you never sell stock you do not have.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.