Top 50 Advanced SQL Questions asked in Top Companies (with Answers + Examples)

1. What is a Partition By clause?

Answer: It divides the result set into partitions and performs operations within each partition.

Example:

SELECT Name, Salary,
RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS DeptRank
FROM Employees;

---

2. Explain PIVOT and UNPIVOT operations.

Answer:

• PIVOT: Rotate rows into columns.

• UNPIVOT: Rotate columns into rows.

Example (PIVOT):

SELECT Department, [2019], [2020]
FROM (SELECT Department, Year, Salary FROM Employees) AS SourceTable
PIVOT (SUM(Salary) FOR Year IN ([2019], [2020])) AS PivotTable;

---

3. What is Dynamic SQL?

Answer: SQL statements built and executed at runtime.

Example:

DECLARE @sql NVARCHAR(MAX);
SET @sql = ‘SELECT * FROM Employees WHERE DepartmentID = 2’;
EXEC sp_executesql @sql;

---

4. What is JSON data in SQL Server?

Answer: Store and query JSON (JavaScript Object Notation) data inside SQL tables.

Example:

SELECT *
FROM Employees
WHERE JSON_VALUE(EmployeeData, ‘$.Address.City’) = ‘New York’;

---

5. What are Temporal Tables?

Answer: Tables that automatically track the full history of data changes.

Example:

ALTER TABLE Employees
ADD ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN,
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);
ALTER TABLE Employees
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = EmployeesHistory));

---

6. What is a Columnstore Index?

Answer: Stores data column-wise instead of row-wise. Great for analytics.

Example: CREATE CLUSTERED COLUMNSTORE INDEX idx_colstore ON Sales;

---

7. What is Row-Level Security (RLS)?

Answer: Restricts data access for users at the row level.

Example:

CREATE SECURITY POLICY EmployeeFilter
ADD FILTER PREDICATE dbo.fn_securitypredicate(UserID) ON dbo.Employees;

---

8. What are Isolation Levels?

Answer: Controls the visibility of transactions. Types:

• • Read Uncommitted

  • Read Committed

  • Repeatable Read

  • Serializable

  • Snapshot

---

9. Explain what Snapshot Isolation is.

Answer: Transactions work on a version of the database as it existed at the start.

---

10. What is a Table Hint?

Answer: Special instructions to override SQL Server’s default query optimizer behavior.

Example: SELECT * FROM Employees WITH (NOLOCK);

Page : 1 2 3 4 5

Top 50 Basic Level SQL Interview Questions and Answers with Examples

Top 50 Intermediate SQL Interview Questions You Must Practice [With Examples]