SQL Foundations Lesson 2 of 7

Choosing columns in SQL

25 min, 5 exercises

Builds on lesson 1. Start at lesson 1

Learn

Choosing columns and maths

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;

Learn

Naming results with AS

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;

Learn

Joining text and choosing well

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.

Try

Run it: shelf values

The query in the editor runs against this lesson's tables (open Tables above the editor to see them). Run it, change the WHERE or the columns, run it again.

Tables snacks, tracks
snacks 7 rows, 5 columns
idnamepricestockcalories
1Crisps1.2540160
2Chocolate bar1.525230
3Gummy worms2.012140
4Energy drink2.758110
5Apple0.53080
6Instant noodles1.7516380
7Mystery sandwich3.252450
tracks 5 rows, 4 columns
idtitleartistseconds
1Glow UpKai Neon166
2Homework Can WaitLofi Owl152
3Thunder LunchboxGravel254
4The Final BossPixel Choir176
5The Long GoodbyeVelvet Static302
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);
CREATE TABLE tracks (id INTEGER PRIMARY KEY, title TEXT, artist TEXT, seconds INTEGER);
INSERT INTO tracks VALUES
 (1, 'Glow Up', 'Kai Neon', 166),
 (2, 'Homework Can Wait', 'Lofi Owl', 152),
 (3, 'Thunder Lunchbox', 'Gravel', 254),
 (4, 'The Final Boss', 'Pixel Choir', 176),
 (5, 'The Long Goodbye', 'Velvet Static', 302);
example.sql to run
-- Pick columns, do maths, rename the result
SELECT name, price, stock, price * stock AS shelf_value
FROM snacks;

Practice

Select name and price

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

exercise_1.sql to run
SELECT * FROM snacks;

Practice

Calories 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.)

exercise_2.sql to run
SELECT name
FROM snacks;

PracticePredict the output

Packs of five

The shop sells some snacks in packs of five. What does this query return? Type one row per line, values separated by |.

exercise_3.sql
SELECT name, stock / 5 AS packs
FROM snacks;

Practice

Build a shelf label

Make a label for every snack, in a single column named label, that reads like Crisps: 40 left.

exercise_4.sql to run
SELECT name, stock
FROM snacks;

Your turn

A playlist in minutes

The lesson's database has a second table, tracks, with each song's length in seconds. For a playlist screen, return one column song that reads like Kai Neon: Glow Up, and a column minutes with the length in minutes as a decimal (166 seconds is 2.766...).

On your own. Hints open after your first check.

exercise_5.sql to run
-- Your query here
SQLite