SELECT, WHERE, ORDER BY

Chapter 2 13 code blocks 3870 words

Everything an analyst does in SQL is a variation on one statement. Even a 200-line report query with six JOINs and three window functions is still, structurally, SELECT ... FROM ... WHERE ... ORDER BY .... So it pays to know these four clauses extremely well before adding anything on top.

Every query below runs on the school.db from Chapter 1.

Choosing columns

SELECT answers the question "which columns do you want to see?" — and the columns come out in the order you name them, not in the order they sit in the table.

-- all columns (exploring only)
SELECT * FROM students;

-- named columns, in the order YOU choose
SELECT last_name, first_name, city FROM students;

-- a column can appear more than once, and constants are allowed
SELECT first_name, first_name, 'student' FROM students;

Result 1

… first 15 of 300 rows

300 rows

Result 2

… first 15 of 300 rows

300 rows

Result 3

… first 15 of 300 rows

300 rows

2 ms

That last one looks pointless, but it teaches an important idea: SELECT builds a new result table. It does not "show you the table" — it constructs a fresh one, column by column, from whatever expressions you write.

Computed columns (expressions)

Anything you can compute, you can put in SELECT. The result becomes a new column that exists only in the output — nothing is written back to the table.

SELECT
    title,
    price,
    price * 0.9              AS discounted_price,
    price / credits          AS price_per_credit,
    credits * 30             AS total_hours
FROM courses;

15 rows

0 ms

:::gotcha ⚠️ Look at Data Visualization: 316666, not 316666.67 950000 / 3 gives 316666 because integer ÷ integer = integer in SQL — the decimal part is thrown away, not rounded. This silently corrupts averages and percentages. Force a decimal by multiplying by 1.0: price * 1.0 / credits, or cast: CAST(price AS REAL) / credits. We come back to this in Chapter 4, where it wrecks a percentage calculation. :::

Joining text together

In SQLite and PostgreSQL, || glues strings:

SELECT
    first_name || ' ' || last_name        AS full_name,
    last_name  || ', ' || first_name      AS sorted_name,
    UPPER(city)                           AS city_upper,
    LENGTH(first_name)                    AS name_length
FROM students
LIMIT 4;

4 rows

0 ms

:::gotcha ⚠️ NULL swallows the whole string For Sevara, city is NULL, so UPPER(city) is NULL — and if you wrote city || ', Uzbekistan' the entire result would be NULL, not ', Uzbekistan'. Wrap it: COALESCE(city, 'unknown') || ', Uzbekistan'. Chapter 3 covers COALESCE properly. :::

Aliases (AS)

An alias renames a column (or a table) in the output. Without one, the header of a computed column is the raw expression — unreadable in a report.

-- header becomes: price * 0.9        ← ugly
SELECT title, price * 0.9 FROM courses;

-- header becomes: discounted_price   ← readable
SELECT title, price * 0.9 AS discounted_price FROM courses;

-- AS is optional, but write it anyway — it is far easier to read
SELECT title, price * 0.9 discounted_price FROM courses;

-- a name with a space needs double quotes (identifier quoting)
SELECT title AS "Course Title", price AS "Price (UZS)" FROM courses;

Result 1

15 rows

Result 2

15 rows

Result 3

15 rows

Result 4

15 rows

1 ms

Table aliases Tables get aliases too. They become essential from Chapter 5 onward, when two tables both have a student_id column and you must say which one you mean.

SELECT s.first_name, s.city
FROM students AS s
WHERE s.city = 'Tashkent';

… first 15 of 82 rows

82 rows

0 ms

:::tip 💡 Alias naming Short but meaningful: s for students, e for enrollments, c for courses. Avoid a, b, t1, t2 — six months later nobody knows what t2 was. :::

WHERE and comparison operators

WHERE keeps a row only when the condition evaluates to TRUE. FALSE rows are dropped — and so are UNKNOWN rows, which is the NULL problem from Chapter 1.

-- "Which enrollments scored below 60?"
SELECT enrollment_id, student_id, course_id, grade, status
FROM enrollments
WHERE grade < 60;

… first 15 of 192 rows

192 rows

1 ms

