Interview Q&A

BEGIN

SELECT * FROM employees WHERE id = @emp_id;

END;

• PostgreSQL:

CREATE OR REPLACE FUNCTION GetEmployeeDetails(emp_id INT)

RETURNS TABLE(id INT, name VARCHAR) AS $$

BEGIN

RETURN QUERY SELECT id, name FROM employees WHERE id = emp_id;

END;

$$ LANGUAGE plpgsql;

• MySQL:

DELIMITER //

CREATE PROCEDURE GetEmployeeDetails(IN emp_id INT)

BEGIN

SELECT * FROM employees WHERE id = emp_id;

END //

DELIMITER ;

• The syntax varies slightly between the databases, but the core idea remains the same.

SQL Server:

CREATE PROCEDURE GetEmployeeDetails (@emp_id INT)

BEGIN

SELECT * FROM employees WHERE id = @emp_id;

END;

PostgreSQL:

CREATE OR REPLACE FUNCTION GetEmployeeDetails(emp_id INT)

RETURNS TABLE(id INT, name VARCHAR) AS $$

BEGIN

RETURN QUERY SELECT id, name FROM employees WHERE id = emp_id;

END;

$$ LANGUAGE plpgsql;

MySQL:

DELIMITER //

CREATE PROCEDURE GetEmployeeDetails(IN emp_id INT)

BEGIN

SELECT * FROM employees WHERE id = emp_id;

END //

DELIMITER ;

The syntax varies slightly between the databases, but the core idea remains the same.

• Performance: Stored procedures are precompiled, so execution is faster compared

to running individual SQL queries each time.

• Code Reusability: Once defined, stored procedures can be reused in multiple

places, reducing redundancy.

• Security: You can grant users permission to execute a stored procedure without

giving them direct access to the underlying tables.

• Maintainability: Centralizing business logic in stored procedures makes

maintenance easier, especially when making changes to the logic.

• Error Handling: Stored procedures allow you to include error-handling mechanisms

like TRY...CATCH in SQL Server or EXCEPTION in PostgreSQL.

BEGIN

SELECT salary FROM employees WHERE id = @emp_id;

END;

• Calling the procedure:

EXEC GetEmployeeSalary @emp_id = 101;

• MySQL:

DELIMITER //

CREATE PROCEDURE GetEmployeeSalary(IN emp_id INT)

BEGIN

SELECT salary FROM employees WHERE id = emp_id;

END //

DELIMITER ;

• Calling the procedure:

CALL GetEmployeeSalary(101);

Stored procedures allow you to pass parameters to them, either input or output

parameters.

• Input Parameters: These allow you to send data to the procedure.
• Output Parameters: These allow the procedure to send data back to the caller.

Example:

SQL Server:

CREATE PROCEDURE GetEmployeeSalary (@emp_id INT)

BEGIN

SELECT salary FROM employees WHERE id = @emp_id;

END;

Calling the procedure:

EXEC GetEmployeeSalary @emp_id = 101;

MySQL:

DELIMITER //

CREATE PROCEDURE GetEmployeeSalary(IN emp_id INT)

BEGIN

SELECT salary FROM employees WHERE id = emp_id;

END //

DELIMITER ;

Calling the procedure:

CALL GetEmployeeSalary(101);

• Function:
• Return Type: Always returns a value (can be scalar or table).
• Used in Queries: Can be used in SELECT, WHERE, and ORDER BY clauses.
• Side Effects: Typically designed to perform calculations or return a result

without modifying the database.

• Stored Procedure:
• No Return Type: May or may not return values (using output parameters or

result sets).

• Used for Actions: Typically used for performing database operations (like

INSERT, UPDATE, DELETE).

• Side Effects: Designed to perform operations that modify data or perform

business logic.

Example:

Function (returns a value):

CREATE FUNCTION GetEmployeeSalary (emp_id INT) RETURNS DECIMAL

BEGIN

