Functions in databases
Functions in databases are a powerful and useful tool when using the SELECT statement. The functions of the SELECT statement are flexible and logical.
Single Row Functions
One-row functions in Oracle are Functions that deal with data of one row only and have a single value and are used anywhere in the SELECT clause.
Types of single row functions
- Character Functions.
- Number Functions.
- Date Functions.
- Conversion Functions.
Character Functions
Literal functions are the functions that deal with literal data, and their data is either a number or a letter. There are several values for this, which we will mention:
LOWER function: Used to convert all characters of a column or string to lowercase.
Select LOWER('GOOD by') from dual;
UPPER function: Used to convert all characters of a column or string to uppercase.
Select UPPER('GOOD by') from dual;
INITCAP function: It is used to convert only the first character of a column or string to an uppercase letter and the rest of the characters to lowercase.
Select INITCAP('GOOD') from dual;
CONCAT function: It is used to join two columns or strings together, and it is like performing a concatenation (||).
Select CONCAT('GOOD', 'BY') from dual;
SUBSTR function: Used to cut a part of a column or string starting with the letter M and the number of characters N.
Select SUBSTR('GOOD BY', 2,3) from dual;
LENGTH: used to find the number of characters in a string or column.
Select LENGTH('GOOD') from dual;
INSTR function: used to search and locate the required character within the column or string.
Select INSTR('GOOD', 'D') from dual;
LPAD function: used to set or add data to a column or string from the right by filling in a specific character from the left.
Select LPAD('AHMED',10, '*') from dual;
RPAD function: used to set or add data to a column or string from the left side by filling in a specific character from the right.
Select RPAD('AHMED',10 '*') from dual;
TRIM function: used to cut a specific character from the beginning or end of a word only.
Select TRIM('S' FROM 'SAMI') from dual;
DUAL function: It is a dummy table inside the SQL language that is used to perform operations in which no table is entered from inside the database.
SELECT LOWER(ename) , UPPER(job) , INITCAP(job) , CONCAT(ename, job)
FROM emp
WHERE sal=3000 ;
Lower (ENAME) | UPPER (JOB) | INITCAP | CONCAT (ENAME, JOB) |
scott | ANALYST | Analyst | SCOTTANALYST |
ford | ANALYST | Analyst | FORDANALYST |
This example displays employee names in lowercase using the LOWER function and displays the job in uppercase using the UPPER function. And display the first letter as uppercase and the rest of the letters lowercase in the function field using the INITCAP function. Then the CONCAT function combines two text strings employee name with the job to appear as one field from the employee FROM emp table.
Number Functions
They are functions that work with numeric data and their result is a number only, and they have some values, which are:
ROUND: Used to cut a specific number from the decimal part and round the numbers to the nearest decimal. or to an integer and n indicates the number of digits after the decimal point and there are cases for the letter n.
- If (n = 0), the rounding is to the nearest whole number.
- If (n<0) is a negative number, the rounding is before the decimal point (the integer).
- If (n>0) is a positive number, the rounding is after the decimal point (the decimal part).
TRUNC function: Used to cut a specific part of a decimal number without rounding and has states.
- If (n=0), the whole decimal part is cut off and the result is an integer.
- If (n>0) is a positive number then the shear is in the part after the decimal point (the decimal part).
- If (n<0) is a negative number then the shear is before the decimal point (the integer).
MOD function: used to find the remainder of dividing m by n.
SQL> SELECT TRUNC(45.923,0),TRUNC(45.923,2),TRUNC(45.923, 1),TRUNC(45.923,-2)
2 FROM dual ;
TRUNC(45,923,0) | TRUNC(45,923,2) | TRUNC(45,923,-1) | TRUNC(45,923,-2) |
45 | 45.92 | 40 | 0 |
The example shows the effect of the numerical function ROUND on numbers, and we find that when n was equal to zero, the decimal part was cut.
- When n is 2, the decimal is clipped to just two digits after the decimal point.
- When n is -1, the number 5 is cut from the part before the decimal point, so the result is 40.
- When n equals -2, the two integers are clipped to zero.
Date Functions
They are functions that deal with data of type date, bearing in mind that Oracle has stored the date. As "DD-MON-YY". There are several subfunctions available to deal with the date, which are:
SYSDATE function: This function is used to display the system history of the current device.
SELECT SYSDATE FROM DUAL;
MONTHS_BETWEEN function: used to find the number of months between two dates.
MONTHS_BETWEEN('01-SEP-95' , '11-JAN-94')
ADD_MONTHS function: used to add a certain number of months to an existing date.
ADD_MONTHS('11-JAN-94' , 6)
NEXT_DAY: Used to find the date of a particular day after the current day.
NEXT_DAY('01-SEP-95' , 'FRIDAY')
LAST_DAY function: This function is used to find the last day of the current month.
LAST_DAY('01-SEP-95')
ROUND: Used to round the date to the nearest month or year.
ROUND('25-JUL-95' , 'MONTH')
TRUNC function: Used to trim the date to the nearest month or year.
TRUNC('25-JUL-95' , 'MONTH')
Conversion Functions in SQL
They are functions that convert data from one type to another, and they are:
TO_CHAR function: This function is used to convert data from numeric type or date to literal data in a specific form "FORMAT" on demand fmt.
SQL> SELECT sysdate,TO_CHAR(sysdate,'DD/MM/YYYY')
2 FROM dual ;
TO_DATE function: used to convert literal data into date data with a specific format "FORMAT" on demand fmt.
SQL> SELECT TO_DATE( 'FEBRUARY 22, 1981' , 'MONTH DD, YYYY' )
2 FROM dual
TO_NUMBER function: used to convert literal data into numeric data in a specific format "FORMAT" on demand fmt.
SQL> SELECT TO_NUMBER( HIREDATE ,'DD ,MONTH,YY' )
2 FROM EMP
3 where sal>2000;
Practical 1
Displaying the names of the employees and then cutting a part of the names and displaying them in another field using the (SUBSTR(ENAME,2,3F) function) starting from the letter number 2 with a space of "3" characters, then displaying the number of characters of the employees' names. Using the (LENGTH) function, then Display the order of the letter 'K' starting from the left, using the WHERE function to avoid the error.
SQL> SELECT ename,
2 SUBSTR(ename,2,3),LENGTH(ename),INSTR(ename,'K')
3 FROM emp
4 WHERE sal>1000 ;
Practical 2
Display the employee's name, salary, commission, and the remainder of dividing his salary by the commission he takes.
SQL> SELECT ename , sal , comm , MOD(sal,comm)
2 FROM emp
3 WHERE sal=1600 ;
ENAME | SAL | COMM | MOD(SAL,COMM) |
ALLEN | 1600 | 300 | 100 |
Practical 3
Display the names of the employees and then display the names after cutting the letter 'S' using the (TRIM) function from the beginning or end of the name. Then display the names of the employees and add some symbols (*) from the beginning of the name, so that the length of the name is ten. Characters using the (LPAD) function, then displaying the names of the employees with the addition of some symbols (#) from the end of the name, so that the name becomes ten characters long using the (RPAD) function.
SQL>SELECT ename,TRIM('S' FROM ename), LPAD(ename,10,'*') , RPAD(ename,10,'#')
2 FROM emp
3 WHERE sal>2500 ;
ENAME | TRIM('S'FR) | LPAD(ENAME) | RPAD(ENAME) |
JONES | JONE | *****JONES | JONES##### |
BLAKE | BLAKE | *****BLAKE | BLAKE##### |
SCOTT | COTT | *****SCOTT | SCOTT##### |
KING | KING | *****KING | KING##### |
FORD | FORD | *****FORD | FORD##### |
Practical 4
Displaying the employee's name, employee number, and salary, and converting the salary to a literal value, so that the salary formula is in the following form ('sal,' $99,999).
SQL> SELECT empno, ename,sal, TO_CHAR(sal , '$99,999') salary
2 FROM emp
3 WHERE sal > 2500 ;
EMPNO | ENAME | SAL | SALARY |
7566 | JONES | 2975 | $2.975 |
7698 | BLAKE | 2850 | $2.850 |
7788 | SCOTT | 3000 | $3.000 |
7839 | KING | 5000 | $5.000 |
7902 | FORD | 3000 | $3.000 |
Notes for '$99,999'
- The number 9: When this number is repeated, it represents the number of digits that appear when we write (99), which means that two numbers appear.
- The number 099: means the appearance of a number and a zero before it.
- The number 990: means zero if the value is zero.
- The number $99: the dollar sign appears before the number and the decimal point is after two digits.
- Comma .: Show the decimal point.
- Millennium Comma ,: Show the comma between every three digits.
- M1: Shows the negative sign if the value is negative.
Aggregate Functions
These are functions that use more than one row in a SELECT query to return a single value. Unlike single-row functions, which handle a single row to output a single value.
Aggregate functions of more than one class
They are functions that manipulate the data of a group of rows to output just one value, such as an addition. For employee salaries, or to know the most salary to be paid to the employee, as well as the least salary to be disbursed.
Types of associative functions
- SUM: used to find the sum of a number of values.
- MAX: used to find the largest value from a sum of values.
- MIN: is used to find the lowest value out of a sum of values.
- AVG: Used to find the arithmetic mean of a set of values.
- COUNT: Used to find the number of values or the number of rows and also ignores NULLs.
- STDDEV DEVIATION: This is used to find the standard deviation of a set of values.
- VARIANCE: Used to find the amount of variance for a set of values.
- NVL: Used to solve for nulls in any column because aggregate functions ignore nulls.
Example
Display the total salaries of employees, the highest salary and the lowest salary and the arithmetic average of salaries.
SQL> SELECT SUM(sal) , MAX(sal) , MIN(sal) , AVG(sal)
2 FROM emp ;
SUM(SAL) | MAX(SAL) | MIN(SAL) | AVG(SAL) |
29025 | 5000 | 800 | 2073.21429 |
Both functions MAX and MIN deal with all data when used with literal data and the result is in alphabetical order
Example
Display the first alphabetic names and the last names.
SQL> SELECT MAX(ename) , MIN(ename)
2 FROM emp ;
MAX ( ENAME ) | MIN ( ENAME ) |
WARD | ADAMS |
Example
Display the arithmetic mean of employee rewards.
SQL> SELECT AVG( NVL(comm , 0) )
2 FROM emp ;
AVG ( NVL(COMM,0) |
157.142857 |
The NVL function is used to divide the total rewards by all employees. Including empty values means that it is divided by 14 employees, but if it is used.
SQL> SELECT AVG( comm )
2 FROM emp ;
It will only be divided by the fields of the employees who receive the rewards. Null values will be ignored and the result will be 550.
Working with the COUNT function
Function ((*)COUNT): Used to count all rows within the table including the duplicate rows. and rows containing NULL and if values. If the condition clause contains a condition, it counts rows according to the condition clause.
Function (COUNT(column)): Used to count values or data for a particular column, including duplicate data, ignoring only NULLs.
Example
Displays the total number of rows within the employees table, the number of employees receiving bonuses, and the number of departments within the (deptno) column.
SQL> SELECT COUNT(*),COUNT(comm), COUNT(deptno)
2 FROM emp ;
COUNT(*) | COUNT(COMM) | COUNT(DEPTNO) |
14 | 4 | 14 |
The Group By function in Oracle
It is a condition function that divides data into groups according to one or more specific columns, and that column may be a secondary key in another column.
SQL >SELECT deptno , MAX(sal)
2 FROM emp
3 GROUP BY deptno ;
DEPTNO | MAX ( SAL ) |
30 | 2850 |
20 | 3000 |
10 | 5000 |
The employees were divided into groups according to the number of management, and the largest salary was in the tenth department, which was 5000. The largest salary was in the twenty department, which was 3000, and the largest salary was in the thirty department. It is 2850 and the condition function Group By was used because of the Max grouping function.
SQL> SELECT deptno , sal FROM emp;
Use of aggregate functions
- When writing any column within the Select list, it must be written with the Group By part, because aggregate functions deal with several rows.
- The ORDER BY part can be used to arrange rows with aggregate as shown in the previous example.
- Aggregate functions cannot be used in the Where part, but we use the Having part instead.
ORDER BY condition function for Oracle
ORDER BY is used to arrange rows with aggregate functions according to each row, such as finding the arithmetic mean for each administration separately.
HAVING function to make conditions on functions
It is a function used to make a specific condition on aggregate functions instead of Where.
Example
Displaying the arithmetic average of the salaries of employees in each department, and the outputs were arranged ascendingly according to the average.
SQL> SELECT deptno , AVG(sal)
2 FROM emp
3 GROUP BY deptno
4 ORDER BY AVG(sal) ;
DEPTNO | AVG ( SAL ) |
30 | 1566.66667 |
20 | 2175 |
10 | 2916.6667 |
When writing the column (deptno) within the Select menu and we did not write it within the Group By part, an error message will appear if when we write the column (deptno) we must write the column (deptno) in the Group By part.
When using aggregate functions inside the Where condition function, an error message appears and instead we use the Having clause.
SQL> SELECT deptno , AVG(sal)
2 FROM emp
3 WHERE AVG(sal) > 2000
4 GROUP BY deptno ;
ERROR at line 3 :
ORA-00934: group function is not allowed herev #error message
Example
Display the arithmetic average of the salaries of employees in each department, provided that the arithmetic averages are. For salaries greater than 2000 we will use the condition function Having.
SQL> SELECT deptno , AVG(sal)
2 FROM emp
3 GROUP BY deptno
4 HAVING AVG(sal) > 2000 ;
DEPTNO | AVG ( SAL ) |
20 | 2175 |
10 | 2916.66667 |