Oracle Database Learning: Part 4 – Advanced SQL: Joins, Subqueries & Analytical Functions

JOINs in Oracle

Oracle supports both ANSI SQL JOIN syntax and its older proprietary syntax. Always prefer ANSI syntax for readability.

INNER JOIN

-- Returns rows with matching values in both tables
SELECT e.first_name, e.last_name, e.salary, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary > 50000
ORDER BY e.salary DESC;

LEFT / RIGHT / FULL OUTER JOIN

-- LEFT JOIN: All employees including those without a department
SELECT e.first_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

-- RIGHT JOIN: All departments including empty ones
SELECT e.first_name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

-- FULL OUTER JOIN: All rows from both tables
SELECT e.first_name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id;

SELF JOIN

-- Find employee and their manager
SELECT e.first_name || ' ' || e.last_name AS employee,
       m.first_name || ' ' || m.last_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

Subqueries

-- Scalar subquery (returns single value)
SELECT first_name, salary,
       (SELECT ROUND(AVG(salary),2) FROM employees) AS avg_salary
FROM employees;

-- Inline view (FROM clause subquery)
SELECT dept_name, avg_sal FROM (
    SELECT d.dept_name, ROUND(AVG(e.salary),2) AS avg_sal
    FROM employees e JOIN departments d ON e.dept_id = d.dept_id
    GROUP BY d.dept_name
)
WHERE avg_sal > 60000;

-- Correlated subquery (references outer query)
SELECT first_name, salary, dept_id
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.dept_id = e1.dept_id  -- correlated to outer dept_id
);

WITH Clause (Common Table Expressions)

-- CTE for readability and reuse
WITH dept_stats AS (
    SELECT dept_id,
           COUNT(*)          AS headcount,
           ROUND(AVG(salary),2) AS avg_salary,
           MAX(salary)       AS max_salary
    FROM employees
    GROUP BY dept_id
),
high_paying_depts AS (
    SELECT dept_id FROM dept_stats WHERE avg_salary > 70000
)
SELECT e.first_name, e.salary, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.dept_id IN (SELECT dept_id FROM high_paying_depts)
ORDER BY e.salary DESC;

Analytical (Window) Functions

Window functions perform calculations across a set of rows related to the current row — without collapsing rows like GROUP BY does.

-- ROW_NUMBER: Unique sequential number per partition
SELECT first_name, dept_id, salary,
       ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
FROM employees;

-- RANK vs DENSE_RANK
-- RANK: gaps after ties (1,1,3)
-- DENSE_RANK: no gaps (1,1,2)
SELECT first_name, salary,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS d_rnk
FROM employees;

-- Top 2 earners per department
SELECT * FROM (
    SELECT first_name, dept_id, salary,
           DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dr
    FROM employees
)
WHERE dr <= 2;

LAG and LEAD

-- Compare current row to previous/next row
SELECT first_name, hire_date, salary,
       LAG(salary, 1, 0)  OVER (ORDER BY hire_date) AS prev_salary,
       LEAD(salary, 1, 0) OVER (ORDER BY hire_date) AS next_salary,
       salary - LAG(salary,1,0) OVER (ORDER BY hire_date) AS salary_diff
FROM employees
ORDER BY hire_date;

SUM / AVG as Window Function

-- Running total salary by department
SELECT first_name, dept_id, salary,
       SUM(salary) OVER (
           PARTITION BY dept_id
           ORDER BY hire_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM employees;

PIVOT and UNPIVOT

-- PIVOT: Rows to columns
SELECT * FROM (
    SELECT dept_id, EXTRACT(YEAR FROM hire_date) AS yr, salary
    FROM employees
)
PIVOT (
    SUM(salary)
    FOR yr IN (2021 AS "2021", 2022 AS "2022", 2023 AS "2023", 2024 AS "2024")
);

-- UNPIVOT: Columns to rows
SELECT dept_id, year_label, total_salary
FROM dept_salary_pivot
UNPIVOT (
    total_salary
    FOR year_label IN ("2021", "2022", "2023", "2024")
);

CONNECT BY (Hierarchical Queries)

-- Org chart: employees and their hierarchy
SELECT LEVEL,
       LPAD(' ', 2*(LEVEL-1)) || first_name AS org_chart,
       emp_id, manager_id
FROM employees
START WITH manager_id IS NULL       -- root node
CONNECT BY PRIOR emp_id = manager_id
ORDER SIBLINGS BY first_name;

Lab 4: Real-World Query Challenges

-- Challenge 1: Median salary per department
SELECT dept_id,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees
GROUP BY dept_id;

-- Challenge 2: Employees hired in the last 90 days
SELECT first_name, hire_date
FROM employees
WHERE hire_date >= SYSDATE - 90;

-- Challenge 3: Cumulative % of total salary
SELECT first_name, salary,
       ROUND(
           SUM(salary) OVER (ORDER BY salary ROWS UNBOUNDED PRECEDING) /
           SUM(salary) OVER () * 100, 2
       ) AS cumulative_pct
FROM employees ORDER BY salary;

Common Errors

ORA-00904: invalid identifier – Column alias used in WHERE (use HAVING or inline view instead).

-- WRONG
SELECT salary * 1.1 AS new_sal FROM employees WHERE new_sal > 50000;

-- CORRECT: Use inline view
SELECT * FROM (SELECT salary * 1.1 AS new_sal FROM employees) WHERE new_sal > 50000;

ORA-01427: single-row subquery returns more than one row – Use IN instead of = for multi-row subqueries.

Summary

Always use ANSI JOIN syntax; avoid Oracle's old (+) outer join syntax
CTEs (WITH clause) improve readability for complex queries
Window functions (OVER/PARTITION BY) are powerful for rankings, running totals, and comparisons
Use CONNECT BY for tree/hierarchy data without recursive CTEs

Next: Part 5 – PL/SQL Essentials: Blocks, Cursors & Procedures