1 of 66

Single-Row Functions

3

2 of 66

Objectives

After completing this lesson, you should be able to

do the following:

Describe various types of functions available�in SQL
Use character, number, and date functions in SELECT statements
Describe the use of conversion functions

3-2

3 of 66

SQL Functions

Function

Input

arg 1

arg 2

arg n

Function performs action

Output

Result

value

3-3

4 of 66

Two Types of SQL Functions

Functions

Single-row

functions

Multiple-row

functions

3-4

5 of 66

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
Accept arguments which can be a column or an expression

function_name [(arg1, arg2,...)]

3-5

6 of 66

Single-Row Functions

Conversion

Character

Number

Date

General

Single-row

functions

3-6

7 of 66

Character Functions

Character

functions

LOWER

UPPER

INITCAP

CONCAT

SUBSTR

LENGTH

INSTR

LPAD | RPAD

TRIM

REPLACE

Case-manipulation

functions

Character-manipulation

functions

3-7

8 of 66

Character Functions

Character

functions

LOWER

UPPER

INITCAP

CONCAT

SUBSTR

LENGTH

INSTR

LPAD | RPAD

TRIM

REPLACE

Case-manipulation

functions

Character-manipulation

functions

3-8

9 of 66

Case Manipulation Functions

These functions convert case for character strings.

Function

Result

LOWER('SQL Course')

UPPER('SQL Course')

INITCAP('SQL Course')

sql course

SQL COURSE

Sql Course

3-9

10 of 66

Using Case Manipulation Functions

Display the employee number, name, and department number for employee Higgins:

SELECT employee_id, last_name, department_id

FROM employees

WHERE last_name = 'higgins';

no rows selected

SELECT employee_id, last_name, department_id

FROM employees

WHERE LOWER(last_name) = 'higgins';

3-10

Case Manipulation Functions (continued)

The slide example displays the employee number, name, and department number of employee Higgins.

The WHERE clause of the first SQL statement specifies the employee name as higgins. Because all the data in the EMPLOYEES table is stored in proper case, the name higgins does not find a match in the table, and no rows are selected.

The WHERE clause of the second SQL statement specifies that the employee name in the EMPLOYEES table is compared to higgins, converting the LAST_NAME column to lowercase for comparison purposes. Since both names are lowercase now, a match is found and one row is selected. The WHERE clause can be rewritten in the following manner to produce the same result:

...WHERE last_name = 'Higgins'

The name in the output appears as it was stored in the database. To display the name capitalized, use the UPPER function in the SELECT statement.

SELECT employee_id, UPPER(last_name), department_id

FROM employees

WHERE INITCAP(last_name) = 'Higgins';

11 of 66

Character-Manipulation Functions

These functions manipulate character strings:

CONCAT('Hello', 'World')

SUBSTR('HelloWorld',1,5)

LENGTH('HelloWorld')

INSTR('HelloWorld', 'W')

LPAD(salary,10,'*')

RPAD(salary, 10, '*')

TRIM('H' FROM 'HelloWorld')

HelloWorld

Hello

10

6

*****24000

24000*****

elloWorld

Function

Result

3-11

Character Manipulation Functions

CONCAT, SUBSTR, LENGTH, INSTR, LPAD, RPAD, and TRIM are the character manipulation functions covered in this lesson.

CONCAT: Joins values together (You are limited to using two parameters with CONCAT.)

SUBSTR: Extracts a string of determined length

LENGTH: Shows the length of a string as a numeric value

INSTR: Finds numeric position of a named character

LPAD: Pads the character value right-justified

RPAD: Pads the character value left-justified

TRIM: Trims heading or trailing characters (or both) from a character string (If trim_character or trim_source is a character literal, you must enclose it in single quotes.)

Instructor Note

Be sure to point out RPAD to the students, because this function is needed in a practice exercise. Also, TRIM, which was a new function in Oracle8i, does the job of both the LTRIM and the RTRIM functions.

12 of 66

Using the Character-Manipulation Functions

SELECT employee_id, CONCAT(first_name, last_name) NAME,

job_id, LENGTH (last_name),

INSTR(last_name, 'a') "Contains 'a'?"

FROM employees

WHERE SUBSTR(job_id, 4) = 'REP';

1

2

3

1

2

3

3-12

13 of 66

Number Functions

ROUND: Rounds value to specified decimal