RETURN (SELECT salary FROM employees WHERE id = emp_id);

END;

• Stored Procedure (performs an action):

CREATE PROCEDURE UpdateEmployeeSalary (IN emp_id INT, IN new_salary

DECIMAL)

BEGIN

UPDATE employees SET salary = new_salary WHERE id = emp_id;

END;

Error handling is an essential part of stored procedures. Here’s how you handle errors in

different databases:

SQL Server: Use TRY...CATCH blocks to handle errors.

BEGIN TRY

• - Some SQL operation

INSERT INTO employees (name) VALUES ('John Doe');

END TRY

BEGIN CATCH

SELECT ERROR_MESSAGE() AS ErrorMessage;

END CATCH;

• PostgreSQL: Use EXCEPTION blocks in PL/pgSQL.

BEGIN

• - Some SQL operation

INSERT INTO employees (name) VALUES ('John Doe');

EXCEPTION

WHEN others THEN

RAISE NOTICE 'Error occurred: %', SQLERRM;

END;

• MySQL: MySQL doesn't have a built-in TRY...CATCH, but you can use

DECLARE...HANDLER.

DELIMITER //

CREATE PROCEDURE ExampleProcedure()

BEGIN

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

SELECT 'An error occurred';

• - Some SQL operation

INSERT INTO employees (name) VALUES ('John Doe');

END //

DELIMITER ;

You can call stored procedures from programming languages using appropriate database

connectors and drivers.

Example (C# with SQL Server):

using (SqlConnection conn = new SqlConnection(connectionString))
{

conn.Open();

using (SqlCommand cmd = new SqlCommand("GetEmployeeDetails",

conn))

{
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@emp_id",

SqlDbType.Int)).Value = 101;
using (SqlDataReader reader = cmd.ExecuteReader())
{

while (reader.Read())

{

Console.WriteLine(reader["name"]);

}
}
}
}

wait conn.OpenAsync();

using (SqlCommand cmd = new SqlCommand("GetEmployeeDetails",

conn))

{
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@emp_id",

SqlDbType.Int)).Value = 101;
using (SqlDataReader reader = await

cmd.ExecuteReaderAsync())

{

while (await reader.ReadAsync())

{

Console.WriteLine(reader["name"]);

}
}
}
}

Executing stored procedures asynchronously can improve the performance of applications

by allowing other tasks to run while waiting for the procedure to finish.

Example (C#):

using (SqlConnection conn = new SqlConnection(connectionString))

await conn.OpenAsync();

using (SqlCommand cmd = new SqlCommand("GetEmployeeDetails",

conn))

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@emp_id",

SqlDbType.Int)).Value = 101;

using (SqlDataReader reader = await

cmd.ExecuteReaderAsync())

while (await reader.ReadAsync())

Console.WriteLine(reader["name"]);

• Vendor Lock-In: Stored procedures use database-specific syntax, making it harder

to migrate to different database platforms.

• Complexity: As business logic grows within stored procedures, they can become

difficult to maintain and debug.

• Performance: While stored procedures can be optimized, poorly written ones can

hurt performance.

• Limited Flexibility: Stored procedures are less flexible compared to application

code, and they cannot easily handle more complex logic that might be easier in a

high-level programming language.

• Testing: Stored procedures are harder to test and debug in isolation compared to

application code.

Answer: Debugging stored procedures can be done in the following ways: SQL Server: SQL Server Management Studio (SSMS) allows you to set breakpoints, step through the code, and inspect variable values during execution. Steps:

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.

• Stored Procedures: The RETURN keyword in a stored procedure is typically used to

exit the procedure and can optionally return an integer value (usually indicating the

success or failure of the procedure). The return value is often used for error handling

or status reporting.

Indexing

n index in SQL is a database object that improves the speed of data retrieval operations

on a table. It works by maintaining a sorted order of column values in a separate structure,

llowing for faster search and retrieval compared to scanning every row in the table. The

