100 SQL Querying Questions (Basic + Intermediate)

— Detailed Explanations
Prepared for Oracle OFSS Associate Consultant interview practice

Q1: Select all columns from table EMPLOYEES.

Answer: SELECT * FROM EMPLOYEES; Explanation: '*' selects all columns. Use explicitly
named columns in production to avoid unnecessary data transfer.

Q2: Retrieve employees with salary > 50000.

Answer: SELECT * FROM EMPLOYEES WHERE salary > 50000; Explanation: WHERE filters
rows before any grouping or aggregation.

Q3: Find employees in department 10.

Answer: SELECT emp_name FROM EMPLOYEES WHERE dept_id = 10; Explanation: Simple
equality filter on dept_id column.

Q4: Count total employees.

Answer: SELECT COUNT(*) AS total_emps FROM EMPLOYEES; Explanation: COUNT(*) counts
all rows; COUNT(column) ignores NULLs in that column.

Q5: Count employees per department.

Answer: SELECT dept_id, COUNT(*) FROM EMPLOYEES GROUP BY dept_id; Explanation:
GROUP BY groups rows; aggregate functions compute per-group values.

Q6: Average salary overall.

Answer: SELECT AVG(salary) AS avg_sal FROM EMPLOYEES; Explanation: AVG ignores NULLs;
good to handle zero rows (returns NULL).

Q7: Maximum salary per department.

Answer: SELECT dept_id, MAX(salary) FROM EMPLOYEES GROUP BY dept_id; Explanation:
MAX returns highest value in each group.

Q8: Employees hired after 2020-01-01.

Answer: SELECT emp_name, hire_date FROM EMPLOYEES WHERE hire_date > DATE
'2020-01-01'; Explanation: Use DATE literal or TO_DATE in Oracle; ensure correct datatype
comparisons.

Q9: Find second highest salary (using subquery).

Answer: SELECT MAX(salary) FROM EMPLOYEES WHERE salary < (SELECT MAX(salary) FROM
EMPLOYEES); Explanation: Outer query finds max less than overall max; handles duplicates
depending on data.

Q10: Second highest salary using ROW_NUMBER().

Answer: SELECT salary FROM (SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC)
rn FROM EMPLOYEES) WHERE rn = 2; Explanation: ROW_NUMBER assigns unique ranks; good

Page 1
when you want exact nth row even with ties.

Q11: Second highest using DENSE_RANK().

Answer: SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC)
dr FROM EMPLOYEES) WHERE dr = 2; Explanation: DENSE_RANK gives same rank to ties and
no gaps; use when ties share same rank.

Q12: Difference between WHERE and HAVING.

Answer: WHERE filters rows before GROUP BY; HAVING filters groups after aggregation.
Example: SELECT dept_id, COUNT(*) FROM EMPLOYEES WHERE salary>30000 GROUP BY
dept_id HAVING COUNT(*)>2;

Q13: INNER JOIN between EMPLOYEES and DEPARTMENTS.

Answer: SELECT e.emp_name, d.dept_name FROM EMPLOYEES e JOIN DEPARTMENTS d ON
e.dept_id = d.dept_id; Explanation: INNER JOIN returns rows matching in both tables; aliasing
improves readability.

Q14: LEFT JOIN example.

Answer: SELECT e.emp_name, d.dept_name FROM EMPLOYEES e LEFT JOIN DEPARTMENTS d
ON e.dept_id = d.dept_id; Explanation: Returns all employees; dept_name NULL if no
matching department.

Q15: RIGHT JOIN example.

Answer: SELECT e.emp_name, d.dept_name FROM EMPLOYEES e RIGHT JOIN DEPARTMENTS
d ON e.dept_id = d.dept_id; Explanation: Returns all departments; employees NULL when no
match. Not supported in some DBs (use LEFT with switched tables).

Q16: FULL OUTER JOIN example.

