Interview Q&A

nd allow storage of unstructured or semi-structured data. They don't require a fixed

schema and are often used for large-scale applications where flexibility, scalability,

nd speed are more important than the strict relational model.

Examples: MongoDB, Cassandra, CouchDB, Firebase

manager, user).

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.

SELECT, INSERT, UPDATE, DELETE).

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.

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.

• Primary Key: A unique identifier for each record in a database table. No two rows in

a 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.

• Normalization is the process of organizing the data in a database to reduce

redundancy and dependency by dividing large tables into smaller ones and linking

them with relationships.

Importance:

• Reduces Data Redundancy: Ensures data is only stored once.
• Improves Data Integrity: Ensures consistency and correctness of data.
• Simplifies Updates: Easier to maintain and modify data without affecting the

entire system.

Why is it important?

• Normalization is the process of organizing the data in a database to reduce

redundancy and dependency by dividing large tables into smaller ones and linking

them with relationships.

Importance:

• Reduces Data Redundancy: Ensures data is only stored once.
• Improves Data Integrity: Ensures consistency and correctness of data.
• Simplifies Updates: Easier to maintain and modify data without affecting the

entire system.

Answer: database schema is the structure that defines the organization of data in a database. It includes definitions of tables, relationships, indexes, constraints, and other elements.

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: Referential integrity ensures that relationships between tables remain consistent. Specifically, it guarantees that foreign keys in a table must match primary keys in another table, or they must be NULL.

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.

view is a virtual table created by querying data from one or more tables. It does not store

data itself but presents it in a specific format.

Use cases:

• Simplify complex queries: A view can encapsulate complex queries for easier

reuse.

• Data security: Views can restrict access to certain columns or rows for users without

giving them full table access.

Answer: transaction in SQL is a sequence of operations performed as a single unit of work. Transactions ensure that database operations are performed atomically. Lifecycle:

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.

• Clustered Index: The data is stored in the order of the index. A table can have only

one clustered index because the rows can only be ordered in one way.

• Non-clustered Index: The index is separate from the data, and the index contains

pointers to the data. A table can have multiple non-clustered indexes.

subquery is a query within a query. It is used to retrieve data that will be used in the main

query.

Use case:

• Filtering: When the result of a subquery is used to filter data in the outer query.
• Aggregation: When the result of the subquery is used in aggregate functions.

Answer: UNION: Combines the result sets of two or more queries and removes duplicate rows. UNION ALL: Combines the result sets of two or more queries and includes all rows, even duplicates. Joins & Queries

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.

FULL OUTER JOIN returns all rows from both tables, matching rows where possible. If

there's no match, the result will contain NULL values for the columns from the table that

doesn't have a match.

Use case: When you want to combine all records from both tables, regardless of whether

they match, and include NULL where no match exists.

Example:

SELECT * FROM table1

FULL OUTER JOIN table2 ON table1.id = table2.id;

JOIN condition specifies the columns that will be used to match rows between two or

more tables. This condition typically uses ON or USING in SQL.

Example:

SELECT *

FROM table1

JOIN table2 ON table1.id = table2.id;

In this example, the condition table1.id = table2.id determines how the rows from

both tables will be joined.

• WHERE: Filters rows before any grouping is done (i.e., filters individual records).
• HAVING: Filters records after grouping is done (i.e., filters grouped results).

Example:

SELECT department, AVG(salary)

FROM employees

GROUP BY department

HAVING AVG(salary) > 50000;

In this query, HAVING is used to filter groups that have an average salary greater than

50,000.

• IN: Checks whether a value is present in a list or a subquery’s result set.
• EXISTS: Checks whether a subquery returns any rows, returning TRUE if the

subquery returns one or more rows, otherwise FALSE.

Example with IN:

SELECT name

FROM employees

WHERE department_id IN (SELECT id FROM departments WHERE name =

'HR');

Example with EXISTS:

SELECT name

FROM employees e

WHERE EXISTS (SELECT 1 FROM departments d WHERE d.id =

e.department_id AND d.name = 'HR');

The BETWEEN operator filters the result set within a given range. It is inclusive, meaning it

includes the boundary values.

Example:

SELECT *

FROM employees

WHERE salary BETWEEN 40000 AND 60000;

This query will return employees whose salaries are between 40,000 and 60,000 (inclusive).

The LIKE operator is used to search for a specified pattern in a column.

• % matches any sequence of characters.
• _ matches a single character.

Example:

SELECT *

FROM employees

WHERE name LIKE 'J%n'; -- Names that start with 'J' and end with

'n'

• COUNT(*): Counts all rows, including rows with NULL values in any column.
• COUNT(column_name): Counts only non-NULL values in the specified column.

Example:

SELECT COUNT(*) FROM employees; -- Counts all rows

SELECT COUNT(salary) FROM employees; -- Counts rows where salary is

NOT NULL

Conditional aggregation allows you to apply aggregate functions to a subset of data that

meets a certain condition. This is usually done with a CASE expression.

Example:

SELECT department,

SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male_count,

SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_count

FROM employees

GROUP BY department;

This query counts the number of male and female employees in each department.

Common Table Expression (CTE) is a temporary result set that can be referenced within

SELECT, INSERT, UPDATE, or DELETE statement. CTEs are often used for organizing

complex queries.

Example:

WITH EmployeeCTE AS (

SELECT name, salary

FROM employees

WHERE salary > 50000

SELECT * FROM EmployeeCTE;

The CASE statement is SQL’s way of handling if-else logic in queries. It allows you to return

different values based on certain conditions.

Example:

SELECT Name,

CASE

WHEN Age < 18 THEN 'Minor'

WHEN Age >= 18 AND Age < 60 THEN 'Adult'

ELSE 'Senior'

END AS AgeGroup

FROM Employees;