Interview Questions on Oracle Sql for Experienced | 4+ Years of Experience

Below is the set of 100 interview questions and answers for the Expert level (4+ years experience), focusing on advanced SQL techniques, Oracle analytics, performance optimization, and advanced database features.

---

Chapter 1 — Advanced SQL Techniques

Question 1:
What is a Common Table Expression (CTE)?
Answer: A CTE is a temporary result set defined within a WITH clause, which can be referenced in the main query. It simplifies complex joins and subqueries, improving readability.

WITH cte AS (SELECT * FROM employees WHERE salary > 50000) 
SELECT * FROM cte;

Question 2:
How does a recursive query work in SQL?
Answer: Recursive queries use a CTE that references itself to process hierarchical data, such as organizational structures or bill-of-materials.

WITH RECURSIVE emp_hierarchy AS (
    SELECT employee_id, manager_id, name FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.name
    FROM employees e
    JOIN emp_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM emp_hierarchy;

Question 3:
What is a pivot query in SQL?
Answer: A pivot query transforms rows into columns, often used to summarize data in reports. The PIVOT operator helps achieve this in Oracle SQL.

SELECT * FROM (
    SELECT department_id, job_id, salary FROM employees
)
PIVOT (
    SUM(salary) FOR job_id IN ('SA_REP', 'IT_PROG', 'AD_ASST')
);

Question 4:
What is an unpivot operation in SQL?
Answer: The UNPIVOT operator converts columns back into rows, reversing a pivot operation. This is helpful when you need to normalize data or perform analysis across multiple columns.

SELECT * FROM (
    SELECT department_id, 'SA_REP' AS job_type, sum(salary) AS total_salary FROM employees
)
UNPIVOT (
    total_salary FOR job_type IN ('SA_REP')
);

---

Chapter 2 — Oracle Analytics and Window Functions

Question 5:
What does the PARTITION BY clause do in Oracle analytics?
Answer: The PARTITION BY clause divides the result set into partitions to perform calculations on each partition independently, while the ORDER BY clause sorts rows within each partition.

SELECT employee_id, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank FROM employees;

Question 6:
What is the purpose of RANK() function in windowing?
Answer: RANK() assigns a unique rank number to each row within a partition, with gaps in ranking for ties.

SELECT employee_id, salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM employees;

Question 7:
How is DENSE_RANK() different from RANK()?
Answer: DENSE_RANK() assigns rank numbers without gaps for ties, whereas RANK() leaves gaps.

SELECT employee_id, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank FROM employees;

Question 8:
What is the ROW_NUMBER() function used for in Oracle?
Answer: The ROW_NUMBER() function assigns a unique, sequential integer to rows, which is useful for pagination or selecting distinct rows in a query.

SELECT employee_id, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num FROM employees;

Question 9:
What is the LAG() function used for in windowing?
Answer: LAG() provides access to a previous row’s value in the result set, allowing you to perform calculations that depend on previous rows.

SELECT employee_id, salary, LAG(salary, 1) OVER (ORDER BY hire_date) AS previous_salary FROM employees;

Question 10:
What does the LEAD() function do?
Answer: LEAD() gives access to the next row’s value in the result set, useful for comparing current and future data.

SELECT employee_id, salary, LEAD(salary, 1) OVER (ORDER BY hire_date) AS next_salary FROM employees;

Question 11:
What is the difference between FIRST_VALUE() and LAST_VALUE()?
Answer: FIRST_VALUE() returns the first value in the partition, while LAST_VALUE() returns the last value. Both functions are used to access values at the start or end of a window.

SELECT employee_id, salary, FIRST_VALUE(salary) OVER (ORDER BY hire_date) AS first_salary FROM employees;

---

Chapter 3 — Working with Large Data Sets

Question 12:
What is bulk insert in Oracle SQL?
Answer: Bulk insert allows you to insert large amounts of data into a table efficiently using the FORALL and BULK COLLECT commands. This method improves performance compared to row-by-row inserts.

FORALL i IN 1..1000
    INSERT INTO employees VALUES (emp_array(i).emp_id, emp_array(i).emp_name);

Question 13:
What are materialized views in Oracle?
Answer: Materialized views store the result of a query as a snapshot, allowing for faster access and better performance for frequently executed complex queries.

CREATE MATERIALIZED VIEW emp_salary_mv AS
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;

Question 14:
How do you manage materialized views in Oracle?
Answer: You can manage materialized views by refreshing them periodically using the REFRESH command. This ensures the data stays current.

BEGIN
    DBMS_MVIEW.REFRESH('emp_salary_mv');
END;

Question 15:
What is partitioning in Oracle?
Answer: Partitioning splits a large table into smaller, more manageable pieces, improving query performance and data management.

CREATE TABLE employees (
    employee_id NUMBER,
    name VARCHAR2(100),
    hire_date DATE
)
PARTITION BY RANGE (hire_date) (
    PARTITION p1 VALUES LESS THAN (TO_DATE('01-JAN-2020', 'DD-MON-YYYY')),
    PARTITION p2 VALUES LESS THAN (TO_DATE('01-JAN-2021', 'DD-MON-YYYY'))
);

Question 16:
What are the benefits of partitioning a table?
Answer: Partitioning enhances query performance by allowing Oracle to scan only the relevant partitions, improves data management, and can speed up data loads and backups.

---

Chapter 4 — Oracle SQL Tuning and Optimization

Question 17:
What is the role of TKPROF in SQL performance tuning?
Answer: TKPROF is a tool used to analyze and format Oracle SQL trace files, providing insight into query performance, such as execution times and resource usage.

tkprof tracefile.prf outputfile.prf

Question 18:
What is an AWR report in Oracle?
Answer: The Automatic Workload Repository (AWR) report provides performance statistics, including SQL execution details, system metrics, and resource consumption, helping to identify bottlenecks.

SELECT * FROM dba_hist_sqlstat WHERE sql_id = 'your_sql_id';

Question 19:
What is SQL Profile in Oracle?
Answer: A SQL Profile helps the Oracle optimizer improve query performance by suggesting alternative execution plans based on historical execution data.

EXEC DBMS_SQLTUNE.CREATE_SQL_PROFILE('profile_name', 'sql_id');

Question 20:
What is SQL Plan Management (SPM)?
Answer: SQL Plan Management helps control and stabilize SQL execution plans by capturing and validating execution plans and ensuring that the same plan is used across executions.

EXEC DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'your_sql_id');

