Selecting Data

TL;DR — SELECT column FROM table retrieves specific columns; SELECT * returns all of them. Use AS to rename columns in results, arithmetic expressions to compute values inline, DISTINCT to remove duplicates, and LIMIT n to cap the number of rows returned. Every SQL query you write starts with SELECT, so getting these basics right pays off in every lesson that follows.

What You'll Learn

By the end of this lesson you will write a single query that turns a raw products table into a readable report: named columns instead of a star, friendly headings, a value computed on the fly, and a defined row order — and you will be able to say which part of that query controls which part of the result. The skill underneath is choosing your own answer's shape rather than accepting whatever the table happens to hand you.

This lesson has no milestone of its own to hand the warehouse project — it is the one that makes the later milestones writable. Every report the capstone eventually needs, from the category summary to the order listing, is a SELECT with a column list, an alias, an expression and an ORDER BY, and those four pieces are what you assemble here, before any of them have to deal with two tables at once. You arrive able to build a table, put rows in it and read them back (Introduction to SQL); what is new is controlling exactly what comes back and in what order.

In this lesson, we'll dive deep into the SELECT statement - the most fundamental and frequently used SQL command. You'll learn how to retrieve exactly the data you need from your database.

The SELECT Statement

The SELECT statement is used to query data from a database. At its simplest, it retrieves columns from a table.

Basic Syntax

SELECT column1, column2, column3
FROM table_name;

That is the shape. Here it is doing something — open the fence below with its Open in Playground pill, press Run Code, and read the three results it prints:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department TEXT NOT NULL,
    salary INTEGER NOT NULL
);

INSERT INTO employees VALUES
    (1, 'John Doe', 'Engineering', 60000),
    (2, 'Jane Smith', 'Marketing', 65000),
    (3, 'Mike Johnson', 'Engineering', 55000),
    (4, 'Sarah Williams', 'Sales', 70000);

-- The asterisk means every column
SELECT * FROM employees ORDER BY id;

-- Naming columns gives you only those, in the order you name them
SELECT name, salary FROM employees ORDER BY id;

SELECT salary, name FROM employees ORDER BY id;

Read the output from the top. The CREATE TABLE printed nothing, the INSERT reported 4 row(s) affected for the four rows it carried, and then each SELECT printed a block of its own: a header line naming the columns joined by |, a rule of dashes exactly as long as that header, and then one line per row. The three blocks follow each other with no blank line between them, so the way to tell where one result ends and the next begins is to spot the next header.

Now compare the three blocks against each other. The first has four columns because the star asked for all of them; the second has two because you named two; the third has the same two values in the opposite order, because a column list is an ordered list and the result follows it. Nothing about the table changed between them — only the question did.

Selecting Specific Columns

Naming the columns you need gives you a result whose shape you control. A star returns whatever columns the table happens to have on the day you run it, so a query written with a star quietly changes its own output the moment someone adds a column. Add a phone column to that table tomorrow and every SELECT * report silently grows a column, while every report that named its columns keeps working exactly as before.

Column Aliases

Aliases let you rename columns in your result set, making output more readable:

-- Create a sample employees table
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    salary INTEGER,
    department TEXT
);

-- Insert sample data
INSERT INTO employees (id, first_name, last_name, salary, department)
VALUES
    (1, 'John', 'Doe', 60000, 'Engineering'),
    (2, 'Jane', 'Smith', 65000, 'Marketing'),
    (3, 'Mike', 'Johnson', 55000, 'Engineering'),
    (4, 'Sarah', 'Williams', 70000, 'Sales');

-- Use aliases to create friendly column names
SELECT
    first_name || ' ' || last_name AS full_name,
    salary AS annual_salary,
    department AS dept
FROM employees;

Expressions and Calculations

SQL can perform calculations directly in your queries:

-- Create a products table
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price DECIMAL(10, 2),
    quantity INTEGER
);

-- Insert products
INSERT INTO products (id, name, price, quantity)
VALUES
    (1, 'Laptop', 999.99, 10),
    (2, 'Mouse', 24.99, 50),
    (3, 'Keyboard', 79.99, 30),
    (4, 'Monitor', 299.99, 15);

-- Calculate total inventory value
SELECT
    name,
    price,
    quantity,
    price * quantity AS total_value
FROM products
ORDER BY name;

