This article provides a thorough explanation for beginners on the MOD and POWER functions, which are extremely useful in numeric manipulation. Utilizing the SCOTT schema, it covers a wide range from basic usage to practical application examples. SQL result examples and key points are also included to help readers understand intuitively.
This article uses examples based on the SCOTT sample schema. You can import and use the SCOTT sample schema by executing the following:
$ sqlplus / as sysdba
SQL> @?/rdbms/admin/utlsampl.sql
$ sqlplus scott/tiger
MOD Function: A Function to Easily Find the Remainder
The MOD function is used to find the remainder after one number is divided by another. While it seems like a simple operation, it is frequently used in data classification and conditional checks.
Basic Syntax
MOD(dividend, divisor)
- dividend: The number to be divided.
- divisor: The number to divide by.
Example: MOD(10, 3) returns 1 (the remainder of 10 divided by 3 is 1).
Practical Example: Classification Based on Salary
In the following example, we calculate the remainder of an employee’s salary (SAL) divided by 500. This is also useful for checking which employees have a salary that is a multiple of 500.
SELECT EMPNO, ENAME, SAL,
MOD(SAL, 500) AS SAL_MOD
FROM EMP;
Result:
SQL> SELECT EMPNO, ENAME, SAL,
2 MOD(SAL, 500) AS SAL_MOD
3 FROM EMP;
EMPNO ENAME SAL SAL_MOD
---------- ---------- ---------- ----------
7369 SMITH 800 300
7499 ALLEN 1600 100
7521 WARD 1250 250
7566 JONES 2975 475
7654 MARTIN 1250 250
7698 BLAKE 2850 350
7782 CLARK 2450 450
7788 SCOTT 3000 0
7839 KING 5000 0
7844 TURNER 1500 0
7876 ADAMS 1100 100
7900 JAMES 950 450
7902 FORD 3000 0
7934 MILLER 1300 300
14 rows selected.
Explanation:
- The
SAL_MODcolumn displays the remainder of the salary divided by 500. - A row with a remainder of 0 means the salary is a multiple of 500.
Applied Example 1: Determining Even and Odd Numbers
Using the MOD function, you can easily determine if a number is even or odd.
SELECT EMPNO, ENAME, SAL,
CASE
WHEN MOD(SAL, 2) = 0 THEN 'EVEN NUMBER'
ELSE 'ODD NUMBER'
END AS SAL_TYPE
FROM EMP;
SQL> SELECT EMPNO, ENAME, SAL,
2 CASE
3 WHEN MOD(SAL, 2) = 0 THEN 'EVEN NUMBER'
4 ELSE 'ODD NUMBER'
5 END AS SAL_TYPE
6 FROM EMP;
EMPNO ENAME SAL SAL_TYPE
---------- ---------- ---------- -----------
7369 SMITH 800 EVEN NUMBER
7499 ALLEN 1600 EVEN NUMBER
7521 WARD 1250 EVEN NUMBER
7566 JONES 2975 ODD NUMBER
7654 MARTIN 1250 EVEN NUMBER
7698 BLAKE 2850 EVEN NUMBER
7782 CLARK 2450 EVEN NUMBER
7788 SCOTT 3000 EVEN NUMBER
7839 KING 5000 EVEN NUMBER
7844 TURNER 1500 EVEN NUMBER
7876 ADAMS 1100 EVEN NUMBER
7900 JAMES 950 EVEN NUMBER
7902 FORD 3000 EVEN NUMBER
7934 MILLER 1300 EVEN NUMBER
14 rows selected.
Applied Example 2: Grouping
We will divide employees into 3 groups using their employee number (EMPNO).
SELECT EMPNO, ENAME,
MOD(EMPNO, 3) AS GROUP_NO
FROM EMP;
SQL> SELECT EMPNO, ENAME,
2 MOD(EMPNO, 3) AS GROUP_NO
3 FROM EMP;
EMPNO ENAME GROUP_NO
---------- ---------- ----------
7369 SMITH 1
7499 ALLEN 2
7521 WARD 0
7566 JONES 0
7654 MARTIN 1
7698 BLAKE 0
7782 CLARK 0
7788 SCOTT 0
7839 KING 0
7844 TURNER 2
7876 ADAMS 1
7900 JAMES 1
7902 FORD 0
7934 MILLER 2
14 rows selected.
Explanation:
- The result of
MOD(EMPNO, 3)is classified as either 0, 1, or 2. - This allows for an even partition of the data.
POWER Function: A Function to Easily Calculate Powers
The POWER function raises a specified number to an exponent. It is convenient for exponential calculations and is frequently used in scenarios requiring mathematical processing.
Basic Syntax
POWER(base, exponent)
- base: The base number (the number to be raised to a power).
- exponent: The number of times to raise the power.
Example: POWER(2, 3) returns 8 (the result of 2 raised to the 3rd power).
Practical Example: Calculating Powers of Salary
In the following example, we calculate the employee’s salary squared (2nd power) and cubed (3rd power).
SELECT EMPNO, ENAME, SAL,
POWER(SAL, 2) AS SAL_SQUARE,
POWER(SAL, 3) AS SAL_CUBE
FROM EMP;
Result:
SQL> col sal_cube for 999999999999999999
SQL> SELECT EMPNO, ENAME, SAL,
2 POWER(SAL, 2) AS SAL_SQUARE,
3 POWER(SAL, 3) AS SAL_CUBE
4 FROM EMP;
EMPNO ENAME SAL SAL_SQUARE SAL_CUBE
---------- ---------- ---------- ---------- -------------------
7369 SMITH 800 640000 512000000
7499 ALLEN 1600 2560000 4096000000
7521 WARD 1250 1562500 1953125000
7566 JONES 2975 8850625 26330609375
7654 MARTIN 1250 1562500 1953125000
7698 BLAKE 2850 8122500 23149125000
7782 CLARK 2450 6002500 14706125000
7788 SCOTT 3000 9000000 27000000000
7839 KING 5000 25000000 125000000000
7844 TURNER 1500 2250000 3375000000
7876 ADAMS 1100 1210000 1331000000
7900 JAMES 950 902500 857375000
7902 FORD 3000 9000000 27000000000
7934 MILLER 1300 1690000 2197000000
14 rows selected.
Applied Example: Score Calculation Based on Name Length
We will square the length of the employee name (LENGTH function) to create a score.
SELECT ENAME, LENGTH(ENAME) AS NAME_LENGTH,
POWER(LENGTH(ENAME), 2) AS NAME_SCORE
FROM EMP;
SQL> SELECT ENAME, LENGTH(ENAME) AS NAME_LENGTH,
2 POWER(LENGTH(ENAME), 2) AS NAME_SCORE
3 FROM EMP;
ENAME NAME_LENGTH NAME_SCORE
---------- ----------- ----------
SMITH 5 25
ALLEN 5 25
WARD 4 16
JONES 5 25
MARTIN 6 36
BLAKE 5 25
CLARK 5 25
SCOTT 5 25
KING 4 16
TURNER 6 36
ADAMS 5 25
JAMES 5 25
FORD 4 16
MILLER 6 36
14 rows selected.
Advanced Application Combining MOD and POWER
We calculate the salary cubed (3rd power) and then find the remainder when the result is divided by a specific value. This enables complex data analysis.
SELECT EMPNO, ENAME, SAL,
MOD(POWER(SAL, 3), 7) AS RESULT
FROM EMP;
SQL> SELECT EMPNO, ENAME, SAL,
2 MOD(POWER(SAL, 3), 7) AS RESULT
3 FROM EMP;
EMPNO ENAME SAL RESULT
---------- ---------- ---------- ----------
7369 SMITH 800 1
7499 ALLEN 1600 1
7521 WARD 1250 1
7566 JONES 2975 0
7654 MARTIN 1250 1
7698 BLAKE 2850 1
7782 CLARK 2450 0
7788 SCOTT 3000 1
7839 KING 5000 1
7844 TURNER 1500 1
7876 ADAMS 1100 1
7900 JAMES 950 6
7902 FORD 3000 1
7934 MILLER 1300 6
14 rows selected.
Points to Note
- In the
MODfunction, specifying 0 as the divisor will cause an error (zero division error). - If you use a very large exponent in the
POWERfunction, the result may become too large and cause an overflow.
Exercises
Deepen your understanding by trying the following problems.
Problem 1: Create an SQL query to determine if the salary (SAL) is an even or odd number.
Answer
Show Answer (Click here)
SQL> SELECT
2 ENAME,
3 SAL,
4 CASE
5 WHEN MOD(SAL, 2) = 0 THEN 'EVEN NUMBER'
6 ELSE 'ODD NUMBER'
7 END AS SAL_TYPE
8 FROM
9 SCOTT.EMP;
ENAME SAL SAL_TYPE
---------- ---------- -----------
SMITH 800 EVEN NUMBER
ALLEN 1600 EVEN NUMBER
WARD 1250 EVEN NUMBER
JONES 2975 ODD NUMBER
MARTIN 1250 EVEN NUMBER
BLAKE 2850 EVEN NUMBER
CLARK 2450 EVEN NUMBER
SCOTT 3000 EVEN NUMBER
KING 5000 EVEN NUMBER
TURNER 1500 EVEN NUMBER
ADAMS 1100 EVEN NUMBER
JAMES 950 EVEN NUMBER
FORD 3000 EVEN NUMBER
MILLER 1300 EVEN NUMBER
14 rows selected.
Problem 2: Create an SQL query to calculate the employee number (EMPNO) cubed (3rd power) and find the remainder when divided by 10.
Answer
Show Answer (Click here)
SQL> SELECT
2 EMPNO,
3 MOD(POWER(EMPNO, 3), 10) AS RESULT
4 FROM
5 SCOTT.EMP;
EMPNO RESULT
---------- ----------
7369 9
7499 9
7521 1
7566 6
7654 4
7698 2
7782 8
7788 2
7839 9
7844 4
7876 6
7900 0
7902 8
7934 4
14 rows selected.
Problem 3: Create an SQL query to calculate the length of the employee name cubed (3rd power) and find the remainder when the result is divided by 5.
Answer
Show Answer (Click here)
SQL> SELECT
2 ENAME,
3 MOD(POWER(LENGTH(ENAME), 3), 5) AS RESULT
4 FROM
5 SCOTT.EMP;
ENAME RESULT
---------- ----------
SMITH 0
ALLEN 0
WARD 4
JONES 0
MARTIN 1
BLAKE 0
CLARK 0
SCOTT 0
KING 4
TURNER 1
ADAMS 0
JAMES 0
FORD 4
MILLER 1
14 rows selected.
Summary
- The
MODfunction easily performs remainder calculations and is useful for data classification and conditional checks. - The
POWERfunction efficiently performs power calculations and can be used in mathematical processing and analysis. - By combining both functions, complex numerical operations become possible, broadening the scope of analysis.
Try executing the SQL yourself using the SCOTT schema examples as a reference! By mastering these functions, you can further improve your SQL skills.
[reference]
Oracle Database SQL Language Reference, 19c
コメント