SQL · Guides · October 30, 2026 · 11 min read

Basic SQL queries: SELECT, WHERE, ORDER BY, GROUP BY and JOIN for beginners

A basic SQL query always has the same shape: SELECT says which columns you want to see, FROM says which table they come from, WHERE filters rows, GROUP BY groups them so you can compute totals, ORDER BY sorts the result and JOIN combines two tables on a shared column. These six pieces cover most of an analyst’s daily work. This guide walks through them one at a time, with queries that actually run: every one of them runs on the airline database from the Nomto SQL course, and each result table below is the real output.

The database in the examples

You will work with five tables from an airline (values are in English, money is in US dollars, times are Moscow time, UTC+3):

Before you write any query, know what one row of each table represents. Almost every beginner mistake in SQL comes from counting rows that are not what you think they are.

SELECT and FROM: picking columns

The simplest query asks for columns from a table:

SELECT model, range
FROM aircrafts;

A few rules worth settling early:

• SELECT * returns every column. It is fine for a quick look, but in a real query name the columns you need: the result is clearer and it does not break when someone adds columns to the table.
• You can calculate in SELECT. AS gives the calculated column a name.
• The semicolon at the end separates queries. The Nomto SQL room runs one query at a time, so you can leave it out.

Range is in kilometres. To show it in miles as well:

SELECT model,
       range,
       ROUND(range / 1.609) AS range_miles
FROM aircrafts
ORDER BY range DESC
LIMIT 5;

Without ROUND the column would come out with a long tail of decimals (6898.69484151647). ORDER BY and LIMIT are covered further down.

WHERE: filtering rows

WHERE lets through only the rows that meet a condition. The airports in Moscow:

SELECT airport_code, airport_name, city
FROM airports
WHERE city = 'Moscow'
ORDER BY airport_code;

Notice two details: text goes in single quotes (in PostgreSQL, double quotes are for column names), and the comparison is case-sensitive: 'moscow' returns nothing.

Combining conditions: AND, OR, IN, BETWEEN

Departures from Sheremetyevo on 1 August:

SELECT flight_no, scheduled_departure, arrival_airport, status
FROM flights
WHERE departure_airport = 'SVO'
  AND scheduled_departure >= '2026-08-01'
  AND scheduled_departure <  '2026-08-02'
ORDER BY scheduled_departure, flight_no
LIMIT 5;

That day 50 flights left SVO. The date filter uses the pattern “greater than or equal to the start of the day, and less than the start of the next day”. It is safer than BETWEEN '2026-08-01' AND '2026-08-01', which on a date-and-time column only catches the exact moment of midnight.

IN replaces several ORs on the same column. Flights that have already taken off (in the air or landed):

SELECT COUNT(*)
FROM flights
WHERE status IN ('Departed', 'Arrived');

The result is 16,765. When you mix AND and OR, use parentheses: AND is evaluated first, so a OR b AND c means a OR (b AND c).

LIKE: matching a pattern

% stands for any number of characters and _ for exactly one. Cities that start with “Novo”:

SELECT airport_code, airport_name, city
FROM airports
WHERE city LIKE 'Novo%'
ORDER BY city;

In PostgreSQL, ILIKE does the same without caring about case.

IS NULL: empty values

NULL means “no value”. A flight that has not left yet has an empty actual_departure. Here is the most famous trap in SQL:

SELECT COUNT(*) FROM flights WHERE actual_departure = NULL;   -- 0
SELECT COUNT(*) FROM flights WHERE actual_departure IS NULL;  -- 16348

The first returns 0 even though thousands of flights have not departed: any comparison with NULL gives “unknown”, and WHERE only lets through what is true. For empty values always use IS NULL or IS NOT NULL.

ORDER BY and LIMIT: sorting and keeping the top rows

Without ORDER BY the database returns rows in whatever order is convenient for it, and that order can change between runs. The five most expensive bookings:

SELECT book_ref, book_date, total_amount
FROM bookings
ORDER BY total_amount DESC
LIMIT 5;

• ASC (the default) sorts from smallest to largest; DESC from largest to smallest.
• You can sort by several columns: ORDER BY scheduled_departure, flight_no breaks ties by flight number when two flights leave at the same time, as in the SVO example.
• LIMIT without ORDER BY gives you “some rows”, not “the first rows”. If it matters which ones you get, sort.

Aggregate functions: COUNT, SUM, AVG, MIN, MAX

Aggregate functions turn many rows into a single number. One difference worth understanding early:

SELECT COUNT(*)                           AS total,
       COUNT(actual_departure)            AS departed,
       COUNT(*) - COUNT(actual_departure) AS not_yet
FROM flights;

COUNT(*) counts rows; COUNT(column) counts only non-empty values. COUNT(DISTINCT column) counts distinct values: SELECT COUNT(DISTINCT timezone) FROM airports returns 17 time zones.

GROUP BY: one total per group

GROUP BY splits the table into groups and computes the aggregate inside each one. How many flights are in each status:

SELECT status, COUNT(*) AS flights
FROM flights
GROUP BY status
ORDER BY flights DESC;

Several aggregates at once, by cabin class:

