1 / 55

Database Programming

Database Programming. Sections 1 & 2 – Case and Character Manipulations, number functions, date functions, conversion functions, general functions, conditional expressions, Null functions. DUAL function. The DUAL table has one row called "X" and one column called "DUMMY.“

felcia
Download Presentation

Database Programming

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

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

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

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

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

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

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

  11. Single Row Functions • Character Functions (Case manipulation) Marge Hohly

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

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

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

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

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

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

  18. Date Functions Marge Hohly

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

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

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

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

  23. Date Types Marge Hohly

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  38. RR Date Format-dates over 2 centuries Marge Hohly

  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 < TO_DATE('01-Jan-90', 'DD-Mon-RR'); Marge Hohly

  40. Try this • SELECT last_name, hire_date, TO_CHAR(hire_date, 'DD-Mon-RRRR')FROM employeesWHERE TO_DATE(hire_date, 'dd-mon-RR') < '01 Jan 1999‘ Marge Hohly

  41. YY and RR • SELECT TO_CHAR(TO_DATE(hire_date, 'DD-Mon-RR'),'DD Mon YYYY') AS "RR Example“FROM employees; • SELECT TO_CHAR(TO_DATE(hire_date, 'DD-Mon-YY'),'DD Mon YYYY') AS "YY Example“FROM employees YY Example17 Jun 208721 Sep 208913 Jan 2093 RR Example17 Jun 198721 Sep 198913 Jan 1993 Marge Hohly

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

  43. Nested Functions • Nesting is allowed to any depth • Evaluate from the inside out Marge Hohly

  44. Null Functions • Null is unavailiable, unassigned, unknown, or inapplicable. • NVL • NVL2 • NULLIF • COALESCE Marge Hohly

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

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

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

  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 is:NULLIF(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 Them“FROM D_PARTNERS; Marge Hohly

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

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

More Related