This presentation is the property of its rightful owner.
1 / 32

# Chapter Six Data Manipulation Language (DML) Functions PowerPoint PPT Presentation

Chapter Six Data Manipulation Language (DML) Functions. Objectives Single Row functions Character functions Number functions Date functions. Functions. Introduction Types of functions Single row Multiple rows. Single Row Functions:. FACTS: Act on each row

Chapter Six Data Manipulation Language (DML) Functions

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

## Chapter SixData Manipulation Language (DML)Functions

Objectives

Single Row functions

Character functions

Number functions

Date functions

### Functions

• Introduction

• Types of functions

• Single row

• Multiple rows

## Single Row Functions:

FACTS:

Act on each row

Return one result per row

May modify the data type returned type

Can be nested

• Character

• Number

• Date

• Conversion

• General

### Character Manipulation:

• LOWER(Col | Exp)

LOWER(‘Database course’)

• UPPER (Col | Exp)

UPPER (‘Database course’)

• INITCAP (Col | Exp)

INITCAP (‘Database course’)

### 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:

SMITHLori

### Character Manipulation

• CONCAT (Col1 | Exp1, Col2 | Exp2)

CONCAT(‘This ‘,’that’)

• SUBSTR(Col | Exp,n,[m])

SUBSTR(‘This is it’,2,5)

### Character Manipulation

• LENGTH(Col | Exp)

LENGTH(‘this is it’)

• CHR(integer)

CHR(97)

• INSTR(‘Computer’,’m’)

### List(Characters):

• Example:

• SELECTLOWER(name),

UPPER(major), LENGTH(name)

FROMstudent;

• SELECT CONCAT(Name , Address) , GPA

FROMStudent;

• SELECT Name || ‘--->‘ || Address , GPA

FROMStudent;

### List(Characters):

• SELECTRPAD (Name, 40, ‘.’), GPA

FROMStudent;

JOHN............... 3.1

MARY ............. 3.2

FROMStudent;

MARY 3.2

FROMStudent;

MARY3.2

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

### List(Characters):

• RTRIM(Col)

RTRIM (Name)

RTRIM (Name, ‘.’)

LTRIM (Name, ‘ABC’)

LTRIM ( RTRIM( Name, ‘.’ ), ‘a’)

From Student;

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

### List(Characters):

• SELECTName

FROMStudent

• SELECTName, SUBSTR (SSN, 5 ,2)

FROMStudent;

• SELECTName, SUBSTR (SSN,5)

FROMStudent;

### List(Characters):

SUBSTR (SSN,5)

FROM Student;

• SELECTName

FROMStudent

WHERESUBSTR (SSN,5,2)=’80’;

• SELECTName, SUBSTR (SSN,-4)

FROMStudent;

### Practice:

• Display the 10 characters from OrderPart description starting at location 6.

• Example:

Door Replacement Handle

### List(Characters):

• SELECTName, INSTR (Name,’r’)

FROMStudent;

------------------------------------------------

MARY3

JOHN0

ROBIN1

• SELECT Name, INSTR (Name,’r’,1,2)

FROMStudent;

FROMStudent;

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

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

### LIST(Numbers)

• SELECTROUND(Salary,1)

FROMFaculty;

• SELECTTRUNC(234.111,2),

FROMDUAL;

TRUNC(234.567);

TRUNC(234.5678,-2);

### DATE:

• Date is stored in an internal numeric format: century, year, month, day, hours, minutes, second

• Default date is DD-MON-YY

• SYSDATE

### DATE:

• Example:

List the ages of students

• SELECTname, SYSDATE - B_Date

FROMstudent;

### Practice:

• Display today’s date.

### Date

• Date + number

• Date – number

• Date – date

• Date + number/24

### DATE:

• MONTHS_BETWEEN(day1,day2)

• SELECT name, MONTHS_BETWEEN(SYSDATE , B_Date) age_in_month

FROMStudent;

FROMStudent;

### DATE:

• ROUND(date [,fmt])

• SELECT name, ROUND(B_Date,’MONTH’)

FROMStudent;

• SELECT name, ROUND(B_Date,’YEAR’)

FROMStudent;

### Conversion Function:

Implicit conversion (Automatic):

• CHAR or VARCHAR2 to NUMBER

• CHAR or VARCHAR2 to DATE

• NUMBER to VARCHAR2

• DATE to VARCHAR2

### Conversion Function:

Explicit datatype conversion:

• TO_CHAR (NUMBER [,‘fmt’] )

• TO_CHAR (DATE [,‘fmt’] )

• TO_DATE (CHAR [,‘fmt’] )

• TO_NUMBER (CHAR [,‘fmt’] )

### Conversion Function:

SELECT TO_CHAR(b_date,’MM/YY’)

FROM student;

Format:

• YYYY

• YEAR

• MM

• MONTH

• DY

• DAY

### Conversion Function:

• SELECTSUBSTR(TO_CHAR(111223333),1,3) ||‘-’ ||

SUBSTR (TO_CHAR(111223333),4,2) || ‘-’ ||

SUBSTR(TO_CHAR(111223333),6)

FROMStudent;

### Conversion Function:

• SELECTSUBSTR(ssn,1,3)

|| ‘-’ ||

SUBSTR(ssn,4,2)

|| ‘-’ ||

SUBSTR(ssn,6)

FROMStudent;