# SELECT basics > Write SELECT queries that pick columns, compute expressions, rename results with aliases and return a limited number of rows. SQL & Databases roadmap · Querying · 14 min read · https://levelcode.org/roadmaps/sql/select-basics `SELECT` is the statement you will write more than any other. It reads data and returns a result table — it never changes anything, so you can experiment freely. In this lesson you will choose columns, compute new values with expressions, give results readable names, remove duplicates and limit how many rows come back. All examples run against the bookstore database from the previous lesson. ## Your first query The smallest useful query names some columns and the table they come from: ```sql SELECT title, price FROM books; ``` ```text title | price ---------------------------+------- A Wizard of Earthsea | 12.99 The Left Hand of Darkness | 15.50 The Dispossessed | 16.00 Guards! Guards! | 10.99 Small Gods | 10.99 Going Postal | 11.50 Kindred | 14.00 Parable of the Sower | 13.25 Norwegian Wood | 17.00 Kafka on the Shore | 18.50 (10 rows) ``` Read it as "from the `books` table, give me the `title` and `price` of every row". The result is itself a table: it has columns, rows, and no guaranteed order. A few syntax rules: - Keywords (`SELECT`, `FROM`) are case-insensitive. Writing them in capitals is a convention that makes queries easier to scan. - Whitespace and line breaks do not matter. Put each clause on its own line once queries grow. - A semicolon ends the statement. `psql` waits for it before running anything. - `--` starts a comment that runs to the end of the line, and `/* ... */` comments can span several lines. Use them to explain *why* a query does something non-obvious. ### SELECT * `SELECT *` returns every column in table order. It is perfect for exploring a table in `psql` (where the `\x` command toggles an expanded display that prints one column per line, handy for wide rows): ```sql SELECT * FROM customers; ``` In application code, list the columns you need instead. `*` fetches data you will not use, and if someone adds or reorders a column later, code that depends on column positions breaks silently. ## Expressions: computing new columns A `SELECT` list is not limited to stored columns. Each item can be any **expression**, evaluated once per row: ```sql SELECT title, price, stock, price * stock AS stock_value FROM books LIMIT 4; ``` ```text title | price | stock | stock_value ---------------------------+-------+-------+------------- A Wizard of Earthsea | 12.99 | 14 | 181.86 The Left Hand of Darkness | 15.50 | 6 | 93.00 The Dispossessed | 16.00 | 0 | 0.00 Guards! Guards! | 10.99 | 22 | 241.78 (4 rows) ``` Nothing is stored: `stock_value` exists only in this result. Expressions can use: | Kind | Examples | | --- | --- | | Arithmetic | `price * 1.2`, `stock - 1`, `quantity * unit_price`, `7 % 2` (remainder) | | String concatenation | `name \|\| ' <' \|\| email \|\| '>'` | | Functions | `upper(name)`, `length(title)`, `round(price * 1.2, 2)` | | Date arithmetic | `ordered_on + 14` (a `date` plus an integer adds days) | | Literals | `'sold out'`, `42`, `true` | ```sql SELECT name || ' <' || email || '>' AS contact, upper(name) AS shouting, length(name) AS name_len FROM customers LIMIT 3; ``` ```text contact | shouting | name_len ----------------------------------+--------------+---------- Ada Park | ADA PARK | 8 Ben Okafor | BEN OKAFOR | 10 Chloe Martin | CHLOE MARTIN | 12 (3 rows) ``` You do not even need a table. `SELECT 2 + 2;` is a valid query that returns one row, which makes `psql` a handy calculator for testing an expression before you use it. ### The integer division trap The type of an expression follows the types of its inputs. Dividing two integers produces an integer, and PostgreSQL truncates the fraction: ```sql SELECT 7 / 2 AS int_div, 7 / 2.0 AS num_div, 7::numeric / 2 AS cast_div, 7 % 2 AS remainder; ``` ```text int_div | num_div | cast_div | remainder ---------+--------------------+--------------------+----------- 3 | 3.5000000000000000 | 3.5000000000000000 | 1 (1 row) ``` If either side is `numeric`, the result is `numeric`. The `::` operator **casts** a value to another type (`7::numeric`, `'2025-01-05'::date`). This bites people computing averages or percentages from integer columns such as `stock` or `quantity` — cast first, then divide, then `round()` if you want fewer digits. ### NULL spreads through expressions Any arithmetic or `||` concatenation involving `NULL` produces `NULL`. Two customers have no city: ```sql SELECT name, 'Lives in ' || city AS with_pipes, concat('Lives in ', city) AS with_concat FROM customers; ``` ```text name | with_pipes | with_concat --------------+-----------------+----------------- Ada Park | Lives in Leeds | Lives in Leeds Ben Okafor | Lives in London | Lives in London Chloe Martin | (null) | Lives in Dev Raman | Lives in Leeds | Lives in Leeds Elena Rossi | Lives in London | Lives in London Farid Haddad | (null) | Lives in (6 rows) ``` The `concat()` function treats `NULL` as an empty string, while `||` does not. Neither is wrong; pick deliberately. The next lesson covers `NULL` and tools like `coalesce()` in depth. ## CASE: choosing a value per row `CASE` is SQL's if/else expression. Conditions are tested top to bottom, and the first match wins: ```sql SELECT title, stock, CASE WHEN stock = 0 THEN 'sold out' WHEN stock < 5 THEN 'low' ELSE 'in stock' END AS availability FROM books; ``` ```text title | stock | availability ---------------------------+-------+-------------- A Wizard of Earthsea | 14 | in stock The Left Hand of Darkness | 6 | in stock The Dispossessed | 0 | sold out Guards! Guards! | 22 | in stock Small Gods | 9 | in stock Going Postal | 3 | low Kindred | 11 | in stock Parable of the Sower | 5 | in stock Norwegian Wood | 8 | in stock Kafka on the Shore | 0 | sold out (10 rows) ``` Order matters: a stock of 0 is also `< 5`, but the `= 0` branch is checked first. If no branch matches and there is no `ELSE`, the result is `NULL`. ## Aliases An **alias** renames a column or table within a single query. Without one, PostgreSQL invents a name for computed columns — usually the unhelpful `?column?`: ```sql SELECT title, price * 2 FROM books LIMIT 2; ``` ```text title | ?column? ---------------------------+---------- A Wizard of Earthsea | 25.98 The Left Hand of Darkness | 31.00 (2 rows) ``` Add `AS name` to fix it. `AS` is optional (`price * 2 doubled` works), but writing it makes the intent obvious. ### Identifiers and quoting PostgreSQL folds unquoted identifiers to lower case, so `SELECT Title FROM Books` works fine. Double quotes preserve case and allow spaces: ```sql SELECT title AS "Book Title", price AS Price FROM books LIMIT 2; ``` ```text Book Title | price ---------------------------+------- A Wizard of Earthsea | 12.99 The Left Hand of Darkness | 15.50 (2 rows) ``` Notice `Price` came out as `price` — it was unquoted, so it was folded. > [!WARNING] > **Single quotes are for string values; double quotes are for identifiers.** `"Title"` means "the column named exactly `Title`", which does not exist, so `SELECT "Title" FROM books;` fails with `ERROR: column "Title" does not exist`. Mixing the two up is one of the most common beginner errors. Prefer lower-case `snake_case` names everywhere and you will rarely need double quotes at all. ### Table aliases You can alias tables too. `FROM books AS b` lets you write `b.title`. Qualifying columns with a table name or alias is optional when only one table is involved, but becomes essential once you join tables and two of them have a column with the same name: ```sql SELECT b.title, b.published, 2025 - b.published AS age_in_2025 FROM books AS b LIMIT 3; ``` ```text title | published | age_in_2025 ---------------------------+-----------+------------- A Wizard of Earthsea | 1968 | 57 The Left Hand of Darkness | 1969 | 56 The Dispossessed | 1974 | 51 (3 rows) ``` ## DISTINCT: removing duplicate rows Which genres does the shop carry? Selecting `genre` returns ten rows, one per book. `DISTINCT` collapses identical result rows: ```sql SELECT DISTINCT genre FROM books; ``` ```text genre ---------- fantasy sci-fi literary (3 rows) ``` `DISTINCT` applies to the **whole row**, not the first column. `SELECT DISTINCT genre, author_id` returns five rows, one per distinct (genre, author) combination. And `NULL`s count as equal for this purpose: `SELECT DISTINCT city FROM customers` returns `Leeds`, `London` and a single `(null)` row. ## LIMIT, OFFSET and row order `LIMIT n` returns at most `n` rows. `OFFSET k` skips the first `k` rows. On its own, though, `LIMIT` returns an *arbitrary* set of rows, because a table has no order. Pair it with `ORDER BY` (covered fully in the next lesson) to make it meaningful: ```sql SELECT title, price FROM books ORDER BY price DESC LIMIT 3; ``` ```text title | price --------------------+------- Kafka on the Shore | 18.50 Norwegian Wood | 17.00 The Dispossessed | 16.00 (3 rows) ``` `LIMIT 3 OFFSET 3` returns the next three (*The Left Hand of Darkness*, *Kindred*, *Parable of the Sower*). The SQL-standard spelling `FETCH FIRST 3 ROWS ONLY` does the same as `LIMIT 3`; PostgreSQL accepts both. > [!TIP] > SQL clauses are written in one order but logically evaluated in another: `FROM` → `SELECT` → `DISTINCT` → `ORDER BY` → `LIMIT`/`OFFSET`. That is why `ORDER BY` can use an alias defined in the `SELECT` list (`ORDER BY stock_value`), and why `LIMIT` applies last, to the already sorted result. You will add `WHERE`, `GROUP BY` and `HAVING` to this sequence in later lessons. ## Practice Using the bookstore database, write one query against `order_items` that returns, for every row: 1. `order_id` and `book_id`, 2. `line_total` — quantity times unit price, 3. `line_total_with_vat` — the line total plus 20% VAT, rounded to two decimals, 4. `size` — the text `'multi'` when more than one copy was bought, otherwise `'single'`. Show only the five lines with the highest `line_total`, largest first. Then answer: what does `SELECT quantity / 2 FROM order_items` return for a line with quantity 3, and how would you get `1.5`? **Solution** ```sql SELECT order_id, book_id, quantity * unit_price AS line_total, round(quantity * unit_price * 1.2, 2) AS line_total_with_vat, CASE WHEN quantity > 1 THEN 'multi' ELSE 'single' END AS size FROM order_items ORDER BY line_total DESC LIMIT 5; ``` ```text order_id | book_id | line_total | line_total_with_vat | size ----------+---------+------------+---------------------+-------- 7 | 1 | 38.97 | 46.76 | multi 5 | 7 | 28.00 | 33.60 | multi 10 | 6 | 23.00 | 27.60 | multi 1 | 4 | 19.98 | 23.98 | multi 4 | 10 | 18.50 | 22.20 | single (5 rows) ``` `ORDER BY line_total` works because `ORDER BY` is evaluated after the `SELECT` list, so the alias exists by then. `quantity / 2` with quantity 3 returns `1`, because both operands are integers. Cast one side to get `1.5`: `quantity::numeric / 2` or `quantity / 2.0`. ## Key takeaways - `SELECT columns FROM table` returns a new result table; it never modifies data. - Each item in the select list can be any expression — arithmetic, `||`, functions, `CASE` — evaluated per row. - Integer divided by integer truncates; cast with `::numeric` when you need fractions. `NULL` in arithmetic or `||` yields `NULL`. - Use `AS` to name computed columns; single quotes are strings, double quotes are case-sensitive identifiers. - `DISTINCT` removes duplicate whole rows. `LIMIT`/`OFFSET` are only meaningful together with `ORDER BY`. ## Quiz 1. What does SELECT 7 / 2; return in PostgreSQL? A. 3.5 B. 3 C. 4 D. An error, because the result is not a whole number **Answer: B.** Both operands are integers, so PostgreSQL performs integer division and truncates toward zero. Write 7 / 2.0 or 7::numeric / 2 to get 3.5. 2. Chloe's city is NULL. What does 'Lives in ' || city return for her row? A. 'Lives in ' B. 'Lives in NULL' C. NULL D. An error **Answer: C.** The || operator returns NULL if any operand is NULL. The concat() function is the one that treats NULL as an empty string and would return 'Lives in '. 3. Why is SELECT title FROM books LIMIT 3; (with no ORDER BY) a problem in application code? A. Which three rows come back is not guaranteed and can change between runs B. LIMIT is not valid without OFFSET C. It always returns the three oldest rows, which is rarely wanted D. It returns all rows and only displays three **Answer: A.** Tables are unordered sets. Without ORDER BY the database may return any three rows, and the choice can change as data or plans change. Always pair LIMIT with ORDER BY. 4. Which query fails with 'column "Title" does not exist'? A. SELECT Title FROM books; B. SELECT title AS "Title" FROM books; C. SELECT TITLE FROM BOOKS; D. SELECT "Title" FROM books; **Answer: D.** Double quotes make an identifier case-sensitive, and the column is stored as lower-case title. Unquoted names like Title or TITLE are folded to lower case and work. Using "Title" as an alias is fine because it creates a new name.