ShahCh06v3.

qxd 4/16/04 11:54 AM Page 132

Working with Tables:

Functions and Grouping

IN THIS CHAPTER . . .

● Data retrieval statements are written using single-row functions.

● Numeric, character, conversion, and miscellaneous functions are introduced
and used.
● Data are manipulated using aggregate or group functions.
● Various clauses are used with data retrieval queries for filtering and grouping
of data.

BUILT-IN FUNCTIONS

The built-in functions provide a powerful tool for the enhancement of a basic query.
A function takes zero or more arguments and returns a single value. Just like other
software and programming languages, the functions covered in this section are specific
to Oracle. Functions are used for performing calculations on data, converting data,
modifying individual data, manipulating a group of rows, and formatting columns. In
Oracle’s SQL, there are two types of functions:
1. Single-row functions, which work on columns from each row and return
one result per row.
2. Group functions or aggregate functions, which manipulate data in a group
of rows and return single result.
ShahCh06v3.qxd 4/16/04 11:54 AM Page 133

Built-In Functions 133

Single-Row Functions

The single-row functions take different types of arguments, work on a data item from
each row, and return one value for each row. The arguments are in the form of a con-
stant value, variable name, column, and/or expression. The value returned by a func-
tion may be of a different type than the argument(s) supplied. The general syntax is

Function(column | expression [, argument1, argument2, . . . ])

where function is the name of the function, column is a column from a table,
expression is a character string or a mathematical expression, and argument is any
argument used by the function.
There are various types of single-row functions:

● Character functions take a character string or character-type column as an

argument and return a character or numeric value.
● Number functions take a number or number-type column as an argument
and return a numeric value.
● Date functions take a date value or date-type column as an argument and
return date-type data. (Exception: The MONTHS_BETWEEN function re-
turns a numeric value.)
● Conversion functions convert value from one data type to another.
● General functions perform different tasks.

Character Functions. The character functions perform case conversion

or character manipulation. Figure 6-1 has a list of character functions and their use.
The case-conversion character functions change a string or character-type column
data’s case. For example,

UPPER(‘Oracle’) : ‘ORACLE’
LOWER(‘DaTaBaSe SyStEmS’) : ‘database systems’
INITCAP(‘DaTaBaSe SyStEmS’) : ‘Database Systems’

For example, Figure 6-2 shows the use of character functions UPPER, LOWER,
and INITCAP in the SELECT clause to display columns with different cases.
Often, more than one data-entry person will populate a table. One person en-
ters names in all uppercase, and the other uses proper case. This could become a
nightmare for data retrieval query writers if not for functions. Functions are very
useful in the WHERE clause’s conditions as well.
For example, in Figure 6-3, a query is issued with the condition WHERE State
= ‘ny’, and it resulted in a “no row selected” message.The table does contain students
from New York state. The problem here is the case used in entering state values. The
same query is rewritten with condition WHERE UPPER1State2 = ‘NY’, and it re-
turned two student names. The use of the UPPER function converted the value in the
ShahCh06v3.qxd 4/16/04 11:54 AM Page 134

134 Chap. 6 Working with Tables: Functions and Grouping

Character Function Use

UPPER1column  expr2 Converts each letter to uppercase.

LOWER1column  expr2 Converts each letter to lowercase.
INITCAP1column  expr2 Converts character value to the proper case
(i.e., first character of each word is converted to
uppercase and the rest to lowercase).
CONCAT1column  expr, column  expr2 Joins the first value to the second value. Similar to
the 7 operator discussed earlier.
SUBSTR1column  expr, x, y2 Returns a substring, starting at character position
x, and returns y number of characters.
SUBSTR1column  expr, z2 Returns a substring, starting at character
position z and going to the end of string.
INSTR1column  expr, c2 Returns the position of the supplied character.
LTRIM1column  expr, c2 Removes the leading supplied character.
RTRIM1column  expr, c2 Removes the trailing supplied character.
TRIM1‘c’ FROM column  expr2 Removes the leading and trailing characters.
TRIM(column) Removes the leading and trailing spaces only.
LENGTH1column  expr2 Returns the number of characters.
LPAD1column  expr, n, ‘str’2 Pads the value with ‘str’ to the left to a total width
of n.
RPAD1column  expr, n, ‘str’2 Pads the value with ‘str’ to the right to a total width
of n.
REPLACE1column  expr, c, r2 Replaces substring c, if present in the column or
expression, with string r.

Figure 6-1 Character functions.

SQL> SELECT UPPER(Lname), LOWER(Fname),

2 INITCAP(Fname || ’ ’ || Lname)
3 FROM employee;

UPPER(LNAME) LOWER(FNAME) INITCAP(FNAME||”||LNAME)

--------------- --------------- ------------------------------
SMITH john John Smith
HOUSTON larry Larry Houston
ROBERTS sandi Sandi Roberts
MCCALL alex Alex Mccall
DEV derek Derek Dev
SHAW jinku Jinku Shaw
GARNER stanley Stanley Garner
CHEN sunny Sunny Chen