Two things are worth noticing in that result. The computed column is a real column of the answer even though no such column exists in the table — price * quantity is evaluated once per row, and AS total_value is what gives the resulting column a name. And the numbers come back in this engine's own formatting rather than as you wrote them: a price stored as 24.99 prints as 24.99, but one written 25.00 would print as 25.0, because a trailing zero carries no information in a floating-point number.

Modify it twice, and predict before each Run. First, change the computed column to price * quantity * 0.9 AS discounted_value and predict what happens to each figure — every one drops by a tenth, but exactly one of the four comes back with a long tail of decimal digits instead of a tidy two-decimal price. Predict which row that will be and why, then Run and read the actual value: neither 0.9 nor that particular product is exactly representable in binary floating point, and the tail is the engine showing you the stored number rather than the one you meant. That is the reason money is kept in whole cents everywhere it matters. Then remove ORDER BY name entirely and Run again: the rows may well come back in the same order, and that is precisely the trap the next section is about.

DISTINCT Keyword

Remove duplicate values from your results:

-- Create an orders table
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_name TEXT,
    product TEXT,
    amount DECIMAL(10, 2)
);

-- Insert sample orders
INSERT INTO orders (id, customer_name, product, amount)
VALUES
    (1, 'Alice', 'Laptop', 999.99),
    (2, 'Bob', 'Mouse', 24.99),
    (3, 'Alice', 'Keyboard', 79.99),
    (4, 'Charlie', 'Monitor', 299.99),
    (5, 'Bob', 'Laptop', 999.99);

-- Get unique customer names
SELECT DISTINCT customer_name FROM orders ORDER BY customer_name;

-- Get all orders (with duplicates)
SELECT customer_name FROM orders ORDER BY id;

The word to hold onto is row. DISTINCT does not deduplicate a column; it deduplicates the whole row of the result, across every column you selected. With one column in the list those are the same thing, which is why DISTINCT looks simpler than it is — and why adding a second column to the list can silently undo it.

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_name TEXT NOT NULL, product TEXT NOT NULL);
INSERT INTO orders VALUES (1,'Alice','Laptop'),(2,'Bob','Mouse'),(3,'Alice','Keyboard'),(4,'Charlie','Monitor'),(5,'Bob','Laptop');

SELECT DISTINCT customer_name, product
FROM orders
ORDER BY customer_name, product;

Limiting Results

Control how many rows are returned:

-- Create a sales table
CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    product TEXT,
    amount DECIMAL(10, 2),
    sale_date TEXT
);

-- Insert sales data
INSERT INTO sales (id, product, amount, sale_date)
VALUES
    (1, 'Laptop', 999.99, '2025-01-01'),
    (2, 'Mouse', 24.99, '2025-01-02'),
    (3, 'Keyboard', 79.99, '2025-01-03'),
    (4, 'Monitor', 299.99, '2025-01-04'),
    (5, 'Headphones', 149.99, '2025-01-05');

-- Get the 3 cheapest sales
SELECT id, product, amount FROM sales ORDER BY amount ASC, id ASC LIMIT 3;

-- Skip the 2 cheapest and get the next 2 (useful for pagination)
SELECT id, product, amount FROM sales ORDER BY amount ASC, id ASC LIMIT 2 OFFSET 2;

LIMIT is meaningless without ORDER BY

LIMIT 3 means "give me three of the rows that qualify". It does not mean "the first three", because without an ORDER BY a query has no first. The database is free to return qualifying rows in any order it finds convenient, and it is entitled to change that order between runs, after an update, or once the table grows past some internal threshold — none of which will announce itself.

What makes this dangerous is that the order usually looks stable. A small table read straight off disk tends to come back in insertion order run after run, so a LIMIT with no sort can pass every test you write today and start returning different rows in production. Treat LIMIT as half a clause: whenever you write one, write the ORDER BY that makes it mean something, and give that ORDER BY a tiebreaker so two rows sharing a sort value cannot swap places either.

The clauses have a fixed order, and the order is not arbitrary

A SELECT is built from clauses that must appear in one sequence: the column list, then FROM, then WHERE, then ORDER BY, then LIMIT. Writing them in any other order is a syntax error, and the reason is worth more than the rule: each clause can only work on what the clauses above it have already produced. WHERE decides which rows exist before there is anything to sort; ORDER BY arranges the survivors; LIMIT cuts the arranged list.

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price_cents INTEGER NOT NULL, quantity INTEGER NOT NULL);
INSERT INTO products VALUES (1,'Laptop',99999,10),(2,'Mouse',2499,50),(3,'Keyboard',7999,30),(4,'Monitor',29999,15),(5,'Cable',999,0);

