By Vinish Kapoor

Oracle SQL Tutorials

Every Oracle SQL tutorial on vinish.dev: queries and joins, grouping and analytic functions, tables and indexes, JSON and XML, and 210+ SQL function guides, organized so you can find the one you need in seconds.

  • 398 tutorials
  • 23 topics
  • 4 learning paths
  • Updated October 2026

Browse by topic

Learning paths

Ordered series you can follow from the first lesson to the last. Your progress is saved in this browser.

BeginnerSQL Essentials15 lessons IntermediateBeyond the Basics18 lessons BeginnerMost-Used SQL Functions19 lessons PracticalSQL Query Recipes12 lessons

Latest tutorials

Books & free tools

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

All tutorials by topic

SQL Basics & DML 20

Create tables, query with SELECT and WHERE, sort and group, and change data with INSERT, UPDATE, DELETE, and MERGE.

  • 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…

  • How NULL Works in Oracle Conditions

    Learn why comparisons with NULL are unknown, how that drops rows from results, and how to avoid the NOT IN trap and other NULL surprises.

    New

  • How to Use Pseudocolumns in Oracle

    Learn what ROWNUM, ROWID, ORA_ROWSCN, and COLUMN_VALUE return, why ROWNUM is assigned before ORDER BY, and when to use FETCH instead.

    New

  • Oracle Boolean Literals and Expressions in SQL

    Store TRUE and FALSE in BOOLEAN columns, select conditions as values, and group, sort, and test BOOLEAN data safely with IS TRUE.

    New

  • How to Use Multitable INSERT ALL in Oracle

    Route the rows of one query into several tables in a single statement with INSERT ALL and INSERT FIRST, with conditions or without.

    New

  • How to Use Direct-Path INSERT in Oracle

    Speed up bulk loads and archiving with direct-path INSERT and the APPEND hint, and confirm the load path with LOAD AS SELECT in the plan.

    New

  • How to Log DML Errors in Oracle

    Keep a bulk INSERT or UPDATE going when some rows fail: log the bad rows to an error table with LOG ERRORS and load the rest.

    New

  • How to Use RETURNING OLD in Oracle DML

    Get the values before and after an update in the same statement with RETURNING OLD and NEW, for a single row or many with BULK COLLECT.

    New

SQL Developer, SQLcl & SQL*Plus 6

Set up and use Oracle's SQL tools: SQL Developer, SQLcl formatting and aliases, and SQL*Plus reports.

Fixing SQL Errors (ORA-) 41

What the common ORA- errors in SQL mean and how to fix them, from ORA-00001 to ORA-30926, with tested examples.

Grouping & Aggregation 6

HAVING, ROLLUP, CUBE, GROUPING SETS, the FILTER clause, and KEEP FIRST/LAST.

Subqueries, CTEs & Hierarchies 9

Subqueries, EXISTS, set operators, the WITH clause, recursive queries, and CONNECT BY.

Joins & Query Recipes 14

Joins and analytics recipes: running totals, cohorts, gaps and islands, and business days.

Analytic (Window) Functions 12

Window frames, QUALIFY, ROW_NUMBER, RANK, NTILE, FIRST_VALUE, and the other analytic functions.

  • How to Define Window Frames in Oracle Analytic Queries

    Control which rows an analytic function sees with PARTITION BY and ROWS, RANGE, or GROUPS frames, and reuse windows with the WINDOW clause.

    New

  • How to Filter Analytic Results with QUALIFY in Oracle

    Filter rows on ROW_NUMBER, RANK, and other analytic results in one query block with QUALIFY, new in Oracle AI Database 26ai.

    New

  • Oracle CUME_DIST Function

    Find what fraction of each group falls at or below every row with CUME_DIST, and select the top or bottom percent of the rows.

    New

  • Oracle DENSE_RANK Function

    Rank rows without gaps after ties with DENSE_RANK, and use it to find the second- or Nth-highest distinct value in each group.

    New

  • Oracle FIRST_VALUE Function

    Show the value from the first row of each group on every row with FIRST_VALUE, and compare each row with its group's starting value.

    New

  • Oracle LAST_VALUE Function

    Get the value from the last row of each group with LAST_VALUE, and avoid the default frame that makes it return the current row instead.

    New

  • Oracle NTH_VALUE Function

    Get the value from any position in a window, such as the second hire or the second most recent row, counting from the first or the last.

    New

  • Oracle NTILE Function

    Divide ordered rows into quartiles, deciles, or any number of equal-sized buckets with NTILE, and describe the range of each bucket.

    New

  • Oracle PERCENT_RANK Function

    Express each row's position as a fraction from 0 to 1 within its group, and compare relative standing across groups of any size.

    New

  • Oracle RANK Function

    Rank rows like places in a competition, where ties share a place and the next place skips, overall or within each group.

    New

  • Oracle RATIO_TO_REPORT Function

    Compute each value's share of the total, such as revenue by payment method, overall or within groups, in one analytic function.

    New

  • Oracle ROW_NUMBER Function

    Number the rows of each group with ROW_NUMBER for top-N-per-group queries and deduplication, and make the numbering repeatable.

    New