8 rows selected.

SQL>

Figure 6-2 Character functions in SELECT.

ShahCh06v3.qxd 4/16/04 11:54 AM Page 135

Built-In Functions 135

SQL> SELECT Last, First FROM student

2 WHERE State=’ny’;

no rows selected

SQL> SELECT Last, First FROM student

2 WHERE UPPER(State) = ‘NY’;

LAST FIRST
--------------- ---------------
Tyler Mickey
Lee Brian

SQL>

Figure 6-3 Character function in WHERE.

column to uppercase, and it was then compared to the value NY, which has same case.
The condition can be written as WHERE LOWER 1State2 = ‘ny’ instead.
A character function is used in various SELECT clauses, including the
ORDER BY clause. The LENGTH function returns the length of a character col-
umn or string literal. Suppose we want to see names in ascending order by length of
names. The clause will use ORDER BY LENGTH(Last) instead of just ORDER BY
Last, as shown in Figure 6-4.

SQL> SELECT Last, First FROM student

2 ORDER BY LENGTH(Last);

LAST FIRST
--------------- ---------------
Lee Brian
Diaz Jose
Khan Amir
Tyler Mickey
Patel Rajesh
Rickles Deborah

6 rows selected.

SQL>

Figure 6-4 Character function in ORDER BY.

In Oracle9i, the LENGTH function is enhanced with other functions like

LENGTHB (to get length in bytes instead of characters) and LENGTHC (to get
length in unicode).
The character manipulation functions manipulate a character-type value to re-
turn another character- or numeric-type result. For example,
CONCAT(‘New’, ‘York’) : ‘NewYork’
SUBSTR(‘HEATER’, 2, 3) : ‘EAT’
ShahCh06v3.qxd 4/16/04 11:54 AM Page 136

136 Chap. 6 Working with Tables: Functions and Grouping

INSTR(‘abcdefg’, ‘d’) : 4
LTRIM(‘00022345’, ‘0’) : ‘22345’
RTRIM(‘0223455’, ‘5’) : ‘02234’
TRIM(‘ ‘FROM’ Monroe’) : ‘Monroe’
LENGTH(‘Oracle9i’) : 8
LPAD(265000, 9, ‘$’) : $$$265000
RPAD(265000, 9, ‘*’ ) : 265000***
REPLACE(‘Basketball’, ‘ket’, ‘e’) : ‘Baseball’

In Oracle9i, the INSTR function is enhanced to take more arguments:

SELECT INSTR(‘CORPORATE FLOOR DOOR’, ‘OR’ 1, 2) FROM dual;

where we are looking for string ‘OR’ and function is asked to start at the first char-
acter from the left to look for the second occurrence of the string. The result is 5, as
the second ‘OR’ starts at position 5. If argument 1 is changed to -3, the function will
start at the third character from right and search in the reverse direction.

Numeric Functions. The numeric functions take numeric value(s) and re-
turn a numeric value. The ROUND function rounds the value, expression, or col-
umn to n decimal places. If n is omitted, zero decimal place is assumed. If n is
negative, rounding takes place to the left side of the decimal place. For example,

ROUND(25.465, 2) = 25.47
ROUND(25.465, 0) = 25
ROUND(25.465, –1) = 30

The TRUNC function truncates the value, expression, or column to n decimal

places. If n is not supplied, zero decimal place is assumed. If n is negative, truncation
takes place to the left side of the decimal place. For example,

TRUNC(25.465, 2) = 25.46
TRUNC(25.465, 0) = 25
TRUNC(25.465, –1) = 20

The POWER function finds the power of a number 1np2. For example,

POWER(2, 4) = 16
POWER(5, 3) = 125

The ABS function returns the absolute value of a column, expression, or value.
For example,

ABS( –10) = 10
ShahCh06v3.qxd 4/16/04 11:54 AM Page 137

Built-In Functions 137

The MOD function finds the integer remainder of x divided by y. It ignores the
quotient. For example,

MOD(5, 2) = 1
MOD(3, 5) = 3
MOD(8, 4) = 0

The SIGN function returns -1 for a negative number, 1 for a positive number,
and 0 for a zero. For example,

SIGN(-50) = –1
SIGN(+43) = 1
SIGN(0) = 0

The FLOOR function is similar to the TRUNC function, and the CEIL func-
tion is similar to the ROUND function. However, both take one argument instead
of two. For example,

FLOOR(54.7) = 54
CEIL(54.7) = 55

There is a difference in CEIL function, because it always returns the next higher in-
teger value. For example,

ROUND (54.3) = 54
CEIL (54.3) = 55

Figure 6-5 shows the use of numeric functions, and Figure 6-6 explains their
use. Did you notice the table name in Figure 6-5? The table is called DUAL, which
is provided by Oracle. The DUAL table is owned by user SYS, and it is available to
all users. The DUAL table is useful when you want to find the outcome of a function
and the argument is not taken from any table. The DUAL table can also be used
perform arithmetic expressions. For example,

