SQL Basics with Real Queries: SELECT, JOIN and GROUP BY Examples

SQL (Structured Query Language) is how you ask relational databases questions. It is used by PostgreSQL, MySQL, SQLite, SQL Server, Oracle and many more, and it is one of the most used languages among developers. This hands-on guide uses one small shop database, two tables, and walks through every core statement: CREATE, INSERT, SELECT, WHERE, ORDER BY, GROUP BY, JOINs, UPDATE and DELETE. Each query is followed by its actual result.

Versions and facts in this guide were checked against official documentation on 26 October 2026 (IST). Tools move fast, so confirm version numbers on the linked official pages before you install.

We ran every statement in PostgreSQL 18 and in SQLite 3.46, and both returned the same rows. The result tables below come from PostgreSQL. SQLite shows the money values without trailing zeros (for example 1499 instead of 1499.00), but the values are the same.

Where to run these queries

• SQLite needs no server: install the sqlite3 command-line tool, run sqlite3 shop.db, and paste the statements.
• PostgreSQL in Docker: docker run -d --name pg -e POSTGRES_PASSWORD=pw postgres:18, then docker exec -it pg psql -U postgres. Our Docker basics guide explains these commands and runs Postgres with Docker Compose.
• MySQL works too. The statements here use standard syntax.

The sample tables

A customers table and an orders table. Each order points to a customer through customer_id. Note that Arjun has not ordered anything yet, which matters later.

customers

orders

CREATE TABLE

CREATE TABLE customers (
    id    INTEGER PRIMARY KEY,
    name  VARCHAR(50) NOT NULL,
    city  VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
    id           INTEGER PRIMARY KEY,
    customer_id  INTEGER REFERENCES customers(id),
    product      VARCHAR(50) NOT NULL,
    amount       DECIMAL(10, 2) NOT NULL,
    order_date   DATE NOT NULL
);

Each column has a name and a type. PRIMARY KEY uniquely identifies each row, NOT NULL makes a value mandatory, and REFERENCES customers(id) declares a foreign key: every order must belong to a real customer. DECIMAL(10, 2) stores exact money values with two decimal places. Never use floating-point types for money. Databases differ in the details: SQLite is flexible about types and only enforces foreign keys after PRAGMA foreign_keys = ON;, while PostgreSQL also offers GENERATED ALWAYS AS IDENTITY for auto-numbered IDs.

INSERT

INSERT INTO customers (id, name, city) VALUES
    (1, 'Asha', 'Chennai'),
    (2, 'Ravi', 'Bengaluru'),
    (3, 'Meena', 'Chennai'),
    (4, 'Arjun', 'Mumbai');

INSERT INTO orders (id, customer_id, product, amount, order_date) VALUES
    (101, 1, 'Laptop bag', 1499.00, '2026-10-01'),
    (102, 2, 'Keyboard', 2199.00, '2026-10-03'),
    (103, 1, 'Mouse', 799.00, '2026-10-05'),
    (104, 3, 'Monitor', 12999.00, '2026-10-07'),
    (105, 2, 'USB cable', 299.00, '2026-10-09'),
    (106, 1, 'Headphones', 3499.00, '2026-10-12');

One INSERT can add many rows. Strings and dates go in single quotes, and dates use the ISO YYYY-MM-DD format.

SELECT: reading data

SELECT * FROM customers;

This returns the whole customers table shown above. * means "all columns". In real code, list the columns you need.

WHERE: filtering rows

SELECT name, city FROM customers WHERE city = 'Chennai';

Result:

Combine conditions with AND and OR, match several values with IN:

SELECT name FROM customers WHERE city IN ('Mumbai', 'Bengaluru') ORDER BY name;

Result:

ORDER BY: sorting

SELECT product, amount FROM orders WHERE amount > 1000 ORDER BY amount DESC;

Result:

DESC sorts from high to low, and ASC (the default) from low to high. Without ORDER BY, the database may return rows in any order, so always add it when order matters.

