Calculated columns and AS

Module 1 of 9 SQLite in your browser

Lesson concept What you'll do

1 / 4

Work out a new column from the others, and give it a name.

Write the sum in the SELECT list, like a formula, and name it with AS.

Next: Working out a value

On your own New data, same idea

For every book, show its title and how many copies are on the shelf (copies minus on_loan), in a column called available.

The database

9 rows

book_id	title	author	genre	year	pages	copies	on_loan
1	Pride and Prejudice	Jane Austen	Romance	1813	432	2	2
2	The Great Gatsby	F. Scott Fitzgerald	Fiction	1925	180	6	6
3	Dracula	Bram Stoker	Horror	1897	418	5	5
4	Mockingjay	Suzanne Collins	Science Fiction	2010	390	6	6
5	The Time Machine	H.G. Wells	Science Fiction	1895	118	1	0
6	Angels & Demons	Dan Brown	Thriller	2000	616	6	2
7	Charlotte's Web	E.B. White	Children	1952	184	6	3
8	The Fault in Our Stars	John Green	Romance	2012	313	6	4
9	The Fellowship of the Ring	J.R.R. Tolkien	Fantasy	1954	423	1	1

Your query Ctrl + Enter runs it

Hint: click the green edit button on the board, or type it yourself

Query output Ready to run

Run your query to see the result table.

Press the green Run button or Ctrl+Enter.

Study notes

How AS works

Can a column be worked out?

Yes. Anything after SELECT can be a calculation: copies - on_loan, pages / 30, 2026 - year. It's worked out for every row, like a formula filled down a column in Excel.
Which maths signs work?

+ add, - subtract, * multiply, / divide. Brackets work as in maths: (copies - on_loan) * 2.
What does AS do?

copies - on_loan AS available
AS names a column in the result. Without it the heading is the sum itself, which is hard to read and hard to use later.
Does it change the table?

No. A calculated column exists only in the result. To store values in the table you'd use UPDATE, which comes later.
How is this like Excel?

In Excel you'd type =G2-H2 in a new column and fill it down. In SQL you write the sum once, and it's worked out for every row.

Other databases

The lessons run on SQLite. Most of SQL is the same everywhere; these are the places where this lesson's SQL differs.

What does 7 / 2 give?
Database	How	Note
SQLite (these lessons)	`3`	Dividing two whole numbers gives a whole number. Write 7.0 / 2 for 3.5.
PostgreSQL	`3`	Whole numbers give a whole number. Write 7.0 / 2 or 7::numeric / 2 for 3.5.
MySQL / MariaDB	`3.5000`	/ always gives a decimal. DIV gives the whole-number answer.
SQL Server	`3`	Whole numbers give a whole number. Write 7.0 / 2 for 3.5.
Oracle	`3.5`	Numbers are decimals, so / gives 3.5. TRUNC(7 / 2) gives 3.
Sybase ASE	`3`	Whole numbers give a whole number. Write 7.0 / 2 for 3.5.

Using the AS name in WHERE
Database	How	Note
SQLite (these lessons)	`WHERE available > 0`	Allowed: SQLite looks the name up in SELECT. Other databases refuse it, so repeat the calculation instead.
PostgreSQL	`Error`	WHERE runs before SELECT. Write WHERE copies - on_loan > 0.
MySQL / MariaDB	`Error`	Unknown column. Repeat the calculation in WHERE.
SQL Server	`Error`	Invalid column name. Repeat the calculation in WHERE.
Oracle	`Error`	Invalid identifier. Repeat the calculation in WHERE.
Sybase ASE	`Error`	Repeat the calculation in WHERE.

A name with a space in it
Database	How	Note
SQLite (these lessons)	`AS "On the shelf"`	Double quotes. [ ] and backticks are accepted too.
PostgreSQL	`AS "On the shelf"`	Double quotes. Without them, names are folded to lower case.
MySQL / MariaDB	AS `On the shelf`	Backticks (double quotes only with the ANSI_QUOTES mode).
SQL Server	`AS [On the shelf]`	Square brackets, or double quotes.
Oracle	`AS "On the shelf"`	Double quotes. Without them, names are folded to capitals.
Sybase ASE	`AS "On the shelf"`	Double quotes need quoted_identifier on. Easiest: no spaces (on_shelf).

Know someone fighting with a spreadsheet? Send them this page.