SELECT 25000 * 0.25 FROM DUAL;

The DUAL table contains a single column called DUMMY and a single row with
value X (see Fig. 6-7).

Date Functions. We already know that Oracle stores dates internally with
day, month, year, century, hour, minute, and second information.The default date display
format is DD-MON-YY.There is a very useful date function called SYSDATE that does
not take any arguments. SYSDATE returns the system’s current date. For example,
SELECT SYSDATE
FROM DUAL;
ShahCh06v3.qxd 4/16/04 11:54 AM Page 138

138 Chap. 6 Working with Tables: Functions and Grouping

SQL> SELECT ROUND(5.55, 1), TRUNC(5.5), SIGN(-5.5), MOD(5,2),

2 ABS(-5), POWER(3, 4), FLOOR(5.5), CEIL(5.5)
3 FROM DUAL;

ROUND(5.55,1) TRUNC(5.5) SIGN(-5.5) MOD(5,2) ABS(-5) POWER(3,4) FLOOR(5.5) CEIL(5.5)

----------- --------- -------- ------- ------ ---------- -------- -------
5.6 5 –1 1 5 81 5 6

SQL>

Figure 6-5 Using numeric functions.

Numeric Function Use

ROUND1column  expr, [n]2 Rounds the column or expression to n decimal places.

TRUNC1column  expr, [n]2 Truncates the column or expression to n decimal places.
POWER(n, p) Returns n raised to power p1np2.
ABS(n) Returns the absolute value of n.
MOD(x, y) Returns the integer remainder of x/y.
SIGN(value) Returns 1 for positive, - 1 for negative and 0 for a zero.
FLOOR(value) Returns the largest integer less than or equal to value.
CEIL(value) Returns the smallest integer greater than or equal to value.

Figure 6-6 Numeric functions.

SQL> DESCRIBE DUAL

Name Null? Type

------------------------------ -------- ----------
DUMMY VARCHAR2(1)

SQL> SELECT * FROM dual;

D
-
X

SQL>

Figure 6-7 DUAL table.

This query will display the current date.You can get more information about day, date
and time by using a format mask with SYSDATE function. Try the following state-
ment, which will make more sense after format masks are explained in Fig. 6-15:
SELECT TO_CHAR(SYSDATE, ’DY, MONTH DD, YYYY HH:MI:SS P.M.’)
FROM DUAL;
ShahCh06v3.qxd 4/16/04 11:54 AM Page 139

Built-In Functions 139

Similarly, the DUAL table can be used to display the outcome of any character and
number functions or an arithmetic expression.
The date-type column is very important. You can derive a lot of information
from date columns by performing “date arithmetic.” As you see in Figure 6-8, you
can add or subtract a number of days to or from a date to get a new resulting date.
You can also add a number of hours to a date. If you have two dates, you can find the
gap in days between them.

Date Expression Result

Date  number Adds a number of days to a date.

Date  number Substracts a number of days from a date.
Date  number/24 Adds a number of hours to a date.
Date1  Date2 Gives the number of days between two
dates.

Figure 6-8 Date arithmetic.

In Figure 6-9, we have an expression 1SYSDATE - BirthDate2 that finds the

difference in days. Then, we divide the number of days by 365 to convert it to years.

SQL> SELECT Last, First, (SYSDATE – BirthDate) / 365 AGE

2 FROM student;

LAST FIRST AGE

--------------- --------------- ----------
Diaz Jose 20.8289038
Tyler Mickey 19.7330134
Patel Rajesh 17.9960271
Rickles Deborah 33.1521915
Lee Brian 18.0343832
Khan Amir 19.4289038

6 rows selected.

SQL>

Figure 6-9 Age calculation from birth date.

To take leap years into consideration, we can divide by 365.25 days instead of by
365. The resulting age has a decimal value.
You can truncate (with the TRUNC function) the result to zero decimal places,
and the age will be a whole number. The modified expression will look like this:
TRUNC((SYSDATE – BirthDate) / 365.25)
ShahCh06v3.qxd 4/16/04 11:54 AM Page 140

140 Chap. 6 Working with Tables: Functions and Grouping

Similarly, we can find the number of months or number of weeks by dividing

days by 30 or 7, respectively.
The common date functions and their use are given in Figure 6-10. The func-
tion MONTHS_BETWEEN returns a number. If date1 is later than date2, the result
is positive; otherwise, the result is negative. The decimal part in the result is because
of the portion of the month or extra days of the month. It is useful in finding the
delay between delivery date and payment date. For example,
MONTHS_BETWEEN(‘02-DEC-03’, ‘04-APR-03’) → 7.93548387

Date Function Use

MONTHS_BETWEEN(date1, date2) Finds the number of months between two dates.

