Webucator's Free Advanced Oracle SQL Queries Tutorial

# Lesson: Scalar SQL Functions (Non-Character)

Welcome to our free Advanced Oracle SQL Queries tutorial. This tutorial is based on Webucator's Advanced Oracle SQL Queries course.

In this lesson, you will learn about non-character SQL functions.

Lesson Goals

• Learn about null value functions.
• Learn about date format functions.
• Learn about date arithmetic functions.

## Numeric Functions

Many mathematical operations and functions, from simple arithmetic to advanced statistical functions, are available in Oracle. Numerical formatting is avaiable through the `TO_CHAR()` function and rounding and trunctation to prescribed degrees of precision are supported as well.

Addition, subtraction, multiplication and division are available using standard SQL operators (+,-,*,/). The `MOD()` function can be used to calculate the modulous (or remainder) that results when one number is divided by another.

## Code Sample:

SQL-Functions-Non-Character/Demos/arithmetic.sql
```--Arithmetic Operations (+,-,/,*) and MOD function

SELECT
first_name,
last_name,
salary,
salary + 1000 AS salary_plus_1000
FROM employees
WHERE last_name LIKE 'K%';

SELECT
first_name,
last_name,
salary,
salary - 1000 AS salary_minus_1000
FROM employees
WHERE last_name LIKE 'K%';

SELECT
first_name,
last_name,
salary,
salary * 2 AS salary_doubled
FROM employees
WHERE last_name LIKE 'K%';

SELECT
first_name,
last_name,
salary,
(salary / 6) AS salary_divided_by_six,
MOD(salary,6) AS the_remainder
FROM employees
WHERE last_name LIKE 'K%';```

The last query's results are shown below. Notice that the division operation results in some unwieldy results that are often rounded or truncated in practice:

### ROUND()

This can be cleaned up using the `ROUND()` function as shown below:

## Code Sample:

SQL-Functions-Non-Character/Demos/round.sql
```SELECT
first_name,
last_name,
(salary / 6),
ROUND(salary / 6),
ROUND(salary / 6, 2)
FROM employees
WHERE last_name LIKE 'K%';```

The results of this query are shown below:

The `ROUND()` function has several options available. A second parameter can be used to specify precision.

### TRUNC()

The `TRUNC()` function cuts off characters from the end of a decimal number, so for a positive number, it rounds down and for a negative number it rounds up. Like `ROUND()`, `TRUNC()` allows for a second parameter to specify precision.

## Code Sample:

SQL-Functions-Non-Character/Demos/trunc.sql
```SELECT
first_name,
last_name,
(salary / 6),
TRUNC(salary / 6),
TRUNC(salary / 6, 2)
FROM employees
WHERE last_name LIKE 'K%';```

The results of this query are shown below:

### The TO_CHAR() Function with Numbers

There are variations of the `TO_CHAR()` function that work with dates, numbers and other characters. The options available depend upon the data type being passed to the function.

There are a wide variety of format models that can be specified. The format model is a series of characters that describe the intended format for a number. This series of characters is interpreted by the `TO_CHAR()` function and used to return a formatted string version of the number.

Some of the most common characters to use in format models include commas, decimal points, dollar signs, 0 (a numeric placeholder that includes leading zeroes) and 9 (a numeric placeholder that does not include leading zeroes).

## Code Sample:

SQL-Functions-Non-Character/Demos/numeric_to_char.sql
```SELECT
first_name,
last_name,
commission_pct,
TO_CHAR(commission_pct, '0.99'),
TO_CHAR(commission_pct * 100,'99.00')
FROM employees
WHERE last_name like 'K%';```

This query demonstrates two of the many format models available. It will output the following:

## Date/Time Functions

The SYSDATE function returns the current system date and time. Oracle includes a variety of globalization options to support various regions and time zones. The SESSIONTIMEZONE function can be used to return the time zone for the current session. It either returns a time zone offset (a character type in the format '[+|]TZH:TZM') or a time zone region name. See Oracle's Globalization Support documentation for details.

## Date/Time and Date Format Functions

### Date/Time Functions

• The `SYSDATE()` function returns the current system date and time. Oracle includes a variety of globalization options to support various regions and time zones.
• The `SESSIONTIMEZONE()` function can be used to return the time zone for the current session. It either returns a time zone offset (a character type in the format `'[+|]TZH:TZM'`) or a time zone region name.

### Date Format Functions

