1 / 32

Chapter Nine Data Manipulation Language (DML) Functions

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

sef
Download Presentation

Chapter Nine 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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter NineData Manipulation Language (DML)Functions Objectives Single Row functions Character functions Number functions Date functions

  2. Functions • Introduction • Types of functions • Single row • Multiple rows

  3. Single Row Functions: FACTS: Act on each row Return one result per row May modify the data type Can be nested

  4. Single Row Functions • Character • Number • Date • Conversion • General

  5. Character Manipulation: • LOWER(Col | Exp) LOWER(‘Database course’) • UPPER (Col | Exp) UPPER (‘Database course’) • INITCAP (Col | Exp) INITCAP (‘Database course’)

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

  7. Character Manipulation • LENGTH(Col | Exp) LENGTH(‘this is it’) • CHR(integer) CHR(97) • INSTR(‘Computer’,’m’)

  8. 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;

  9. 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

  10. List(Characters): • RTRIM(Col) RTRIM (Name) RTRIM (Name, ‘.’) LTRIM (Name, ‘ABC’) LTRIM ( RTRIM( Name, ‘.’ ), ‘A’) From Student;

  11. 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;

  12. 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;

  13. 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;

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

  15. List(Characters): Character Manipulations • SOUNDEX SELECT name, GPA FROM student WHERE SOUNDEX (name)=SOUNDEX(‘LAVALE’);

  16. Character Manipulation New Functions: REGEXP_SUBSTR REGEXP_INSTR REGEXP_LIKE REGEXP_REPLACE

  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)

  18. LIST(Numbers) • SELECT ROUND(Salary,1) FROM Faculty; • SELECT TRUNC(234.111,2), FROM DUAL; TRUNC(234.567); TRUNC(234.5678,-2);

  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

  20. DATE: • Example: List the ages of students • SELECT name, SYSDATE - B_Date FROM student;

  21. Date • Date + number • Date – number • Date – date • Date + number/24

  22. DATE: • MONTHS_BETWEEN(day1,day2) • SELECT name, MONTHS_BETWEEN(SYSDATE , B_Date) age_in_month FROM Student;

  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;

  24. DATE: • ROUND(date [,fmt]) • SELECT name, ROUND (B_Date,’MONTH’) FROM Student; • SELECT name, ROUND(B_Date,’YEAR’) FROM Student;

  25. DATE: • ROUND(date [,fmt]) • SELECT ’25-OCT-04’ - ROUND (SYSDATE) FROM DOAL; • SELECT name, ROUND(B_Date,’YEAR’) FROM Student;

  26. DATE: • NEXT_DAY • SELECT cycledate FROM paydate; • SELECT NEXT_DAY(cycledate, ‘FRIDAY’) FROM paydate;

  27. Conversion Function: Implicit conversion (Automatic): • CHAR or VARCHAR2 to NUMBER • CHAR or VARCHAR2 to DATE • NUMBER to VARCHAR2 • DATE to VARCHAR2

  28. Conversion Function: Explicit datatype conversion: • TO_CHAR (NUMBER [,‘fmt’] ) • TO_CHAR (DATE [,‘fmt’] ) • TO_DATE (CHAR [,‘fmt’] ) • TO_NUMBER (CHAR [,‘fmt’] )

  29. Conversion Function: SELECT TO_CHAR(b_date,’MM/YY’) FROM student; Format: • YYYY • YYY • YY • YEAR • MM • MONTH • DD • DY • DAY • HH HH12 HH24 • MI • SS

  30. Conversion Function: • SELECT SUBSTR(TO_CHAR(111223333),1,3) ||‘-’ || SUBSTR (TO_CHAR(111223333),4,2) || ‘-’ || SUBSTR(TO_CHAR(111223333),6) FROM Student;

  31. Conversion Function: • SELECT SUBSTR(ssn,1,3) || ‘-’ || SUBSTR(ssn,4,2) || ‘-’ || SUBSTR(ssn,6) FROM Student;

  32. 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;

More Related