Chapter Seven part one Data Manipulation Language DML Functions - PowerPoint PPT Presentation

1 / 34
About This Presentation
Title:

Chapter Seven part one Data Manipulation Language DML Functions

Description:

Date functions. 2. Functions. Introduction. Types of functions. Single ... DATE: Date is stored in an internal numeric format: century, year, month, day, hours, ... – PowerPoint PPT presentation

Number of Views:137
Avg rating:3.0/5.0
Slides: 35
Provided by: scie6
Category:

less

Transcript and Presenter's Notes

Title: 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
Write a Comment
User Comments (0)
About PowerShow.com