Sets 4–6: Window Functions, Query Optimization, Transactions and Real Interview Problems
This article continues the Backend Developer SQL series and focuses on the topics typically asked in 3–10 years experienced backend developer interviews.
Topics covered:
✅ Window Functions
✅ ROW_NUMBER, RANK, DENSE_RANK
✅ Common Table Expressions (CTE)
✅ Query Optimization
✅ Indexes
✅ Execution Plans
✅ Transactions
✅ Locking
✅ Isolation Levels
✅ Deadlocks
✅ Real SQL Interview Problems
Additional Sample Table
EMPLOYEE
| EMP_ID | NAME | DEPT | SALARY |
|---|---|---|---|
| 1 | John | IT | 5000 |
| 2 | Mary | HR | 7000 |
| 3 | Steve | IT | 6000 |
| 4 | David | HR | 7000 |
| 5 | Tom | IT | 8000 |
SET 4 – Window Functions
Q1. What does ROW_NUMBER() do?
A. Returns duplicate rows
B. Assigns unique sequence numbers
C. Counts rows
D. Groups rows
Q2.
SELECT NAME,
ROW_NUMBER() OVER(
ORDER BY SALARY DESC)
FROM EMPLOYEE;
What happens?
A. Rows numbered by salary order
B. Compilation error
C. Duplicate numbers possible
D. Groups employees
Q3. Which function gives same rank for ties?
A. ROW_NUMBER
B. RANK
C. COUNT
D. AVG
Q4.
Employees with salaries:
7000
7000
6000
Using RANK().
Result?
A. 1,2,3
B. 1,1,2
C. 1,1,3
D. 1,2,2
Q5.
Same data using DENSE_RANK().
A. 1,1,3
B. 1,2,3
C. 1,1,2
D. 1,2,2
Q6.
SELECT NAME,
SALARY,
RANK() OVER(
ORDER BY SALARY DESC)
FROM EMPLOYEE;
Purpose?
A. Aggregate rows
B. Rank employees by salary
C. Remove duplicates
D. Sort only
Q7. Which clause is mandatory in window functions?
A. WHERE
B. OVER
C. GROUP BY
D. HAVING
Q8. Exception scenario.
What happens?
SELECT ROW_NUMBER()
FROM EMPLOYEE;
A. Works
B. Compilation error
C. Returns NULL
D. Runtime error
Q9. Which SQL feature allows temporary named result sets?
A. Cursor
B. View
C. CTE
D. Index
Q10. Which function skips ranks?
A. ROW_NUMBER
B. DENSE_RANK
C. RANK
D. COUNT
Answers – SET 4
| Q | Answer | Explanation |
|---|---|---|
| 1 | B | ROW_NUMBER assigns unique sequential numbers. |
| 2 | A | Employees sorted and numbered. |
| 3 | B | RANK gives same rank to ties. |
| 4 | C | Ranks 1,1,3 because rank 2 is skipped. |
| 5 | C | DENSE_RANK removes gaps. |
| 6 | B | Ranks employees by salary. |
| 7 | B | OVER clause is mandatory. |
| 8 | B | OVER clause missing. |
| 9 | C | CTE creates temporary named results. |
| 10 | C | RANK skips numbers. |
SET 5 – Transactions and Locking
Q1. Which ACID property prevents partial transactions?
A. Isolation
B. Consistency
C. Atomicity
D. Durability
Q2.
UPDATE ACCOUNT
SET BALANCE = BALANCE - 100;
COMMIT;
Purpose?
A. Undo changes
B. Save changes permanently
C. Lock rows
D. Release index
Q3.
UPDATE ACCOUNT
SET BALANCE = BALANCE - 100;
ROLLBACK;
Result?
A. Changes saved
B. Changes reverted
C. Table deleted
D. Lock remains
Q4. Which isolation level prevents dirty reads?
A. Read Uncommitted
B. Read Committed
C. Serializable
D. Snapshot
Q5. What is a deadlock?
A. Slow query
B. Two transactions waiting for each other
C. Full table scan
D. Index corruption
Q6. Which isolation level provides maximum consistency?
A. Read Uncommitted
B. Read Committed
C. Repeatable Read
D. Serializable
Q7.
Transaction A locks row 1.
Transaction B locks row 2.
A waits for row 2 and B waits for row 1.
This is:
A. Blocking
B. Deadlock
C. Timeout
D. Rollback
Q8. Which command permanently saves changes?
A. SAVEPOINT
B. COMMIT
C. ROLLBACK
D. BEGIN
Q9. Oracle automatically handles deadlocks by:
A. Restarting DB
B. Killing one transaction
C. Dropping tables
D. Rebuilding indexes
Q10. Which statement creates savepoints?
A. SAVEPOINT
B. COMMIT
C. LOCK
D. RELEASE
Answers – SET 5
| Q | Answer | Explanation |
|---|---|---|
| 1 | C | Atomicity ensures all-or-nothing execution. |
| 2 | B | COMMIT permanently saves changes. |
| 3 | B | ROLLBACK undoes changes. |
| 4 | B | Read Committed prevents dirty reads. |
| 5 | B | Transactions wait for each other. |
| 6 | D | Serializable provides strongest isolation. |
| 7 | B | Classic deadlock scenario. |
| 8 | B | COMMIT saves transaction. |
| 9 | B | Oracle selects a victim transaction. |
| 10 | A | SAVEPOINT creates checkpoints. |
SET 6 – Query Optimization and Indexes
Q1. What is an index?
A. Duplicate table
B. Search structure
C. Backup table
D. Temporary table
Q2. Which operation benefits most from indexes?
A. INSERT
B. UPDATE
C. DELETE
D. SELECT
Q3. Which index is automatically created?
A. Foreign Key
B. Check Constraint
C. Primary Key
D. View
Q4.
SELECT *
FROM EMPLOYEE
WHERE EMP_ID = 100;
Best index?
A. Salary index
B. EMP_ID index
C. Name index
D. Composite index
Q5. Full table scan means:
A. Index lookup
B. Entire table read
C. Locking table
D. Corrupted table
Q6. Which SQL statement displays execution plans in Oracle?
A. ANALYZE
B. EXPLAIN PLAN
C. SHOW PLAN
D. PLAN
Q7. Composite index contains:
A. Multiple rows
B. Multiple tables
C. Multiple columns
D. Multiple databases
Q8.
SELECT *
FROM EMPLOYEE
WHERE NAME LIKE '%JOHN%';
Index usage?
A. Always used
B. Usually not used efficiently
C. Mandatory
D. Compilation error
Q9. Which SQL clause is processed last?
A. FROM
B. WHERE
C. ORDER BY
D. GROUP BY
Q10. Which operation generally becomes slower after adding indexes?
A. SELECT
B. INSERT
C. Lookup
D. Search
Answers – SET 6
| Q | Answer | Explanation |
|---|---|---|
| 1 | B | Indexes improve search performance. |
| 2 | D | SELECT benefits most. |
| 3 | C | Primary keys create indexes automatically. |
| 4 | B | Searching by EMP_ID needs EMP_ID index. |
| 5 | B | Entire table must be scanned. |
| 6 | B | Oracle uses EXPLAIN PLAN. |
| 7 | C | Composite indexes contain multiple columns. |
| 8 | B | Leading wildcard limits index usage. |
| 9 | C | ORDER BY executes last. |
| 10 | B | Indexes increase INSERT overhead. |
Real SQL Interview Problems
Problem 1: Second Highest Salary
SELECT MAX(SALARY)
FROM EMPLOYEE
WHERE SALARY <
(
SELECT MAX(SALARY)
FROM EMPLOYEE
);
Problem 2: Department Wise Highest Salary
SELECT DEPT,
MAX(SALARY)
FROM EMPLOYEE
GROUP BY DEPT;
Problem 3: Duplicate Records
SELECT EMP_ID,
COUNT(*)
FROM EMPLOYEE
GROUP BY EMP_ID
HAVING COUNT(*) > 1;
Problem 4: Employees Without Departments
SELECT E.NAME
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D
ON E.DEPT_ID = D.DEPT_ID
WHERE D.DEPT_ID IS NULL;
Advanced Backend Evaluation
| Score | Assessment |
|---|---|
| 27–30 | Outstanding SQL Developer |
| 24–26 | Strong Database Knowledge |
| 20–23 | Good Intermediate Skills |
| 15–19 | Average |
| Below 15 | Needs Improvement |
Topics Covered
✅ Window Functions
✅ ROW_NUMBER
✅ RANK
✅ DENSE_RANK
✅ CTE
✅ Transactions
✅ Locking
✅ Isolation Levels
✅ Deadlocks
✅ Indexes
✅ Query Optimization
✅ Execution Plans
✅ Full Table Scan
✅ Oracle Concepts
✅ Real SQL Problems
Candidates scoring above 80% across all six sets generally possess strong backend database skills expected from experienced Java and Spring Boot developers.
