Types of SQL Functions
There are two distinct types of functions:
- Single-row functions
- Multiple-row functions
Single-Row Functions
These functions operate on single rows only and return one result per row. There are different types of single-row functions. This lesson covers the following ones:
- Character
- Number
- Date
- Conversion
Multiple-Row Functions
Functions can manipulate groups of rows to give one result per group of rows. These functions are known as group functions. This is covered in a later lesson.
Single-Row Functions (continued)
This lesson covers the following single-row functions:
- Character functionsAccept character input and can return both character and number values
- Number functionsAccept numeric input and returns numeric values
- Date functionsOperate on values of the DATE data type (All date functions return a value of DATE data type except the MONTHS_BETWEEN function, which returns a number.)
- Conversion functionsConvert a value from one data type to another
- General functions:
– NVL
– NVL2
– NULLIF
– COALSECE
– CASE
– DECODE
1. Character Functions
SELECT 'The job id for '||UPPER(last_name)||' is '
||LOWER(job_id) AS "EMPLOYEE DETAILS"
FROM employees;
SELECT employee_id, last_name, department_id
FROM employees
WHERE last_name = 'higgins';
no rows selected
SELECT employee_id, last_name, department_id
FROM employees
WHERE LOWER(last_name) = 'higgins';
SELECT employee_id, UPPER(last_name), department_id
FROM employees
WHERE INITCAP(last_name) = 'Higgins';
||LOWER(job_id) AS "EMPLOYEE DETAILS"
FROM employees;
SELECT employee_id, last_name, department_id
FROM employees
WHERE last_name = 'higgins';
no rows selected
SELECT employee_id, last_name, department_id
FROM employees
WHERE LOWER(last_name) = 'higgins';
SELECT employee_id, UPPER(last_name), department_id
FROM employees
WHERE INITCAP(last_name) = 'Higgins';
Character-Manipulation Functions
SELECT employee_id, CONCAT(first_name, last_name) NAME, job_id,
LENGTH (last_name), INSTR(last_name, 'a') "Contains 'a'?"
FROM employees
WHERE SUBSTR(job_id, 4) = 'REP';
2 - Number Functions
• ROUND: Rounds value to specified decimal
ROUND(45.926, 2) -----> 45.93
• TRUNC: Truncates value to specified decimal
TRUNC(45.926, 2)-----> 45.92
• MOD: Returns remainder of division
MOD(1600, 300) -----> 100
SELECT ROUND(45.923,2), ROUND(45.923,0),
ROUND(45.923,-1)
FROM DUAL;
SELECT last_name, salary, MOD(salary, 5000)
FROM employees
WHERE job_id = 'SA_REP';
3 - Working with Dates
• MONTHS_BETWEEN ('01-SEP-95','11-JAN-94') 19.6774194
• ADD_MONTHS ('11-JAN-94',6) '11-JUL-94'
• NEXT_DAY ('01-SEP-95','FRIDAY') '08-SEP-95'
• LAST_DAY('01-FEB-95') '28-FEB-95'
SELECT SYSDATE
FROM DUAL;
SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
FROM employees
WHERE department_id = 90;
SELECT employee_id, hire_date,
ROUND(hire_date, 'MONTH'), TRUNC(hire_date, 'MONTH')
FROM employees
WHERE hire_date LIKE '%97';
SELECT SYSDATE,ROUND(SYSDATE,'HH24') FROM dual;
24-Nov-1999 09:23:56 24-Nov-1999 09:00:00
SELECT TRUNC(last_analyzed,'HH')
FROM user_tables
WHERE table_name='TEST_CASE';
TRUNC(LAST_ANALYZED,
--------------------
28-Nov-1999 11:00:00
SELECT NEXT_DAY('01-Jan-2000','Monday') "1st Monday"
,NEXT_DAY('01-Nov-2004','Tuesday')+7 "2nd Tuesday"
FROM dual;
1st Monday 2nd Tuesday
----------- -----------
03-Jan-2000 09-Nov-2004
4 - Conversion Functions
Data-type conversion :
- Implicit data-type Conversion
- Explicit data-type Conversion
TO_CHAR(date, 'format_model')
SELECT employee_id, TO_CHAR(hire_date, 'MM/YY') Month_Hired
FROM employees
WHERE last_name = 'Higgins';
Elements of the Date Format Model
YYYY Full year in numbers
YEAR Year spelled out
MM Two-digit value for month
MONTH Full name of the month
DY Three-letter abbreviation of the day of the week
DAY Full name of the day of the week
MON Three-letter abbreviation of the month
DD Numeric day of the month
SELECT last_name,
TO_CHAR(hire_date, 'fmDD Month YYYY') HIREDATE
FROM employees;
SELECT last_name,
TO_CHAR(hire_date, 'fmDdspth "of" Month YYYY fmHH:MI:SS AM')
HIREDATE
FROM employees;
TO_CHAR(number, 'format_model')
SELECT TO_CHAR(salary, '$99,999.00') SALARY
FROM employees
WHERE last_name = 'Ernst';
TO_NUMBER(char[, 'format_model'])
TO_DATE(char[, 'format_model'])
SELECT last_name, TO_CHAR(hire_date, 'DD-Mon-YYYY')
FROM employees
WHERE hire_date < TO_DATE('01-Jan-90', 'DD-Mon-RR');
Nesting Functions
SELECT last_name,
NVL(TO_CHAR(manager_id), 'No Manager')
FROM employees
WHERE manager_id IS NULL;
Example
Display the date of the next Friday that is six months from the hire date. The resulting date should appear as Friday, August 13th, 1999. Order the results by hire date.
SELECT TO_CHAR(NEXT_DAY(ADD_MONTHS
(hire_date, 6), 'FRIDAY'),
'fmDay, Month DDth, YYYY')
"Next 6 Month Review"
FROM employees
ORDER BY hire_date;
5 - General Functions
These functions work with any data type and pertain to using null value.
• NVL (expr1, expr2)
• NVL2 (expr1, expr2, expr3)
• NULLIF (expr1, expr2)
• COALESCE (expr1, expr2, ..., exprn)
– NVL(commission_pct,0)
– NVL(hire_date,'01-JAN-97')
– NVL(job_id,'No Job Yet')
SELECT last_name, salary, NVL(commission_pct, 0),
(salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL
FROM employees;
Conditional Expressions
• Give you the use of IF-THEN-ELSE logic within a SQL statement
• Use two methods:
– CASE expression
– DECODE function
SELECT last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "REVISED_SALARY"
FROM employees;
Using the CASE Expression
In the preceding SQL statement, the value of JOB_ID is decoded. If JOB_ID is IT_PROG, the salary increase is 10%; if JOB_ID is ST_CLERK, the salary increase is 15%; if JOB_ID is SA_REP, the salary increase is 20%. For all other job roles, there is no increase in salary.
The same statement can be written with the DECODE function.
SELECT last_name, job_id, salary,
DECODE(job_id, 'IT_PROG', 1.10*salary,
'ST_CLERK', 1.15*salary,
'SA_REP', 1.20*salary,
salary)
REVISED_SALARY
FROM employees;
Group (Multi-row) Functions
- AVG
- COUNT
- MAX
- MIN
- STDDEV
- SUM
- VARIANCE
Examples
Select MIN(AVG(salary)) from employees group by job_id
The example in the slide displays department numbers and maximum salaries for those departments whose maximum salary is greater than $10,000.
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000;
The example in the slide displays the job ID and total monthly salary for each job with a total payroll
exceeding $13,000. The example excludes sales representatives and sorts the list by the total monthly
salary.
SELECT job_id, SUM(salary) PAYROLL
FROM employees
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id
HAVING SUM(salary) > 13000
ORDER BY SUM(salary);