Database Programming - Study Guide Section 2

Name ______Date ______

  1. List and describe the Explicit Data-Type Conversion functions?

TO_CHAR – converts a number or a date value to a character string with a specified format.

TO_NUMBER – converts a character string containing digits to a number in the format specified.

TO_DATE – converts a character string representing a date to a date value according to a specified format.

  1. List and describe the Implicit Data-Type Conversion functions?

For an assignment:

VARCHAR2 or CHAR to NUMBER

VARCHAR2 or CHAR to DATE

NUMBER to VARCHAR2

DATE to VARCHAR2

For an expression:

VARCHAR2 or CHAR to NUMBER

VARCHAR2 or CHAR to DATE

  1. What element is added to the format of an explicit data-type conversion function to remove padded blanks or suppress leading zeros? Give an example.

fm

  1. Create the SQL SELECT clause to display the hire_date as follows:

Today’s date is: September 29, 1963

TO_CHAR(‘”Today’s date is: “fmMonth DD, YYYY’)

  1. If numbers represent currency, what function would you use? Give an example.

TO_CHAR

TO_CHAR(salary * 12, ‘$999,999.99’)

  1. Create the SQL SELECT clause to display the salary as follows:

salary: $123,000.99

TO_CHAR(salary, ‘$999,999.99’)

  1. What happens when a number exceeds the format specified? How is this corrected?

The Oracle Server displays a string of hash signs (#) in place of a whole number whose digits exceed the number of digits provided in the format.

  1. Explain the difference between YY and RR date format?

The YY and RR date formats are alike, they represent years in a century. The RR date format can be used to specify different centuries.

  1. Explain nesting? Give an example.

The calculation of a column of data by a function of a function.

SUM(AVG(salary)) SUM(NVL(commission_pct, 0))

  1. How deep can functions be nested?

Single-row functions can be nested to any depth.

  1. How are nested functions evaluated?

Evaluated from the inner function to the outer function.

  1. List and describe the four general null functions?

NVL – converts a null value to an actual value

NVL2 – If expr1 is not null, NVL2 returns expr2. If expr1 is null, NVL2 returns expr3.

NULLIF – compares two expressions and returns null if they are equal, or the first expression if they are not equal.

COALESCE – returns the first non-null expression in the expression list.

  1. Create a SQL query that displays SALARY for an employee with a salary and NO SALARY for an employee without a salary.

SELECT NVL2(salary, ‘SALARY’, ‘NO SALARY’)

  1. Create a SQL query that displays the salary or a zero if there is no salary of all employees.

SELECT NVL(salary, 0)

  1. List and describe the two IF-THEN-ELSE logic methods? Give an example of each.

CASE – ANSI SQL complied of an if-then-else logic method

DECODE – ORACLE specific of an if-then-else logic method

Oracle Academy11-May-2005