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…

[gp-comments width="770" linklove="off" ]

chat