AND, OR, NOT — and precedence

-- both conditions must hold
SELECT first_name, city, gender FROM students
WHERE city = 'Tashkent' AND gender = 'M';        -- Jasur, Otabek

-- either condition is enough
SELECT first_name, city FROM students
WHERE city = 'Bukhara' OR city = 'Fergana';      -- Dilnoza, Ulugbek

-- negation
SELECT first_name, status FROM enrollments
WHERE NOT status = 'completed';                  -- careful: NULL statuses would vanish

:::gotcha ⚠️ AND binds tighter than OR Exactly like × binds tighter than + in arithmetic. These two queries are not the same:

-- reads as: Tashkent, OR (Samarkand AND female)
-- returns 3 Tashkent students (both genders) + Zarina = 4 rows
WHERE city = 'Tashkent' OR city = 'Samarkand' AND gender = 'F';

-- what was actually meant: (Tashkent OR Samarkand) AND female
-- returns Aziza + Zarina = 2 rows
WHERE (city = 'Tashkent' OR city = 'Samarkand') AND gender = 'F';

Rule for students: the moment a condition mixes AND with OR, put brackets in. Even where the brackets are redundant, they cost nothing and remove all doubt. :::

ORDER BY

Rows in a table have no order, so if the output order matters you must ask for it. ASC (ascending) is the default; DESC reverses it.

-- one column
SELECT first_name, enrolled_date FROM students
ORDER BY enrolled_date DESC;

-- several columns: sort by city, then by name inside each city
SELECT city, first_name FROM students
ORDER BY city ASC, first_name DESC;

-- you may sort by an expression, or by an alias
SELECT title, price / credits AS per_credit FROM courses
ORDER BY per_credit DESC;

-- you may even sort by a column you did NOT select
SELECT first_name FROM students
ORDER BY birth_date;

Result 1

… first 15 of 300 rows

300 rows

Result 2

… first 15 of 300 rows

300 rows

Result 3

15 rows

Result 4

… first 15 of 300 rows

300 rows

1 ms

:::gotcha ⚠️ Where do NULLs sort? ORDER BY city on our students puts Sevara (NULL) first in SQLite and MySQL, because they treat NULL as smaller than everything. PostgreSQL and Oracle put NULLs last by default. If the position matters, be explicit — PostgreSQL supports ORDER BY city NULLS LAST; a portable trick that works everywhere is:

ORDER BY (city IS NULL), city;   -- 0 sorts before 1, so NULLs go last

:::

LIMIT and OFFSET

LIMIT caps how many rows come back. It is the last step of all, so it applies after sorting.

-- top 3 grades
SELECT enrollment_id, student_id, grade
FROM enrollments
WHERE grade IS NOT NULL
ORDER BY grade DESC
LIMIT 3;

3 rows

0 ms

enrollment_idstudent_idgrade

136100 6395 1192

OFFSET skips rows first — this is how pagination works:

SELECT first_name FROM students ORDER BY student_id LIMIT 3;             -- page 1
SELECT first_name FROM students ORDER BY student_id LIMIT 3 OFFSET 3;   -- page 2
SELECT first_name FROM students ORDER BY student_id LIMIT 3 OFFSET 6;   -- page 3

Result 1

3 rows

Result 2

3 rows

Result 3

3 rows

1 ms

:::gotcha ⚠️ LIMIT without ORDER BY is meaningless LIMIT 10 alone means "any 10 rows the database happens to hand me" — and that set can change between runs. LIMIT is only meaningful with ORDER BY. The same applies to pagination: without a stable sort, page 2 can repeat rows from page 1. :::

DISTINCT

DISTINCT removes duplicate rows from the result. It is the fastest way to answer "what values actually occur in this column?"

SELECT DISTINCT city FROM students ORDER BY city;

city

NULL Andijan Bukhara Fergana Namangan Samarkand Tashkent

Seven rows from ten students. Note that DISTINCT keeps NULL as one of the values — it treats all NULLs as duplicates of each other, even though NULL = NULL is UNKNOWN. This is an inconsistency in SQL itself; just tell students it is a special case.

Practice set

Give these to students in order; each one adds exactly one new idea.

