Oracle Database Query Cookbook

Enterprise object-relational database standard deployed across mission-critical corporate environments. 10 production-tested query recipes ready to copy and execute.

🐘 PostgreSQL 🐬 MySQL 🪶 SQLite 🔴 Oracle Database 💻 Microsoft SQL Server 🍃 MongoDB 🔴 Redis ❄️ Snowflake 🍒 ClickHouse 🦆 DuckDB

1. Paginate Results with FETCH FIRST N ROWS

Standard ANSI pagination syntax in modern Oracle 12c+.

SELECT employee_id, first_name, salary
FROM employees
ORDER BY salary DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

#pagination#oracle#fetch

2. Handle NULLs with NVL / NVL2

Substitute default value when expressions return NULL.

SELECT first_name, NVL(commission_pct, 0) AS commission,
       NVL2(commission_pct, 'Commissioned', 'Salaried') AS pay_type
FROM employees;

#nvl#null#oracle

3. Query Hierarchical Trees with CONNECT BY

Traverse parent-child org chart relationships.

SELECT LEVEL, employee_id, last_name, manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

#connect_by#hierarchy#tree

4. Concatenate Rows with LISTAGG

Aggregate group strings with specified delimiter.

SELECT department_id,
       LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY last_name) AS team
FROM employees
GROUP BY department_id;

#listagg#string#aggregate

5. Generate Identity Sequence Values

Create sequence generator and fetch NEXTVAL.

CREATE SEQUENCE order_seq START WITH 1000 INCREMENT BY 1;

-- Usage in INSERT:
INSERT INTO orders (id, total) VALUES (order_seq.NEXTVAL, 150.00);

#sequence#nextval#identity

6. Flashback Query Past Data State

Inspect deleted table rows at historical timestamp.

SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '15' MINUTE)
WHERE department_id = 20;

#flashback#history#recovery

7. Upsert with MERGE INTO

Synchronize data using ANSI standard MERGE statement.

MERGE INTO target_table t
USING source_table s ON (t.id = s.id)
WHEN MATCHED THEN
  UPDATE SET t.val = s.val
WHEN NOT MATCHED THEN
  INSERT (id, val) VALUES (s.id, s.val);

#merge#upsert#oracle

8. Transform Rows with PIVOT

Pivot department totals into cross-tab output columns.

SELECT * FROM (
  SELECT department_id, salary FROM employees
)
PIVOT (
  SUM(salary) FOR department_id IN (10 AS D10, 20 AS D20, 30 AS D30)
);

#pivot#analytics#cross-tab

9. View Current Session Memory

Check current connected session SGA and PGA consumption.

SELECT name, value FROM v$mystat m JOIN v$statname n USING (statistic#)
WHERE n.name LIKE '%memory%';

#dba#memory#stats

10. Create Materialized View

Build pre-computed query snapshot view with auto-refresh.

CREATE MATERIALIZED VIEW dept_summary
BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND
AS SELECT department_id, COUNT(*) cnt, SUM(salary) total FROM employees GROUP BY department_id;

#materialized_view#performance#ddl

Test your Oracle Database architectural knowledge!

Practice key concepts, memory structures, and indexing questions with flashcards.

Study Oracle Database Flashcards →