PIVOT, MATCH_RECOGNIZE & Advanced SQL 8

PIVOT and UNPIVOT, pattern matching, the MODEL clause, property graphs, and JavaScript in SQL.

Tables, Constraints & Partitioning 20

Identity and virtual columns, constraints, domains, special table types, and partitioning.

Indexes, Views & Materialized Views 11

Composite, function-based and bitmap indexes, views, and materialized views with refresh and rewrite.

Performance & Execution Plans 14

Read execution plans, access paths and join methods, find out why an index is not used, and find slow SQL with V$SQL.

Transactions, Locking & Flashback 6

Savepoints, isolation levels, row locking, deadlocks, lock-free reservations, and Flashback Query.

Users, Privileges & Data Dictionary 8

Create users, grant privileges and roles, explore the data dictionary and V$ views, and use database links.

JSON in SQL 17

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_EQUAL Function

    Compare JSON documents by their content rather than their text, and learn which differences JSON_EQUAL ignores and which it reports.

    New

  • Oracle JSON_EXISTS Function

    Check whether a JSON document has a field or matches a filter, and pass values into path filters as variables instead of literals.

    New

  • Oracle JSON_MERGEPATCH Function

    Replace, add, and delete fields of a JSON document with a merge patch, and learn why arrays are replaced whole and nulls mean delete.

    New

  • 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…

XML Functions 11

Generate, query, and transform XML in SQL with XMLELEMENT, XMLAGG, XMLTABLE, and the other XML functions.

  • Oracle SYS_XMLGEN Function

    Turn values and objects into XML documents with Oracle's SYS_XMLGEN, rename them with XMLFormat, and aggregate them with SYS_XMLAGG.

    New

  • Oracle XMLAGG Function

    Aggregate the rows of each group into ordered XML child elements, and nest XMLAGG calls to build a complete document in one query.

    New

  • Oracle XMLCAST Function

    Convert XML query results into SQL numbers, dates, and strings with XMLCAST, and avoid the trap of paths that match several nodes.

    New

  • Oracle XMLCONCAT Function

    Join XML values into one fragment with XMLCONCAT, and create comments, processing instructions, and CDATA sections in SQL.

    New

  • Oracle XMLDIFF Function

    Compare two XML documents, list the changed nodes as xdiff operations, and rebuild the new version from the old one with XMLPATCH.

    New

  • Oracle XMLELEMENT Function

    Turn table rows into well-formed XML elements with XMLELEMENT, add attributes with XMLATTRIBUTES, and create element runs with XMLFOREST.

    New

  • Oracle XMLEXISTS Condition

    Test whether an XML document contains an element or value, and filter rows in WHERE by the content of their XML with bound variables.

    New

  • Oracle XMLQUERY Function

    Run XQuery paths, functions, and FLWOR expressions against XML inside SQL with XMLQUERY, and turn results into values or text.

    New

  • Oracle XMLSERIALIZE Function

    Convert XML to indented or compact text of the type you need with XMLSERIALIZE, and parse text into XMLType documents or fragments.

    New

  • Oracle XMLTABLE Function

    Shred XML documents into relational rows and typed columns with XMLTABLE, including attributes, row numbers, and namespaced XML.

    New

  • Oracle XMLTRANSFORM Function

    Apply XSLT stylesheets to XML inside the database with XMLTRANSFORM, turning table data into HTML, text, or another XML layout.

    New

String Functions 29

