SQL Advanced Topics & Scenarios

Complete Guide with Queries and Answers

150+ Questions | DDL, DML, Advanced Queries, Transactions, Views, Indexes & More
Generated on: August 04, 2026
Table of Contents

1. DDL - Data Definition Language (15 questions)

2. INSERT, UPDATE & DELETE (15 questions)
3. CASE Statement (15 questions)
4. EXISTS / NOT EXISTS (10 questions)
5. UNION / INTERSECT / EXCEPT (10 questions)
6. CTE - Common Table Expressions (10 questions)
7. Transactions (10 questions)
8. Views (10 questions)
9. Indexes (10 questions)
10. Advanced Scenarios (15 questions)
11. Bonus Real Interview Questions (30 questions)
DDL - Data Definition Language (1-15)

Q1. Create a table with PRIMARY KEY.

Query:
CREATE TABLE Employee (EmployeeID INT PRIMARY KEY, EmployeeName VARCHAR(100), Department
VARCHAR(50));

Explanation: PRIMARY KEY uniquely identifies each row. Automatically creates a clustered index. NULL values
not allowed. Only one PRIMARY KEY per table.

Q2. Create a table with FOREIGN KEY.

Query:
CREATE TABLE Employee (EmployeeID INT PRIMARY KEY, DepartmentID INT FOREIGN KEY REFERENCES
Department(DepartmentID));

Explanation: FOREIGN KEY maintains referential integrity. References PRIMARY KEY in another table. Prevents
orphan records.

Q3. Add a UNIQUE constraint to an existing table.

Query:
ALTER TABLE Employee ADD CONSTRAINT UQ_Email UNIQUE (Email);

Explanation: UNIQUE allows NULL values (multiple NULLs are allowed). Unlike PRIMARY KEY, multiple UNIQUE
constraints per table possible.
Q4. Add a CHECK constraint for salary > 0.
Query:
ALTER TABLE Employee ADD CONSTRAINT CHK_Salary CHECK (Salary > 0);

Explanation: CHECK constraint validates data before insert/update. Enforces business rules like salary must be
positive.

Q5. Add a DEFAULT value to a column.

Query:
ALTER TABLE Employee ADD CONSTRAINT DF_HireDate DEFAULT GETDATE() FOR HireDate;

Explanation: DEFAULT provides automatic value if none specified. Reduces NULL values and ensures
consistency.

Q6. Drop a PRIMARY KEY constraint.

Query:
ALTER TABLE Employee DROP CONSTRAINT PK_Employee; -- Or: DROP CONSTRAINT EmployeeID;

Explanation: Removes PRIMARY KEY constraint. Any FOREIGN KEYs referencing this must be dropped first.
Q7. Rename a table.
Query:
EXEC sp_rename 'Employee', 'EmployeeData'; -- MySQL: RENAME TABLE Employee TO EmployeeData;

Explanation: Changes table name. Different syntax for SQL Server (sp_rename) vs MySQL (RENAME TABLE).

Q8. Rename a column.

Query:
EXEC sp_rename '[Link]', 'EmployeeName'; -- Or: ALTER TABLE Employee CHANGE EmpName
EmployeeName VARCHAR(100);

Explanation: Renames column. SQL Server uses sp_rename, MySQL uses ALTER TABLE CHANGE. Updates all
references.

Q9. Change a column's data type.

Query:
ALTER TABLE Employee ALTER COLUMN Salary DECIMAL(10,2);

Explanation: Changes existing column type. May lose data if converting from larger to smaller type. Check
compatibility first.
Q10. Add a new column to an existing table.
Query:
ALTER TABLE Employee ADD PhoneNumber VARCHAR(15);

Explanation: Adds new column. Can specify DEFAULT to populate existing rows. NULL for current rows if no
default.

Q11. Drop a column.

Query:
ALTER TABLE Employee DROP COLUMN PhoneNumber;

Explanation: Permanently removes column and all data. Cannot undo. Ensure column is not referenced by
views/constraints.

Q12. Truncate a table.

Query:
TRUNCATE TABLE Employee;

Explanation: Removes all rows without logging individual row deletes. Faster than DELETE. Identity seed resets
(SQL Server). Cannot use WHERE clause.
Q13. Delete all records without deleting the table.
Query:
DELETE FROM Employee;

Explanation: Removes all records but keeps table structure. Slower than TRUNCATE (logs each delete). Allows
WHERE clause. Identity NOT reset.

Q14. Drop a table permanently.

Query:
DROP TABLE Employee;

Explanation: Removes table structure and data completely. Cannot be recovered (unless backup). Removes
associated constraints/indexes.

Q15. Explain the difference between DELETE, TRUNCATE, and DROP.

Query:
DELETE: DELETE FROM table; TRUNCATE: TRUNCATE TABLE table; DROP: DROP TABLE table;

Explanation: DELETE: DML, removes rows with WHERE, slower, logs rows, identity unchanged. TRUNCATE:
DDL, removes all rows, faster, no WHERE, identity reset (SQL Server). DROP: DDL, removes table structure.
INSERT, UPDATE & DELETE (16-30)

Q16. Insert multiple records into Employee.

Query:
INSERT INTO Employee (EmployeeID, EmployeeName, Salary) VALUES (1, 'Harini', 35000), (2,
'Priya', 40000), (3, 'Raj', 38000);

Explanation: Single INSERT with multiple VALUES clauses. More efficient than separate INSERTs. Values must
match column order/count.

Q17. Copy data from one table to another.

Query:
INSERT INTO EmployeeBackup SELECT * FROM Employee;

Explanation: Inserts results of SELECT query. Target table must exist. Column order/types must match. Great for
backups/archiving.

Q18. Update salary by 10% for all employees.

Query:
UPDATE Employee SET Salary = Salary * 1.10;

Explanation: Updates all rows since no WHERE clause. Uses expression to calculate new value. Affects all
records.
Q19. Update salary only for HR department.
Query:
UPDATE Employee SET Salary = Salary * 1.10 WHERE Department = 'HR';

Explanation: WHERE clause limits update to specific rows. Only HR department salaries increased.

Q20. Increase salary by ■5000 for employees with experience > 5 years.
Query:
UPDATE Employee SET Salary = Salary + 5000 WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 5;

Explanation: Uses DATEDIFF to calculate years of service. Combines condition on calculated field. Rewards
experience.

Q21. Delete employees earning less than ■20,000.

Query:
DELETE FROM Employee WHERE Salary < 20000;

Explanation: WHERE clause targets specific rows. Only employees with salary < 20000 deleted. Logs each delete.
Q22. Delete customers who never placed orders.
Query:
DELETE FROM Customer WHERE CustomerID NOT IN (SELECT DISTINCT CustomerID FROM Orders);