Question 21:
What is adaptive query optimization in Oracle?
Answer: Adaptive query optimization dynamically adjusts execution plans during runtime based on actual execution conditions, improving performance for complex queries.

---

Chapter 5 — Advanced Database Features

Question 22:
How do you manipulate JSON data in Oracle SQL?
Answer: Oracle provides native functions such as JSON_VALUE, JSON_TABLE, and JSON_OBJECT to query and manipulate JSON data stored in the database.

SELECT JSON_VALUE(data, '$.employee_id') FROM employees WHERE department_id = 10;

Question 23:
What is the purpose of XMLTYPE in Oracle?
Answer: XMLTYPE is a data type used to store and manipulate XML data in Oracle, providing methods to query and update XML content.

SELECT XMLTYPE('<employee><id>101</id><name>John</name></employee>') FROM dual;

Question 24:
How do you query XML data in Oracle?
Answer: You can use the EXTRACTVALUE function or `XMLTABLE

` to extract values from XML data.

SELECT EXTRACTVALUE(xml_column, '/employee/name') FROM employees;

Question 25:
What are Flashback queries in Oracle?
Answer: Flashback queries allow you to query data as it was at a specific point in the past, which is useful for recovery and auditing.

SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR) WHERE employee_id = 101;

Question 26:
How does Oracle’s Multitenant Architecture work?
Answer: Multitenant architecture allows a single container database (CDB) to manage multiple pluggable databases (PDBs), simplifying database consolidation and management.