ROUND(45.926, 2) 45.93

TRUNC: Truncates value to specified decimal

TRUNC(45.926, 2) 45.92

MOD: Returns remainder of division

MOD(1600, 300) 100

3-13

14 of 66

Using the ROUND Function

SELECT ROUND(45.923,2), ROUND(45.923,0),

ROUND(45.923,-1)

FROM DUAL;

DUAL is a dummy table you can use to view results

from functions and calculations.

1

2

3

1

2

3-14

ROUND Function

The ROUND function rounds the column, expression, or value to n decimal places. If the second argument is 0 or is missing, the value is rounded to zero decimal places. If the second argument is 2, the value is rounded to two decimal places. Conversely, if the second argument is -2, the value is rounded to two decimal places to the left.

The ROUND function can also be used with date functions. You will see examples later in this lesson.

The 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 once only, for instance, 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 SELECT clause syntax completeness, because both SELECT and FROM clauses are mandatory, and several calculations do not need to select from actual tables.

15 of 66

Using the TRUNC Function

SELECT TRUNC(45.923,2), TRUNC(45.923),

TRUNC(45.923,-2)

FROM DUAL;

3

1

2

1

2

3

3-15

16 of 66

Using the MOD Function

Calculate the remainder of a salary after it is divided by 5000 for all employees whose job title is sales representative.

SELECT last_name, salary, MOD(salary, 5000)

FROM employees

WHERE job_id = 'SA_REP';

3-16

17 of 66

Working with Dates

Oracle database stores dates in an internal numeric format: century, year, month, day, hours, minutes, seconds.
The default date display format is DD-MON-RR.

Allows you to store 21st century dates in the 20th century by specifying only the last two digits of the year.
Allows you to store 20th century dates in the 21st century in the same way.

SELECT last_name, hire_date

FROM employees

WHERE last_name like 'G%';

3-17

Oracle Date Format

Oracle database stores dates in an internal numeric format, representing the century, year, month, day, hours, minutes, and seconds.

The default display and input format for any date is DD-MON-RR. Valid Oracle dates are between January 1, 4712 B.C. and December 31, 9999 A.D.

In the example in the slide, the HIRE_DATE for the employee Gietz is displayed in the default format DD-MON-RR. However, dates are not stored in the database in this format. All the components of the date and time are stored. So, although a HIRE_DATE such as 07-JUN-94 is displayed as day, month, and year, there is also time and century information associated with it. The complete data might be June 7th, 1994 5:10:43 p.m.

This data is stored internally as follows:

CENTURY YEAR MONTH DAY HOUR MINUTE SECOND

19 94 06 07 5 10 43

Centuries and the Year 2000

The Oracle server is year 2000 compliant. When a record with a date column is inserted into a table, the century information is picked up from the SYSDATE function. However, when the date column is displayed on the screen, the century component is not displayed by default.

The DATE data type always stores year information as a four-digit number internally: two digits for the century and two digits for the year. For example, the Oracle database stores the year as 1996 or 2001, and not just as 96 or 01.

18 of 66

Working with Dates

SYSDATE is a function that returns:

Date
Time

3-18

19 of 66

Arithmetic with Dates

Add or subtract a number to or from a date for a resultant date value.
Subtract two dates to find the number of days between those dates.
Add hours to a date by dividing the number of hours by 24.

3-19

20 of 66

Using Arithmetic Operators�with Dates

SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS

FROM employees

WHERE department_id = 90;

3-20

21 of 66

Date Functions

Number of months�between two dates

MONTHS_BETWEEN

ADD_MONTHS

NEXT_DAY

LAST_DAY

ROUND

TRUNC

Add calendar months to date

Next day of the date specified

Last day of the month

Round date

Truncate date

Function

Description

3-21

Date Functions

Date functions operate on Oracle dates. All date functions return a value of DATE data type except MONTHS_BETWEEN, which returns a numeric value.

MONTHS_BETWEEN(date1, date2): Finds the number of months between date1 and date2. The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month.

ADD_MONTHS(date, n): Adds n number of calendar months to date. The value of n must be an integer and can be negative.

NEXT_DAY(date, 'char'): Finds the date of the next specified day of the week ('char') following date. The value of char may be a number representing a day or a character string.

LAST_DAY(date): Finds the date of the last day of the month that contains date.

