Database Management

Systems
Subject Teacher: Zartasha Baloch
 2

Single Row Functions

Lecture # 14 & 15

Disclaimer: The material used in this presentation to deliver the lecture i.e., definitions/text and
pictures/graphs etc. does not solely belong to the author/presenter. The presenter has gathered this
lecture material from various sources on web/textbooks. Following sources are especially
acknowledged:
1. Connolly, Thomas M., and Carolyn E. Begg. Database systems: a practical approach to design, implementation,
and management. Pearson Education, 2005.

2. Gorman, Tim, Inger Jorgensen, Melanie Caffrey, and Lex deHaan. Beginning Oracle SQL: For Oracle Database
12c. Apress, 2014.

3. Greenberg, Nancy, and Instructor Guide PriyaNathan. "Introduction to Oracle9i: SQL." ORACLE, USA (2001).
 Objectives

After completing this lesson, you should be able to do the following:

 Describe various types of functions available
in SQL
 Use character, number, and date functions in SELECT statements
 Describe the use of conversion functions
SQL Functions

Input Output
Function

arg 1 Function
performs action
arg 2
Result
value

arg n
Two Types of SQL Functions

Functions

Single-row Multiple-row
functions functions
Single-Row Functions

Single row functions:

 Manipulate data items
 Accept arguments and return one value
 Act on each row returned
 Return one result per row
 May modify the data type
 Can be nested
 Accept arguments which can be a column or an expression

function_name [(arg1, arg2,...)]

Single-Row Functions

Character

General Number
Single-row
functions

Conversion Date
Character Functions
Character
functions

Case-manipulation Character-manipulation
functions functions
LOWER CONCAT
UPPER SUBSTR
INITCAP LENGTH
INSTR
LPAD | RPAD
TRIM
REPLACE
Case Manipulation Functions

These functions convert case for character strings.

Function Result
LOWER('SQL Course') sql course
UPPER('SQL Course') SQL COURSE
INITCAP('SQL Course') Sql Course
Using Case Manipulation Functions

Display the employee number, name, and department number

for employee Higgins:

SELECT empno, ename, deptno

FROM emp
WHERE ename = 'higgins';
no rows selected

SELECT empno, ename, deptno

FROM emp
WHERE LOWER(ename) = 'higgins';
Character-Manipulation Functions

These functions manipulate character strings:

