Database Programming

1
Database Programming

• Sections 1 2 Case and Character
  Manipulations, number functions, date functions,
  conversion functions, general functions,
  conditional expressions, Null functions

2
DUAL function

• The DUAL table has one row called "X" and one
  column called "DUMMY.
• The DUAL table is used to create SELECT
  statements and execute commands not directly
  related to a specific database table.

3
Single Row Functions

• Single row functions are very powerful
  pre-defined code that accepts arguments and
  returns a value. An argument can be defined as a
  column name, an expression, or a constant.
• There are five single row functions groups
• Character
• Date
• General
• Number
• Conversion

4
Single Row Functions

• Single-row character functions are divided into
  two categories
• functions that convert the case of character
  strings
• functions that can join, extract, show, find,
  pad, and trim character strings.
• Single-row functions can be used in the SELECT,
  WHERE, and ORDER BY clauses.

5
Single Row Functions

• Character Functions (Case manipulation)
• LOWER converts character strings to all lower
  case.SELECT last_nameFROM employeesWHERE
  last_name kingWHERE LOWER(last_name)
  king (should be this way)
• UPPER converts character strings to all upper
  case.
• INITCAP converts the first letter of each word to
  upper case and the remaining letters to lower
  case.

6
DUAL examples

• SELECT LOWER('Marge')FROM dual
• SELECT UPPER(Hello)FROM dual
• SELECT SYSDATEFROM dual

7
LOWER examples

• Create a query that outputs the CD titles in the
  DJ on Demand database in all lowercase
  letters.SELECT LOWER(title)FROM d_cds
• Create a query that selects the first names of
  the DJ on Demand clients who have an "a"
  somewhere in their name. Output the results set
  in all uppercase letters. Ask students why UPPER
  was put in the SELECT statement and not in the
  WHERE clause.SELECT UPPER(first_name)FROM
  d_clientsWHERE first_name LIKE 'a'

8
Character Functions

• Character Functions (Case manipulation)

9
Using LOWER, UPPER INITCAP

• Use LOWER, UPPER, INITCAP in SELECT statement
  to affect the output of the data
• Use in WHERE ORDER BY to determine how data is
  chosen not displayed
• SELECT last_name,job_idFROM employeesWHERE
  LOWER(job_id) it_prog
• SELECT UPPER(last_name),job_idFROM employees

10
Character Functions

• Character Functions (Case manipulation)
• CONCAT joins two values together.
• SUBSTR extracts a string of characters from a
  value.
• LENGTH shows the length of a string as a numeric
  value.
• LPAD/RPAD pads specified character to the left or
  right.
• TRIM trims leading, trailing, or both characters
  from a string.
• REPLACE replaces a string of characters.

11
Single Row Functions

• Character Functions (Case manipulation)

12
Try these

• SELECT SUBSTR(hire_date, 2, 4)FROM employees
• SELECT LENGTH(last_name), last_nameFROM
  employees