ADD_MONTHS(date, m) Adds calendar months to a date.
NEXT_DAY (date, ‘day’) Finds the next occurrence of a day from the given
date.
LAST_DAY(date) Returns the last day of the month.
ROUND(date [, ‘format’]) Rounds the date to the nearest day, month, or
year.
TRUNC(date [, ‘format’ ]) Truncates the date to the nearest day, month, or
year.
EXTRACT1YEAR  MONTH  DAY FROM date2 Extracts the year, month, or day from a date
value.
NEXT_TIME(date, existing timezone, newtimezone) Returns the date in different time zone, such as
EST or PST.

Figure 6-10 Date functions.

The function ADD_MONTHS adds the number of months supplied as a second

argument. The number must be an integer value. It can be positive or negative. For
example, if an item is shipped today and payment is due in three months, what is the
payment date?
ADD_MONTHS(‘10-MAY-03’, 3) → ‘10-AUG-03’

The function NEXT_DAY returns the next occurrence of a day of the week
following the date supplied. The second argument could be a number in quotes or a
day of the week. For example,
NEXT_DAY (‘14-OCT-03’, ‘SUNDAY’) → ‘19-OCT-03’
NEXT_DAY(‘14-OCT-03’, ‘TUESDAY’) ‘21-OCT-03’

The function LAST_DAY finds the last date of the month for the date sup-
plied as an argument. If something is due by the end of this month, what is that
date? For example,

LAST_DAY(‘05-FEB-04’) → ’29-FEB-04’
ShahCh06v3.qxd 4/16/04 11:54 AM Page 141

Built-In Functions 141

The ROUND function rounds a date based on the format specified. If a for-
mat is missing, rounding is to the nearest day. For example,
ROUND(TO_DATE(‘07/20/03’, ‘MM/DD/YY’), ‘MONTH’) → ‘01-AUG-03’

Here, the date is nearer to August 1 than to July 1. In the next example, the
date is nearest to the first of next year:

ROUND(TO_DATE(‘07/20/03’, ‘MM/DD/YY’), ‘YEAR’) → ‘01-JAN-04’

The TRUNC function truncates the date to the nearest format specified. Trun-
cation to the nearest month returns the first day of the date’s month, and truncation
to the nearest year returns the January 1 of the date’s year. For example,

TRUNC(TO_DATE(‘07/20/03’, ‘MM/DD/YY’), ‘MONTH’) → ‘01-JUL-03’

TRUNC(TO_DATE(‘07/20/03’, ‘MM/DD/YY’), ‘YEAR’) → ‘01-JAN-03’

The EXTRACT function extracts year, month, or day from a date value.For
example,

SELECT EXTRACT(MONTH FROM sysdate),

EXTRACT(DAY FROM sysdate),
EXTRACT(YEAR FROM sysdate) FROM dual;

The following is a list of a few more date- and time-related functions intro-
duced in Oracle9i:

CURRENT_DATE—returns the current date the in session’s time zone.

CURRENT_TIMESTAMP—returns the current date and time in the ses-
sion’s time zone.
DBTIMEZONE—returns the value of the database’s time zone.
SESSIONTIMEZONE—returns the current session’s time zone.
SYSTIMESTAMP—returns the date and time in the time zone of the database.

Other Functions. The NVL function converts a null value to an actual

value supplied as an argument. The second argument is enclosed within the single
quotation marks for columns with DATE, CHAR, or VARCHAR2 data types. The
general syntax is
NVL (column, value)

If the value in a column is null, convert it to a specified value. For example,

NVL(Commission, 0)
NVL(HireDate, ‘01-JAN-03’)
NVL(PreReq, ‘None’)
ShahCh06v3.qxd 4/16/04 11:54 AM Page 142

142 Chap. 6 Working with Tables: Functions and Grouping

If the commission amount is null, convert it to zero. If HireDate is not entered,

use ‘01-JAN-03’ for it. If prerequisite is null, use ‘None’.
Now, we will revisit our query in Fig. 5-18, where we tried to add Salary and
Commission columns. The total was blank for employees without a value in the
Commission column. Remember, any number plus a null value is equal to null. Let
us rewrite the same query using the NVL function (see Fig. 6-11).

SQL> SELECT Lname, Fname,

2 Salary + NVL(Commission, 0) “Total Salary”
3 FROM employee;

LNAME FNAME Total Salary

--------------- --------------- ------------
Smith John 300000
Houston Larry 160000
Roberts Sandi 75000
McCall Alex 66500
Dev Derek 100000
Shaw Jinku 27500
Garner Stanley 50000
Chen Sunny 35000

8 rows selected.

SQL>

Figure 6-11 Arithmetic with the NVL function.

An extension of the NVL function is the NVL2 function. It takes three param-
eters instead of the two parameters used by NVL function. The NVL2 function
checks for null as well as not null values. If the column has a not null value, the sec-
ond parameter is displayed. If the column has a null value, the third parameter is dis-
played. The general syntax is
NVL2(column, notnullvalue, nullvalue)

For example,
NVL2(PreReq, ‘YES’, ‘NO’)

If prerequisite has a not null value, YES is displayed. If prerequisite is null, NO

