Oracle SQL Senior-Level Interview Guide
Comprehensive Coverage of Lessons 1–116
1. Oracle Architecture Fundamentals (Lessons 1–10)
• Database vs Instance (Memory + Background Processes).
• Physical files: Datafiles, Control Files, Redo Logs.
• Logical storage: Tablespaces, Segments, Extents, Blocks.
Interview Q: Difference between Database and Instance?
Answer: Database = physical files; Instance = memory structures + processes managing DB.
2. SQL Execution Order & Query Processing (Lessons 11–20)
Execution Order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.
Predicate filtering and join evaluation.
Alias cannot be used in WHERE because SELECT executes later.
Interview Q: Why is execution order critical in optimization?
Answer: Because filtering early reduces dataset size and improves performance.
3. Advanced SELECT & Filtering (Lessons 21–30)
• Subqueries: Single-row, Multi-row, Correlated.
• EXISTS vs IN performance considerations.
• ROWNUM vs ROW_NUMBER().
Interview Q: Difference between IN and EXISTS?
Answer: EXISTS stops at first match; IN evaluates full result set.
4. Joins Mastery (Lessons 31–40)
• INNER, LEFT, RIGHT, FULL OUTER JOIN.
• Self Join and Non-Equi Join.
• Cartesian Product causes performance issues.
Interview Q: When is LEFT JOIN preferred over INNER JOIN?
Answer: When preserving unmatched rows from left table.
5. Aggregation & Analytical Functions (Lessons 41–55)
• GROUP BY & HAVING.
• Window functions: ROW_NUMBER, RANK, DENSE_RANK.
• PARTITION BY & ORDER BY in analytics.
Interview Q: Difference between RANK and DENSE_RANK?
Answer: RANK skips numbers after ties; DENSE_RANK does not.
6. Data Manipulation & Transactions (Lessons 56–65)
• INSERT, UPDATE, DELETE, MERGE.
• COMMIT, ROLLBACK, SAVEPOINT.
• Transaction isolation basics.
Interview Q: What happens if WHERE is missing in UPDATE?
Answer: All rows will be updated.
7. Indexing Deep Dive (Lessons 66–80)
• Types: B-Tree, Bitmap, Function-Based Index.
• Index selectivity & cardinality.
• When indexes are not used (functions on indexed column).
Interview Q: When should you avoid indexing?
Answer: Low cardinality columns in OLTP (unless bitmap in DW).
8. Execution Plan Analysis (Lessons 81–95)
• EXPLAIN PLAN and DBMS_XPLAN.
• Cost-based optimizer (CBO).
• Full Table Scan vs Index Range Scan.
• Nested Loop vs Hash Join vs Merge Join.
Interview Q: How to improve a bad execution plan?
Answer: Add index, rewrite query, update statistics, avoid functions on indexed columns.
9. Performance Optimization (Lessons 96–110)
• Gather statistics using DBMS_STATS.
• Avoid SELECT *.
• Use bind variables.
• Reduce correlated subqueries when possible.
• Partitioning for large tables.
Interview Q: What affects query performance most?
Answer: Indexing strategy, statistics accuracy, join methods, and data volume.
10. Advanced SQL & Real Interview Scenarios (Lessons 111–116)
• Top-N queries optimization.
• Handling large datasets efficiently.
• Materialized views basics.
• Query rewrite concepts.
Interview Q: Explain how Oracle chooses join method.
Answer: Based on statistics, cardinality estimates, and cost model.
Senior Interview Checklist
✔ Understand execution plan deeply.
✔ Know when to use each index type.
✔ Be able to rewrite slow queries.
✔ Explain optimizer behavior.
✔ Handle real-world performance tuning scenarios.