SQL for beginners: the first queries that bring results

What is SQL in a nutshell?

SQL (Structured Query Language) — a language to ask the database about facts: "How many orders?", "Which products are more expensive than 50?", "Who hasn't made a single purchase?".

Basic set of commands for reading:

• SELECT — which columns to take;

• FROM — from which table;

• WHERE — which lines to leave;

• ORDER BY — how to sort;

• LIMIT — how many rows to return;

• JOIN — how to merge tables;

• GROUP BY + aggregates (COUNT, SUM, AVG, MIN, MAX) — group and count.

Our mini-base for examples

Table users

Table products

Table orders

The very first request:

SELECT * 
FROM users;

SELECT name, city 
FROM users;

Filtering: WHERE

SELECT name, email
FROM users
WHERE city = 'Moscow';

SELECT title, price
FROM products
WHERE price > 20;

SELECT title, price
FROM products
WHERE price BETWEEN 20 AND 150;

SELECT name, city
FROM users
WHERE city IN ('Moscow', 'Saint-P');

SELECT email
FROM users
WHERE email LIKE '%@example.com';

SELECT name
FROM users
WHERE city IS NULL;

Sorting and limiting: ORDER BY + LIMIT

SELECT title, price
FROM products
ORDER BY price ASC
LIMIT 2;

SELECT id, user_id, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 5;

Unique values: DISTINCT

SELECT DISTINCT city
FROM users
WHERE city IS NOT NULL;

Groupings and aggregates: GROUP BY

SELECT user_id, COUNT(*) AS orders_count
FROM orders
GROUP BY user_id;

SELECT p.title, SUM(o.qty * p.price) AS total_revenue
FROM orders o
JOIN products p ON p.id = o.product_id
GROUP BY p.title
ORDER BY total_revenue DESC;

SELECT p.title, SUM(o.qty * p.price) AS total_revenue
FROM orders o
JOIN products p ON p.id = o.product_id
GROUP BY p.title
HAVING SUM(o.qty * p.price) > 100;

Simple JOINs without pain

SELECT u.name, p.title, o.qty, o.created_at
FROM orders o
JOIN users u    ON u.id = o.user_id
JOIN products p ON p.id = o.product_id
ORDER BY o.created_at DESC;

SELECT u.name, MAX(o.created_at) AS last_order
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.name
ORDER BY last_order DESC NULLS LAST;

12 quick recipes for work

-- 1. Последние 5 заказов
SELECT * FROM orders ORDER BY created_at DESC LIMIT 5;

-- 2. Пользователи с доменом example.com
SELECT name, email FROM users WHERE email LIKE '%@example.com';

-- 3. Топ-3 товара по выручке
SELECT p.title, SUM(o.qty * p.price) AS revenue
FROM orders o JOIN products p ON p.id = o.product_id
GROUP BY p.title
ORDER BY revenue DESC
LIMIT 3;

-- 4. Пользователи без города
SELECT * FROM users WHERE city IS NULL;

-- 5. Товары в ценовом коридоре
SELECT * FROM products WHERE price BETWEEN 20 AND 150;

-- 6. Количество заказов по дням
SELECT DATE(created_at) AS day, COUNT(*) AS cnt
FROM orders
GROUP BY DATE(created_at)
ORDER BY day;

-- 7. Сколько разных городов
SELECT COUNT(DISTINCT city) FROM users WHERE city IS NOT NULL;

-- 8. Сумма корзины конкретного заказа
SELECT SUM(o.qty * p.price) AS total
FROM orders o JOIN products p ON p.id = o.product_id
WHERE o.id = :id;

-- 9. Все товары, которые покупал Иван
SELECT p.title
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN products p ON p.id = o.product_id
WHERE u.name = 'Ivan Petrov';

-- 10. Заказы за последние 7 дней
SELECT * FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '7 DAY';

-- 11. Первые N строк для быстрой проверки
SELECT * FROM users LIMIT 10;

-- 12. Уникальные домены email
SELECT DISTINCT SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain
FROM users;

If you want practice and short explanations, Code we analyze SQL on live tasks, from simple samples to analytics with aggregations. No water, with interactive.

We also have an active Telegram channel, where we discuss cool ideas, share experiences and analyze tasks together — learning becomes not only useful, but also fun.

Question to you: Which part of SQL is the most frightening: JOIN, GROUP BY or the mysterious NULL? Write what you want to analyze in the next article.