Aggregate functions

SELECT COUNT(*) AS total_orders, SUM(amount) AS revenue, ROUND(AVG(amount), 2) AS avg_order FROM orders;

Result:

COUNT, SUM, AVG, MIN and MAX collapse many rows into one value. AS gives the result column a readable name. ROUND(..., 2) keeps the average tidy.

GROUP BY and HAVING

GROUP BY runs the aggregate once per group, here once per customer:

SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC;

Result:

To filter groups, use HAVING. WHERE filters rows before grouping, and HAVING filters the grouped results:

SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 3000
ORDER BY total_spent DESC;

Result:

JOINs: combining tables

INNER JOIN

Customer IDs are not very readable. A join matches each order to its customer:

SELECT c.name, o.product, o.amount
FROM orders AS o
INNER JOIN customers AS c ON c.id = o.customer_id
ORDER BY o.order_date;

Result:

o and c are table aliases. ON c.id = o.customer_id is the matching rule. An INNER JOIN only keeps rows that match on both sides, so Arjun, who has no orders, does not appear.

LEFT JOIN

A LEFT JOIN keeps every row from the left table, even without a match:

SELECT c.name, COUNT(o.id) AS orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY orders DESC, c.name;

Result:

Arjun now appears with 0 orders. We count o.id rather than * because, for Arjun, the order columns are NULL and COUNT(column) skips NULLs. A handy variation finds customers who have never ordered:

SELECT c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL;

Result: one row, Arjun.

JOIN with GROUP BY

Revenue per city combines a join and grouping. Plain JOIN means INNER JOIN:

SELECT c.city, SUM(o.amount) AS revenue
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.city
ORDER BY revenue DESC;

Result:

Join types at a glance

UPDATE

UPDATE orders SET amount = 1299.00 WHERE id = 101;

PostgreSQL replies UPDATE 1 (one row changed). Checking it:

SELECT id, product, amount FROM orders WHERE id = 101;

Result:

DELETE

DELETE FROM orders WHERE amount < 500;

PostgreSQL replies DELETE 1: only the USB cable order (299.00) was below 500.

SELECT COUNT(*) AS remaining_orders FROM orders;

Result:

Warning: UPDATE or DELETE without a WHERE clause changes every row. A safe habit is to run the same WHERE as a SELECT first to see which rows will be affected. On important data, wrap changes in a transaction (BEGIN; ... COMMIT; or ROLLBACK;). Our article on transactions and locking explains how.

SQL quick reference

The logical order in which a query is evaluated helps explain many errors: FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. That is why WHERE cannot use an aggregate, but HAVING can.

Where to go next

Once these basics feel natural, learn indexes to keep queries fast (MySQL indexes and EXPLAIN) and window functions for rankings and running totals. To expose your data to apps, see REST APIs explained, and keep your schema scripts in Git.

FAQ

Is SQL a programming language?

It is a declarative query language: you describe the result you want and the database works out how to get it. It is a core skill alongside general-purpose languages such as Python or JavaScript.

PostgreSQL, MySQL or SQLite for learning?

All three are free. SQLite is the easiest to start with because it needs no server. PostgreSQL ranked as the most used database in the Stack Overflow Developer Survey 2026, and MySQL remains very common, especially in PHP stacks.

Are SQL keywords case-sensitive?

Keywords are not: SELECT and select are the same. Writing keywords in capitals is just a readability convention. Whether string comparisons are case-sensitive depends on the database and its settings.

What is NULL?

NULL means “no value” or “unknown”. It is not zero or an empty string. Test for it with IS NULL or IS NOT NULL, never with = NULL.

Sources

• SQL as understood by SQLite (sqlite.org)
• PostgreSQL tutorial: the SQL language (postgresql.org)
• PostgreSQL versioning policy (postgresql.org)
• Stack Overflow Developer Survey 2026: Technology