SQL AND PLSQL

Questions49

1. Performance Tuning — the troubleshooting narrative

-- Find the SQL_ID for a slow statement
SELECT sql_id, elapsed_time/1e6 secs, executions, sql_text
FROM v$sql WHERE sql_text LIKE '%ORDER_PROCESSING%';

-- Actual execution plan (not estimated)
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('7gk3xyz1abcd', NULL, 'ALLSTATS LAST'));

-- Check if stats are stale
SELECT table_name, last_analyzed, stale_stats
FROM dba_tab_statistics WHERE table_name = 'ORDERS';
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA','ORDERS', cascade=>TRUE);

Say it out loud as: SQL_ID → real plan → stats check → indexing/cardinality → rewrite → re-measure..

2. Bulk Processing — FORALL with SAVE EXCEPTIONS

DECLARE
  TYPE t_ids IS TABLE OF orders.order_id%TYPE;
  l_ids t_ids;
  bulk_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(bulk_errors, -24381);
  CURSOR c IS SELECT order_id FROM orders WHERE status = 'PENDING';
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO l_ids LIMIT 1000;
    EXIT WHEN l_ids.COUNT = 0;
    FORALL i IN 1..l_ids.COUNT SAVE EXCEPTIONS
      UPDATE orders SET status = 'PROCESSED' WHERE order_id = l_ids(i);
  END LOOP;
  CLOSE c;
EXCEPTION
  WHEN bulk_errors THEN
    FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('Row '||SQL%BULK_EXCEPTIONS(i).ERROR_INDEX||
        ' failed: '||SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
    END LOOP;
END;

Key talking point: LIMIT caps PGA memory, SAVE EXCEPTIONS lets bad rows fail without killing the whole batch.

3. Cursors / REF CURSOR — decision-driven, not definitional

-- Weak REF CURSOR: max flexibility, used when SQL is built dynamically
FUNCTION get_report(p_table VARCHAR2, p_filter VARCHAR2) RETURN SYS_REFCURSOR IS
  v_cursor SYS_REFCURSOR;
BEGIN
  DBMS_ASSERT.SIMPLE_SQL_NAME(p_table);  -- prevent SQL injection on identifier
  OPEN v_cursor FOR 'SELECT * FROM ' || p_table || ' WHERE ' || p_filter;
  RETURN v_cursor;
END;


-- Strong REF CURSOR: return type fixed at compile time, used when structure is known
TYPE emp_cur IS REF CURSOR RETURN employees%ROWTYPE;

Answer to “why not a normal cursor?”: REF CURSORs travel — you can pass them out of a procedure to Java/.NET or between PL/SQL blocks; a normal cursor can’t leave its declaring scope.

4. Recursive CTE vs CONNECT BY

-- Recursive CTE
WITH cargo_tree (item_id, parent_id, lvl) AS (
  SELECT item_id, parent_id, 1 FROM cargo WHERE parent_id IS NULL
  UNION ALL
  SELECT c.item_id, c.parent_id, ct.lvl + 1
  FROM cargo c JOIN cargo_tree ct ON c.parent_id = ct.item_id
)
SELECT * FROM cargo_tree;


-- CONNECT BY equivalent
SELECT item_id, parent_id, LEVEL
FROM cargo
START WITH parent_id IS NULL
CONNECT BY PRIOR item_id = parent_id;

Interview line: CONNECT BY is Oracle-proprietary and gives LEVEL/SYS_CONNECT_BY_PATH for free; recursive CTE is ANSI-standard, portable, and lets you filter/aggregate before recursing.

5. Materialized Views — fast refresh mechanics

CREATE MATERIALIZED VIEW LOG ON orders WITH ROWID, SEQUENCE
  (order_id, customer_id, amount) INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW mv_order_summary
  BUILD IMMEDIATE
  REFRESH FAST ON COMMIT
AS
  SELECT customer_id, SUM(amount) total FROM orders GROUP BY customer_id;

Fast refresh only works if the MV log captures the right columns/rowids and the query is a supported pattern (simple joins/aggregates). Otherwise Oracle silently falls back to complete refresh.

6. MERGE / ETL upsert

MERGE INTO target_customers t
USING (SELECT customer_id, name, email FROM stg_customers) s
ON (t.customer_id = s.customer_id)
WHEN MATCHED THEN
  UPDATE SET t.name = s.name, t.email = s.email
  WHERE t.name != s.name OR t.email != s.email
WHEN NOT MATCHED THEN
  INSERT (customer_id, name, email) VALUES (s.customer_id, s.name, s.email);

For a “10M row ETL” question, layer this inside the FORALL/BULK COLLECT loop from #2, with exception logging to an audit table.

7. DBMS_SCHEDULER — production batch pattern

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'DAILY_ORDER_RECONCILE',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'PKG_ORDERS.RECONCILE_DAILY',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2',
    enabled         => TRUE);