Answer: SELECT e.emp_name, d.dept_name FROM EMPLOYEES e FULL OUTER JOIN
DEPARTMENTS d ON e.dept_id = d.dept_id; Explanation: Combines left and right joins;
missing rows show NULLs; expensive operation.

Q17: Self join to get manager names.

Answer: SELECT e.emp_name AS employee, m.emp_name AS manager FROM EMPLOYEES e
LEFT JOIN EMPLOYEES m ON e.manager_id = m.emp_id; Explanation: Self-join uses same
table twice to relate employees to managers.

Q18: Find employees with NULL manager_id.

Answer: SELECT emp_name FROM EMPLOYEES WHERE manager_id IS NULL; Explanation:
NULL comparisons use IS/IS NOT; equality operator won't work.

Q19: Find employees whose name starts with 'A'.

Answer: SELECT emp_name FROM EMPLOYEES WHERE emp_name LIKE 'A%'; Explanation:
LIKE with % wildcard matches prefix; case sensitivity depends on collation.

Q20: Find employees whose name contains 'an'.

Answer: SELECT emp_name FROM EMPLOYEES WHERE emp_name LIKE '%an%'; Explanation:
% on both sides finds substring anywhere.

Page 2
Q21: Use IN with list of departments.
Answer: SELECT emp_name FROM EMPLOYEES WHERE dept_id IN (10,20); Explanation: IN
checks membership; efficient if list small; otherwise join is preferred.

Q22: Use NOT IN to find unassigned department employees.

Answer: SELECT emp_name FROM EMPLOYEES WHERE dept_id NOT IN (SELECT dept_id FROM
DEPARTMENTS); Explanation: Beware of NULLs in subquery result; NOT IN returns no rows if
subquery contains NULL. Use NOT EXISTS instead.

Q23: Use EXISTS to avoid NULL pitfalls.

Answer: SELECT emp_name FROM EMPLOYEES e WHERE EXISTS (SELECT 1 FROM
DEPARTMENTS d WHERE d.dept_id = e.dept_id); Explanation: EXISTS checks row existence
and handles NULLs safely; correlated subquery.

Q24: Correlated subquery example (salary > dept avg).

Answer: SELECT emp_name, salary FROM EMPLOYEES e WHERE salary > (SELECT
AVG(salary) FROM EMPLOYEES WHERE dept_id = e.dept_id); Explanation: Subquery
references outer query; computes per-row department average.

Q25: Find departments with more than 2 employees.

Answer: SELECT dept_id FROM EMPLOYEES GROUP BY dept_id HAVING COUNT(*) > 2;
Explanation: HAVING filters groups after aggregation.

Q26: Use DISTINCT to get unique salaries.

Answer: SELECT DISTINCT salary FROM EMPLOYEES; Explanation: DISTINCT removes
duplicates; can be applied to multiple columns.

Q27: UNION vs UNION ALL.

Answer: UNION removes duplicates between result sets; UNION ALL preserves duplicates and
is faster since no deduplication.

Q28: INTERSECT example.

Answer: SELECT emp_name FROM EMPLOYEES WHERE dept_id = 10 INTERSECT SELECT
emp_name FROM EMPLOYEES WHERE salary > 50000; Explanation: INTERSECT returns rows
common to both queries.

Q29: MIN and MAX usage.

Answer: SELECT MIN(salary), MAX(salary) FROM EMPLOYEES; Explanation: Aggregate
functions over full table or grouped sets.

Q30: Use CASE for conditional logic.

Answer: SELECT emp_name, salary, CASE WHEN salary > 60000 THEN 'High' WHEN salary
>= 45000 THEN 'Medium' ELSE 'Low' END AS grade FROM EMPLOYEES; Explanation: CASE
returns values conditionally; useful for bucketing.

Q31: UPDATE example to give bonus to IT dept.

Page 3
Answer: UPDATE EMPLOYEES SET salary = salary * 1.10 WHERE dept_id = 30; Explanation:
UPDATE modifies rows; always test with SELECT first or use transaction/rollback.

