Основы SQL: SELECT, WHERE, GROUP BY, HAVING, ORDER BY и порядок выполнения запроса?

CREATE TABLE customers (
    id       SERIAL PRIMARY KEY,
    name     VARCHAR(200) NOT NULL,
    email    VARCHAR(320) NOT NULL UNIQUE,
    city     VARCHAR(100)
);

CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(id),     -- внешний ключ
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    status      VARCHAR(20) NOT NULL DEFAULT 'New',
    total       NUMERIC(12, 2) NOT NULL CHECK (total >= 0)
);

SELECT id, name, city
FROM customers
WHERE city = 'Ташкент'
  AND email LIKE '%@gmail.com'          -- шаблон: % — любые символы, _ — один символ
ORDER BY name
LIMIT 20 OFFSET 40;                     -- 3-я страница по 20 (SQL Server: OFFSET ... FETCH)

SELECT * FROM orders WHERE status IN ('New', 'Paid');
SELECT * FROM orders WHERE total BETWEEN 100000 AND 500000;
SELECT * FROM customers WHERE city IS NULL;            -- НЕ city = NULL

SELECT c.city,
       COUNT(*)          AS orders_count,
       SUM(o.total)      AS revenue,
       AVG(o.total)      AS avg_check
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'Paid'                 -- фильтр строк ДО группировки
GROUP BY c.city
HAVING COUNT(*) > 10                    -- фильтр групп ПОСЛЕ группировки
ORDER BY revenue DESC;

SELECT NULL = NULL;                 -- NULL (не TRUE!)
SELECT * FROM customers WHERE city <> 'Ташкент';   -- строки с city = NULL НЕ попадут
SELECT * FROM customers WHERE city <> 'Ташкент' OR city IS NULL;

SELECT name, COALESCE(city, 'не указан') AS city FROM customers;   -- замена NULL

INSERT INTO customers (name, email, city) VALUES ('Анна', 'anna@mail.uz', 'Самарканд')
RETURNING id;                                  -- PostgreSQL: получить сгенерированный id

UPDATE orders SET status = 'Cancelled' WHERE id = 42;     -- без WHERE изменятся ВСЕ строки!

DELETE FROM orders WHERE status = 'Draft' AND created_at < now() - INTERVAL '30 days';

BEGIN;                                         -- транзакция: всё или ничего
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

SELECT c.*
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Заказы дороже среднего
SELECT * FROM orders WHERE total > (SELECT AVG(total) FROM orders);