Advanced Oracle SQL Query Examples

By Ercan - 15/06/2025 - 0 comments

When working with Oracle databases, you often go beyond simple SELECT statements. Real-world data is hierarchical, analytical, and sometimes messy. In this article, we’ll explore powerful Oracle SQL techniques that solve complex data problems — using realistic examples and sample tables.

We’ll cover partitions, hierarchies, analytics, and more. Each example includes setup scripts and queries you can run directly in your Oracle environment.

Sample Schema Setup

Let’s start by creating two simple but related tables: departments and employees.

CREATE TABLE departments (
  dept_id NUMBER PRIMARY KEY,
  dept_name VARCHAR2(50)
);

CREATE TABLE employees (
  emp_id NUMBER PRIMARY KEY,
  emp_name VARCHAR2(50),
  manager_id NUMBER,
  dept_id NUMBER,
  salary NUMBER,
  hire_date DATE,
  FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

Now insert a few rows:

INSERT INTO departments VALUES (1, 'Engineering');
INSERT INTO departments VALUES (2, 'Sales');

INSERT INTO employees VALUES (101, 'Alice', NULL, 1, 9500, DATE '2020-01-15');
INSERT INTO employees VALUES (102, 'Bob', 101, 1, 7200, DATE '2021-03-10');
INSERT INTO employees VALUES (103, 'Carol', 101, 2, 6800, DATE '2022-07-25');
INSERT INTO employees VALUES (104, 'David', 102, 1, 6000, DATE '2023-05-05');
INSERT INTO employees VALUES (105, 'Eve', 103, 2, 6400, DATE '2024-02-11');

1. Partition Queries: Ranking Employees Within Departments

Partitioning functions let you perform grouped operations without collapsing rows.
Let’s find the top two earners in each department.

SELECT dept_id, emp_name, salary
FROM (
  SELECT dept_id, emp_name, salary,
         ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
  FROM employees
)
WHERE rn <= 2;

Explanation:

  • PARTITION BY dept_id restarts ranking for each department.
  • ORDER BY salary DESC sorts employees within that partition.
  • The outer query filters only the top two.

2. Hierarchical Queries: Manager → Employee Tree

Oracle’s CONNECT BY PRIOR syntax makes it easy to traverse hierarchical relationships such as organizational charts.

SELECT LPAD(' ', LEVEL * 2) || emp_name AS hierarchy
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

Explanation:

  • START WITH manager_id IS NULL selects the root (top-level manager).
  • CONNECT BY PRIOR recursively finds subordinates.
  • LEVEL indicates the hierarchy depth and can be used for indentation.

This is one of Oracle’s oldest but still most elegant features.

3. Recursive WITH Queries: A Modern Hierarchy Alternative

In modern Oracle versions, you can achieve the same result with a recursive WITH clause.

WITH emp_hierarchy (emp_id, emp_name, manager_id, level_no) AS (
  SELECT emp_id, emp_name, manager_id, 1 AS level_no
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.emp_name, e.manager_id, h.level_no + 1
  FROM employees e
  JOIN emp_hierarchy h ON e.manager_id = h.emp_id
)
SELECT LPAD(' ', level_no * 2) || emp_name AS hierarchy
FROM emp_hierarchy;

Tip: Recursive CTEs are more flexible for modern applications because you can join, filter, or aggregate inside the recursion.

4. Analytical Functions: Comparing Salaries Over Time

Analytical functions such as LAG() and LEAD() help analyze sequential data — for example, tracking salary changes.

SELECT emp_id, emp_name, salary,
       LAG(salary) OVER (ORDER BY hire_date) AS previous_salary,
       salary - LAG(salary) OVER (ORDER BY hire_date) AS salary_diff
FROM employees;

Explanation:

  • LAG(salary) returns the previous row’s salary.
  • salary_diff shows how much each employee’s salary differs from the last hired employee.

5. PIVOT Queries: Converting Rows to Columns

Oracle’s PIVOT clause lets you dynamically reshape data. Here’s an example that aggregates salaries by department.

SELECT *
FROM (
  SELECT dept_id, emp_name, salary
  FROM employees
)
PIVOT (
  SUM(salary) FOR dept_id IN (1 AS Engineering, 2 AS Sales)
);


Explanation:

  • The inner query provides base data.
  • PIVOT transforms department IDs into columns.
  • Each cell contains the sum of salaries in that department.

This is extremely useful in reporting and data visualization.

6. JSON Queries: Extracting Structured Data

If you store JSON data in Oracle (from version 12c onward), you can query it directly using JSON_TABLE.

CREATE TABLE projects (
  project_id NUMBER PRIMARY KEY,
  project_data CLOB CHECK (project_data IS JSON)
);

INSERT INTO projects VALUES (1, '{"name": "Apollo", "team": ["Alice","Bob"], "budget": 25000}');
INSERT INTO projects VALUES (2, '{"name": "Zeus", "team": ["Carol","Eve"], "budget": 18000}');

SELECT p.project_id, jt.name, jt.budget
FROM projects p,
     JSON_TABLE(p.project_data, '$'
       COLUMNS (
         name VARCHAR2(50) PATH '$.name',
         budget NUMBER PATH '$.budget'
       )
     ) jt;

Result:

PROJECT_ID NAME BUDGET
1 Apollo 25000
2 Zeus 18000


Note: You can also extract nested arrays or objects by using NESTED PATH inside JSON_TABLE.

7. Conditional Aggregation: Dynamic Summaries

You can conditionally aggregate data without writing multiple queries by using CASE WHEN.

SELECT
  SUM(CASE WHEN dept_id = 1 THEN salary ELSE 0 END) AS eng_total,
  SUM(CASE WHEN dept_id = 2 THEN salary ELSE 0 END) AS sales_total
FROM employees;

This is the manual equivalent of PIVOT and works in databases that don’t support it.

8. Performance Tip: Inline Views and Materialization Hints

When your subqueries are reused multiple times, Oracle might re-evaluate them. You can use the MATERIALIZE hint to force caching of results.

SELECT /*+ MATERIALIZE */ *
FROM (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
) v
WHERE v.avg_salary > 7000;

This can help when dealing with large datasets or analytical dashboards.

Final Notes

Oracle SQL offers an incredibly rich feature set — far beyond what basic tutorials cover. Partitioned queries, hierarchical traversal, analytical functions, and JSON querying can significantly reduce application logic by pushing intelligence to the database layer.

Experimenting with these techniques on small datasets helps you understand how Oracle’s optimizer works and how to design more efficient SQL for real-world enterprise systems.

Tags: oracle