Department of Computer and Information Science, School of Science, IUPUI

1. Department of Computer and Information Science,School of Science, IUPUI Queries Dale Roberts, Lecturer Computer Science, IUPUI E-mail: droberts@cs.iupui.edu

2. Getting Text Information and Changing It • There are 2 kinds of functions. Functions either: • return modified the data. • return information about the data. • There are many kinds of functions: • String functions (Chapter 7) • Numeric functions (Chapter 8) • Date functions (Chapter 9) • Conversion functions (Chapter 10) • Group functions (Chapter 11) • DECODE and CASE (Chapter 17) • User Defined (Chapter 29)

3. String Functions • CHAR and VARCHAR2 are “string” data types in Oracle. CHAR is fixed length, VARCHAR2 is variable length. • || is for string concatenation. For example:SELECT CITY||’, ’||STATE||’ ’||ZIP AS ADDRESS FROM CUSTOMER; • ADDRESS • ------------------------------------------ • INDIANAPOLIS, IN 46250 • RED WOOD SHORES, ca 94710 • INITCAP, UPPER, LOWER changes capitalization. For example:SELECT INITCAP(CITY)||’, ’||UPPER(STATE)||’ ’||ZIP AS ADDRESS FROM CUSTOMER;ADDRESS • ------------------------------------------ • Indianapolis, IN 46250 • Red Wood Shores, CA 94710

4. String Functions SUBSTR returns only a part of a string. Example:SELECT SUBSTR(ADDRESS,1,8) FROM CUSTOMER;SUBSTR(A • --------INDIANAPRED WOOD • INSTR returns the relative position of a character within the string. Example:SELECT ADDRESS, INSTR(ADDRESS, ’, ’) FROM CUSTOMER; • ADDRESS INSTR(ADDRESS, ’, ’ ) • ---------------------------- --------------------- • INDIANAPOLIS, IN 46250 13 • RED WOOD SHORES, CA 94710 16

5. String Functions • Example combining INITCAP, SUBSTR and INSTR:SELECT INITCAP(SUBSTR(ADDRESS,1,INSTR(ADDRESS,’, ’)-1)) FROM CUSTOMER;INITCAP(SUBSTR(ADDRESS,1,INSTR(ADDRESS,’, ’)-1)) • ------------------------------------------------ • Indianapolis • Red Wood Shores • LENGTH returns length of string. • LTRIM, RTRIM returns the string without the specified leading (ltrim) or trailing (rtrim) characters. Excellent for cleaning up data fields that contain garbage. • LPAD, RPAD returns the string with extra characters to pad out to the specified length either on the left (lpad) or the right (rpad).

6. Numeric Functions • Single Value Functions:ABS, ACOS, ASIN, ATAN, ATAN2, BITAND, CEIL, COS, COSH, EXP, FLOOR, LN, LOG, MOD, NVL, POWER, ROUND, SIGN, SIN, SINH, SQRT, TAN, TANH, TRUNC, VSIZE Group Functions:AVG, CORR, COUNT, COVAR_POP, COVAR_SAMP, CUME_DIST, DENSE_RANK, FIRST, GROUP_ID, GROUPING, GROUPING_ID, LAST, MAX, MIN, PERCENTILE_COUNT, PERCENTILE_DISC, PERCENT_RANK, RANK, STDDEV, STDDEV_POP, STDDEV_SAMP, SUM, VAR_POP, VAR_SAMP, VARIANCE List Functions:COALESCE, GREATEST, LEAST Ø9i new functions in bold italics

7. Date Functions • Current date functions: SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP, SESSIONTIMEZONE, SYSTIMESTAMPDate functions:ADD_MONTS, GREATEST, LEAST, LAST_DAY, MONTHS_BETWEEN, NEW_TIME, NEXT_DAY, TO_DATE, TO_CHAR,ROUND, TRUNC, DBTIMEZONE, EXTRACT, FROM_TZ,NUMTODSINTERVAL, NUMTOYMINTERVAL,SYS_EXTRACT_UTC, TO_DSINTERVAL, TO_TIMESTAMP, TO_TIMESTAMP_TZ, TO_YMINTERVAL,TZ_OFFSET Ø9i new functions in bold italics

8. Grouping Things Together • ANSI standard SQL Group functions: • AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE • Others: • 8i: GROUPING (used with CUBE and ROLLUP, see Ch.13) • 9i statistical functions: CORR, COVAR_POP, COVAR_SAMP, CUME_DIST, DENSE_RANK, FIRST, FIRST_VALUE, GROUP_ID, GROUPING_ID, LAST, PERCENTILE_CONT, PERCENTILE_DISC, PERCENT_RANK, RANK, REGR, STDDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP • Obsolete? In 9i: GLB, LUB 8

9. Grouping Things Together • A group function returns a single row for an entire group of queried rows. • NULL values are ignored in group functions. • Without a GROUP BY clause the data returned for a group function is one row for the entire table. • With a GROUP BY clause, one row is returned for each group your data is subdivided into. • The WHERE clause is evaluated before data are grouped in GROUP BY clause. • The HAVING clause is similar to the WHERE clause, but it works on entire groups of data. • DISTINCT keyword forces only unique occurrences to be considered in data groups. • When using a group by clause, these are the only selectable items: • constants, • group functions, and • group expressions -- these must match exactly the group by clause 9

10. Grouping Things Together • Order of clause execution within select statements: • If the statement contains a WHERE clause, removes all rows that do not satisfy it. • Group rows together based on the GROUP BY clause. • Calculate the results of the group functions for each group. • Choose and eliminate groups based on the HAVING clause. • Order the results based on the ORDER BY clause. • Specify the GROUP BY and HAVING clauses after the WHERE clause. If both the GROUP BY and HAVING clauses are specified, they can appear in either order. • A side effect of GROUP BY is a ordered result, same effect as having an ORDER BY clause of the GROUP BY columns. 10

11. Grouping Things Together • Views over groups, commonly used for percent of total calculations. • Inline view example from p. 214:select categoryname, counter, (counter/bookcount)*100 “pct” from category_count, (select count(*) as bookcount from bookshelf)order by categoryname; • Group expressions can be used in the HAVING clause, even those not used in the SELECT clause. 11

12. Acknowledgements • Loney, Oracle Database 10g The Complete Reference