Chapter six data manipulation language dml functions
This presentation is the property of its rightful owner.
Sponsored Links
1 / 32

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


  • 109 Views
  • Uploaded on
  • Presentation posted in: General

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

Download Presentation

Chapter Six Data Manipulation Language (DML) Functions

An Image/Link below is provided (as is) to download presentation

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 six data manipulation language dml functions

Chapter SixData Manipulation Language (DML)Functions

Objectives

Single Row functions

Character functions

Number functions

Date functions


Functions

Functions

  • Introduction

  • Types of functions

    • Single row

    • Multiple rows


Single row functions

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 functions1

Single Row Functions

  • Character

  • Number

  • Date

  • Conversion

  • General


Character manipulation

Character Manipulation:

  • LOWER(Col | Exp)

    LOWER(‘Database course’)

  • UPPER (Col | Exp)

    UPPER (‘Database course’)

  • INITCAP (Col | Exp)

    INITCAP (‘Database course’)


Practice

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 manipulation1

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


Character manipulation2

Character Manipulation

  • LENGTH(Col | Exp)

    LENGTH(‘this is it’)

  • CHR(integer)

    CHR(97)

  • INSTR(‘Computer’,’m’)


List characters

List(Characters):

  • Example:

  • SELECTLOWER(name),

    UPPER(major), LENGTH(name)

    FROMstudent;

  • SELECT CONCAT(Name , Address) , GPA

    FROMStudent;

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

    FROMStudent;


List characters1

List(Characters):

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

    FROMStudent;

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

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

  • SELECTRPAD(Name, 20), GPA

    FROMStudent;

    MARY 3.2

    MOHAMMAD 3.3

  • SELECTLPAD(Name, 20), GPA

    FROMStudent;

    MARY3.2

    MOHAMMAD3.3


Practice1

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 characters2

List(Characters):

  • RTRIM(Col)

    RTRIM (Name)

    RTRIM (Name, ‘.’)

    LTRIM (Name, ‘ABC’)

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

    From Student;


Practice2

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 characters3

List(Characters):

  • SELECTName

    FROMStudent

    WHERELENGTH(Address)<20;

  • SELECTName, SUBSTR (SSN, 5 ,2)

    FROMStudent;

  • SELECTName, SUBSTR (SSN,5)

    FROMStudent;


List characters4

List(Characters):

  • SELECTRPAD (INITCAP(LOWER(Name)),70,’.’),

    SUBSTR (SSN,5)

    FROM Student;

  • SELECTName

    FROMStudent

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

  • SELECTName, SUBSTR (SSN,-4)

    FROMStudent;


Practice3

Practice:

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

  • Example:

    Door Replacement Handle


List characters5

List(Characters):

  • SELECTName, INSTR (Name,’r’)

    FROMStudent;

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

    MARY3

    JOHN0

    ROBIN1

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

    FROMStudent;

  • SELECTName, INSTR(Address,’Frostburg’)

    FROMStudent;


Practice4

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

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 numbers1

LIST(Numbers)

  • SELECTROUND(Salary,1)

    FROMFaculty;

  • SELECTTRUNC(234.111,2),

    FROMDUAL;

    TRUNC(234.567);

    TRUNC(234.5678,-2);


Chapter six data manipulation language dml functions

DATE:

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

  • Default date is DD-MON-YY

  • SYSDATE


Chapter six data manipulation language dml functions

DATE:

  • Example:

    List the ages of students

  • SELECTname, SYSDATE - B_Date

    FROMstudent;


Practice5

Practice:

  • Display today’s date.


Chapter six data manipulation language dml functions

Date

  • Date + number

  • Date – number

  • Date – date

  • Date + number/24


Chapter six data manipulation language dml functions

DATE:

  • MONTHS_BETWEEN(day1,day2)

  • SELECT name, MONTHS_BETWEEN(SYSDATE , B_Date) age_in_month

    FROMStudent;


Chapter six data manipulation language dml functions

DATE:

  • ADD_MONTHS (date,n)

    SELECT name, ADD_MONTHS(B_Date,5) age

    FROMStudent;


Chapter six data manipulation language dml functions

DATE:

  • ROUND(date [,fmt])

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

    FROMStudent;

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

    FROMStudent;


Conversion function

Conversion Function:

Implicit conversion (Automatic):

  • CHAR or VARCHAR2 to NUMBER

  • CHAR or VARCHAR2 to DATE

  • NUMBER to VARCHAR2

  • DATE to VARCHAR2


Conversion function1

Conversion Function:

Explicit datatype conversion:

  • TO_CHAR (NUMBER [,‘fmt’] )

  • TO_CHAR (DATE [,‘fmt’] )

  • TO_DATE (CHAR [,‘fmt’] )

  • TO_NUMBER (CHAR [,‘fmt’] )


Conversion function2

Conversion Function:

SELECT TO_CHAR(b_date,’MM/YY’)

FROM student;

Format:

  • YYYY

  • YEAR

  • MM

  • MONTH

  • DY

  • DAY


Conversion function3

Conversion Function:

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

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

    SUBSTR(TO_CHAR(111223333),6)

    FROMStudent;


Conversion function4

Conversion Function:

  • SELECTSUBSTR(ssn,1,3)

    || ‘-’ ||

    SUBSTR(ssn,4,2)

    || ‘-’ ||

    SUBSTR(ssn,6)

    FROMStudent;


  • Login