Q32: DELETE example to remove temp hires.

Answer: DELETE FROM EMPLOYEES WHERE hire_date > DATE '2025-01-01' AND is_temp = 1;
Explanation: Use WHERE to avoid deleting all rows; backup before destructive ops.

Q33: INSERT example.

Answer: INSERT INTO EMPLOYEES(emp_id, emp_name, dept_id, salary) VALUES (201, 'Nina',
10, 52000); Explanation: Specify columns to avoid errors if table schema changes.

Q34: Multi-row insert (Oracle 12c+).

Answer: INSERT ALL INTO EMPLOYEES(emp_id, emp_name, dept_id, salary) VALUES
(301,'Sam',20,48000) INTO EMPLOYEES(emp_id,emp_name,dept_id,salary) VALUES
(302,'Lia',30,51000) SELECT * FROM DUAL; Explanation: INSERT ALL allows multiple rows in
Oracle; other DBs use multi-row VALUES.

Q35: CREATE VIEW example.

Answer: CREATE VIEW v_emp_dept AS SELECT e.emp_id, e.emp_name, d.dept_name FROM
EMPLOYEES e JOIN DEPARTMENTS d ON e.dept_id = d.dept_id; Explanation: View simplifies
complex queries and can provide security/abstraction.

Q36: Materialized view concept.

Answer: Materialized view stores precomputed results for faster access; must be refreshed to
reflect base table changes.

Q37: Use analytic function LAG.

Answer: SELECT emp_id, salary, LAG(salary) OVER (ORDER BY hire_date) prev_salary FROM
EMPLOYEES; Explanation: LAG accesses previous row's value in defined window; useful for
change calculations.

Q38: LEAD usage.

Answer: SELECT emp_id, salary, LEAD(salary) OVER (ORDER BY hire_date) next_salary FROM
EMPLOYEES; Explanation: LEAD looks forward; both LAG and LEAD are non-aggregating
analytic functions.

Q39: ROW_NUMBER vs RANK vs DENSE_RANK.

Answer: ROW_NUMBER gives unique sequential numbers; RANK gives tied ranks with gaps;
DENSE_RANK gives tied ranks without gaps. Use based on tie-handling required.

Q40: Pagination using ROW_NUMBER.

Answer: SELECT * FROM (SELECT e.*, ROW_NUMBER() OVER (ORDER BY emp_id) rn FROM
EMPLOYEES e) WHERE rn BETWEEN 11 AND 20; Explanation: Useful for paging results in
applications.

Q41: String function CONCAT or ||.

Answer: SELECT emp_name || ' - ' || dept_id AS info FROM EMPLOYEES; Explanation: Operator
varies by DB; CONCAT() or || in Oracle; watch NULL propagation.

Page 4
Q42: TRIM, LTRIM, RTRIM usage.
Answer: SELECT TRIM(emp_name) FROM EMPLOYEES; Explanation: Removes whitespace;
LTRIM/RTRIM remove left/right sides. Useful when data has padding.

Q43: SUBSTR example.

Answer: SELECT SUBSTR(emp_name,1,3) FROM EMPLOYEES; Explanation: Extract
substring—parameters vary slightly across DBs (start index 1 in Oracle).

Q44: INSTR example to find position.

Answer: SELECT INSTR(emp_name,'a') FROM EMPLOYEES; Explanation: Returns position
index; 0 or NULL if not found depending on DB.

Q45: TO_CHAR for date formatting.

Answer: SELECT TO_CHAR(hire_date,'DD-MON-YYYY') FROM EMPLOYEES; Explanation: Format
date into readable string; useful for reports.

Q46: TO_DATE for inserting dates.

Answer: INSERT INTO EMPLOYEES(emp_id, emp_name, hire_date) VALUES (401,'Raj',
TO_DATE('2021-05-01','YYYY-MM-DD')); Explanation: Ensure correct format masks; DB may
accept DATE literals.