-- 1. Female students, alphabetically by last name
SELECT first_name, last_name FROM students
WHERE gender = 'F'
ORDER BY last_name;

-- 2. Courses costing more than 1,300,000, most expensive first
SELECT title, price FROM courses
WHERE price > 1300000
ORDER BY price DESC;

-- 3. Full name + city, unknown city shown as 'unknown'
SELECT first_name || ' ' || last_name AS full_name,
       COALESCE(city, 'unknown')      AS city
FROM students;

-- 4. The 3 lowest grades that were actually given
SELECT enrollment_id, grade FROM enrollments
WHERE grade IS NOT NULL
ORDER BY grade ASC
LIMIT 3;

-- 5. Which statuses exist in enrollments?
SELECT DISTINCT status FROM enrollments;

-- 6. Data-category courses with 4 or more credits
SELECT title, credits FROM courses
WHERE category = 'Data' AND credits >= 4
ORDER BY credits DESC, title;

-- 7. Students from Tashkent or Samarkand who are female (brackets!)
SELECT first_name, city FROM students
WHERE (city = 'Tashkent' OR city = 'Samarkand') AND gender = 'F';

-- 8. Course price per credit, as a proper decimal
SELECT title, ROUND(price * 1.0 / credits, 2) AS per_credit
FROM courses
ORDER BY per_credit DESC;

Result 1

… first 15 of 154 rows

154 rows

Result 2

4 rows

Result 3

… first 15 of 300 rows

300 rows

Result 4

3 rows

Result 5

4 rows

Result 6

2 rows

Result 7

… first 15 of 67 rows

67 rows

Result 8

15 rows

2 ms

Interview questions

Q: Can you use a column alias in WHERE?

No. WHERE runs at step 2 and SELECT — where the alias is created — runs at step 5. Repeat the expression in WHERE, or wrap the query in a CTE/subquery. ORDER BY (step 7) can use the alias.

Q: What is the difference between DISTINCT and GROUP BY?

For simply listing unique values they give the same answer, and most engines execute them identically. GROUP BY is more powerful: it can compute aggregates per group (COUNT, AVG) and be filtered with HAVING. Use DISTINCT when you only want unique rows; use GROUP BY when you want something calculated per group.

Q: Does DISTINCT apply to one column or all of them?

All the selected columns together. SELECT DISTINCT a, b returns unique pairs of (a, b).

Q: WHERE city != 'Tashkent' — will rows with a NULL city be returned?

No, and this is the classic trick question. NULL != 'Tashkent' is UNKNOWN, and WHERE keeps only TRUE. Write WHERE city != 'Tashkent' OR city IS NULL.

Q: What does LIMIT 10 return without ORDER BY?

Ten arbitrary rows — whichever ones the engine produced first. It is not guaranteed to be stable between runs, so it is only safe for eyeballing data, never for reporting.

Q: Why does SELECT 7/2 return 3?

Integer ÷ integer = integer; the fractional part is truncated, not rounded. Use 7 * 1.0 / 2 or CAST(7 AS REAL) / 2.

Q: What is the difference between ORDER BY name and ORDER BY 2?

The second sorts by the second column in the SELECT list, by position. It works but is fragile: editing the SELECT list silently changes the sort.

Summary

• SELECT builds a new table: columns come out in the order you name them, and can be expressions, constants or concatenations.
• Integer division truncates — price / credits loses the decimals. Multiply by 1.0 or CAST.
• Aliases (AS) rename output columns; they are usable in ORDER BY but never in WHERE.
• WHERE keeps only rows that are TRUE. NULL rows are UNKNOWN, so <> silently drops them — add OR col IS NULL when you need them.
• AND binds tighter than OR. Mixing them without brackets is a bug waiting to happen.
• ORDER BY is the only way to guarantee row order; NULL placement differs by database.
• LIMIT is meaningless without ORDER BY; LIMIT ... OFFSET ... is pagination.
• DISTINCT applies to the whole selected row and keeps NULL as one value.

Next: Chapter 3 goes deeper into filtering — IN, BETWEEN, LIKE patterns, and the NULL traps that quietly break real reports.