is displayed.
Another similar function is COALESCE. It is also an extension to the NVL
function. The NVL function specifies a single alternative for a null value, whereas
the COALESCE function provides multiple alternatives. The general syntax is

COALESCE(column, alternative1, alternative2, . . . )

ShahCh06v3.qxd 4/16/04 11:54 AM Page 143

Built-In Functions 143

For example,

COALESCE(Commission, Salary, -1)

In other words, if the commission value is not null, then display it. If commission value
is null, then display salary value. If salary value is null, then display -1.
The NULLIF function generates null values. First, it compares two expressions.
Then, if their values are equal, it generates a null, or it returns the first expression.
The general syntax is

NULLIF(exp1, exp2)

For example,

NULLIF(Supervisor, 111)

If Supervisor is equal to 111, then a null is displayed; otherwise, the supervisor’s value
is displayed.
The DECODE function is a conditional statement type of function. If you are fa-
miliar with any programming language like Visual Basic 6 (If Á ElseIf or Select Á
Case structures) or C (if Á else if or switch Á case structures), you will understand
the function with ease. The DECODE function tests a column or expression and for
each of its matching value, provides an action. The general syntax is

DECODE(column | expr, value1, action1,

[value2, action2, . . . ,]
[, default]);

The default action is provided for any value that does not match the values
checked within the function. If the default value is not used, a null value is returned
for nonmatching values. For example, we are displaying new salary for all employees
based on their PositionId. PositionId 1 gets a 20% raise, 2 gets 15%, 3 gets 10%, 4
gets 5%, and others get no increment at all. If the last default salary is not included
in the statement, the new salary for employees with PositionId 5 is displayed as null.

SELECT Lname, Salary,

DECODE(PositionId, 1, Salary * 1.2,
2, Salary * 1.15,
3, Salary * 1.1,
4, Salary * 1.05,
Salary) “New Salary”
FROM employee;

The CASE structure is an easier alternative to the DECODE function. It also

uses a conditional expression, with the key words WHEN and THEN. A CASE struc-
ture ends with the key word END. The CASE structure can be used in the SELECT,
ShahCh06v3.qxd 4/16/04 11:54 AM Page 144

144 Chap. 6 Working with Tables: Functions and Grouping

FROM, WHERE, or ORDER BY clause. Let us rewrite the DECODE function

(given above) with CASE structure:
SELECT Lname, Salary,
CASE WHEN PositionId = 1 THEN Salary*1.2
WHEN PositionId = 2 THEN Salary*1.15
WHEN PositionId = 3 THEN Salary*1.1
WHEN PositionId = 4 THEN Salary*1.05
ELSE Salary
END “New Salary”
FROM employee;

Conversion Functions. The conversion functions convert data from one

data type to another. The Oracle server follows some rules to convert data type im-
plicitly. For example, if you enter a character string that includes a valid number, the
Oracle server can successfully convert CHAR data to NUMBER data. If you enter
a date as a string and use the default date format DD-MON-YY, the Oracle server
can perform CHAR-to-DATE conversion successfully. It is advisable to use explicit
data conversion functions for successful and reliable queries. The three conversion
functions shown in Fig. 6-12 are used for explicit data-type conversion in queries.

Conversion Function Use

TO_CHAR1number  date [, format]2 Converts a number or a date to a VARCHAR2 value

based on the format provided.
TO_NUMBER(char [, format]) Converts a character value with valid digits to a
number using the format provided.
TO_DATE(char [, format]) Converts a character value to date value based on the
format provided. Default format is DD-MON-YY.

Figure 6-12 Conversion functions.

The TO_CHAR function converts a number or date value to its character

equivalent. The format argument is enclosed in single quotation marks, and the for-
mat value is case sensitive. Figures 6-13 to 6-16 describe common formats for number
and date with examples. In Figure 6-16, fill mode (fm) is used to remove unnecessary
spaces or zeroes in the front or in the middle.

Number Format Meaning

9 Number of 9s to determine length (e.g., 99999).

0 Displays leading zeroes (e.g., 099999).
$ Displays floating dollar sign (e.g., $99999).
. Displays decimal point in specified location (e.g., 99999.99).
, Displays comma in specified location (e.g., 99,999).
PR Puts negative numbers in parenthesis (e.g., 99999PR).

Figure 6-13 Number formats.

ShahCh06v3.qxd 4/16/04 11:54 AM Page 145

Built-In Functions 145

SQL> SELECT Lname, Fname, TO_CHAR(Salary, ’$999,999’) SALARY

2 FROM employee;

LNAME FNAME SALARY

--------------- --------------- ---------
Smith John $265,000
Houston Larry $150,000
Roberts Sandi $75,000
McCall Alex $66,500
Dev Derek $80,000
Shaw Jinku $24,500
Garner Stanley $45,000
Chen Sunny $35,000

8 rows selected.

SQL>

Figure 6-14 TO_CHAR with number format.

Date/Time Format Meaning

YYYY Four-digit year

