SQL

Use connect by clause to get all the dates in a month in Oracle. Below are the examples: Example-1: The following SQL query uses the trunc() function to get the numeric month value with connect by clause to get all the dates in a month: SELECT TRUNC(SYSDATE, 'MM') + LEVEL - 1 AS day FROM ... Read more

Use add_months() function to get the next month from the current date in Oracle. Below are the examples: Get Next Month's Date in Oracle To simply get the next month date from the current date using the following SQL query: Select add_months(sysdate, 1) from dual; Output: Today the date is 6th November 2022, so the ... Read more

You can use the greatest() function in Oracle to find the maximum value in the list. Below are its syntax and examples: Syntax greatest(value1, value2, value3, ...) Example-1: The following SQL query will get the maximum value from the comma-separated list: select greatest(5, 29, 99, 89) grt from dual; Output: 99 Example-2: Get the maximum ... Read more

In Oracle, you can use the cast function to convert the date to the local time zone. Below are the examples: Date to Local Time Zone Example The following SQL query converts the date to local time zone format using the cast function: SELECT CAST('21-OCT-2022' AS TIMESTAMP WITH LOCAL TIME ZONE) FROM DUAL; Output 21-OCT-22 ... Read more

In Oracle, any_value is a function that you can use to return any value from a table on which you are aggregating a column value. For example, you are summing up the salary on the department number for the EMP table and you want to display one of the employee names from that department. Then, ... Read more

Use the lead() function to get the next row value in Oracle. Below are its syntax and example: Lead Function Syntax lead(expr, offset, default_value) over (partition_by_clause order_by_clause) For the expr, specify the column name, for offset, specify the row number, and for the default_value, specify the value to show the default value for the last ... Read more

In Oracle, use the lag() analytical function to get the previous row value. Below are some examples: Lag Syntax lag(expr, offset, defaut_value) over (partition_clause order_by_clause) For the expr, you will provide the column name, the offset is the previous row number from which to get the value and the default_value is for the current row ... Read more

To get the remainder in Oracle, you can use the mod() or remainder() functions. Below are the syntax and examples for the same: Mod Function Syntax mod(n2, n1) MOD gives you the remainder when dividing n2 by n1. If n1 is zero, it gives back the value for n2. Get Remainder in Oracle Using Mod ... Read more

To convert a string to uppercase, use the upper() function in Oracle. Below are the syntax and examples: UPPER Function Syntax upper(char) UPPER Function Examples The following SQL query converts a string to uppercase using the upper() function: select upper('oracle') from dual; Output: ORACLE Using UPPER Function in PL/SQL Program Below PL/SQL code demonstrates, how ... Read more

To substring a string you can use the substr function in Oracle. The SUBSTR functions return a substring of string, starting at character position and lasting substring length characters. The following are the substr function syntax and some examples: Substr Function Syntax substr(char, position, substring_length); The SUBSTR functions return a substring of char, starting at ... Read more