SQL - Calculated Fields and Functions

Customer Table

To create a new database in Microsoft Access:

• Open Microsoft Access and click Blank Database under the New Blank Database heading. 
• Name the database in the File Name box on the right side of the screen. Click the folder icon to place the database where you want. 
• Afterwards, click Create to save the database you just created.

To create a query using Microsoft Access 2010:

• Switch to SQL View by clicking Create from the menu running across the top of the screen.
• Click the Query Design button, causing the Show Table dialog box to appear. 
• Click Close in the dialog box without selecting a table. 
• Locate the View drop-down button near the top left. 
• Click the down arrow and select SQL View. This is the place to type the SQL script. 
• To execute the script in SQL View, click on the Run button.

Concatenating Data

concatenation (&) symbol: used to bring two columns together to form a single column.

We want to create address labels from the above table. First, we need to join the FirstName and the LastName together separating them with a space. Example:

SELECT FirstName&' '&LastName
FROM Customer;

Concatenate First and Last Name

The second query displays the Address column. Example:

Display Address

SELECT Address
FROM Customer;















The third query merges City, State, and ZipCode.

SELECT RTrim(City)&','&RTRIM(State)&' '&ZipCode 
FROM Customer;

Concatenate City, State, and ZipCode

note: the above three queries could have been written as one query.

note: The column heading defaulted to Expr1000 because I didn't indicate what to display the column heading AS. Be sure to use the AS command to specify how to display the fields.

note: SQL Server uses the plus sign (+) for concatenation.

note: Oracle uses the double pipe (||) symbols for concatenation.

note: Microsoft SQL Server rules state that you cannot concatenate a column with an INTEGER datatype. You must first convert the datatype to a text datatype, temporarily, using the CAST() function. Two arguments are required, column name and the datatype to convert to. The AS keyword specifies the datatype. Example:

CAST (column name AS datatype)

note: Because of datatype differences in some versions of Access, the following SQL script might need to be used for concatenation on the above first and third query:

SELECT RTRIM (FirstName)&' ' &LastName
FROM Customer;

SELECT RTRIM (City)& ',' & RTRIM (State)& ' ' &ZipCode
FROM Customer;

note: MYSQL does not support the || and & operators to concatenate strings. Instead, use the CONCAT () function. The CONCAT () function uses any number of arguments:

SELECT CONCAT (FirstName," ", LastName)
FROM Customer;

How to Create Calculated Fields

alias: A name substituted for the actual name of a field; the AS keyword is used to assign an alias.

calculated fields: These fields allow you to perform mathematical calculations on the retrieved data.

Create a Service Plan Table:

Service Plan Table

Create a query to find the difference between two prices using calculated fields and display result as new field:

SELECT SvcID, SvcName, SvcPrice, DiscountPrice,

(SvcPrice - DiscountPrice) AS Difference

FROM [Service Plans]

ORDER BY SvcName;

Query for Service Plan Price Difference

Create a query to increase prices on SvcID 1 and 2 using calculated fields and display a new column with the price increase:

SELECT SvcID, SvcName, SvcPrice, 

(SvcPrice + 1.25) AS PriceIncrease

FROM [Service Plans]

WHERE SvcID IN (1,2)

ORDER BY SvcName;

Query for Svc Plan Price Increase of $1.25 for SvcID 1 and 2

Functions

While most SQL statements are portable from one DBMS to another, SQL functions, for the most part, are not portable. The function will need to be rewritten for the particular DBMS.

In SQL, there are six types of functions:

1. aggregate
2. arithmetic
3. date and time
4. character
5. conversion
6. miscellaneous

Aggregate Functions

Aggregate functions return a single value that are based on the values in a column. In other words, summarize the data.

AVG () Returns the average of a column.

COUNT () Counts the number of rows in a column.

MAX () Will find the highest number in a column.

MIN () Will find the lowest number in a column.

SUM () Will sum the values in a column.

Arithmetic Functions

Arithmetic functions perform mathematical manipulations of numeric data.

EXP () Returns a value of e (exponent) raised to the power of a given number.

MOD () Returns the remainder of a division.

SQRT () Returns the square root.

Date and Time Functions

Date and Time functions manipulate data based on the date and time.

ADD_MONTHS () Adds a number of months to the date.

LAST_DAY () Returns the last day of a month.

SYSDATE () Returns computer system date.

Character Functions

Character functions manipulate character data.

LTRIM () Removes extra spaces on the left of a field.

REPLACE () Replaces specified data in a field.

RTRIM () Removes extra spaces on the right of a field.

Conversion Functions

Conversion functions convert one type of data to another type of data.

TO_CHAR () Converts a number to a character.

TO_NUMBER () Converts characters to a number.

Miscellaneous Functions

Miscellaneous functions manipulate character and numeric data.

GREATEST () Finds the largest value in a list of values.

LEAST () Finds the lowest value in a list of values.

LEN () Returns a string length.

note: Microsoft Access does not support all of the above functions.

Create a query to remove extra spaces from the left of a PriceIncrease from the [Service Plans] table using the LTRIM () function:

SELECT SvcID, SvcName, SvcPrice, LTRIM (SvcPrice + 1.25) AS

PriceIncrease

FROM [Service Plans]

WHERE SvcID IN (1,2)

ORDER BY SvcName;

Query to LTRIM spaces from left of Price Increase

Note: LTRIM () is used to remove spaces in a text field, so the dollar sign ($) does not exists in the actual field data, so it is deleted along with the extra spaces.

Create a query to calculate the average price for service plans and display as AvgSvcPlanPrice:

SELECT AVG (SvcPrice) AS AvgSvcPlanPrice

FROM [Service Plans]

WHERE NOT SvcID IN (1,2);

Query for Average Price on Service Plan

Create a query to count all customers in the Customer table and display as TotalCustomers using the COUNT () function:

SELECT COUNT (*) AS TotalCustomers

FROM Customer;

Query to COUNT Total Customers

The following link explains many functions used in SQL:
http://www.sir.com.au/help/sql_function.htm

The following link describes how to get the most out of using Microsoft Access by using SQL:
SQL for Microsoft Access

The following link is good for SQL beginners:
The Language of SQL