Q47: DATE arithmetic example (days since hire).

Answer: SELECT emp_name, SYSDATE - hire_date AS days_with_company FROM EMPLOYEES;
Explanation: In Oracle, subtracting dates returns days (fractional). Use proper units for DB
used.

Q48: Use NVL or COALESCE to handle NULLs.

Answer: SELECT emp_name, NVL(manager_id,0) FROM EMPLOYEES; Explanation: NVL
(Oracle) or COALESCE (ANSI) returns first non-NULL; useful to avoid NULL propagation.

Q49: Find running total of salaries.

Answer: SELECT emp_id, salary, SUM(salary) OVER (ORDER BY emp_id ROWS BETWEEN
UNBOUNDED PRECEDING AND CURRENT ROW) running_total FROM EMPLOYEES; Explanation:
Window SUM computes cumulative totals per ordered set.

Q50: Partitioned window example.

Answer: SELECT dept_id, emp_name, salary, SUM(salary) OVER (PARTITION BY dept_id
ORDER BY emp_id) dept_running_total FROM EMPLOYEES; Explanation: PARTITION BY creates
separate windows per department.

Q51: Find gaps in serial numbers (e.g., emp_id).

Answer: SELECT (t.emp_id + 1) AS missing_start, MIN(t2.emp_id)-1 AS missing_end FROM
(SELECT emp_id FROM EMPLOYEES) t LEFT JOIN (SELECT emp_id FROM EMPLOYEES) t2 ON
t.emp_id +1 = t2.emp_id GROUP BY t.emp_id HAVING MIN(t2.emp_id) IS NULL; Explanation:
Complex pattern; alternative use GENERATE_SERIES or sequences depending on DB.

Q52: Find cumulative percentage of salary per dept.

Page 5
Answer: SELECT emp_name, salary, ROUND(100 * SUM(salary) OVER (PARTITION BY dept_id
ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) /
SUM(salary) OVER (PARTITION BY dept_id),2) AS cum_pct FROM EMPLOYEES; Explanation:
Calculates running percentage within partition; useful for Pareto analysis.

Q53: Pivoting rows to columns (Oracle PIVOT).

Answer: SELECT * FROM (SELECT dept_id, emp_name, salary FROM EMPLOYEES) PIVOT
(SUM(salary) FOR dept_id IN (10 AS D10, 20 AS D20, 30 AS D30)); Explanation: PIVOT
converts row values into columns; syntax is DB-specific.

Q54: Unpivot example to normalize columns to rows.

Answer: SELECT dept, emp, sal FROM DEPT_EMPS UNPIVOT (sal FOR emp IN (emp1 AS 'E1',
emp2 AS 'E2')); Explanation: UNPIVOT reverses PIVOT; useful when data stored in wide
format.

Q55: Find top N per group using analytic functions.

Answer: SELECT * FROM (SELECT e.*, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER
BY salary DESC) rn FROM EMPLOYEES e) WHERE rn <= 3; Explanation: Standard pattern for
top-N per group problems.

Q56: Use EXISTS to delete orphan rows safely.

Answer: DELETE FROM EMPLOYEES e WHERE NOT EXISTS (SELECT 1 FROM DEPARTMENTS d
WHERE d.dept_id = e.dept_id); Explanation: Deletes employees whose dept_id has no match;
NOT EXISTS safe with NULLs.

Q57: Find median salary (approx) using PERCENTILE_DISC.

Answer: SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary) FROM
EMPLOYEES; Explanation: Oracle analytic aggregate returns discrete percentile; other DBs
have different functions.

Q58: Use GROUPING SETS to compute multiple aggregates.

Answer: SELECT dept_id, job, SUM(salary) FROM EMPLOYEES GROUP BY GROUPING SETS
((dept_id, job),(dept_id),(job),()); Explanation: GROUPING SETS computes aggregates at
multiple granularities in one pass.

Q59: Rollup example for hierarchical totals.

