slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
SQL 101 3rd Session PowerPoint Presentation
Download Presentation
SQL 101 3rd Session

Loading in 2 Seconds...

play fullscreen
1 / 9

SQL 101 3rd Session - PowerPoint PPT Presentation


  • 104 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SQL 101 3rd Session' - jamuna


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

SQL 101

3rdSession

slide2

Functions:

- Character functions

- Number functions  today

- Single Row functions

slide3

Character Functions:

LOWER UPPER

INITCAP LPAD

RPAD LTRIM

RTRIM TRIM

SUBSTR INSTR

LENGTH in WHERE & GROUP BY clauses

Nested functions Concatenation

REPLACE TRANSLATE

SOUNDEX

slide4

Number Functions:

ABS SIGN

ROUND TRUNC

FLOOR CEIL

MOD REMAINDER

Floating point numbers Arithmetic operators

slide5

Single-Row Functions:

NVL COALESCE

NVL2 LNNVL

NULLIF NANVL

DECODE Searched CASE

Simple CASE

slide6

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;

slide7

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

slide8

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.

slide9

Numberfunctions....

+ -

* /

SELECT DISTINCT(cost), cost + 10,

cost – 10, cost * 10, cost / 10

FROM course;

SELECT DISTINCT cost + (cost * .10)

FROM course;