# All About SQL Functions

In the last tutorial we left off with the SUM(column) function. In this article we will cover the remaining Aggregate Functions and the Scalar Functions. We might even make some fat jokes, some mama jokes, and as always, some nerd jokes.

For this tutorial we are going to create a new database. Since I saw the Robot Chicken and Family Guy Star Wars episodes last night, and got like four hours of sleep to boot, we will be working with a Star Wars database. I know, I know. The writing in Star Wars was atrocious. Of course to avoid a lawsuit, we will be using my own version of Star Wars, call SilverWares: The Battle of the Knives and Spoons.

Before I give you the data though, let’s do a little thing I like to call back-story. The story picks up with the Spoons trying to destroy the Knives. To do so, they have created a menacing weapon, a Spork, a Spoon with the built in ability to use…the Fork. Meanwhile, the Knives have uncovered their old Fork Master Gouda, a strange smelly alien whom they hope will teach young Luke Breadslicer the ways of the Fork.

Here is the data for our new database Employees:

 Name Position Salary Side Darth Brooks Sith Lord/Country Singer \$10,000 Spoon Darth Ladel Sith Master \$100,000 Spoon Luke Breadslicer Fork Apprentice \$20,000 Knife Gouda Fork Master \$120,000 Knife Jabba the Cup Bounty Hunter Agent \$500,000 Neutral Bubba Fett Red Neck Alien Bounty Hunter \$1,000 Neutral Dance Solo Pilot for Hire \$40,000 Knife Baklava Hairy Co-Pilot \$25,000 Knife

{mospagebreak title=More Aggregate Functions}

If we want to see what the first record in our Name column contains, we could use the First(column) function.

Select First(Name)

From Employees

This will result in the following:

Darth Brooks

We could also sort the table first if we wanted to, allowing us to get the first alphabetical person in the table:

Select First(Name)

From Employees

Order by Name

This would return:

Baklava

Similarly, we could use the Last(column) Function to retrieve the last piece of data in a column:

Select Last(Position)

From Employees

This would give us:

Hairy Co-Pilot

And again we could also sort the data before choosing the last record:

Select First(Position)

From Employees

Order by Position

Here our result would be:

Bounty Hunter Agent

{mospagebreak title=STDEV}

When you are dealing with a huge amount of data, STDEV can return the Standard Deviation of a column. You have to have a minimum of two values or the value returned will be null.

Here is how it looks in code:

Select STDEV(Salary) As SDSalary

From Employees

The above function takes a sampling of your data instead of the whole list, and returns the standard deviation.

STDEVP

If you want to work with all the data in a column to find the standard deviation, you can use STDEVP instead.

Select STDEVP(Salary) As DevSalary

From Employees

VAR

The VAR Function allows you to find the variance of a set of data from a column. Like STDEV, VAR only works with a sampling and not the whole column. And again, you must have at least two values in the column or a null will be returned.

Select VAR(Salary) As VARSalary

From Employees

VARP

To get a variance using all of the data in a column, use this handy little guy.

Select VARP(Salary) As VARPSalary

From Employees

{mospagebreak title=Scalar Functions}

Scalar functions work against one value and return one value based on the input. Aggregate functions work on a bunch of data and return one result. I understand if you fell asleep during that; I did just writing it.

Here is a table of scalar functions.

 Function Name What it does UCASE(c) Changes a field to Uppercase LCASE(c) Changes a field to lowercase MID(c,start[,end]) Extracts characters from a text field LEN(c) Returns the length of a text field INSTR(c,char) Returns the position of a character in a field LEFT(c,number_of_char) Returns the left part of text RIGHT(c,number_of_char) Returns the right part of text ROUND(c,decimals) Rounds a numeric field to a number of decimals MOD(x,y) Gives the remainder in division NOW() Returns the present system date FORMAT(c,format) Formats a field DATEDIFF(d,date1,date2) For date calculation

UCASE(c)

If you want make all the characters of a selected item uppercase, you can use the UCASE function, like so:

Select UCASE(‘darth ladel’) from Employees;

This will return the following: DARTH LADEL

LCASE(c)

Likewise, you may also wish to force all of the characters to be lowercase.

Select LCASE(‘Darth Brooks’) from Employees;

{mospagebreak title=Mid(c,start[,end]}

The Mid function extracts a certain number of characters from a text field. There are three parts to the Mid function:

• String: The string from which you wish to extract data. If the string is I Hate You and you want the word You, you could start with the word HATE for your string.

• Start: Where you want the start of the extraction to begin. For instance, if you wanted the word YOU from the string I Hate You, you could put your string as Hate and your start as 6 (the space between hate and you counts as one)

• Length: How many characters to return. If you wanted the word YOU, this would be 3.

Select Mid([Luke Breadslicer],6,5) as SampleColumn From Employees;

The above code would create a column named SampleColumn and display the word: Bread.

LEN(c)

The Len function counts the length of the string you specify.

Select Len([Luke Breadslicer]) as SampleColumn From Employees;

The above would create a column named SampleColumn and display the count of the characters in the string Luke Breadslicer, which in this case would be 16 (including the space).

Left(c,number_of_char)

The Left function returns a string, starting from the left and working its way to the right, of however many places you tell it. I will use the string Luke Breadslicer in the example below:

Select Left([Luke Breadslicer], 4) as SampleColumn;

Again, this creates a column named SampleColumn, and displays the string Luke.

Right(c,number_of_char)

As you can probably guess, the Right function works the in same way as the Left function, only from the opposite end. If you specify a number larger than the string, it returns the entire string.

Select Right([Luke], 3) as SampleColumn from Employees;

This would return a column named SampleColumn and display the data: Uke

{mospagebreak title=Round(c,decimals)}

With the Round function you can round off a number to a specific number of decimal places. Here is an example:

Select Round(1.75,1) as SampleColumn from Employees;

This would result in the number 2, as we are rounding up 1 decimal. Had the numbers in the parentheses been (1.76,2), the function would have returned 1.8.

Now()

Now returns the current date and time based on your computer’s system date and time. If these are not properly set, the result will be wrong.

Select Now() As SampleColumn from Employees;

This would return something like: 10/20/2007 8:32:19 PM. "A long, long time ago" is not an option.

DateDiff(d,date1,date2)

This function returns the difference between two dates. The syntax is like this:

Select DateDiff(‘d’,#10/10/2007#,Now()) as SampleColumn from Employees;

This would return the difference between 10/10/2007 and today’s date. There are a lot of options you can use with the DateDiff function, which I do not have the space to go over. However, I will say that you can use an alternate Syntax, which is:

DateDiff ( interval, date1, date2[, firstdayofweek[, firstweekofyear]] )

You can fool around with the Interval, firstdayofweek, and firstweekofyear sections by inserting data from the following tables:

 For the Interval Section What it does YYYY Year Q Quarter M Month Y Day of the Year W Weekday WW Week H Hour N Minute S Second

 For Firstdayofweek Value What it Does vbUseSystem 0 Uses the NLS API VbSunday 1 Sunday VbMonday 2 Monday VbTuesday 3 Tuesday etc

 For Firstweekofyear Value What it Does VbUseSystem 0 Uses the NLS API VbFirstJan1 1 First Week in January VbFirstFourDays 2 Starts with the first week of the year that has a minimum of four days VbFirstFullWeek 3 Starts with the first full week of the year

Well that’s it for this episode. I did leave out two of the more complex functions listed, but don’t worry: I will be doing a future tutorial on even more functions in SQL, and I will cover those there. Hope you enjoyed.

Till then…