• SELECT LPAD(SUBSTR(123-56-8901,11,)FROM
  dual

13
Single Row Functions

• Character Functions (Case manipulation)

14
Try These

• SELECT LPAD(salary, 9, '')FROM employees
• SELECT TRIM(trailing 'a from 'abbba')FROM dual
• SELECT TRIM(both 'a from 'abbba')FROM dual
• SELECT REPLACE('ABC', 'B','')FROM dual

15
Single Row Functions

• Number Functions
• ROUND rounds a value to specified position.
• TRUNC truncates a value to a specified position.
• MOD returns the remainder of a divide operation.
• SELECT ROUND(45.927, 2), ROUND(45.927, 0),
  ROUND(45.927), ROUND(45.927, -1) FROM dual
• SELECT TRUNC(45.927, 2),TRUNC(45.927,
  0),TRUNC(45.927),TRUNC(45.927, -1) FROM dual
• SELECT MOD(600, 500)FROM dual

16
Single Row Functions

• Working with Dates
• the default display and input format for any date
  is DD-MON-RR. For example 12-OCT-05 (more on RR
  later)
• SYSDATE is a date function that returns the
  current database server date and time.
• the Oracle database stores dates in an internal
  numeric format. Which means arithmetic operations
  can be performed on dates.

17
Examples

• SELECT (SYSDATE - hire_date)/7 AS "No. of
  WeeksFROM employees
• SELECT MONTHS_BETWEEN(SYSDATE, '01-Jan-87') AS
  "no. of monthsFROM dual
• SELECT ROUND(MONTHS_BETWEEN(SYSDATE,
  '01-Jan-87'),2) AS "no. of monthsFROM dual
• SELECT NEXT_DAY('01-Sep-95','Friday')FROM dual

18
Date Functions
19
Single Row Functions

• Working with Dates (a few examples)
• SELECT last_name, hire_date 60 AS "Review
  DateFROM employees
• SELECT last_name, (SYSDATE-hire_date)/7FROM
  employees
• SELECT order_no,amt_due,purch_date 30 AS "Due
  DateFROM transactions

20
Single Row Functions

• Date Functions
• MONTHS_BETWEEN returns the number of months
  between two dates.
• ADD_MONTHS adds a number of months to a date.
• NEXT_DAY returns the date of the next specified
  day of the week.
• LAST_DAY returns the date of the last day of the
  specified month.
• ROUND returns the date rounded to the unit
  specified.
• TRUNC returns the date truncated to the unit
  specified.

21
Single Row Functions

• Date Functions (a few examples)

22
Single Row Functions

• Date Functions (a few more examples)
• Assume SYSDATE 25-JUL-95

23
Date Types
24
Implicit Data Type Conversion

• For assignments, the Oracle serve can
  automatically convert the following

25
Explicit Type Conversion
26
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

27
Elements of the Date Format Model

• YYYY
• YEAR
• MM
• MONTH
• MON
• DY
• DAY
• DD

• Full year in numbers
• Year spelled out
• Two-digit value for month
• Full name of the month
• Three-letter abbreviation of the month
• Three-letter abbreviation of the day of the week
• Full name of the day of the week
• Numeric day of the month

28
Examples of Date formatting

• Date conversion to character data
• June 19th, 2004      TO_CHAR(hire_date, 'Month
  ddth, YYYY')
• January 1, 2000      TO_CHAR(hire_date, 'fmMonth
  dd, YYYY')
• MAR 5, 2001           TO_CHAR(hire_date, 'fmMON
  dd, YYYY')
• June 17th Wednesday Nineteen Eighty-Seven
         TO_CHAR(hire_date, 'Month ddth Day YyYYSP')

29
Examples

• Using the current SYSDATE display it in the
  following format
• August 6th, 2004
• August 06, 2004
• AUG 6, 2004
• August 6th, Friday, Two Thousand Four

30
Using Date Format

• SELECT employee_id, TO_CHAR(hire_date,'MM/YY')
  Month_HiredFROM employeesWHERE last_name
  'Higgins'

31
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.

32
Using the TO_CHAR Function with Dates

• SELECT last_name, TO_CHAR(hire_date, 'fmDD Month
  YYYY') AS HIREDATEFROM employees

33
Using the TO_CHAR Function with Numbers

• TO_CHAR (number, format_model)These are some
  of the format elements you can use with the
  TO_CHAR function to display a number value as a
  character

34
Number conversions to Character (VARCHAR2)

• Can you identify the format models used to
  produce the following output?
• 3000.00
• 4,500
• 9,000.00
• 0004422

35
Using the TO_CHAR Function with Numbers

• SELECT TO_CHAR(salary, '99,999.00') SALARYFROM
  employeesWHERE last_name 'Ernst

36
Using the TO_NUMBER and TO_DATE Functions

• Convert a character string to a number format
  using the TO_NUMBER functionTO_NUMBER(char,
  format_model)
• Convert a character string to a date format using
  the TO_DATE functionTO_DATE(char,
  format_model)
• These functions have a fx modifier. This
  modifier specifies the exact matching for the
  character argument and date format model of a
  TO_DATE function

37
Using fx modifier

• Use the fx modifier to format dates exactly as
  follows
• June19     2004
• July312004
• Format your birth date use DUAL
• Example June 19, 1990

38
RR Date Format-dates over 2 centuries
39
Example of RR Date Format

• To find employees hired prior to 1990, use the RR
  format, which produces the same results whether
  the commands is run in 1999 or now
• SELECT last_name, TO_CHAR(hire_date,
  'DD-Mon-YYYY')FROM employeesWHERE hire_date lt
  TO_DATE('01-Jan-90', 'DD-Mon-RR')

40
Try this

• SELECT last_name, hire_date, TO_CHAR(hire_date,
  'DD-Mon-RRRR')FROM employeesWHERE
  TO_DATE(hire_date, 'dd-mon-RR') lt '01 Jan 1999

41
YY and RR

• SELECT TO_CHAR(TO_DATE(hire_date,
  'DD-Mon-RR'),'DD Mon YYYY') AS "RR ExampleFROM
  employees
• SELECT TO_CHAR(TO_DATE(hire_date,
  'DD-Mon-YY'),'DD Mon YYYY') AS "YY ExampleFROM
  employees
• YY Example17 Jun 208721 Sep 208913
  Jan 2093

• RR Example17 Jun 198721 Sep 198913
  Jan 1993

42
2.1.14 2.1.15 Examples

• 2. Convert January 3, 2004, to the default date
  format 03-JAN-04.
• 4. Convert today's date to a format such as
  "Today is the Twentieth of March, Two Thousand
  Four
• 8. Create one query that will convert 25-DEC-04
  into each of the following (you will have to
  convert 25-DEC-04 to a date and then to character
  data)
• December 25th, 2004
• DECEMBER 25TH, 2004
• december 25th, 2004

43
Nested Functions

• Nesting is allowed to any depth
• Evaluate from the inside out

44
Null Functions

• Null is unavailiable, unassigned, unknown, or
  inapplicable.
• NVL
• NVL2
• NULLIF
• COALESCE

45
NVL FUNCTION

• NVL function converts a null value to a date, a
  character, or a number.
• The data types of the null value column and the
  new value must be the same.
• NVL (value that may contain a null, value to
  replace the null)
• can be used to convert column values containing
  nulls to a number before doing calculations.
• When arithmetic calculation is performed with
  null, the result is null.

46
NVL FUNCTION examples

• SELECT NVL(auth_expense_amt,0) FROM d_partners
• SELECT NVL(hire_date,'01-JAN-97')FROM employees
• SELECT NVL(specialty,'None Yet')FROM d_partners
• SELECT first_name, last_name,NVL(auth_expense_amt,
  0) 1.05 AS ExpensesFROM D_Partners

47
NVL2  FUNCTION

• NVL2 (expression 1 value that may contain a null,
  expression 2 value to return if expression 1 is
  not null, expression 3 value to replace if
  expression 1 is null)
• SELECT last_name, salary, NVL2(commission_pct,
  salary (salary commission_pct), salary)
  incomeFROM employees

48
NULLIF FUNCTION

• NULLIF function compares two functions.
• If they are equal, the function returns null.
• If they are not equal, the function returns the
  first expression.
• The NULLIF function isNULLIF(expression 1,
  expression 2)
• SELECT first_name, LENGTH(first_name) "Expression
  1",last_name, LENGTH(last_name) "Expression 2",
  NULLIF(LENGTH(first_name), LENGTH(last_name)) AS
  "Compare ThemFROM D_PARTNERS

49
COALESCE  FUNCTION

• The COALESCE function is an extension of the NVL
  function, except COALESCE can take multiple
  values.
• If the first expression is null, the function
  continues down the line until a not null
  expression is found.
• If the first expression has a value, the function
  returns the first expression and the function
  stops.

50
DP 2.10.2,4

• Not all Global Fast Foods staff members receive
  overtime pay. Instead of displaying a null value
  for these employees, replace null with zero.
  Include the employee's last name and overtime
  rate in the output. Label the overtime rate as
  "Overtime Status."
• Not all Global Fast Foods staff members have a
  manager. Create a query that displays the
  employee last name and 9999 in the manager ID
  column for these employees.

51
Conditional Expressions

• Provide the use of IF-THEN-ELSE logic within a
  SQL statement
• Use two methods
• CASE expressions
• DECODE function

52
CASE Function when then - else

• Facilitates conditional inquiries by doing the
  work of an IF-THEN-ELSE statementSELECT
  last_name, job_id, salary, CASE job_id WHEN
  'IT_PROG' THEN 1.10salary
  WHEN 'ST_CLERK' THEN 1.15salary
  WHEN 'SA_REP' THEN 1.20salary ELSE
  salary END "REVISED_SALARYFROM employees

53
DECODE Function

• Facilitates conditional inquires by doing the
  work of a CASE or IF_THEN_ELSE statementSELECT
  last_name, job_id, salary, DECODE(job_id,
  'IT_PROG',1.10salary,
  'ST_CLERK',1.15salary,
  'SA_REP',1.20salary, salary)
  "REVISED_SALARYFROM employees

54
DECODE Example

• 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

• Monthly Salary Range Rate
• 0.00 1999.99 00
• 2,000.00 3,999.99 09
• 4,000.00 5,999.99 20
• 6,000.00 7,999.99 30
• 8,000.00 9,999.99 40
• 10,000.00 11,999.99 42
• 12,200.00 13,999.99 44
• 14,000.00 or greater 45

55
2.3.5 Practice

• 1. For each Global Fast Foods promotional menu,
  display the event name, and calculate the number
  of months between today and the ending date of
  the promotion. Round the months to a whole
  number. Label the column "Past Promos."
• 2. Use the Oracle database to write a query that
  returns the salary for employee 174 as
• Ellen Abel earns 11000.00 monthly but wants
  14000.00