Explanation: Uses subquery to find customers with no orders. Subquery identifies inactive customers.

Q23. Update NULL ManagerID to 1.

Query:
UPDATE Employee SET ManagerID = 1 WHERE ManagerID IS NULL;

Explanation: IS NULL checks for missing values. Assigns default manager to employees without one.

Q24. Replace all NULL salaries with 25000.

Query:
UPDATE Employee SET Salary = 25000 WHERE Salary IS NULL;

Explanation: Fills NULL values with default salary. Ensures all employees have salary data.
Q25. Copy only Chennai employees into another table.
Query:
INSERT INTO ChennaiEmployees SELECT * FROM Employee WHERE City = 'Chennai';

Explanation: Copies filtered data. WHERE clause selects only Chennai records. Useful for city-specific analysis.

Q26. Insert records using SELECT.

Query:
INSERT INTO EmployeeArchive SELECT * FROM Employee WHERE YEAR(HireDate) < 2020;

Explanation: Inserts old employee records to archive table. SELECT determines which rows to insert.

Q27. Update department names to uppercase.

Query:
UPDATE Employee SET Department = UPPER(Department);

Explanation: UPPER() converts text. Standardizes department names for consistency.

Q28. Delete duplicate employee records.
Query:
DELETE FROM Employee WHERE EmployeeID NOT IN (SELECT MIN(EmployeeID) FROM Employee GROUP BY
EmployeeName, Salary);

Explanation: Keeps MIN(EmployeeID) per name/salary combo, deletes rest. Assumes duplicate = same name &
salary.

Q29. Update product price by category.

Query:
UPDATE Product SET Price = Price * 1.15 WHERE Category = 'Electronics';

Explanation: Different updates for different categories. Can have multiple UPDATE statements for different groups.

Q30. Restore deleted data using transaction rollback.

Query:
BEGIN TRANSACTION; DELETE FROM Employee WHERE Salary < 20000; ROLLBACK;

Explanation: ROLLBACK undoes changes within transaction. COMMIT saves changes. Demo: delete then rollback
restores data.
CASE Statement (31-45)

Q31. Display "High", "Medium", "Low" salary categories.

Query:
SELECT EmployeeName, Salary, CASE WHEN Salary > 50000 THEN 'High' WHEN Salary > 30000 THEN
'Medium' ELSE 'Low' END as SalaryCategory FROM Employee;

Explanation: CASE with multiple WHEN conditions. Assigns category based on salary range. ELSE provides
default.

Q32. Display Pass/Fail based on marks.

Query:
SELECT StudentName, Marks, CASE WHEN Marks >= 40 THEN 'Pass' ELSE 'Fail' END as Result FROM
Student;

Explanation: Simple CASE with one condition. >= 40 passes, below fails. Typical pass threshold.

Q33. Show Bonus based on salary slabs.

Query:
SELECT EmployeeName, Salary, CASE WHEN Salary > 60000 THEN Salary * 0.20 WHEN Salary > 40000
THEN Salary * 0.15 ELSE Salary * 0.10 END as Bonus FROM Employee;

Explanation: Multiple slabs with different bonus percentages. Higher earners get higher bonus percentage.
Hierarchical conditions.
Q34. Convert gender code into Male/Female.
Query:
SELECT EmployeeName, CASE WHEN GenderCode = 'M' THEN 'Male' WHEN GenderCode = 'F' THEN 'Female'
ELSE 'Other' END as Gender FROM Employee;

Explanation: Converts codes to readable values. Makes reports user-friendly. Handles unknown codes with ELSE.

Q35. Display Working/Resigned status.

Query:
SELECT EmployeeName, CASE WHEN ResignationDate IS NULL THEN 'Working' ELSE 'Resigned' END as
Status FROM Employee;

Explanation: IS NULL vs IS NOT NULL logic. NULL ResignationDate = still working. Useful for employee analytics.

Q36. Classify employees based on experience.

Query:
SELECT EmployeeName, DATEDIFF(YEAR, HireDate, GETDATE()) as Experience, CASE WHEN
DATEDIFF(YEAR, HireDate, GETDATE()) >= 10 THEN 'Senior' WHEN DATEDIFF(YEAR, HireDate,
GETDATE()) >= 5 THEN 'Mid-level' ELSE 'Junior' END as Level FROM Employee;

Explanation: Combines DATEDIFF for calculated field in CASE. Classifies by years of service.
Q37. Find tax percentage using CASE.
Query:
SELECT EmployeeName, Salary, CASE WHEN Salary > 1000000 THEN 0.30 WHEN Salary > 500000 THEN
0.20 WHEN Salary > 250000 THEN 0.10 ELSE 0 END as TaxRate FROM Employee;

Explanation: Tax brackets using CASE. Realistic tax slab scenario. ELSE for lowest bracket.

Q38. Show Grade A/B/C/D from marks.

Query:
SELECT StudentName, Marks, CASE WHEN Marks >= 80 THEN 'A' WHEN Marks >= 70 THEN 'B' WHEN Marks
>= 60 THEN 'C' WHEN Marks >= 50 THEN 'D' ELSE 'F' END as Grade FROM Student;

Explanation: Grading system with CASE. Common academic use case. Ranges for each grade.

Q39. Categorize products into Cheap, Medium, Expensive.

Query:
SELECT ProductName, Price, CASE WHEN Price < 1000 THEN 'Cheap' WHEN Price < 5000 THEN 'Medium'
ELSE 'Expensive' END as Category FROM Product;

Explanation: Product pricing tiers. Helps in inventory and marketing segmentation.

Q40. Replace NULL city with "Unknown".
Query:
SELECT EmployeeName, CASE WHEN City IS NULL THEN 'Unknown' ELSE City END as City FROM Employee;
-- OR: COALESCE(City, 'Unknown')

Explanation: CASE or COALESCE both work. COALESCE is simpler for single replacement. CASE more flexible
for multiple conditions.

Q41. Show "Weekend" or "Weekday" from order date.

Query:
SELECT OrderDate, CASE WHEN DATEPART(WEEKDAY, OrderDate) IN (1,7) THEN 'Weekend' ELSE 'Weekday'
END as DayType FROM Orders;

Explanation: DATEPART(WEEKDAY) returns day number. 1=Sunday, 7=Saturday (SQL Server). Different across
databases.

Q42. Display age groups.

Query:
SELECT EmployeeName, DATEDIFF(YEAR, DateOfBirth, GETDATE()) as Age, CASE WHEN DATEDIFF(YEAR,
DateOfBirth, GETDATE()) < 25 THEN 'Young' WHEN DATEDIFF(YEAR, DateOfBirth, GETDATE()) < 40 THEN
'Mid-age' ELSE 'Senior' END as AgeGroup FROM Employee;

