Шесть книг. Четыре читателя. Семь выдач. Пройдите путь от первого запроса до отчёта — SQL выполняется прямо здесь, в учебной SQLite.
← Вернуться к маршруту SQLПройдено 0 из 7 заданий
1. Выберите нужные столбцы
SELECT определяет столбцы результата, FROM — таблицу. Звёздочка возвращает все столбцы: это удобно для знакомства, но в рабочем запросе лучше явно назвать нужные.
В учебной библиотеке books хранит книги, readers — читателей, loans — выдачи. Начнём с одной таблицы.
Ваш запросПокажите только title всех шести книг. Порядок строк пока не важен.
Подсказка
Вместо * укажите имя столбца title.
Разобрать решение
SELECT title FROM books;Прочитать таблицу и выбрать только нужный столбец.
2. Отберите строки
WHERE оставляет строки, для которых условие истинно. AND требует выполнения обоих условий, OR — хотя бы одного.
Сначала решите, какие строки должны остаться, затем запишите условие. Названия столбцов не берут в одинарные кавычки: ими обозначают текстовые значения.
Ваш запросВерните title и published_year книг, изданных после 2020 года, но не позднее 2023 года. Порядок не важен.
Подсказка
Добавьте второе условие через AND: верхняя граница включается.
Разобрать решение
SELECT title, published_year FROM books WHERE published_year > 2020 AND published_year <= 2023;Отобрать строки по двум условиям WHERE.
3. Задайте порядок
Без ORDER BY база не обещает порядок результата. ASC сортирует по возрастанию, DESC — по убыванию.
LIMIT ограничивает число возвращённых строк. Для «самых новых» сначала задают порядок, затем берут нужное количество.
Ваш запросВыведите title и published_year трёх самых новых книг — от новой к старой.
Подсказка
Измените направление сортировки на DESC и добавьте LIMIT 3.
Разобрать решение
SELECT title, published_year FROM books ORDER BY published_year DESC LIMIT 3;Упорядочить результат и ограничить его размер.
4. Найдите неизвестное
NULL обозначает отсутствие известного значения. Он отличается от нуля, пустой строки и слова 'NULL'. Для проверки используют IS NULL или IS NOT NULL.
В loans значение returned_at равно NULL, пока возврат не отмечен. Сравнение returned_at = NULL не даёт нужного условия.
Ваш запросПокажите id, book_id и reader_id всех ещё не возвращённых выдач. Отсортируйте по id по возрастанию.
Подсказка
Замените сравнение = NULL на IS NULL.
Разобрать решение
SELECT id, book_id, reader_id FROM loans WHERE returned_at IS NULL ORDER BY id;Правильно отобрать отсутствующее значение через IS NULL.
5. Соедините данные
JOIN собирает сведения из связанных таблиц. Внешний ключ loans.book_id указывает на books.id, а loans.reader_id — на readers.id.
У одной книги может быть несколько выдач. Поэтому книга может встретиться в результате несколько раз: одна строка здесь обозначает выдачу, а не уникальную книгу.
Ваш запросДля каждой из семи выдач получите loan_id, title книги и reader — имя читателя. Отсортируйте по loan_id.
Подсказка
Добавьте JOIN readers ON readers.id = loans.reader_id и readers.name AS reader.
Разобрать решение
SELECT loans.id AS loan_id, books.title, readers.name AS reader FROM loans JOIN books ON books.id = loans.book_id JOIN readers ON readers.id = loans.reader_id ORDER BY loans.id;Соединить выдачи с книгами и читателями по ключам.
6. Посчитайте по группам
GROUP BY объединяет строки по выбранному ключу. COUNT(*) считает строки внутри каждой группы, а не число разных значений.
WHERE фильтрует исходные строки, HAVING — уже собранные группы. В этом задании нужны все выдачи, включая возвращённые.
Ваш запросПолучите reader_id и loan_count — количество выдач каждого читателя. Сначала читатели с большим числом выдач, при равенстве — меньший reader_id.
Подсказка
Добавьте GROUP BY reader_id и ORDER BY loan_count DESC, reader_id ASC.
Разобрать решение
SELECT reader_id, COUNT(*) AS loan_count FROM loans GROUP BY reader_id ORDER BY loan_count DESC, reader_id ASC;Получить агрегат для каждой группы и отсортировать его.
7. Соберите полный отчёт
LEFT JOIN сохраняет все строки левой таблицы. Если выдач нет, столбцы loans будут NULL.
COUNT(*) посчитает и такую строку, а COUNT(loans.id) считает только известные идентификаторы выдач. Поэтому для книги без выдач получится ноль.
Ваш запросВерните book_id, title и loan_count для всех шести книг, включая книги без выдач. Отсортируйте по book_id. В GROUP BY перечислите books.id и books.title — это переносимый вариант и для PostgreSQL.
Подсказка
Сохранить книги без выдач поможет LEFT JOIN. Нужен COUNT(loans.id), чтобы NULL не считался выдачей.
Разобрать решение
SELECT books.id AS book_id, books.title, COUNT(loans.id) AS loan_count FROM books LEFT JOIN loans ON loans.book_id = books.id GROUP BY books.id, books.title ORDER BY books.id;Сохранить строки без связей и корректно посчитать нулевые значения.
Ctrl + Enter · Выполнение в браузере, без отправки запроса на сервер
Измените запрос и нажмите «Выполнить».
SQLite и PostgreSQL: что переносится
Здесь работает настоящая SQLite в памяти браузера. SELECT, WHERE, JOIN и GROUP BY из этих уроков применимы и в PostgreSQL. Строгая типизация, работа с датами, ILIKE, планы индексов и конкурентные транзакции требуют отдельной проверки в PostgreSQL. Этот тренажёр не выдаёт исполнение SQLite за проверку PostgreSQL.
Все данные вымышленные. Запросы, ответы и прогресс не сохраняются. Движок — sql.js 1.15.1, поставляется вместе с сайтом; внешняя база и аккаунт не нужны.
Связи и модель данных
Каждая выдача ссылается на существующую книгу и читателя. В этой маленькой модели одна строка books обозначает одну книгу из каталога. Если нужно выдавать несколько физических экземпляров одного названия, добавьте отдельную сущность экземпляра: не копируйте название во все выдачи.
Во внешнем ключе хранится идентификатор, а название и имя берутся через JOIN. UNIQUE, NOT NULL и CHECK закрепляют правила данных независимо от интерфейса.
Дальше — индексы
Для частого поиска выдач одного читателя стоит проверить индекс по reader_id. Индекс занимает место и увеличивает стоимость записи; его полезность проверяют по реальному плану запроса и распределению данных. Шесть учебных книг не доказывают ускорения рабочей базы.
Дальше — транзакции
Несколько связанных изменений объединяют в транзакцию, чтобы не оставить половинный результат. При параллельной работе нужны ограничения и выбранный уровень изоляции. Курс выше упражняет чтение; изменения и конкурентные транзакции — следующий этап работы с базами.