END;

Say why over cron: DBMS_SCHEDULER runs inside the DB, has built-in logging (DBA_SCHEDULER_JOB_RUN_DETAILS), dependency chains, and doesn't need OS-level access.

8. Autonomous Transactions for logging

PROCEDURE log_error(p_msg VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO error_log VALUES (SYSDATE, p_msg);
  COMMIT;
END;

Point to make: the log survives even if the calling transaction rolls back — it’s committed independently.

9. DBMS_ASSERT / RLS for security-aware dynamic SQL

-- Row Level Security
BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema   => 'HR',
    object_name     => 'EMPLOYEES',
    policy_name     => 'DEPT_ISOLATION',
    function_schema => 'HR',
    policy_function => 'DEPT_PREDICATE');
END;

-- DBMS_ASSERT guarding dynamic identifiers (same pattern as #3)
DBMS_ASSERT.SIMPLE_SQL_NAME(p_column_name);

The document’s core advice still applies: don’t drill these in isolation — pick a scenario (e.g. “slow nightly batch touching 10M rows”) and force yourself to combine 3–4 of these pillars in one spoken answer, since that’s the format the interviewer actually uses.

10. Instead of triggers — making a view updatable

CREATE OR REPLACE TRIGGER trg_emp_view_insert
INSTEAD OF INSERT ON emp_dept_view
FOR EACH ROW
BEGIN
  INSERT INTO employees (employee_id, name) VALUES (:NEW.employee_id, :NEW.name);
  UPDATE departments SET headcount = headcount + 1 WHERE department_id = :NEW.department_id;
END;

Needed when a view joins multiple tables — Oracle can’t natively resolve which base table an INSERT/UPDATE/DELETE should target, so you intercept and route it manually.

11. Full exception diagnostics — FORMAT_ERROR_STACK / BACKTRACE

BEGIN
  process_order(p_order_id => 500);
EXCEPTION
  WHEN OTHERS THEN
    INSERT INTO error_log (err_msg, err_backtrace)
    VALUES (DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    RAISE;
END;

FORMAT_ERROR_STACK gives the error message; FORMAT_ERROR_BACKTRACE gives the exact line number where it was raised — critical for debugging nested procedure calls where SQLERRM alone doesn't tell you which inner call actually failed.

12. Application Context + VPD (SYS_CONTEXT-driven security)

CREATE OR REPLACE CONTEXT app_ctx USING pkg_security;

CREATE OR REPLACE PACKAGE pkg_security IS
  PROCEDURE set_dept(p_dept_id NUMBER);
END;
CREATE OR REPLACE PACKAGE BODY pkg_security IS
  PROCEDURE set_dept(p_dept_id NUMBER) IS
  BEGIN
    DBMS_SESSION.SET_CONTEXT('app_ctx', 'dept_id', p_dept_id);
  END;
END;
-- Later, in a VPD policy function or view:
SELECT * FROM employees WHERE department_id = SYS_CONTEXT('app_ctx', 'dept_id');

This is how row-level security gets driven dynamically per logged-in user instead of hardcoding filters — ties back to DBMS_RLS from the earlier set, but this is the piece that actually populates the session’s security attribute.

13. PRAGMA DETERMINISTIC — function result caching inside SQL

FUNCTION get_state_abbr(p_state_name VARCHAR2) RETURN VARCHAR2
DETERMINISTIC IS
BEGIN
  RETURN CASE p_state_name WHEN 'Maharashtra' THEN 'MH' WHEN 'Karnataka' THEN 'KA' END;
END;

Tells Oracle the function always returns the same output for the same input, so it can skip re-execution for repeated values in the same query (and allows use in function-based indexes). Trap: marking a function DETERMINISTIC that secretly depends on SYSDATE or session state gives wrong cached results.

14. External tables — querying flat files as if they were DB tables

CREATE TABLE ext_daily_txns (
  txn_id NUMBER, amount NUMBER, txn_date DATE
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_dir
  ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ',')
  LOCATION ('daily_txns.csv')
)
REJECT LIMIT UNLIMITED;

SELECT * FROM ext_daily_txns WHERE amount > 1000;

Common in ETL: load a CSV drop into a queryable table without a separate load step, then MERGE/INSERT SELECT from it into real tables.

15. Partition exchange load — fast bulk load pattern

-- Load into a plain staging table matching the partition structure, then:
ALTER TABLE sales_history
EXCHANGE PARTITION sales_2024_q1
WITH TABLE staging_sales_q1
INCLUDING INDEXES WITHOUT VALIDATION;

Swaps a staging table in as a partition instantly (metadata-only operation, no data movement) — the go-to answer for “how would you load 50M rows into a partitioned table with near-zero downtime.”

16. Table function accepting a REF CURSOR as input

CREATE OR REPLACE FUNCTION transform_data(p_cur SYS_REFCURSOR)
RETURN t_emp_tab PIPELINED IS
  v_id NUMBER; v_name VARCHAR2(100);
BEGIN
  LOOP
    FETCH p_cur INTO v_id, v_name;
    EXIT WHEN p_cur%NOTFOUND;
    PIPE ROW (t_emp_row(v_id, UPPER(v_name)));
  END LOOP;
  CLOSE p_cur;
  RETURN;
END;

SELECT * FROM TABLE(transform_data(CURSOR(SELECT emp_id, name FROM employees)));

Combines the pipelined function and REF CURSOR pillars into one — lets you build reusable “transform any query’s output” utilities.

17. MERGE inside a BULK COLLECT/FORALL loop with error logging (full ETL combo)

DECLARE
  TYPE t_stg IS TABLE OF stg_customers%ROWTYPE;
  l_batch t_stg;
  CURSOR c IS SELECT * FROM stg_customers;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO l_batch LIMIT 500;
    EXIT WHEN l_batch.COUNT = 0;

    FORALL i IN 1..l_batch.COUNT SAVE EXCEPTIONS
          MERGE INTO target_customers t
          USING (SELECT l_batch(i).customer_id cid, l_batch(i).name nm FROM dual) s
          ON (t.customer_id = s.cid)
          WHEN MATCHED THEN UPDATE SET t.name = s.nm
          WHEN NOT MATCHED THEN INSERT (customer_id, name) VALUES (s.cid, s.nm);
  END LOOP;
  CLOSE c;
EXCEPTION
  WHEN OTHERS THEN
    FOR i IN 1..NVL(SQL%BULK_EXCEPTIONS.COUNT,0) LOOP
      INSERT INTO error_log VALUES (SYSDATE, SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
    END LOOP;
END;

This is the single most likely “design the whole pipeline” answer for a 10M-row ETL question — combines bulk fetch, FORALL, MERGE, and exception logging in one block.

18. Recursive CTE feeding a table function for hierarchical bonus calculation

WITH mgmt_chain (emp_id, mgr_id, lvl) AS (
  SELECT employee_id, manager_id, 1 FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, m.lvl + 1
  FROM employees e JOIN mgmt_chain m ON e.manager_id = m.emp_id
)
SELECT emp_id, lvl, pkg_bonus.calc_bonus(emp_id, lvl) bonus
FROM mgmt_chain;

Combines recursive SQL with a PL/SQL function call per row — common pattern when business logic (bonus tiers) is too complex to express in pure SQL but the hierarchy traversal is cleanest in a CTE.

20. Compound trigger calling a package procedure with autonomous logging

CREATE OR REPLACE TRIGGER trg_order_audit
FOR UPDATE OF status ON orders
COMPOUND TRIGGER
  TYPE t_ids IS TABLE OF orders.order_id%TYPE;
  g_ids t_ids := t_ids();

AFTER EACH ROW IS
  BEGIN
    g_ids.EXTEND;
    g_ids(g_ids.COUNT) := :NEW.order_id;
  END AFTER EACH ROW;
  AFTER STATEMENT IS
  BEGIN
    FORALL i IN 1..g_ids.COUNT
      pkg_audit.log_status_change(g_ids(i));  -- internally uses AUTONOMOUS_TRANSACTION
  END AFTER STATEMENT;
END;

Ties compound triggers + FORALL + autonomous transaction logging together — the log survives even if the outer UPDATE later rolls back.

21. Dynamic SQL with bind variables (EXECUTE IMMEDIATE)

DECLARE
  v_sql VARCHAR2(200);
  v_dept_id NUMBER := 10;
  v_count NUMBER;
BEGIN
  v_sql := 'SELECT COUNT(*) FROM employees WHERE department_id = :1';
  EXECUTE IMMEDIATE v_sql INTO v_count USING v_dept_id;
  DBMS_OUTPUT.PUT_LINE(v_count);
END;

Know why binds matter: without :1 and USING, you're vulnerable to SQL injection and you blow the shared pool with hard-parsed literal SQL every time.

22. Collections — associative array vs nested table vs varray

-- Associative array: sparse, indexed by number or string, no dense storage needed
TYPE t_assoc IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
l_map t_assoc;
l_map(100) := 'Engineering';
l_map(500) := 'Sales';

-- Nested table: dense or sparse, can be stored in DB column, supports BULK COLLECT
TYPE t_nested IS TABLE OF employees%ROWTYPE;
l_emps t_nested;
-- Varray: fixed max size, order preserved, stored inline in a column
TYPE t_varray IS VARRAY(5) OF VARCHAR2(20);
l_top5 t_varray := t_varray('A','B','C');

The interview question is always “when would you pick which?” — associative arrays for PL/SQL-only lookup tables, nested tables when you need BULK COLLECT or DB persistence, varrays when order and a hard size cap matter.

23. Packages — state persistence and overloading

CREATE OR REPLACE PACKAGE pkg_counter IS
  FUNCTION get_next RETURN NUMBER;
  PROCEDURE reset;
  FUNCTION calc_total(p_qty NUMBER, p_price NUMBER) RETURN NUMBER;
  FUNCTION calc_total(p_amounts SYS.ODCINUMBERLIST) RETURN NUMBER; -- overload
END pkg_counter;
/
CREATE OR REPLACE PACKAGE BODY pkg_counter IS
  g_counter NUMBER := 0;  -- persists for the session
  FUNCTION get_next RETURN NUMBER IS
  BEGIN
    g_counter := g_counter + 1;
    RETURN g_counter;
  END;
  PROCEDURE reset IS BEGIN g_counter := 0; END;
  FUNCTION calc_total(p_qty NUMBER, p_price NUMBER) RETURN NUMBER IS
  BEGIN RETURN p_qty * p_price; END;
  FUNCTION calc_total(p_amounts SYS.ODCINUMBERLIST) RETURN NUMBER IS
    l_total NUMBER := 0;
  BEGIN
    FOR i IN 1..p_amounts.COUNT LOOP l_total := l_total + p_amounts(i); END LOOP;
    RETURN l_total;
  END;
END pkg_counter;

Key point interviewers probe: package-level variables persist for the life of the session, not the call — that’s how you build in-memory caches or counters across calls.

24. Row-level vs statement-level trigger with :NEW/:OLD

-- Row-level: fires once per affected row, :NEW/:OLD available
CREATE OR REPLACE TRIGGER trg_salary_check
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
  IF :NEW.salary < :OLD.salary THEN
    RAISE_APPLICATION_ERROR(-20001, 'Salary cannot decrease');
  END IF;
END;

-- Statement-level: fires once total, no :NEW/:OLD (no single row context)
CREATE OR REPLACE TRIGGER trg_audit_bulk_update
AFTER UPDATE ON employees
BEGIN
  INSERT INTO audit_log VALUES (USER, SYSDATE, 'Bulk update on EMPLOYEES');
END;

:NEW/:OLD only exist in row-level triggers because they reference a specific row being changed — statement-level has no "current row" concept.

25. Custom exceptions with centralized error handling

CREATE OR REPLACE PACKAGE pkg_errors IS
  e_invalid_salary EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_invalid_salary, -20001);
  PROCEDURE log_and_raise(p_code NUMBER, p_msg VARCHAR2);
END pkg_errors;
/
CREATE OR REPLACE PACKAGE BODY pkg_errors IS
  PROCEDURE log_and_raise(p_code NUMBER, p_msg VARCHAR2) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO error_log (error_code, error_msg, created_on)
    VALUES (p_code, p_msg, SYSDATE);
    COMMIT;
    RAISE_APPLICATION_ERROR(p_code, p_msg);
  END;
END pkg_errors;

log survives even though RAISE_APPLICATION_ERROR immediately rolls back the caller's transaction.

Tier 1 — Non-Negotiable (1–10)

1. BULK COLLECT + FORALL with SAVE EXCEPTIONS
LIMIT caps PGA memory, SAVE EXCEPTIONS keeps one bad row from killing the whole batch. The backbone of every batch-processing answer.

Get Pranav Bakare’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

2. Cursors — attributes, REF CURSOR, FOR UPDATE
%FOUND/%NOTFOUND/%ROWCOUNT for loop control, WHERE CURRENT OF for row locking, weak vs strong REF CURSOR for when the shape is known vs dynamic. Know it as one continuous story.

3. Compound Triggers
Fixes ORA-04091 mutating table error by deferring row-level collection to AFTER STATEMENT.

4. Row-level vs statement-level triggers, :NEW/:OLD
:NEW/:OLD only exist where there's a single row in context.

5. Dynamic SQL — EXECUTE IMMEDIATE with binds
Binds prevent injection and avoid hard-parsing the shared pool to death.

6. Packages — state persistence, overloading
Package-level variables persist for the session, not the call.

7. Custom exceptions + centralized handling
PRAGMA EXCEPTION_INIT maps an Oracle error number to a named, catchable exception.

8. Autonomous Transactions
Independent commit for logging — survives even if the caller rolls back.

9. MERGE / ETL upsert pattern
Especially layered inside a BULK COLLECT/FORALL loop for the “10M row load” scenario.

10. DBMS_SCHEDULER over cron
In-DB logging (DBA_SCHEDULER_JOB_RUN_DETAILS), dependency chains, no OS access required.

Tier 2 — Real Production Signal (11–20)

11. Pipelined table functions
Streams rows as produced; queryable via SELECT * FROM TABLE(...).

12. %ROWTYPE vs %TYPE vs custom records
%ROWTYPE breaks on a partial-column SELECT — that's when you need a custom record type.

13. Collections — associative array vs nested table vs varray
Plus sparse-collection handling with FORALL i IN INDICES OF.

14. Global Temporary Tables
ON COMMIT DELETE ROWS vs PRESERVE ROWS — session-private staging, no permanent-table overhead.

15. NOCOPY
Pass-by-reference for large IN OUT collection parameters; caveat around partial writes if an exception hits mid-loop.

16. RESULT_CACHE
Caches function results per distinct input — good for lookup-table-style functions called repeatedly.

17. Invoker Rights vs Definer Rights
AUTHID CURRENT_USER for shared utility packages that must resolve to the caller's own schema.

18. DBMS_LOB
Chunked CLOB/BLOB reads/writes — direct VARCHAR2-style handling silently truncates.

19. EXECUTE IMMEDIATE … RETURNING INTO
Dynamic insert that returns a generated key in the same round-trip.

20. DBMS_ASSERT
SIMPLE_SQL_NAME guards any identifier concatenated into dynamic SQL — pairs with #5, #11, #17.

Tier 3 — Senior-Level Differentiators (21–30)

21. Application Context + VPD (DBMS_RLS + SYS_CONTEXT)
Drives row-level security dynamically per logged-in user rather than hardcoded filters.

22. PRAGMA DETERMINISTIC
Enables SQL-level result caching and function-based index eligibility — but breaks silently if the function secretly depends on SYSDATE or session state.

23. Trigger FOLLOWS/PRECEDES
Makes multi-trigger execution order deterministic (undefined before 11g otherwise).

24. FORMAT_ERROR_STACK / FORMAT_ERROR_BACKTRACE
Gets you the exact failing line number in nested procedure calls — SQLERRM alone doesn't.

25. Virtual columns
Computed on read, always in sync, indexable — no trigger needed to maintain a derived value.

26. External tables
Query a CSV/flat file as a table without a separate load step.

27. Partition exchange load
Metadata-only swap of a staging table into a partition — the answer for near-zero-downtime bulk loads.

28. INSTEAD OF triggers
Makes a multi-table join view updatable by manually routing the DML.

29. Sequences — CACHE/NOCACHE and gap behavior
Guaranteed unique, never guaranteed gap-free; cached values are lost on instance crash.

30. JSON generation/parsing — JSON_OBJECT, JSON_ARRAYAGG, JSON_TABLE
Increasingly common wherever PL/SQL sits behind a REST/microservice layer.

Honorable mentions (just outside the 30, worth a skim): UTL_FILE for OS-level file writes, conditional compilation ($IF/$$DEBUG_MODE), object types with member methods, the WITH FUNCTION inline clause, DBMS_PROFILER for code-level profiling, and user-defined aggregate functions via the ODCIAggregate interface.

Tier 1 — Non-Negotiable (1–10)

1. Window functions — RANK, DENSE_RANK, ROW_NUMBER

SELECT * FROM (
  SELECT emp_id, salary, DENSE_RANK() OVER (ORDER BY salary DESC) rnk
  FROM employees
) WHERE rnk = 3;

The Nth-highest-salary question and its variants. Know the difference between all three cold.

2. PARTITION BY — top-N per group

SELECT * FROM (
  SELECT customer_id, txn_date, amount,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY txn_date DESC) rn
  FROM transactions
) WHERE rn = 1;

