The RANK() function is an analytic function in Oracle SQL used to assign a rank to each row within a partition of a result set, based on the ordering of a specified column(s). It is commonly used for ranking results, like top performers, scores, or sales. Syntax Components: Key Characteristics Basic Example Assume a table…

The NVL() function in Oracle is used to replace NULL values with a specified default value. It is widely used for handling missing data, ensuring calculations don’t fail, and improving query readability. Syntax Behavior: Note: Both expr1 and expr2 must be of compatible data types. Basic Example Output: RESULT Hello Explanation: Since the first argument…

The COALESCE() function in Oracle is used to return the first non-null value from a list of expressions. It’s a powerful function often used in data cleaning, conditional logic, and reporting. Syntax Basic Example Output: RESULT Hello Explanation: The first two values are NULL, so it returns ‘Hello’ (the first non-null value). When to Use…

Problem Statement You are given a table named Weather with the following structure: You are asked to write a query that returns the IDs of the days where the temperature is higher than the previous day’s temperature. Sample Data Expected Output We want a list of all IDs where the temperature is greater than the…

When working with Oracle SQL, we often need to retrieve the Nth highest salary (like 2nd, 3rd, etc.). While analytic functions like RANK() or DENSE_RANK() are commonly used for this, some scenarios or database versions require solutions without analytic functions. This tutorial shows how to find the 3rd highest salary using only nested subqueries. Step…

Assume we have a table: Method 1: Using ORDER BY with OFFSET … FETCH (Oracle 12c+) Explanation: Method 2: Using DISTINCT with ORDER BY + ROWNUM (Oracle 11g and below) Explanation: To get the full employee details: Method 3: Using ROW_NUMBER() (Oracle 10g+) Explanation: Method 4: Using DENSE_RANK() (Handles Ties) Explanation: Sample Output (Using DENSE_RANK):…

What Does “Last 5 Records” Mean in SQL? Unlike Excel, SQL tables have no inherent order. So when we say “last 5 records”, we must define it based on a column — for example: Sample Table: employees Methods to Read Last 5 Records Method 1: Using ORDER BY with FETCH (Oracle 12c+) Get Last 5…

In Oracle, unlike databases like MySQL or PostgreSQL that support LIMIT, selecting the Top N rows requires different techniques depending on your Oracle version (especially before and after 12c). This tutorial focuses on Oracle SQL, especially how to retrieve the Top 5 rows from a table. 1. Using ROWNUM (For Oracle 11g and earlier) ROWNUM…

Duplicate records are common in data due to data entry errors, improper joins, or integration issues. Oracle provides multiple SQL techniques to detect and remove duplicates efficiently. 1. What is a Duplicate Row? In SQL, a row is considered duplicate if two or more rows have the same values in one or more columns, and…

In SQL, aggregate functions are built-in functions used to perform calculations on a set of rows and return a single summarized value. These functions are widely used in reporting, analytics, data warehousing, and business intelligence applications. Oracle SQL supports a rich set of aggregate functions that help in summarizing data across multiple rows. In this…