Les02-Single functions-20223 (1)

After completing this lesson, you should be able to do the following: - Describe various types of functions that are available in SQL - Use 1 character, 2 number, and 3 date functions in SELECT Statements - Describe the use of conversion functions Objectives Functions à make the basic query block more powerful, and à they are used to manipulate data values. This is the first of two lessons that explore functions. Focus is on Single-row character, number, and date functions Functions that convert data from one type to another -- For example, conversion from character data to numeric data 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 3 of 53

3-5 Single-Row functions These functions manipulate data items. Expect one or more arguments and return a single value for each row that is retrieved by the query. An argument can be one of the following: User supplied constant Variable value Column name Expression The actions of single row functions include: Acts on each row that is returned by the query Returns one result per row May possibly return a different data type than the one that is referenced The function expects one or more arguments Can be used in THESelect Where Order by - can also be nested 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 6 of 53

Function accepts character data à à returns character and numeric data 2 groups à Case Manipulation à Character Manipulation LOWER ( Column or Expression ) UPPER INITCAP – changes string to Initial letter in each word is capitalized SUBSTR – needs string or column and starting position and length (2 arguments) CONCAT – like || -- needs 2 arguments LENGTH – returns number of characters in the expression (1 argument) SELECT LENGTH ( CONCAT (first_name, last_name)) from employees INSTR – returns the numeric position of a named string -- you can give it a starting position before counting LPAD – pads the character value right justified RPAD – pads the character value shown by the amount not filled by the field select RPAD ( first_name, 9 , '*' ) from employees TRIM REPLACE Examples on next set of slides Ellen**** Curtis*** 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 9 of 53 EXAMPLES on next slides

Let us improve it to get a result SELECT employee_id, first_name, last_name, job_id FROM employees where lower(last_name) = 'higgins' This assumes the user will enter the data as lower case lower case Make it user friendly to enter the data SELECT employee_id, last_name, department_id FROM employees WHERE lower (last_name) = lower (‘&Last_Name’) 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 12 of 53 Convert the data stored in the database to LOWER case and match it to the input Case statement on BOTH sides covers all possibilities . Make it simpler for the user. Although you are hard coding for now, it is on a form and the user enters the name. It is unknown how they will enter it

ANSWER SELECT employee_id, CONCAT (first_name, last_name) NAME, job_id, LENGTH (last_name), INSTR (last_name, 'a') "contains an 'a'" FROM employees WHERE SUBSTR (job_id, 4,1) = 'M'; OUTPUT EMPLOYEE_ID NAME JOB_ID LENGTH(LAST_NAME) contains an 'a' ----------- --------------------------------------------- ---------- ----------------- --------------- 124 KevinMourgos ST_MAN 7 0 149 EleniZlotkey SA_MAN 7 0 201 MichaelHartstein MK_MAN 9 2 205 ShelleyHiggins AC_MGR 7 0 The above example displays employee 1 first names and last names joined. 2 the job title 3 the length of the employee last name, and 4 the numeric position of the letter a in the string, employee last name And for all employees  That have the string “M” contained in the job title starting at position 4 and going 1 character Change SQL to looking for those with RE in the 4 th position SELECT employee_id, CONCAT (first_name, last_name) NAME, job_id, LENGTH (last_name), INSTR (last_name, 'a') "contains an 'a'" FROM employees WHERE SUBSTR (job_id, 4,2) = 'RE' 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 15 of 53

3-14 Again, this is simple functions If use 0 or no value it is rounded to zero decimal places SELECT salary * 1.3 +23.456, round (salary *1.3+23.456) -- rounding to whole dollars FROM employees 13673.456 13673 14323.456 14323 11203.456 11203 9123.456 9123 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 18 of 53 NOTE: DUAL used because SELECT and FROM are mandatory … but the data does not come from any columns or tables

3-17 DATES RR – goes back to pre-2000 times to avoid a problem The Oracle database stores dates in an internal numeric format, representing the century, year, month, day, hours, minutes, and seconds. The default display and input format for any date is DD-MON-RR. Valid Oracle dates are between January 1, 4712 B.C., and December 31, 9999 A.D. In the example in the slide, the HIRE_DATE column output is displayed in the default format DD- MON-RR. However, dates are not stored in the database in this format. All the components of the date and time are stored. So, although a HIRE_DATE such as 17-JUN-87 is displayed as day, month, and year, there is also time and century information associated with the date. The complete data might be June 17, 1987, 5:10:43 p.m. CENTURY YEAR MONTH DAY HOUR MINUTE SECOND 19 87 06 17 17 10 43 Note: century or year stored as 4 digits even if displayed as 2 3-19 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 21 of 53 NOTE: Default date display format. Company may choose different defaults for display. Actual date stored differently. Full date and time June 17, 1987, 5:10:43 p.m

