Lesson 1 of 14 · 9 min · Querying one table
SELECT, WHERE and sorting: your first SQL queries
What you will learn
- Create the practice tables used across this course
- Read columns with
SELECTand filter rows withWHERE - Combine conditions with
AND,OR,NOT,IN,BETWEENandLIKE - Sort with
ORDER BY, cut withLIMITand remove repeats withDISTINCT - The order in which SQL actually reads your query
In simple words
A database table is like an Excel sheet with a fixed set of columns. SQL is the language you use to ask questions of it. SELECT says which columns you want, FROM says which table, and WHERE says which rows. You do not tell the database how to find the rows, you only describe what you want and it works out the rest.
Words to know
- Table: rows and columns of data, like a sheet.
- Row: one record, such as one customer or one order.
- Column: one field of every row, such as
city. - Query: a question written in SQL.
The practice database
Every example in this course uses these tables. Run this once in PostgreSQL or MySQL (any SQL tool works; pgAdmin, DBeaver and the MySQL Workbench are common).
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
city VARCHAR(50),
signup_date DATE
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT REFERENCES customers(customer_id),
order_date DATE NOT NULL,
amount NUMERIC(10,2) NOT NULL,
status VARCHAR(20) NOT NULL
);
INSERT INTO customers VALUES
(1,'Asha','Hyderabad','2025-06-10'),
(2,'Ravi','Pune','2025-07-02'),
(3,'Meena','Chennai','2025-07-15'),
(4,'Kiran','Hyderabad','2025-08-01'),
(5,'Divya',NULL,'2025-08-20');
INSERT INTO orders VALUES
(101,1,'2025-09-01',1200,'paid'),
(102,1,'2025-09-15', 800,'paid'),
(103,2,'2025-09-20',1500,'refunded'),
(104,3,'2025-10-02', 600,'paid'),
(105,3,'2025-10-10', 900,'pending'),
(106,1,'2025-10-20', 500,'paid');
Notice that Divya has no city (a NULL) and that Kiran and Divya have no orders. Those gaps are deliberate: later lessons use them.
SELECT and FROM
SELECT name, city FROM customers;
name | city
-------+-----------
Asha | Hyderabad
Ravi | Pune
Meena | Chennai
Kiran | Hyderabad
Divya |
SELECT * returns every column. It is fine for exploring but avoid it in application code, because a new column silently changes your result. You can rename a column in the output with AS: SELECT amount AS order_value FROM orders;. SQL keywords are not case sensitive, but writing them in capitals and ending each statement with a semicolon keeps queries readable.
WHERE filters rows
SELECT name FROM customers WHERE city = 'Hyderabad';
returns Asha and Kiran. Text values use single quotes; numbers do not; dates are written as '2025-10-01'.
| Operator | Meaning |
|---|---|
=, <> (or !=), <, <=, >, >= |
comparisons |
AND, OR, NOT |
combine tests |
IN ('a','b') |
matches any value in the list |
BETWEEN x AND y |
from x to y, both ends included |
LIKE 'M%' |
pattern: % is any run of characters, _ is one character |
IS NULL |
the value is missing (next lesson) |
SELECT order_id, amount
FROM orders
WHERE status = 'paid' AND amount >= 800;
returns orders 101 (1200.00) and 102 (800.00).
SELECT order_id FROM orders
WHERE order_date BETWEEN '2025-10-01' AND '2025-10-31';
returns 104, 105 and 106. Mixing AND and OR needs brackets, because AND is evaluated first:
WHERE status = 'paid' OR status = 'pending' AND amount > 1000 -- probably not what you meant
WHERE (status = 'paid' OR status = 'pending') AND amount > 1000 -- clear
LIKE is case sensitive in PostgreSQL (use ILIKE for case-insensitive) and case insensitive by default for MySQL's usual collations. A pattern that starts with % cannot use a normal index, which matters for speed (lesson on indexes).
ORDER BY, LIMIT and DISTINCT
SELECT order_id, amount FROM orders ORDER BY amount DESC LIMIT 3;
order_id | amount
----------+---------
103 | 1500.00
101 | 1200.00
105 | 900.00
ORDER BYsorts ascending by default;DESCreverses. You can sort by several columns:ORDER BY status, amount DESC.- Without
ORDER BY, SQL promises no row order at all. Never rely on the order you happened to see. LIMIT nkeeps the firstnrows (SQL Server usesTOP n, OracleFETCH FIRST n ROWS ONLY; PostgreSQL and MySQL both acceptLIMIT). AddOFFSETto skip rows.SELECT DISTINCT status FROM orders;returns each status once:paid,refunded,pending.
The order SQL really runs in
You write SELECT first, but the database reads the query in this logical order:
FROM(which tables)WHERE(which rows)GROUP BYandHAVING(next lessons)SELECT(which columns, and anyASnames)ORDER BYLIMIT
This is why you cannot use a SELECT alias inside WHERE (the alias does not exist yet) but you can in ORDER BY. Keep this order in mind and many error messages make sense.
Common mistakes
- Double quotes around text. In standard SQL (and PostgreSQL) double quotes mean a column name; strings use single quotes.
AND/ORwithout brackets.- Relying on row order without
ORDER BY. SELECT *in application code.- Comparing a date column to text in another format such as
'01-10-2025'; useYYYY-MM-DD.
Try it yourself
- Create the tables and insert the rows above.
- List customers whose name starts with
K, then those whose city is Pune or Chennai usingIN. - Show the three smallest orders, then the most recent order.
- List the distinct cities (notice what happens to the missing one).
- Write a query that returns paid orders in September 2025 only.
Check yourself
What is the difference between WHERE and ORDER BY?
WHERE chooses which rows are returned. ORDER BY only decides the order of the rows that were chosen.
Why does WHERE a = 1 OR b = 2 AND c = 3 often surprise people?
AND binds tighter than OR, so it means a = 1 OR (b = 2 AND c = 3). Use brackets to say what you mean.
Is BETWEEN 1 AND 5 inclusive of 1 and 5?
Yes, both ends are included.
Can you rely on the order of rows when there is no ORDER BY?
No. SQL gives no guarantee; add ORDER BY whenever order matters.
Interview questions
Explain the logical order in which a SELECT statement is processed.
FROM, then WHERE, GROUP BY, HAVING, SELECT, ORDER BY, and finally LIMIT. That is why a column alias from SELECT can be used in ORDER BY but not in WHERE.
What is the difference between DISTINCT and GROUP BY?
Both can remove repeats, but DISTINCT just de-duplicates the selected columns, while GROUP BY forms groups so you can also compute aggregates like COUNT or SUM per group.
Why avoid SELECT * in production code?
It pulls columns you do not need (more data over the network), and it changes silently when a column is added or reordered, which can break code that depends on column positions.
Summary
SELECT columns FROM table WHERE conditionis the core of reading data.- Use
AND,OR(with brackets),IN,BETWEENandLIKEto filter. ORDER BYsorts,LIMITcaps,DISTINCTremoves repeats; withoutORDER BYthe order is not defined.- SQL runs
FROM,WHERE,GROUP BY,HAVING,SELECT,ORDER BY,LIMITin that logical order.
Going deeper
- Queries: select lists and table expressions: the PostgreSQL manual chapter on queries.
- Sorting rows and LIMIT:
ORDER BYdetails. - Pattern matching:
LIKEandILIKE. - MySQL SELECT statement: the MySQL syntax reference.
See how much of this you can explain
A free mock interview asks you new questions on SQL for analysts and developers for Data Analyst and scores every answer, with what to fix.