chapter six data manipulation language dml functions
Download
Skip this Video
Download Presentation
Chapter Six Data Manipulation Language (DML) Functions

Loading in 2 Seconds...

play fullscreen
1 / 32

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


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

slide21
DATE:
  • Date is stored in an internal numeric format: century, year, month, day, hours, minutes, second
  • Default date is DD-MON-YY
  • SYSDATE
slide22
DATE:
  • Example:

List the ages of students

  • SELECT name, SYSDATE - B_Date

FROM student;

practice5
Practice:
  • Display today’s date.
slide24
Date
  • Date + number
  • Date – number
  • Date – date
  • Date + number/24
slide25
DATE:
  • MONTHS_BETWEEN(day1,day2)
  • SELECT name, MONTHS_BETWEEN(SYSDATE , B_Date) age_in_month

FROM Student;

slide26
DATE:
  • ADD_MONTHS (date,n)

SELECT name, ADD_MONTHS(B_Date,5) age

FROM Student;

slide27
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