facebook twitter
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 numeric functions.
  • Learn about null value functions.
  • Learn about date format functions.
  • Learn about date arithmetic functions.
  • Learn about date/time 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: Division and Modulus

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: ROUND()

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: TRUNC()

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: TOCHAR() with Numbers

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 Formatting

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'), 
	add_months(hire_date, 2)
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;