Module 2: Picking rows and columns · Lesson 2.1 · about 25 min
SELECT: choosing columns
Pick the columns you want, rename them, work out new ones with arithmetic, and remove repeated rows with DISTINCT, all on a live store database.
Module 2 · about 25 minutes · read first: 1.4. You’ll be able to choose and rename columns, work out new columns from old ones, and remove repeated rows.
In everyday terms: photocopying a wide spreadsheet with most columns folded under. Every row is still on the copy, just narrower; you can also write a new column in the margin, like “price with tax”.
1. Why this matters
Real tables have dozens of columns and most of a report needs only a few. Choosing columns, naming them clearly and working out new ones (margin, full name, price with tax) is the first thing you do in almost every query. Clean column names also matter downstream: a Power BI or Tableau report shows them to the people reading it.
2. Choose columns by name
After SELECT, list the columns you want, separated by commas, in the order you want them shown.
-- try
SELECT product_name, unit_price
FROM products
Press Show how it runs. FROM brings in every column of products; at the SELECT step all but two are faded out. Rows are not touched: there are still 61.
Change the order to unit_price, product_name and run again: the columns swap. You can also list a column twice, though there is rarely a reason to.
3. SELECT * and when not to use it
* means every column. It is perfect for a quick look, but in saved queries list the columns: the query is easier to read, faster on wide tables, and doesn’t change shape when someone adds a column to the table.
-- try
SELECT * FROM categories
4. Work out new columns
A column in SELECT can be any expression: arithmetic with + - * /, a function like ROUND(x, 2), or text joined with ||.
-- try
SELECT product_name,
unit_price,
unit_cost,
unit_price - unit_cost AS margin,
ROUND(unit_price * 1.13, 2) AS price_with_tax
FROM products
WHERE category_id = 7
AS margin is an alias: a name for the result column. Without it the column would be called unit_price - unit_cost. Aliases exist only in the answer; the products table is unchanged. If an alias has a space, put it in double quotes: AS "price with tax".
Joining text uses || (two vertical bars):
-- try
SELECT customer_id,
first_name || ' ' || last_name AS full_name,
city
FROM customers
LIMIT 5
SQL Server uses + or CONCAT(first_name, ' ', last_name); PostgreSQL and SQLite accept both || and CONCAT (SQLite from version 3.44).
- SELECT a, b
- Keep columns a and b, in that order.
- *
- Every column. Fine for a quick look; list columns in saved queries.
- Expression
- A worked-out value, such as unit_price - unit_cost or ROUND(x, 2).
- AS name
- An alias: the name of the result column. The table is not renamed.
- ||
- Joins pieces of text end to end.
- DISTINCT
- Keeps each different row (or pair of values) once.
- Physical meaning
- SELECT never removes or adds rows (DISTINCT aside): it makes every row narrower or wider. New columns are calculated row by row and exist only in the answer.
“SELECT DISTINCT a, b gives the different values of a and the different values of b.” DISTINCT works on the whole row you chose. With two columns it keeps each different pair, so you can see the same channel several times, once for each status it comes with.
Watch SELECT with a new column and then DISTINCT, one step at a time:
| product | unit_price | unit_cost | category |
|---|---|---|---|
| Duvet | 89.99 | 45.44 | Bedding |
| Pillow 2 pack | 29.99 | 13.80 | Bedding |
| Kettle | 44.99 | 24.00 | Kitchen |
| product | margin |
|---|---|
| Duvet | 44.55 |
| Pillow 2 pack | 16.19 |
| Kettle | 20.99 |
| category |
|---|
| Bedding |
| Bedding |
| Kitchen |
| category |
|---|
| Bedding |
| Kitchen |
The picture uses simplified prices; the live boxes use Northbay’s real ones, and their Show how it runs button animates each step.
5. A column that is the same on every row
You can SELECT a fixed value, called a literal. It repeats on every row, which is handy for labelling results.
-- try
SELECT 'Bedding' AS shelf, product_name, unit_price
FROM products
WHERE category_id = 7
6. DISTINCT: each different row once
SELECT DISTINCT removes rows that are exact repeats of each other in the columns you chose.
-- try
SELECT DISTINCT department
FROM categories
Press Show how it runs. Ten category rows go in; at the DISTINCT step the repeats (five Grocery rows, three Home rows) collapse, leaving 4.
With two columns, DISTINCT keeps each different pair:
-- try
SELECT DISTINCT channel, status
FROM orders
Four pairs: every channel has some completed and some cancelled orders.
Maths and principle
Principle: choosing columns is projection, written π. It keeps some columns of every row and throws the rest away. Pure projection on a set also removes repeats; SQL’s SELECT does not unless you add DISTINCT, because SQL tables are bags (multisets) where a row may appear more than once.
Equation: SELECT department FROM categories is the bag πdepartment(categories) with 10 elements. SELECT DISTINCT department is the set δ(πdepartment(categories)) = {Grocery, Home, Kids, Tech}, with 4. A computed column is a function applied to each row: margin(r) = r.unit_price − r.unit_cost.
Analogy: photocopying a spreadsheet with some columns folded under. Every row is still there, just narrower. DISTINCT is crossing out lines that look identical on the copy. It breaks down a little because the database doesn’t really fold: it never reads columns it doesn’t need.
Try it: how many rows does SELECT DISTINCT province FROM stores return? (5: ON, MB, AB, BC, and NULL for the online store. DISTINCT treats NULLs as one value.)
7. Worked example
Question: for each Bedding product, what is the profit on one unit and the margin as a percentage of the price?
- Bedding is category 7, so filter
WHERE category_id = 7(WHERE gets its own lesson next). - Profit per unit =
unit_price - unit_cost. For the Duvet: 89.99 − 45.44 = 44.55. - Margin % = profit / price × 100. For the Duvet: 44.55 / 89.99 × 100 = 49.5.
-- try
SELECT product_name,
ROUND(unit_price - unit_cost, 2) AS profit,
ROUND((unit_price - unit_cost) / unit_price * 100, 1) AS margin_pct
FROM products
WHERE category_id = 7
The Pillow 2 pack has the highest margin (54%), even though the Duvet makes the most dollars per unit. Two columns, two different stories.
8. Practice
-- exercise: List every store's name and size (size_sqft), with the size column renamed to square_feet.
-- hint: SELECT store_name, size_sqft AS square_feet FROM stores
SELECT * FROM stores
-- answer:
SELECT store_name, size_sqft AS square_feet
FROM stores
-- exercise: Show each Electronics product (category_id = 10) with its name and its price after a 25% discount, rounded to 2 decimals.
-- hint: 25% off means multiply by 0.75. ROUND(x, 2) rounds.
SELECT product_name, unit_price
FROM products
WHERE category_id = 10
-- answer:
SELECT product_name, ROUND(unit_price * 0.75, 2) AS sale_price
FROM products
WHERE category_id = 10
-- exercise: List each different brand Northbay sells, once each.
-- hint: SELECT DISTINCT brand ...
SELECT brand FROM products
-- answer:
SELECT DISTINCT brand FROM products
- List the columns you want after SELECT, in the order you want them.
- Any expression can be a column; name it with AS.
- Aliases and worked-out columns exist only in the answer, never in the table.
- SELECT keeps every row; only DISTINCT removes repeats.
- DISTINCT works on the whole chosen row, so two columns give different pairs.
New words: projection, expression, alias, literal, DISTINCT, bag.
Check yourself
Sources
- SQLite documentation, SELECT (result columns and DISTINCT) https://www.sqlite.org/lang_select.html
- PostgreSQL documentation, Select Lists https://www.postgresql.org/docs/current/queries-select-lists.html
- Microsoft Learn, SELECT clause (Transact-SQL) https://learn.microsoft.com/en-us/sql/t-sql/queries/select-clause-transact-sql
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. Northbay Retail is a made-up company; its data is generated in your browser. Queries run on your device and nothing you type is sent anywhere.