Answer: SELECT dept_id, job, SUM(salary) FROM EMPLOYEES GROUP BY ROLLUP(dept_id,
job); Explanation: ROLLUP produces subtotals and grand total; useful for reports.

Q60: Cube example for cross-tab totals.

Answer: SELECT dept_id, job, SUM(salary) FROM EMPLOYEES GROUP BY CUBE(dept_id, job);
Explanation: CUBE computes all combinations of subtotals; more expensive.

Q61: Use HAVING with aggregate expressions.

Answer: SELECT dept_id FROM EMPLOYEES GROUP BY dept_id HAVING AVG(salary) > 50000;
Explanation: HAVING filters groups using aggregate results.

Q62: Find consecutive hires per manager (windowing).

Page 6
Answer: SELECT manager_id, emp_id, hire_date, LAG(hire_date) OVER (PARTITION BY
manager_id ORDER BY hire_date) prev_hire FROM EMPLOYEES; Explanation: LAG helps
compare adjacent rows within partition.

Q63: Detect duplicate rows across multiple columns.

Answer: SELECT emp_name, dept_id, COUNT(*) cnt FROM EMPLOYEES GROUP BY emp_name,
dept_id HAVING COUNT(*) > 1; Explanation: Identify duplicates by grouping by key columns.

Q64: Merge (upsert) in Oracle using MERGE INTO.

Answer: MERGE INTO EMPLOYEES t USING (SELECT 501 emp_id, 'Zoe' emp_name FROM
DUAL) s ON (t.emp_id = s.emp_id) WHEN MATCHED THEN UPDATE SET t.emp_name =
s.emp_name WHEN NOT MATCHED THEN INSERT(emp_id, emp_name) VALUES(s.emp_id,
s.emp_name); Explanation: MERGE performs conditional insert/update in one statement;
careful with triggers and multi-row sources.

Q65: Explain transaction control—COMMIT and ROLLBACK.

Answer: COMMIT makes changes permanent; ROLLBACK undoes uncommitted changes. Use
SAVEPOINT to rollback partially within a transaction.

Q66: Use SAVEPOINT example.

Answer: SAVEPOINT sp1; UPDATE ...; ROLLBACK TO sp1; Explanation: SAVEPOINT creates a
marker to which you can rollback without undoing entire transaction.

Q67: Locking example SELECT FOR UPDATE.

Answer: SELECT * FROM EMPLOYEES WHERE emp_id = 101 FOR UPDATE; Explanation: Locks
selected rows for update to prevent concurrent modifications; use carefully to avoid
deadlocks.

Q68: Deadlock prevention tips.

Answer: Acquire locks in consistent order, keep transactions short, avoid user interaction
inside transactions, use lower isolation if acceptable. Explanation: Design and monitoring
help reduce deadlocks.

Q69: Explain isolation levels briefly.

Answer: Read Uncommitted, Read Committed, Repeatable Read, Serializable. Higher levels
reduce anomalies but reduce concurrency.

Q70: Find employees whose salary increased compared to previous record

(requires history table).
Answer: Using a history table, join current to previous by emp_id and compare salary, or use
LAG over ordering by change_date. Explanation: Temporal comparisons typically use LAG or
self-join on timestamps.

Q71: Use DECODE (Oracle) vs CASE.

Answer: DECODE(expr, val1, res1, val2, res2, default) is Oracle-specific; CASE is standard
and more flexible (supports ranges, NULL handling).

Q72: Find employees with top 3 salaries overall.

Page 7
Answer: SELECT * FROM (SELECT e.*, ROW_NUMBER() OVER (ORDER BY salary DESC) rn
FROM EMPLOYEES e) WHERE rn <= 3; Explanation: ROW_NUMBER provides deterministic
top-N when ties exist and unique ordering available.

Q73: Use analytical AVG over partition for comparison.