The single most reused pattern across interviews — expect 2–3 variations in one sitting.

3. Running totals / cumulative aggregates

SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) running_total
FROM orders;

4. LAG / LEAD — period-over-period comparison

SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) mom_change
FROM monthly_revenue;

5. Recursive CTE vs CONNECT BY

WITH cargo_tree (item_id, parent_id, lvl) AS (
  SELECT item_id, parent_id, 1 FROM cargo WHERE parent_id IS NULL
  UNION ALL
  SELECT c.item_id, c.parent_id, ct.lvl + 1
  FROM cargo c JOIN cargo_tree ct ON c.parent_id = ct.item_id
)
SELECT * FROM cargo_tree;

Recursive CTE is ANSI-standard and portable; CONNECT BY is Oracle-proprietary but gives LEVEL/SYS_CONNECT_BY_PATH for free.

6. Duplicate detection and removal

DELETE FROM employees e
WHERE ROWID > (SELECT MIN(ROWID) FROM employees e2 WHERE e2.emp_id = e.emp_id);

Know the ROW_NUMBER() alternative too, for tables without a reliable ROWID story.

7. Self-joins

SELECT e.emp_name, e.salary, m.salary mgr_salary
FROM employees e JOIN employees m ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;

8. Correlated subquery vs JOIN (performance angle)

