Experiment 1

 

Queries to facilitate acquaintance of Built-In Functions, String Functions, Numeric Functions, Date Functions and Conversion Functions.

Oracle Built-In-Functions:

1.      ASCII: Returns the number code that represents the specific character.

Query synatx: ASCII(single_character)

 

2.      CONCAT: Concatenates two or more strings together.

Query syntax:CONCAT(string1,string2)

3.      LENGTH: Returns the length of the specified string

Query synatx: LENGTH(string)

 

4.      REPLACE: Replaces a sequence of characters in a string with another set of character.

Query syntax:  REPLACE(expression,pattern,replacement)

 

String functions in SQL:

1.      LOWER: All the letters in 'string_value' is converted to lowercase.

Query  syntax: LOWER(string_value)

2.      UPPER: All the letters in ‘string_value’ is converted to upper case.

Query syntax: UPPER(string_value)

3.      INITCAP: All the letters in 'string_value' is converted to mixed case.

Query syntax:INITCAP(string_value)

4.      LPAD: Returns 'string_value' left-padded with 'pad_value' . length of the whole string will be of 'n' characters.

Query syntax: LPAD(string_value, n, pad_value)

5.      RPAD: Returns ‘string_value’ right-padded with ‘pad_value’, length of the whole string will be of ‘n’ characters.

Query syntax: RPAD(string_value,n,pad_value)

Numeric functions in SQL:

1.      ABS: Returns absolute value of the number ‘x’

Query syntax: ABS(x)

2.      CEIL: Returns integer value that is greater than or equal to the number ‘x’

Query syntax: CEIL(x)

3.      FLOOR: Returns integer value that is less than or equal to the number ‘x’

Query syntax: FLOOR(x)

4.      ROUND: Returns rounded off value of the number ‘x’ upto ‘y’.

Query syntax: ROUND(x,y)

Date functions in SQL:

1. ADD_MONTHS: Return a date value after adding ‘n’ months to the date ‘x’.

Query syntax: ADD_MONTHS(date,n)

2. MONTHS_BETWEEN: Returns the number of months between dates x1 and x2

Query syntax: MONTHS_BETWEEN(x1,x2)

3. LAST_DAY: It is used to determine the number of days remaining in a month from the date 'x' specified.

Query syntax: LAST_DAY(x)

Conversion functions in SQL:

1. TO_CHAR: Converts Numeric and Date values to a character string value. It cannot be used for calculations since it is a string value.

Query syntax: TO_CHAR (x [,y])

2. TO_DATE: Converts a valid Numeric and Character values to a Date value. Date is formatted to the format specified by 'date_format'.

Query syntax: TO_DATE (x [, date_format])

3. NVL: If 'x' is NULL, replace it with 'y'. 'x' and 'y' must be of the same datatype.

Query syntax: NVL(x,y)