Explanation: Age calculation with DATEDIFF and CASE. Demographic segmentation.

Q43. Find shift based on login time.
Query:
SELECT EmployeeName, LoginTime, CASE WHEN DATEPART(HOUR, LoginTime) < 12 THEN 'Morning' WHEN
DATEPART(HOUR, LoginTime) < 18 THEN 'Afternoon' ELSE 'Night' END as Shift FROM Attendance;

Explanation: DATEPART extracts hour from time. Determines shift based on login hour.

Q44. Categorize customers based on purchase amount.

Query:
SELECT CustomerName, SUM(Amount) as TotalPurchase, CASE WHEN SUM(Amount) > 100000 THEN 'VIP'
WHEN SUM(Amount) > 50000 THEN 'Premium' ELSE 'Regular' END as CustomerType FROM Orders GROUP BY
CustomerID, CustomerName;

Explanation: Combines GROUP BY with CASE. Customer segmentation by spending. Aggregate function in
CASE.

Q45. Assign performance ratings.

Query:
SELECT EmployeeName, CASE WHEN (SELECT COUNT(*) FROM ProjectAssignment WHERE EmployeeID =
[Link]) > 5 THEN 'Excellent' WHEN (SELECT COUNT(*) FROM ProjectAssignment WHERE
EmployeeID = [Link]) > 3 THEN 'Good' ELSE 'Average' END as Rating FROM Employee e;

Explanation: Uses subquery in CASE. Rates based on project count. Correlated subquery for each employee.
EXISTS / NOT EXISTS (46-55)

Q46. Find employees assigned to projects.

Query:
SELECT * FROM Employee e WHERE EXISTS (SELECT 1 FROM ProjectAssignment pa WHERE [Link] =
[Link]);

Explanation: EXISTS checks if subquery returns any rows. If EXISTS true, outer query returns employee. More
efficient than IN for large datasets.

Q47. Find employees without projects.

Query:
SELECT * FROM Employee e WHERE NOT EXISTS (SELECT 1 FROM ProjectAssignment pa WHERE
[Link] = [Link]);

Explanation: NOT EXISTS finds employees with no matching projects. Returns unassigned employees.

Q48. Find customers with orders.

Query:
SELECT * FROM Customer c WHERE EXISTS (SELECT 1 FROM Orders o WHERE [Link] =
[Link]);

Explanation: EXISTS efficient for checking existence. Returns only customers who ordered.
Q49. Find customers without orders.
Query:
SELECT * FROM Customer c WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE [Link] =
[Link]);

Explanation: NOT EXISTS finds inactive/new customers. Never placed order.

Q50. Find products that have sales.

Query:
SELECT * FROM Product p WHERE EXISTS (SELECT 1 FROM Sales s WHERE [Link] = [Link]);

Explanation: Identifies sold products. EXISTS faster than JOIN for this check.

Q51. Find products with no sales.

Query:
SELECT * FROM Product p WHERE NOT EXISTS (SELECT 1 FROM Sales s WHERE [Link] =
[Link]);

Explanation: Finds unsold inventory. Useful for removing products from catalog.
Q52. Find departments having employees.
Query:
SELECT * FROM Department d WHERE EXISTS (SELECT 1 FROM Employee e WHERE [Link] =
[Link]);

Explanation: Only departments with staff. Ignores empty departments.

Q53. Find departments with no employees.

Query:
SELECT * FROM Department d WHERE NOT EXISTS (SELECT 1 FROM Employee e WHERE [Link] =
[Link]);

Explanation: Empty departments. Candidates for consolidation or closure.

Q54. Find managers supervising employees.

Query:
SELECT DISTINCT m.* FROM Employee m WHERE EXISTS (SELECT 1 FROM Employee e WHERE [Link] =
[Link]);

Explanation: Managers who have subordinates. Uses self-join logic with EXISTS.
Q55. Find employees who are not managers.
Query:
SELECT * FROM Employee e WHERE NOT EXISTS (SELECT 1 FROM Employee e2 WHERE [Link] =
[Link]);

Explanation: Individual contributors without reports. Front-line staff.

UNION / INTERSECT / EXCEPT (56-65)

Q56. Combine customer names from two tables.

Query:
SELECT CustomerName FROM OldCustomers UNION SELECT CustomerName FROM NewCustomers;

Explanation: UNION removes duplicates. Returns unique customer names from both tables.

Q57. Remove duplicates while combining.

Query:
SELECT City FROM Employees UNION SELECT City FROM Customers;

Explanation: UNION automatically removes duplicates. Each city listed once even if in both tables.
Q58. Combine all records including duplicates.
Query:
SELECT City FROM Employees UNION ALL SELECT City FROM Customers;

Explanation: UNION ALL keeps duplicates. Faster than UNION (no deduplication). City appears multiple times if
duplicate.

Q59. Find common employees between two branches.

Query:
SELECT EmployeeName FROM BranchA_Employee INTERSECT SELECT EmployeeName FROM BranchB_Employee;

Explanation: INTERSECT returns only rows in both queries. Employees in both branches. Works on names.

Q60. Find employees present in Branch A but not Branch B.

Query:
SELECT EmployeeName FROM BranchA_Employee EXCEPT SELECT EmployeeName FROM BranchB_Employee;

Explanation: EXCEPT returns rows in first query but not second. Exclusive to Branch A. Branch B exclusive
employees not shown.
Q61. Find products sold online and offline.
Query:
SELECT ProductID FROM OnlineSales UNION SELECT ProductID FROM OfflineSales;

Explanation: Products with any sales channel. Shows omnichannel products.

Q62. Find products sold only online.

Query:
SELECT ProductID FROM OnlineSales EXCEPT SELECT ProductID FROM OfflineSales;

Explanation: Online-only products. Products never sold offline.

Q63. Merge sales data from multiple years.

Query:
SELECT * FROM Sales_2022 UNION ALL SELECT * FROM Sales_2023 UNION ALL SELECT * FROM Sales_2024;

Explanation: Combines historical data. UNION ALL preserves all records from each year.
Q64. Display common cities between customers and employees.
Query:
SELECT City FROM Customer INTERSECT SELECT City FROM Employee;

Explanation: Cities with both customers and employees. Common locations.

Q65. Find cities existing only in Employee table.

Query:
SELECT City FROM Employee EXCEPT SELECT City FROM Customer;

Explanation: Employee-only cities. No customers in these locations.

CTE - Common Table Expressions (66-75)

