By Vinish Kapoor

Oracle SQL Tutorials

Every Oracle SQL tutorial on vinish.dev: queries, joins, analytics recipes, JSON, and 130+ SQL function guides, organized so you can find the one you need in seconds.

  • 172 tutorials
  • 9 topics
  • 3 learning paths
  • Updated October 2026

Browse by topic

Learning paths

Ordered series you can follow from the first lesson to the last.

Beginner

SQL Essentials

Create a table, then query, change, and join data, step by step.

Start with: How to Create a Table in Oracle 23ai

All 15 lessons
  1. How to Create a Table in Oracle 23ai
  2. Understanding Data Types in Oracle Database 23ai
  3. How to Insert Data into a Table in Oracle 23ai
  4. How to Use SELECT Queries in Oracle Database 23ai
  5. How to Use WHERE Clause in Oracle Database 23ai Queries
  6. How to Use ORDER BY in Oracle Database 23ai Queries
  7. How to Use GROUP BY in Oracle Database 23ai
  8. Using UPDATE Statement in Oracle Database 23ai
  9. Using DELETE Statement in Oracle Database 23ai
  10. Using MERGE Statement in Oracle Database 23ai
  11. Oracle SQL INNER JOIN: Complete Guide to Matching Records Between Tables
  12. Oracle SQL Query to Use LEFT OUTER JOIN to Include All Records from Left Table
  13. Oracle SQL Query to Use Full Outer Join for Data Reconciliation
  14. Oracle SQL Query to Use EXPLAIN PLAN for Join Analysis
  15. Synonym in Oracle Database
Beginner

Most-Used SQL Functions

The functions you will reach for every day: dates, text, numbers, NULL handling, and conversions.

Start with: Oracle TO_CHAR (Date) Function: A Simple Guide to Formatting Dates

All 19 lessons
  1. Oracle TO_CHAR (Date) Function: A Simple Guide to Formatting Dates
  2. Oracle TO_DATE Function: A Simple Guide to Converting Strings to Dates
  3. Oracle SYSDATE Function: A Simple Guide
  4. Oracle ADD_MONTHS Function: A Simple Guide
  5. Oracle MONTHS_BETWEEN Function: A Simple Guide
  6. Oracle TRUNC (Date) Function: A Simple Guide
  7. Oracle SUBSTR Function: A Simple Guide
  8. Oracle INSTR Function: A Simple Guide to Finding Substrings
  9. Oracle REPLACE Function: A Simple Guide
  10. Oracle TRIM Function: A Simple Guide
  11. Oracle REGEXP_REPLACE Function: A Simple Guide
  12. Oracle ROUND Function (Number): A Simple Guide
  13. Oracle NVL Function: A Simple Guide
  14. Oracle COALESCE Function: A Simple Guide
  15. Oracle DECODE Function: A Simple Guide
  16. Oracle TO_NUMBER Function: A Simple Guide
  17. Oracle CAST Function: A Simple Guide
  18. Oracle JSON_VALUE Function
  19. Oracle JSON_TABLE Function
Practical

SQL Query Recipes

Analytics patterns you can reuse: running totals, cohorts, churn, gaps and islands.

Start with: Oracle SQL Query to Calculate Business Days Excluding Holidays

All 12 lessons
  1. Oracle SQL Query to Calculate Business Days Excluding Holidays
  2. Oracle SQL Query to Find Overlapping Date Ranges
  3. Oracle SQL Query to Implement Cohort Analysis for Customer Retention
  4. Oracle SQL Query to Identify and Handle Duplicate Records
  5. Oracle SQL Query to Parse and Extract JSON Data with JSON_TABLE
  6. Oracle SQL Query to Calculate Churn Rate and Retention Metrics
  7. Oracle SQL Query to Perform ABC Analysis for Inventory Management
  8. Oracle SQL Query to Calculate Running Totals
  9. Oracle SQL Query to Find Gaps and Islands in Sequential Data
  10. Oracle SQL Query to Use LISTAGG for String Aggregation
  11. How to Convert Oracle Database Tables to JSON Format
  12. Oracle SQL Query to Implement Lead/Lag Analysis for Time Series Data