SUBSTR, INSTR, REPLACE, REGEXP, fuzzy matching, collation, and every other character function.

  • A Simple Guide to the CHR Function in Oracle

    If you've ever needed to use a special character in Oracle SQL, like a line break or a copyright symbol (©), the CHR function is the tool you need. This guide will…

  • How to Get Substring from a String in Oracle SQL?

    In Oracle SQL, you get a substring from a string using the SUBSTR function. The SUBSTR function is one of the most versatile and commonly used string manipulation…

  • Oracle ASCII Function: A Simple Guide

    The ASCII function in Oracle SQL is the reverse of the CHR function. It takes a character string and returns the numeric code (like ASCII, EBCDIC, etc.) for its very…

  • Oracle Character Set Functions

    Convert between character set names and numbers, find the database's character sets, and size NCHAR columns with NLS_CHARSET_DECL_LEN.

    New

  • Oracle Collation Functions

    See which collation applies to an expression, convert collation names and numbers, and compare text without regard to case or accents.

    New

  • Oracle CONCAT Function: A Simple Guide to Joining Strings

    When working with data in Oracle SQL, you'll often need to combine or "join" strings together. The CONCAT function is a built-in tool specifically for this purpose. This…

  • Oracle FUZZY_MATCH Function

    Find misspelled and similar names in SQL by scoring string similarity with Levenshtein, Jaro-Winkler, bigram, and other algorithms.

    New

  • Oracle INITCAP Function: A Simple Guide to Capitalizing Words

    When you have text data in your Oracle database that is messy—like all lowercase or all uppercase—it can look unprofessional in reports. The INITCAP function is a simple…

  • Oracle INSTR Function: A Simple Guide to Finding Substrings

    Ever needed to find the exact position of a word or letter within a string in Oracle SQL? The INSTR function is the perfect tool for the job. This simple guide will…

  • Oracle LENGTH Function: A Simple Guide

    The LENGTH function in Oracle SQL is one of the most fundamental string functions. It simply tells you the number of characters in a given string. It's an essential…

  • Oracle LOWER Function: A Simple Guide to Converting Text

    When you're working with text data in an Oracle database, you'll often find that the capitalization is inconsistent. You might have 'Admin', 'admin', and 'ADMIN' all in…

  • Oracle LPAD Function: A Simple Guide to Left-Padding Strings

    Have you ever needed to format your query results so they line up perfectly? For example, making all your ID numbers a fixed length (like 001, 023, 100)? The Oracle LPAD…

  • Oracle LTRIM Function: A Simple Guide to Trimming Characters

    When working with data, you'll often find strings with unwanted characters at the beginning, especially leading spaces. The Oracle LTRIM function is a simple and…

  • Oracle NLSSORT Function

    Sort and compare strings by linguistic rules instead of byte values, ignoring case with BINARY_CI and accents with BINARY_AI.

    New

  • Oracle PHONIC_ENCODE Function

    Find names that sound alike despite different spellings by comparing Double Metaphone codes, with primary and alternate pronunciations.

    New

  • Oracle REGEXP_COUNT Function

    Count how many times a pattern occurs in a string, from words and search terms to digits and groups, with case-insensitive options.

    New

  • Oracle REGEXP_INSTR Function

    Find the position of a regular expression match in a string, choose which match, get the position after it, and locate a single group.

    New

  • Oracle REGEXP_LIKE Function

    Match strings against regular expressions in WHERE, CASE, and CHECK constraints, and avoid the bracket mistake with \d in Oracle patterns.

    New

  • Oracle REGEXP_REPLACE Function: A Simple Guide

    When you need to find and replace text in Oracle, you might think of the REPLACE function. But what if you need to find a pattern, not just a fixed string? For this…

  • Oracle REGEXP_SUBSTR Function: A Simple Guide

    While REGEXP_REPLACE is for changing text, what if you just want to extract text that matches a pattern? For this, Oracle provides the powerful REGEXP_SUBSTR function.…

  • Oracle REPLACE Function: A Simple Guide

    When you need to do a simple "find and replace" on a string, the Oracle REPLACE function is the perfect tool for the job. Unlike REGEXP_REPLACE, which searches for…

  • Oracle RPAD Function: A Simple Guide

    The RPAD function in Oracle is the mirror image of LPAD. It "Right Pads" a string, adding characters to the end of it until it reaches a specified total length. This…

  • Oracle RTRIM Function: A Simple Guide

    The RTRIM function in Oracle is a powerful tool for cleaning data. It "Right Trims" a string, meaning it removes all specified characters from the end (or right side) of…

  • Oracle SOUNDEX Function: A Simple Guide

    The SOUNDEX function in Oracle is a special phonetic tool that helps you find words that "sound alike" in English, even if they are spelled differently. It converts a…

  • Oracle SUBSTR Function: A Simple Guide

    The SUBSTR function (short for "Substring") is one of the most powerful and commonly used string functions in Oracle SQL. It allows you to extract or "cut out" a…

  • Oracle TRANSLATE Function: A Simple Guide

    The TRANSLATE function in Oracle SQL is a powerful tool for performing multiple, single-character substitutions all at once. It works like a find-and-replace for…

  • Oracle TRANSLATE USING Function: A Simple Guide

    The TRANSLATE USING function in Oracle SQL is a specialized function used to convert a string from one character set to another. Specifically, it converts a string to…

  • Oracle TRIM Function: A Simple Guide

    The TRIM function in Oracle SQL is a flexible and powerful tool for cleaning strings. It allows you to remove unwanted characters from the beginning (leading), the end…

  • Oracle UPPER Function: A Simple Guide

    The UPPER function in Oracle SQL is a straightforward and widely used function that converts all letters in a string to uppercase. It's an essential tool for…

