Select Statement Expressions

You can use the SELECT statement expressions to perform calculations on your table’s columns. These calculations are called expressions. In this lesson we are going to look at expressions to perform simple calculations. Keep in mind this is just the beginning! There are many built-in functions you can use to build more complex expressions, but for today’s lesson let’s keep it simple and focus on some basic arithmetic.

All of the examples in this tutorial are based on PizzaDB. You can get the script to build the PizzaDB here.

In this example we’re going to work with the Product table to calculate new prices.

What would prices be, if we increased them by a 1 dollar?

/* Answer */
select ProductID, ProductName, Price, Price+1
from Product

For this example you can see we use an expression as our last column. It takes the price adds one dollar to the amount. Since this is in the query’s column list, it performs this calculation for every row in the result.

You may have notice there is no meaningful column name for our calculation. However, we can add one using a column alias. This is just a fancy way of saying rename the column.

Rename the calculated column to NewPrice so it is easier to read and use.

/* Answer */
select ProductID, ProductName, Price, Price+1 as NewPrice
from Product

You can see the alias NewPrice is placed after the column. You can use the optional keyword “as” to separate them.

Here is the statement with “as”

select ProductID, ProductName, Price, Price+1 as NewPrice
from Product

Here is the statement without it.

select ProductID, ProductName, Price, Price+1 NewPrice
from Product

Either variation works.

Here is the syntax for using an expression in the SELECT Statement

SELECT column1, column2, expression as alias, ..
FROM TableName

This is a simplified view of the syntax, so there are a couple of items you should know:

• First, one or more expressions can be in the select statement.
• The expressions can be in any order, infact, they can be at the beginning of the column list.
• You can alias columns just as you can an expression.

You can use these operators in your expressions:

in this example let use several expressions to show price differences resulting from a 5% increase in prices.

We’ll add two expressions:

• NewPrice = Price * 1.05 — this is a 5% increase
• PriceDifference = Price * .05 — this is the difference

/* Answer */
select ProductID, ProductName, Price,
    Price * 1.05 as NewPrice,
    (Price * .05) PriceDifference
from Product

Did you notice I place the expressions on a separate line? That’s to make it easier to read. As long as the columns and expression are separated by commas (,) , you place on the same or separate lines; it’s your choice!

Write a query to list the ProductID, ProductName, Price, and NewPrice

For the Price increase use the following formula: (1 + .08) * price

/* Answer */
select ProductID, ProductName, Price,
    (1 + .08) * Price NewPrice
from Product