Chapter six data manipulation language dml functions
Download
1 / 32

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


  • 166 Views
  • Uploaded on

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

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

PowerPoint Slideshow about ' Chapter Six Data Manipulation Language (DML) Functions' - latoya


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:

    SMITH Lori


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:

  • SELECT LOWER(name),

    UPPER(major), LENGTH(name)

    FROM student;

  • SELECT CONCAT(Name , Address) , GPA

    FROM Student;

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

    FROM Student;


List characters1
List(Characters):

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

    FROM Student;

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

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

  • SELECTRPAD (Name, 20), GPA

    FROM Student;

    MARY 3.2

    MOHAMMAD 3.3

  • SELECTLPAD (Name, 20), GPA

    FROM Student;

    MARY 3.2

    MOHAMMAD 3.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):

  • SELECT Name

    FROM Student

    WHERE LENGTH(Address)<20;

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

    FROM Student;

  • SELECT Name, SUBSTR (SSN,5)

    FROM Student;


List characters4
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;


Practice3
Practice:

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

  • Example:

    Door Replacement Handle


List characters5
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;


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)

  • 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;


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

  • ADD_MONTHS (date,n)

    SELECT name, ADD_MONTHS(B_Date,5) age

    FROM Student;


DATE:

  • ROUND(date [,fmt])

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

    FROM Student;

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

    FROM Student;


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:

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

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

    SUBSTR(TO_CHAR(111223333),6)

    FROM Student;


Conversion function4
Conversion Function:

  • SELECT SUBSTR(ssn,1,3)

    || ‘-’ ||

    SUBSTR(ssn,4,2)

    || ‘-’ ||

    SUBSTR(ssn,6)

    FROM Student;


ad