data base part 2

• Database Management System


  A system that manages a database
• Single-Row Function


  A function that operates on one row at a time
• Functions are a very powerful feature of SQL
• Things functions can be used for

  • Perform calculations on data
  • Modify individual data items
  • Manipulate output for groups of rows
  • Format dates and numbers for display
  • Convert column data types
• Types of Functions


  • Single-Row Function
  • Multiple-Row Function
• Single-Row Functions


  • Character Function
  • Number Function
  • Case Manipulation
  • Character Manipulation
• LOWER


  Converts mixed-case or uppercase character strings to lowercase
• UPPER


  Converts mixed-case or lowercase character strings to uppercase
• INITCAP


  Converts the first letter of each word to uppercase and the remaining letters to lowercase
• Case Conversion Functions


  • LOWER('Database Management System')
  • UPPER('Database Management System')
  • INITCAP('Database Management System')
• Using Case-Conversion Functions


  1. UPPER(LNAME||','||FNAME) AS "NAME"

  2. LOWER(LNAME||','||FNAME) AS "NAME"

  3. INITCAP(LNAME||','||FNAME) AS "NAME"
• CONCAT


  Joins values together (You are limited to using two parameters with CONCAT.)
• SUBSTR


  Extracts a string of determined length
• LENGTH


  Shows the length of a string as a numeric value
• INSTR


  Finds the numeric position of a named character
• LPAD


  Returns an expression left-padded to the length of n characters with a character expression
• RPAD


  Returns an expression right-padded to the length of n characters with a character expression
• TRIM


  Trims leading or trailing characters (or both) from a character string
• Using Character Manipulation Functions

  1. CONCAT(LNAME,FNAME), YR_PUB

  2. LNAME, FNAME, SUBSTR(BOOK,6)

  3. BOOK,LENGTH(BOOK), INSTR(BOOK,'A')

  4. BOOK, LPAD(YR_PUB,7,'$'), RPAD(YR_PUB,7,'@')

  5. REPLACE(FNAME,'A','T')

  6. BOOK, TRIM('A' FROM BOOK)
• ROUND


  Rounds value to a specified decimal
• TRUNC


  Truncates value to a specified decimal
• MOD


  Returns remainder of division
• Using Number Functions


  1. ROUND(75.475,2),ROUND(75.475,1), ROUND(75.475,-1)

  2. TRUNC(75.475,2),TRUNC(75.475,1),TRUNC(75.475,-1)

  3. MOD(1000,300)
• DUAL table


  A table owned by the user SYS that can be accessed by all users, containing one column (DUMMY) and one row (X)
• The DUAL table is useful when you want to return a value only once
• The DUAL table is generally used for completeness of the SELECT clause syntax, because both SELECT and FROM clauses are mandatory, and several calculations do not need to select from the actual tables