Chapter SixData 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 Return one result per row May modify the data type returned type Can be nested
Single Row Functions • Character • Number • Date • Conversion • General
Character Manipulation: • LOWER(Col | Exp) LOWER(‘Database course’) • UPPER (Col | Exp) UPPER (‘Database course’) • INITCAP (Col | Exp) INITCAP (‘Database course’)
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 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 Manipulation • LENGTH(Col | Exp) LENGTH(‘this is it’) • CHR(integer) CHR(97) • INSTR(‘Computer’,’m’)
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(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
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(Characters): • RTRIM(Col) RTRIM (Name) RTRIM (Name, ‘.’) LTRIM (Name, ‘ABC’) LTRIM ( RTRIM( Name, ‘.’ ), ‘a’) From Student;
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(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(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;
Practice: • Display the 10 characters from OrderPart description starting at location 6. • Example: Door Replacement Handle
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;
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) • 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(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;
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: Implicit conversion (Automatic): • CHAR or VARCHAR2 to NUMBER • CHAR or VARCHAR2 to DATE • NUMBER to VARCHAR2 • DATE to VARCHAR2
Conversion Function: Explicit datatype conversion: • TO_CHAR (NUMBER [,‘fmt’] ) • TO_CHAR (DATE [,‘fmt’] ) • TO_DATE (CHAR [,‘fmt’] ) • TO_NUMBER (CHAR [,‘fmt’] )
Conversion Function: SELECT TO_CHAR(b_date,’MM/YY’) FROM student; Format: • YYYY • YEAR • MM • MONTH • DY • DAY
Conversion Function: • SELECT SUBSTR(TO_CHAR(111223333),1,3) ||‘-’ || SUBSTR (TO_CHAR(111223333),4,2) || ‘-’ || SUBSTR(TO_CHAR(111223333),6) FROM Student;
Conversion Function: • SELECT SUBSTR(ssn,1,3) || ‘-’ || SUBSTR(ssn,4,2) || ‘-’ || SUBSTR(ssn,6) FROM Student;