Numeric Functions 27

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 ROUND_TIES_TO_EVEN Function

    Round exact halves to the nearest even number with banker's rounding, and keep totals of many rounded values from drifting upward.

    New

  • 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 35

SYSDATE, ADD_MONTHS, DATEDIFF, TIME_BUCKET, calendars, time zones, and format models.

  • How to Handle Daylight Saving Time Gaps in Oracle

    Avoid ORA-01878 in the spring gap, resolve the repeated autumn hour with TZD, and keep timestamp arithmetic correct across clock changes.

    New

  • 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 CALENDAR_ Functions

    Name the quarter, month, or week of a date, find the first and last day of any period, and move dates by whole periods in one call.

    New

  • Oracle CALENDAR_SINCE Function

    Show dates the way user interfaces do, such as 3 days ago, 6 weeks ago, or in 2 hours, directly in SQL with a long or short form.

    New

  • 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 DATEADD Function

    Add any unit from years to nanoseconds to a date or timestamp with DATEADD, and see how it differs from ADD_MONTHS at month ends.

    New

  • Oracle DATEDIFF Function

    Count the days, weeks, months, or years between two dates in one function, and understand why DATEDIFF counts boundaries, not whole units.

    New

  • Oracle Datetime Format Models Explained

    Format and read dates with the right elements, use FM, FX, TH, and SP, understand RR versus YY, and avoid session format surprises.

    New

  • 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 FISCAL_ Functions

    Compute fiscal years, quarters, and period start and end dates for a year that starts in April, October, or any day you choose.

    New

  • 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 RETAIL_ Functions

    Report by the 4-5-4 retail calendar of whole weeks, with retail month and week names and their start and end dates, without a table.

    New

  • 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 TIME_BUCKET Function

    Group events into periods of any length, from fifteen-minute slots to six-hour blocks, by placing each timestamp in a fixed-size bucket.

    New

  • Oracle TIMESTAMPDIFF Function

    Get the hours, minutes, seconds, or milliseconds between two timestamps as a number, correctly across time zones and clock changes.

    New

  • 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 43

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

Aggregate & Statistical Functions 24

