chapter nine data manipulation language (dml) functions
DESCRIPTION
Chapter Nine 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 - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/1.jpg)
Chapter NineData Manipulation Language (DML)
Functions
Objectives• Single Row functions
• Character functions
• Number functions
• Date functions
![Page 2: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/2.jpg)
2
Functions
• Introduction
• Types of functions– Single row– Multiple rows
![Page 3: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/3.jpg)
Single Row Functions:
•FACTS:– Act on each row
– Return one result per row
– May modify the data type
– Can be nested
![Page 4: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/4.jpg)
4
Single Row Functions
• Character
• Number
• Date
• Conversion
• General
![Page 5: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/5.jpg)
5
Character Manipulation:
• LOWER(Col | Exp)LOWER(‘Database course’)
• UPPER (Col | Exp)UPPER (‘Database course’)
• INITCAP (Col | Exp)
INITCAP (‘Database course’)
![Page 6: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/6.jpg)
6
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,’.’)
![Page 7: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/7.jpg)
7
Character Manipulation
• LENGTH(Col | Exp)LENGTH(‘this is it’)
• CHR(integer)CHR(97)
• INSTR(‘Computer’,’m’)
![Page 8: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/8.jpg)
8
List(Characters):
• Example:
• SELECT LOWER(name), UPPER(major), LENGTH(name)
FROM student;
• SELECT CONCAT(Name , Address) , GPAFROM Student;
• SELECT Name || ‘--->‘ || Address , GPA
FROM Student;
![Page 9: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/9.jpg)
9
List(Characters):
• SELECT RPAD (Name, 40, ‘.’), GPA
FROM Student;
JOHN............... 3.1
MARY ............. 3.2
• SELECT RPAD (Name, 20), GPA
FROM Student;
MARY 3.2
MOHAMMAD 3.3
• SELECT LPAD (Name, 20), GPA
FROM Student;
MARY 3.2
MOHAMMAD 3.3
![Page 10: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/10.jpg)
10
List(Characters):
• RTRIM(Col)
RTRIM (Name)
RTRIM (Name, ‘.’)
LTRIM (Name, ‘ABC’)
LTRIM ( RTRIM( Name, ‘.’ ), ‘A’)
From Student;
![Page 11: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/11.jpg)
11
List(Characters):
• SELECT NameFROM StudentWHERE LENGTH(Address)<20;
• SELECT Name, SUBSTR (SSN, 5 ,2)
FROM Student;
• SELECT Name, SUBSTR (SSN,5)FROM Student;
![Page 12: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/12.jpg)
12
List(Characters):
• SELECT RPAD (INITCAP(LOWER(Name)),70,’.’),
SUBSTR (SSN,5)
FROM Student;
• SELECT NameFROM StudentWHERE SUBSTR (SSN,5,2)=’80’;
• SELECT Name, SUBSTR (SSN,-4)FROM Student;
![Page 13: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/13.jpg)
13
List(Characters):
• SELECT Name, INSTR (Name,’R’)FROM Student;
------------------------------------------------
MARY 3JOHN 0ROBIN 1
• SELECT Name, INSTR (Name,’R’,1,2)FROM Student;
• SELECT Name, INSTR(Address,’FROSTBURG’)FROM Student;
![Page 14: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/14.jpg)
14
List(Characters):
Character Manipulations
• REPLACE(string, searchSt [,replace])REPLACE(address,’21532’, ‘21211’)
• TRANSLATE (string, fromSt, toSt)TRANSLATE(‘12345678’, ‘123’, ‘999’)
• ASCII(string)ASCII(‘A’)
![Page 15: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/15.jpg)
15
List(Characters):
Character Manipulations
• SOUNDEX
SELECT name, GPA
FROM student
WHERE
SOUNDEX (name)=SOUNDEX(‘LAVALE’);
![Page 16: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/16.jpg)
16
Character Manipulation
New Functions:
REGEXP_SUBSTR
REGEXP_INSTR
REGEXP_LIKE
REGEXP_REPLACE
![Page 17: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/17.jpg)
17
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)
![Page 18: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/18.jpg)
18
LIST(Numbers)
• SELECT ROUND(Salary,1)
FROM Faculty;
• SELECT TRUNC(234.111,2),
FROM DUAL;
TRUNC(234.567);
TRUNC(234.5678,-2);
![Page 19: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/19.jpg)
19
DATE:
• Date is stored in an internal numeric format: century, year, month, day, hours, minutes, second
• Default date is ‘DD-MON-YY’
• SYSDATE• CURRENT_DATE• STSTIMESTAMP
25-OCT-04 04.15.31.652000 PM
![Page 20: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/20.jpg)
20
DATE:
• Example:
List the ages of students
• SELECT name, SYSDATE - B_Date
FROM student;
![Page 21: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/21.jpg)
21
Date
• Date + number
• Date – number
• Date – date
• Date + number/24
![Page 22: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/22.jpg)
22
DATE:
• MONTHS_BETWEEN(day1,day2)
• SELECT name, MONTHS_BETWEEN(SYSDATE , B_Date)
age_in_month
FROM Student;
![Page 23: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/23.jpg)
23
DATE:
• ADD_MONTHS (date,n)SELECT name, ADD_MONTHS(B_Date,5) age
FROM Student;
SELECT name, ADD_MONTHS(B_Date,-15) age
FROM Student;
![Page 24: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/24.jpg)
24
DATE:
• ROUND(date [,fmt])
• SELECT name,
ROUND (B_Date,’MONTH’)
FROM Student;
• SELECT name, ROUND(B_Date,’YEAR’)
FROM Student;
![Page 25: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/25.jpg)
25
DATE:
• ROUND(date [,fmt])
• SELECT ’25-OCT-04’ - ROUND
(SYSDATE)
FROM DOAL;
• SELECT name, ROUND(B_Date,’YEAR’)
FROM Student;
![Page 26: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/26.jpg)
26
DATE:
• NEXT_DAY
• SELECT cycledate FROM paydate;
• SELECT NEXT_DAY(cycledate, ‘FRIDAY’)
FROM paydate;
![Page 27: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/27.jpg)
27
DATE:
LAST_DAY (date)
SELECT LAST_DAY (SYSDATE) “Last”,
SYSDATE, LAST_DAY (SYSDATE) – SYSDATE “Left”
FROM DUAL;
Last SYSDATE Left
--------------------------------------------------------
31-MAY-05 28-MAY-05 4
![Page 28: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/28.jpg)
28
Conversion Function:
Implicit conversion (Automatic):
– CHAR or VARCHAR2 to NUMBER– CHAR or VARCHAR2 to DATE– NUMBER to VARCHAR2– DATE to VARCHAR2
![Page 29: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/29.jpg)
29
Conversion Function:
Explicit datatype conversion:
– TO_CHAR (NUMBER [,‘fmt’] )– TO_CHAR (DATE [,‘fmt’] )– TO_DATE (CHAR [,‘fmt’] )– TO_NUMBER (CHAR [,‘fmt’] )
![Page 30: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/30.jpg)
30
Conversion Function:
SELECT TO_CHAR(b_date,’MM/YY’)FROM student;
Format:• YYYY• YYY• YY• RR• YEAR• MM• MONTH• DD• DY• DAY• HH HH12 HH24• MI• SS
![Page 31: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/31.jpg)
31
Conversion Function:
EXTRACT (YEAR FROM B_Date)YEARMONTHDAYHOURMINUTESECOND
SELECT EXTRACT (YEAR FROM DATE ‘1999-01-08’)FROM DUAL; --1999
SELECT EXTRACT (YEAR FROM TO_DATE (B_Date, ‘DD-
MON-YY’)FROM Student;
![Page 32: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/32.jpg)
32
Conversion Function:
• SELECTSUBSTR(TO_CHAR(111223333),1,3)
||‘-’ ||
SUBSTR (TO_CHAR(111223333),4,2) || ‘-’ ||
SUBSTR(TO_CHAR(111223333),6)
FROM Student;
![Page 33: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/33.jpg)
33
Conversion Function:
• SELECT SUBSTR(ssn,1,3)
|| ‘-’ ||
SUBSTR(ssn,4,2)
|| ‘-’ ||
SUBSTR(ssn,6)
FROM Student;
![Page 34: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/34.jpg)
34
CAST
• CAST (expression) AS (Type);
CHAR NUMBER DATE ROW ROWIDVARCHAR2 TIMESTAMP
1 X X X X X
2 X X
3 X X
4 X X
5 X X
CAST (B_Date AS CHAR);
![Page 35: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/35.jpg)
35
NULLIF
NULLIF (Exp1, Exp2)IF Exp1 = Exp2 THEN RETURN
NULL ELSE RETURN
Exp1SELECT Name, NULLIF (a.major, b.major)
“oldMajor”FROM Student a, StudentHistory bWHERE a.ID = b.ID;
![Page 36: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/36.jpg)
36
NVL
NVL (Exp1,Exp2)
SELECT Name, NVL
(TO_CHAR (Commission_pct, ‘Not Applicable’))
commissionFROM Sales;
![Page 37: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/37.jpg)
37
NVL2
NVL2 (Exp1, Exp2, Exp3)
SELECT Name,
NVL2 (Commission_pct, Salary + Salary * Commission_pct, Salary)
FROM Employee;
![Page 38: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/38.jpg)
38
COALESCE
• COALESCE (exp_List)
Return the first non_null expression in the expression list:
COALESCE (Email, Address, Phone_#, Phone_W)
SELECT Product_ID, Product_Name, List_Price, Min_Price, COALESCE (List_Price, Min_Price, 10) ”Sale Price”
FROM Product;
![Page 39: Chapter Nine Data Manipulation Language (DML) Functions](https://reader035.vdocuments.us/reader035/viewer/2022062800/568140c0550346895dac85a8/html5/thumbnails/39.jpg)
39
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;