005_Numberic_Func.sql

-- SINGLE ROW FUNCTIONS
-- A SINGLE ROW FUCNTIONS RETURN ONE RESULT PER EACH ROW

-- Aggregate FUNCTIONS MIN(), MAX(), COUNT(), SUM(), AVG()
-- THESE FUNCTIONS PROVIDE ONE RESULT PER MULTIPLE ROWS

-- NUMBERIC FUNCTIONS
-- CEIL(), ABS(), FLOOR(), POWER(), SQRT()
-- MOD() (Modulo Division)

-- DUAL TABLE IS USED AS DUMMY TABLE WE USE 
-- WHEN WE WANT TO FETCH THE DATA WITHOUT ACTUAL TABLE
SELECT 1 + 1 FROM dual; -- > 2 WITH COLUMN NAME AS "1 + 1"

SELECT 4 * 20 AS product FROM dual; -- > 80 WITH COLUMN NAME AS "PRODUCT"

-- Character Functions
-- Date / Time Functions
SELECT
    1 + 1 AS sum, -- > 2 WITH COLUMN NAME AS SUM
    10 * 20 AS product, --> 200 WITH COLUMN NAME AS PRODUCT
    5 / 2 AS quotient -- > 2 WITH COULMN NAME AS QUOTIENT
FROM dual;

    
-- ABS()
-- USED TO GET ABSOLUTE VALUE

SELECT ABS(-146) AS absolute_value FROM dual; -- > 146 
SELECT ABS(146) AS absolute_value FROM dual; -- > 146
SELECT ABS(-146.45) AS absolute_value FROM dual; -- > 146.45

-- FLOOR(), CEIL()
-- FLOOR() --> ROUNDS DOWN THE NUMBER TO NEAREST INTEGER POSSIBLE
-- CEIL() --> ROUNDS UP THE NUMBER TO NEAREST INTEGER POSSIBLE

SELECT FLOOR(15.9) FROM dual; -- > 15
SELECT CEIL(15.9) FROM dual; -- > 16
SELECT CEIL(15.1) FROM dual; -- > 16
SELECT FLOOR(-15.9) FROM dual; -- > -16
SELECT CEIL(-15.9) FROM dual; -- > -14

-- POWER(), SQRT()
SELECT POWER(6, 3), SQRT(25), SQRT(30) FROM dual; --> 216 | 5 | 5.4777

-- MOD() 
-- USED TO PERFORM MODULO DIVISION, GIVES REMAINDER AS RESULT
SELECT MOD(10, 2) AS remainder FROM dual; -- > 0
SELECT MOD(100, 41) AS remainder FROM dual; -- > 18

-- ROUND()
-- ROUNDS THE NUMBER BASED ON NUMBER OF DIGITS PROVIDED
SELECT ROUND(143.2579, 2) FROM dual;

-- TRUNC()
-- JUST DROP THE NUMBER AFTER THAT PRECISION WITH ROUNDING
SELECT TRUNC(143.2579, 2) FROM dual;