SHOW CON_NAME; -- Displays the current container database or pluggable database.

---

Chapter 1 — Advanced SQL Techniques (Continued)

Question 27:
What is the difference between a recursive CTE and a regular CTE?
Answer: A recursive CTE references itself within its query to perform iterative processing, such as traversing hierarchical data. A regular CTE doesn’t reference itself and is used for simplifying complex queries.

WITH RECURSIVE emp_hierarchy AS (
    SELECT employee_id, manager_id, name FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.name
    FROM employees e
    JOIN emp_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM emp_hierarchy;

Question 28:
How do you handle pivoting with dynamic columns in Oracle?
Answer: Dynamic pivoting involves constructing the PIVOT query dynamically using PL/SQL or by executing the query with the EXECUTE IMMEDIATE command to handle unknown column values at runtime.

DECLARE
   l_sql VARCHAR2(4000);
BEGIN
   SELECT 'SELECT * FROM (SELECT department_id, job_id, salary FROM employees) PIVOT (SUM(salary) FOR job_id IN (' || LISTAGG(DISTINCT job_id, ', ') WITHIN GROUP (ORDER BY job_id) || '))'
   INTO l_sql
   FROM employees;
   EXECUTE IMMEDIATE l_sql;
END;

Question 29:
Explain the purpose of the CONNECT BY clause in Oracle SQL.
Answer: The CONNECT BY clause is used for hierarchical queries to navigate tree-like data, such as employee-manager relationships. It creates parent-child relationships in a query.

SELECT employee_id, manager_id, LEVEL FROM employees CONNECT BY PRIOR employee_id = manager_id;

Question 30:
What is a Self-Join?
Answer: A self-join is a join where a table is joined with itself, often used to query hierarchical data or compare rows within the same table.

SELECT e1.employee_id, e1.name, e2.name AS manager_name
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id;

---

Chapter 2 — Oracle Analytics and Window Functions (Continued)

Question 31:
What is the purpose of NTILE() function in windowing?
Answer: NTILE() divides a result set into a specified number of ranked buckets, which is helpful for segmenting data into equal-sized partitions.

SELECT employee_id, salary, NTILE(4) OVER (ORDER BY salary) AS quartile FROM employees;

Question 32:
What is the significance of ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW in window functions?
Answer: This clause defines the window for aggregation, including all rows from the start of the partition to the current row, useful for cumulative sums or running totals.

SELECT employee_id, salary, SUM(salary) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM employees;

Question 33:
How does COUNT() differ when used as a window function vs an aggregate function?
Answer: As an aggregate function, COUNT() returns the number of rows in the entire result set or group, while as a window function, it provides a count over a specified window of rows.

SELECT department_id, COUNT(*) OVER (PARTITION BY department_id) FROM employees;

Question 34:
What is a “window” in Oracle window functions?
Answer: A window in Oracle SQL is a set of rows related to the current row, defined by the PARTITION BY and ORDER BY clauses, over which an analytic function operates.

Question 35:
Explain the difference between RANK() and ROW_NUMBER() in a tie situation.
Answer: In a tie situation, RANK() assigns the same rank to tied rows but leaves gaps in the sequence, whereas ROW_NUMBER() assigns a unique number to each row, regardless of ties.

SELECT employee_id, salary, RANK() OVER (ORDER BY salary DESC) AS rank, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num FROM employees;

---

Chapter 3 — Working with Large Data Sets (Continued)

Question 36:
What is the advantage of using BULK COLLECT in Oracle SQL?
Answer: BULK COLLECT improves performance by fetching multiple rows in a single context switch from the database to the PL/SQL engine, reducing overhead compared to fetching rows one at a time.

DECLARE
  TYPE emp_table IS TABLE OF employees%ROWTYPE;
  l_employees emp_table;
BEGIN
  SELECT * BULK COLLECT INTO l_employees FROM employees;
END;

Question 37:
How do you optimize large data loads in Oracle?
Answer: Large data loads can be optimized by using features like DIRECT PATH loading, using APPEND for inserts, and leveraging parallel execution for bulk loads.

ALTER SESSION FORCE PARALLEL DML PARALLEL 4;

Question 38:
What is the difference between a regular view and a materialized view?
Answer: A regular view is a virtual table that computes results dynamically, whereas a materialized view stores the result set physically and can be refreshed periodically for better performance.

CREATE MATERIALIZED VIEW mv_emp_salary AS
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;

Question 39:
Explain the concept of partitioning with respect to performance.
Answer: Partitioning divides large tables into smaller, more manageable segments, allowing Oracle to scan only relevant partitions, which improves query performance and data management.

Question 40:
What are the different partitioning strategies in Oracle?
Answer: Common partitioning strategies in Oracle include range partitioning, list partitioning, hash partitioning, and composite partitioning, each suited to different types of data distribution.

CREATE TABLE employees (
    employee_id NUMBER,
    name VARCHAR2(100),
    hire_date DATE
)
PARTITION BY RANGE (hire_date) (
    PARTITION p1 VALUES LESS THAN (TO_DATE('01-JAN-2020', 'DD-MON-YYYY')),
    PARTITION p2 VALUES LESS THAN (TO_DATE('01-JAN-2021', 'DD-MON-YYYY'))
);

---

Chapter 4 — Oracle SQL Tuning and Optimization (Continued)

Question 41:
What is Oracle’s SQL Tuning Advisor?
Answer: The SQL Tuning Advisor analyzes SQL queries and provides recommendations to improve performance, such as creating indexes or rewriting queries for optimal execution.

Question 42:
How does EXPLAIN PLAN help in query optimization?
Answer: EXPLAIN PLAN shows the execution plan for a query, detailing how Oracle will access and join tables. This helps identify bottlenecks and optimize queries.

EXPLAIN PLAN FOR 
SELECT * FROM employees WHERE salary > 50000;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Question 43:
What is the role of an index in performance optimization?
Answer: Indexes speed up query performance by allowing Oracle to quickly locate rows based on indexed columns, reducing full table scans.

Question 44:
What are function-based indexes, and how are they used?
Answer: Function-based indexes are created on expressions or functions, improving performance for queries that filter or sort based on expressions.

CREATE INDEX emp_salary_idx ON employees (UPPER(name));

Question 45:
What are SQL Hints in Oracle?
Answer: SQL hints are directives embedded in SQL queries to influence the optimizer’s choice of execution plan, such as specifying index usage or join methods.

SELECT /*+ USE_NL(emp) */ * FROM employees emp JOIN departments dept ON emp.department_id = dept.department_id;

---

Chapter 5 — Advanced Database Features (Continued)

Question 46:
What is the JSON_TABLE function in Oracle?
Answer: The JSON_TABLE function allows you to extract data from JSON documents and return it as a relational table.

SELECT * FROM JSON_TABLE(
    '{"employees":[{"id":1,"name":"John"},{"id":2,"name":"Jane"}]}',
    '$.employees[*]' COLUMNS (
        employee_id INT PATH '$.id',
        employee_name VARCHAR2(100) PATH '$.name'
    )
);

Question 47:
How do you perform Flashback Table in Oracle?
Answer: Flashback Table allows you to restore a table to its previous state as of a specific timestamp or SCN.

FLASHBACK TABLE employees TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);