Latest tutorials

Books & free tools

Go deeper with the books, or try the free tools.

All tutorials by topic

SQL Basics & DML 14

SQL Developer, creating tables, SELECT, WHERE, GROUP BY, INSERT, UPDATE, DELETE, and MERGE.

  • Oracle SQL Developer Guide

    If you work with Oracle databases, you need a reliable tool that makes querying, building, and managing your data feel effortless. Oracle SQL Developer is exactly that…

  • How to Create a Table in Oracle 23ai

    Creating tables is the foundation of database development in Oracle. A table defines how your data will be structured, stored, and managed. In Oracle Database 23ai, the…

  • Understanding Data Types in Oracle Database 23ai

    Every value stored in an Oracle Database must follow a data type. A data type defines what kind of data can be stored, how much space it takes, and what operations you…

  • How to Insert Data into a Table in Oracle 23ai

    When you start working with Oracle Database 23ai, one of the first things you need to learn is how to add data into your tables. Inserting data is a basic yet powerful…

  • How to Use SELECT Queries in Oracle Database 23ai

    Working with a database is all about getting useful information out of it. Oracle Database 23ai gives you a powerful way to do this with the SELECT statement. This…

  • How to Use WHERE Clause in Oracle Database 23ai Queries

    The WHERE clause tells Oracle which rows to keep and which to skip. This tutorial shows practical ways to filter data using comparisons, ranges, pattern matching, NULL…

  • How to Use ORDER BY in Oracle Database 23ai Queries

    The ORDER BY clause in Oracle Database 23ai lets you sort the rows returned by a query. Without it, the database can return results in any order. ORDER BY helps you…

  • How to Use GROUP BY in Oracle Database 23ai

    The GROUP BY clause in Oracle Database 23ai is used when you want to organize rows into groups and then apply aggregate functions such as COUNT, SUM, AVG, MIN, or MAX.…

  • Using UPDATE Statement in Oracle Database 23ai

    The UPDATE statement in Oracle Database 23ai is used to change existing data in a table. Instead of inserting new rows, UPDATE helps you adjust or fix information that…

  • Using DELETE Statement in Oracle Database 23ai

    The DELETE statement in Oracle Database 23ai is used to remove existing rows from a table. Unlike DROP TABLE, which removes the entire table, DELETE only takes away…

  • Using MERGE Statement in Oracle Database 23ai

    The MERGE statement in Oracle Database is a powerful command that combines the functionality of both INSERT and UPDATE. Instead of writing separate SQL commands, MERGE…

  • Synonym in Oracle Database

    If you work with multiple schemas or long object names, synonyms in Oracle Database can make your SQL cleaner and your apps easier to maintain. A synonym is a friendly…

  • Oracle Create Table Examples

    In Oracle SQL, the CREATE TABLE statement is used to define a new table in the database. A table consists of rows and columns, where each column has a specific data type…

  • CREATE TABLE in Oracle with Auto Increment ID

    If you have worked with MySQL or SQL Server, you are probably used to AUTO_INCREMENT or IDENTITY columns that generate unique IDs automatically. Oracle does things a…

Joins & Query Recipes 14

Joins, execution plans, and analytics: running totals, cohorts, gaps and islands.

JSON in SQL 14