Y, YY, or YYY Last one, two, or three digits of the year
YEAR Year spelled out
Q Quarter of the year
MM Two-digit month
MON First three letters of the month
MONTH Month name using nine characters; left characters padded with spaces
Month Same as MONTH, but in InitCap format
RR Two-digit year based on century (previous century for years 50 to 99 and
current century for years 00 to 49)
RM Month in Roman numerals
WW or W Week number of year or month
DDD, DD, or D Day of year, month, or week
DAY Name of day using nine characters; left characters padded with blanks
DY Three-letter abbreviated name of day
DDTH Ordinal number (e.g., seventh)
DDSP Spelled-out number
DDSPTH Spelled-out ordinal number
HH, HH12, or HH24 Hour of day, or hour (0–12), or hour (0–23)
MI Minute (0–59)
SS Second (0–59)
SSSSS Seconds from midnight (0–86399)
“of” String in quotes displayed in the result
fm Fill mode used with other format mask (e.g., DAY) to suppress blanks

Figure 6-15 Date/time formats.

ShahCh06v3.qxd 4/16/04 11:54 AM Page 146

146 Chap. 6 Working with Tables: Functions and Grouping

SQL> SELECT Last, First,

2 TO_CHAR (BirthDate, ’fmMonth DD, YYYY’) DOB
3 FROM student
4 /

LAST FIRST DOB

--------------- --------------- ------------------
Diaz Jose February 12, 1983
Tyler Mickey March 18, 1984
Patel Rajesh December 12, 1985
Rickles Deborah October 20, 1970
Lee Brian November 28, 1985
Khan Amir July 7, 1984

6 rows selected.

SQL>

Figure 6-16 TO_CHAR with date format (fill mode).

As we have been saying, the default date format is set to DD-MON-YY in most
Oracle installations. In Oracle-9i, DD-MON-YYYY also works as a default. If you
enter a birth date as 15-APR-60, it will be stored with year as 2060. That will create
undesired result. If the default format is DD-MON-RR, years 50 to 99 are interpreted
as 1950 to 1999 and years 00 to 49 as 2000 to 2049. If you are not sure about your ses-
sion settings, use a four-digit year with INSERT as well as other SQL statements. In
Figure 6-17, the character date values are converted to date type with the TO_DATE
function and format mask DD-MON-RR. Then, it is changed back to character for-
mat with the TO_CHAR function and format mask YYYY.

SQL> SELECT TO_CHAR(TO_DATE(’15-APR-60’, ’DD-MON-RR’), ’YYYY’) “19TH”,

2 TO_CHAR(TO_DATE(’15-APR-03’, ’DD-MON-RR’), ’YYYY’) “20TH”
3 FROM DUAL;

19TH 20TH
---- ----
1960 2003

SQL>

Figure 6-17 TO_CHAR with RR date format.

Nested Functions. Single-row functions can be nested within each other. In

nested functions, the innermost function is evaluated first, and then evaluation moves
outward.The outermost function is evaluated last.There is no limit on layers of nesting
for single-row functions. Evaluate the expression in Figure 6-18. First, the TO_DATE
function is applied to the character date string, then the TRUNC function truncates it
ShahCh06v3.qxd 4/16/04 11:54 AM Page 147

Built-In Functions 147

SQL> SELECT NEXT_DAY(ADD_MONTHS(TRUNC

2 (TO_DATE(’01/13/03’, ’MM/DD/YY’), ’MONTH’), 3), ’TUESDAY’) +7
3 AS “TAX DAY OR BIRTHDAY”
4 FROM DUAL;

TAX DAY O
---------
15-APR-03

SQL>

Figure 6-18 Nested single-row functions.

to nearest month next, the ADD_MONTHS function adds three months to it, the
NEXT_DAY function finds the date on next Tuesday, and finally, seven is added to it.
The result is the tax day, which happens to be my birthday also. (People get gifts on
their birthday, but I always have to send a gift to Uncle Sam on mine!)

Group Functions

The group functions perform an operation on a group of rows and return one result.
Look at the EMPLOYEE and STUDENT tables:
● Who makes the lowest salary?
● Who got the maximum commission?
● What is the company’s total payroll?
● How many students started in the Winter 2003 semester?

It is easy to look through small tables and find answers. In a real-life situation,
however, most tables have thousands or even millions of records. It is efficient to
look through them with simple queries and group functions.
While using the functions described in Figure 6-19, the key words DISTINCT
or ALL can be used before listing the argument in parenthesis. The key word ALL,

Group Function Use

SUM (column) Finds the sum of all values in a column; ignores null values.
AVG (column) Finds the average of all values in a column; ignores null
values.
MAX1column ƒ expression2 Finds the maximum value; ignores null values.
MIN1column ƒ expression2 Finds the minimum value; ignores null values.
COUNT1* ƒ column ƒ expression2 Counts the number of rows, including nulls, for *; counts
nonnull values if the column or expression is used as an
argument.

Figure 6-19 Group functions.