Q66. Use CTE to find employees above average salary.

Query:
WITH AvgSalary AS (SELECT AVG(Salary) as AvgSal FROM Employee) SELECT * FROM Employee WHERE
Salary > (SELECT AvgSal FROM AvgSalary);

Explanation: CTE (AvgSalary) calculates average once. Main query uses CTE result. More readable than
subquery.
Q67. Use CTE to calculate department totals.
Query:
WITH DeptTotal AS (SELECT Department, SUM(Salary) as TotalSal, COUNT(*) as EmpCount FROM
Employee GROUP BY Department) SELECT Department, TotalSal, TotalSal/EmpCount as AvgSal FROM
DeptTotal;

Explanation: CTE stores grouped data. Main query uses CTE for calculations. Cleaner logic separation.

Q68. Use CTE to find highest salary.

Query:
WITH TopSalary AS (SELECT MAX(Salary) as MaxSal FROM Employee) SELECT * FROM Employee WHERE
Salary = (SELECT MaxSal FROM TopSalary);

Explanation: Named result set for clarity. Reusable in query. Alternative to nested subqueries.

Q69. Use recursive CTE to display hierarchy.

Query:
WITH RECURSIVE EmployeeHierarchy AS (SELECT EmployeeID, EmployeeName, ManagerID, 0 as Level
FROM Employee WHERE ManagerID IS NULL UNION ALL SELECT [Link], [Link],
[Link], [Link] + 1 FROM Employee e INNER JOIN EmployeeHierarchy h ON [Link] =
[Link]) SELECT * FROM EmployeeHierarchy;

Explanation: Recursive CTE: base case (top managers), recursive case (adds subordinates). Shows org hierarchy
with levels.
Q70. Generate numbers from 1 to 100 using recursive CTE.
Query:
WITH RECURSIVE Numbers AS (SELECT 1 as N UNION ALL SELECT N+1 FROM Numbers WHERE N < 100)
SELECT * FROM Numbers;

Explanation: Simple recursive CTE for number generation. Useful for generating sequences without actual table
data.

Q71. Display organization chart.

Query:
WITH RECURSIVE OrgChart AS (SELECT EmployeeID, EmployeeName, ManagerID, 1 as Hierarchy FROM
Employee WHERE ManagerID IS NULL UNION ALL SELECT [Link], [Link], [Link],
[Link] + 1 FROM Employee e INNER JOIN OrgChart oc ON [Link] = [Link]) SELECT
REPLICATE('--', Hierarchy-1) + EmployeeName as EmployeeHierarchy FROM OrgChart ORDER BY
Hierarchy;

Explanation: Recursive CTE with indentation. Shows tree structure. REPLICATE for visual hierarchy.

Q72. Find cumulative sales using CTE.

Query:
WITH CumulativeSales AS (SELECT OrderDate, Amount, SUM(Amount) OVER (ORDER BY OrderDate) as
CumulativeTotal FROM Sales) SELECT * FROM CumulativeSales;

Explanation: CTE with window function. Shows running total. ORDER BY OrderDate ensures sequence.
Q73. Display running balance.
Query:
WITH RunningBalance AS (SELECT TransactionDate, Amount, SUM(Amount) OVER (ORDER BY
TransactionDate) as Balance FROM BankTransactions) SELECT * FROM RunningBalance;

Explanation: Running sum for account balance. Each row shows cumulative total to that point.

Q74. Find duplicate employees using CTE.

Query:
WITH DuplicateEmployee AS (SELECT EmployeeName, Email, ROW_NUMBER() OVER (PARTITION BY Email
ORDER BY EmployeeID) as rn FROM Employee) SELECT * FROM DuplicateEmployee WHERE rn > 1;

Explanation: CTE with ROW_NUMBER to identify duplicates. Rows with rn > 1 are duplicates. Based on Email
uniqueness.

Q75. Delete duplicate rows using CTE.

Query:
WITH DuplicateRows AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY Email ORDER BY EmployeeID) as
rn FROM Employee) DELETE FROM DuplicateRows WHERE rn > 1;

Explanation: Uses CTE in DELETE statement. Keeps first occurrence (rn=1), deletes rest. Clean duplicate removal.
Transactions (76-85)

Q76. Start a transaction.

Query:
BEGIN TRANSACTION; INSERT INTO Employee VALUES (5, 'NewEmp', 30000); COMMIT;

Explanation: BEGIN TRANSACTION starts atomic unit. COMMIT saves changes. All-or-nothing execution.
Ensures data consistency.

Q77. Commit transaction after insert.

Query:
BEGIN TRANSACTION; INSERT INTO Employee VALUES (5, 'NewEmp', 30000); COMMIT;

Explanation: COMMIT finalizes changes. After COMMIT, changes permanent. Before COMMIT, changes visible
only in transaction.

Q78. Rollback transaction after update.

Query:
BEGIN TRANSACTION; UPDATE Employee SET Salary = 50000 WHERE EmployeeID = 1; ROLLBACK;

Explanation: ROLLBACK undoes all changes. Update never applied. Returns to pre-transaction state. Used for
error handling.
Q79. Use savepoints.
Query:
BEGIN TRANSACTION; INSERT INTO Employee VALUES (5, 'NewEmp', 30000); SAVE TRANSACTION SP1;
UPDATE Employee SET Salary = 40000; ROLLBACK TRANSACTION SP1;

Explanation: Savepoint marks checkpoint. ROLLBACK TO SP1 undoes only changes after SP1. Partial rollback
within transaction.

Q80. Rollback to savepoint.

Query:
BEGIN TRANSACTION; INSERT INTO Employee VALUES (5, 'NewEmp', 30000); SAVE TRANSACTION SP1;
DELETE FROM Employee WHERE Salary < 20000; ROLLBACK TRANSACTION SP1; COMMIT;

Explanation: Returns to savepoint state. INSERT kept, DELETE undone. COMMIT applies remaining changes.

Q81. Commit multiple updates.

Query:
BEGIN TRANSACTION; UPDATE Employee SET Salary = 50000 WHERE Dept = 'HR'; UPDATE Employee SET
Salary = 45000 WHERE Dept = 'Finance'; COMMIT;

Explanation: Multiple statements in one transaction. All succeed or all fail. Maintains consistency across multiple
updates.
Q82. Transfer money between accounts.
Query:
BEGIN TRANSACTION; UPDATE BankAccount SET Balance = Balance - 1000 WHERE AccountID = 1; UPDATE
BankAccount SET Balance = Balance + 1000 WHERE AccountID = 2; COMMIT;

Explanation: Classic transaction example. Both updates must succeed. If second fails, both ROLLBACK. No partial
transfer.

