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. Marge Hohly

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 Marge Hohly

4. Case/Character Manipulation Marge Hohly

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. Marge Hohly

6. Single Row Functions • Character Functions (Case manipulation) • LOWER converts character strings to all lower case.SELECT last_nameFROM employeesWHERE last_name = ‘king’WHERE 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. Marge Hohly

7. Case Manipulation • LOWER(column|expression) converts alpha characters to lower-case. • UPPER(column|expression) converts alpha character to upper case • INITCAP(column|expression) converts alpha character values to uppercase for the first letter of each word. (Title Case) Marge Hohly

8. Character Functions • Character Functions (Case manipulation) Marge Hohly

9. DUAL examples • SELECT LOWER('Marge')FROM dual; • SELECT UPPER(‘Hello’)FROM dual; • SELECT SYSDATEFROM dual; Marge Hohly

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%'; Marge Hohly

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; Marge Hohly

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. Marge Hohly

13. Single Row Functions • Character Functions (Character manipulation) Marge Hohly

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; Marge Hohly

15. Single Row Functions • Character Functions (Character manipulation) Marge Hohly

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; Marge Hohly

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. Marge Hohly

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 Marge Hohly

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 (||). Marge Hohly

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 Marge Hohly

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 Name”FROM f_staffs; Marge Hohly

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. Marge Hohly

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. Marge Hohly

24. Single Row Functions • Number Functions • ROUND rounds a value to specified position. • ROUND(column|expression, decimal places) • Default is 0 decimals • SELECT ROUND(45.927, 2), ROUND(45.927, 0), ROUND(45.927), ROUND(45.927, -1) FROM dual; Marge Hohly

25. Single Row Functions • TRUNC truncates a value to a specified position. • TRUNC(column|expression, 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 Marge Hohly

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 Demo”FROM f_staffsWHERE staff_type IN(‘Order Taker’, ‘Cook’, ‘Manager’); Marge Hohly

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 Marge Hohly

28. Date Functions Example Marge Hohly

29. Examples • SELECT SYSDATEFROM DUAL; • SELECT (SYSDATE - hire_date)/7 AS "No. of Weeks“FROM employees; • SELECT MONTHS_BETWEEN(SYSDATE, '01-Jan-87') AS "no. of months“FROM dual; • SELECT ROUND(MONTHS_BETWEEN(SYSDATE, '01-Jan-87'),2) AS "no. of months“FROM dual; • SELECT NEXT_DAY('01-Sep-95','Friday')FROM dual; Marge Hohly

30. Date Functions Marge Hohly

31. Single Row Functions • Working with Dates (a few examples) • SELECT last_name, hire_date + 60 AS "Review Date“FROM employees; • SELECT last_name, (SYSDATE-hire_date)/7FROM employees; • SELECT order_no,amt_due,purch_date + 30 AS "Due Date“FROM transactions; Marge Hohly

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. Marge Hohly

33. Single Row Functions • Date Functions (a few examples) Marge Hohly

34. Single Row Functions • Date Functions (a few more examples) • Assume SYSDATE = ’25-JUL-95’ Marge Hohly

35. Date Types Marge Hohly

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). Marge Hohly

37. Implicit Data Type Conversion • For assignments, the Oracle serve can automatically convert the following: Marge Hohly

38. TO_NUMBER TO_DATE NUMBER CHARACTER DATE TO_CHAR TO_CHAR Explicit Type Conversion Marge Hohly

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 Marge Hohly

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 Marge Hohly

41. 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 Elements of the Date Format Model Marge Hohly

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') Marge Hohly

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 Marge Hohly

44. Using Date Format • SELECT employee_id, TO_CHAR(hire_date,'MM/YY') Month_HiredFROM employeesWHERE last_name = 'Higgins'; Marge Hohly

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. Marge Hohly

46. Using the TO_CHAR Function with Dates • SELECT last_name, TO_CHAR(hire_date, 'fmDD Month YYYY') AS HIREDATEFROM employees; Marge Hohly

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: Marge Hohly

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 Marge Hohly

49. Using the TO_CHAR Function with Numbers • SELECT TO_CHAR(salary, '$99,999.00') SALARYFROM employeesWHERE last_name = 'Ernst‘; Marge Hohly

50. Using the TO_NUMBER and TO_DATE Functions • Convert a character string to a number format using the TO_NUMBER function:TO_NUMBER(char[, ‘format_model’]) • Convert a character string to a date format using the TO_DATE function:TO_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 Marge Hohly