Chapter Seven part one Data Manipulation Language DML Functions

1
Chapter Seven (part one)Data Manipulation
Language (DML)Functions

• Objectives
• Single Row functions
• Character functions
• Number functions
• Date functions

2
Functions

• Introduction
• Types of functions
• Single row
• Multiple rows

3
Single Row Functions

• FACTS
• Act on each row
• Return one result per row
• May modify the data type returned type
• Can be nested

4
Single Row Functions

• Character
• Number
• Date
• Conversion
• General

5
Character Manipulation

• LOWER(Col Exp)
• LOWER(Database course)
• UPPER (Col Exp)
• UPPER (Database course)
• INITCAP (Col Exp)
• INITCAP (Database course)

6
Practice

• Display last name and first name of customers.
  Last name in upper case. First character of each
  first name in upper case, the rest in lower case.
  
• Example
• SMITH Lori

7
Character Manipulation

• CONCAT (Col1 Exp1, Col2 Exp2)
• CONCAT(This ,that)
• SUBSTR(Col Exp,n,m)
• SUBSTR(This is it,2,5)
• LPAD(Col Exp,n,string)
• LPAD(name,9,.)

8
Character Manipulation

• LENGTH(Col Exp)
• LENGTH(this is it)
• CHR(integer)
• CHR(97)
• INSTR(Computer,m)

9
List(Characters)

• Example
• SELECT LOWER(name),
• UPPER(major), LENGTH(name)
• FROM student
• SELECT CONCAT(Name , Address) , GPA
• FROM Student
•  
• SELECT Name ---gt Address , GPA
• FROM Student

10
List(Characters)

• SELECT RPAD (Name, 40, .), GPA
• FROM Student
• JOHN............... 3.1
• MARY ............. 3.2
• SELECT RPAD (Name, 20), GPA
• FROM Student
• MARY 3.2
• MOHAMMAD 3.3
• SELECT LPAD (Name, 20), GPA
• FROM Student
• MARY 3.2
• MOHAMMAD 3.3

11
Practice

• Display the last name and address of each
  customer together. In the next column display
  customer phone number.
• SMITH 11 MAIN ST FROSTBURG MD..301 689 1111

12
List(Characters)

• RTRIM(Col)
• RTRIM (Name)
• RTRIM (Name, .)
• LTRIM (Name, ABC)
•  
• LTRIM ( RTRIM( Name, . ), a)
• From Student

13
Practice

• Customer phone number is stored as
• -301-689-1111
• Part 1- We would like to delete the first dash
  and the last colon from the phone numbers
• -301-689-1111
• Part 2- Remove all dashes and colon

14
List(Characters)

• SELECT Name
• FROM Student
• WHERE LENGTH(Address)lt20
• SELECT Name, SUBSTR (SSN, 5 ,2)
• FROM Student
• SELECT Name, SUBSTR (SSN,5)
• FROM Student

15
List(Characters)

• SELECT RPAD (INITCAP(LOWER(Name)),70,.),
• SUBSTR (SSN,5)
• FROM Student
• SELECT Name
• FROM Student
• WHERE SUBSTR (SSN,5,2)80
• SELECT Name, SUBSTR (SSN,-4)
• FROM Student

16
Practice

• Display the 10 characters from OrderPart
  description starting at location 6.
• Example
• Door Replacement Handle

17
List(Characters)

• SELECT Name, INSTR (Name,r)
• FROM Student
•   -----------------------------------------
  -------
• MARY 3
• JOHN 0
• ROBIN 1
• SELECT Name, INSTR (Name,r,1,2)
• FROM Student
• SELECT Name, INSTR(Address,Frostburg)
• FROM Student

18
Practice

• We would like to switch the position of last
  name with the first name. Assume the attribute
  name consists of both first and last names with a
  blank character in between.

19
List(Characters)

• Character Manipulations
• REPLACE(string, searchSt ,replace)
• REPLACE(address,21532, 21211)
• TRANSLATE (string, fromSt, toSt)
• TRANSLATE(12345678, 123, 999)
• ASCII(string)
• ASCII(A)

20
LIST(Numbers)

• ROUND (value, precision)
• ROUND(234.1161,2)
• TRUNC(value, precision)
• TRUNC(234.1161,2)
• POWER(value,exponent)
• POWER(3,2) 
• MOD(value1, value2)
• MOD(900,400)

21
LIST(Numbers)

• SELECT ROUND(Salary,1)
• FROM Faculty
• SELECT TRUNC(234.111,2),
• FROM DUAL
• TRUNC(234.567)
• TRUNC(234.5678,-2)

22
DATE

• Date is stored in an internal numeric format
  century, year, month, day, hours, minutes, second
• Default date is DD-MON-YY
• SYSDATE

23
DATE

• Example
• List the ages of students
•  
• SELECT name, SYSDATE - B_Date
• FROM student

24
Practice

• Display todays date.

25
Date

• Date number
• Date number
• Date date
• Date number/24

26
DATE

• MONTHS_BETWEEN(day1,day2)
• SELECT name, MONTHS_BETWEEN(SYSDATE , B_Date)
  age_in_month
• FROM Student

27
DATE

• ADD_MONTHS (date,n)
•  
• SELECT name, ADD_MONTHS(B_Date,5) age
• FROM Student

28
DATE

• ROUND(date ,fmt)
•  
• SELECT name, ROUND (B_Date,MONTH)
• FROM Student
• SELECT name, ROUND(B_Date,YEAR)
• FROM Student

29
Conversion Function

• Implicit conversion (Automatic)
• CHAR or VARCHAR2 to NUMBER
• CHAR or VARCHAR2 to DATE
• NUMBER to VARCHAR2
• DATE to VARCHAR2

30
Conversion Function

• Explicit datatype conversion
• TO_CHAR (NUMBER ,fmt )
• TO_CHAR (DATE ,fmt )
• TO_DATE (CHAR ,fmt )
• TO_NUMBER (CHAR ,fmt )

31
Conversion Function

• SELECT TO_CHAR(b_date,MM/YY)
• FROM student
• Format
• YYYY
• YEAR
• MM
• MONTH
• DY
• DAY

32
Conversion Function

• SELECT SUBSTR(TO_CHAR(111223333),1,3) -
  
• SUBSTR (TO_CHAR(111223333),4,2) -
• SUBSTR(TO_CHAR(111223333),6)
• FROM Student

33
Conversion Function

• SELECT SUBSTR(ssn,1,3)
• -
• SUBSTR(ssn,4,2)
• -
• SUBSTR(ssn,6)
• FROM Student

34
Use of DECODE

• DECODE
• DECODE (col/exp, compare1, result1
• ,compare2, result2,
• ,default )
• SELECT name, salary,
• DECODE (Dept, COSC, salary2.2,
• MATH, salary1.2,
• ART, salary0.2,
• salary)
• FROM Faculty