Q83. Undo accidental delete.

Query:
BEGIN TRANSACTION; DELETE FROM Employee WHERE Salary < 20000; ROLLBACK;

Explanation: ROLLBACK restores deleted records. Delete never persisted. Data recovery within transaction scope.

Q84. Lock a table during update.

Query:
BEGIN TRANSACTION; UPDATE Employee SET Salary = Salary * 1.10 WITH (ROWLOCK); COMMIT;

Explanation: ROWLOCK prevents concurrent access. Ensures data isolation during update. Other transactions
wait.
Q85. Explain ACID properties using SQL examples.
Query:
A(Atomicity): All-or-nothing transaction. C(Consistency): Data valid before/after.
I(Isolation): Transactions separate. D(Durability): Committed data permanent.

Explanation: Atomicity: BEGIN...COMMIT either all succeed or all fail. Consistency: constraints maintained.
Isolation: one transaction doesn't affect others. Durability: committed data survives failures.

Views (86-95)

Q86. Create employee view.

Query:
CREATE VIEW EmployeeView AS SELECT EmployeeID, EmployeeName, Department, Salary FROM Employee;

Explanation: Virtual table based on query. Query executes when view accessed. Simplifies complex queries.
Stored query definition.

Q87. Create department salary summary view.

Query:
CREATE VIEW DeptSalarySummary AS SELECT Department, COUNT(*) as EmpCount, AVG(Salary) as
AvgSal, SUM(Salary) as TotalSal FROM Employee GROUP BY Department;

Explanation: Aggregated view with GROUP BY. Provides summary statistics. Hides complex grouping logic.
Q88. Update records through view.
Query:
UPDATE EmployeeView SET Salary = 50000 WHERE EmployeeID = 1;

Explanation: If view based on single table, updates work on underlying table. Updates through view must affect
base table directly.

Q89. Drop a view.

Query:
DROP VIEW EmployeeView;

Explanation: Removes view definition. Underlying tables unchanged. Removes view from database.

Q90. Create read-only view.

Query:
CREATE VIEW ReadOnlyEmployee AS SELECT EmployeeID, EmployeeName, Salary FROM Employee WITH
CHECK OPTION;

Explanation: WITH CHECK OPTION restricts inserts/updates. Records must satisfy view WHERE conditions (if
exists). Enforces view logic.
Q91. Create view joining multiple tables.
Query:
CREATE VIEW EmployeeDeptView AS SELECT [Link], [Link], [Link], [Link]
FROM Employee e INNER JOIN Department d ON [Link] = [Link];

Explanation: View with JOIN. Simplifies joining frequent table combinations. Query complex joins transparently.

Q92. Find top salary using view.

Query:
CREATE VIEW TopSalaryView AS SELECT TOP 10 EmployeeName, Salary FROM Employee ORDER BY Salary
DESC; SELECT * FROM TopSalaryView;

Explanation: View with TOP clause. Provides top earners easily. Encapsulates ranking logic.

Q93. Hide confidential columns using view.

Query:
CREATE VIEW PublicEmployeeView AS SELECT EmployeeID, EmployeeName, Department FROM Employee;

Explanation: View excludes sensitive columns (Salary, SSN). Users query view, not base table. Security through
obscurity.
Q94. Replace existing view.
Query:
ALTER VIEW EmployeeView AS SELECT EmployeeID, EmployeeName, Department, Salary, HireDate FROM
Employee;

Explanation: ALTER VIEW modifies view definition. Adds HireDate to previous version. Maintains view
name/permissions.

Q95. Compare View and Materialized View.

Query:
View: Dynamic, no storage, query executes each time. Materialized View: Static, stored data,
refreshed periodically.

Explanation: View: Virtual, latest data always. Materialized View: Actual table, faster access, potentially stale.
Tradeoff: freshness vs performance.

Indexes (96-105)

Q96. Create index on Employee Name.

Query:
CREATE INDEX IDX_EmployeeName ON Employee(EmployeeName);

Explanation: Non-clustered index on EmployeeName. Speeds up searches/sorts by name. Creates index structure.
Q97. Create composite index.
Query:
CREATE INDEX IDX_DeptSalary ON Employee(Department, Salary);

Explanation: Index on multiple columns. Efficient for searches on both Department & Salary. Column order matters.

Q98. Drop index.

Query:
DROP INDEX IDX_EmployeeName ON Employee;

Explanation: Removes index. Frees storage. Slows down queries that used this index. Speeds up updates/inserts.

Q99. Find whether index is used.

Query:
SELECT * FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID('YourDB') AND object_id =
OBJECT_ID('Employee');

Explanation: sys.dm_db_index_usage_stats shows index usage. Queries: seeks, scans, lookups count. Identifies
unused indexes.
Q100. Compare clustered vs non-clustered index.
Query:
Clustered: One per table, determines row order, PRIMARY KEY default. Non-clustered: Multiple
per table, separate structure, faster for specific columns.

Explanation: Clustered: Physical table order, fastest range queries. Non-clustered: Pointer structure, multiple
possible. Clustered usually on PK, non-clustered on frequent search columns.

Q101. Find slow queries.

Query:
SELECT TOP 10 [Link] as QueryText, qs.total_worker_time as TotalTime FROM
sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt ORDER BY
qs.total_worker_time DESC;

Explanation: Queries consuming most CPU. Top 10 by total_worker_time. Candidates for optimization/indexing.

Q102. Improve query performance.

Query:
Add indexes on frequently searched columns. Use EXPLAIN PLAN to see execution plan. Optimize
WHERE clauses. Avoid functions on indexed columns. Use proper joins.

Explanation: Strategies: indexing, query rewrites, query plan analysis. Remove unnecessary columns/rows. Use
EXISTS over COUNT(*). Proper indexes on WHERE/JOIN columns.
Q103. Create unique index.
Query:
CREATE UNIQUE INDEX IDX_Email ON Employee(Email);

Explanation: Unique index enforces uniqueness like UNIQUE constraint. Prevents duplicate values. Useful for
optional unique fields.

Q104. Explain covering index.

Query:
CREATE INDEX IDX_Cover ON Employee(Department) INCLUDE (Salary, HireDate);

Explanation: Index includes non-key columns. Query satisfied entirely from index. No lookup to base table. Faster
queries.

Q105. Explain index scan vs index seek.

Query:
Seek: Direct access, fast, used with good indexes. Scan: Reads all index/table pages, slower,
used when no suitable index.

Explanation: Seek: Targeted lookup like EmployeeID = 5. Scan: Searches every row like Salary > 50000 without
index. Seek better for large tables.
Advanced Scenarios (106-120)