JSON functions, JSON_TABLE, and generating JSON from tables.

  • How to Convert Oracle Database Tables to JSON Format

    How do you convert traditional relational data into JSON format using Oracle SQL? JSON has become the de facto standard for data exchange in modern applications, and…

  • Oracle JSON Type Constructor

    Important Prerequisite: The JSON type constructor is a modern feature. To use it, your database compatible parameter must be set to 20 or higher. If you are on an older…

  • Oracle JSON_ARRAY Function

    The JSON_ARRAY function in Oracle SQL is a "constructor" function. Its job is the opposite of JSON_VALUE or JSON_QUERY. Instead of extracting data from JSON, JSON_ARRAY…

  • Oracle JSON_ARRAYAGG Function

    The JSON_ARRAYAGG function in Oracle SQL is an aggregate function. Its job is to take all the values from a single column across multiple rows and combine (or…

  • Oracle JSON_DATAGUIDE Function

    The JSON_DATAGUIDE function in Oracle SQL is a powerful aggregate function used for data analysis. Its job is not to extract data, but to analyze a collection of JSON…

  • Oracle JSON_OBJECT Function

    The JSON_OBJECT function in Oracle SQL is a "constructor" function. Its job is the opposite of JSON_VALUE or JSON_QUERY. Instead of extracting data from JSON…

  • Oracle JSON_OBJECTAGG Function

    The JSON_OBJECTAGG function in Oracle SQL is an aggregate function. Its job is to take all the rows in a group and combine them into a single JSON object. This is the…

  • Oracle JSON_QUERY Function

    When working with JSON data in Oracle, you have two main functions for extracting data: JSON_VALUE and JSON_QUERY. This tutorial focuses on JSON_QUERY, which you use…

  • Oracle JSON_SCALAR Function

    The JSON_SCALAR function in Oracle SQL is a constructor function that converts a single SQL scalar value (like a NUMBER, VARCHAR2, or DATE) into its equivalent JSON…

  • Oracle JSON_SERIALIZE Function

    The JSON_SERIALIZE function in Oracle SQL is a conversion function that takes JSON data (stored as a JSON type, VARCHAR2, CLOB, or BLOB) and converts it back into a…

  • Oracle JSON_TABLE Function

    The JSON_TABLE function in Oracle SQL is the most powerful and complex of Oracle's JSON functions. Its job is to "shred" a JSON document and present it as a regular…

  • Oracle JSON_TRANSFORM Function

    The JSON_TRANSFORM function is Oracle's most powerful tool for modifying JSON data directly within a SQL query. While JSON_VALUE and JSON_QUERY are used to read data…

  • Oracle JSON_VALUE Function

    When working with JSON data in Oracle, you have two main functions for extracting data: JSON_VALUE and JSON_QUERY. The difference is simple but critical: This tutorial…

  • Oracle SQL Query to Parse and Extract JSON Data with JSON_TABLE

    Have you ever found yourself staring at a column full of JSON data in your Oracle database, wondering how to extract specific values without writing complex string…

String Functions 21

SUBSTR, INSTR, REPLACE, REGEXP, TRIM, LPAD, and every other character function.

Numeric Functions 26

ROUND, TRUNC, MOD, CEIL, FLOOR, POWER, and the math functions.

  • Oracle ABS Function: A Simple Guide

    The ABS function in Oracle SQL is a straightforward mathematical function. Its one and only job is to return the absolute value of a number. In simple terms, it makes…

  • Oracle ACOS Function: A Simple Guide

    The ACOS function in Oracle SQL is a trigonometric function that stands for Arc Cosine. It does the opposite of the COS (Cosine) function. While COS takes an angle and…

  • Oracle ASIN Function: A Simple Guide

    The ASIN function in Oracle SQL is a trigonometric function that stands for Arc Sine. It is the opposite of the SIN (Sine) function. While SIN takes an angle and gives…

  • Oracle ATAN Function: A Simple Guide

    The ATAN function in Oracle SQL is a trigonometric function that stands for Arc Tangent. It is the opposite of the TAN (Tangent) function. While TAN takes an angle and…

  • Oracle ATAN2 Function: A Simple Guide

    The ATAN2 function in Oracle SQL is a powerful trigonometric function. It's the "2-argument" version of the arc tangent. While ATAN(n) calculates the arc tangent of a…

  • Oracle BITAND Function: A Simple Guide

    The BITAND function in Oracle SQL is a bitwise function. It compares two numbers (or numeric columns) on a bit-by-bit basis and returns a new number as the result of a…

  • Oracle CEIL Function: A Simple Guide

    The CEIL function in Oracle SQL is a mathematical function that "rounds up" a number to the smallest integer that is greater than or equal to that number. The name CEIL…

  • Oracle COS Function: A Simple Guide

    The COS function in Oracle SQL is a trigonometric function that returns the cosine of a given angle. It's important to remember that this function, like all…

  • Oracle COSH Function: A Simple Guide

    The COSH function in Oracle SQL is a mathematical function that returns the hyperbolic cosine of a given number. This is a technical function used in engineering…

  • Oracle EXP Function: A Simple Guide

    The EXP function in Oracle SQL is a mathematical function that returns the value of the constant e (approximately 2.71828183) raised to the power of a given number. This…

  • Oracle FLOOR Function: A Simple Guide

    The FLOOR function in Oracle SQL is a mathematical function that returns the largest integer equal to or less than a given number. In simple terms, it always rounds a…

  • Oracle LN Function: A Simple Guide

    The LN function in Oracle SQL is a mathematical function that returns the natural logarithm of a number. The natural logarithm is the logarithm to the base e, where e is…

  • Oracle LOG Function: A Simple Guide

    The LOG function in Oracle SQL is a mathematical function that returns the logarithm of a number to a specific base. While the LN function is always base e, the LOG…

  • Oracle MOD Function: A Simple Guide

    The MOD function in Oracle SQL is a mathematical function that returns the remainder of one number divided by another. It's also known as the "modulus" function. This…

  • Oracle NANVL Function: A Simple Guide

    The NANVL function in Oracle SQL is a specialized function used to handle NaN (Not a Number) values. NaN is a special marker used in floating-point arithmetic…

  • Oracle POWER Function: A Simple Guide

    The POWER function in Oracle SQL is a straightforward mathematical function that raises one number to the power of another number. This is the standard function to use…

  • Oracle REMAINDER Function: A Simple Guide

    The REMAINDER function in Oracle SQL, like the MOD function, calculates the remainder of a division. However, it uses a different internal formula, which can lead to…

  • Oracle ROUND Function (Number): A Simple Guide

    The ROUND function in Oracle SQL is one of the most common mathematical functions. It is used to round a number to a specified number of decimal places. You can use it…

  • Oracle SIGN Function: A Simple Guide

    The SIGN function in Oracle SQL is a simple mathematical function that tells you the sign of a number. It doesn't return the number itself, but rather an indicator of…

  • Oracle SIN Function: A Simple Guide

    The SIN function in Oracle SQL is a trigonometric function that calculates the sine of a given angle. The most important thing to remember is that the SIN function, like…

  • Oracle SINH Function: A Simple Guide

    The SINH function in Oracle SQL is a mathematical function that returns the hyperbolic sine of a number. This is an exponential function used in engineering, physics…

  • Oracle SQRT Function: A Simple Guide

    The SQRT function in Oracle SQL is a straightforward mathematical function that returns the square root of a given number. It's important to note that the SQRT function…

  • Oracle TAN Function: A Simple Guide

    The TAN function in Oracle SQL is a trigonometric function that calculates the tangent of a given angle. Like all trigonometric functions in Oracle, TAN requires the…

  • Oracle TANH Function: A Simple Guide

    The TANH function in Oracle SQL is a mathematical function that returns the hyperbolic tangent of a number. This is an exponential function, different from the standard…

  • Oracle TRUNC Function: A Simple Guide

    The TRUNC function (for numbers) in Oracle SQL is a mathematical function that cuts off (truncates) a number to a specified number of decimal places. This is different…

  • Oracle WIDTH_BUCKET Function: A Simple Guide

    The WIDTH_BUCKET function in Oracle SQL is a powerful analytical function that lets you group data into "buckets" of equal size. It's most commonly used to build…

Date & Time Functions 25

SYSDATE, ADD_MONTHS, MONTHS_BETWEEN, time zones, and date arithmetic.

  • Oracle ADD_MONTHS Function: A Simple Guide

    Working with dates in SQL can be tricky, especially when you need to add or subtract months. Oracle provides a powerful and straightforward function for this exact task…

  • Oracle CEIL (Date) Function: A Simple Guide

    The CEIL function in Oracle SQL can be used on numbers, but it also has a powerful version for DATE and TIMESTAMP values. In this context, CEIL "rounds up" a datetime…

  • Oracle CURRENT_DATE Function: A Simple Guide

    The CURRENT_DATE function in Oracle SQL is a simple function that returns the current date and time based on your session's time zone. A common point of confusion is its…

  • Oracle CURRENT_TIMESTAMP Function: A Simple Guide

    The CURRENT_TIMESTAMP function in Oracle SQL returns the exact current date, time, and time zone of your SQL session. Its key feature is that it returns a TIMESTAMP WITH…

  • Oracle DBTIMEZONE Function: A Simple Guide

    The DBTIMEZONE function in Oracle SQL is a simple function that returns the time zone setting of the database itself. This is different from SESSIONTIMEZONE, which…

  • Oracle EXTRACT (Date/Time) Function: A Simple Guide

    The EXTRACT function in Oracle SQL is a powerful tool for "pulling out" a single, specific part from a date or timestamp. Want just the YEAR from a DATE? Or the HOUR…

  • Oracle FLOOR (Date) Function: A Simple Guide

    The FLOOR function in Oracle SQL can be used on numbers, but it also has a version for DATE and TIMESTAMP values. For dates, FLOOR "rounds down" the datetime to the unit…

  • Oracle FROM_TZ Function: A Simple Guide

    The FROM_TZ function in Oracle SQL is a straightforward conversion function. Its one and only job is to convert a TIMESTAMP value (which has no time zone information)…

  • Oracle LAST_DAY Function: A Simple Guide

    The LAST_DAY function in Oracle SQL is a very convenient datetime function that returns the date of the last day of the month for a given date. It's an essential tool…

  • Oracle LOCALTIMESTAMP Function: A Simple Guide

    The LOCALTIMESTAMP function in Oracle SQL returns the current date and time (including fractional seconds) based on your session's time zone. The most important thing to…

  • Oracle MONTHS_BETWEEN Function: A Simple Guide

    The MONTHS_BETWEEN function in Oracle SQL calculates the precise number of months between two dates. This function is essential for any business logic that involves…

  • Oracle NEW_TIME Function: A Simple Guide

    The NEW_TIME function in Oracle SQL is a specific-purpose function used to convert a date and time from one time zone to another. Important Note: This is an older Oracle…

  • Oracle NEXT_DAY Function: A Simple Guide

    The NEXT_DAY function in Oracle SQL is a handy date function that helps you find the date of the next specified day of the week. For example, if it's a Wednesday, you…

  • Oracle NUMTODSINTERVAL Function: A Simple Guide

    When working with DATE or TIMESTAMP values in Oracle, you'll often need to add or subtract a specific duration, like "90 days" or "4 hours." While you can add days by…

  • Oracle NUMTOYMINTERVAL Function: A Simple Guide

    When you need to perform date arithmetic in Oracle involving years or months, the NUMTOYMINTERVAL function is the right tool for the job. This function is the companion…

  • Oracle ORA_DST_AFFECTED Function: A Simple Guide

    The ORA_DST_AFFECTED function in Oracle SQL is a highly specialized diagnostic tool used by Database Administrators (DBAs). This function is not used in regular…

  • Oracle ORA_DST_CONVERT Function: A Simple Guide

    The ORA_DST_CONVERT function in Oracle SQL is a highly specialized tool used by Database Administrators (DBAs) during a database time zone file upgrade. This function is…

  • Oracle ORA_DST_ERROR Function: A Simple Guide

    The ORA_DST_ERROR function in Oracle SQL is another highly specialized diagnostic tool used by Database Administrators (DBAs). It is part of the toolkit for managing…

  • Oracle ROUND (Date) Function: A Simple Guide

    The ROUND function in Oracle SQL is not just for numbers; it's also a powerful tool for DATE and TIMESTAMP values. When used with dates, ROUND "rounds" a datetime value…

  • Oracle SESSIONTIMEZONE Function: A Simple Guide

    The SESSIONTIMEZONE function in Oracle SQL is a simple function that returns the time zone of your current SQL session or connection. This is different from DBTIMEZONE…

  • Oracle SYS_EXTRACT_UTC Function: A Simple Guide

    The SYS_EXTRACT_UTC function in Oracle SQL is a simple and powerful tool for standardizing time. Its one and only job is to convert a TIMESTAMP WITH TIME ZONE value into…

  • Oracle SYSDATE Function: A Simple Guide

    The SYSDATE function is one of the most popular and frequently used functions in Oracle SQL. Its purpose is simple: it returns a single DATE value representing the…

  • Oracle SYSTIMESTAMP Function: A Simple Guide

    The SYSTIMESTAMP function in Oracle SQL is the high-precision version of SYSDATE. It returns the exact current date, time (including fractional seconds), and time zone…

  • Oracle TRUNC (Date) Function: A Simple Guide

    The TRUNC function (for dates) in Oracle SQL is one of the most important and frequently used date functions. It "truncates" or "cuts off" a DATE or TIMESTAMP value down…

  • Oracle TZ_OFFSET Function: A Simple Guide

    The TZ_OFFSET function in Oracle SQL is a simple utility that returns the current time zone offset from UTC for a given time zone name. For example, it can tell you that…

Conversion Functions 40

TO_CHAR, TO_DATE, TO_NUMBER, CAST, and every conversion function.

NULL, Comparison & Other Functions 15

NVL, COALESCE, DECODE, GREATEST, hashing, and LOB functions.

  • Oracle BFILENAME Function: A Simple Guide

    The BFILENAME function in Oracle SQL is a special function used to work with external files. Its job is to create a BFILE locator, which is a pointer to a physical file…

  • Oracle COALESCE Function: A Simple Guide

    The COALESCE function in Oracle SQL is a powerful and flexible function used to handle NULL values. Its job is to return the first non-NULL expression it finds in a list…

  • Oracle DECODE Function: A Simple Guide

    The DECODE function in Oracle SQL is a powerful and concise way to write conditional logic (like an IF...THEN...ELSE statement) directly within your SQL query. It…

  • Oracle DUMP Function: A Simple Guide

    The DUMP function in Oracle SQL is a powerful diagnostic tool. It's not used for normal reporting but is essential for debugging and internal analysis. Its purpose is to…

  • Oracle EMPTY_BLOB Function: A Simple Guide

    The EMPTY_BLOB() function in Oracle SQL is a special function used to create an "empty" BLOB (Binary Large Object). This is a crucial first step when you want to add…

  • Oracle EMPTY_CLOB Function: A Simple Guide

    The EMPTY_CLOB() function in Oracle SQL is a special function used to create an "empty" CLOB (Character Large Object). This is a crucial first step when you want to add…

  • Oracle GREATEST Function: A Simple Guide

    The GREATEST function in Oracle SQL is a simple utility that looks at a list of values you provide and returns the one that is the largest. It's an incredibly useful…

  • Oracle LEAST Function: A Simple Guide

    The LEAST function in Oracle SQL is the direct opposite of the GREATEST function. It looks at a list of values you provide and returns the one that is the smallest. It's…

  • Oracle LNNVL Function: A Simple Guide

    The LNNVL function in Oracle SQL is a special logical function. Its name stands for "Logical Not NULL-Value Logic," and it's used to simplify conditions that involve…

  • Oracle NULLIF Function: A Simple Guide

    The NULLIF function in Oracle SQL is a simple but powerful control-flow function. It compares two expressions. If they are equal, it returns NULL. If they are not equal…

  • Oracle NVL Function: A Simple Guide

    The NVL function in Oracle SQL is one of the most common and essential functions for handling NULL values. A NULL value in a database means "unknown" or "empty," and it…

  • Oracle NVL2 Function: A Simple Guide

    The NVL2 function in Oracle SQL is a powerful "if-then-else" function for handling NULL values. It's an extension of the basic NVL function. NVL2 checks an expression.…

  • Oracle ORA_HASH Function: A Simple Guide

    The ORA_HASH function in Oracle SQL is a powerful function used to compute a consistent "hash value" (a number) for any given expression. Its main purpose is to help you…

  • Oracle STANDARD_HASH Function: A Simple Guide

    The STANDARD_HASH function in Oracle SQL is a powerful security function. It computes a "hash value" (also known as a "fingerprint" or "checksum") for a given…

  • Oracle VSIZE Function: A Simple Guide

    The VSIZE function in Oracle SQL is a simple utility function that tells you the number of bytes used to store a specific expression. This is different from the LENGTH…

Interview Prep & Books 3

SQL interview questions, tricky queries, and the SQL and PL/SQL book.