Question 48:
What are Oracle’s recovery options for undo segments?
Answer: Oracle provides options like Flashback, Log Mining, and point-in-time recovery to recover lost or modified data

from undo segments.

Question 49:
What are the benefits of Oracle Multitenant Architecture?
Answer: Multitenant architecture enables resource sharing, simplified patching, and database management with a single container database and multiple pluggable databases (PDBs).

Question 50:
How does the DBMS_ADVANCED_REWRITE package help in query optimization?
Answer: DBMS_ADVANCED_REWRITE helps rewrite SQL queries automatically for optimization, including transforming suboptimal queries into more efficient ones.

---

Chapter 1 — Advanced SQL Techniques (Continued)

Question 51:
What is the purpose of using PIVOT and UNPIVOT in Oracle?
Answer: PIVOT is used to convert rows into columns, while UNPIVOT transforms columns into rows, enabling the reshaping of data for easier analysis.

SELECT * FROM (
    SELECT department_id, job_id, salary FROM employees
) PIVOT (
    SUM(salary) FOR job_id IN ('SA_REP', 'IT_PROG', 'AD_ASST')
);

Question 52:
How can you implement a dynamic PIVOT operation in Oracle SQL?
Answer: A dynamic PIVOT can be implemented by constructing the PIVOT query dynamically using PL/SQL or EXECUTE IMMEDIATE to account for changing columns.

