Практика: создание базы «Библиотека»
Мы прошли три лекции по базам данных. Теперь — время практики. Мы создадим базу данных «Библиотека» с нуля: от пустого окна SSMS до готовых запросов с JOIN и агрегатными функциями.
🗄️ БД
📋 SSMS
🏗️ CREATE TABLE
🔗 FOREIGN KEY
📊 JOIN
📈 Агрегация
Мы создадим четыре связанные таблицы:
Authors, Books, Readers, Loans. Наполним их данными и напишем запросы для настоящих отчётов.
Формат: практическая работа, 2 академических часа. Все запросы проверяются в SSMS сразу после ввода.
Цель и задачи
Цель: получить практические навыки создания базы данных и написания SQL-запросов в MS SQL Server.
Что мы сделаем
- 1 Создадим базу данных
LibraryDB. - 2 Создадим таблицы
Authors,Books,Readers,Loans. - 3 Настроим первичные и внешние ключи.
- 4 Заполним таблицы тестовыми данными.
- 5 Напишем запросы с
JOIN,GROUP BY,HAVING. - 6 Построим отчёт: кто какие книги брал и когда.
В результате получится мини-база данных реального библиотечного приложения.
Схема базы данных
Вот как устроена наша база. Четыре таблицы, три связи.
Authors (авторы)
↓ 1 : много
Books (книги, AuthorId → Authors.Id)
↓ 1 : много
Loans (выдачи, BookId → Books.Id, ReaderId → Readers.Id)
↑ много : 1
Readers (читатели)
ТаблицаЧто хранитСвязь
Authorsавторы книг—
BooksкнигиAuthorId → Authors.Id
Readersчитатели библиотеки—
Loansвыдачи книг читателямBookId → Books.Id, ReaderId → Readers.Id
Один автор может написать много книг. Одна книга может быть выдана много раз. Один читатель может брать много книг. Это классическая схема «многие-ко-многим» через промежуточную таблицу
Loans.Шаг 1: запускаем SSMS
Открываем SQL Server Management Studio (SSMS — программа для работы с MS SQL Server).
Что делаем
- Запускаем SSMS.
- В окне подключения вводим параметры:
- Server type: Database Engine
- Server name:
localhost\SQLEXPRESS(или имя вашего экземпляра) - Authentication: Windows Authentication
- Нажимаем Connect.
Если подключение прошло, слева появится дерево «Object Explorer» с базами данных, которые уже есть на сервере.
Если подключение не проходит — проверьте, что служба «SQL Server (SQLEXPRESS)» запущена в «Службах» Windows (Win+R →
services.msc).Шаг 2: создаём базу данных
В SSMS нажимаем New Query — открывается окно для SQL-запросов. Пишем первую команду.
-- Создаём новую базу данных
CREATE DATABASE LibraryDB;
GO
-- Переключаемся на неё
USE LibraryDB;
GO
CREATE DATABASE LibraryDB— создаёт базу данных с именем LibraryDB.GO— разделитель пакетов команд (в MS SQL Server).USE LibraryDB— говорим серверу: «дальше работаем в этой базе».
Нажмите F5 или кнопку «Execute». Внизу появится «Commands completed successfully». База создана.
Шаг 3: таблица Authors
Создаём таблицу авторов. У неё будет только идентификатор и имя — этого достаточно.
CREATE TABLE Authors (
Id INT PRIMARY KEY IDENTITY(1,1),
Name NVARCHAR(100) NOT NULL,
Country NVARCHAR(50)
);
СтолбецТипЧто значит
IdINT PRIMARY KEY IDENTITYуникальный номер, автоматически
NameNVARCHAR(100) NOT NULLимя автора, обязательно
CountryNVARCHAR(50)страна (можно не заполнять)
IDENTITY(1,1) означает: первая запись получит Id = 1, каждая следующая — на 1 больше.Шаг 4: таблица Books
Таблица книг. У каждой книги есть автор — ссылка на Authors.Id.
CREATE TABLE Books (
Id INT PRIMARY KEY IDENTITY(1,1),
Title NVARCHAR(200) NOT NULL,
Year INT,
AuthorId INT NOT NULL,
FOREIGN KEY (AuthorId) REFERENCES Authors(Id)
);
СтолбецТипЧто значит
IdINT PRIMARY KEY IDENTITYуникальный номер книги
TitleNVARCHAR(200) NOT NULLназвание книги
YearINTгод издания
AuthorIdINT NOT NULL + FOREIGN KEYссылка на автора
Внешний ключ
FOREIGN KEY (AuthorId) REFERENCES Authors(Id) гарантирует: нельзя добавить книгу с несуществующим автором.Шаг 5: таблица Readers
Таблица читателей. Здесь хранятся те, кто берёт книги в библиотеке.
CREATE TABLE Readers (
Id INT PRIMARY KEY IDENTITY(1,1),
Name NVARCHAR(100) NOT NULL,
Email NVARCHAR(100),
Registered DATE
);
СтолбецТипЧто значит
IdINT PRIMARY KEY IDENTITYуникальный номер читателя
NameNVARCHAR(100) NOT NULLимя читателя
EmailNVARCHAR(100)электронная почта
RegisteredDATEдата регистрации
Дата хранится в формате
YYYY-MM-DD. Например, '2026-10-24' — 24 октября 2026 года.Шаг 6: таблица Loans
Таблица выдач. Она связывает книги и читателей: кто какую книгу взял и когда вернул.
CREATE TABLE Loans (
Id INT PRIMARY KEY IDENTITY(1,1),
BookId INT NOT NULL,
ReaderId INT NOT NULL,
LoanDate DATE NOT NULL,
ReturnDate DATE,
FOREIGN KEY (BookId) REFERENCES Books(Id),
FOREIGN KEY (ReaderId) REFERENCES Readers(Id)
);
СтолбецТипЧто значит
BookIdINT NOT NULL + FOREIGN KEYкакая книга
ReaderIdINT NOT NULL + FOREIGN KEYкакой читатель
LoanDateDATE NOT NULLкогда выдана
ReturnDateDATEкогда возвращена (NULL — если ещё на руках)
Два внешних ключа в одной таблице — это нормально. Так строятся связи «многие-ко-многим»: книга может быть выдана много раз, читатель может брать много книг.
Шаг 7: заполняем Authors
Добавим трёх авторов. Id не указываем — его автоматически присвоит IDENTITY.
INSERT INTO Authors (Name, Country) VALUES
(N'Александр Пушкин', N'Россия'),
(N'Лев Толстой', N'Россия'),
(N'Джоан Роулинг', N'Великобритания'),
(N'Стивен Кинг', N'США');
Проверим результат:
SELECT * FROM Authors;
В таблице 4 строки. У Пушкина Id = 1, у Толстого — 2, у Роулинг — 3, у Кинга — 4.
Шаг 8: заполняем Books
Добавим книги. Каждая привязана к автору через AuthorId.
INSERT INTO Books (Title, Year, AuthorId) VALUES
(N'Евгений Онегин', 1833, 1),
(N'Война и мир', 1869, 2),
(N'Анна Каренина', 1877, 2),
(N'Гарри Поттер и философский камень', 1997, 3),
(N'Оно', 1986, 4),
(N'Сияние', 1977, 4);
Проверим:
SELECT Id, Title, Year, AuthorId FROM Books;
Если попробовать добавить книгу с
AuthorId = 99, которого нет в Authors, — база выдаст ошибку внешнего ключа. Это защита от «сиротских» записей.Шаг 9: заполняем Readers
Добавим читателей библиотеки.
INSERT INTO Readers (Name, Email, Registered) VALUES
(N'Иван Петров', N'ivan@mail.ru', '2026-09-01'),
(N'Мария Сидорова', N'maria@mail.ru', '2026-09-05'),
(N'Пётр Иванов', N'petr@mail.ru', '2026-09-10'),
(N'Ольга Смирнова', N'olga@mail.ru', '2026-09-15');
Проверим:
SELECT * FROM Readers;
Даты записываются в кавычках в формате
'YYYY-MM-DD'. Это стандартный формат для MS SQL Server.Шаг 10: заполняем Loans
Добавим записи о выдачах книг. Здесь есть и возвращённые книги, и те, что ещё на руках.
INSERT INTO Loans (BookId, ReaderId, LoanDate, ReturnDate) VALUES
(1, 1, '2026-09-20', '2026-10-01'), -- Онегин → Иван, возвращена
(2, 2, '2026-09-25', '2026-10-15'), -- Война и мир → Мария, возвращена
(4, 1, '2026-10-05', NULL), -- Гарри Поттер → Иван, ещё на руках
(5, 3, '2026-10-10', NULL), -- Оно → Пётр, ещё на руках
(3, 2, '2026-10-12', '2026-10-20'), -- Анна Каренина → Мария, возвращена
(4, 4, '2026-10-18', NULL); -- Гарри Поттер → Ольга, ещё на руках
Книга с
BookId = 4 («Гарри Поттер») выдана дважды — Ивану и Ольге. Это нормально: одну и ту же книгу могут брать разные читатели в разное время.Проверка: что получилось
Прежде чем писать сложные запросы, убедимся, что данные на месте.
SELECT COUNT(*) AS AuthorsCount FROM Authors;
SELECT COUNT(*) AS BooksCount FROM Books;
SELECT COUNT(*) AS ReadersCount FROM Readers;
SELECT COUNT(*) AS LoansCount FROM Loans;
ТаблицаОжидаемое количество
Authors4
Books6
Readers4
Loans6
Если все числа совпадают — база готова. Переходим к запросам.
Запрос 1: книги с авторами
Первый запрос — соединяем Books и Authors, чтобы увидеть название книги и имя автора.
SELECT
b.Title AS Книга,
a.Name AS Автор,
b.Year AS Год
FROM Books b
INNER JOIN Authors a ON b.AuthorId = a.Id
ORDER BY a.Name, b.Year;
Результат:
КнигаАвторГод
Евгений ОнегинАлександр Пушкин1833
Война и мирЛев Толстой1869
Анна КаренинаЛев Толстой1877
Гарри Поттер...Джоан Роулинг1997
ОноСтивен Кинг1986
СияниеСтивен Кинг1977
Запрос 2: книги Толстого
Найдём все книги Льва Толстого.
SELECT b.Title AS Книга, b.Year AS Год
FROM Books b
INNER JOIN Authors a ON b.AuthorId = a.Id
WHERE a.Name = N'Лев Толстой';
Результат:
КнигаГод
Война и мир1869
Анна Каренина1877
Обратите внимание:
WHERE применяется к результатам соединения. Мы ищем по имени автора, но оно находится в таблице Authors.Запрос 3: кто что брал
Соединяем три таблицы: книги, читатели и выдачи.
SELECT
r.Name AS Читатель,
b.Title AS Книга,
l.LoanDate AS ДатаВыдачи
FROM Loans l
INNER JOIN Readers r ON l.ReaderId = r.Id
INNER JOIN Books b ON l.BookId = b.Id
ORDER BY l.LoanDate;
Результат:
ЧитательКнигаДатаВыдачи
Иван ПетровЕвгений Онегин2026-09-20
Мария СидороваВойна и мир2026-09-25
Иван ПетровГарри Поттер...2026-10-05
Пётр ИвановОно2026-10-10
Мария СидороваАнна Каренина2026-10-12
Ольга СмирноваГарри Поттер...2026-10-18
Мы соединили три таблицы через два
INNER JOIN. Это стандартный приём в SQL.Запрос 4: невозвращённые книги
Найдём книги, которые ещё на руках. У них ReturnDate = NULL.
SELECT
r.Name AS Читатель,
b.Title AS Книга,
l.LoanDate AS ДатаВыдачи
FROM Loans l
INNER JOIN Readers r ON l.ReaderId = r.Id
INNER JOIN Books b ON l.BookId = b.Id
WHERE l.ReturnDate IS NULL;
Результат:
ЧитательКнигаДатаВыдачи
Иван ПетровГарри Поттер...2026-10-05
Пётр ИвановОно2026-10-10
Ольга СмирноваГарри Поттер...2026-10-18
Важно: для проверки на
NULL нельзя использовать = NULL. Это всегда даст пустой результат. Нужно IS NULL или IS NOT NULL.Запрос 5: сколько книг у каждого автора
Используем GROUP BY и COUNT, чтобы посчитать книги по авторам.
SELECT
a.Name AS Автор,
COUNT(b.Id) AS КоличествоКниг
FROM Authors a
LEFT JOIN Books b ON b.AuthorId = a.Id
GROUP BY a.Name
ORDER BY КоличествоКниг DESC;
Результат:
АвторКоличествоКниг
Лев Толстой2
Стивен Кинг2
Александр Пушкин1
Джоан Роулинг1
Мы использовали
LEFT JOIN, чтобы даже авторы без книг попали в результат (с 0). С INNER JOIN они бы исчезли.Запрос 6: активные читатели
Найдём читателей, которые взяли больше одной книги.
SELECT
r.Name AS Читатель,
COUNT(l.Id) AS КоличествоВыдач
FROM Readers r
LEFT JOIN Loans l ON l.ReaderId = r.Id
GROUP BY r.Name
HAVING COUNT(l.Id) > 1
ORDER BY КоличествоВыдач DESC;
Результат:
ЧитательКоличествоВыдач
Иван Петров2
Мария Сидорова2
HAVING COUNT(l.Id) > 1 — фильтрует уже сгруппированные результаты. В WHERE так нельзя.Запрос 7: статистика по годам
Посмотрим, сколько книг издано в каждом десятилетии.
SELECT
(Year / 10) * 10 AS Десятилетие,
COUNT(*) AS КоличествоКниг
FROM Books
GROUP BY (Year / 10) * 10
ORDER BY Десятилетие;
Результат:
ДесятилетиеКоличествоКниг
18301
18601
18701
19701
19802
19901
(Year / 10) * 10 округляет год вниз до десятков. 1833 → 1830, 1997 → 1990. Это простой способ группировать по десятилетиям.Запрос 8: самая популярная книга
Найдём книгу, которую брали чаще всего.
SELECT
b.Title AS Книга,
COUNT(l.Id) AS КоличествоВыдач
FROM Books b
LEFT JOIN Loans l ON l.BookId = b.Id
GROUP BY b.Title
ORDER BY КоличествоВыдач DESC;
Результат:
КнигаКоличествоВыдач
Гарри Поттер...2
Евгений Онегин1
Война и мир1
Анна Каренина1
Оно1
Сияние0
«Гарри Поттер» — самая популярная книга. «Сияние» ни разу не брали.
Сводка всех запросов
Вот что мы научились делать в этой практике.
ЗапросЧто делаетКлючевые команды
1книги с авторамиINNER JOIN, ORDER BY
2книги ТолстогоWHERE
3кто что бралтри таблицы, два JOIN
4невозвращённые книгиIS NULL
5книг у автораLEFT JOIN, GROUP BY, COUNT
6активные читателиGROUP BY, HAVING
7статистика по годамGROUP BY с выражением
8популярная книгаLEFT JOIN, COUNT, ORDER BY
Это не учебные примеры — это настоящие запросы, которые используются в реальных библиотечных системах.
Самостоятельные задания
Попробуйте написать эти запросы сами, без подсказок. Если что-то не получается — вернитесь к соответствующим слайдам.
- Выведите всех читателей, зарегистрированных после 1 сентября 2026 года.
- Выведите все книги Стивена Кинга.
- Найдите читателей, которые брали книги, но ещё не вернули ни одной.
- Посчитайте, сколько раз каждая книга была выдана.
- Найдите автора, у которого больше всего книг.
- Выведите список всех книг с указанием автора и года, отсортированный по году издания (от старых к новым).
Решения разберём на следующем занятии. Не бойтесь ошибаться — каждая ошибка учит больше, чем готовый ответ.
Что вы освоили
Подведём итоги практики.
- ✅ CREATE DATABASE — создание базы данных.
- ✅ CREATE TABLE — создание таблиц с типами.
- ✅ PRIMARY KEY, IDENTITY — первичные ключи и автонумерация.
- ✅ FOREIGN KEY — внешние ключи и связи между таблицами.
- ✅ INSERT INTO — добавление данных, в том числе с NULL.
- ✅ INNER JOIN, LEFT JOIN — соединение двух и трёх таблиц.
- ✅ WHERE, IS NULL — фильтрация строк.
- ✅ GROUP BY, HAVING — группировка и фильтрация групп.
- ✅ COUNT, ORDER BY — статистика и сортировка.
Вы прошли путь от пустого окна SSMS до полноценной базы с отчётами. Это уровень junior-разработчика по SQL.
Домашнее задание
Задания для самостоятельной работы. Первые три обязательны, остальные — для оценки «отлично».
- Добавьте ещё двух авторов и трёх книг.
- Добавьте нового читателя и оформите ему выдачу книги.
- Напишите запрос: «все книги с авторами и годами издания, отсортированные по автору».
- Найдите читателя, который взял больше всего книг.
- Найдите автора, чьи книги ни разу не брали.
- Посчитайте средний год издания книг по каждому автору.
Если задание кажется сложным — разбейте его на части. Сначала данные, потом запрос, потом сортировка.
Что дальше?
Мы научились работать с базой данных через SSMS — вручную. Но настоящие приложения работают с базой программно. Именно этим займёмся на следующей практике.
- Практика 2: подключение Java к MS SQL Server через JDBC.
- Драйвер
mssql-jdbc,Connection,PreparedStatement,ResultSet. - Консольное приложение, которое читает и пишет данные в нашу базу «Библиотека».
Это финальный шаг: превратить базу данных в часть настоящей программы на Java.
Спасибо за работу!
📚
База «Библиотека» готова!
Вы создали четыре связанные таблицы, заполнили их данными и написали восемь настоящих SQL-запросов. Теперь вы умеете работать с базой данных вручную.
🗄️ БД
📚 Библиотека
🔗 JOIN
📊 Отчёты
🚀 Java + JDBC