Database Management System 1
Single-Row Function
Lesson Objective
After completing this lesson, the student should
be able to:
• Describe the various types of functions available in SQL
• Use the character, number, and date functions in SELECT
statements
Functions are a very powerful feature of SQL. They can be used
to do the following:
• Perform calculations on data
• Modify individual data items
• Manipulate output for groups of rows
• Format dates and numbers for display
• Convert column data types
2 Types of Functions
Functions
Single – Row Multiple – Row
Function Function
Single-Row Functions
Single-Row
Function
Character Function Number Function
MOD
Case Character
Manipulation
TRUNC
Manipulation
ROUND
Case Conversion function:
Function Result
LOWER(‘Database Management database management system
System’)
UPPER(‘Database Management DATABASE MANAGEMENT
System’) SYSTEM
INITCAP(‘Database Management Database Management System
System’)
• Where:
• LOWER: Converts mixed-case or uppercase character strings to lowercase
• UPPER: Converts mixed-case or lowercase character strings to uppercase
• INITCAP: Converts the first letter of each word to uppercase and the
remaining letters to lowercase
Using Case-Conversion function
• Example: UPPER
SELECT UPPER(LNAME||','||FNAME) AS "NAME"
FROM AUTHORS
WHERE LNAME LIKE'S%';
• Example: lower
SELECT LOWER(LNAME||','||FNAME) AS "NAME"
FROM AUTHORS
WHERE LNAME LIKE'S%';
• Example: lower
SELECT INITCAP(LNAME||','||FNAME) AS "NAME“
FROM AUTHORS
WHERE LNAME LIKE'S%';
Character Manipulation Function
Function Result
CONCAT(‘Data’,’Base’) DataBase
SUBSTR(Database System, 1, 8) Database
LENGTH(‘Database’) 8
INSTR(Database,’b’) 5
LPAD(price,6,’$’) $$$360
RPAD(proce,6,’$’) 360$$$
REPLACE(Database, ‘a’,’c’) Dctcbce
TRIM(‘D’ from ‘Database’) atabase
Character Manipulation Function cont.
• Where:
• CONCAT: Joins values together (You are limited to using two parameters with CONCAT.) instead used
|| (double bars) if it contains two or more columns and character literal string
• SUBSTR: Extracts a string of determined length
• LENGTH: Shows the length of a string as a numeric value
• INSTR: Finds the numeric position of a named character
• LPAD: Returns an expression left-padded to the length of n characters with a character expression
• RPAD: Returns an expression right-padded to the length of n characters with a character expression
• TRIM: Trims leading or trailing characters (or both) from a character string (If
trim_character or trim_source is a character literal, you must enclose it within
single quotation marks.)
Using Character Manipulation Function
• Example: CONCAT
SELECT CONCAT(LNAME,FNAME), YR_PUB
FROM AUTHORS
WHERE YR_PUB = 2010;
• Example: SUBSTR
SELECT LNAME, FNAME, BOOK
FROM AUTHORS
WHERE SUBSTR(BOOK,6)='ING';
Using Character Manipulation Function cont.
• Example: LENGTH, INSTR
SELECT BOOK,LENGTH(BOOK), INSTR(BOOK,'A')
FROM AUTHORS
WHERE BOOK LIKE 'A%';
• Example: RPAD, LPAD
SELECT BOOK, LPAD(YR_PUB,7,'$'), RPAD(YR_PUB,7,'@')
FROM AUTHORS
WHERE BOOK LIKE '%ING%';
Using Character Manipulation Function: cont.
• Example: REPLACE
SELECT REPLACE(FNAME,'A','T')
FROM AUTHORS;
• Example: TRIM
SELECT BOOK, TRIM('A' FROM BOOK)
FROM AUTHORS;
Number Function
Function Result
ROUND(75. 46,1) 75.5
TRUNC(75.46,1) 75
MOD(100/3) 10
• Where:
• ROUND: Rounds value to a specified decimal
• TRUNC: Truncates value to a specified decimal
• MOD: Returns remainder of division
Dual Table
• The DUAL table is owned by the user SYS and can be accessed by all
users. It contains one column, DUMMY, and one row with the value X.
• The DUAL table is useful when you want to return a value only once
(for example, the value of a constant, pseudocolumn, or expression
that is not derived from a table with user data).
• The DUAL table is generally used for completeness of the SELECT
clause syntax, because both SELECT and FROM clauses are mandatory,
and several calculations do not need to select from the actual tables.
Using Number Function
• Example: ROUND
SELECT ROUND(75.475,2),ROUND(75.475,1), ROUND(75.475,-1)
FROM DUAL;
• Example: TRUNC
SELECT TRUNC(75.475,2),TRUNC(75.475,1),TRUNC(75.475,-1)
FROM DUAL;
• Example: MOD
SELECT MOD(1000,300)
FROM DUAL;
Lesson Summary:
In this lesson, you should have learned how to:
• Perform calculations on data using functions
• Modify case and character values.