COUNT, SUM, AVG, MEDIAN, percentiles, statistics, and approximate aggregates.

  • Oracle ANY_VALUE Function

    Select a column that has the same value in every row of a group without adding it to GROUP BY or wrapping it in MAX, and avoid ORA-00979.

    New

  • Oracle APPROX_COUNT Function

    Answer top-N questions such as the busiest routes or best-selling cabins quickly with APPROX_COUNT, APPROX_SUM, and APPROX_RANK.

    New

  • Oracle APPROX_COUNT_DISTINCT Function

    Estimate the number of distinct values in huge tables quickly, and store mergeable sketches so distinct counts add up correctly.

    New

  • Oracle APPROX_MEDIAN Function

    Estimate medians and percentiles of very large data quickly, make the result repeatable with DETERMINISTIC, and check its accuracy.

    New

  • Oracle AVG Function

    Average the values of each group with AVG, understand how NULLs change the divisor, and average intervals in Oracle AI Database 26ai.

    New

  • Oracle BIT_AND_AGG Function

    Combine flag integers bit by bit over a group to find options common to all rows or available in any, and decode them with BITAND.

    New

  • Oracle BITMAP_ Functions for Exact Distinct Counts

    Count distinct integers exactly with bitmaps that can be stored per group and merged, so distinct counts over any period stay correct.

    New

  • Oracle BOOLEAN_AND_AGG Function

    Check whether all or any rows of a group are true, such as all passengers checked in, with BOOLEAN_AND_AGG, BOOLEAN_OR_AGG, and EVERY.

    New

  • Oracle CHECKSUM Function

    Detect whether data changed or differs between databases by comparing order-independent checksums of column values over groups.

    New

  • Oracle CORR Function

    Measure how closely two columns move together with Pearson, Spearman, and Kendall correlation, and why per-group results can differ.

    New

  • Oracle COUNT Function

    Learn the difference between COUNT(*), COUNT(column), and COUNT(DISTINCT), and how to count matches correctly after an outer join.

    New

  • Oracle COVAR_POP Function

    Measure whether two columns rise and fall together with COVAR_POP and COVAR_SAMP, and see how units change covariance but not correlation.

    New

  • Oracle MAX Function

    Find the largest value, latest date, or worst delay in each group with MAX, and learn when GREATEST is the function you need.

    New

  • Oracle MEDIAN Function

    Find the middle value of each group with MEDIAN, and see why it describes skewed data such as salaries better than the average.

    New

  • Oracle MIN Function

    Find the smallest number, the earliest date, or the first name alphabetically in each group with MIN, and get other columns of that row.

    New

  • Oracle PERCENTILE_CONT Function

    Compute medians, quartiles, and 90th percentiles of each group with PERCENTILE_CONT, which interpolates between the nearest values.

    New

  • Oracle PERCENTILE_DISC Function

    Get percentiles that are actual values from the data with PERCENTILE_DISC, and see how they differ from interpolated PERCENTILE_CONT.

    New

  • Oracle REGR_ Functions

    Fit a least-squares line through pairs of values with REGR_SLOPE and REGR_INTERCEPT, measure the fit with REGR_R2, and predict values.

    New

  • Oracle SKEWNESS Function

    Describe the shape of your data beyond mean and spread: measure lopsidedness with SKEWNESS and the weight of the tails with KURTOSIS.

    New

  • Oracle STATS_ Test Functions

    Test whether differences between groups are real with t-tests, ANOVA, and chi-squared tests run directly in SQL with the STATS_ functions.

    New

  • Oracle STATS_MODE Function

    Find the most frequent value in each group, such as the usual base airport or cabin, with STATS_MODE, and know how it handles ties.

    New

  • Oracle STDDEV Function

    Measure how spread out the values of each group are with STDDEV, and choose between the population and sample versions correctly.

    New

  • Oracle SUM Function

    Add up values per group with SUM, total intervals of time in Oracle AI Database 26ai, and handle NULLs and empty groups correctly.

    New

  • Oracle VARIANCE Function

    Measure the spread of each group as the mean squared deviation with VARIANCE, and choose between the population and sample versions.

    New

NULL, Comparison & Other Functions 25

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

  • How to Use Multiset Operators in Oracle

    Unite, intersect, and subtract nested tables like sets, and test membership and subsets, in PL/SQL and SQL without loops.

    New

  • 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 CARDINALITY Function

    Count the elements of a nested table collection in SQL, and see how empty collections, NULL collections, and NULL elements behave.

    New

  • 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 COLLECT Function

    Gather the values of each group into a nested table collection with COLLECT and CAST, and avoid the ORA-00932 element type mismatch.

    New

  • Oracle CON_ Functions

    Translate between container names, container IDs, DBIDs, UIDs, and GUIDs in a container database with the eight CON_ functions.

    New

  • 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 DOMAIN_ Functions

    Format, sort, and validate values by the rules of a data use case domain with DOMAIN_DISPLAY, DOMAIN_ORDER, and DOMAIN_CHECK.

    New

  • 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 POWERMULTISET Function

    List every subset of a collection, or only the pairs or triples, with POWERMULTISET and POWERMULTISET_BY_CARDINALITY.

    New

  • Oracle SET Function

    Remove duplicate elements from a nested table collection, the collection version of DISTINCT, and use it after MULTISET UNION.

    New

  • 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 SYS_CONTEXT Function

    Find out who is logged in, where they connect from, and which module is running, with SYS_CONTEXT and the built-in USERENV context.

    New

  • Oracle SYS_GUID Function

    Generate globally unique 16-byte identifiers for keys, use SYS_GUID as a column default, and see when a random UUID is the better choice.

    New

  • Oracle UUID Functions

    Generate random UUIDs, convert between RAW and the hyphenated text form, validate strings, and store UUIDs efficiently as RAW(16).

    New

  • 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…

Machine Learning in SQL 9

Score models in SQL: predictions, probabilities, anomaly detection, feature extraction, and evaluation.

Interview Prep & Books 3

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