1 / 9

SQL 101 3rd Session

SQL 101 3rd Session. Functions: - Character functions - Number functions  today - Single Row functions. Character Functions: LOWER UPPER INITCAP LPAD RPAD LTRIM RTRIM TRIM SUBSTR INSTR LENGTH in WHERE & GROUP BY clauses Nested functions Concatenation

jamuna
Download Presentation

SQL 101 3rd Session

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. SQL 101 3rdSession

  2. Functions: - Character functions - Number functions  today - Single Row functions

  3. Character Functions: LOWER UPPER INITCAP LPAD RPAD LTRIM RTRIM TRIM SUBSTR INSTR LENGTH in WHERE & GROUP BY clauses Nested functions Concatenation REPLACE TRANSLATE SOUNDEX

  4. Number Functions: ABS SIGN ROUND TRUNC FLOOR CEIL MOD REMAINDER Floating point numbers Arithmetic operators

  5. Single-Row Functions: NVL COALESCE NVL2 LNNVL NULLIF NANVL DECODE Searched CASE Simple CASE

  6. Numberfunctions.... ABS(value) SIGN(value) ROUND(value [, precision]) TRUNC(value [, precision]) SELECT 'The absolute value of -29 is '||ABS(-29) FROM dual; SELECT -14, SIGN(-14), SIGN(14), SIGN(0), ABS(-14) FROM dual; SELECT 222.34501, ROUND(222.34566, 2), TRUNC(222.34566, 1) FROM dual; SELECT 222.34501, ROUND(222.34566, -2), TRUNC(222.34566, -1) FROM dual;

  7. Round 249.34566 to the number 250 Round 249.34566 to the number 249.35 Round 249.34566 to ZERO Truncate 249.34566 to 200

  8. Numberfunctions.... FLOOR(value) CEIL(value) MOD(value, divisor) REMAINDER(value, divisor) Floating Point Numbers SELECT FLOOR(22.53), CEIL(22.49), TRUNC(22.5), ROUND(22.5) FROM dual; SELECT MOD(23,8) FROM dual; SELECT MOD(23,8), REMAINDER(23,8) FROM dual; Ref: http://docs.oracle.com/cd/E11882_01/server.112/e25789/tablecls.htm#CNCPT1834 Floating-Point Numbers Oracle Database provides two numeric data types exclusively for floating-point numbers: BINARY_FLOAT and BINARY_DOUBLE. These types support all of the basic functionality provided by the NUMBER data type. However, while NUMBER uses decimal precision, BINARY_FLOAT and BINARY_DOUBLE use binary precision, which enables faster arithmetic calculations and usually reduces storage requirements. BINARY_FLOAT and BINARY_DOUBLE are approximate numeric data types. They store approximate representations of decimal values, rather than exact representations. For example, the value 0.1 cannot be exactly represented by either BINARY_DOUBLE or BINARY_FLOAT. They are frequently used for scientific computations. Their behavior is similar to the data types FLOAT and DOUBLE in Java and XMLSchema.

  9. Numberfunctions.... + - * / SELECT DISTINCT(cost), cost + 10, cost – 10, cost * 10, cost / 10 FROM course; SELECT DISTINCT cost + (cost * .10) FROM course;

More Related