ShahCh06v3.qxd 4/16/04 11:54 AM Page 148

148 Chap. 6 Working with Tables: Functions and Grouping

which means use all values (including duplicate values), is the default. The key word
DISTINCT tells the function to use nonduplicate values only.
Let us write a query to find the total, average, highest, and lowest salaries from
the EMPLOYEE table. Figure 6-20 shows the use of group functions on a number
column, Salary.

SQL> SELECT SUM(Salary), AVG(Salary), MAX(Salary), MIN(Salary)

2 FROM EMPLOYEE;

SUM(SALARY) AVG(SALARY) MAX(SALARY) MIN(SALARY)

------------ ----------- ----------- -----------

741000 92625 265000 24500

1 row selected.

SQL>

Figure 6-20 Group functions.

Now, we will try the MAX and MIN functions on a date field. Which student
from the STUDENT table was born first, and which was born last? Check out
Figure 6-21. The MAX of a date returns the latest date, and the MIN of a date re-
turns the earliest date. If you use the function on a character column, MAX will re-
turn the last name alphabetically, and MIN will return the first name alphabetically.

SQL> SELECT MAX(BirthDate) YOUNGEST,

2 MIN(BirthDate) OLDEST
3 FROM student;

YOUNGEST OLDEST
--------- ---------
12-DEC-85 20-OCT-70

SQL>

Figure 6-21 Group function on a date column.

In Figures 6-22 and 6-23, uses of the COUNT function on an entire row and a
column are given. In Figure 6-22, when rows are counted in the EMPLOYEE table,
all eight employees’ rows are counted. In Figure 6-23, when EmployeeId column val-
ues are counted, it returns eight employees. When Commission column values are
counted, the null values are ignored, giving us only five commissioned employees.
We can change that using the NVL function:

COUNT(NVL(Commission,0))
ShahCh06v3.qxd 4/16/04 11:54 AM Page 149

Grouping Data 149

SQL> SELECT COUNT(*)

2 FROM employee;

COUNT(*)
----------
8

SQL>

Figure 6-22 COUNT all rows.

SQL> SELECT COUNT(EmployeeId), COUNT(Commission)

2 FROM employee;

COUNT(EMPLOYEEID) COUNT(COMMISSION)
----------------- -----------------
8 5

SQL>

Figure 6-23 COUNT columns with and without null values.

Null values in Commission columns are replaced with 0 in the query, and the value 8
is returned from the query.

Question: Which of the following queries will return a higher average from
the Commission column?

SELECT AVG(Commission) FROM EMPLOYEE;

SELECT AVG(NVL (Commission, 0)) FROM EMPLOYEE;

Answer: The first query, because it adds five commission values and divides the total
by five, whereas the second query divides the total by eight. The output
would be 14600 from the first query but 9125 from the second query.

GROUPING DATA

The rows in a table can be divided into different groups to treat each group sepa-
rately. The group functions can be applied to individual groups in the same fashion
they are applied to all rows. The GROUP BY clause is used for grouping data. The
general syntax is
SELECT column, groupfunction(column)
FROM tablename
[WHERE condition(s)]
[GROUP BY column | expression]
[ORDER BY column | expression [ASC | DESC]];
ShahCh06v3.qxd 4/16/04 11:54 AM Page 150

150 Chap. 6 Working with Tables: Functions and Grouping

Important points to remember include:

● When you include a group function and the GROUP BY clause in your
query, the individual column(s) appearing in SELECT must also appear in
GROUP BY.
● The WHERE clause can still be used to restrict data before grouping.
● The WHERE clause cannot be used to restrict groups.
● A column alias cannot be used in a GROUP BY clause.
● The GROUP BY column does not have to appear in a SELECT query.
● When a column is used in the GROUP BY clause, the result is sorted in as-
cending order by that column by default. In other words, GROUP BY has
an implied ORDER BY. You can still use an ORDER BY clause explicitly
to change the implied sort order.
● In Oracle9i, the order of the WHERE and GROUP BY clauses in the SELECT
query does not matter, but traditionally, the WHERE clause is written before
the GROUP BY clause.

In the next few figures, you will see the effect of a GROUP BY clause on queries
with group functions.
As you see in Figure 6-24, the DeptId column is automatically sorted, because it
is used in the GROUP BY clause.The DeptId column is not necessary in the SELECT
clause, but it is a good idea to include it so the counts make sense.

SQL> SELECT DeptId, COUNT(*) “# of Emp”

2 FROM employee
3 GROUP BY DeptId
4 /

DEPTID # of Emp
------- -------
10 3
20 2
30 2
40 1

SQL>

Figure 6-24 COUNT rows by group.

What will happen if the query in Figure 6-24 is typed without a GROUP BY
clause? If the SELECT clause contains only a group function, it does not matter.
The query in the figure, in fact, has the SELECT clause with a column and a group
function. When a column appears in the SELECT clause along with the group func-
tion, the column must appear in the GROUP BY clause. Failure to do so results in
error ORA-00937 (see Fig. 6-25).
ShahCh06v3.qxd 4/16/04 11:54 AM Page 151

