Database Systems
T. Amani Al-Kebsi Lecture 4
Using Single-Row Functions to
Customize Output
Two Types of SQL Functions
Functions
Single-row Multiple-row
functions functions
Return one result Return one result
per row per set of rows
Using Single-Row Functions to
Customize Output
Single-Row Functions
Character
Single-row
General Number
functions
Conversion Date
Character Functions
Character
functions
Case-conversion Character-manipulation
functions functions
LOWER CONCAT
UPPER SUBSTR
INITCAP LENGTH
INSTR
LPAD | RPAD
TRIM
REPLACE
Case-Conversion Functions
› These functions convert the case for character strings:
Function Result
LOWER('SQL Course') sql course
UPPER('SQL Course') SQL COURSE
INITCAP('SQL Course') Sql Course
Using Case-Conversion Functions
› Display the employee number, name, and
department number for employee Higgins:
SELECT employee_id, last_name, department_id
FROM employees
WHERE last_name = 'higgins';
SELECT employee_id, last_name, department_id
FROM employees
WHERE LOWER(last_name) = 'higgins';
Character-Manipulation Functions
› These functions manipulate character strings:
Function Result
CONCAT('Hello', 'World') HelloWorld
SUBSTR('HelloWorld',1,5) Hello
LENGTH('HelloWorld') 10
INSTR('HelloWorld', 'W') 6
LPAD(salary,10,'*') *****24000
RPAD(salary, 10, '*') 24000*****
REPLACE BLACK and BLUE
('JACK and JUE','J','BL')
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 a specified decimal
– TRUNC: Truncates value to a specified decimal
– MOD: Returns remainder of division
Function Result
ROUND(45.926, 2) 45.93
TRUNC(45.926, 2) 45.92
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 that 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,-1) 3
FROM DUAL;
1 2 3
Using the MOD Function
› For all employees with the job title of Sales Representative,
calculate the remainder of the salary after it is divided by 5,000.
SELECT last_name, salary, MOD(salary, 5000)
FROM employees
WHERE job_id = 'SA_REP';
RR Date Format
CurrentYear
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 current 0–49 the current century the century before the
year are: current one
The return date is in The return date is in
50–99 the century after the the current century
current one
Using the SYSDATE Function
› SYSDATE is a function that returns:
– Date
– Time
SELECT sysdate
FROM dual;
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-Manipulation Functions
Function Result
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
Function Result
MONTHS_BETWEEN 19.6774194
('01-SEP-95','11-JAN-94')
ADD_MONTHS (‘31-JAN-96',1) ‘29-FEB-96'
NEXT_DAY ('01-SEP-95','FRIDAY') '08-SEP-95'
LAST_DAY ('01-FEB-95') '28-FEB-95'
Using ROUND and TRUNC Functions with
Dates
› Assume SYSDATE = '25-JUL-03':
Function Result
ROUND(SYSDATE,'MONTH') 01-AUG-03
ROUND(SYSDATE ,'YEAR') 01-JAN-04
TRUNC(SYSDATE ,'MONTH') 01-JUL-03
TRUNC(SYSDATE ,'YEAR') 01-JAN-03
References
➢ Oracle Slides