The `TO_CHAR()` functions is also available for dates and can support various date format models. Note that once a date has been converted to a string of characters, it will be sorted as a string in an `ORDER BY` clause. For example, the string '4-Jul-1776' would come after the string '20-Jul-1969', because '4' is greater than '2'. But as dates, `4-Jul-1776` would come before `20-Jul-1969` (which makes sense as the moon landing came after U.S. independence).

Some of the most common characters used to specify date format models include:

• `MM` - two digit month
• `DD` - two digit day
• `YYYY` - four digit year
• `HH24` - hour using a 24 hour clock
• `MI` - minute
• `SS` - second

*Be careful not to confuse MM (month) and MI (minute).

The following query demonstrates a formatting of a date using the hire date in the employee table, which does not have interesting hour, minute and second information, and the formatting of `SYSDATE()`, which does.

## Code Sample:

SQL-Functions-Non-Character/Demos/date_to_char.sql
```SELECT
first_name,
last_name,
hire_date,
TO_CHAR(hire_date, 'MM/DD/YYYY HH24:MI:SS'),
TO_CHAR(sysdate, 'MM/DD/YYYY HH24:MI:SS')
FROM employees
WHERE last_name LIKE 'K%';```

It will output the following:

## Date Arithmetic Functions

Dates can be modified by using the `+` and `-` operators. Several functions can be used to read and manipulate dates as well.

• The `MONTHS_BETWEEN()` function returns a number representing the number of months between two dates.
• The `NEXT_DAY()` returns the first day after the date in the first argument that is the day of the week specified in the second argument.
• The `ADD_MONTHS()` function returns the date specified in the first argument incremented by the number of months specified in the second argument.
• Dates can be rounded or truncated using `ROUND()` and `TRUNC()`.
• There are also various functions relate to timestamps.

The query below shows several ways to return a date based on the employee hire date (`+` operation, `NEXT_DAY()` and `ADD_MONTHS()` functions):

## Code Sample:

SQL-Functions-Non-Character/Demos/date_arithmetic.sql
```SELECT
first_name,
last_name,
hire_date,
hire_date + 1,
next_day(hire_date,'TUESDAY'),
FROM employees
WHERE last_name LIKE 'K%';```

## Null Value Functions

The `NVL()` function is used to replace a null with a string. The `NVL2()` function returns one value if a given expression is null and a different value if the expression not null. The value returned needs to be of the same type regardless of whether the expression is null or not null.

## Code Sample:

SQL-Functions-Non-Character/Demos/nvl_and_nvl2.sql
```SELECT
first_name,
last_name,
salary,
NVL(commission_pct,0)
FROM employees
WHERE last_name like 'K%';

SELECT
first_name,
last_name,
salary,
NVL(TO_CHAR(commission_pct,'0.99'),
'No Commission'
)
FROM employees
WHERE last_name like 'K%';

SELECT
first_name,
last_name,
salary,
NVL2(commission_pct,
'Has a Commission',
'No Commission'
)
FROM employees
WHERE last_name like 'K%';```

In the first example, a `NULL` in the employee `commission_pct` is replaced with a zero. The second query returns either a formatted commission percent or the string literal "No Commission." The third example either returns a literal indicating that the record has a commission or has no commission.

## SQL Non-Character Functions

Duration: 20 to 30 minutes.
1. Create a query that selects the department and average salary from the `emp_details_view`.
2. Modify the query so that the average salary is truncated.
3. Modify the query so that the average salary is rounded.
4. Format the salary so that it includes a dollar sign, decimal point and commas (Hint: \$999,999.00).
5. Create a query to select the `job_id`, `start_date`, `end_date` from the `job_history` table.
6. Modify the query so that the start date appears in the `MM/DD/YYYY` format and the end date appears in the `DAY MONTH D, YYYY` format.
7. Round the months between the start date and end date for each record.

## Solution:

SQL-Functions-Non-Character/Solutions/solutions_non_char.sql
```SELECT
department_name,
avg(salary)
FROM emp_details_view
GROUP BY department_name;

SELECT department_name, trunc(avg(salary))
FROM emp_details_view
GROUP BY department_name;

SELECT department_name, round(avg(salary))
FROM emp_details_view
GROUP BY department_name;

SELECT department_name,to_char(avg(salary),'\$999,999.00')
FROM emp_details_view
GROUP BY department_name;

SELECT job_id, start_date, end_date
FROM job_history;

SELECT
job_id,
to_char(start_date,'MM/DD/YYYY'),
TO_CHAR(end_date,'DAY MONTH D, YYYY')
FROM job_history;

SELECT
job_id,
round(months_between(end_date, start_date))
FROM job_history;```