DECLARE
   l_sql VARCHAR2(4000);
BEGIN
   SELECT 'SELECT * FROM (SELECT department_id, job_id, salary FROM employees) PIVOT (SUM(salary) FOR job_id IN (' || LISTAGG(DISTINCT job_id, ', ') WITHIN GROUP (ORDER BY job_id) || '))'
   INTO l_sql
   FROM employees;
   EXECUTE IMMEDIATE l_sql;
END;

Question 53:
What is the difference between INTERSECT and EXCEPT in Oracle SQL?
Answer: INTERSECT returns the common rows from two queries, while EXCEPT (or MINUS in Oracle) returns rows from the first query that are not present in the second query.

SELECT employee_id FROM employees WHERE department_id = 10
INTERSECT
SELECT employee_id FROM employees WHERE salary > 50000;

Question 54:
What is a ROLLUP in SQL, and how is it used?
Answer: ROLLUP is used to generate subtotals and grand totals in a GROUP BY query, creating hierarchical aggregations.

SELECT department_id, job_id, SUM(salary) FROM employees
GROUP BY ROLLUP (department_id, job_id);

---

Chapter 2 — Oracle Analytics and Window Functions (Continued)

Question 55:
How do you calculate a moving average using window functions?
Answer: A moving average can be calculated by defining a window with ROWS BETWEEN and applying an aggregate function like AVG.

SELECT employee_id, salary,
       AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM employees;

Question 56:
What is the difference between RANK() and DENSE_RANK() in Oracle SQL?
Answer: RANK() leaves gaps in rank values for ties, while DENSE_RANK() assigns consecutive rank values without gaps for tied rows.