The table above is already built and stocked, and the five lines below are the whole of the query that reads it.

Reading Results You Did Not Expect

The characteristic SQL failure is not an error message. It is a query that parses, runs, exits cleanly, and returns the wrong rows — so the only thing standing between you and a wrong report is the habit of predicting the row count before you Run, and investigating whenever the two disagree.

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_name TEXT NOT NULL, product TEXT NOT NULL);
INSERT INTO orders VALUES (1,'Alice','Laptop'),(2,'Bob','Mouse'),(3,'Alice','Keyboard'),(4,'Charlie','Monitor'),(5,'Bob','Laptop');
SELECT DISTINCT customer_name, product FROM orders ORDER BY customer_name;

5 row(s) affected
customer_name
-------------
Alice
Bob
Charlie

Try It Yourself

Here is the build task. The starter creates a products table with prices in whole cents, fills it, and then runs a query that answers a different question than the one asked. Work the three TODOs until it reports the three most valuable lines of stock.

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price_cents INTEGER NOT NULL, quantity INTEGER NOT NULL);
INSERT INTO products VALUES (1,'Laptop',99999,10),(2,'Mouse',2499,50),(3,'Keyboard',7999,30),(4,'Monitor',29999,15);
-- TODO 1: return exactly two columns, headed product and stock_value_cents.
--         The first is the product's name; the id must not appear.
-- TODO 2: stock_value_cents is the price in cents times the quantity in stock.
--         The Laptop is 99999 times 10 = 999990.
-- TODO 3: show only the three most valuable lines, largest value first,
--         breaking any tie by product name in alphabetical order.
SELECT * FROM products;

4 row(s) affected
product | stock_value_cents
---------------------------
Laptop | 999990
Monitor | 449985
Keyboard | 239970

Once it passes, try two variations, predicting the result before each Run. First, delete the ORDER BY clause and leave the LIMIT 3 — predict whether you can still name which three rows come back, and notice that the answer is no, even if the rows you happen to see look reasonable. Then restore the sort, change the cap to LIMIT 2 OFFSET 1, and predict which rows appear: the second and third of the sorted result, Monitor and Keyboard, because OFFSET discards from the top of the sorted order rather than from the table.

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price_cents INTEGER NOT NULL, quantity INTEGER NOT NULL);
INSERT INTO products VALUES (1,'Laptop',99999,10),(2,'Mouse',2499,50),(3,'Keyboard',7999,30),(4,'Monitor',29999,15);

SELECT name AS product, price_cents * quantity AS v FROM products ORDER BY v DESC, name ASC;

SELECT name AS product, price_cents * quantity AS v FROM products ORDER BY price_cents * quantity DESC, name ASC;

SELECT name AS product, price_cents * quantity AS v FROM products ORDER BY 2 DESC, 1 ASC;

Practice Exercises

Try these exercises in the playground above:

1. Select only product names and their total values (price * quantity)
2. Find all unique products that have been ordered
3. Get the top 2 most expensive products
4. Calculate a 10% discount on all product prices and display both original and discounted prices
5. Combine first and last names with a comma separator (HINT: use ||)

Pro Tip: When working with large datasets, always use LIMIT during development to avoid accidentally retrieving millions of rows. Pair it with an ORDER BY so you know which rows you are looking at, and remove both once you are confident the query is correct.

Key Takeaways

• SELECT retrieves data from database tables
• Use * to look around a table, and name the columns you want in a report, so the result keeps its shape when someone adds a column
• Aliases make your results more readable with the AS keyword
• DISTINCT removes duplicate values from results
• Calculations and expressions can be performed directly in SELECT
• LIMIT controls the number of rows returned

Next Steps

Selecting all data from a table is useful, but real-world queries need precision. The next lesson introduces WHERE clauses for filtering rows and ORDER BY for sorting results. Together with SELECT, these tools let you answer specific questions like "which orders were placed last month, sorted by total?" instead of scanning entire tables manually.

Keep exploring:

• Back to Introduction to SQL — review the basics if anything above was unclear
• Continue learning → — work through all 25 SQL lessons, from basics to query optimization, right in your browser
• SQL Playground — experiment with SELECT queries against a live database