SQL и PostgreSQL: от запроса к схеме
Все объяснения, задачи и лабораторная в одном месте.
Можно сохранить страницу в PDF через печать браузера. Для печати разборы ответов раскрываются автоматически.
1. SELECT как конвейер
Объяснение
SQL описывает требуемый набор данных. Логически FROM и WHERE определяют строки до SELECT; ORDER BY задаёт порядок, который иначе не гарантирован. NULL означает неизвестность, поэтому проверяется IS NULL.
Задача для самостоятельного решения
Почему WHERE price = NULL не находит пустые цены?
Показать разбор ответа
Сравнение с NULL даёт unknown, а WHERE оставляет только true. Используйте price IS NULL; ноль и неизвестная цена — разные значения.
2. JOIN и кардинальность
Объяснение
JOIN соединяет строки по условию. INNER оставляет совпадения, LEFT сохраняет все строки слева и добавляет NULL при отсутствии справа. Условие справа в WHERE может убрать эти сохранённые строки.
Задача для самостоятельного решения
У клиента три заказа. Сколько строк даст JOIN клиента с заказами?
Показать разбор ответа
Три: одна строка на пару. Агрегаты клиента после JOIN могут повторяться; считайте на правильной гранулярности.
3. Агрегаты и оконные функции
Объяснение
GROUP BY объединяет строки в группы, HAVING фильтрует агрегаты. Window function считает по окну, сохраняя отдельные строки. Порядок внутри окна определяет running total и ранги.
Задача для самостоятельного решения
Чем SUM(amount) OVER() отличается от обычной SUM(amount)?
Показать разбор ответа
Оконная сумма повторяется в каждой строке исходного набора; обычная агрегатная сумма без GROUP BY возвращает одну строку.
4. Транзакции и индексы
Объяснение
Транзакция объединяет изменения в единый результат. Индекс ускоряет определённый доступ ценой записи и места. Уникальное ограничение предотвращает дубликаты даже при конкурентных запросах.
Задача для самостоятельного решения
Почему предварительный SELECT не гарантирует уникальность логина?
Показать разбор ответа
Два запроса могут одновременно не найти логин. UNIQUE в БД атомарно отклонит вторую вставку; приложение должно обработать конфликт.
Лабораторная работа
Подготовка
PostgreSQL в учебной базе. Выполните весь пример в одной SQL-сессии; TEMP-таблицы удаляются при закрытии.
Учебный пример
CREATE TEMP TABLE customers(id int PRIMARY KEY, name text);
CREATE TEMP TABLE orders(id int PRIMARY KEY, customer_id int, amount numeric(10,2));
INSERT INTO customers VALUES (1,'Анна'),(2,'Борис');
INSERT INTO orders VALUES (10,1,100),(11,1,150);
SELECT c.name, COUNT(o.id) AS orders, COALESCE(SUM(o.amount),0) AS total
FROM customers c LEFT JOIN orders o ON o.customer_id=c.id
GROUP BY c.id,c.name ORDER BY c.id;Как работает пример и что ожидать
Ожидаются Анна: 2 заказа и 250; Борис: 0 и 0. COUNT(o.id) не считает NULL, появившийся у Бориса, тогда как COUNT(*) дал бы 1. COALESCE превращает неизвестную сумму пустого набора в нужный по задаче ноль. LEFT JOIN сохраняет клиента без заказа.
Итоговая работа
Добавьте товары и позиции, внешние ключи и ограничения сумм. Напишите отчёт без двойного учёта заказа после JOIN. Продемонстрируйте ROLLBACK перевода и объясните, какой индекс помогает выбранному WHERE по дате.
Проверка результата
1. Опишите исходные данные и условия запуска, чтобы другой человек мог повторить работу.
2. Приложите результат обычного сценария и сравните его с ожидаемым.
3. Проверьте неверный вход, граничный случай и отказ зависимости, если она есть.
4. Объясните выбранное решение и известное ограничение.
5. Сохраните исправления после самопроверки вместе с примером, который раньше не работал.
Как оценить работу
По каждому пункту поставьте 0 (не выполнено), 1 (выполнено с пробелами) или 2 (результат воспроизводим и объяснён). Если обязательный сценарий не работает, вернитесь к нему независимо от общей суммы. Это рубрика самопроверки: сайт не исполняет присланный код и не выдаёт автоматическую оценку проекта.