SELECT employee_id, salary, RANK() OVER (ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

Question 57:
Explain the LAG() function in Oracle.
Answer: The LAG() function returns the value of a column from a previous row in the result set, which is useful for comparing rows.

SELECT employee_id, salary, LAG(salary) OVER (ORDER BY hire_date) AS previous_salary
FROM employees;

Question 58:
How do you calculate the cumulative sum for a partitioned dataset using window functions?
Answer: A cumulative sum can be calculated using SUM() over a window defined by PARTITION BY and ORDER BY.

SELECT employee_id, department_id, salary, 
       SUM(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS cumulative_salary
FROM employees;

---

Chapter 3 — Working with Large Data Sets (Continued)

Question 59:
How do you optimize large data loads using parallel execution in Oracle?
Answer: Oracle allows parallel execution of DML operations like INSERT, UPDATE, and DELETE using the PARALLEL hint, which speeds up data processing.

ALTER SESSION FORCE PARALLEL DML PARALLEL 4;
INSERT /*+ PARALLEL(emp, 4) */ INTO emp_backup SELECT * FROM employees;

Question 60:
What are the benefits of using partitioned tables for large datasets in Oracle?
Answer: Partitioned tables allow Oracle to access only the relevant partitions for queries, improving performance for large datasets and managing them more efficiently.

Question 61:
What is the difference between INLINE and OUTLINE in Oracle SQL?
Answer: INLINE is a hint for the optimizer to expand subqueries directly into the main query, while OUTLINE refers to an approach that preserves query plans across executions.

Question 62:
How do you use BULK COLLECT and FORALL together in Oracle?
Answer: BULK COLLECT retrieves data into PL/SQL collections, and FORALL is used to execute DML operations on those collections in bulk, improving performance.

DECLARE
  TYPE emp_table IS TABLE OF employees%ROWTYPE;
  l_employees emp_table;
BEGIN
  SELECT * BULK COLLECT INTO l_employees FROM employees WHERE salary > 50000;
  FORALL i IN l_employees.FIRST .. l_employees.LAST
    UPDATE employees SET salary = salary * 1.1 WHERE employee_id = l_employees(i).employee_id;
END;

---

Chapter 4 — Oracle SQL Tuning and Optimization (Continued)

Question 63:
What is an Oracle SQL execution plan, and how is it used?
Answer: An execution plan shows the steps Oracle takes to execute a SQL query, providing insights into the performance of queries, including join methods and access paths.

EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Question 64:
What is the purpose of an Oracle SQL profile?
Answer: A SQL profile is used to influence the query optimization process by providing statistics, hints, and recommendations that improve the performance of a specific SQL query.

Question 65:
How do you monitor SQL performance using the AWR report in Oracle?
Answer: The Automatic Workload Repository (AWR) stores performance data that can be analyzed to identify performance bottlenecks and recommend optimizations.

SELECT * FROM dba_hist_sqlstat WHERE sql_id = 'abc123';

Question 66:
What is the significance of OPTIMIZER_FEATURES_ENABLE parameter in Oracle?
Answer: This parameter controls the behavior of the Oracle optimizer by enabling specific features, ensuring compatibility with previous Oracle versions or enforcing certain optimizations.

---

Chapter 5 — Advanced Database Features (Continued)

Question 67:
How does Oracle handle large XML data?
Answer: Oracle provides XMLType to store, query, and manipulate large XML documents efficiently using both structured and unstructured storage options.

SELECT EXTRACT(xml_column, '/employee/name') FROM employees;

Question 68:
What is the difference between XMLTYPE and CLOB in Oracle?
Answer: XMLTYPE is specifically designed for handling XML data, providing XML-specific functions, while CLOB stores character data without XML-specific features.

Question 69:
What is the purpose of the DBMS_FLASHBACK package?
Answer: The DBMS_FLASHBACK package provides functionality for performing flashback operations, such as retrieving the state of data at a past timestamp or undoing transactions.

SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);

Question 70:
Explain the concept of Multitenant Architecture in Oracle databases.
Answer: Multitenant architecture allows multiple pluggable databases (PDBs) to reside within a single container database (CDB), providing benefits such as easier consolidation and management.

Question 71:
How does Oracle handle JSON data?
Answer: Oracle provides native support for JSON with functions like JSON_VALUE, JSON_TABLE, and JSON_OBJECT, enabling storage, querying, and manipulation of JSON data.

SELECT JSON_VALUE(json_column, '$.name') FROM employees;

Question 72:
What is the use of DBMS_ADVANCED_REWRITE in Oracle?
Answer: DBMS_ADVANCED_REWRITE allows Oracle to automatically rewrite SQL queries to improve their performance by transforming inefficient queries into optimized ones.

---

Chapter 1 — Advanced SQL Techniques (Continued)

Question 73:
What is the purpose of using WITH clause (Common Table Expressions – CTE)?
Answer: The WITH clause allows you to define temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement, making complex queries more readable and maintainable.

WITH dept_avg AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT * FROM dept_avg;

Question 74:
What is a recursive query in Oracle, and how is it written?
Answer: A recursive query in Oracle is used to retrieve hierarchical data. It uses a CTE where the base case and recursive part are defined.

WITH RECURSIVE emp_hierarchy AS (
    SELECT employee_id, manager_id, employee_name
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.employee_name
    FROM employees e
    JOIN emp_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM emp_hierarchy;

Question 75:
What is a PIVOT query, and how is it different from UNPIVOT?
Answer: PIVOT converts rows into columns, while UNPIVOT converts columns into rows, both useful for reshaping data.

-- PIVOT example
SELECT * FROM (
    SELECT department_id, job_id, salary FROM employees
) PIVOT (
    SUM(salary) FOR job_id IN ('SA_REP', 'IT_PROG', 'AD_ASST')
);

Question 76:
Explain how UNPIVOT works with an example.
Answer: UNPIVOT transforms columns into rows, which can be helpful in situations where data needs to be converted from a wide format to a long format.

SELECT department_id, job_id, salary
FROM (
    SELECT department_id, 'SA_REP' AS job_id, salary FROM employees
    UNION ALL
    SELECT department_id, 'IT_PROG' AS job_id, salary FROM employees
) UNPIVOT (
    salary FOR job_id IN ('SA_REP', 'IT_PROG')
);

---

Chapter 2 — Oracle Analytics and Window Functions (Continued)

Question 77:
What is the ROW_NUMBER() window function in Oracle SQL?
Answer: ROW_NUMBER() assigns a unique sequential integer to rows within a partition of a result set, useful for ranking or removing duplicates.

SELECT employee_id, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

Question 78:
How can PARTITION BY be used with window functions?
Answer: PARTITION BY is used to divide the result set into partitions and apply a window function to each partition independently.

SELECT department_id, employee_id, salary, 
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;

Question 79:
What is the LEAD() function, and how is it different from LAG()?
Answer: LEAD() provides the value from the next row, while LAG() gives the value from the previous row, both used for comparing values across rows.

SELECT employee_id, salary, LEAD(salary) OVER (ORDER BY hire_date) AS next_salary
FROM employees;

Question 80:
Explain the difference between RANK() and DENSE_RANK() when used with window functions.
Answer: RANK() leaves gaps in the rank for ties, while DENSE_RANK() assigns consecutive ranks without gaps.

SELECT salary, RANK() OVER (ORDER BY salary DESC), DENSE_RANK() OVER (ORDER BY salary DESC)
FROM employees;

---

Chapter 3 — Working with Large Data Sets (Continued)

Question 81:
What is the advantage of using BULK COLLECT in Oracle PL/SQL?
Answer: BULK COLLECT allows you to retrieve multiple rows of data into PL/SQL collections in a single context switch, significantly improving performance.

DECLARE
   TYPE emp_table IS TABLE OF employees%ROWTYPE;
   l_employees emp_table;
BEGIN
   SELECT * BULK COLLECT INTO l_employees FROM employees WHERE department_id = 10;
END;

Question 82:
How does FORALL help in improving performance in DML operations?
Answer: FORALL allows bulk DML operations to be executed in a single context switch, reducing overhead and improving performance.

DECLARE
   TYPE emp_ids IS TABLE OF employees.employee_id%TYPE;
   l_emp_ids emp_ids := emp_ids(1001, 1002, 1003);
BEGIN
   FORALL i IN l_emp_ids.FIRST .. l_emp_ids.LAST
      UPDATE employees SET salary = salary * 1.05 WHERE employee_id = l_emp_ids(i);
END;

Question 83:
What is a materialized view in Oracle, and how does it improve performance?
Answer: A materialized view stores the result of a query physically, allowing for faster retrieval of data without having to re-run complex queries. It can be refreshed periodically to stay current.

CREATE MATERIALIZED VIEW emp_summary AS
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;

Question 84:
How does partitioning improve query performance in Oracle?
Answer: Partitioning divides large tables into smaller, more manageable pieces, improving query performance by limiting the amount of data scanned.

CREATE TABLE employees (
    employee_id INT,
    name VARCHAR2(100),
    department_id INT
)
PARTITION BY RANGE (salary) (
    PARTITION low_salary VALUES LESS THAN (50000),
    PARTITION mid_salary VALUES LESS THAN (100000),
    PARTITION high_salary VALUES LESS THAN (200000)
);

---

Chapter 4 — Oracle SQL Tuning and Optimization (Continued)

Question 85:
How can you read and interpret an Oracle execution plan?
Answer: An execution plan shows the steps Oracle takes to execute a query. You can interpret it to understand the methods used for accessing data, like table scans, index scans, and join methods.

EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Question 86:
What is the purpose of using Oracle hints?
Answer: Oracle hints influence the optimizer’s decision on how to execute a query, improving performance by suggesting specific access methods or join strategies.

SELECT /*+ INDEX(employees emp_id_idx) */ * FROM employees WHERE employee_id = 100;

Question 87:
What is Adaptive Query Optimization in Oracle?
Answer: Adaptive Query Optimization allows the optimizer to dynamically adjust query plans during execution based on real-time statistics, improving performance in unpredictable environments.

Question 88:
What is the significance of the TKPROF utility in Oracle SQL tuning?
Answer: TKPROF formats raw trace files generated by Oracle, helping to analyze the execution time and resource consumption of SQL queries for performance tuning.

TKPROF tracefile.prf outputfile.prf

---

Chapter 5 — Advanced Database Features (Continued)

Question 89:
What is the role of DBMS_FLASHBACK in data recovery?
Answer: DBMS_FLASHBACK allows you to query data as it existed at a previous point in time, enabling rollback or recovery of lost data.

SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);

Question 90:
Explain Oracle’s support for JSON data.
Answer: Oracle provides native support for JSON using the JSON data type, along with functions like JSON_VALUE, JSON_TABLE, and JSON_OBJECT to query and manipulate JSON data.

SELECT JSON_VALUE(json_column, '$.name') FROM employees;

Question 91:
What is Oracle’s Multitenant Architecture and its benefits?
Answer: The Multitenant Architecture allows multiple pluggable databases (PDBs) within a single container database (CDB), simplifying database management and providing better resource utilization.

Question 92:
How do you optimize a query using SQL Profiles in Oracle?
Answer: SQL Profiles help improve query performance by providing recommendations to the optimizer about better query plans based on historical performance data.

Question 93:
What are Flashback queries in Oracle, and how do they work?
Answer: Flashback queries allow you to view historical data as it appeared at a previous time, providing an important feature for recovering from mistakes or analyzing data changes.

SELECT * FROM employees AS OF TIMESTAMP (SYSDATE – INTERVAL ‘2’ DAY);

---

### Final Set of Questions

**Question 94:**  
**What are `Database Links` in Oracle, and how are they used?**  
**Answer:** A `Database Link` connects one Oracle database to another, enabling queries and operations to be performed across databases.  
```sql
SELECT * FROM employees@remote_db;

Question 95:
What is Oracle’s Data Guard?
Answer: Data Guard provides data protection and disaster recovery by creating and maintaining standby databases that can be used for failover or backup.

Question 96:
What is Oracle ASM (Automatic Storage Management)?
Answer: Oracle ASM is a storage management solution that simplifies storage management by automatically distributing database files across multiple disks.

Question 97:
What are Oracle Data Pump and its advantages?
Answer: Oracle Data Pump is a high-speed data transfer utility for exporting and importing data between Oracle databases. It is faster than the traditional exp and imp utilities.

Question 98:
Explain the concept of Exadata and its benefits.
Answer: Exadata is Oracle’s engineered system designed for high-performance, high-availability database workloads, offering optimized hardware and software for superior database performance.

Question 99:
How do you implement a Global Temporary Table in Oracle?
Answer: A Global Temporary Table holds session-specific data that is private to the session but can be used across transactions within that session.

CREATE GLOBAL TEMPORARY TABLE temp_employees (
    employee_id INT,
    name VARCHAR2(100)
) ON COMMIT DELETE ROWS;

Question 100:
What is the Oracle Cloud Infrastructure (OCI)?
Answer: OCI provides cloud-based services for managing Oracle databases, applications, and infrastructure, offering scalability, security, and high availability.

---

This completes all 100 expert-level Oracle SQL interview questions and answers.