Interview Q&A

Short answer: Sharding is the process of breaking a large database into smaller, more manageable pieces (shards), each of which is stored on a different server.

Explain a bit more

When to use it: When a single database server can no longer handle the volume of data or queries. When scaling vertically (increasing server resources) is no longer cost-effective or possible. How it works: The data is divided based on a specific shard key (e.g., user ID, geographic region, etc.). Each shard contains a subset of the data and is stored on a separate server or cluster. Example: In an e-commerce platform, you could shard user data based on geographic region. Users from the US might be stored in one shard, while users from Europe are stored in another.

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.