Answer: SELECT emp_name, salary, AVG(salary) OVER (PARTITION BY dept_id) dept_avg
FROM EMPLOYEES WHERE salary > AVG(salary) OVER (PARTITION BY dept_id); Explanation:
Compare row value to partition average using analytic function (can't be used directly in
WHERE in some DBs—wrap in subquery).

Q74: Explain why analytic functions can't always be in WHERE directly.

Answer: WHERE is evaluated before window functions; to filter on analytic results, use a
subquery or HAVING appropriately.

Q75: Use WITH (CTE) for readability.

Answer: WITH dept_salary AS (SELECT dept_id, AVG(salary) avg_sal FROM EMPLOYEES
GROUP BY dept_id) SELECT e.emp_name, e.salary, ds.avg_sal FROM EMPLOYEES e JOIN
dept_salary ds ON e.dept_id = ds.dept_id; Explanation: CTEs break complex queries into
readable named subqueries; can be recursive.

Q76: Recursive CTE example (generate numbers).

Answer: WITH RECURSIVE nums(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM nums WHERE
n<10) SELECT * FROM nums; Explanation: Recursive CTEs generate sequences or traverse
hierarchies; syntax DB-dependent.

Q77: Hierarchical query in Oracle using CONNECT BY.

Answer: SELECT emp_id, emp_name, manager_id FROM EMPLOYEES START WITH manager_id
IS NULL CONNECT BY PRIOR emp_id = manager_id; Explanation: Traverses tree structures;
START WITH defines root; PRIOR indicates parent-child relationship.

Q78: Alternative hierarchical query using recursive CTE.

Answer: WITH emp_tree AS (SELECT emp_id, emp_name, manager_id FROM EMPLOYEES
WHERE manager_id IS NULL UNION ALL SELECT e.emp_id, e.emp_name, e.manager_id FROM
EMPLOYEES e JOIN emp_tree et ON e.manager_id = et.emp_id) SELECT * FROM emp_tree;
Explanation: Portable approach across DBs supporting recursive CTEs.

Q79: Find employees with salaries equal to department average.

Answer: SELECT e.emp_name FROM EMPLOYEES e JOIN (SELECT dept_id, AVG(salary) avg_sal
FROM EMPLOYEES GROUP BY dept_id) d ON e.dept_id = d.dept_id WHERE e.salary =
d.avg_sal; Explanation: Equating floats may be tricky due to precision—use ROUND if needed.

Q80: Use analytic PERCENT_RANK.

Answer: SELECT emp_name, PERCENT_RANK() OVER (ORDER BY salary) pr FROM
EMPLOYEES; Explanation: PERCENT_RANK returns relative standing between 0 and 1; useful
for normalization.

Q81: Use NTILE to bucket rows.

Answer: SELECT emp_name, NTILE(4) OVER (ORDER BY salary DESC) quartile FROM
EMPLOYEES; Explanation: NTILE distributes rows into N buckets as evenly as possible.

Page 8
Q82: Use JSON functions (if DB supports) to extract from JSON column.
Answer: SELECT json_col->>'name' AS name FROM employees_json; Explanation: JSON
handling is DB-specific; use appropriate functions for parsing and indexing.

Q83: Full-text search basics.

Answer: Use full-text indexes and MATCH/CONTAINS functions for efficient text search
instead of LIKE on large text fields. Explanation: Full-text provides language-aware search,
weighting, stemming.

Q84: Explain explain-plan / query optimizer.

Answer: Use EXPLAIN PLAN to see execution strategy (indexes, joins, scans). Understand
cost-based optimizer to tune queries with indexes, hints, or rewriting queries.

Q85: When to use indexes?

Answer: Use indexes on columns used in WHERE, JOIN, ORDER BY, and GROUP BY. Avoid
indexing columns with low cardinality or on heavy-write tables without reason.

Q86: Composite index considerations.

Answer: Order matters in composite index; leftmost prefix can be used for matching queries.
Choose ordering based on query patterns.

Q87: Covering index concept.

