facebook google plus 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 are available in Oracle (from simple arithmetic to advanced statistical functions). Numerical formatting is avaiable through the TO_CHAR method and rounding and trunctation to prescribed degrees of precision are supported as well.

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%';

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 two numbers are divided. Notice that the division operation results in some unwieldy results that are often rounded or truncated in practice.

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 ROUND function has several options available. A second parameter can be used 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 TRUNC function (like the ROUND function) allows for a second parameter to specify precision.

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 numberic 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 demonstrates two of the many format models available.

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 Format Functions

The TO_CHAR functions is also available for dates and can support various date format models. Once a date has been converted to a string of characters, it will be sorted differently in an ORDER BY clause.

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 (for minute), SS for second. Be careful not to confuse MM (month) and MI (minute).

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%';

This 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.

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.

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%';

This example shows several different ways to return a date based upon the employee hire date (+ operationr, NEXT_DAY and ADD_MONTHS functions).

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 one indicating that there is no commission.

SQL Non-Character Functions

Duration: 10 to 15 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 as MM/DD/YYYY format and the end date appears in 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;