Using Other Single-Row Functions

131
Parameter Description
BG_JOB_ID
Returns the job ID (that is,
DBA_JOBS
) if the session was created
by a background process. Returns
NULL
if the session is a fore-
ground session. See also
FG_JOB_ID
.
CLIENT_IDENTIFIER
Returns the client session identifier in the global context. It can
be set with the
DBMS_SESSION
built-in package.
CLIENT_INFO
Returns the 64 bytes of user session information stored by
DBMS_APPLICATION_INFO
.
CURRENT_BIND
Returns bind variables for fine-grained auditing.
CURRENT_SCHEMA
Returns the current schema as set by
ALTER SESSION SET
CURRENT_SCHEMA
or, by default, the login schema/ID.
CURRENT_SCHEMAID

ENTERPRISE_IDENTITY
Returns OID DN for enterprise users, for local users NULL.
FG_JOB_ID
Returns the job ID of the current session if a foreground pro-
cess created it. Returns
NULL
if the session is a background
session. See also
BG_JOB_ID
.
GLOBAL_CONTEXT_MEMORY
Returns the number in the SGA by the globally accessible
context.
GLOBAL_UID
Returns the global user ID from OID.
HOST
Returns the hostname of the machine from where the client
connected. This is not the same terminal in
V$SESSION
.
table 2.14 Parameters in the
USERENV
Namespace (continued)
95127c02.indd 131 2/18/09 6:46:45 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
132

Chapter 2
N
Using Single-Row Functions

form
language_territory.characterset.
MODULE
Returns the application name set through
DBMS_APPLICATION_INFO
.
NETWORK_PROTOCOL
Returns the network protocol being used as specified in the
PROTOCOL=
section of the connect string or
tnsnames.ora

definition.
NLS_CALENDAR
Returns the calendar for the current session.
NLS_CURRENCY
Returns the currency for the current session.
NLS_DATE_FORMAT
Returns the date format for the current session.
NLS_DATE_LANGUAGE
Returns the language used for displaying dates.
NLS_SORT
Returns the binary or linguistic sort basis.
NLS_TERRITORY
Returns the territory for the current session.
OS_USER
Returns the operating-system username for the current session.
POLICY_INVOKER
Returns the invoker of row-level security-policy functions.
PROXY_ENTERPRISE_

Returns the auditing session identifier AUDSID. This parameter
is out of scope for distributed queries.
SID
Returns the session number (same as the SID from
V$SESSION
).
STATEMENT_ID
Returns the auditing statement identifier.
TERMINAL
Returns the terminal identifier for the current session. This is
the same as the terminal in
V$SESSION
.
Here are few more examples of
SYS_CONTEXT
in the
USERENV
namespace:
SELECT SYS_CONTEXT(‘USERENV’, ‘OS_USER’),
SYS_CONTEXT(‘USERENV’, ‘CURRENT_SCHEMA’),
SYS_CONTEXT(‘USERENV’, ‘HOST’),
SYS_CONTEXT(‘USERENV’, ‘NLS_TERRITORY’)
FROM dual;
SYS_CONTEXT(‘USERENV’,’OS_USER’)
SYS_CONTEXT(‘USERENV’,’CURRENT_SCHEMA’)
SYS_CONTEXT(‘USERENV’,’HOST’)
SYS_CONTEXT(‘USERENV’,’NLS_TERRITORY’)
--------------------------------------------
table 2.14 Parameters in the
USERENV

the session. The user ID uniquely identifies each user in a database and can be selected from
the
DBA_USERS
view.
SQL> SHOW USER
USER is “BTHOMAS”
SELECT username, account_status
FROM dba_users
WHERE user_id = UID;
USERNAME ACCOUNT_STATUS
---------------- ---------------
BTHOMAS OPEN
95127c02.indd 134 2/18/09 6:46:45 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Using Other Single-Row Functions

