Introduction to SQL Functions
S
I
N
G
L
E
-
R
O
W
F
U
N
C
T
I
O
N
S
L
e
c
t
u
r
e
9
S
Q
L
F
u
n
c
t
i
o
n
s
Functions are very powerful feature of SQL and can be
used to do the following:
Perform a calculation on data
Modify individual data items
Manipulate output of groups of rows
Format dates and numbers for display
Convert column data types
T
w
o
T
y
p
e
s
o
f
S
Q
L
F
u
n
c
t
i
o
n
s
T
h
e
r
e
a
r
e
t
w
o
d
i
s
t
i
n
c
t
t
y
p
e
s
o
f
f
u
n
c
t
i
o
n
s
:
S
i
n
g
l
e
-
R
o
w
F
u
n
c
t
i
o
n
s
These functions operate on single rows only and return one
result per row. There are different types of single-row
functions
M
u
l
t
i
p
l
e
-
R
o
w
F
u
n
c
t
i
o
n
s
Functions can manipulate groups of rows to give one result
per group of rows. These functions are known as
group
functions
S
i
n
g
l
e
-
R
o
w
F
u
n
c
t
i
o
n
s
S
i
n
g
l
e
r
o
w
f
u
n
c
t
i
o
n
s
:
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
Can be used
in SELECT
,
WHERE
, and
ORDER
BY
clauses
Accept arguments which can be a column or an
expression
Syntax:
f
u
n
c
t
i
o
n
_
n
a
m
e
[
(
a
r
g
1
,
a
r
g
2
,
.
.
.
)
]
S
i
n
g
l
e
-
R
o
w
F
u
n
c
t
i
o
n
s
T
h
i
s
l
e
s
s
o
n
c
o
v
e
r
s
t
h
e
f
o
l
l
o
w
i
n
g
s
i
n
g
l
e
-
r
o
w
f
u
n
c
t
i
o
n
s
:
Character functions
: accept character input and can
return both character and number values
Number functions
: Accept numeric input and return
numeric values
C
h
a
r
a
c
t
e
r
F
u
n
c
t
i
o
n
s
1.
LOWER
2.
UPPER
3.
INITCAP
1.
SUBSTR
2.
LENGTH
:
C
h
a
r
a
c
t
e
r
F
u
n
c
t
i
o
n
s
C
a
s
e
M
a
n
i
p
u
l
a
t
i
o
n
F
u
n
c
t
i
o
n
s
T
h
e
s
e
f
u
n
c
t
i
o
n
s
c
o
n
v
e
r
t
c
a
s
e
f
o
r
c
h
a
r
a
c
t
e
r
s
t
r
i
n
g
s
.
S
e
e
(
E
x
a
m
p
l
e
1
,
E
x
a
m
p
l
e
2
)
:
C
h
a
r
a
c
t
e
r
F
u
n
c
t
i
o
n
s
C
a
s
e
M
a
n
i
p
u
l
a
t
i
o
n
F
u
n
c
t
i
o
n
s
:
E
x
a
m
p
l
e
1
SELECT 'The job id for '||UPPER(last_name)||' is '
||LOWER(job_id) AS "EMPLOYEE DETAILS"
FROM employees;
:
E
x
a
m
p
l
e
2
•
D
i
s
p
l
a
y
t
h
e
e
m
p
l
o
y
e
e
n
u
m
b
e
r
,
n
a
m
e
,
a
n
d
d
e
p
a
r
t
m
e
n
t
n
u
m
b
e
r
f
o
r
e
m
p
l
o
y
e
e
H
i
g
g
i
n
s
:
C
h
a
r
a
c
t
e
r
F
u
n
c
t
i
o
n
s
:
C
h
a
r
a
c
t
e
r
M
a
n
i
p
u
l
a
t
i
o
n
F
u
n
c
t
i
o
n
s
C
h
a
r
a
c
t
e
r
F
u
n
c
t
i
o
n
s
:
C
h
a
r
a
c
t
e
r
M
a
n
i
p
u
l
a
t
i
o
n
F
u
n
c
t
i
o
n
s
•
T
h
e
s
e
f
u
n
c
t
i
o
n
s
m
a
n
i
p
u
l
a
t
e
c
h
a
r
a
c
t
e
r
s
t
r
i
n
g
s
.
F
o
r
e
x
a
m
p
l
e
:
C
h
a
r
a
c
t
e
r
F
u
n
c
t
i
o
n
s
:
C
h
a
r
a
c
t
e
r
M
a
n
i
p
u
l
a
t
i
o
n
F
u
n
c
t
i
o
n
s
(
C
o
n
t
.
)
E
x
a
m
p
l
e
:
S
E
L
E
C
T
e
m
p
l
o
y
e
e
_
i
d
,
j
o
b
_
i
d
,
L
E
N
G
T
H
(
l
a
s
t
_
n
a
m
e
)
F
R
O
M
e
m
p
l
o
y
e
e
s
W
H
E
R
E
S
U
B
S
T
R
(
j
o
b
_
i
d
,
4
)
=
'
R
E
P
'
;
N
u
m
b
e
r
F
u
n
c
t
i
o
n
s
R
O
U
N
D
:
R
o
u
n
d
s
v
a
l
u
e
t
o
s
p
e
c
i
f
i
e
d
d
e
c
i
m
a
l
(
E
x
a
m
p
l
e
3
)
S
y
n
t
a
x
:
ROUND(column
|
expression,
n
) :Rounds the column, expression, or
value to n decimal places, or,
if
n
is omitted, no decimal places.
E
x
a
m
p
l
e
:
R
O
U
N
D
(
4
5
.
9
2
6
,
2
)
4
5
.
9
3
N
u
m
b
e
r
F
u
n
c
t
i
o
n
s
T
R
U
N
C
:
T
r
u
n
c
a
t
e
s
v
a
l
u
e
t
o
s
p
e
c
i
f
i
e
d
d
e
c
i
m
a
l
(
E
x
a
m
p
l
e
4
)
S
y
n
t
a
x
:
TRUNC(column
|
expression,
n
) Truncates the column, expression, or
value to n decimal places, or,
if
n
is omitted, then n defaults to zero
E
x
a
m
p
l
e
:
T
R
U
N
C
(
4
5
.
9
2
6
,
2
)
4
5
.
9
2
N
u
m
b
e
r
F
u
n
c
t
i
o
n
s
M
O
D
:
R
e
t
u
r
n
s
r
e
m
a
i
n
d
e
r
o
f
d
i
v
i
s
i
o
n
(
E
x
a
m
p
l
e
5
)
S
y
n
t
a
x
:
MOD(m,n) Returns the remainder of m divided by n
E
x
a
m
p
l
e
:
M
O
D
(
1
6
0
0
,
3
0
0
)
1
0
0
E
x
a
m
p
l
e
3
E
x
a
m
p
l
e
4
E
x
a
m
p
l
e
5
SQL functions are a powerful feature that allows you to perform calculations, modify data, manipulate output, and format dates and numbers in SQL queries. There are two main types of functions: Single-Row Functions and Multiple-Row Functions. Single-Row Functions operate on individual rows, while Multiple-Row Functions work on groups of rows. Single-Row Functions can manipulate data items, accept arguments, and return one value per row. They can be used in various clauses like SELECT, WHERE, and ORDER BY. This lesson covers different types of Single-Row Functions, including Character and Number functions for manipulating text and numerical data.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
SINGLE-ROW FUNCTIONS Lecture 9
SQL Functions Functions are very powerful feature of SQL and can be used to do the following: Perform a calculation on data Modify individual data items Manipulate output of groups of rows Format dates and numbers for display Convert column data types
Two Types of SQL Functions There are two distinct types of 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 Multiple-Row Functions Functions can manipulate groups of rows to give one result per group of rows. These functions are known as group 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 Can be used in SELECT, WHERE, and ORDER BY clauses Accept arguments which can be a column or an expression Syntax: function_name [(arg1, arg2,...)]
Single-Row Functions This lesson covers the following single -row functions: Character functions: accept character input and can return both character and number values Number functions: Accept numeric input and return numeric values
Character Functions Character Functions Case- Character- manipulation manipulation 1. LOWER 2. UPPER 3. INITCAP 1. SUBSTR 2. LENGTH
Character Functions Case Manipulation Functions : These functions convert case for character strings. See (Example 1, Example 2) Function result sql course LOWER( SQL Course ) SQL COURSE UPPER( SQL Course ) Sql Course INITCAP( SQL Course )
Character Functions Case Manipulation Functions :
Example1 : SELECT 'The job id for '||UPPER(last_name)||' is ' ||LOWER(job_id) AS "EMPLOYEE DETAILS" FROM employees;
Example2 : Display the employee number, name, and department number for employee Higgins:
Character Functions: Character Manipulation Functions Function Purpose LENGTH(Column|expression) Returns the number of characters in the expression SUBSTR(column|expression,m [,n]) Returns specified characters from character value starting at character position m,n character long (if m is negative the count starts and the end of the character value . If n is omitted all characters to the end of the string are returned
Character Functions: Character Manipulation Functions These functions manipulate character strings. For example: Function Result LENGTH('HelloWorld') 10 SUBSTR('HelloWorld',1,5) Hello
Character Functions: Character Manipulation Functions (Cont.) Example: SELECT employee_id, job_id,LENGTH (last_name) FROM employees WHERE SUBSTR(job_id, 4) = 'REP';
Number Functions ROUND: Rounds value to specified decimal (Example 3) Syntax: ROUND(column|expression, n) :Rounds the column, expression, or value to n decimal places, or, if n is omitted, no decimal places. Example: ROUND(45.926, 2) 45.93
Number Functions TRUNC: Truncates value to specified decimal (Example 4) Syntax: TRUNC(column|expression,n) Truncates the column, expression, or value to n decimal places, or, if n is omitted, then n defaults to zero Example: TRUNC(45.926, 2) 45.92
Number Functions MOD: Returns remainder of division (Example 5) Syntax: MOD(m,n) Returns the remainder of m divided by n Example: MOD(1600, 300) 100