ROUND(date[,'fmt']): Returns date rounded to the unit specified by the format model fmt. If the format model fmt is omitted, date is rounded to the nearest day.

TRUNC(date[, 'fmt']): Returns date with the time portion of the day truncated to the unit specified by the format model fmt. If the format model fmt is omitted, date is truncated to the nearest day.

This list is a subset of the available date functions. The format models are covered later in this lesson. Examples of format models are month and year.

22 of 66

Using Date Functions

MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')

ADD_MONTHS ('11-JAN-94',6)

NEXT_DAY ('01-SEP-95','FRIDAY')

LAST_DAY('01-FEB-95')

19.6774194

'11-JUL-94'

'08-SEP-95'

'28-FEB-95'

3-22

23 of 66

Using Date Functions

ROUND(SYSDATE,'MONTH') 01-AUG-95

ROUND(SYSDATE ,'YEAR') 01-JAN-96

TRUNC(SYSDATE ,'MONTH') 01-JUL-95

TRUNC(SYSDATE ,'YEAR') 01-JAN-95

Assume SYSDATE = '25-JUL-95':

3-23

Date Functions (continued)

The ROUND and TRUNC functions can be used for number and date values. When used with dates, these functions round or truncate to the specified format model. Therefore, you can round dates to the nearest year or month.

Example

Compare the hire dates for all employees who started in 1997. Display the employee number, hire date, and start month using the ROUND and TRUNC functions.

SELECT employee_id, hire_date,

ROUND(hire_date, 'MONTH'), TRUNC(hire_date, 'MONTH')

FROM employees

WHERE hire_date LIKE '%97';

Instructor Note

If the format model is month, dates 1-15 result in the first day of the current month. Dates 16-31 result in the first day of the next month. If the format model is year, months 1-6 result with January 1st of the current year. Months 7-12 result in January 1st of the next year.

This is a good point to break the lesson in half. Have the students do Practice 3 - Part 1 (1-5) now.

24 of 66

Practice 3, Part One: Overview

This practice covers the following topics:

Writing a query that displays the current date
Creating queries that require the use of numeric, character, and date functions
Performing calculations of years and months of service for an employee

3-24

25 of 66

Conversion Functions

Implicit data type

conversion

Explicit data type

conversion

Data type

conversion

3-25

Conversion Functions

In addition to Oracle data types, columns of tables in an Oracle9i database can be defined using ANSI, DB2, and SQL/DS data types. However, the Oracle server internally converts such data types to Oracle data types.

In some cases, Oracle server uses data of one data type where it expects data of a different data type. When this happens, Oracle server can automatically convert the data to the expected data type. This data type conversion can be done implicitly by Oracle server, or explicitly by the user.

Implicit data type conversions work according to the rules explained in the next two slides.

Explicit data type conversions are done by using the conversion functions. Conversion functions convert a value from one data type to another. Generally, the form of the function names follows the convention data type TO data type. The first data type is the input data type; the last data type is the output.

Note: Although implicit data type conversion is available, it is recommended that you do explicit data type conversion to ensure the reliability of your SQL statements.

26 of 66

Implicit Data Type Conversion

For assignments, the Oracle server can automatically

convert the following:

VARCHAR2 or CHAR

From

To

VARCHAR2 or CHAR

NUMBER

DATE

NUMBER

DATE

VARCHAR2

3-26

27 of 66

Implicit Data Type Conversion

For expression evaluation, the Oracle Server can

automatically convert the following:

VARCHAR2 or CHAR

From

To

VARCHAR2 or CHAR

NUMBER

DATE

3-27

28 of 66

Explicit Data Type Conversion

NUMBER

CHARACTER

TO_CHAR

TO_NUMBER

DATE

TO_CHAR

TO_DATE

3-28

29 of 66

Explicit Data Type Conversion

NUMBER

CHARACTER

TO_CHAR

TO_NUMBER

DATE

TO_CHAR

TO_DATE

3-29

30 of 66

Explicit Data Type Conversion

NUMBER

CHARACTER

TO_CHAR

TO_NUMBER

DATE

TO_CHAR

TO_DATE

3-30

31 of 66

Using the TO_CHAR Function with Dates

The format model:

Must be enclosed in single quotation marks and is case sensitive
Can include any valid date format element
Has an fm element to remove padded blanks or suppress leading zeros
Is separated from the date value by a comma