Q106. Find employees hired on the same day.

Query:
SELECT HireDate, COUNT(*) as EmpCount FROM Employee GROUP BY HireDate HAVING COUNT(*) > 1 ORDER
BY HireDate;

Explanation: Groups by HireDate, counts employees. HAVING > 1 shows dates with multiple hires. Shows
recruitment patterns.

Q107. Find salary difference between consecutive employees.

Query:
SELECT EmployeeID, EmployeeName, Salary, LAG(Salary) OVER (ORDER BY EmployeeID) as PrevSalary,
Salary - LAG(Salary) OVER (ORDER BY EmployeeID) as SalaryDiff FROM Employee;

Explanation: LAG gets previous salary. Calculates difference. Shows salary progression. Ordered by EmployeeID.

Q108. Find longest employee name.

Query:
SELECT TOP 1 EmployeeName, LEN(EmployeeName) as NameLength FROM Employee ORDER BY
LEN(EmployeeName) DESC;

Explanation: LEN() calculates name length. ORDER BY DESC, TOP 1 gets longest. Uses function in ORDER BY.
Q109. Find shortest employee name.
Query:
SELECT TOP 1 EmployeeName, LEN(EmployeeName) as NameLength FROM Employee ORDER BY
LEN(EmployeeName) ASC;

Explanation: ORDER BY ASC finds shortest. Reverse of longest query. Could have multiple employees with same
length.

Q110. Find palindrome names.

Query:
SELECT EmployeeName FROM Employee WHERE EmployeeName = REVERSE(EmployeeName);

Explanation: REVERSE() flips string. Palindrome equals its reverse. Rare in names but interesting data quality
check.

Q111. Find duplicate phone numbers.

Query:
SELECT PhoneNumber, COUNT(*) as Count FROM Employee GROUP BY PhoneNumber HAVING COUNT(*) > 1;

Explanation: Same phone for multiple employees. Data quality issue. May indicate data entry errors or family
members.
Q112. Find employees having birthdays this month.
Query:
SELECT * FROM Employee WHERE MONTH(DateOfBirth) = MONTH(GETDATE());

Explanation: MONTH() extracts month. Compares to current month. Useful for birthday greetings/celebrations.

Q113. Find leap-year join dates.

Query:
SELECT * FROM Employee WHERE MONTH(HireDate) = 2 AND DAY(HireDate) = 29;

Explanation: Feb 29 only exists in leap years. DAY() = 29 and MONTH() = 2. Very rare dates.

Q114. Find products never purchased in the last year.

Query:
SELECT p.* FROM Product p LEFT JOIN Sales s ON [Link] = [Link] AND [Link] >=
DATEADD(YEAR, -1, GETDATE()) WHERE [Link] IS NULL;

Explanation: LEFT JOIN with date condition. WHERE ProductID IS NULL means no sales in last year. Candidates
for removal.
Q115. Find inactive customers.
Query:
SELECT c.* FROM Customer c LEFT JOIN Orders o ON [Link] = [Link] AND [Link] >=
DATEADD(YEAR, -1, GETDATE()) WHERE [Link] IS NULL;

Explanation: No orders in last year. Reactivation targets. Potential for win-back campaigns.

Q116. Find top-selling product every month.

Query:
WITH MonthlySales AS (SELECT YEAR(SaleDate) as Y, MONTH(SaleDate) as M, ProductID,
SUM(Quantity) as Qty, ROW_NUMBER() OVER (PARTITION BY YEAR(SaleDate), MONTH(SaleDate) ORDER BY
SUM(Quantity) DESC) as rn FROM Sales GROUP BY YEAR(SaleDate), MONTH(SaleDate), ProductID)
SELECT Y, M, ProductID, Qty FROM MonthlySales WHERE rn = 1;

Explanation: CTE with ROW_NUMBER and PARTITION BY. Top 1 per month. Shows seasonal top products.

Q117. Find department contributing maximum salary.

Query:
SELECT TOP 1 Department, SUM(Salary) as TotalSalary FROM Employee GROUP BY Department ORDER BY
SUM(Salary) DESC;

Explanation: Department with highest total payroll. Budget allocation indicator. Usually largest department.
Q118. Find customer with highest average order value.
Query:
SELECT TOP 1 CustomerID, CustomerName, AVG(Amount) as AvgOrderValue FROM Customer c INNER JOIN
Orders o ON [Link] = [Link] GROUP BY [Link], [Link] ORDER BY
AVG(Amount) DESC;

Explanation: High-value customer per order (not total spend). VIP candidates. Average order value metric.

Q119. Find percentage contribution of each department to total salary.

Query:
SELECT Department, SUM(Salary) as DeptSalary, ROUND((SUM(Salary) / (SELECT SUM(Salary) FROM
Employee)) * 100, 2) as PercentageContribution FROM Employee GROUP BY Department;

Explanation: Subquery for total salary. Calculates percentage per department. Shows salary distribution.

Q120. Generate invoice numbers using SQL.

Query:
SELECT 'INV-' + FORMAT(YEAR(OrderDate), '0000') + FORMAT(MONTH(OrderDate), '00') +
FORMAT(ROW_NUMBER() OVER (PARTITION BY YEAR(OrderDate), MONTH(OrderDate) ORDER BY OrderDate),
'00000') as InvoiceNumber FROM Orders;

Explanation: Concatenates parts: prefix + year + month + sequence. ROW_NUMBER for sequential numbering per
month. Generates unique invoice IDs.
Bonus Real Interview Questions (121-150)

Q121. Find the median salary.

Query:
WITH SalaryRank AS (SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary) as rn, COUNT(*) OVER ()
as TotalCount FROM Employee) SELECT AVG(Salary) as MedianSalary FROM SalaryRank WHERE rn IN
((TotalCount + 1)/2, (TotalCount + 2)/2);

Explanation: ROW_NUMBER ranks salaries. Median is middle value(s). For odd count: middle value. For even
count: avg of two middle values.

Q122. Find the mode of salaries.

Query:
SELECT TOP 1 Salary FROM Employee GROUP BY Salary ORDER BY COUNT(*) DESC;

Explanation: Mode = most frequent value. GROUP BY salary, count occurrences. ORDER BY count DESC, TOP 1
= most frequent.

Q123. Find employees earning exactly the average salary.

Query:
SELECT * FROM Employee WHERE Salary = (SELECT AVG(Salary) FROM Employee);

Explanation: Subquery calculates average. WHERE equality finds exact matches. May return 0 rows if no exact
match.
Q124. Find employees whose salary is a prime number.
Query:
This is complex SQL depending on database. May require recursive CTE to check primality or
number theory calculations.

