SQL: choose columns and rename with AS

Picking columns and naming them

SELECT * is great for a first look, but real queries ask for exactly the columns they need. List them after SELECT, separated by commas:

SELECT name, price FROM snacks;

The columns come back in the order you list them, not the order they live in the table. Want price first? Write price, name.

You are not limited to plain columns either. SQL can do maths on every row as it goes:

SELECT name, price * stock FROM snacks;

That works out, for each snack, how much money is sitting on the shelf. The + - * / operators work like you would expect. One trap: dividing two whole numbers gives a whole number in SQLite, so 7 / 2 is 3, not 3.5. Write 7 / 2.0 if you want the decimal.

The column heading for price * stock comes out as literally price * stock, which is ugly. Fix that with AS, which gives a result column a new name, called an alias:

SELECT name, price * stock AS shelf_value FROM snacks;

Aliases only change the result. The real table is untouched; you are just labelling the answer. Stick to letters, numbers and underscores in alias names and you will never need to quote them.

You can also glue text together with ||:

SELECT name || ' (' || calories || ' kcal)' AS label FROM snacks;

Rule of thumb: ask for what you need. On a table with a million rows and forty columns, SELECT * is the database equivalent of emptying your whole bag on the floor to find your keys.

Tables in this lesson

snacks 7 rows

id	name	price	stock	calories
1	Crisps	1.25	40	160
2	Chocolate bar	1.5	25	230
3	Gummy worms	2.0	12	140
4	Energy drink	2.75	8	110
5	Apple	0.5	30	80
6	Instant noodles	1.75	16	380
7	Mystery sandwich	3.25	2	450

Show the SQL that built them

CREATE TABLE snacks (id INTEGER PRIMARY KEY, name TEXT, price REAL, stock INTEGER, calories INTEGER);
INSERT INTO snacks VALUES
 (1, 'Crisps', 1.25, 40, 160),
 (2, 'Chocolate bar', 1.5, 25, 230),
 (3, 'Gummy worms', 2.0, 12, 140),
 (4, 'Energy drink', 2.75, 8, 110),
 (5, 'Apple', 0.5, 30, 80),
 (6, 'Instant noodles', 1.75, 16, 380),
 (7, 'Mystery sandwich', 3.25, 2, 450);

Try it yourself

Edit it. Break it. Run it again.

-- Pick columns, do maths, rename the result
SELECT name, price, stock, price * stock AS shelf_value
FROM snacks;

Your turn

Type it yourself. That is the whole trick.

1Just the menu

Return only the name and price of every snack. No other columns.

SELECT * FROM snacks;

2Calories per pound

For every snack, return its name and a column named kcal_per_pound that is calories divided by price. (It is a terrible way to shop and a great way to practise.)

SELECT name
FROM snacks;