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.

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

SQL PizzaDB Product Table for Select Statement Expressions examples

Example

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

select ProductID, ProductName, Price, Price+1 from Product
/* 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.

Example

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

select ProductID, ProductName, Price, Price+1 as NewPrice from Product
/* 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.

Syntax

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:

OperatorDescriptionExample
+Additioncolumn1 + column2
Subtractioncolumn1 – column2
*Multiplicationcolumn1 * column2
/Divisioncolumn1 / column2
( )Parenthesis to establish precedence(1 + column1) / column2

Using Multiple Expressions in Select

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
select ProductID, ProductName, Price, Price * 1.05 as NewPrice, (Price * .05) PriceDifference from Product
/* 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!

Exercise

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