1 / 12

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

Department of Computer and Information Science, School of Science, IUPUI. Queries. Dale Roberts, Lecturer Computer Science, IUPUI E-mail: droberts@cs.iupui.edu. Getting Text Information and Changing It. There are 2 kinds of functions. Functions either: return modified the data.

sleister
Download Presentation

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

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

More Related