135
USER
USER
takes no parameters and returns a character string containing the username for the
current user.
SELECT default_tablespace, temporary_tablespace
FROM dba_users
WHERE username = USER;
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ---------------------
USERS TEMP
USERENV
USERENV
(

ÛN
returns the language, territory, and database character set. The delimiters are
an underscore (
_
) between language and territory and a period (
.
) between the terri-
tory and character set.
LANG
ÛN
returns the ISO abbreviation of the session’s language.
TERMINAL
ÛN
returns a VARCHAR2 string containing information corresponding to the
operating system identifier for the current session’s terminal.
The option can appear in uppercase, lowercase, or mixed case. The
USERENV
function
has been deprecated since Oracle 9i. It is recommended to use the
SYS_CONTEXT
function
with the built-in
USERENV
namespace instead.
VSIZE
VSIZE(x)
takes a single argument, where
x
is an expression. This function returns the size
in bytes of the internal representation of the

You learned that single-row functions return a value for each row as it is retrieved from
the table. You can use single-row functions to interpret
NULL
values, format output, convert
datatypes, transform data, perform date arithmetic, give environment information, and
perform trigonometric calculations.
You can use single-row functions in the
SELECT
,
WHERE
, and
ORDER BY
clauses of
SELECT

statements. I covered the rich assortment of functions available in each datatype category
and some functions that work on any datatype.
The
NVL
,
NVL2
, and
COALESCE
functions interpret
NULL
values.
The single-row character functions operate on character input. The
INSTR
function returns
the position of a substring within the string. The

returns a value rounded to certain digits of precision.
REMAINDER
and
MOD
are similar
functions.
Date functions operate on datetime values.
SYSDATE
and
SYSTIMESTAMP
values return the
current date and time.
MONTHS_BETWEEN
finds the number of months between two date val-
ues.
ADD_MONTHS
is a commonly used function and can add months to or subtract months
from a date. You can use
ROUND
and
TRUNC
on datetime values to find the nearest date,
month, or year.
Of the conversion functions,
TO_CHAR
and
TO_DATE
are the most commonly used. I also
reviewed the format codes that can be used with numeric and datetime values.
The

. This is true of most functions as well. Use the
NVL
,
NVL2
, and
COALESCE
functions to deal with
NULL
s.
Review the character-manipulation functions. Understand the arguments and the result
of using character-manipulation functions such as
INSTR
,
SUBSTR
,
REPLACE
, and
TRANSLATE
.
Understand the numeric functions. Know the effects of using
TRUNC
and
ROUND
with
-n
as
the second argument. Also practice using
LENGTH
and
INSTR

Understand the use of the
DECODE
function.
DECODE
acts like a case statement in C, Pascal,
or Ada. Learn how this function works and how to use it.
95127c02.indd 137 2/18/09 6:46:45 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
138

Review Questions
Review Questions
1. You want to display each project’s start date as the day, week, number, and year. Which
statement will give output like the following?
Tuesday Week 23, 2008
A. SELECT proj_id, TO_CHAR(start_date, ‘DOW Week WOY YYYY’) FROM projects;
B. SELECT proj_id, TO_CHAR(start_date,’Day’||’ Week’||’ WOY, YYYY’) FROM
projects;
C. SELECT proj_id, TO_CHAR(start_date, ‘Day” Week” WW, YYYY’) FROM projects;
D. SELECT proj_id, TO_CHAR(start_date, ‘Day Week# , YYYY’) FROM projects;
E. You can’t calculate week numbers with Oracle.
2. What will the following statement return?
SELECT last_name, first_name, start_date
FROM employees
WHERE hire_date < TRUNC(SYSDATE) – 5;
A. Employees hired within the past five hours
B. Employees hired within the past five days
C. Employees hired more than five hours ago
D. Employees hired more than five days ago
3. Which assertion about the following statements is most true?

FROM products;
B. SELECT sku, name, LEAST(NVL(base_price,0) * 1.05, surcharge * 1.2)
FROM products;
C. SELECT sku, name, COALESCE(LEAST(base_price*1.05, surcharge * 1.2),
base_price * 1.05)
FROM products;
D. A, B, and C will all achieve the desired results.
E. None of these statements will achieve the desired results.
6. Which function(s) accept arguments of any datatype? (Choose all that apply.)
A. SUBSTR
B. NVL
C. ROUND
D. DECODE
E. SIGN
7. What will be returned by SIGN(ABS(NVL(-32,0)))?
A. 1
B. 32
C. –1
D. 0
E. NULL
95127c02.indd 139 2/18/09 6:46:45 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
140

Review Questions
8. The SALARY table has the following data:
LAST_NAME FIRST_NAME SALARY
------------ -------------------- ----------
Mavris Susan 6500
Higgins Shelley 12000

Review Questions

141
11. Which two SQL statements will replace the last two characters of last_ nam e with ‘XX‘ in
the employees table when executed? (Choose two.)
A. SELECT RTRIM(last_name, SUBSTR(last_name, LENGTH(last_name)-1)) || ‘XX’
new_col FROM employees;
B. SELECT REPLACE(last_name, SUBSTR(last_name, LENGTH(last_name)-1), ‘XX’)
new_col FROM employees;
C. SELECT REPLACE(SUBSTR(last_name, LENGTH(last_name)-1), ‘XX’) new_col
FROM employees;
D. SELECT CONCAT(SUBSTR(last_name, 1,LENGTH(last_name)-2), ‘XX’) new_col
FROM employees;
12. Which date components does the CURRENT_TIMESTAMP function display?
A. Session date, session time, and session time zone offset
B. Session date and session time
C. Session date and session time zone offset
D. Session time zone offset
13. Using the SALESPERSON_REVENUE table described here, which statements will properly dis-
play the TOTAL_REVENUE (CAR_SALES + WARRANTY_SALES) of each salesperson?
Column Name salesperson_id car_sales warranty_sales
Key Type pk
NULL
s/Unique
NN NN
FK Table
Datatype NUMBER NUMBER NUMBER
Length 10 11,2 11,2
A. SELECT salesperson_id, car_sales, warranty_sales, car_sales + warranty_
sales total_sales

SELECT COALESCE(NULL,’Oracle ‘,’Certified’) FROM dual;
A. NULL
B. Oracle
C. Certified
D. Oracle Certified
17. Which expression will always return the date one year later than the current date?
A. SYSDATE + 365
B. SYSDATE + TO_YMINTERVAL(‘01-00’)
C. CURRENT_DATE + 1
D. NEW_TIME(CURRENT_DATE,1,’YEAR’)
E. None of the above
18. Which function will return a TIMESTAMP WITH TIME ZONE datatype?
A. CURRENT_TIMESTAMP
B. LOCALTIMESTAMP
C. CURRENT_DATE
D. SYSDATE
95127c02.indd 142 2/18/09 6:46:46 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Review Questions

143
19. Which statement would change all occurrences of the string ‘IBM’ to the string ’SUN’ in
the DESCRIPTION column of the VENDOR table?
A. SELECT TRANSLATE(description, ‘IBM’, ‘SUN’) FROM vendor
B. SELECT CONVERT(description, ‘IBM’, ‘SUN’) FROM vendor
C. SELECT EXTRACT(description, ‘IBM’, ‘SUN’) FROM vendor
D. SELECT REPLACE(description, ‘IBM’, ‘SUN’) FROM vendor
20. Which function implements IF…THEN…ELSE logic?
A. INITCAP
B. REPLACE

10. B. MOD returns the number remainder after division. The REMAINDER function is similar to
MOD but will use the ROUND function in the algorithm; hence, the result of REMAINDER(11,4)
would be –1. MOD uses FLOOR in the algorithm.
11. A, D. The SUBSTR function in option A would return the last two characters of the last
name. These two characters are right-trimmed using the RTRIM function. The result would
be the first portion of the last name and is concatenated to ‘XX’. Option B also would do
the same as A, but would replace all the occurrences of the last two characters (Paululul
will be PaXXXXXX instead of PaululXX). Option C would return only the last two characters
of the last name. The SUBSTR function in option D would return the first character through
the last –2 characters. ‘XX‘ is concatenated to the result.
12. A. The CURRENT_TIMESTAMP function returns the session date, session time, and session
time zone offset. The return datatype is TIMESTAMP WITH TIME ZONE.
95127c02.indd 144 2/18/09 6:46:46 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Answers to Review Questions

145
13. C. Option A will result in NULL TOTAL_SALES for rows where there are NULL WARRANTY_
SALES. Option B is not the correct syntax for NVL2, because it requires three arguments.
With option C, if WARRANTY_SALES is NULL, then CAR_SALES is returned; otherwise, CAR_
SALES+WARRANTY_SALES is returned. The COALESCE function returns the first non-NULL
argument and could be used to obtain the desired results, but the first argument here is
CAR_SALES, which is not NULL, and therefore COALESCE will always return CAR_SALES.
14. C. The ADD_MONTHS function returns the date d plus i months. If <d> is the last day of the month
or the resulting month has fewer days, then the result is the last day of the resulting
month.
15. C. Statement 1 will result in 30-SEP-0007, and statement 2 will result in 30-SEP-2007.
The RR and RRRR formats derive the century based on the current date if the century is not
specified. The YY format will use the current century, and the YYYY format expects the cen-
tury in the input.


Group data by using the GROUP BY clause

Include or exclude the grouped rows by using the

HAVING clause
95127c03.indd 147 2/17/09 11:38:10 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
As explained in the previous chapter, functions are programs
that take zero or more arguments and return a single value. The
exam focuses on two types of functions: single-row and aggre-
gate (group) functions. Single-row functions were covered in Chapter 2, “Using Single-Row
Functions.” Group functions are covered in this chapter.
Group functions differ from single-row functions in how they are evaluated. Single-row
functions are evaluated once for each row retrieved. Group functions are evaluated on
groups of one or more rows at a time.
In this chapter, you will explore which group functions are available in SQL, the rules
for how to use them, and what to expect on the exam about aggregating data and group
functions. You will also explore nesting function calls together. SQL allows you to nest
group functions within calls to single-row functions, as well as nest single-row functions
within calls to group functions.
Group-Function Fundamentals
Group functions are sometimes called aggregate functions and return a value based on a num-
ber of inputs. The exact number of inputs is not determined until the query is executed and
all rows are fetched. This differs from single-row functions, in which the number of inputs is
known at parse time—before the query is executed. Because of this difference, group functions
have slightly different requirements and behavior than single-row functions.
Group functions do not consider
NULL
values, except the

is specified, only one of each non-
NULL
value is applied
to the function. If you do not specify
ALL
or
DISTINCT
, the default is
ALL
.
To better understand the difference of
ALL
vs.
DISTINCT
, let’s look at a few rows from the
EMPLOYEES
table:
SELECT first_name, salary
FROM employees
WHERE first_name LIKE ‘D%’
ORDER BY salary;
95127c03.indd 148 2/17/09 11:38:10 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Utilizing Aggregate Functions

149
FIRST_NAME SALARY
-------------------- ----------
Donald 2600
Douglas 2600

Utilizing Aggregate Functions
As with single-row functions, Oracle offers a rich variety of aggregate functions. These
functions can appear in the
SELECT
,
ORDER BY
, or
HAVING
clauses of
SELECT
statements.
When used in the
SELECT
clause, they usually require a
GROUP BY
clause as well. If no
GROUP
BY
clause is specified, the default grouping is for the entire result set. Group functions can-
not appear in the
WHERE
clause of a
SELECT
statement. The
GROUP BY
and
HAVING
clauses of
SELECT
statements are associated with grouping data. I’ll discuss the

SELECT
clause), the default grouping becomes the entire result set.
When the query executes and the data is fetched, it is grouped based on the
GROUP BY
clause,
and the group function is applied.
The basic syntax of using a group function in the
SELECT
statement is as follows:
SELECT [column names], group_function (column_name), … … …
FROM table
[WHERE condition]
[GROUP BY column names]
[ORDER BY column names]
In the following example, you find the total number of employees from the
EMPLOYEES
table:
SELECT COUNT(*) FROM employees;
COUNT(*)
----------
107
Since you did not have any other column in the
SELECT
clause, you didn’t need to specify
the
GROUP BY
clause. Suppose you want to find out the number of employees in each depart-
ment; you can include
department_id
in the