SQL Queries

Scalar Function:- Oracle Scalar Functions allow you to perform different calculations on data values. There are different types of Scalar Functions.

1. String Function - functions that perform operations on character values.

2. Numeric Function - functions that perform operations on numeric values.

3. Date Function - functions that perform operations on date values.

4. Conversion Function - functions that convert column data types.

5. NULL-related Function - functions for handling null values.

Oracle String Functions:-

1. CONCAT - Returns text strings concatenated

     select concat('Hello','World') from dual;

2. INSTR - Returns the location of a substring in a string.

     select instr('parag','r') from dual;

3. LENGTH - Returns the number of characters of the specified string expression.

     select length('parag') from dual;

4. RTRIM - Returns a character string after truncating all trailing blanks.

     select rtrim('  parag      ') from dual;

5. LTRIM - Returns a character expression after it removes leading blanks.

    select ltrim('    parag    ') from dual;

6. REPLACE - Replaces all occurrences of a specified string value with another string value.

    select replace('hello','e','$') from dual;

7. REVERSE - Returns the reverse order of a string value.

    select reverse('parag') from dual;

8. SUBSTR - Returns part of a text.

     select substr('hello',2,3) from dual;

9. LOWER - Returns a character expression after converting uppercase character data to lowercase.

     select lower('PARAG') from dual;

10. UPPER - Returns a character expression with lowercase character data converted to uppercase.

     select upper('parag') from dual;

Oracle Numeric Functions:-
1. TRUNC - Returns an integer that is less than or equal to the specified numeric expression.
    select trunc(69.9) from dual;

2. CEIL - Returns an integer that is greater than, or equal to, the specified numeric expression.
    select ceil(69.1) from dual;

3.ROUND - Returns a numeric value, rounded to the specified length or precision.
    select round(69.9) from dual;

How to insert date and time in oracle?
Using to_date() function we can insert date and time in oracle. To insert date and time we need to use date as a data type.
CREATE TABLE WorkOn ( StaffNo NCHAR(4), MechanicName VARCHAR(50), DateTime DATE, Hours VARCHAR(2) ) ;
insert into WorkOn values('101','Parag',to_date('22/06/2018 8:30:00AM','DD/MM/YYYY HH24:MI:SS'),3);