Chapter Seven (part one) Data Manipulation Language (DML) Functions

1. Chapter Seven (part one)Data 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 returned 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. 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

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

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

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

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

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

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

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

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

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

16. Practice: • Display the 10 characters from OrderPart description starting at location 6. • Example: Door Replacement Handle

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

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

19. List(Characters): Character Manipulations • REPLACE(string, searchSt [,replace]) REPLACE(address,’21532’, ‘21211’) • TRANSLATE (string, fromSt, toSt) TRANSLATE(‘12345678’, ‘123’, ‘999’) • ASCII(string) ASCII(‘A’)

20. 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)

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

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

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

24. Practice: • Display today’s date.

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

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

27. DATE: • ADD_MONTHS (date,n) SELECT name, ADD_MONTHS(B_Date,5) age FROM Student;

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

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

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

31. Conversion Function: SELECT TO_CHAR(b_date,’MM/YY’) FROM student; Format: • YYYY • YEAR • MM • MONTH • DY • DAY

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

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

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