Explanation: Advanced scenario requiring prime number logic. Rarely needed in practice. Tests advanced SQL
logic.

Q125. Find customers who purchased at least three different products.

Query:
SELECT CustomerID, COUNT(DISTINCT ProductID) as ProductCount FROM Orders GROUP BY CustomerID
HAVING COUNT(DISTINCT ProductID) >= 3;

Explanation: DISTINCT ProductID avoids counting duplicates. GROUP BY customer, HAVING >= 3. Identifies
multi-product buyers.

Q126. Find products purchased by every customer.

Query:
SELECT ProductID FROM Orders GROUP BY ProductID HAVING COUNT(DISTINCT CustomerID) = (SELECT
COUNT(DISTINCT CustomerID) FROM Orders);

Explanation: Products with customer count = total customers. Must be bought by every customer. Very rare
scenario.
Q127. Find departments where all employees earn above ■50,000.
Query:
SELECT Department FROM Employee GROUP BY Department HAVING MIN(Salary) > 50000;

Explanation: MIN(Salary) ensures minimum in dept > 50k. If min > 50k, all > 50k. Efficient check.

Q128. Find employees who joined on weekends.

Query:
SELECT * FROM Employee WHERE DATEPART(WEEKDAY, HireDate) IN (1, 7);

Explanation: DATEPART(WEEKDAY) = 1 for Sunday, 7 for Saturday (SQL Server). Different on MySQL. Unlikely
hiring day.

Q129. Calculate year-over-year sales growth.

Query:
WITH YearlySales AS (SELECT YEAR(OrderDate) as Y, SUM(Amount) as TotalSales FROM Sales GROUP BY
YEAR(OrderDate)) SELECT Y, TotalSales, LAG(TotalSales) OVER (ORDER BY Y) as PrevYearSales,
ROUND((TotalSales - LAG(TotalSales) OVER (ORDER BY Y)) / LAG(TotalSales) OVER (ORDER BY Y) *
100, 2) as GrowthPercent FROM YearlySales;

Explanation: Year totals calculated. LAG gets previous year. Growth = (current - previous) / previous * 100. Shows
trends.
Q130. Find employees with gaps in employment history.
Query:
SELECT [Link], [Link], [Link] FROM EmploymentHistory e1 INNER JOIN
EmploymentHistory e2 ON [Link] = [Link] AND [Link] > DATEADD(DAY, 1,
[Link]);

Explanation: Self-join on employment records. Finds gaps between end and next start date. Identifies career
breaks.

Q131. Find customers with increasing monthly purchases.

Query:
WITH MonthlyCust AS (SELECT CustomerID, YEAR(OrderDate) as Y, MONTH(OrderDate) as M,
SUM(Amount) as Sales FROM Orders GROUP BY CustomerID, YEAR(OrderDate), MONTH(OrderDate)) SELECT
DISTINCT [Link] FROM MonthlyCust c1 INNER JOIN MonthlyCust c2 ON [Link] =
[Link] AND (c1.Y > c2.Y OR (c1.Y = c2.Y AND c1.M = c2.M + 1)) AND [Link] > [Link];

Explanation: Monthly totals per customer. Self-join compares consecutive months. Sales must increase
month-over-month.

Q132. Find the longest consecutive sales streak.

Query:
WITH DateDiff AS (SELECT SaleDate, ROW_NUMBER() OVER (ORDER BY SaleDate) - ROW_NUMBER() OVER
(PARTITION BY DayName ORDER BY SaleDate) as Grp FROM Sales), ConsecutiveGroups AS (SELECT Grp,
COUNT(*) as StreakLength FROM DateDiff GROUP BY Grp) SELECT MAX(StreakLength) as LongestStreak
FROM ConsecutiveGroups;

Explanation: Complex logic: gap-and-island pattern. Identifies consecutive date sequences. Streak = unbroken
sequence.
Q133. Find missing dates in sales records.
Query:
WITH Dates AS (SELECT DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1, (SELECT
MIN(SaleDate) FROM Sales)) as D FROM Sales), MissedDates AS (SELECT D FROM Dates WHERE D NOT IN
(SELECT DISTINCT CAST(SaleDate as DATE) FROM Sales)) SELECT * FROM MissedDates;

Explanation: Generates all dates in range. Finds dates with no sales. Identifies gaps in sales records. Date audit.

Q134. Pivot monthly sales into columns.

Query:
SELECT CustomerID, [January] as Jan, [February] as Feb, [March] as Mar FROM (SELECT CustomerID,
DATENAME(MONTH, OrderDate) as Month, SUM(Amount) as Amount FROM Sales GROUP BY CustomerID,
DATENAME(MONTH, OrderDate)) T PIVOT (SUM(Amount) FOR Month IN ([January], [February], [March]))
as P;

Explanation: PIVOT rotates months to columns. Shows customer sales by month. Useful for dashboards/reports.

Q135. Unpivot quarterly sales into rows.

Query:
SELECT * FROM (SELECT Q1, Q2, Q3, Q4 FROM QuarterlySales) T UNPIVOT (Sales FOR Quarter IN (Q1,
Q2, Q3, Q4)) as U;

Explanation: UNPIVOT converts columns to rows. Converts denormalized data to normalized. Opposite of PIVOT.
Q136. Calculate moving average of sales.
Query:
SELECT SaleDate, Amount, AVG(Amount) OVER (ORDER BY SaleDate ROWS BETWEEN 6 PRECEDING AND
CURRENT ROW) as MovingAvg7 FROM Sales;

Explanation: Window function with ROWS BETWEEN. 7-day moving average (current + 6 previous). Smooths data
trends.

Q137. Find the top 10% highest-paid employees.

Query:
SELECT * FROM Employee WHERE Salary >= (SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY
Salary) FROM Employee);

Explanation: PERCENTILE_CONT calculates 90th percentile. Employees at/above 90th percentile = top 10%.
Statistical approach.

Q138. Detect duplicate email addresses ignoring case.

Query:
SELECT LOWER(Email), COUNT(*) as Count FROM Employee GROUP BY LOWER(Email) HAVING COUNT(*) > 1;

Explanation: LOWER() converts to lowercase for comparison. Case-insensitive duplicates. Groups on lowercase
version.
Q139. Split comma-separated values into rows.
Query:
SELECT EmployeeID, value FROM Employee CROSS APPLY STRING_SPLIT(Skills, ',');

Explanation: STRING_SPLIT breaks delimited string into rows. One skill per row. Normalizes denormalized data.

Q140. Merge duplicate customer records.

