Single-Row Functions
Practical 5
Lesson Objectives
o Describe various types of functions that are available
in SQL
o Use single row functions in SELECT statements
o Describe the use of conversion functions
Using Scripts
o Run the following scripts before the practical.
o Northwoods.sql
o HR.sql
3
Dummy Table
o The DUAL is special one row, one column
dummy table present by default in all Oracle
databases.
o The owner of DUAL is SYS (SYS owns the
data dictionary, therefore DUAL is part of the
data dictionary.) but DUAL can be accessed
by every user.
DESCRIBE DUAL;
Dummy Table
Try the following SQL statements:
SELECT 'ABCDEF12345'
FROM DUAL;
Result: ABCDEF12345
SELECT 15+10-5*5/5
FROM DUAL;
Result: 20
SQL Functions
o Two Types:
n Single-Row Function
n Multiple-Row Function
o Single-Row Function
n Operate on single rows only and return one result per row.
n character, number, date, conversion, general
o Multiple-Row Function
n Can manipulate groups of rows to give one result per
group of rows.
n Also known as Group Function.
Single-Row Example
S_ID S_LAST S_FIRST S_MI S_ADDRESS S_CITY S_STATE S_ZIP
1 Miller Sarah M 144 Windridge Blvd. Eau Claire WI 54703
2 Umato Brian D 454 St. John’s Place Eau Claire WI 54702
3 Black Daniel 8921 Circle Drive Bloomer WI 54715
4 Mobley Amanda J 1716 Summit St. Eau Claire WI 54703
5 Sanchez Ruben R 1780 Samantha Court Eau Claire WI 54701
6 Connoly Michael S 1818 Silver Street Elk Mound WI 54712
Single Row:
- Round - Lower - Concat - Instr
- Trunc - Upper - Substr - Replace
- Sign - Initcap - Length - Trim
Multiple-Row Example
COURSE_ID CALL_ID COURSE_NAME CREDITS
1 MIS 101 Intro. To Info. Systems 3
2 MIS 301 Systems Analysis 3
3 MIS 441 Database Management 3
4 CS 155 Programming in C++ 3
5 MIS 451 Web-Based Systems 3
Multiple Row Function:
- Sum
- Avg
- Count
- Max
- Min
Single-Row Number Functions
o ROUND
n Rounds a value to a specified position.
o TRUNC
n Truncates a value to a specified position.
o SIGN
n SIGN function returns a value indicating the sign of a
number.
9
Single-Row Number Functions
SELECT ROUND(45.927,2),
ROUND (45.927,0),
ROUND (45.927),
ROUND(45.927,-1)
FROM DUAL;
ROUND(45.927,2) ROUND(45.927,0) ROUND(45.927) ROUND(45.927,-1)
-----------------------------------------------------------------------------------------------
45.93 46 46 50
10
Single-Row Number Functions
SELECT TRUNC(45.927,2),
TRUNC (45.927,0),
TRUNC (45.927),
TRUNC (45.927,-1)
FROM DUAL;
TRUNC(45.927,2) TRUNC(45.927,0) TRUNC(45.927) TRUNC(45.927,-1)
----------------------------------------------------------------------------------------------
45.92 45 45 40
11
Single-Row Number Functions
SELECT s_id, s_last, SYSDATE - s_dob
FROM student;
SELECT s_id, s_last, (SYSDATE - s_dob) / 365.25
FROM student;
SELECT s_id, s_last,TRUNC((SYSDATE - s_dob) /365.25)
FROM student;
13
Single-Row Character Functions
o Case Manipulation
n LOWER, UPPER, INITCAP
o Character Manipulation
n CONCAT, SUBSTR, LENGTH, INSTR,
REPLACE, TRIM
14
Single-Row Character Functions
o LOWER() - Returns the string with all
characters converted to lowercase letters.
o UPPER() - Returns the string with all
characters converted to uppercase letters.
o INITCAP() - Returns the string with only the
first letter in uppercase for each word.
15
Single-Row Character Functions
o CONCAT() - Concatenates (joins) two
strings.
o SUBSTR() - Returns a string, starting at the
start position, and of the specified length.
o LENGTH() - Returns an integer representing
the string length.
16
Single-Row Character Functions
o INSTR() - Searches a string for a substring and
returns an integer indicating the position of the
character in string that is the first character of this
occurrence.
o REPLACE() - Returns the string with every
occurrence of the search string replaced with the
replacement string.
o TRIM() , LTRIM(), RTRIM()
- Removes all specified characters either from the
beginning or the ending of a string. 17
Case Manipulation
SELECT LOWER('I Love SQL')
FROM DUAL;
SELECT UPPER('I Love SQL')
FROM DUAL;
SELECT INITCAP('I Love SQL')
FROM DUAL;
18
Using Case Manipulation Functions
SELECT term_id, term_desc, status
FROM term;
SELECT term_id, term_desc, INITCAP(status)
FROM term;
19
Using Case Manipulation Functions
SELECT s_last, s_first, s_dob
FROM student
WHERE s_last = 'mobley';
Result: no rows selected
SELECT s_last, s_first, s_dob
FROM student
WHERE LOWER(s_last) = 'mobley'; 20
Character Manipulation
SELECT REPLACE('ABC','B','*')
FROM DUAL;
SELECT CONCAT('ABC','DEF')
FROM DUAL;
SELECT TRIM (' ABC ')
FROM DUAL;
SELECT TRIM(LEADING '0' FROM '000123’)
FROM DUAL; 21
Character Manipulation
TRIM:
22
Character Manipulation
SELECT SUBSTR('ABCDEF',2,3)
FROM DUAL;
SELECT LENGTH('ABCDEFG ')
FROM DUAL;
SELECT INSTR('ABCDEFG','C')
FROM DUAL;
23
Character Manipulation
SELECT bldg_code, room
FROM location
WHERE bldg_code = 'CR';
SELECT CONCAT(bldg_code, room)
FROM location
WHERE bldg_code = 'CR';
24
Concatenation Operator
o Links columns or character strings to other
columns.
o Is represented by two vertical bars, ||
SELECT bldg_code, room
FROM location;
SELECT bldg_code || room
FROM location;
Practice 5.1
Write a SQL statement to show the following result:
Bldg No
-----------------
CR-101
CR-103
CR-105
CR-202
BUS-105
BUS-211
BUS-402
BUS-404
BUS-421
BUS-424
BUS-433
o Try the exercise given.
Working with Dates
o The Oracle database stores dates in an internal
numeric format: century, year, month, day, hours,
minutes, and seconds.
o The default date format: DD-MON-RR.
n Enables you to store 21st-century dates in the 20th
century by specifying only the last two digits of the year
n Enables you to store 20th-century dates in the 21st
century in the same way
Copyright © 2014 – 2015 Iconix Consulting Sdn Bhd. All rights reserved. 28
SYSDATE Function
o SYSDATE is a date function that returns the
current database server date and time.
Copyright © 2014 – 2015 Iconix Consulting Sdn Bhd. All rights reserved. 29
Single-Row Date Functions
o Add_months(date,n)
o Months_between (date1, date2)
o next_day(date, 'Day')
o Last_day (date)
o ROUND(date[, 'fmt'])
o TRUNC(date[, 'fmt'])
Copyright © 2014 – 2015 Iconix Consulting Sdn Bhd. All rights reserved. 30
Single-Row Date Functions
SELECT ADD_MONTHS(sysdate, 2)
FROM dual;
SELECT MONTHS_BETWEEN(hire_date,
SYSDATE)
FROM employees
WHERE employee_id=114;
Copyright © 2014 – 2015 Iconix Consulting Sdn Bhd. All rights reserved. 31
Single-Row Date Functions
SELECT NEXT_DAY(sysdate, 'FRIDAY')
FROM dual;
SELECT LAST_DAY(sysdate)
FROM dual;
Copyright © 2014 – 2015 Iconix Consulting Sdn Bhd. All rights reserved. 32
Practice 5.2
o You next appointment with the dentist is
six months from today. On what day will
you go to the dentist? Name the output
“appointment”.
Copyright © 2014 – 2015 Iconix Consulting Sdn Bhd. All rights reserved. 33
Single-Row Date Functions
SELECT ROUND(SYSDATE, 'MONTH') FROM DUAL;
SELECT ROUND(SYSDATE, 'YEAR') FROM DUAL;
o SELECT ROUND(to_date('15/07/2008','DD/MM/YYYY'), 'MONTH’)
FROM dual;
ROUND(TO_
---------
01-JUL-08
o SELECT ROUND(to_date('16/07/2008','DD/MM/YYYY'), 'MONTH’)
FROM dual;
ROUND(TO_
---------
01-AUG-08
34
Copyright © 2014 – 2015 Iconix Consulting Sdn Bhd. All rights reserved.
Single-Row Date Functions
SELECT TRUNC(SYSDATE, 'MONTH')
FROM DUAL;
SELECT TRUNC (SYSDATE, 'YEAR')
FROM DUAL;
Copyright © 2014 – 2015 Iconix Consulting Sdn Bhd. All rights reserved. 35
Nesting Functions
o Single-row functions can be nested to any level.
o Nested functions are evaluated from deepest level to
the least deep level.
36
Nesting Functions
SELECT LENGTH(SUBSTR(course_name, 9, 5))
FROM COURSE;
37
Conversion Functions
o SQL provides three functions to convert a value
from one data type to another.
n TO_CHAR
n TO_DATE
n TO_NUMBER
38
Numerical Format Models
39
Using the TO_CHAR Function with Numbers
o Translates a value of NUMBER data type to
VARCHAR2 data type.
o TO_CHAR(field_name, 'format_model')
SELECT salary, TO_CHAR(salary, '$99,999.00') SALARY
FROM employees
WHERE last_name like 'E%';
40
Using the TO_CHAR Function with Dates
o translates a value of DATE data type to VARCHAR2
data type.
o TO_CHAR(date, 'format_model')
o The format model:
n Must be enclosed by single quotation marks
n Is case-sensitive
n Can include any valid date format element
n Is separated from the date value by a comma
SELECT to_char(sysdate, 'YYYY' )
FROM dual;
41
Date Format Models
42
Date Format Models
43
Formatting Date/time
SELECT s_id, s_last, s_dob
FROM student;
SELECT s_id, s_last, TO_CHAR(s_dob, 'MM/YY')
Month of Birth
FROM student;
44
Using the TO_DATE Functions
o Convert a character string to a date format using the
TO_DATE function:
n TO_DATE(char[, 'format_model'])
45
Using the TO_DATE Functions in Search Expressions
*
SELECT s_id, s_first, s_dob
ERROR at line 2:
FROM student ORA-01843: not a valid
WHERE s_dob = '07/14/1985'; month
SELECT s_id, s_first, s_dob
FROM student
WHERE s_dob = TO_DATE('07/14/1985', 'MM/DD/YYYY');
SELECT s_id, s_first, s_dob
FROM student
WHERE s_dob = TO_DATE('07/14/85', 'MM/DD/YY');
SELECT s_id, s_first, s_dob
FROM student
WHERE s_dob = TO_DATE('07/14/85', 'MM/DD/YYYY');
Current Year Specified Date RR Format YY Format
1995 02-Nov-76
1995 02-Nov-14
2001 02-Nov-76
2001 02-Nov-14
47
Using the TO_DATE Functions in Search Expressions
SELECT s_id, s_first, s_dob
FROM student
WHERE s_dob = TO_DATE('07/14/1985', 'MM/DD/RRRR');
SELECT s_id, s_first, s_dob
FROM student
WHERE s_dob = TO_DATE('07/14/85', 'MM/DD/RR');
SELECT s_id, s_first, s_dob
FROM student
WHERE s_dob = TO_DATE('07/14/85', 'MM/DD/RRRR');
Using the TO_DATE Functions in Search Expressions
Oracle default date format :
DD-MON-YYYY / DD-MON-RR
SELECT s_id, s_first, s_dob
FROM student
WHERE s_dob = '14-07-1985';
SELECT s_id, s_first, s_dob
FROM student
WHERE s_dob = '14-JUL-1985';
SELECT s_id, s_first, s_dob
FROM student
WHERE s_dob = '14-JUL-85';
Practice 5.3
o Write a query to retrieve to produce the following
output.
Birthday
-------------------------------------------------------------
Miller Sarah's birthday is on 14, July, 1985.
Umato Brian's birthday is on 19, August, 1985.
Black Daniel's birthday is on 10, October, 1982.
Mobley Amanda's birthday is on 24, September, 1986.
Sanchez Ruben's birthday is on 20, November, 1986.
Connoly Michael's birthday is on 4, December, 1986.
Using the TO_NUMBER Functions
o Convert a character string to a number format using
the TO_NUMBER function:
n TO_NUMBER(char[, 'format_model'])
51
Using the TO_NUMBER Function
o SELECT TO_NUMBER('1210.73', '9999.99’)
FROM dual;
Result: 1210.73
o SELECT TO_NUMBER('546', '999')
Result: 546
o SELECT TO_NUMBER(room)
FROM location;
Do it yourself
1. Retrieve the student last name who were born in
‘September’.
2. Get all employees last name and hired date, who
were hired in ‘Jan’.
3. Get all employees last name and hired date, who
were hired, arrange the hired date in alphabetical
order in month regardless of year.
o Try the exercise given.
