Interview Q&A

• Indexes are data structures that speed up the retrieval of data from a database.

They work like the index in a book, allowing quick lookup of data without having to

scan the entire table.

Importance:

• Faster Searches: Improves query performance, especially for large datasets.
• Efficient Sorting: Helps in sorting and filtering operations.
• Primary and Foreign Keys: Automatically indexed to ensure quick data

retrieval.

• 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: Analyze the query execution plan to identify any inefficient operations (e.g., full table scans) and refactor accordingly. Advanced Topics

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.

query to leverage more efficient join algorithms.

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.

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.

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

Can you give examples?

Database Constraints are rules applied to ensure the integrity and accuracy of the data

within a database. Examples include:

• NOT NULL: Ensures that a column cannot have a NULL value.
• UNIQUE: Ensures all values in a column are unique.
• CHECK: Ensures that all values in a column satisfy a specific condition.
• DEFAULT: Sets a default value for a column if no value is specified.
• FOREIGN KEY: Ensures the value in one table corresponds to a valid value in

another table.

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.

trigger is a special kind of stored procedure that is automatically executed or fired when

certain events occur in a database, such as INSERT, UPDATE, or DELETE.

Example: A trigger that automatically updates a timestamp field every time a row is modified.

• SQL Server: Developed by Microsoft, it's known for its strong integration with other

Microsoft products. It’s commonly used in enterprise environments.

• PostgreSQL: An open-source, object-relational database known for its standards

compliance, extensibility, and advanced features like support for complex queries,

JSONB, and custom data types.

• MySQL: An open-source relational database known for its speed and ease of use.

It's often used in web applications (e.g., with PHP) and is less feature-rich than

PostgreSQL but highly reliable.

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.

nd vice versa. This often requires a junction 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.

• One-to-One (1:1): Each row in one table is linked to one row in another table.
• One-to-Many (1:M): A row in one table can be linked to many rows in another table.
• Many-to-Many (M:N): Rows in one table can be linked to many rows in another table

and vice versa. This often requires a junction table.

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

Answer: composite index is an index that involves more than one column in a table. It's used when queries often filter or sort by multiple columns, optimizing performance for those specific 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.

• INNER JOIN: Returns only the rows that have matching values in both tables.
• LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and the

matching rows from the right table. If no match is found, NULLs are returned for

columns from the right table.

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;

Window functions allow you to perform calculations across a set of table rows that are

related to the current row, without collapsing the result set into a single row.

Example: ROW_NUMBER() generates a sequential integer to each row within the result set.

Example:

SELECT name, salary,

ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num

FROM employees;

This query adds a sequential row number to each employee, ordered by salary.

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');