SELECT Basics

SELECT is how you read data back out of a table. This lesson covers the core shape every query builds on: picking specific columns, grabbing everything with *, filtering rows with WHERE, renaming a result column with AS, and removing duplicate rows with DISTINCT.

Selecting Specific Columns

Name the columns you want, separated by commas, right after SELECT. Only those columns come back, in the order you listed them — not necessarily the order they appear in the table.

SELECT title, difficulty FROM recipes;

-- Creamy Garlic Pasta        | easy
-- Slow-Roasted Tomato Soup   | medium

SELECT * — Every Column

* is shorthand for "every column, in the table's own order." It's convenient while exploring data at the sqlite3 prompt, but in application code it's usually better to name columns explicitly — that way your code doesn't silently break (or silently start reading the wrong column) when someone adds a column to the table later.

SELECT * FROM recipes;

Filtering with WHERE

WHERE keeps only the rows that satisfy a condition. It's evaluated per row, before the results are returned.

SELECT title, prep_minutes FROM recipes WHERE difficulty = 'easy';

SELECT title FROM recipes WHERE prep_minutes <= 30;

SELECT username FROM users WHERE username = 'chef_amara' OR username = 'sourdough_sam';

Aliasing Columns with AS

AS gives a result column a different name in the output, without changing anything in the underlying table. It's especially useful for computed columns, where the real column name doesn't apply.

SELECT title AS recipe_name, prep_minutes AS minutes_to_prep
FROM recipes;

-- Works without AS too: SQLite allows "title recipe_name"
-- but writing AS explicitly reads more clearly
SELECT prep_minutes / 60.0 AS prep_hours FROM recipes;

DISTINCT — Removing Duplicate Rows

DISTINCT collapses duplicate rows in the result down to one. It applies to the whole row being selected, not just the first column — two rows only collapse into one if every selected column matches.

-- Without DISTINCT, 'medium' would appear once per matching row
SELECT DISTINCT difficulty FROM recipes;

-- easy
-- medium

-- Only collapses rows where BOTH columns match
SELECT DISTINCT difficulty, user_id FROM recipes;

Putting It Together

A query that combines all of it — picking columns, filtering, aliasing, and de-duplicating:

SELECT DISTINCT difficulty AS level
FROM recipes
WHERE prep_minutes < 60;

This reads as: look at every recipe that takes under an hour to prep, list its difficulty under the friendlier name level, and don't repeat a difficulty value that's already been shown.