Interview Q&A

Short answer: And query data without modifying the underlying tables directly. Views do not support procedural logic, loops, or variables.

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: View: A view is a virtual table defined by a SELECT query.

Explain a bit more

It provides a way to view and query data without modifying the underlying tables directly. Views do not support procedural logic, loops, or variables. Stored Procedure: A stored procedure is a set of SQL statements that can be executed as a single unit. It can include complex logic like loops, conditionals, and multiple queries. Stored procedures can perform INSERT, UPDATE, DELETE, and other operations on the database. They can also return results. Example of a stored procedure: CREATE PROCEDURE GetEmployeeSalary(IN emp_id INT) BEGIN SELECT salary FROM employees WHERE id = emp_id; END;

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: Optimizing views is important to ensure that your queries are efficient.

Explain a bit more

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.

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: A stored procedure is a precompiled collection of SQL statements that can be executed as a single unit.

Explain a bit more

It allows you to encapsulate logic, such as data manipulation, complex queries, or repetitive tasks, into reusable blocks. Stored procedures can accept parameters, perform actions like SELECT, INSERT, UPDATE, and DELETE, and return results. Benefits: Improved performance (since the procedure is precompiled), code reusability, centralized logic, and security (by restricting direct access to tables).

Example code

CREATE PROCEDURE GetEmployeeDetails (IN emp_id INT) BEGIN SELECT * FROM employees WHERE id = emp_id; END;

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

Explain a bit more

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

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: SQL Server: CREATE PROCEDURE GetEmployeeDetails (@emp_id INT) AS 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…

Explain a bit more

employees WHERE id = emp_id; END // DELIMITER ; The syntax varies slightly between the databases, but the core idea remains the same.

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: Performance: Stored procedures are precompiled, so execution is faster compared to running individual SQL queries each time.

Explain a bit more

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.

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: 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); BEGIN SELECT… salary FROM employees WHERE…… id = @emp_id; END; Calling the procedure: EXEC…

Explain a bit more

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); 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); BEGIN SELECT… salary FROM employees WHERE id = @emp_id; END; Calling the procedure: EXEC GetEmployeeSalary…

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

SQL Server: CREATE PROCEDURE GetEmployeeSalary (@emp_id INT) AS 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);

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: Function: Return Type: Always returns a value (can be scalar or table).

Explain a bit more

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 code

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;

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: Error handling is an essential part of stored procedures.

Explain a bit more

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 ;

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

Example code

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

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: 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"]); } } } }…… wait conn.OpenAsync(); using (SqlCommand cmd = new…

Explain a bit more

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"]); } } } } 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()) {……

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

Example code

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

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: Vendor Lock-In: Stored procedures use database-specific syntax, making it harder to migrate to different database platforms.

Explain a bit more

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.

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

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

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 in SQL is a database object that improves the speed of data retrieval operations on a table.

Explain a bit more

It works by maintaining a sorted order of column values in a separate structure, allowing 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.

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.

Short answer: Indexes improve query performance in several ways:

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.

Short answer: 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 code

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

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.

Short answer: Two ranges [a,b] and [c,d] overlap when a <= d AND c <= b (for closed intervals). Self-join bookings/events with that predicate and exclude the same Id.

Sample solution

SELECT a.Id AS Id1, b.Id AS Id2
FROM Bookings a
INNER JOIN Bookings b ON a.Id < b.Id
    AND a.StartDate <= b.EndDate
    AND b.StartDate <= a.EndDate;

Clarify inclusive/exclusive end times — hotel checkout logic often uses half-open intervals.

Short answer: Clustered index defines physical row order (one per table, often PK). Nonclustered is a separate structure pointing to heap/clustered keys. Queries filtering/sorting on clustered keys can avoid sorts and lookups.

Relate to query plans: Clustered Index Seek vs Key Lookup vs Bookmark Lookup.

Short answer: Compute each employee’s salary share within department via Salary / SUM(Salary) OVER (PARTITION BY DepartmentId).

Sample solution

SELECT EmpId, DepartmentId, Salary,
       CAST(100.0 * Salary / SUM(Salary) OVER (PARTITION BY DepartmentId) AS DECIMAL(5,2)) AS DeptPct
FROM Employees;

Window aggregates keep detail rows — unlike GROUP BY alone.

Short answer: DELETE with WHERE DepartmentId IN (departments where ManagerId IS NULL) or use JOIN. Preview with SELECT first.

Sample solution

DELETE e
FROM Employees e
INNER JOIN Departments d ON d.DepartmentId = e.DepartmentId
WHERE d.ManagerId IS NULL;

In interviews, always say you would run SELECT with the same FROM/WHERE before DELETE.

Short answer: CTEs are named query scopes (not persisted). Temp tables (#t) live in tempdb, support indexes/statistics, good for larger intermediate sets. Table variables (@t) are lighter but historically weaker stats — fine for small sets. Choose based on size and reuse.

Do not claim CTEs are always “faster” — they are about readability/recursion first.