Answer: When an index contains all columns needed for a query, the DB can satisfy query
from index alone (index-only scan), improving performance.

Q88: Avoiding SELECT * for performance and stability.

Answer: Select only needed columns to reduce I/O and coupling to schema changes; helps
covering indexes too.

Q89: Use ANALYZE / statistics gathering.

Answer: Keep table/index statistics up-to-date so the optimizer can make good decisions; use
DB-specific tools to gather stats.

Q90: Use bind variables to prevent SQL injection and improve performance.
Answer: Use parameterized queries or bind variables (e.g., :1) so the DB can reuse execution
plans and avoid injection vulnerabilities.

Q91: Simple PL/SQL block example (anonymous block).

Answer: DECLARE v_count NUMBER; BEGIN SELECT COUNT(*) INTO v_count FROM
EMPLOYEES; DBMS_OUTPUT.PUT_LINE('Count='||v_count); END; Explanation: PL/SQL blocks
allow procedural logic; use exception handling for robust code.

Q92: Stored procedure example.

Answer: CREATE OR REPLACE PROCEDURE give_bonus(p_dept IN NUMBER, p_pct IN NUMBER)
AS BEGIN UPDATE EMPLOYEES SET salary = salary * (1+p_pct/100) WHERE dept_id = p_dept;
COMMIT; END; Explanation: Procedures encapsulate business logic; avoid COMMIT inside
called procedures in multi-operation transactions unless appropriate.

Page 9
Q93: Function example returning value.
Answer: CREATE OR REPLACE FUNCTION get_emp_count(p_dept NUMBER) RETURN NUMBER
AS v_cnt NUMBER; BEGIN SELECT COUNT(*) INTO v_cnt FROM EMPLOYEES WHERE dept_id =
p_dept; RETURN v_cnt; END; Explanation: Functions return values and can be used in SQL if
deterministic and side-effect free.

Q94: Cursor FOR LOOP example in PL/SQL.

Answer: BEGIN FOR rec IN (SELECT emp_id, emp_name FROM EMPLOYEES) LOOP
DBMS_OUTPUT.PUT_LINE(rec.emp_name); END LOOP; END; Explanation: Cursor FOR loops
simplify row-by-row processing without explicit open/fetch/close.

Q95: Bulk operations example FORALL in PL/SQL.

Answer: DECLARE TYPE t_ids IS TABLE OF EMPLOYEES.emp_id%TYPE; ids t_ids :=
t_ids(101,102,103); BEGIN FORALL i IN ids.FIRST..ids.LAST UPDATE EMPLOYEES SET salary =
salary*1.05 WHERE emp_id = ids(i); END; Explanation: FORALL and BULK COLLECT reduce
context switches and improve performance for bulk DML.

Q96: Exception handling sample in PL/SQL.

Answer: BEGIN ... EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No
rows'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); END; Explanation: Handle
known exceptions explicitly and log or re-raise others; avoid swallowing errors silently.

Q97: Explain optimizer hints briefly.

Answer: Hints (e.g., /*+ INDEX(e emp_idx) */) can direct optimizer to use certain plans; use
sparingly when optimizer misbehaves.

Q98: Using temporary tables.

Answer: CREATE GLOBAL TEMPORARY TABLE temp_emp ON COMMIT PRESERVE ROWS AS
SELECT ...; Explanation: Temp tables store session-specific data; lifetime and commit
behavior DB-specific.

Q99: Secure sensitive data (masking/encryption).

Answer: Use column-level encryption, data masking, or views to protect PII; follow
compliance requirements.

Q100: How to approach writing efficient queries—checklist.

Answer: 1) Review schema and indexes. 2) Avoid SELECT *. 3) Use proper joins and
predicates. 4) Use analytic functions instead of complex self-joins where applicable. 5) Test
with EXPLAIN PLAN and optimize. Explanation: Combine good schema design, indexing, and
query patterns; profile and iterate.

Page 10