Less03Using Single-Row Functions to

After completing this lesson, you should be able to do the following:

Describe various types of functions that are available in SQL

Use character, number, and date functions in SELECT statements

Describe the use of conversion functions

Accept arguments that can be a column or an expression

Single-row functions:

Manipulate data items

Accept arguments and return one value

Act on each row that is returned

Return one result per row

May modify the data type

Can be nested

SELECT employee_id, last_name, department_id

FROM   employees

WHERE  LOWER(last_name) = 'higgins';

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

       ROUND(45.923,-1)

FROM   DUAL;

For all employees with job title of Sales Representative, calculate the remainder of the salary after it is divided by 5,000.

SELECT last_name, salary, MOD(salary, 5000)

FROM   employees

WHERE  job_id = 'SA_REP';

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

The default date display format is DD-MON-RR.

Enables you to store 21st-century dates in the
20th century by specifying only the last two digits of the year

Enables you to store 20th-century dates in the
21st century in the same way 

SELECT last_name, hire_date

FROM   employees

WHERE  hire_date < '01-FEB-88';

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

FROM   employees

WHERE  department_id = 90;

SELECT last_name,

       TO_CHAR(hire_date, 'fmDD Month YYYY')

       AS HIREDATE

FROM   employees;

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

FROM   employees

WHERE  last_name = 'Ernst';

To find employees hired prior to 1990, use the RR date 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');

SELECT last_name,

  UPPER(CONCAT(SUBSTR (LAST_NAME, 1, 8), '_US'))

FROM   employees

WHERE  department_id = 60;

The following 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)

Converts a null value 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')

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

(salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SA    FROM employees; 

SELECT last_name,  salary, commission_pct,

       NVL2(commission_pct,

            'SAL+COMM', 'SAL') income

FROM   employees WHERE department_id IN (50, 80);

SELECT first_name, LENGTH(first_name) "expr1",

       last_name,  LENGTH(last_name)  "expr2",

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

FROM   employees;

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, the COALESCE function returns that expression; otherwise, it does a COALESCE of the remaining expressions.

SELECT last_name,

       COALESCE(manager_id,commission_pct, -1) comm

FROM   employees

ORDER BY commission_pct;

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

CASE expression

DECODE function

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;

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;

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;

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

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