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
Case/Character Manipulation
5
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.

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

7
Case Manipulation

• LOWER(columnexpression) converts alpha
  characters to lower-case.
• UPPER(columnexpression) converts alpha character
  to upper case
• INITCAP(columnexpression) converts alpha
  character values to uppercase for the first
  letter of each word. (Title Case)

8
Character Functions

• Character Functions (Case manipulation)

9
DUAL examples

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

10
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'

11
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

12
Character Functions

• Character Functions (Character 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.

13
Single Row Functions

• Character Functions (Character manipulation)

14
Try these

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

15
Single Row Functions

• Character Functions (Character manipulation)

16
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

17
Terminology Review

• DUAL- Dummy table used to view results from
  functions and calculations
• Format-The arrangement of data for storage or
  display.
• INITCAP-Converts alpha character values to
  uppercase for the first letter of each word, all
  other letters in lowercase.

18
Terminology cont.

• Character functions-Functions that accept
  character data as input and can return both
  character and numeric values.
• TRIM-Removes all specified characters from either
  the beginning or the ending of a string.
• Expression -A symbol that represents a quantity
  or a relationship between quantities

19
Terminology cont.

• Single- row functions-Functions that operate on
  single rows only and return one result per row
• UPPER-Converts alpha characters to upper case
• Input-Raw data entered into the computer
• CONCAT-Concatenates the first character value to
  the second character value equivalent to
  concatenation operator ().

20
Terminology cont.

• Output-Data that is processed into information
• LOWER-Converts alpha character values to
  lowercase.
• LPAD-Pads the left side of a character, resulting
  in a right-justified value
• SUBSTR-Returns specific characters from character
  value starting at a specific character position
  and going specified character positions long

21
Use Alias in Functions

• Aliases can be used in commands to replace column
  name etc.
• SELECT LOWER(SUBSTR(first_name,1,1))
  LOWER(last_name) AS User NameFROM f_staffs

22
Terminology cont.

• REPLACE-Replaces a sequence of characters in a
  string with another set of characters.
• INSTR-Returns the numeric position of a named
  string.
• LENGTH-Returns the number of characters in the
  expression
• RPAD-Pads the right-hand side of a character,
  resulting in a left- justified value.

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

24
Single Row Functions

• Number Functions
• ROUND rounds a value to specified position.
• ROUND(columnexpression, decimal places)
• Default is 0 decimals
• SELECT ROUND(45.927, 2), ROUND(45.927, 0),
  ROUND(45.927), ROUND(45.927, -1) FROM dual

25
Single Row Functions

• TRUNC truncates a value to a specified position.
• TRUNC(columnexpression, decimal places)
• SELECT TRUNC(45.927, 2),TRUNC(45.927,
  0),TRUNC(45.927),TRUNC(45.927, -1) FROM dual
• TRUNC(45.927, 2) 45.92
• TRUNC(45.927, 0) 45
• TRUNC(45.927) 45
• TRUNC(45.927, -1) 40

26
Mod demo

• MOD returns the remainder of a divide operation.
• MOD(1st value, 2nd value)
• The 1st value is divided by the 2nd value
• SELECT MOD(600, 500)FROM dual
• SELECT last_name, salary, MOD(salary, 2) AS Mod
  DemoFROM f_staffsWHERE staff_type IN(Order
  Taker, Cook, Manager)

27
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.
• default date DD-MON-RR. Oracle dates are between
  1/1/4712 B.C. and 12/31/9999 A.D.
• Stores year as a 4 digit value, 2 digit century,
  2 digit year

28
Date Functions Example
29
Examples

• SELECT SYSDATEFROM DUAL
• 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

30
Date Functions
31
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

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

33
Single Row Functions

• Date Functions (a few examples)

34
Single Row Functions

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

35
Date Types
36
Data Types

• VARCHAR2 Used for character data of variable
  length, including numbers, special characters,
  and dashes.
• CHAR Used for text and character data of fixed
  length, including numbers, dashes, and special
  characters.
• NUMBER Used to store variable-length numeric
  data. No dashes, text, or other nonnumeric data
  are allowed. Currency is stored as a number data
  type.
• DATE Used for date and time values. Internally,
  Oracle stores dates as numbers and by default
  DATE information is displayed as DD-MON-YY (for
  example, 16-OCT-07).

37
Implicit Data Type Conversion

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

38
Explicit Type Conversion
39
Using the TO_CHAR Function with Dates

• The format model
• TO_CHAR(date column name, format model you
  specify)
• 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

40
Using the TO_CHAR Function with Dates

• Use sp to spell out a number
• Use th to have the number appear as an ordinal
• Use double quotation marks to add character
  strings to format models

41
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

42
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')

43
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

44
Using Date Format

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

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

46
Using the TO_CHAR Function with Dates

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

47
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

48
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

49
Using the TO_CHAR Function with Numbers

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

50
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

51
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

52
RR Date Format-dates over 2 centuries
53
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')

54
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

55
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

56
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

57
Nested Functions

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

58
(No Transcript)
59
Null Functions

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

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

61
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

62
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

63
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

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

65
Examples Section 2 Lesson 2

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

66
Conditional Expressions

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

67
(No Transcript)
68
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

69
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

70
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

71
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