TEST IT --- this will show you the format to use SELECT SYSDATE FROM DUAL Was the format SYSDATE --------- 17-SEP-18 DD-MON-YY Currently SYSDATE -------- 23-01-16 YY-MM-DD Can also use … select current_date from dual; CURRENT_ -------- 23-01-16 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 22 of 53

3-20 Sample finding the date after you added x value of hours SELECT last_name, hire_date, hire_date + 36/24 -- added 36 hours to hire date FROM employees LAST_NAME HIRE_DAT HIRE_DAT --------------- -------- -------- Long 16-09-28 16-09-29 Patterson 16-01-14 16-01-15 Hughes 16-03-08 16-03-09 Flores 16-08-20 16-08-21 Washington 16-10-30 16-10-31 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 23 of 53 MAJOR IMPORTANCE TO BUSINESS BUSINESS RUNS ON DATES AND DOLLARS

When a business sells goods or products to another it usually is not for immediate payment. The seller offers terms. Sample: NET 30 2%NET10 On the invoice is the date the invoice is made or billed, and the date 30 days later for when the payment is due. This would mean output is invoice date (which is probably the current date) plus 30 days. You do not have to calculate the date the payment is due. NOTE: Select statements get all the data and the form and procedures place it in the correct places 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 24 of 53

3-21 Using Arithmetic Operators with Dates PROBLEM: Find how many weeks an employee has worked at the company - and only for department 90 Answer looking for is: LAST_NAME Weeks Employed ------------------------- -------------- King 1526.509089 Kochhar 1408.366232 De Haan 1235.509089 SELECT last_name, (sysdate - hire_date)/7 "Weeks Employed" -- returns days converted to weeks FROM employees Termede 389.094061 Chan 254.522632 Testorok 167.236918 Whiteduck 422.665489 Montoya 170.094061 55 rows selected. …. Just showing the last few This answer is not very good …. Improve it SELECT last_name, trunc((sysdate - hire_date)/7, 2) "Weeks Employed" FROM employees Termede 389.09 Chan 254.52 Testorok 167.23 Whiteduck 422.66 Montoya 170.09 55 rows selected. 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 25 of 53

3-22 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 26 of 53 EX: next page

3-23 EXAMPLE: SELECT NEXT_DAY ('20-05-22','TUESDAY') AS "Next Tuesday" FROM dual; Next Tue -------- 20-05-26 PROBLEM: Try this Display the employee number, hire date, - number of months employed, - six-month from now is the employees review date, it is the date we review the performance of the employee - what is the first Friday after hire date, and - last day of the hire month for all employees who have been employed for fewer than 200 months. ANS: Next page 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 27 of 53 Try SYSDATE

