D80190GC20 Les04
D80190GC20 Les04
D80190GC20 Les04
4
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2012, Oracle and/or its affiliatesฺ
s a
) h a
ฺ c om
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T
E is S
H
S e th
H
N AT to us
R AM ense
K AR le lic
H AN ferab
N IS ans
A VA on-tr
BH n
NIIT LTD
Course Roadmap
Unit 2: Joins, Subqueries, and Lesson 4: Using Single-Row Functions to You are here!
Set Operators Customize Output
r
(r tude
s
T S
In Unit 1, you will learn how to query the data from S HE how
tables, h i
tos query selected records from tables,
and also how to sort the data retrieved from the
T Htables.use t
N A to
R AM ense
K AR le lic
H AN ferab
N IS ans
A VA on-tr
BH n
Objectives
s a
) h a
ฺ c om
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T
E they iareSused to manipulate data values.
Functions make the basic query block more powerful, Hand h s
H SItufocuses
This is the first of two lessons that explore functions.
T s e t on single-row character, number, and
date functions. N A to
R AM ense
K AR le lic
H AN ferab
N IS ans
A VA on-tr
BH n
HR Application Scenario
HR Application
Average (Salary) GO
HR Application
Zhen Accounts s a
IT
) h a
ฺ c om Sales
oo eฺ
Marketing
a h
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T
E is S
Consider a scenario where Zhen, an HR manager S in H
China, wants
h to calculate the average salary
across various departments of all employees T H uinsChina.
working
t
e In order to generate such
information, Zhen has to enter conditionsNsuch A as tcountry
o name (China) and get the list of
employees working in China. Then A M e
s the operation to be performed on the values (in this
case, AVERAGE salary). The R
R he has
ic
HR applicatione
enter
nqueries the database conditionally and then applies the
A e l
mathematical operation
A NtoKcalculate
r a bl the average salary. The results are returned to Zhen, along with
I S H sfe
a chart for analysis.
AN performed
The operations
V - t r an on the values returned by a SQL query are called Functions. There are
A types
different
H n oofnfunctions, which are useful when you want to apply some kind of customization on
B
the values returned by the query. In the following lessons, you will learn about the different types of
functions.
Lesson Agenda
• Nesting functions
• Number functions
• Working with dates
• Date functions
s a
) h a
ฺ c om
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T
E is S
H
S e th
H
N AT to us
R AM ense
K AR le lic
H AN ferab
N IS ans
A VA on-tr
BH n
SQL Functions
_____________
_____________
________
.SQL
_____________
Input _______
_____
Output
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2012, Oracle and/or its affiliatesฺ
Function
arg n
s a
) h a
ฺ c om
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T
E functionsS to do the following:
Functions are a very powerful feature of SQL. You S Huse
can h i s
• Perform calculations on data. T H use t
N A to
Modify individual data items.AM
•
R e n se
• Manipulate output for R
A c
groups oflirows.
K l e
b for display.
•
H AN numbers
Format dates and
e r a
•
N IScolumn
Convert
a n sf types.
data
SQLAfunctions n -tr take arguments and always return a value.
VA osometimes
BH n
Functions
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2012, Oracle and/or its affiliatesฺ
Single-row Multiple-row
functions functions
r
(r tude
s
T
E is S
There are two types of functions: H
S e th
H
• Single-row functions
N AT to us
• Multiple-row functions
R AM ense
Single-Row Functions
K AR le lic
These functions operate
H r ab rows only and return one result per row. There are different types
AN onfesingle
IS anThis
of single-row functions.
N s lesson covers the following functions:
• A n-tr
VCharacter
A no
BH • Number
• Date
Multiple-Row Functions
Functions can manipulate groups of rows to give one result per group of rows. These functions are
also known as group functions (covered in the lesson titled “Reporting Aggregated Data Using the
Group Functions”).
Note: For more information and a complete list of available functions and their syntax, see the
“Functions” section in Oracle Database SQL Language Reference for 12c database.
Single-Row Functions
Single-row functions:
• Manipulate data items
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2012, Oracle and/or its affiliatesฺ
s a
function_name [(arg1, arg2,...)]
) h a
ฺ c om
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T
E They S one or more arguments and
You can use single-row functions to manipulate data H
items.
S e An h i s accept
t argument can be one of the following:
return one value for each row that is returnedTbyHthe query.
s
• User-supplied constant N A to u
• Variable value R AM ense
• Column name NK
AR le lic
ExpressionH A ferab
•
N IS ans
A VA
Features -tr functions include the following:
of single-row
o n
BH • n each row that is returned in the query
Act on
• Return one result per row
• Possibly return a data value of a different type than the one that is referenced
• Possibly expect one or more arguments
• Can be used in SELECT, WHERE, and ORDER BY clauses; can be nested
In the syntax:
function_name Is the name of the function
arg1, arg2 Is any argument to be used by the function. This can be
represented by a column name or expression.
Single-Row Functions
Character
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2012, Oracle and/or its affiliatesฺ
Single-row
General Number
functions
s a
Conversion Date
) h a
ฺ c om
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T
E functions:S
H
In this lesson, you will learn about the following single-row
S et h i s
Character functions: Accept character T Hinput us can return both character and number
•
values N A to and
M se
Anumeric
• Number functions: Accept
R R
l i c eninput and return numeric values
• Date functions: N KA bonlevalues of the DATE data type
Operate
f e ra single-row functions in the lesson titled “Using Conversion
HAthe following
S
You will learn Iabout
N Conditionalns Expressions”:
FunctionsAand
V on- t r a
H• A Conversion
n functions: Convert a value from one data type to another
B
• General functions: These functions take any data type and can also handle NULLs.
Lesson Agenda
• Nesting functions
• Number functions
• Working with dates
• Date functions
s a
) h a
ฺ c om
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T
E is S
H
S e th
H
N AT to us
R AM ense
K AR le lic
H AN ferab
N IS ans
A VA on-tr
BH n
Character Functions
Character
functions
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2012, Oracle and/or its affiliatesฺ
Case-conversion Character-manipulation
functions functions
LOWER CONCAT
UPPER SUBSTR
INITCAP LENGTH
INSTR
LPAD | RPAD
s a
TRIM
) h a
om
REPLACE
ฺ c
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T
Einput and S
Single-row character functions accept character data
S Has
t h i s can return both character and
A TH into
numeric values. Character functions can be divided
u e following:
sthe
• Case-conversion functions MN
e to
A
R icen
• Character-manipulation functions
s
R
A le l
Function K
N rab Purpose
A
H sfe
I S
LOWER(column|expression)
N n Converts alpha character values to lowercase
VA on-tr a
UPPER(column|expression) Converts alpha character values to uppercase
H A n
B INITCAP(column|expression) Converts alpha character values to uppercase for
the first letter of each word; all other letters in
lowercase
CONCAT(column1|expression1, Concatenates the first character value to the
column2|expression2) second character value; equivalent to
concatenation operator (||)
SUBSTR(column|expression,m[,n]) Returns specified characters from character
value starting at character position m, n
characters long (If m is negative, the count starts
from the end of the character value. If n is
omitted, all characters to the end of the string are
returned.)
Note: The functions discussed in this lesson are only some of the available functions.
Function Purpose
LENGTH(column|expression) Returns the number of characters in the expression
INSTR(column|expression, Returns the numeric position of a named string.
’string’, [,m], [n] ) Optionally, you can provide a position m to start
searching, and the occurrence n of the string. m and n
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2012, Oracle and/or its affiliatesฺ
Case-Conversion Functions
You can use these functions to convert the case of character strings:
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2012, Oracle and/or its affiliatesฺ
Function Result
LOWER('SQL Course') sql course
UPPER('SQL Course') SQL COURSE
INITCAP('SQL Course') Sql Course
s a
) h a
ฺ c om
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
E T S case-conversion functions.
As shown in the slide, LOWER, UPPER, and INITCAP H are theh i s
three
LOWER: Converts mixed-case or uppercaseT H Scharacter
s e t
strings to lowercase
•
N A to u
UPPER: Converts mixed-case Mor lowercase e character strings to uppercase
•
A
R letter n s
• INITCAP: Converts the
A Rfirst l i ceof each word to uppercase and the remaining letters to
lowercase
A NK rable
I S H sfe
AN n-tran
For example:
V
A SELECT
no 'The job id for '||UPPER(last_name)||' is '
BH
||LOWER(job_id) AS "EMPLOYEE DETAILS"
FROM employees;
Display the employee number, name, and department number for employee Higgins:
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2012, Oracle and/or its affiliatesฺ
s a
) h a
ฺ c om
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T
The slide example displays the employee number, S name, is S number of employee
HE andthdepartment
Higgins.
A TH use
The WHERE clause of the first SQL statement
M e to the employee name as higgins. Because all
N specifies
the data in the EMPLOYEES tableR A s
is storedeinnproper case, the name higgins does not find a match
in the table, and no rows are
K
R
Aselected.
l e lic
The WHERE clause
N second
Athe
of r a bSQL statement converts the LAST_NAME column to lowercase for
H
IS aBecause e
sf both names are now lowercase, a match is found and one row is
comparisonN purposes. n
selected.
A n -tr clause can be rewritten in the following manner to produce the same result:
VAThe oWHERE
B H
...WHERE nlast_name = 'Higgins'
The name in the output appears as it was stored in the database. To display the name in uppercase,
use the UPPER function in the SELECT statement.
SELECT employee_id, UPPER(last_name), department_id
FROM employees
WHERE INITCAP(last_name) = 'Higgins';
Note: You can use functions such as UPPER and LOWER with ampersand substitution. For example,
use UPPER('&job_title') so that the user does not have to enter the job title in a specific case.
Character-Manipulation Functions
Function Result
CONCAT('Hello', 'World') HelloWorld
SUBSTR('HelloWorld',1,5) Hello
LENGTH('HelloWorld') 10
INSTR('HelloWorld', 'W') 6
LPAD(24000,10,'*') *****24000
RPAD(24000, 10, '*') 24000*****
s a
) h a
ฺ c om
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T S
Ethe character-manipulation
CONCAT, SUBSTR, LENGTH, INSTR, LPAD, and RPADH
S are
t h i s functions that
you will learn in this lesson.
A TH use
• CONCAT: Joins values togetherM (you to to using two parameters with CONCAT)
N areelimited
SUBSTR: Extracts a stringR
A s
en length
of determined
•
R l i c
• LENGTH: Shows N KAlength
the b lofe a string as a numeric value
INSTR:S
A
H thesnumeric
Finds f a
er position of a named character
•
I n expression left-padded to the length of n characters with a character
• V AN Returns
LPAD:
n - t r aan
H A expression
n o
B • RPAD: Returns an expression right-padded to the length of n characters with a character
expression
s a
) h a
ฺ c om
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T
E andisjobSIDs for all employees who have
The first example in the slide displays employee last H
names
Sthe fourththposition of the job ID.
the string, REP, contained in the job ID, starting
T Hat
us e
N A t o
The second SQL statement in the slide
and last name, length of the last R AMand
name,
displaysedata such as employee ID, concatenated first name
e n s position of the first occurrence of the letter ‘a’ in the
the
ARwhose
last name for those employees e ic names end with the letter “n.”
llast
K l
H AN ferab
N IS ans
A VA on-tr
BH n
Lesson Agenda
• Nesting functions
• Number functions
• Working with dates
• Date functions
s a
) h a
ฺ c om
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T
E is S
H
S e th
H
N AT to us
R AM ense
K AR le lic
H AN ferab
N IS ans
A VA on-tr
BH n
Nesting Functions
F3(F2(F1(col,arg1),arg2),arg3)
Step 1 = Result 1
Step 2 = Result 2
Step 3 = Result 3
s a
) h a
ฺ c om
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T
Efunctions Sare evaluated from the innermost
H
Single-row functions can be nested to any depth. Nested h i s
H Sto ushow
level to the outermost level. Some examples follow
T s
t the flexibility of these functions.
e you
N A to
R AM ense
K AR le lic
H AN ferab
N IS ans
A VA on-tr
BH n
SELECT last_name,
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2012, Oracle and/or its affiliatesฺ
s a
) h a
ฺ c om
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T S
HE thiins department
The example in the slide displays the last names ofSemployees 60. The evaluation of
the SQL statement involves three steps:
A TH use
1. The inner function retrieves theMfirst to
N eighte characters of the last name.
Result1 = SUBSTR (LAST_NAME,
A
R ic1,en8) s
R
A le l the result with _US.
2. The outer function K concatenates
H AN ferab '_US')
IS ans
Result2 = CONCAT(Result1,
N
3. V
A
The
o n -tr function converts the results to uppercase.
A outermost
BHResult3n= UPPER(Result2)
Result3 is displayed. The entire expression becomes the column heading because no column alias
was given.
Lesson Agenda
• Nesting functions
• Number functions
• Working with dates
• Date Functions
s a
) h a
ฺ c om
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T
E is S
H
S e th
H
N AT to us
R AM ense
K AR le lic
H AN ferab
N IS ans
A VA on-tr
BH n
Numeric Functions
• CEIL: Returns the smallest whole number greater than or equal to a specified number
• FLOOR: Returns the largest whole number equal to or less than a specified number
• MOD: Returns remainder of division
Function Result
ROUND(45.926, 2) 45.93
TRUNC(45.926, 2) 45.92
CEIL (2.83) 3
s a
FLOOR (2.83) 2
) h a
MOD (1600, 300) 100
ฺ c om
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T SThis section describes some of
Numeric functions accept numeric input and returnS HE values.
numeric h i s
the numeric functions. T H use t
N A to
Function
R AM ePurpose
n se
K
ROUND(column|expression,AR len) lic Rounds the column, expression, or value to n decimal
H AN ferab places or, if n is omitted, no decimal places (If n is
S
NI -trans negative, numbers to the left of decimal point are rounded
A
AV non
TRUNC(column|expression,
H
n) Truncates the column, expression, or value to n decimal
B places or, if n is omitted, n defaults to zero
MOD(m,n) Returns the remainder of m divided by n
Note: This list contains only some of the available numeric functions.
For more information, see the “Numeric Functions” section in Oracle Database SQL Language
Reference for 12c database.
1 2
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2012, Oracle and/or its affiliatesฺ
1 2 3
s a
) h a
ฺ c om
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T S places. If the second
E toisn decimal
The ROUND function rounds the column, expression, or Hvalue h
argument is 0 or is missing, the value is rounded
T HtoSzeros e t places. If the second argument is 2,
decimal
u if the second argument is –2, the value is
N
the value is rounded to two decimal places. AConversely,
t o
rounded to two decimal places to the
R AM se to the nearest unit of 100).
left (rounded
e n
Recall DUAL Table
K AR le lic
The DUAL table isH AN byfethe
owned rabuser SYS and can be accessed by all users. It contains one column,
DUMMY, andN ISrow awith
one n sthe value X. The DUAL table is useful when you want to return a value only
A
V example,
once (for t r
n- theThe
value of a constant, pseudocolumn, or expression that is not derived from a
H
table
Awith n
userodata). DUAL table is generally used for completeness of the SELECT clause
B
syntax, because both SELECT and FROM clauses are mandatory, and several calculations do not
need to select from the actual tables.
1 2
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2012, Oracle and/or its affiliatesฺ
1 2 3
s a
) h a
ฺ c om
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T
Evalueitos nSdecimal places.
The TRUNC function truncates the column, expression, Hor
S e th
The TRUNC function works with arguments A H
T to those
similar us of the ROUND function.
N t o
AM ethensvalue
If the second argument is 0 or is missing,
R
e is truncated to zero decimal places.
If the second argument is 2,AtheR value lisictruncated to two decimal places.
K
N argument
a b leis –2, the value is truncated to two decimal places to the left.
A
H sfer
Conversely, if the second
I S
V ANargument
If the second
- t r anis –1, the value is truncated to one decimal place to the left.
H A non
B
s a
) h a
ฺ c om
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T
E divided S
H
The MOD function finds the remainder of the first argument h i s by the second argument. The
slide example displays employee records where T HtheSemployee_id
u s e t is an even number.
Note: The MOD function is often used M
A
N e towhether a value is odd or even.
to determine
R A e n s
K AR le lic
H AN ferab
N IS ans
A VA on-tr
BH n
Lesson Agenda
• Nesting functions
• Number functions
• Working with dates
• Date functions
s a
) h a
ฺ c om
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T
E is S
H
S e th
H
N AT to us
R AM ense
K AR le lic
H AN ferab
N IS ans
A VA on-tr
BH n
• The Oracle Database stores dates in an internal numeric format: century, year, month,
day, hours, minutes, and seconds.
• The default date display format is DD-MON-RR.
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2012, Oracle and/or its affiliatesฺ
s a
) h a
ฺ c om
…
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T S
Eformat,isrepresenting
The Oracle Database stores dates in an internal numericS H t h the century, year,
month, day, hours, minutes, and seconds. TH se
A u
N datee istoDD-MON-RR. Valid Oracle dates are between
The default display and input format for Many
January 1, 4712 B.C., and December R A 31, e n
9999s A.D.
K
In the example in the slide,AtheRHIRE_DATE
l e lic column output is displayed in the default format DD-
MON-RR. However, AN are
dates r a
not
bstored in the database in this format. All the components of the
H
S stored.
Iare e
f although a HIRE_DATE such as 17-JUN-11 is displayed as day,
sSo,
date and time
N a n
month,V
A and
o -tr is also time and century information associated with the date. The complete
A year,nthere
H might ben June 17, 2011, 5:10:43 PM.
date
B
RR Date Format
r
(r tude
s
T
Ecan useisit S
The RR date format is similar to the YY element, but you
S H t h to specify different centuries. Use
the RR date format element instead of YY so that
A THthe se of the return value varies according to
century
u
the specified two-digit year and the last two
M N e to current year. The table in the slide
digits of the
summarizes the behavior of the RR A element. s
R R c e n
Current Year A Date l i
N KGiven b l e Interpreted (RR) Interpreted (YY)
1994 H A 27-OCT-95
f e ra 1995 1995
I S s
1994AN
V - t r an 27-OCT-17 2017 1917
A n
BH 2001 no 27-OCT-17 2017 2017
2048 27-OCT-52 1952 2052
2051 27-OCT-47 2147 2047
Note: The values shown in the last two rows of the preceding table.
• Time
SELECT sysdate
FROM dual;
s a
) h a
ฺ c om
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T
EYou can S
SYSDATE is a date function that returns the system date. H
S ethe h i s use SYSDATE just as you would
t system date by selecting SYSDATE
use any other column name. For example, you T H
can display
s
N
from a table. It is customary to select SYSDATE A fromt o aupublic table called DUAL.
M and time
Adate se set for the operating system on which the
Note: SYSDATE returns the current R e n
c a place in Australia and connected to a remote database in
database resides. Therefore,
K ARif you e
l areliin
a location in the United
A b the SYSDATE function will return the U.S. date and time. In
NStatesra(U.S.),
such a case, to H e
sf time, you can use the CURRENT_DATE function that returns the current
ISget theanlocal
N r zone.
A VAsession
date in the
o n -ttime
BH n
• CURRENT_TIMESTAMP returns the current date and time from the user session.
s a
) h a
ฺ c om
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T
E return S
The CURRENT_DATE and CURRENT_TIMESTAMP functions H h i s the current date and current time
stamp, respectively. T H S use t
Note: The SESSIONTIMEZONE function N A thetovalue of the current session’s time zone. The
returns
R
return type is a time zone offset (a AM entype
character se in the format '[+|-]TZH:TZM') or a time zone
region name, depending onAhow
K
R the user
l e lic specified the session time zone value in the most recent
AN feThe
ALTER SESSION statement.
H r abexample in the slide shows that the session time zone is
IS anObserve
Etc/Universal timezone.
N s that the database time zone is different from the current session’s
VA on-tr
time zone.
A
BH n
s a
) h a
ฺ c om
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T
Eperform S
Because the database stores dates as numbers, youS H
can
t h i s calculations using arithmetic
THcan add
operators such as addition and subtraction. You
A u seand subtract number constants as well as
dates.
A MN se to
R icen
You can perform the following operations:
R
K A le l
Operation H rab
AN feResult Description
I S n s
V AN
date + number
n - tra Date Adds a number of days to a date
A o
BH date –nnumber
date – date
Date
Number of days
Subtracts a number of days from a date
Subtracts one date from another
date + number/24 Date Adds a number of hours to a date
s a
) h a
ฺ c om
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T
EnumberisofSweeks employed for all
The example in the slide displays the last name and H
the
S which th employee was hired from the current
employees in department 90. It subtracts the T H on
date e the
usthe number of weeks that a worker has been
date (SYSDATE) and divides the result byN7A o
to calculate
t
employed.
R AM ense
K AR le lic
H AN ferab
N IS ans
A VA on-tr
BH n
Lesson Agenda
• Nesting functions
• Number functions
• Working with dates
• Date functions
s a
) h a
ฺ c om
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T
E is S
H
S e th
H
N AT to us
R AM ense
K AR le lic
H AN ferab
N IS ans
A VA on-tr
BH n
Date-Manipulation Functions
Function Result
MONTHS_BETWEEN Number of months between two dates
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2012, Oracle and/or its affiliatesฺ
s a
) h a
ฺ c om
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T
E returnisa Svalue of the DATE data type
H
Date functions operate on Oracle dates. All date functions
Svalue.e th
except MONTHS_BETWEEN, which returns a numeric
T H us
• MONTHS_BETWEEN(date1, M NA Finds
date2): t o the number of months between date1 and
date2. The result can beR positive se If date1 is later than date2, the result is
A eornnegative.
positive; if date1 isA R thanlicdate2, the result is negative. The noninteger part of the
earlier
K b
N a portion
result represents a lethe month.
of
A
H sfern): Adds n number of calendar months to date. The value of n must
• I S
ADD_MONTHS(date,
N -traand n
beAan integer
V can be negative.
A o n
BH• n
NEXT_DAY(date, 'char'): Finds the date of the next specified day of the week
('char') following date. The value of char may be a number representing a day or a
character string.
• LAST_DAY(date): Finds the date of the last day of the month that contains date
The preceding list is a subset of the available date functions. ROUND and TRUNC number functions
can also be used to manipulate the date values as shown below:
• ROUND(date[,'fmt']): Returns date rounded to the unit that is specified by the format
model fmt. If the format model fmt is omitted, date is rounded to the nearest day.
• TRUNC(date[, 'fmt']): Returns date with the time portion of the day truncated to the
unit that is specified by the format model fmt. If the format model fmt is omitted, date is
truncated to the nearest day.
The format models are covered in detail in the lesson titled “Using Conversion Functions and
Conditional Expressions.”
Function Result
MONTHS_BETWEEN 19.6774194
('01-SEP-16','11-JAN-15')
s a
) h a
ฺ c om
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T S to the supplied date value
E oneismonth
In the example in the slide, the ADD_MONTHS functionH adds h
“31-JAN-16” and returns “29-FEB-16.” The function T H Srecognizes
s e t the year 2016 as a leap year and,
uIf you change the input date value to “31-JAN-
N
therefore, returns the last day of the FebruaryA month.t o
15,” the function returns “28-FEB-15.”
R AM ense
For example, display the employee
K lic hire date, number of months employed, six-month
AR lenumber,
review date, first Friday
A Nafter hire
r a bdate, and the last day of the hire month for all employees who have
been employed H
ISfor fewer e
f 150 months.
sthan
N r a n
A VASELECT
o n -temployee_id,
BH n TENURE, ADD_MONTHS hire_date, MONTHS_BETWEEN (SYSDATE, hire_date)
(hire_date, 6) REVIEW, NEXT_DAY (hire_date,
'FRIDAY'), LAST_DAY(hire_date)
FROM employees WHERE MONTHS_BETWEEN (SYSDATE, hire_date) < 150;
Assumption: The date when the below functions were run was 08-JUL-16.
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2012, Oracle and/or its affiliatesฺ
Function Result
ROUND(SYSDATE,'MONTH') 01-JUL-16
ROUND(SYSDATE,'YEAR') 01-JAN-17
TRUNC(SYSDATE,'MONTH') 01-JUL-16
TRUNC(SYSDATE,'YEAR') 01-JAN-16
s a
) h a
ฺ c om
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T
E and date Svalues. When used with dates,
The ROUND and TRUNC functions can be used for number H
S model. h i s
t Therefore, you can round dates to
these functions round or truncate to the specified
T Hformat s e
u dates 1-15 result in the first day of the
N A is month,
the nearest year or month. If the format model t o
current month. Dates 16-31 result inAtheM first day
se of the next month. If the format model is year,
R e n
months 1-6 result in January R
K A1 of the
l e lic year. Months 7-12 result in January 1 of the next year.
current
Example
H AN ferab
Compare the N IS dates
hire
a s all employees who started in 2010. Display the employee number, hire
nfor
A t r
n-month using the ROUND and TRUNC functions.
H AV starting
date, and
n o
B
SELECT employee_id, hire_date,
ROUND(hire_date, 'MONTH'), TRUNC(hire_date, 'MONTH')
FROM employees
WHERE hire_date LIKE '%10';
Quiz Q
Which four of the following statements are true about single-row functions?
a. Manipulate data items
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2012, Oracle and/or its affiliatesฺ
s a
) h a
ฺ c om
a h oo eฺ
@ y u id
b m nt G Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T
E is S
Answer: a, c, f, g H
S e th
H
N AT to us
R AM ense
K AR le lic
H AN ferab
N IS ans
A VA on-tr
BH n
Summary
s a
) h a
ฺ c om
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T
E is S
H
S e th
H
N AT to us
R AM ense
K AR le lic
H AN ferab
N IS ans
A VA on-tr
BH n
Practice 4: Overview
• Creating queries that require the use of numeric, character, and date functions
• Performing calculations of years and months of service for an employee
s a
) h a
ฺ c om
a h oo eฺ
@ y u id
b m nt G
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
r
(r tude
s
T S that are available for character,
This practice provides a variety of exercises using S HE functions
different h i s
number, and date data types. T H use t
N A to
R AM ense
K AR le lic
H AN ferab
N IS ans
A VA on-tr
BH n
s a
) h a
ฺ c om
a h oo eฺ
@ y u id
b m nt G
r
(r tude
s
T
E is S
H
S e th
H
N AT to us
R AM ense
K AR le lic
H AN ferab
N IS ans
A VA on-tr
BH n