Macromedia Dreamweaver 8 UNLEASHED [Electronic resources] نسخه متنی

Functions

Aside from using operators to manually construct expressions, SQL provides built-in

functions you can use.

TIP

Functions are small blocks of code that can perform operations and return a value.

Functions are available simply by making a call to them and passing the value and/or values on which you want the function to operate.

NOTE

The functions outlined in the next sections represent a generic list of SQL functions. It's important to realize that not all databases support the same functions. Although some databases support similar functions, the way the function is written can differ syntactically from database to database. In the next sections, I'll provide you with a broad list of these functions. It's up to you however, to consult your database documentation for the appropriate syntax variation for the function.

Date and Time Functions

Date and Time functions allow for manipulations using dates and times that are stored within your database. For instance, if you wanted to return all items from the Orders table that were purchased on June 30, 2005, you might write the following code:

SELECT * 
FROM Orders 
WHERE DatePurchased LIKE '6/30/2005'

This code would produce the following results:

If you wanted to find all the orders from the previous month, you could use the DateAdd() function:

SELECT * 
FROM Orders
WHERE DatePurchased > DateAdd(m, -1, Date())

Assuming that the current date was 6/30/05, the results would be as follows:

TIP

Parameters are values that you pass into the function so that it knows what to do or how to return the value.

Also notice that the DateAdd() function accepts parameters. These parameters include the following:

• This parameter specifies which part of the date/time object you want to work with. Typically, you would want to use one of a few values: m for month, w for week, d for day, h for hour, n for minute, s for second, and so forth.

• How much time to add or subtractin the preceding example, I subtracted one month.

• The date you want to use. In the preceding example, I called another functionthe system dateas the date I wanted to use. When you use the Date() function, you are effectively reading the date and time from the computer and passing it in as a value.

There are many other Date and Time functions. Too many, in fact, to cover in this small section. Date and Time functions are among the widely used functions in SQL and are worth the research.

The Count() Function

One of the most obvious functions available is the Count() function. The Count() function is used when you want to perform a count of records. Consider the following data from the Orders table:

You could use the following code to count the number of orders you have taken in a day from the Orders table:

SELECT Count(Quantity) AS NumberOfOrders
FROM Orders

The statement would result in the following:

Notice that you pass in the field name as a parameter in the Count() function. The parameter is evaluated, and a value is returned into a virtual field named NumberOfOrders.

The Sum() Function

Unlike the Count() function that returns a value from a calculation on the number of fields, the Sum() function performs a calculation on data within those fields. If, for instance, you needed to know the total number of items you sold, you could modify the preceding statement to read:

SELECT Sum(Quantity) AS Total 
FROM Orders

The statement would produce the following results:

Rather than simply doing a count on the records, the sum is calculated based on the values within the specified field. Because a total of 5 items were ordered, this value is shown.

The Avg() Function

The Avg() function returns the average of values in specific fields. Consider the following orders in the Orders table:

To get the total average of items being ordered, we might write a statement that resembled the following:

SELECT Avg(Quantity) AS Average 
FROM Orders

The statement would produce the following result:

Of course, this is because the average of the numbers 1, 3, and 5 is 3.

The Min() and Max() Functions

The Min() and Max() functions enable you to find the smallest and largest values of a specific record. To get the minimum quantity ordered, you could write a statement such as this one:

SELECT Min(Quantity) AS Minimum 
FROM Orders

Based on the Orders table data from previous section, the preceding statement produces this result (because the minimum value in the Quantity field is 1):

To receive the maximum value of a record in the database, try this statement:

SELECT MAX(Quantity) AS Maximum 
FROM Orders

Based on the Orders table data from previous section, the preceding statement produces this result (because the maximum value in the Quantity field is 5):