PROBLEM: Try this Display the employee number, hire date, - number of months employed, - six-month from now is the employees review date, - what is the first Friday after hire date, and - last day of the hire month for all employees who have been employed for fewer than 200 months. SELECT employee_id, hire_date, MONTHS_BETWEEN (SYSDATE, hire_date) "Seniority", ADD_MONTHS (hire_date, 6) "Review Date", NEXT_DAY (hire_date, 'Friday'), LAST_DAY (hire_date) FROM employees WHERE MONTHS_BETWEEN (SYSDATE, hire_date) > 70; EMPLOYEE_ID HIRE_DAT Seniority Review D NEXT_DAY LAST_DAY ----------- -------- ---------- -------- -------- -------- 100 87-06-17 418.989109 87-12-17 87-06-19 87-06-30 101 89-09-21 391.860077 90-03-21 89-09-22 89-09-30 102 93-01-13 352.118141 93-07-13 93-01-15 93-01-31 103 90-01-03 388.440722 90-07-03 90-01-05 90-01-31 104 91-05-21 371.860077 91-11-21 91-05-24 91-05-31 107 99-02-07 279.311689 99-08-07 99-02-12 99-02-28 124 99-11-16 270 00-05-16 99-11-19 99-11-30 141 95-10-17 318.989109 96-04-17 95-10-20 95-10-31 142 97-01-29 303.602012 97-07-29 97-01-31 97-01-31 143 98-03-15 290.053625 98-09-15 98-03-20 98-03-31 144 98-07-09 286.247173 99-01-09 98-07-10 98-07-31 149 00-01-29 267.602012 00-07-29 00-02-04 00-01-31 174 96-05-11 312.182657 96-11-11 96-05-17 96-05-31 176 98-03-24 289.763302 98-09-24 98-03-27 98-03-31 178 99-05-24 275.763302 99-11-24 99-05-28 99-05-31 200 87-09-17 415.989109 88-03-17 87-09-18 87-09-30 201 96-02-17 314.989109 96-08-17 96-02-23 96-02-29 202 97-08-17 296.989109 98-02-17 97-08-22 97-08-31 205 94-06-07 335.311689 94-12-07 94-06-10 94-06-30 206 94-06-07 335.311689 94-12-07 94-06-10 94-06-30 28 01-04-28 252.63427 01-10-28 01-05-04 01-04-30 21 rows selected. 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 28 of 53

3-24 In the example shown the dates are DD-MON-YY Using Date Functions Try these to see result based on current sysdate The ROUND and TRUNC functions can be used for number and date values. When used with dates, these functions round or truncate to the specified format model. Therefore, you can round dates to the nearest year or month. ORIGINAL DATE is in SEPTEMBER 2018 ROUND __ MONTH select round(sysdate, 'month') from dual; ROUND(SYSDATE,'MONTH') ---------------------- 01-OCT-18 TRUNC ___ Month TRUNC(SYSDATE,'MONTH') ---------------------- 01-SEP-18 select round(sysdate, 'year') from dual; ROUND(SYSDATE,'YEAR') --------------------- 01-JAN-19 TRUNC(SYSDATE,'YEAR') --------------------- 01-JAN-18 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 29 of 53

PROBLEM: Compare the hire dates for all employees who started in 1997. Display the employee number, hire date, and start month using the ROUND and TRUNC functions. SELECT employee_id, hire_date, ROUND(hire_date, 'MONTH') as Started_Month_Rounded, TRUNC(hire_date, 'MONTH') as Truncated FROM employees WHERE hire_date LIKE '%97'; OracleExpress in Jan 2015 has a different default date style, but result is the same data EMPLOYEE_ID HIRE_DATE STARTED_MONTH_ROUNDED TRUNCATED 142 01/29/1997 02/01/1997 01/01/1997 202 08/17/1997 09/01/1997 08/01/1997 USING EMPLOYEES in 2021-1 data SELECT employee_id, hire_date, ROUND(hire_date, 'MONTH') as Started_Month_Rounded, TRUNC(hire_date, 'MONTH') as Truncated FROM employees WHERE hire_date LIKE '16%';  depends on how dates are stored. Could be ‘%16’ EMPLOYEE_ID HIRE_DAT STARTED_ TRUNCATE ----------- -------- -------- -------- 7 16-07-27 16-08-01 16-07-01 8 16-08-08 16-08-01 16-08-01 30 16-06-30 16-07-01 16-06-01 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 30 of 53

EXERCISE for you to do at back of Oracle book chapter 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 31 of 53

3-26 Conversion Functions 2 Types - Implicit - Explicit 3-27 IMPLICIT - what the Oracle software does itself. EXPLICIT - what a specific conversion function does See notes for IMPLICIT and EXPLICIT explanations 3-28 3-29 3-30 3-31 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 32 of 53