Query:
BEGIN TRANSACTION; UPDATE Orders SET CustomerID = (SELECT MIN(CustomerID) FROM Customer WHERE
Email = @Email) WHERE CustomerID IN (SELECT CustomerID FROM Customer WHERE Email = @Email);
DELETE FROM Customer WHERE CustomerID != (SELECT MIN(CustomerID) FROM Customer WHERE Email =
@Email) AND Email = @Email; COMMIT;

Explanation: Identifies duplicates by email. Merges to lowest ID. Updates foreign keys. Deletes duplicates.
Transaction for safety.

Q141. Find products with declining sales for three consecutive months.
Query:
WITH MonthlySales AS (SELECT ProductID, YEAR(SaleDate) as Y, MONTH(SaleDate) as M,
SUM(Quantity) as Qty, LAG(SUM(Quantity)) OVER (PARTITION BY ProductID ORDER BY YEAR(SaleDate),
MONTH(SaleDate)) as PrevQty FROM Sales GROUP BY ProductID, YEAR(SaleDate), MONTH(SaleDate))
SELECT ProductID FROM MonthlySales WHERE Qty < PrevQty GROUP BY ProductID HAVING COUNT(*) >= 3;

Explanation: LAG compares consecutive months. Declining = current < previous. 3+ declining months. Problem
products.
Q142. Identify employees whose salary increased every year.
Query:
WITH YearlySalary AS (SELECT EmployeeID, YEAR(ReviewDate) as Y, Salary, LAG(Salary) OVER
(PARTITION BY EmployeeID ORDER BY YEAR(ReviewDate)) as PrevSal FROM EmployeeSalaryHistory)
SELECT DISTINCT EmployeeID FROM YearlySalary WHERE Salary > PrevSal GROUP BY EmployeeID HAVING
COUNT(*) = (SELECT COUNT(DISTINCT YEAR(ReviewDate)) FROM EmployeeSalaryHistory) - 1;

Explanation: Salary history table required. Every year higher than previous. All increases = consistent growth.

Q143. Find customers who ordered on consecutive days.

Query:
WITH Dates AS (SELECT DISTINCT CustomerID, OrderDate, LAG(OrderDate) OVER (PARTITION BY
CustomerID ORDER BY OrderDate) as PrevDate FROM Orders WHERE DATEDIFF(DAY, LAG(OrderDate) OVER
(PARTITION BY CustomerID ORDER BY OrderDate), OrderDate) = 1) SELECT DISTINCT CustomerID FROM
Dates WHERE DATEDIFF(DAY, PrevDate, OrderDate) = 1;

Explanation: Consecutive days = difference of 1. DATEDIFF(DAY) = 1. Shows frequent/engaged customers.

Q144. Generate a calendar table using SQL.

Query:
WITH Dates AS (SELECT CAST('2024-01-01' as DATE) as D UNION ALL SELECT DATEADD(DAY, 1, D) FROM
Dates WHERE D < '2024-12-31') SELECT D, DATENAME(WEEKDAY, D) as DayName, MONTH(D) as MonthNum,
DAY(D) as DayNum, WEEK(D) as WeekNum FROM Dates;

Explanation: Recursive CTE generates date range. DATENAME, MONTH, DAY, WEEK extract components.
Useful for date joins.
Q145. Calculate business days between two dates.
Query:
SELECT COUNT(*) as BusinessDays FROM (WITH Dates AS (SELECT DATEADD(DAY, ROW_NUMBER() OVER
(ORDER BY (SELECT NULL)) - 1, @StartDate) as D FROM Sales) SELECT D FROM Dates WHERE D <=
@EndDate AND DATEPART(WEEKDAY, D) NOT IN (1, 7)) T;

Explanation: Generates all dates in range. Filters weekdays (WEEKDAY NOT IN 1,7). Counts business days only.
Excludes weekends.

Q146. Find the first non-NULL value in a group.

Query:
SELECT EmployeeID, FIRST_VALUE(ManagerID) OVER (PARTITION BY Department ORDER BY EmployeeID
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as FirstManager FROM Employee;

Explanation: FIRST_VALUE returns first value in window. PARTITION BY groups. ROWS BETWEEN defines
frame. Or COALESCE with multiple columns.

Q147. Replace gaps in sequence numbers.

Query:
WITH Gaps AS (SELECT Id, ROW_NUMBER() OVER (ORDER BY Id) as rn FROM TableName), Consecutive AS
(SELECT Id, rn, Id - rn as Grp FROM Gaps) SELECT MIN(Id) as GapStart, MAX(Id) as GapEnd,
COUNT(*) as GapSize FROM Consecutive WHERE Grp IN (SELECT Grp FROM Consecutive GROUP BY Grp
HAVING COUNT(*) > 1);

Explanation: Gap-and-island pattern. Identifies missing sequences. Islands = consecutive ranges, gaps = missing
numbers.
Q148. Build an employee hierarchy using recursive queries.
Query:
WITH RECURSIVE Hierarchy AS (SELECT EmployeeID, EmployeeName, ManagerID, 0 as Level FROM
Employee WHERE ManagerID IS NULL UNION ALL SELECT [Link], [Link], [Link],
[Link] + 1 FROM Employee e INNER JOIN Hierarchy h ON [Link] = [Link]) SELECT * FROM
Hierarchy ORDER BY Level, EmployeeName;

Explanation: Base case: top managers (NULL ManagerID). Recursive: adds subordinates at each level. Shows org
structure with levels.

Q149. Find circular manager relationships.

Query:
WITH RECURSIVE CircularCheck AS (SELECT EmployeeID, ManagerID, CAST(EmployeeID as VARCHAR(MAX))
as Path FROM Employee WHERE ManagerID IS NOT NULL UNION ALL SELECT [Link], [Link],
CAST(Path + ',' + CAST([Link] as VARCHAR(MAX)) as VARCHAR(MAX)) FROM Employee e INNER
JOIN CircularCheck c ON [Link] = [Link] WHERE Path NOT LIKE '%' + CAST([Link]
as VARCHAR(MAX)) + '%') SELECT * FROM CircularCheck WHERE Path LIKE '%' + CAST(ManagerID as
VARCHAR(MAX)) + '%';

Explanation: Recursive CTE builds path. Detects cycles = employee is own manager indirectly. Path contains
loops. Data quality issue.

Q150. Write a query to compare two tables and return only the changed rows.
Query:
SELECT COALESCE([Link], [Link]) as ID, [Link] as OldValue, [Link] as NewValue FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON [Link] = [Link] WHERE [Link] != [Link] OR [Link] IS NULL OR [Link]
IS NULL;

Explanation: FULL OUTER JOIN shows all rows from both tables. WHERE filters to changed/missing rows.
OldValue from t1, NewValue from t2.