# DATA MANIPULATION SQL Function

## What is SQL Function?

SQL has a lot of built-in functions for counting and calculations. |

Functions serve the purpose of performing operations on data such as manipulation of data items and returning a result. Functions accept variables or constants, supplied by user and operate upon them. Such variables or constants are called ARGUMENTS. |

SQL has several in-built functions which are used for this purpose. They can be classified as: |

## Aggregate functions

Aggregate functions operate against a collection of values, but return a single value. |

Note: If used among many other expressions in the item list of a SELECT statement, the SELECT must have a GROUP BY clause!! |

Following are SQL Aggregate Functions with their description: |

Syntax: |

SELECT function_name(column_name) FROM table_name |

Example for the Employee table below: |

We key in the query |

SELECT Avg(Salary) FROM Employee |

will yield the result set that shows the average salary of the employees: |

Similarly we can use the sum function to Add all the numeric values in the selected table by typing in the following query: |

Lets take an example using Max function to find out the maximum value from the field-Age. We type the following query: |

SELECT Max(Age) FROM Employee; |

which results in: |

Similarly we can use MIn function to find the minimum value from a column |

We key in another query which is slightly different from others because he function that we use here i.e, count(*), is not supplied with any column, as shown below: |

SELECT Count(*) FROM Employee |

The above statement returns total number of rows in the Employee table i.e |

## Scalar functions

Scalar functions operate against a single value, and return a single value based on the input value. |

We group the scalar functions into three categories and discuss them one by one. The three categories are: |

Numeric functions String Functions Conversion functions |

About Dual |

Before explaining the various Scalar functions, lets understand first, what a Dual is and its use. A Dual is a small worktable. It consists of one row and one column only and supports arithmetic calculations, date retrieval and it’s formatting. |

There are situations when arithmetic calculations are to be done such as 14*26, where no table is referenced as only numeric literals are used. However to display the output on screen, SQL has SELECT option which makes mentioning a table name mandatory in its FROM clause. In such cases we use Dual. It is a virtual/dummy table provided by Oracle to facilitate such calculations. |

In our examples using Scalar functions, we will make use of Dual. |

Numeric functions |

Given below is the list of some Numeric Functions with their Descriptions: |

We display the examples using the above functions and their respective output in the tabular form given below: |

Notice in the above table, we have used ‘dual’ as table name. |

String Functions |

Given below is the list of some commonly used STRING FUNCTIONS with their Descriptions: |

We display the examples using the above functions and their respective output in the tabular form given below: |

We display the examples using the above functions and their respective output in the tabular form given below: |

## SQL GROUP BY and HAVING

Aggregate functions (like SUM) often need an added GROUP BY functionality. |

GROUP BY… |

GROUP BY… was added to SQL because aggregate functions (like SUM) return the aggregate of all column values every time they are called, and without the GROUP BY function it was impossible to find the sum for each individual group of column values. |

The syntax for the GROUP BY function is: |

GROUP BY Example |

This “Sales report” Table: |

And This SQL: |

Returns this result: |

The above code is invalid because the column returned is not part of an aggregate. A GROUP BY clause will solve this problem: |

Returns this result: |

HAVING… |

HAVING… was added to SQL because the WHERE keyword could not be used against aggregate functions (like SUM), and without HAVING… it would be impossible to test for result conditions. |

The syntax for the HAVING function is: |

This “Sales report” Table: |

This SQL: |

Returns this result |

follower cum reader.itu yg dimahukan semua orang.kalau setakat tekan butang follow , lepas tu tak datang jengah dah,mcm sia2 aja.jangan salahkan orang yg follow sgt.salahkan juga diri secn.riipedgenualian diberi kepada mereka yg memang follow dan baca secara tetap.