Interview Q&A

Short answer: A materialized view is a database object that stores the result of a query physically, similar to an indexed view but more generalized.

Explain a bit more

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 code

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.

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: To delete a view, you use the DROP VIEW statement. This removes the view from the database entirely.

Example code

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.

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

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: Consider creating an index when:

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: The query optimizer in the database engine decides which index to use based on several factors:

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

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: Use STRING_SPLIT (SQL Server 2016+), or OPENJSON, or a numbers table. Note STRING_SPLIT historically lacked guaranteed order unless using the enable_ordinal parameter on newer versions.

Sample solution

SELECT LTRIM(RTRIM(value)) AS Tag
FROM STRING_SPLIT('sql,interview,t-sql', ',');

Ask about ordering requirements before picking STRING_SPLIT.

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.