Oracle SQL Basic Day 5
Today we are going to cover Numerical functions of oracle SQL. Few function are as below
- Round
- Trunc
- Floor
- Mid
- Ceil
- Sign
- Abs
- Power
These function can be apply with oracle SQL, where clause and order by clause.
Before we apply these function let's take a quick look of basic math operators like multiplayer and division
select first_name, salary, salary*12 annual_salary from employees
order by salary;
select first_name, salary, salary/30 daily_salary from employees
order by salary;
select first_name, salary, round(salary/30,2) daily_salary from employees
order by salary;
The above three examples get data from employees table and show the yearly , daily salary. In third example we got the daily salary in round figure.
Now you understand basic math operators with numeric values let's take look further with built in function of oracle SQL.
. ROUND()
Description:
The ROUND() function rounds a number to a specified number of decimal places. Without the second argument, it rounds to the nearest whole number.
SELECT FIRST_NAME, SALARY, ROUND(SALARY, -2) AS ROUNDED_SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90;
SELECT FIRST_NAME, ROUND(COMMISSION_PCT, 1) AS ROUNDED_COMMISSION
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;
2. TRUNC()
Description:
TRUNC() cuts off digits after a certain decimal place without rounding. It’s useful for financial and reporting purposes.
SELECT FIRST_NAME, SALARY, TRUNC(SALARY, -2) AS TRUNC_SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100;
SELECT FIRST_NAME, TRUNC(COMMISSION_PCT, 2) AS TRUNC_COMMISSION
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;
3. MOD()
Description:
MOD() returns the remainder from a division. It helps in identifying even/odd values, recurring periods, or cyclic patterns.
SELECT EMPLOYEE_ID, SALARY, MOD(SALARY, 1000) AS SALARY_REMAINDER
FROM EMPLOYEES;
SELECT EMPLOYEE_ID, MOD(EMPLOYEE_ID, 2) AS EVEN_OR_ODD
FROM EMPLOYEES;
4. FLOOR()
Description:
FLOOR() returns the greatest integer less than or equal to a number. It's useful when you want to round down.
SELECT FIRST_NAME, SALARY, FLOOR(SALARY / 1000) AS THOUSANDS
FROM EMPLOYEES;
SELECT EMPLOYEE_ID, FLOOR(COMMISSION_PCT * 100) AS PERCENTAGE
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;
5. CEIL()
Description:
CEIL() returns the smallest integer greater than or equal to a number. It’s often used in pagination or range calculations.
SELECT FIRST_NAME, SALARY, CEIL(SALARY / 1000) AS CEIL_THOUSANDS
FROM EMPLOYEES;
SELECT EMPLOYEE_ID, CEIL(COMMISSION_PCT * 10) AS CEIL_COMMISSION
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;
6. POWER()
Description:
POWER() raises a number to the power of another. Ideal for exponential growth or mathematical models.
SELECT EMPLOYEE_ID, POWER(COMMISSION_PCT, 2) AS COMMISSION_SQUARED
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;
SELECT EMPLOYEE_ID, POWER(SALARY, 2) AS SALARY_SQUARED
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80;
7. ABS()
Description:
ABS() returns the absolute value of a number. It's used when only magnitude matters, regardless of sign.
SELECT EMPLOYEE_ID, SALARY - 5000 AS SALARY_DIFF, ABS(SALARY - 5000) AS ABS_DIFF
FROM EMPLOYEES;
SELECT EMPLOYEE_ID, ABS(COMMISSION_PCT - 0.2) AS COMMISSION_VARIANCE
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;
8. SIGN()
Description:
SIGN() returns 1 if the number is positive, 0 if zero, and -1 if negative. Great for trend and movement analysis.
SELECT EMPLOYEE_ID, SALARY - 5000 AS DIFF, SIGN(SALARY - 5000) AS SALARY_SIGN
FROM EMPLOYEES;
SELECT EMPLOYEE_ID, SIGN(NVL(COMMISSION_PCT, 0)) AS HAS_COMMISSION
FROM EMPLOYEES;
Hopefully you understand numeric function in oracle SQL. Below are some more example queries for practice and understanding purpose. You may execute them one by one and compare the results.
SELECT FIRST_NAME, SALARY, ROUND(SALARY, -2) AS ROUNDED_SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;
SELECT EMPLOYEE_ID, FIRST_NAME, MOD(EMPLOYEE_ID, 2) AS IS_ODD
FROM EMPLOYEES
WHERE MOD(EMPLOYEE_ID, 2) = 1;
SELECT FIRST_NAME, SALARY, CEIL(SALARY * 0.10) AS ESTIMATED_BONUS
FROM EMPLOYEES;
SELECT FIRST_NAME, COMMISSION_PCT, SIGN(NVL(COMMISSION_PCT, 0)) AS COMMISSION_STATUS
FROM EMPLOYEES;
SELECT E.EMPLOYEE_ID, E.SALARY,
ABS(E.SALARY - (SELECT AVG(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID = E.DEPARTMENT_ID)) AS SALARY_GAP
FROM EMPLOYEES E
WHERE E.DEPARTMENT_ID IS NOT NULL;
SELECT EMPLOYEE_ID, COMMISSION_PCT,
POWER(COMMISSION_PCT, 2) AS COMMISSION_IMPACT
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;
Conclusion
These numeric functions in Oracle SQL simplify data manipulation and add power to your queries. By using the HR schema, you can experiment with real employee and department data for hands-on practice.
Explore, modify, and build on these examples to better understand your data and sharpen your SQL skills!
0 Comments