SELECT fare_conditions,
       COUNT(*)              AS segments,
       ROUND(AVG(amount), 2) AS avg_price,
       MIN(amount)           AS min_price,
       MAX(amount)           AS max_price
FROM ticket_flights
GROUP BY fare_conditions
ORDER BY avg_price DESC;

The golden rule: every column in SELECT must either be in GROUP BY or sit inside an aggregate function. If you write SELECT status, flight_no, COUNT(*) ... GROUP BY status, you get an error, because each group holds hundreds of flight numbers and the database does not know which one to show.

HAVING: filtering groups

WHERE filters rows before grouping; HAVING filters groups after. Cities with more than one airport:

SELECT city, COUNT(*) AS airports
FROM airports
GROUP BY city
HAVING COUNT(*) > 1
ORDER BY airports DESC;

You cannot write WHERE COUNT(*) > 1: when WHERE runs, the groups do not exist yet. The GROUP BY vs HAVING guide goes deeper into this.

JOIN: combining tables

In flights the aircraft appears only as a code (321, SU9). The model name lives in aircrafts. JOIN connects the two tables through the shared column:

SELECT a.model, COUNT(*) AS flights
FROM flights f
JOIN aircrafts a ON a.aircraft_code = f.aircraft_code
GROUP BY a.model
ORDER BY flights DESC;

f and a are aliases: short names so you do not have to repeat the full table name. ON says which rows belong together.

INNER JOIN and LEFT JOIN

The table above has 8 models, but aircrafts has 9. JOIN (short for INNER JOIN) keeps only rows that have a match in both tables, so the model with no flights disappears. LEFT JOIN keeps every row of the left table and fills in NULL where it finds no match. That is how you find “rows without a partner”:

SELECT a.aircraft_code, a.model
FROM aircrafts a
LEFT JOIN flights f ON f.aircraft_code = a.aircraft_code
WHERE f.flight_id IS NULL;

If you count after a LEFT JOIN, use COUNT(f.flight_id), not COUNT(*): that way the A320 shows up with 0 instead of 1.

A warning for later: if you join a table to another one that has several rows for each of its rows (one booking with several tickets), rows multiply and sums inflate. The SQL joins guide shows this with real numbers.

Putting it together: a complete query

The pieces always go in a fixed order: SELECT … FROM … JOIN … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT. Cities with at least 300 landed flights and their average departure delay in minutes:

SELECT ap.city,
       COUNT(*) AS flights,
       ROUND(AVG(EXTRACT(EPOCH FROM f.actual_departure - f.scheduled_departure) / 60), 1) AS avg_delay_min
FROM flights f
JOIN airports ap ON ap.airport_code = f.departure_airport
WHERE f.status = 'Arrived'
GROUP BY ap.city
HAVING COUNT(*) >= 300
ORDER BY avg_delay_min DESC;

Subtracting one timestamp from another gives an interval; EXTRACT(EPOCH FROM …) turns it into seconds, and dividing by 60 gives minutes.

The order in which a query runs

You write SELECT first, but the database processes it almost last:

1. FROM and JOIN: build the working table.
2. WHERE: drop rows.
3. GROUP BY: form the groups.
4. HAVING: drop groups.
5. SELECT: compute the columns and aliases.
6. ORDER BY: sort (here you can already use the aliases from SELECT).
7. LIMIT: cut.

This order explains why a SELECT alias works in ORDER BY but not in WHERE, and why aggregates go in HAVING.

Common beginner mistakes

• = NULL instead of IS NULL. It returns zero rows without any warning.
• Double quotes around text. In PostgreSQL, WHERE city = "Moscow" looks for a column called Moscow.
• Ungrouped columns in SELECT. Anything not in GROUP BY has to go inside an aggregate.
• LIMIT without ORDER BY. The result is not “the top”, it is arbitrary.
• COUNT(*) after a LEFT JOIN. Rows without a match are counted as 1.
• BETWEEN on columns that include a time. Use >= the start and < the next day.

How to practise

Reading queries helps, but you learn by writing them and getting them wrong. Every example in this guide can be run and changed in the Nomto SQL room, on the same database: change the city, the status or the sort order and see what happens. The first module of the course starts with exactly SELECT and aliases, and every lesson ends with three tasks that are checked automatically. Once the basics feel solid, move on to the SQL practice problems and window functions.

FAQ

How long does it take to learn basic SQL queries?

You can learn SELECT, WHERE, ORDER BY, GROUP BY and JOIN in two or three weeks at an hour a day. What takes longer is reading the data correctly: knowing what one row means and when a JOIN multiplies rows.

What is the difference between WHERE and HAVING?

WHERE filters rows before grouping and cannot use aggregates. HAVING filters groups that have already been formed and is used with COUNT, SUM, AVG and the rest.

Does the SQL in this guide work in MySQL or SQL Server?

Almost all of it. SELECT, WHERE, GROUP BY, HAVING, JOIN and ORDER BY are the same everywhere. The details differ: SQL Server uses TOP instead of LIMIT, ILIKE does not exist in MySQL or SQL Server, and date functions have different names.

Do I need to install a database to practise?

No. In the Nomto SQL room you type the query in the browser and run it on the airline database, with nothing to install.

Also in Português (Brasil), Tiếng Việt, 简体中文, Español