Практикум shop_data

ОБЯЗАТЕЛЬНОДЛЯ НОВИЧКОВ

Разработчику Аналитику Архитектору

Схема shop_data — единая учебная база для практики в PostgreSQL (DBeaver, psql). Запросы ниже соответствуют планам занятий курса и папке Examples. В браузерном SQL-тренажёре доступна та же схема shop_data плюс users и cities для JOIN. На сервере PostgreSQL выполняйте те же запросы в DBeaver/psql.

---

Подготовка схемы

CREATE SCHEMA IF NOT EXISTS shop_data;

CREATE TABLE IF NOT EXISTS shop_data.customers (
    customer_id SERIAL PRIMARY KEY,
    full_name   TEXT NOT NULL,
    email       TEXT UNIQUE,
    city        TEXT,
    created_at  TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS shop_data.products (
    product_id SERIAL PRIMARY KEY,
    name       TEXT NOT NULL,
    price      NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
    category   TEXT
);

CREATE TABLE IF NOT EXISTS shop_data.orders (
    order_id    SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES shop_data.customers(customer_id),
    order_date  DATE DEFAULT CURRENT_DATE,
    status      TEXT DEFAULT 'new'
);

CREATE TABLE IF NOT EXISTS shop_data.order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id      INT NOT NULL REFERENCES shop_data.orders(order_id),
    product_id    INT NOT NULL REFERENCES shop_data.products(product_id),
    quantity      INT NOT NULL CHECK (quantity > 0),
    unit_price    NUMERIC(10, 2) NOT NULL,
    UNIQUE (order_id, product_id)
);

-- Демо-данные
INSERT INTO shop_data.customers (full_name, email, city) VALUES
  ('Анна Иванова', 'anna@example.com', 'Москва'),
  ('Борис Петров', 'boris@example.com', 'СПб')
ON CONFLICT DO NOTHING;

INSERT INTO shop_data.products (name, price, category) VALUES
  ('SQL для профессионалов', 1299.00, 'Книги'),
  ('USB-кабель', 299.00, 'Аксессуары')
ON CONFLICT DO NOTHING;

Проверка подключения:

SELECT version();
SELECT current_database(), current_user;
SELECT pg_size_pretty(pg_database_size(current_database()));

---

SELECT и фильтрация (уроки 10–11)

SELECT product_id, name, price
FROM shop_data.products
WHERE category = 'Книги' AND price BETWEEN 500 AND 2000;

SELECT DISTINCT category FROM shop_data.products;

SELECT name, price
FROM shop_data.products
WHERE name ILIKE '%кабель%'
ORDER BY price DESC NULLS LAST;

---

Агрегаты и GROUP BY (урок 13)

SELECT
    category,
    COUNT(*) AS cnt,
    AVG(price) AS avg_price,
    MAX(price) AS max_price
FROM shop_data.products
GROUP BY category
HAVING COUNT(*) >= 1;

---

JOIN (урок 14)

SELECT
    o.order_id,
    c.full_name,
    o.order_date,
    oi.quantity,
    p.name AS product_name
FROM shop_data.orders o
JOIN shop_data.customers c ON c.customer_id = o.customer_id
JOIN shop_data.order_items oi ON oi.order_id = o.order_id
JOIN shop_data.products p ON p.product_id = oi.product_id
ORDER BY o.order_date DESC;

---

Подзапросы и EXISTS (урок 15)

SELECT c.full_name
FROM shop_data.customers c
WHERE EXISTS (
    SELECT 1 FROM shop_data.orders o WHERE o.customer_id = c.customer_id
);

SELECT name, price
FROM shop_data.products p
WHERE price > (
    SELECT AVG(price) FROM shop_data.products p2 WHERE p2.category = p.category
);

---

Оконные функции (урок 19)

SELECT
    order_id,
    customer_id,
    order_date,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_num,
    SUM(oi.quantity * oi.unit_price) OVER (
        PARTITION BY o.customer_id
        ORDER BY o.order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_spent
FROM shop_data.orders o
JOIN shop_data.order_items oi ON oi.order_id = o.order_id;

---

Системные каталоги (урок 9)

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'shop_data'
ORDER BY table_name;

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'shop_data' AND table_name = 'products'
ORDER BY ordinal_position;

Подробнее: Словарь данных и системные каталоги.

---

План и статистика (уроки 28–29)

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM shop_data.orders WHERE customer_id = 1;

SELECT schemaname, relname, last_analyze, n_live_tup, n_mod_since_analyze
FROM pg_stat_user_tables
WHERE schemaname = 'shop_data';

ANALYZE shop_data.orders;

---

Транзакции и блокировки (уроки 22–23)

BEGIN;
UPDATE shop_data.orders SET status = 'processing' WHERE order_id = 1;
COMMIT;

BEGIN;
SELECT order_id FROM shop_data.orders
WHERE status = 'new'
FOR UPDATE SKIP LOCKED
LIMIT 1;
COMMIT;

Подробнее: Блокировки и конкурентный доступ.

---

Резервное копирование (урок 30)

pg_dump -Fc -f shop_data.dump -n shop_data your_database
pg_restore -l shop_data.dump

Подробнее: Резервное копирование и восстановление.

---

Карта тем курса

Урок	Тема	Статья в энциклопедии
1–3	Введение, модель	102, 103
4	Нормализация	104
5–8	Типы, DDL, ограничения	33, 44, 444
9	Каталоги	105
10–12	SELECT, фильтр, сортировка	107, 109, 6
13–17	Агрегаты, JOIN, CTE	6, 55, 551
15	Подзапросы	108
18	CRUD	5
19	Окна	7
20	JSON, массивы	66
21–24	VIEW, транзакции, процедуры	8, 77, 88
25–27	Индексы	884, 881
28–29	EXPLAIN, ANALYZE	881
30–31	Backup, права	106, 4

Полная шпаргалка синтаксиса: 885, справочник: 883.

---