31. What is Dynamic SQL?
Answer: SQL commands constructed and executed at runtime.
Example:
DECLARE @sql NVARCHAR(MAX);
SET @sql = ‘SELECT * FROM Employees WHERE DepartmentID = 2’;
EXEC sp_executesql @sql;
32. How to prevent SQL Injection in Dynamic SQL?
Answer: Always use parameterized queries instead of string concatenation.
Example:
DECLARE @DeptID INT = 2;
EXEC sp_executesql
N’SELECT * FROM Employees WHERE DepartmentID = @DeptID’,
N’@DeptID INT’,
@DeptID;
33. What is an Isolation Level in SQL?
Answer: Determines how/when changes made by one operation are visible to other operations.
| Isolation Level | Description |
|---|---|
| READ UNCOMMITTED | Dirty reads allowed |
| READ COMMITTED | No dirty reads |
| REPEATABLE READ | No dirty, no non-repeatable reads |
| SERIALIZABLE | Full isolation |
| SNAPSHOT | Versioned rows |
34. What is Dirty Read?
Answer: Reading uncommitted data from another transaction.
Example:
-
Transaction 1 updates a row but not committed.
-
Transaction 2 reads the updated value.
35. What is a Phantom Read?
Answer: New rows added/removed by another transaction during the current transaction.
36. Explain the difference between RANK(), DENSE_RANK(), and ROW_NUMBER().
Answer:
| Function | Behavior |
|---|---|
| RANK() | Skips ranking after ties |
| DENSE_RANK() | No gaps after ties |
| ROW_NUMBER() | Unique sequential number |
Example:
SELECT Name, Salary,
RANK() OVER (ORDER BY Salary DESC) AS RankNo,
DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRankNo,
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees;
37. What is Sharding in SQL?
Answer: Distributing data across multiple databases/servers to handle massive loads and improve scalability.
38. What is a Materialized View?
Answer: A view that stores the actual query result physically for faster access.
Example: In Oracle and some other systems:
39. What is the COALESCE function?
Answer: Returns the first non-null value in the list.
Example: SELECT COALESCE(NULL, NULL, 'Hello', 'World') AS Result;
Output: Hello
40. What are Table-Valued Parameters (TVP)?
Answer: Allows sending multiple rows as a parameter to stored procedures.
Example:
— Step 1: Create a table type
CREATE TYPE EmployeeType AS TABLE (ID INT, Name NVARCHAR(50));
— Step 2: Use in stored procedure
CREATE PROCEDURE InsertEmployees
@EmpList EmployeeType READONLY
AS
BEGIN
INSERT INTO Employees(ID, Name)
SELECT ID, Name FROM @EmpList;
END
Top 50 Basic Level SQL Interview Questions and Answers with Examples
Top 50 Intermediate SQL Interview Questions You Must Practice [With Examples]
1 thought on “Top 50 Advanced SQL Questions asked in Top Companies (with Answers + Examples)”