This lesson's interactive features are locked, please to keep using them

SQL Functions

SQL is a programming language, and like nearly all programming languages, it supports functions. We can use functions and aliases to calculate new columns in a query. This is similar to how you might use formulas in Excel.

A calculated column is a new column that doesn't exist in the original table but is created on the fly when you run a query.

The IIF Function

In SQLite, the IIF function works like a ternary expression. For example:

IIF(carA > carB, 'Car A is bigger', 'Car B is bigger')

If carA is greater than carB, this statement evaluates to the string 'Car A is bigger'. Otherwise, it evaluates to 'Car B is bigger'.

Here's how we can use IIF() and a directive alias to add a new calculated column to our result set:

SELECT quantity,
  IIF(quantity < 10, 'Order more', 'In Stock') AS directive
FROM products;

Assignment

We need to look through CashPal's transaction data and determine whether or not any of the transactions need to be audited.

Return all the data from the transactions table, and add an extra column at the end with the alias audit.

If a row's was_successful field is true, the audit field should say 'No action required'.
If a row's was_successful field is false, the audit field should say 'Perform an audit'.

Tip

Some comparison operators in SQL:

=: Equal to
<: Less than
>: Greater than