Interview Q&A

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

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.

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.

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.

ggregate functions perform a calculation on a set of values and return a single value.

Common aggregate functions include:

• COUNT(): Returns the number of rows.
• SUM(): Returns the sum of a column.
• AVG(): Returns the average of a column.
• MAX(): Returns the maximum value.
• MIN(): Returns the minimum value.

Example:

SELECT AVG(salary)

FROM employees;

• Data Storage:
• Table: Stores actual data in the database.
• View: A virtual table that shows data from one or more tables but does not

store data. It derives its data from the underlying tables each time it is

queried.

• Modifications:
• Table: You can insert, update, or delete data directly.
• View: You cannot modify data in a view unless certain conditions are met.

Some views (those based on a single table) are updatable, while others

(especially those with JOINs, aggregations, or complex logic) are not.

• Performance:
• Table: Generally optimized for performance with indexes, data distribution,

and so on.

• View: May have slower performance due to the query being executed each

time the view is accessed, especially if it's a complex view or involves large

datasets.

ccess to the data without revealing the full database schema.

• Security: By granting access to a view instead of a table, you can restrict access to

sensitive data and only expose the columns or rows that are necessary.

• Consistency: A view can standardize how data is accessed across multiple queries

or applications, ensuring consistent results.

• Reduced Redundancy: Rather than writing the same complex query multiple times,

you can create a view to encapsulate it and reuse the view in different queries.

• Simplifies Complex Queries: Views can encapsulate complex queries, making

them reusable and easier to manage.

• Data Abstraction: Views abstract the underlying table structure and allow for easier

access to the data without revealing the full database schema.

• Security: By granting access to a view instead of a table, you can restrict access to

sensitive data and only expose the columns or rows that are necessary.

• Consistency: A view can standardize how data is accessed across multiple queries

or applications, ensuring consistent results.

• Reduced Redundancy: Rather than writing the same complex query multiple times,

you can create a view to encapsulate it and reuse the view in different queries.

Yes, views can be updated in SQL, but there are conditions for when this is possible:

• Updatable Views: A view is updatable if it:
• Is based on a single table.
• Does not contain aggregation functions like COUNT(), AVG(), or SUM().
• Does not include DISTINCT, GROUP BY, JOIN, or other complex operations

that modify the set of rows.

• Non-Updatable Views: Views that use complex JOINs, aggregations, or GROUP BY

clauses are typically non-updatable.

You can still modify data through these views by using triggers, such as INSTEAD

OF triggers, which allow you to define custom behavior for updating or deleting rows.

Example of an updatable view:

CREATE VIEW simple_view AS

SELECT id, name, salary FROM employees;

• - You can now update 'simple_view' directly:

UPDATE simple_view SET salary = 60000 WHERE id = 101;

Under what conditions?

Yes, views can be updated in SQL, but there are conditions for when this is possible:

• Updatable Views: A view is updatable if it:
• Is based on a single table.
• Does not contain aggregation functions like COUNT(), AVG(), or SUM().
• Does not include DISTINCT, GROUP BY, JOIN, or other complex operations

that modify the set of rows.

• Non-Updatable Views: Views that use complex JOINs, aggregations, or GROUP BY

clauses are typically non-updatable.

You can still modify data through these views by using triggers, such as INSTEAD

OF triggers, which allow you to define custom behavior for updating or deleting rows.

Example of an updatable view:

CREATE VIEW simple_view AS

SELECT id, name, salary FROM employees;

• - You can now update 'simple_view' directly:

UPDATE simple_view SET salary = 60000 WHERE id = 101;

Answer: re certain DBMS-specific features and optimizations (e.g., indexed views in SQL Server).

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.

Here’s how you can create a view in each of these DBMS:

SQL Server:

CREATE VIEW view_name AS

SELECT column1, column2

FROM table_name

WHERE condition;

PostgreSQL:

CREATE VIEW view_name AS

SELECT column1, column2

FROM table_name

WHERE condition;

MySQL:

CREATE VIEW view_name AS

SELECT column1, column2

FROM table_name

WHERE condition;

The syntax for creating a view is generally the same across these databases, although there

are certain DBMS-specific features and optimizations (e.g., indexed views in SQL Server).

materialized view is a database object that stores the result of a query physically, similar

to an indexed view but more generalized. Unlike regular views, which generate their result

dynamically when queried, materialized views store the result set and can be periodically

refreshed.

• Advantages:
• Faster query performance, especially for complex queries or aggregations,

since the data is precomputed and stored.

• Refresh: The data in a materialized view may become outdated over time, so it

needs to be refreshed periodically, either manually or automatically, depending on the

DBMS.

Example:

CREATE MATERIALIZED VIEW sales_summary AS

SELECT product_id, SUM(sales) AS total_sales

FROM sales

GROUP BY product_id;

• - Refresh the materialized view when needed:

REFRESH MATERIALIZED VIEW sales_summary;

• Materialized views are supported in systems like PostgreSQL and Oracle, but MySQL does

not have a built-in materialized view feature. You can simulate one using tables and

scheduled jobs.

To delete a view, you use the DROP VIEW statement. This removes the view from the

database entirely.

Example:

DROP VIEW view_name;

Be cautious when dropping a view, as it will no longer be available for use in queries. Ensure

no other objects are dependent on the view before dropping it.

Optimizing views is important to ensure that your queries are efficient. Here are some

strategies:

• Avoid Complex Views: Avoid creating views with complex logic (e.g., multiple

JOINs, GROUP BY, or subqueries), as they can result in slower performance. Keep

views simple and focused.

• Indexing: Ensure that the underlying tables have proper indexes, especially on the

columns used in JOIN or WHERE conditions.

• Materialized Views: Use materialized views when querying large datasets or

performing heavy aggregations. These views store the results physically and can be

refreshed periodically.

• Limit the Data: Use WHERE clauses in your views to filter unnecessary rows and

columns. Only select the data that is necessary.

• Optimize the Base Tables: Since views reflect the data from base tables, ensure

those tables are optimized (e.g., with appropriate indexing and normalization).

• Avoid Nested Views: A view based on another view can result in poor performance,

as the inner view’s query needs to be executed each time the outer view is queried.

Stored Procedures & Functions

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

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"]);

}
}
}
}