What are SQL Functions? - Use & Examples

An error occurred trying to load this video.

Try refreshing the page, or contact customer support.

Coming up next: SQL: NVL Function

You're on a roll. Keep up the good work!

Take Quiz Watch Next Lesson
 Replay
Your next lesson will play in 10 seconds
  • 0:04 SQL Functions: What Are They?
  • 0:57 Classification & Pupose of SQL
  • 2:02 Some SQL Function Examples
  • 4:59 User-Defined Functions
  • 6:12 Lesson Summary
Save Save Save

Want to watch this again later?

Log in or sign up to add this lesson to a Custom Course.

Log in or Sign up

Timeline
Autoplay
Autoplay
Speed Speed
Lesson Transcript
Instructor: Ee Teik OH

Ee Teik OH has 25 years of teaching experience at different colleges and universities and a bachelor of applied science in computer science & technology.

All major SQL database implementations like Microsoft SQL, Oracle and mySQL provide a complete suite of built-in functions as well as the facility for users to create their own functions. The contents of this lesson are applicable to all SQL systems.

SQL Functions: What Are They?

SQL functions are simply sub-programs, which are commonly used and re-used throughout SQL database applications for processing or manipulating data. All SQL database systems have DDL (data definition language) and DML (data manipulation language) tools to support the creation and maintenance of databases.

DDL is responsible for tasks related to database creation, modification, and maintenance, while DML is responsible for tasks related to the manipulation of data, which essentially means DML is the programming interface of the database. The DML, like other programming languages, consists of instructions which are used to process or compute data within the database. The DML also supports functions, and these are referred to as SQL functions. SQL functions are small programs that may have zero or more input parameters but can return only one value.

Classification & Purpose of SQL

There are many ways to classify SQL functions. The easiest is to divide them into built-in functions and user-defined functions. The built-in functions are standard functions which are already provided by the SQL database system, and most of these functions are defined clearly in the ANSI (American National Standards Institute) SQL standards. These built-in functions can be divided further into aggregate functions or scalar functions. Meanwhile, user-defined functions are functions which are created by the user for a specific purpose. The user-defined functions can either return a single value or a set of values.

There are many advantages of using functions. The most common ones are listed here:

  1. A function needs to be written only once and can be reused multiple times. This saves time and effort and supports modular programming.
  2. Functions improve performance and efficiency of the database. SQL functions are compiled and cached before use.
  3. Complex programming logic can be decomposed into a number of smaller and simpler functions, thus making it easier to understand and maintain.

Some SQL Function Examples

As mentioned earlier, all built-in SQL functions are a part of the SQL database. These can be divided into the scalar and aggregate functions. The scalar functions act on the input values and can return text, numeric, or time and date values. The aggregate functions act on field/fields in the database and return single values (either strings or numerical values) after processing. Functions can also be classified according to the type of data returned. The diagram here shows some common built-in SQL functions.


Diagram06


We'll be using the table named 'EmpSalary' shown here for all the examples discussed.


Diagram01


Consider the SQL statement appearing below:


SELECT employeeNo, FirstName, len(FirstName) FROM EmpSalary;


Here, the len function acts on each row in the table and returns the length of the firstName field in each row.


Diagram07


Next, let's consider the SQL statement here which is definitely more complex than the first example:


SELECT employeeNo, concat(trim(LastName),', ',trim(FirstName)) as fullName, len(concat(trim(LastName),', ',trim(FirstName))) as lengthOfFullName from EmpSalary;


In this SQL statement, there are three built-in functions used: concat, trim, and len. The output after execution is as shown in the diagram here.


Diagram02


The key point to note here is that every function acts on each row of data, and a function can contain another function. The trim function removes trailing blanks from any field. The concat function then concatenates the firstName and lastName fields, inserting a comma in between to produce the complete name. The len function gives the length of the full name, including the comma and blank space in between firstName and lastName. Here the concat, trim, and len functions act on each row of data in the table and return new values and fields as shown. If the number of records in this table is increased from 10 to 100, these functions will run 100 times and return the corresponding number of values.

Now, let's take an example of aggregate functions. As the name suggests, aggregate functions acts on an aggregate or group of records in the table. An SQL statement using aggregate functions is as shown here:


SELECT department, count(department) as noEmployees, sum(salary) as totalDeptSal, avg(salary) as avgDeptSal from empSalary group by department;


The result after execution is as shown in the diagram here.


Diagram03


To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support