Chapter Seven part one Data Manipulation Language DML Functions Dr' Chitsaz

1
Chapter Seven (part one)Data Manipulation
Language (DML)FunctionsDr. Chitsaz

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

7
Character Manipulation

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

8
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

9
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

10
List(Characters)

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

11
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

12
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

13
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

14
List(Characters)

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

15
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)

16
LIST(Numbers)

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

17
DATE

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

18
DATE

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

19
Date

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

20
DATE

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

21
DATE

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

22
DATE

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

23
Conversion Function

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

24
Conversion Function

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

25
Conversion Function

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

26
Conversion Function

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

27
Conversion Function

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

28
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