TO_CHAR(date, 'format_model')

3-31

Displaying a Date in a Specific Format

Previously, all Oracle date values were displayed in the DD-MON-YY format. You can use the TO_CHAR function to convert a date from this default format to one specified by you.

Guidelines

The format model must be enclosed in single quotation marks and is case sensitive.

The format model can include any valid date format element. Be sure to separate the date value from the format model by a comma.

The names of days and months in the output are automatically padded with blanks.

To remove padded blanks or to suppress leading zeros, use the fill mode fm element.

You can format the resulting character field with the iSQL*Plus COLUMN command covered in a later lesson.

SELECT employee_id, TO_CHAR(hire_date, 'MM/YY') Month_Hired

FROM employees

WHERE last_name = 'Higgins';

32 of 66

Elements of the Date Format Model

YYYY

YEAR

MM

MONTH

DY

DAY

Full year in numbers

Year spelled out

Two-digit value for month

Three-letter abbreviation of the day of the week

Full name of the day of the week

Full name of the month

MON

Three-letter abbreviation of the month

DD

Numeric day of the month

3-32

33 of 66

3-33

34 of 66

Elements of the Date Format Model

Time elements format the time portion of the date.�
Add character strings by enclosing them in double quotation marks.�
Number suffixes spell out numbers.

HH24:MI:SS AM

15:45:32 PM

DD "of" MONTH

12 of OCTOBER

ddspth

fourteenth

3-34

35 of 66

3-35

36 of 66

Using the TO_CHAR Function with Dates

SELECT last_name,

TO_CHAR(hire_date, 'fmDD Month YYYY')

AS HIREDATE

FROM employees;

…

3-36

37 of 66

Using the TO_CHAR Function with Numbers

These are some of the format elements you can use with the TO_CHAR function to display a number value as a character:

TO_CHAR(number, 'format_model')

9

0

$

L

.

,

Represents a number

Forces a zero to be displayed

Places a floating dollar sign

Uses the floating local currency symbol

Prints a decimal point

Prints a thousand indicator

3-37

38 of 66

Using the TO_CHAR Function with Numbers

SELECT TO_CHAR(salary, '$99,999.00') SALARY

FROM employees

WHERE last_name = 'Ernst';

3-38

39 of 66

Using the TO_NUMBER and TO_DATE Functions

Convert a character string to a number format using the TO_NUMBER function:

Convert a character string to a date format using the TO_DATE function:

These functions have an fx modifier. This modifier specifies the exact matching for the character argument and date format model of a TO_DATE function

TO_NUMBER(char[, 'format_model'])

TO_DATE(char[, 'format_model'])

3-39

40 of 66

Using the TO_NUMBER and TO_DATE Functions

Convert a character string to a number format using the TO_NUMBER function:

Convert a character string to a date format using the TO_DATE function:

These functions have an fx modifier. This modifier specifies the exact matching for the character argument and date format model of a TO_DATE function

TO_NUMBER(char[, 'format_model'])

TO_DATE(char[, 'format_model'])

3-40

41 of 66

RR Date Format

Current Year

1995

2001

Specified Date

27-OCT-95

27-OCT-17

27-OCT-95

RR Format

1995

2017

1995

YY Format

1995

1917

2017

2095

If two digits of the current �year are:

0–49

50–99

The return date is in the current century

The return date is in the century after the current one

The return date is in the century before the current one

The return date is in the current century

If the specified two-digit year is:

3-41

42 of 66

Example of RR Date Format

To find employees hired prior to 1990, use the RR

format, which produces the same results whether the

command is run in 1999 or now:

SELECT last_name, TO_CHAR(hire_date, 'DD-Mon-YYYY')

FROM employees

WHERE hire_date < TO_DATE('01-Jan-90', 'DD-Mon-RR');

3-42

43 of 66

Nesting Functions

Single-row functions can be nested to any level.
Nested functions are evaluated from deepest level to the least deep level.

F3(F2(F1(col,arg1),arg2),arg3)

Step 1 = Result 1

Step 2 = Result 2

Step 3 = Result 3

3-43

44 of 66

Nesting Functions

SELECT last_name,

NVL(TO_CHAR(manager_id), 'No Manager')

FROM employees

WHERE manager_id IS NULL;

3-44

Nesting Functions (continued)

The slide example displays the head of the company, who has no manager. The evaluation of the SQL statement involves two steps:

1. Evaluate the inner function to convert a number value to a character string.

Result1 = TO_CHAR(manager_id)

2. Evaluate the outer function to replace the null value with a text string.

NVL(Result1, 'No Manager')

The entire expression becomes the column heading because no column alias was given.

Example

Display the date of the next Friday that is six months from the hire date. The resulting date should appear as Friday, August 13th, 1999. Order the results by hire date.

SELECT TO_CHAR(NEXT_DAY(ADD_MONTHS

(hire_date, 6), 'FRIDAY'),

'fmDay, Month DDth, YYYY')

"Next 6 Month Review"

FROM employees

ORDER BY hire_date;

Instructor Note

Demo: 3_nest.sql

Purpose: To illustrate nesting of several single row functions

45 of 66

General Functions

These functions work with any data type and pertain

to using nulls.

NVL (expr1, expr2)
NVL2 (expr1, expr2, expr3)
NULLIF (expr1, expr2)
COALESCE (expr1, expr2, ..., exprn)

3-45

46 of 66

NVL Function

Converts a null to an actual value.

Data types that can be used are date, character, and number.
Data types must match:

NVL(commission_pct,0)
NVL(hire_date,'01-JAN-97')
NVL(job_id,'No Job Yet')

3-46

47 of 66

Using the NVL Function

SELECT last_name, salary, NVL(commission_pct, 0),

(salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL

FROM employees;

…

1

2

1

2

3-47

48 of 66

Using the NVL2 Function

SELECT last_name, salary, commission_pct,

NVL2(commission_pct,

'SAL+COMM', 'SAL') income

FROM employees WHERE department_id IN (50, 80);

1

2

1

2

3-48

The NVL2 Function

The NVL2 function examines the first expression. If the first expression is not null, then the NVL2 function returns the second expression. If the first expression is null, then the third expression is returned.

Syntax

NVL(expr1, expr2, expr3)

In the syntax:

expr1 is the source value or expression that may contain null

expr2 is the value returned if expr1 is not null

expr3 is the value returned if expr2 is null

In the example shown, the COMMISSION_PCT column is examined. If a value is detected, the second expression of SAL+COMM is returned. If the COMMISSION_PCT column holds a null values, the third expression of SAL is returned.

The argument expr1 can have any data type. The arguments expr2 and expr3 can have any data types except LONG. If the data types of expr2 and expr3 are different, The Oracle server converts expr3 to the data type of expr2 before comparing them unless expr3 is a null constant. In that case, a data type conversion is not necessary.

The data type of the return value is always the same as the data type of expr2, unless expr2 is character data, in which case the return value’s data type is VARCHAR2.

49 of 66

Using the NULLIF Function

SELECT first_name, LENGTH(first_name) "expr1",

last_name, LENGTH(last_name) "expr2",

NULLIF(LENGTH(first_name), LENGTH(last_name)) result

FROM employees;

…

1

2

3

1

2

3

3-49

The NULLIF Function

The NULLIF function compares two expressions. If they are equal, the function returns null. If they are not equal, the function returns the first expression. You cannot specify the literal NULL for first expression.

Syntax

NULLIF (expr1, expr2)

In the syntax:

expr1 is the source value compared to expr2

expr2 is the source value compared with expr1. (If it is not equal to expr1, expr1 is returned.)

In the example shown, the job ID in the EMPLOYEES table is compared to the job ID in the JOB_HISTORY table for any employee who is in both tables. The output shows the employee’s current job. If the employee is listed more than once, that means the employee has held at least two jobs previously.

Note: The NULLIF function is logically equivalent to the following CASE expression. The CASE expression is discussed in a subsequent page:

CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END

50 of 66

Using the COALESCE Function

The advantage of the COALESCE function over the NVL function is that the COALESCE function can take multiple alternate values.
If the first expression is not null, it returns that expression; otherwise, it does a COALESCE of the remaining expressions.

3-50

51 of 66

Using the COALESCE Function

SELECT last_name,

COALESCE(commission_pct, salary, 10) comm

FROM employees

ORDER BY commission_pct;

…

3-51

52 of 66

Conditional Expressions

Provide the use of IF-THEN-ELSE logic within a SQL statement
Use two methods:

CASE expression
DECODE function

3-52

53 of 66

The CASE Expression

Facilitates conditional inquiries by doing the work of

an IF-THEN-ELSE statement:

CASE expr WHEN comparison_expr1 THEN return_expr1

[WHEN comparison_expr2 THEN return_expr2

WHEN comparison_exprn THEN return_exprn

ELSE else_expr]

END

3-53

The CASE Expression

CASE expressions let you use IF-THEN-ELSE logic in SQL statements without having to invoke procedures.

In a simple CASE expression, Oracle searches for the first WHEN ... THEN pair for which expr is equal to comparison_expr and returns return_expr. If none of the WHEN ... THEN pairs meet this condition, and an ELSE clause exists, then Oracle returns else_expr. Otherwise, Oracle returns null. You cannot specify the literal NULL for all the return_exprs and the else_expr.

All of the expressions ( expr, comparison_expr, and return_expr) must be of the same data type, which can be CHAR, VARCHAR2, NCHAR, or NVARCHAR2.

Instructor Note

There is also a searched CASE expression. Oracle searches from left to right until it finds an occurrence of a condition that is true, and then returns return_expr. If no condition is found to be true, and an ELSE clause exists, Oracle returns else_expr. Otherwise Oracle returns null. For more information, see Oracle9i SQL Reference, “Expressions.”

54 of 66

Using the CASE Expression

Facilitates conditional inquiries by doing the work of

an IF-THEN-ELSE statement:

SELECT last_name, job_id, salary,

CASE job_id WHEN 'IT_PROG' THEN 1.10*salary

WHEN 'ST_CLERK' THEN 1.15*salary

WHEN 'SA_REP' THEN 1.20*salary

ELSE salary END "REVISED_SALARY"

FROM employees;

…

3-54

55 of 66

The DECODE Function

Facilitates conditional inquiries by doing the work of a CASE or IF-THEN-ELSE statement:

DECODE(col|expression, search1, result1

[, search2, result2,...,]

[, default])

3-55

56 of 66

Using the DECODE Function

SELECT last_name, job_id, salary,

DECODE(job_id, 'IT_PROG', 1.10*salary,

'ST_CLERK', 1.15*salary,

'SA_REP', 1.20*salary,

salary)

REVISED_SALARY

FROM employees;

…

3-56

57 of 66

Using the DECODE Function

Display the applicable tax rate for each employee in department 80.

SELECT last_name, salary,

DECODE (TRUNC(salary/2000, 0),

0, 0.00,

1, 0.09,

2, 0.20,

3, 0.30,

4, 0.40,

5, 0.42,

6, 0.44,

0.45) TAX_RATE

FROM employees

WHERE department_id = 80;

3-57

58 of 66

Summary

In this lesson, you should have learned how to:

Perform calculations on data using functions
Modify individual data items using functions
Manipulate output for groups of rows using functions
Alter date formats for display using functions
Convert column data types using functions
Use NVL functions
Use IF-THEN-ELSE logic

3-58

59 of 66

Practice 3, Part Two: Overview

This practice covers the following topics:

Creating queries that require the use of numeric, character, and date functions
Using concatenation with functions
Writing case-insensitive queries to test the usefulness of character functions
Performing calculations of years and months of service for an employee
Determining the review date for an employee

3-59

Practice 3, Part Two

This practice is designed to give you a variety of exercises using different functions available for character, number, and date data types.

Remember that for nested functions, the results are evaluated from the innermost function to the outermost function.

Instructor Note

This practice should be done in two parts. Part 1 contains questions 1-5 which cover the material from pages 1-23. Part 2 contains questions 6-14 and matches the material for the remainder of the lesson.

Practice question 6: Be sure to tell the students that their results may differ from the one provided, because SYSDATE is used in the exercise.

Instructor hint for practice question 10: The ORDER BY clause in the solution sorts on TO_CHAR(hiredate-1, 'd'). The format element ‘d’ returns a ‘1’ for Sunday, ‘2’ for Monday, and so forth. The expression hiredate-1 effectively “shifts” each hiredate to the previous day, so that an employee hired on a Monday appears to have been hired on Sunday. The TO_CHAR function returns a ‘1’ for that employee and the result set is sorted beginning with those employees hired on Monday.

60 of 66

3-60

61 of 66

3-61

62 of 66

3-62

63 of 66

3-63

64 of 66

3-64

65 of 66

3-65

66 of 66

3-66