SELECT DISTINCT c.customer_id
FROM customers c JOIN orders o ON o.customer_id = c.customer_id AND o.amount > 1000;

Tests whether you can discuss when EXISTS beats IN, and when a join beats both — ties straight into performance tuning.

9. Gaps and islands

SELECT MIN(login_date) start_date, MAX(login_date) end_date
FROM (SELECT login_date, login_date - ROW_NUMBER() OVER (ORDER BY login_date) grp
      FROM user_logins)
GROUP BY grp;

The date-minus-row_number trick is the thing to memorize — “consecutive active days” is the usual framing.

10. LISTAGG — row-to-string aggregation

SELECT department_id,
       LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_name) emp_list
FROM employees GROUP BY department_id;

Know the 4000-byte overflow trap (ORA-01489) and the ON OVERFLOW TRUNCATE fix.

11. Hierarchical path building

SELECT emp_id, emp_name, LEVEL,
       SYS_CONNECT_BY_PATH(emp_name, ' > ') hierarchy_path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

12. Function-based indexes validated via execution plan

CREATE INDEX idx_state_abbr ON employees (get_state_abbr(state_name));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

Only works if the underlying function is DETERMINISTIC — confirm actual usage via the plan, don’t assume.

13. Partition-aware querying and pruning

SELECT * FROM sales_history PARTITION (sales_2024_q1) WHERE amount > 1000;