Function Result
CONCAT('Hello', 'World') HelloWorld
SUBSTR('HelloWorld’,-3,3) Hello
LENGTH('HelloWorld') 10
INSTR('HelloWorld’, ‘W') 6
LPAD(salary,10,'*') *****24000
RPAD(salary, 10, '*') 24000*****
TRIM('H' FROM 'HelloWorld') elloWorld
Using the Character-Manipulation
Functions
1
SELECT employee_id, CONCAT(first_name, last_name) NAME,
job_id, LENGTH (last_name), 2
INSTR(last_name, 'a') "Contains 'a'?"
FROM employees 3
WHERE SUBSTR(job_id, 4) = 'REP';

1 2 3
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
Using the ROUND Function

1 2

SELECT ROUND(45.923,2), ROUND(45.923,0),

ROUND(45.923,-1) 3
FROM DUAL;

1 2 3
DUAL is a dummy table you can use to view results
from functions and calculations.
Using the TRUNC Function

1 2

SELECT TRUNC(45.923,2), TRUNC(45.923),

TRUNC(45.923,-2) 3
FROM DUAL;

1 2 3
Using the MOD Function

Calculate the remainder of a salary after it is divided by 5000 for

all employees whose job title is sales representative.

SELECT last_name, salary, MOD(salary, 5000)

FROM employees
WHERE job_id = 'SA_REP';
 Working with Dates

 Oracle database stores dates in an internal numeric format: century, year, month, day,
hours, minutes, seconds.
 The default date display format is DD-MON-RR.
 Allows you to store 21st century dates in the 20th century by specifying only the last two digits of
the year.
 Allows you to store 20th century dates in the 21st century in the same way.

SELECT last_name, hire_date

FROM employees
WHERE last_name like 'G%';
Working with Dates

SYSDATE is a function that returns:

 Date
 Time
Arithmetic with Dates

 Add or subtract a number to or from a date for a resultant date value.

 Subtract two dates to find the number of days between those dates.
 Add hours to a date by dividing the number of hours by 24.
Using Arithmetic Operators
with Dates

SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS

FROM employees
WHERE department_id = 90;
Date Functions

Function Description

MONTHS_BETWEEN Number of months

between two dates
ADD_MONTHS Add calendar months to
date
NEXT_DAY Next day of the date
specified
LAST_DAY Last day of the month
ROUND Round date
TRUNC Truncate date
Using Date Functions

• 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'
Using Date Functions

Assume SYSDATE = '25-JUL-95':

• ROUND(SYSDATE,'MONTH') 01-AUG-95

• ROUND(SYSDATE ,'YEAR') 01-JAN-96

• TRUNC(SYSDATE ,'MONTH') 01-JUL-95

• TRUNC(SYSDATE ,'YEAR') 01-JAN-95

 Practice, Part One: Overview

This practice covers the following topics:

 Writing a query that displays the current date
 Creating queries that require the use of numeric, character, and date functions
 Performing calculations of years and months of service for an employee
Conversion Functions

Data type
conversion

Implicit data type Explicit data type

conversion conversion
 Implicit Data Type Conversion

For assignments, the Oracle server can automatically convert the following:

From To

VARCHAR2 or CHAR NUMBER

VARCHAR2 or CHAR DATE

NUMBER VARCHAR2

DATE VARCHAR2
 Implicit Data Type Conversion

For expression evaluation, the Oracle Server can automatically convert the following:

From To

VARCHAR2 or CHAR NUMBER

VARCHAR2 or CHAR DATE

Explicit Data Type Conversion

TO_NUMBER TO_DATE

NUMBER CHARACTER DATE

TO_CHAR TO_CHAR
Using the TO_CHAR Function with Dates

TO_CHAR(date, 'format_model')

The format model:

 Must be enclosed in single quotation marks and is case sensitive
 Can include any valid date format element
 Has an fm element to remove padded blanks or suppress leading
zeros
 Is separated from the date value by a comma
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
Three-letter abbreviation of the
MON
month
Three-letter abbreviation of the
DY
day of the week
DAY Full name of the day of the week
DD Numeric day of the month
Elements of the Date Format Model

 Time elements format the time portion of the date.

HH24:MI:SS AM 15:45:32 PM

 Add character strings by enclosing them in double quotation marks.

DD "of" MONTH 12 of OCTOBER

 Number suffixes spell out numbers.

ddspth fourteenth
Using the TO_CHAR Function with Dates

SELECT last_name,
TO_CHAR(hire_date, 'fmDD Month YYYY')
AS HIREDATE
FROM employees;

…
Using the TO_CHAR Function with Numbers

TO_CHAR(number, 'format_model')

These are some of the format elements you can use with the
TO_CHAR function to display a number value as a character:

9 Represents a number
0 Forces a zero to be displayed
$ Places a floating dollar sign
L Uses the floating local currency symbol
. Prints a decimal point
, Prints a thousand indicator
Using the TO_CHAR Function with Numbers

SELECT TO_CHAR(salary, '$99,999.00') SALARY

FROM employees
WHERE last_name = 'Ernst';
Using the TO_NUMBER and TO_DATE Functions

 Convert a character string to a number format using the TO_NUMBER

function:

TO_NUMBER(char[, 'format_model'])

 Convert a character string to a date format using the TO_DATE function:

TO_DATE(char[, 'format_model'])

 These functions have fx modifier. This modifier specifies the exact

matching for the character argument and date format model of a
TO_DATE function
RR Date Format
Current Year Specified Date RR Format YY Format
1995 27-OCT-95 1995 1995
1995 27-OCT-17 2017 1917
2001 27-OCT-17 2017 2017
2001 27-OCT-95 1995 2095

If the specified two-digit year is:

0–49 50–99
If two digits The return date is in The return date is in
of the 0–49 the current century the century before
current the current one
year are: The return date is in The return date is in
50–99 the century after the current century
the current one
 37
MySQL Format
Syntax STR_TO_DATE(string, format)

Quick Example SELECT STR_TO_DATE('17-09-2010','%d-%m-%Y');

Error Returns NULL if the format is not matched, or datetime value is not valid

MySQL Oracle Format Specifier

%Y YYYY 4-digit year
%y YY 2-digit year
Abbreviated month (Jan
%b MON
- Dec)
Month name (January -
%M MONTH
December)
%m MM Month (1 - 12)
Abbreviated day (Sun -
%a DY
Sat)
%d DD Day (1 - 31)
%H HH24 Hour (0 - 23)
%h HH or HH12 Hour (1 - 12)
%i MI Minutes (0 - 59)
%s SS Seconds (0 - 59)
 Example of RR Date Format

 To find employees hired prior to 1990, use the RR format, which produces
the same results whether the command is run in 1999 or now:

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

 Single-row functions can be nested to any level.

 Nested functions are evaluated from deepest level to the
least deep level.

F3(F2(F1(col,arg1),arg2),arg3)

Step 1 = Result 1
Step 2 = Result 2
Step 3 = Result 3
Nesting Functions

SELECT last_name,
NVL(TO_CHAR(manager_id), 'No Manager')
FROM employees
WHERE manager_id IS NULL;
General Functions

These functions work with any data type and pertain

to using nulls.
 NVL (expr1, expr2)
 NVL2 (expr1, expr2, expr3)
 NULLIF (expr1, expr2)
 COALESCE (expr1, expr2, ..., exprn)
NVL Function

Converts a null to an actual value.

 Data types that can be used are date, character, and
number.
 Data types must match:
 NVL(commission_pct,0)
 NVL(hire_date,'01-JAN-97')
 NVL(job_id,'No Job Yet')
Using the NVL Function

SELECT last_name, salary, NVL(commission_pct, 0), 1

(salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL 2
FROM employees;

1 2
Using the NVL2 Function

SELECT last_name, salary, commission_pct, 1

NVL2(commission_pct,
'SAL+COMM', 'SAL') income
2
FROM employees WHERE department_id IN (50, 80);

1 2
Using the NULLIF Function
1
SELECT first_name, LENGTH(first_name) "expr1",
last_name, LENGTH(last_name) "expr2", 2
NULLIF(LENGTH(first_name), LENGTH(last_name)) result 3
FROM employees;

1 2 3
 Using the COALESCE Function

 The advantage of the COALESCE function over the NVL function is that the COALESCE
function can take multiple alternate values.
 If the first expression is not null, it returns that expression; otherwise, it does a COALESCE
of the remaining expressions.
Using the COALESCE Function

SELECT last_name,
COALESCE(commission_pct, salary, 10) comm
FROM employees
ORDER BY commission_pct;

…
Conditional Expressions

 Provide the use of IF-THEN-ELSE logic within a SQL statement

 Use two methods:
 CASE expression
 DECODE function
 The CASE Expression

Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE

statement:

CASE expr WHEN comparison_expr1 THEN return_expr1

[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
 Using the CASE Expression

Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement:

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;

…
 The DECODE Function

Facilitates conditional inquiries by doing the work of a CASE or IF-THEN-ELSE

statement:

DECODE(col|expression, search1, result1

[, search2, result2,...,]
[, default])
Using 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;

…
Using the DECODE Function

Display the applicable tax rate for each employee in

department 80.

SELECT last_name, salary,

DECODE (TRUNC(salary/2000, 0),
0, 0.00,
1, 0.09,
2, 0.20,
3, 0.30,
4, 0.40,
5, 0.42,
6, 0.44,
0.45) TAX_RATE
FROM employees
WHERE department_id = 80;
Summary

In this lesson, you should have learned how to:

 Perform calculations on data using functions
 Modify individual data items using functions
 Manipulate output for groups of rows using functions
 Alter date formats for display using functions
 Convert column data types using functions
 Use NVL functions
 Use IF-THEN-ELSE logic