Oracle SQL, PL/SQL & APEX Interview
Preparation
1. Performance Issue in a Query – Debugging Approach
When a query is slow, follow a step-by-step approach to identify the root cause:
1. Identify the Query – Use V$SQL, AWR, or SQL Monitor reports to find high-resource
queries.
2. Check Execution Plan – Look for full table scans, missing indexes, or costly operations.
3. Index Analysis – Ensure proper indexes exist and are used.
4. Statistics – Verify table/index statistics are updated.
5. Locking & Blocking – Check for blocking sessions.
6. Query Rewrite – Optimize subqueries/joins.
7. System Check – Monitor CPU, memory, and I/O.
2. COMMIT, ROLLBACK, and SAVEPOINT
Command Description
COMMIT Makes all changes in the current
transaction permanent.
ROLLBACK Reverts all changes since the last COMMIT
or SAVEPOINT.
SAVEPOINT Creates a checkpoint in a transaction to
rollback partially.
3. Importance of %TYPE and %ROWTYPE
%TYPE – Declares a variable with the same datatype as a table column or another variable.
Example:
v_sal emp.sal%TYPE;
• Benefit: Auto-syncs with datatype changes.
%ROWTYPE – Declares a record for a full row of a table.
Example:
v_emp emp%ROWTYPE;
• Benefit: Simplifies handling of multiple columns.
4. Difference between Triggers and Constraints
Feature Trigger Constraint
Purpose Executes PL/SQL code Enforces simple data
after/before DML events integrity rules
Scope Complex rules across Simple rules (NOT NULL,
multiple tables PK, FK, UNIQUE, CHECK)
Reliability Can be disabled Always active unless
dropped
Performance Slower (executes Faster (built into DB
procedural code) engine)
5. PL/SQL Script – Print Series 99,96,93…3
SET SERVEROUTPUT ON;
DECLARE
v_num NUMBER := 99;
BEGIN
WHILE v_num >= 3 LOOP
DBMS_OUTPUT.PUT_LINE(v_num);
v_num := v_num - 3;
END LOOP;
END;
/
6. SQL Practical Questions
Q1. Show Dept Name and Employee Count
SELECT d.dname, COUNT(e.empno) AS emp_count
FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno
GROUP BY d.dname;
Q2. Display Top Five Salaries
SELECT employee_name, salary
FROM employee
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;
Q3. Employees Joined in 1981
SELECT * FROM employee
WHERE TO_CHAR(date_of_joining, 'YYYY') = '1981';
