Experiment No: 6

AIM

RDBMS Built in Functions

There are two types of functions:

1. Single Row Functions: Single row or Scalar functions return a value for every row that is processed in a query.
2. Group Functions: These functions group the rows of data based on the values returned by the query. This is discussed in SQL GROUP Functions. The group functions are used to calculate aggregate values like total or average, which return just one total or one average value after processing a group of rows.

There are four types of single row functions. They are:

1. Numeric Functions: These are functions that accept numeric input and return numeric values.
2. Character or Text Functions: These are functions that accept character input and can return both character and number values.
3. Date Functions: These are functions that take values that are of datatype DATE as input and return values of datatype DATE, except for the MONTHS_BETWEEN function, which returns a number.
4. Conversion Functions: These are functions that help us to convert a value in one form to another form. For Example: a null value into an actual value, or a value from one datatype to another datatype like NVL, TO_CHAR, TO_NUMBER, TO_DATE etc.

COMMANDS

Mathematical Functions

1. ABS

SELECT ABS(-13) FROM DUAL;

2. FLOOR

SELECT FLOOR(6.9) FROM DUAL;

3. CEIL

SELECT CEIL(6.9) FROM DUAL;

4. GREATEST

SELECT GREATEST(11, 22, 33, 44, 55) FROM DUAL;

5. LEAST

SELECT LEAST(11, 22, 33, 44, 55) FROM DUAL;

6. LENGTH

SELECT LENGTH('Adithyan A') FROM DUAL

7. SQRT

SELECT SQRT(25) FROM DUAL;

8. POWER

SELECT POWER(2, 4) FROM DUAL;

9. ROUND

SELECT ROUND(5.8) FROM DUAL;

10. SIN

SELECT SIN(90) FROM DUAL

11. COS

SELECT COS(45) FROM DUAL

12. TAN

SELECT TAN(30) FROM DUAL;

13. SIGN

SELECT SIGN(-5), SIGN(5), SIGN(0) FROM DUAL;

14. LOG

SELECT LOG(10, 100), LOG(100, 10) FROM DUAL;

15. MOD

SELECT MOD(4, 3) FROM DUAL;

16. EXP

SELECT EXP(2) FROM DUAL;

Date Functions

1. CURRENT_DATE

SELECT CURRENT_DATE FROM DUAL;

2. Select Year Only

SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL

3. Select Day Only

SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL;

4. Select Current Month

SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL;

5. SYSDATE

SELECT SYSDATE FROM DUAL;

String Functions

1. ASCII

SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL;

2. UPPER

SELECT UPPER('Adithyan A') FROM DUAL;

3. LOWER

SELECT LOWER('Adithyan A') FROM DUAL;

4. REPLACE

SELECT REPLACE('hello', 'h', 'm') FROM DUAL;

5. TRIM

SELECT TRIM('aanacondaa'), TRIM('a' from 'aanacondaa'), ltrim('aanacondaa', 'a'), rtrim('aanacondaa', 'a') FROM DUAL;