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

1 / 34

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

Chapter Seven (part one) 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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

PowerPoint Slideshow about 'Chapter Seven (part one) Data Manipulation Language (DML) Functions' - william-kennedy

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 - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Chapter Seven (part one)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

Return one result per row

May modify the data type returned type

Can be nested

Single Row Functions
• 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:

SMITH Lori

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:
• SELECT LOWER(name),

UPPER(major), LENGTH(name)

FROM student;

• SELECT CONCAT(Name , Address) , GPA

FROM Student;

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

FROM Student;

List(Characters):
• SELECTRPAD (Name, 40, ‘.’), GPA

FROM Student;

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

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

FROM Student;

MARY 3.2

FROM Student;

MARY 3.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):
• SELECT Name

FROM Student

• SELECT Name, SUBSTR (SSN, 5 ,2)

FROM Student;

• SELECT Name, SUBSTR (SSN,5)

FROM Student;

List(Characters):

SUBSTR (SSN,5)

FROM Student;

• SELECT Name

FROM Student

WHERE SUBSTR (SSN,5,2)=’80’;

• SELECT Name, SUBSTR (SSN,-4)

FROM Student;

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

Door Replacement Handle

List(Characters):
• SELECT Name, INSTR (Name,’r’)

FROM Student;

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

MARY 3

JOHN 0

ROBIN 1

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

FROM Student;

FROM Student;

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(Characters):

Character Manipulations

• REPLACE(string, searchSt [,replace])

• TRANSLATE (string, fromSt, toSt)

TRANSLATE(‘12345678’, ‘123’, ‘999’)

• ASCII(string)

ASCII(‘A’)

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)
• SELECT ROUND(Salary,1)

FROM Faculty;

• SELECT TRUNC(234.111,2),

FROM DUAL;

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

• SELECT name, SYSDATE - B_Date

FROM student;

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

FROM Student;

DATE:

FROM Student;

DATE:
• ROUND(date [,fmt])
• SELECT name, ROUND (B_Date,’MONTH’)

FROM Student;

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

FROM Student;

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:
• SELECT SUBSTR(TO_CHAR(111223333),1,3) ||‘-’ ||

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

SUBSTR(TO_CHAR(111223333),6)

FROM Student;

Conversion Function:
• SELECT SUBSTR(ssn,1,3)

|| ‘-’ ||

SUBSTR(ssn,4,2)

|| ‘-’ ||

SUBSTR(ssn,6)

FROM Student;

Use of DECODE:
• DECODE:

DECODE (col/exp, compare1, result1

[,compare2, result2, …]

[,default] )

SELECT name, salary,

DECODE (Dept, ‘COSC’, salary*2.2,

‘MATH’, salary*1.2,

‘ART’, salary*0.2,

‘salary)

FROM Faculty;