index essentially creates a quick lookup table to find specific values.

Example of an index creation:

CREATE INDEX idx_column_name ON table_name(column_name);

Indexes are particularly useful for queries that involve WHERE, JOIN, ORDER BY, and GROUP

BY clauses.

Indexes improve query performance in several ways:

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.

There are several types of indexes in SQL:

Unique Index: Ensures that all values in the indexed column(s) are unique. Automatically

created when a PRIMARY KEY or UNIQUE constraint is defined on a column.

Example:

CREATE UNIQUE INDEX idx_employee_id ON employees(id);

• Full-Text Index: Used for indexing large text fields. It allows for more advanced searches

like full-text searches (e.g., MATCH in MySQL).

Example (MySQL):

CREATE FULLTEXT INDEX idx_fulltext_desc ON products(description);

• ● Clustered Index: Defines the physical order of the data in the table based on the

index. Each table can have only one clustered index.

• Non-Clustered Index: An index that stores a separate structure containing the

indexed columns and pointers to the actual data rows. A table can have multiple

non-clustered indexes.

• Composite Index: An index that includes multiple columns. Useful for queries that

filter based on multiple columns.

• Spatial Index: Used for indexing spatial data types such as points, lines, and

polygons (mostly for geographical data).

Answer: Creating an Index: CREATE INDEX idx_index_name ON table_name(column_name); Dropping an Index: DROP INDEX idx_index_name ON table_name; In SQL Server, dropping an index: DROP INDEX idx_index_name; -- No need to specify table name.

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 in the table is physically sorted based on the clustered index.
• Each table can have only one clustered index (usually the primary key).
• Fast for queries that involve range-based retrieval (e.g., BETWEEN or ORDER

BY).

• Non-Clustered Index:
• The data in the table is not physically sorted. The non-clustered index is

stored separately from the actual data.

• A table can have multiple non-clustered indexes.
• Faster for point lookups (single value queries) but less efficient for

range-based searches.

Indexes can slow down data modification operations:

• INSERT: When a new row is inserted, the index must also be updated to include the

new value, adding extra overhead.

• UPDATE: If the indexed column is updated, the index must be modified to reflect the

new value, which can be slower.

• DELETE: When a row is deleted, the corresponding index entries must be removed,

causing additional overhead.

While indexes speed up SELECT queries, they can reduce the performance of INSERT,

UPDATE, and DELETE operations due to the extra work needed to maintain the index.

Consider creating an index when:

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.

composite index is an index that is created on multiple columns. It is used when a query

filters or sorts based on more than one column. Composite indexes are especially useful

when queries frequently involve conditions on multiple columns.

• Example Use Case: If a query filters by both last_name and first_name,

creating a composite index on (last_name, first_name) will speed up the

query.

Example of creating a composite index:

CREATE INDEX idx_lastname_firstname ON employees(last_name,

first_name);

• Unique Index:
• Ensures that the values in the indexed columns are unique.
• Can be created on any column and allows NULL values (in most DBMS).
• A table can have multiple unique indexes.
• Primary Key Index:
• Enforces the uniqueness of the column(s) and also guarantees NOT NULL

constraint on the column(s).

• A table can only have one primary key.
• Automatically creates a unique index.

In short, both enforce uniqueness, but the primary key also guarantees that the column(s)

cannot be NULL.

Answer: The query optimizer in the database engine decides which index to use based on several factors:

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.

covering index is an index that contains all the columns needed by a query, meaning the

query can be answered entirely by the index without accessing the actual table data. This

can improve query performance by reducing disk I/O.

Example:

If a query selects id, name, and salary from the employees table and there is an index

on (id, name, salary), the index itself is sufficient to satisfy the query, and the

database doesn't need to access the table at all.

Answer: Index fragmentation occurs when data is inserted, updated, or deleted, causing the index structure to become inefficient. This can negatively impact performance in several ways:

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.