3-32 PURPOSE: To change the look of the date to meet requirements select last_name, salary, hire_date, TO_CHAR (hire_date, 'YYYY-Month-DD') from employees where salary = 11000 LAST_NAME SALARY HIRE_DAT TO_CHAR(HIRE_DATE ------------------------- ---------- -------- ----------------- Abel 11000 96-05-11 1996-May -11 Rodriguez 11000 15-04-27 2015-April -27 Termede 11000 14-12-01 2014-December -01 Testorok 11000 13-03-03 2013-March -03 SHOW FM TO_CHAR (hire_date, ' fm YYYY-Month-DD') LAST_NAME SALARY HIRE_DAT TO_CHAR(HIRE_DATE ------------------------- ---------- -------- ----------------- Abel 11000 96-05-11 1996-May-11 Rodriguez 11000 15-04-27 2015-April-27 Termede 11000 14-12-01 2014- December-1… note it is 1 and not 01 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 33 of 53 NOT FM With FM FM goes inside quotes

Testorok 11000 13-03-03 2013-March-3 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 34 of 53

CHANGE REQUIREMENT This example is changing it to MM/YY SELECT employee_id, last_name, to_char (hire_date, 'mm/yy') Month_Hired FROM employees WHERE last_name like 'H%' 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 35 of 53 NOTE: you control the output format Test with fm also

3-33 --4-12 Do This SELECT EMPLOYEE_ID, TO_CHAR (HIRE_DATE, 'MM/DD/YY') FROM EMPLOYEES WHERE LAST_NAME like 'H%' Then this SELECT EMPLOYEE_ID, TO_CHAR (HIRE_DATE , 'fmMM/DD/YY')HireDate FROM EMPLOYEES WHERE LAST_NAME like 'H%' Try fm with a lot of spaces CAN YOU SEE THE DIFFERENCE 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 36 of 53

3-34 – 4-13 MANY OTHERS Try out some of them to see what they do 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 37 of 53

3-35-4-14 Again, another set of formats REMEMBER: Business uses dates 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 38 of 53

3-37 Fm will get rid of leading zeros – see Lorentz Using the TO_CHAR function to add more control SELECT last_name, hire_date, TO_CHAR(hire_date, 'fmDdspth "of" Month YYYY fmHH:MI') FROM employees ==> Try it with 24 hour format and see results LAST_NAME TO_CHAR(HIRE_DATE,'FMDDSPTH"OF"MONTHYYYYFMHH:MI') King Seventeenth of June 1987 12:00 Kochhar Twenty-First of September 1989 12:00 De Haan Thirteenth of January 1993 12:00 Hunold Third of January 1990 12:00 Ernst Twenty-First of May 1991 12:00 Lorentz Seventh of February 1999 12:00 Mourgos Sixteenth of November 1999 12:00 Rajs Seventeenth of October 1995 12:00 Davies Twenty-Ninth of January 1997 12:00 Matos Fifteenth of March 1998 12:00 Plus more rows 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 39 of 53

3-38 Using TO_CHAR with number SELECT last_name, TO_CHAR(salary, '$99,999.00') as SALARY FROM employees; LAST_NAME SALARY King $24,000.00 Kochhar $17,000.00 De Haan $17,000.00 Hunold $9,000.00 Ernst $6,000.00 Lorentz $4,200.00 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 40 of 53 Problems of a floating dollar sign is that the field is left justified as a character field and numbers do not align well. AGAIN, SQL was not meant to be fancy. BUT right justifies on other software

Convert character string to NUMBER or DATE 03-39--4-20 General format of conver to a number SELECT to_number('1234') - 2 -- convert STRING of characters to a number less 2 from dual; RESULT: TO_NUMBER('1234')-2 ------------------- 1232 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 41 of 53

Convert a character to a date 4-20 Find those hired before 1990 Try this: Convert a date format to a character string format SELECT last_name, to_char (hire_date, 'DD-Mon-YYYY') from employees where hire_date < to_date ('01-Jan-90', 'DD-Mon-YY'); LAST_NAME TO_CHAR(HIRE_DATE,'DD-MON-YYYY') ------------------------- -------------------------------- King 17-Jun-1987 Kochhar 21-Sep-1989 De Haan 13-Jan-1993 Hunold 03-Jan-1990 Ernst 21-May-1991 Lorentz 07-Feb-1999 Mourgos 16-Nov-1999 Rajs 17-Oct-1995 NOTE Look at the results. Is it correct? Try running it with current data. Wrong results because it assumed with YY that it was 2090 Change it to RR 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 42 of 53

TRY THIS (fx means eXact formatting) Find employees hired on May 24, 1999 SELECT last_name, hire_date from employees where hire_date = to_date('May 24, 1999', 'fxMonth DD, YYYY'); It is selecting an employee with a specific hire date. The test for equal would not work unless the formats matched. Notice there are spaces between May and 24. NOTE: 1 Repeat the code above, add some extra spaces in the date 2 Add some spaces in the format and rerun Adding equal number of spaces in both SELECT last_name, hire_date from employees where hire_date = to_date('May 24, 1999', 'fxMonth DD, YYYY'); RESULT: where are spaces. Using spaces to ensure matching types, but output said just to show name and hire date. What you see is hire date. LAST_NAME HIRE_DATE ------------------------- --------- Grant 24-MAY-99 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 43 of 53

Nesting Functions 4-24 - Single row functions can be nested to any level - Nested functions evaluate from the innermost or deepest level 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 44 of 53

Examples of Nesting Functions 4-25 TRY THIS: Display the - Last name of the employees in department 60 - And their new email name -- made up of first 4 characters of last name with _CA added - all to appear in uppercase - make the title of column 2 much nicer looking Example Higgins becomes HIGG_CA SELECT last_name, UPPER (CONCAT(SUBSTR(LAST_NAME, 1, 4) , '_CA')) as "Email" FROM employees WHERE department_id = 60; 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 45 of 53

General Functions 04-27 Handling NULLS General Format The most used is NVL 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 46 of 53

NULL Examples PROBLEM 1: List last name Salary And the result of multiplying salary times commission percent SELECT last_name, salary, salary*commission_pct FROM employees; The effect of a NULL value in a calculation is to give a NULL result in display Some of the output Rajs 3500 - Davies 3100 - Matos 2600 - Vargas 2500 - Zlotkey 10500 2100 Abel 11000 3300 Taylor 8600 1720 Grant 7000 1050 Whalen 4400 - Hartstein 13000 - Correction: (might be) SELECT last_name, salary, salary* nvl(commission_pct,0) FROM employees; Rajs 3500 0 Davies 3100 0 Matos 2600 0 Vargas 2500 0 Zlotkey 10500 2100 Abel 11000 3300 Taylor 8600 1720 Grant 7000 1050 Whalen 4400 0 Hartstein 13000 0 PROBLEM 2: Add up the totals – next chapter 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 47 of 53 NVL does not need to be 0

NULL with date 4-28 NVL (hire_date, '01-JAN-2015') if NULL then make it ... NULL with character Suppose you are missing any value in a character field and you wanted to not leave it as NULL, but wanted it to appear as Unavailable. NVL (city, 'Unavailable' ) BAD EXAMPLE … but SELECT last_name, NVL(to_char(commission_pct), to_char('???')) FROM employees; Davies ??? Matos ??? Vargas ??? Zlotkey .2 Abel .3 Taylor .2 Grant .15 Whalen ??? Hartstein ??? Better SELECT last_name, NVL(to_char(commission_pct), to_char('not commission')) FROM employees 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 48 of 53 First needed to convert numeric field to a charater because want to diplay characters (the question mark)

READ the book for the other NULLs 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 49 of 53

COALESCE 4-32 and 4-33 Evaluates multiple expressions --- read the book SELECT last_name, salary, commission_pct, coalesce( (salary +(commission_pct*salary)), salary + 2000, salary) as "New Salary" FROM employees; Davies 3100 - 5100 Matos 2600 - 4600 Vargas 2500 - 4500 Zlotkey 10500 .2 12600 Abel 11000 .3 14300 Taylor 8600 .2 10320 Grant 7000 .15 8050 Whalen 4400 - 6400 Hartstein 13000 - 15000 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 50 of 53 1 st value evaluates as a NULL so filled with salary + 2000 1 st value wasn't a null so the calculated expression appears of salary plus salary times commission

CONDITIONAL EXPRESSIONS 4-35 CASE applies to ANSI standard DECODE is Oracle syntax (from an earlier period) 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 51 of 53

CASE 4-38 NOTE: -- ST_MAN as a job_id did not fit any of the cases so the ELSE took effect and the new salary was just the same as the salary SELECT last_name, job_id, salary, CASE job_id WHEN 'IT_PROG' THEN 1.10 * salary WHEN 'ST_CLERK' THEN 1.15 * salary WHEN 'SA_REP' THEN 1.20 * salary ELSE salary END as "Revised Salary" FROM employees; 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 52 of 53

DECODE -- not using as I prefer case, but you could read it 4-39 PLEASE READ 3dcc1d71318c08fd2265a0eda5fb2cb5634406a3.docx --- 12 December 2023 53 of 53