SQL + PL/SQL Interview Preparation
Guide
Section 1: Topics (Advanced → Beginner)
🔹 Advanced Topics
• Execution Plans & Query Optimization
• Indexes (Clustered, Non-Clustered, Bitmap, Function-based, Composite)
• Window Functions (ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG)
• Recursive CTEs
• Dynamic SQL
• Materialized Views & Inline Views
• Partitioning, Sharding, Parallel Execution
• Optimizer Hints
• Star & Snowflake Schema
• Slowly Changing Dimensions (SCD Types)
• Bulk Collect & FORALL in PL/SQL
• Ref Cursors, Collections, Autonomous Transactions
• Analytical Functions
• Handling Millions of Records
• OLTP vs OLAP Databases
🔹 Intermediate Topics
• Stored Procedures & Functions
• Triggers (Before/After, Row/Statement Level)
• Transaction Management & Isolation Levels
• Deadlocks & Concurrency
• Joins (Inner, Outer, Self, Cross)
• WHERE vs HAVING
• UNION, INTERSECT, EXCEPT
• NVL, ISNULL, COALESCE
• Case Expressions
• Subqueries (Correlated & Non-Correlated)
• Derived & Temporary Tables
• Cursors (Implicit & Explicit)
• Exception Handling in PL/SQL
• Packages (Specification & Body)
• PL/SQL Collections (VARRAY, Nested Table, Associative Array)
• Sequences, Synonyms, Constraints
• Normalization Types (1NF, 2NF, 3NF, BCNF)
• Data Integrity
🔹 Beginner Topics
• SQL & PL/SQL Basics
• DDL, DML, DCL, TCL commands
• SELECT, INSERT, UPDATE, DELETE statements
• Constraints (PK, FK, Unique, Not Null, Check, Default)
• ORDER BY, DISTINCT, LIKE, BETWEEN, IN
• Aggregate Functions (SUM, AVG, MIN, MAX, COUNT)
• NULL Handling
• Tables, Views, Schemas, Databases
• Basic PL/SQL Block Structure
• Variables, Constants, Loops
• DBMS_OUTPUT in PL/SQL
Section 2: SQL + PL/SQL Interview Q&A (100 Questions)
1. Q1: What is an execution plan and how do you read it?
Answer: It shows how the database will execute a query. It includes operations like Index
Scan, Index Seek, Nested Loop, Hash Join, etc. Helps identify performance bottlenecks.
2. Q2: Difference between clustered and non-clustered index?
Answer: Clustered index sorts data physically, one per table. Non-clustered is a separate
structure with pointers to data, multiple allowed per table.
3. Q3: Explain window functions with examples.
Answer: Functions applied over a set of rows while retaining row detail. Example:
ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG().
4. Q4: What is dynamic SQL?
Answer: SQL statements constructed and executed at runtime using EXECUTE IMMEDIATE
in PL/SQL.
5. Q5: Difference between procedure and function in PL/SQL?
Answer: Procedure performs actions and can return multiple values via OUT params,
cannot be used in SELECT. Function must return a value, can be used in SELECT.
6. Q6: What are triggers in PL/SQL?
Answer: Special stored programs that execute automatically on events like INSERT,
UPDATE, DELETE. Types: Before/After, Row/Statement level.
7. Q7: What is a deadlock?
Answer: When two transactions wait for each other’s locked resource. DB resolves by killing
one transaction.
8. Q8: Difference between WHERE and HAVING?
Answer: WHERE filters before aggregation, HAVING filters after aggregation.
9. Q9: What is SQL?
Answer: Structured Query Language, used to query and manage relational databases.
10. Q10: What are DDL commands?
Answer: Data Definition Language: CREATE, ALTER, DROP, TRUNCATE.
11. Q11: What is a primary key?
Answer: A constraint that uniquely identifies rows, not null + unique.
12. Q12: Write a simple PL/SQL block.
Answer: DECLARE v_name VARCHAR2(50); BEGIN SELECT first_name INTO v_name FROM
employees WHERE employee_id=101; DBMS_OUTPUT.PUT_LINE(v_name); END;