SQL Recipes
SQL
Core SQL recipes for Oracle — analytics functions, data recovery, regular expressions, date handling, and much more.
Access Data from Previous Record
Use LAG and LEAD analytics functions to compare values across rows.
Learn more →MERGE — Insert or Update in One Statement
Combine INSERT and UPDATE logic into a single atomic MERGE statement.
Learn more →Regular Expressions in Oracle SQL
Pattern matching with REGEXP_LIKE, REGEXP_SUBSTR, REGEXP_REPLACE, and REGEXP_COUNT.
Learn more →PIVOT and UNPIVOT — Rotating Rows and Columns
Transform rows into columns and back for reporting and data analysis.
Learn more →Date Arithmetic and Formatting
Master date calculations, formatting with TO_CHAR, TRUNC, ADD_MONTHS, and date generation.
Learn more →NVL, NVL2, COALESCE, and DECODE — Handling NULLs
Deal with NULL values using Oracle's null-handling functions and avoid common pitfalls.
Learn more →Hierarchical Queries with CONNECT BY
Build tree structures, org charts, and recursive paths with CONNECT BY and recursive WITH.
Learn more →Sequences and Auto-Increment Columns
Generate unique IDs with sequences, triggers, and Oracle 12c identity columns.
Learn more →Using WITH to Avoid Duplicate Code
Materialize subqueries with the WITH clause to eliminate duplication and improve readability.
Learn more →Creating a Dummy Table for Tests
Generate test tables with random data using DBMS_RANDOM and hierarchical queries.
Learn more →Using Flashback to Recover Lost Data
Recover accidentally deleted data using Oracle Flashback technology.
Learn more →What Oracle Functions Are There
Query the data dictionary to discover built-in Oracle functions and their parameters.
Learn more →Get a List of All Available Tables
Query dba_tables, all_tables, or user_tables to list database tables.
Learn more →Aggregate Multiple Rows into a Comma-Delimited List
Use LISTAGG to combine multiple rows into a single comma-separated string.
Learn more →Limit Number of Rows in a Sorted Query
Correctly limit results with ORDER BY using subqueries or the 12c FETCH clause.
Learn more →Working with Arrays - VARRAY
Use Oracle VARRAY types to store multi-value attributes in table columns.
Learn more →Create a Table Out of Nothing
Generate virtual tables and number sequences using DUAL and hierarchical queries.
Learn more →