Grouping Data 151

SQL> SELECT DeptId, COUNT(*) “# of Emp”

2 FROM employee
3 /
SELECT DeptId, COUNT(*) “# of Emp”
*

ERROR at line 1:
ORA-00937: not a single-group group function

SQL>

Figure 6-25 Missing GROUP BY clause.

Can we use a condition in the WHERE clause that contains a group function?
The WHERE clause can be used to restrict rows, but it cannot be used to restrict
groups, as you see in Figure 6-26. In this figure, we are trying to see buildings that
have four or more rooms. The error ORA-00934 states that the group function is not
allowed in the WHERE clause. We can fix this problem with a new, HAVING
clause, which is used for restricting groups, as you will see shortly.

SQL> SELECT Building, COUNT(*)

2 FROM location
3 WHERE COUNT(*) >= 4
4 GROUP BY Building;
WHERE COUNT(*) >= 4
*

ERROR at line 3:
ORA-00934: group function is not allowed here

SQL>

Figure 6-26 Invalid WHERE clause.

HAVING Clause

The HAVING clause can restrict groups. The WHERE clause restricts rows, the
GROUP BY clause groups remaining rows, the Group function works on each
group, and the HAVING clause keeps the groups that match the group condition.
In the sample query (see Fig. 6-27), the WHERE clause filters out the building
named Kennedy, the rest of the rows are grouped by the building names Gandhi and
Nehru, the group function COUNT counts the number of rows in each group, and
the HAVING clause keeps groups with four or more rows—that is, the Gandhi
building with five rows/rooms.
The implied ascending sort with the GROUP BY clause can be overriden by
adding an explicit ORDER BY clause to the query. Figure 6-28 shows outcome sorted
in ascending order by Building column with the GROUP BY clause, then it is re-
versed to descending order by inserting the ORDER BY clause in line 5.
ShahCh06v3.qxd 4/16/04 11:54 AM Page 152

152 Chap. 6 Working with Tables: Functions and Grouping

SQL> SELECT Building, COUNT(*) ROOMS

2 FROM location
3 WHERE UPPER(Building) <> ’KENNEDY’
4 GROUP BY Building
5 HAVING COUNT(*) >= 4;

BUILDIN ROOMS
------- ------
Gandhi 5

1 row selected.

SQL>

Figure 6-27 HAVING clause.

SQL> SELECT Building, COUNT(*)

2 FROM location
3 GROUP BY Building
4 HAVING COUNT(*) > 2;

BUILDIN COUNT(*)
------- --------
Gandhi 5
Kennedy 4

SQL> i
5 ORDER BY Building DESC;

BUILDIN COUNT(*)
------- --------
Kennedy 4
Gandhi 5

SQL>

Figure 6-28 GROUP BY sort order changed with ORDER BY.

Let us look at another example of GROUP BY and HAVING. Let us find em-
ployees who have more than two dependents. We will use the DEPENDENT table
and GROUP BY EmployeeId column, find COUNT of DependentId, and also
check for COUNT higher than 2:

SELECT EmployeeId, COUNT(DependentId)

FROM dependent
GROUP BY EmployeeId
HAVING COUNT(DependentId) > 2;
ShahCh06v3.qxd 4/16/04 11:54 AM Page 153

Chap. 6 Exercise Questions 153

Nesting Group Functions

The single-row functions can be nested to many levels, but the group functions can
only be nested to two levels. For example,
SELECT SUM(MaxCount) FROM crssection GROUP BY CourseId;

will find the total available seats for each CourseId. If you use this output for an
outer function in a nested scenario as follows, you will get a different answer:
SELECT MAX(SUM(MaxCount)) FROM crssection GROUP BY CourseId;

The answer returned by this query is 85, because the outer query takes totals by
each CourseId and finds the one with the largest value.

IN A NUTSHELL . . .

● Single-row functions work on each row individually. They include character

functions, number functions, date functions, data conversion functions, and
other general functions. All functions take zero or more arguments and re-
turn one value back.
● The NVL function converts a null value to another specified value that is
provided as its second argument.
● The DECODE function is similar to the if Á else if or case structures in
programming languages.
● In an expression with nested single-row functions, the innermost function is
performed first, and the outermost function is performed last.
● The SYSDATE function is an Oracle function that returns the current date
from the system. SYSDATE is very useful in date arithmetic.
● The group functions work on a group of rows to return one result per group.
The rows can be grouped together by using the GROUP BY clause with a
SELECT query.
● The WHERE clause is used to restrict rows; similarly, the HAVING clause is
used to restrict groups.
● The group functions can be nested like single-row functions. The nesting is
limited to two functions for group functions.

EXERCISE QUESTIONS

True/False:
1. A single-row function may be used in the SELECT clause, but it is not allowed in the
WHERE clause.
2. The SYSDATE function can return the current date but not the current time.