Практикум shop_data
Схема shop_data — единая учебная база для практики в PostgreSQL (DBeaver, psql). Запросы ниже соответствуют планам занятий курса и папке Examples. В браузерном SQL-тренажёре доступна та же схема shop_data плюс users и cities для JOIN. На сервере PostgreSQL выполняйте те же запросы в DBeaver/psql.
Для тех же таблиц с построчным разбором, ожидаемыми цифрами и заданиями "попробуйте" (удобно для лабораторной и поиска в Google) — SQL — реальные кейсы.
Подготовка схемы
Код ITЗагрузка примера кода…
Проверка подключения:
SELECT version();
SELECT current_database(), current_user;
SELECT pg_size_pretty(pg_database_size(current_database()));
SELECT и фильтрация
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
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
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
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
);
Оконные функции
Полный разбор — Встроенные и оконные функции в SQL (ранжирование, накопительные суммы, доли, LAG/LEAD, отчёты без Excel, кейсы).
Общая база: именованный вызов с аргументами и возвратом в коде — функции в коде. В SQL выражения
COUNT(),SUM(),OVER— тот же приём на уровне запроса, другой синтаксис.
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;
Системные каталоги
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;
Подробнее: Словарь данных и системные каталоги.
План и статистика
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;
Транзакции и блокировки
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;
Подробнее: Блокировки и конкурентный доступ (очередь на одну задачу) и пакетная обработка.
Резервное копирование
pg_dump -Fc -f shop_data.dump -n shop_data your_database
pg_restore -l shop_data.dump
Подробнее: Резервное копирование и восстановление.
Карта тем курса
Полная шпаргалка синтаксиса: Шпаргалка с типичными задачами по SQL, справочник: Справочник по SQL. Галерея кейсов с разбором для учебы: SQL — реальные кейсы.