Запросы к базе данных Библиотека

База данных «Библиотека»
Минимальный список характеристик, которые должна иметь база данных:

Автор книги, название, год издания, цена, количество экземпляров, краткая аннотация;
Номер читательского билета, ФИО, адрес и телефон читателя, дата выдачи книги читателю и дата сдачи книги читателем, отметка о выбытии.

Книга имеет много экземпляров и поэтому может быть выдана многим читателям.

Запросы на выборку:

• Выбрать книгу, для которой наибольшее количество экземпляров находится "на полках" (не выданы читателям).
• Выбрать читателей, которые имеют задолженность более 4 месяцев.
• Определить книгу, которая была наиболее популярной весной 2000 года.
• Определить читателей, у которых на руках находятся книги на общую сумму более 100 руб.

Вот и добрался я, наконец, до этих, головоломных на первый взгляд, запросов. Несмотря на то, что некоторые из них выглядят нерешаемыми, задача легко решается с помощью нескольких запросов. Но, если писать на чистом SQL, пытаясь уложиться в один запрос, то будет посложнее. Ну, попытаемся взять количеством))

Итак, создаем базу данных из трех таблиц – Книги, Читатели и Выдача.
Схема базы данных на рисунке:

Никаких дополнительных атрибутов не создано, если не считать фактической даты сдачи. Она нужна для выяснения, просрочена ли книга. Непонятна отметка о выбытии. Куда ее ставить, если имеется десять экземпляров книг, а выдан один? Поэтому было создано еще одно поле в таблице Книги – «Выдано». Тут придется или вручную подсчитывать, сколько выдано, или же делать запрос на обновление после каждой выдачи или сдачи книги. Поскольку я собираюсь только создать запросы на выборку (по заданию), то вопрос по подсчету выданных книг оставляю без решения. В учебной базе можно и вручную занести.

Начнем с запросов попроще.

Нужно выбрать читателей, которые имеют задолженность более 4 месяцев.

Делаем запрос:

На языке SQL это будет выглядеть так:

SELECT Читатели.ФИО, Выдача.Дата_выдачи, Книги.Название
FROM Читатели INNER JOIN (Книги INNER JOIN Выдача ON Книги.[Шифр книги] = Выдача.Щифр_книги) ON Читатели.Номер_билета = Выдача.Номер_билета
WHERE (((DateDiff("m",[Выдача]![Дата_сдачи],Date()))>4) AND ((IsNull([Выдача]![Дата_сдачи_факт]))=Yes));

Для выборки записей стоят два условия:
Во-первых, не заполнена фактическая дата сдачи, то есть книга на руках. В этом случае функция IsNull вернет значение «истина».
Во-вторых, с момента назначенной даты сдачи прошло 4 месяца. Считаем, как разницу между текущей датой и датой сдачи (функция DateDiff, интервал – месяц).
Если два условия выполняются, то запись попадает в выборку.

Определить читателей, у которых на руках находятся книги на общую сумму более 100 руб

Создаем запрос с группировкой

На SQL

SELECT Читатели.ФИО, Sum(Книги.Цена) AS [Sum-Цена]
FROM Читатели INNER JOIN (Книги INNER JOIN Выдача ON Книги.[Шифр книги] = Выдача.Щифр_книги) ON Читатели.Номер_билета = Выдача.Номер_билета
WHERE (((IsNull([Выдача]![Дата_сдачи_факт]))=Yes))
GROUP BY Читатели.ФИО
HAVING (((Sum(Книги.Цена))>100));

Группируем по ФИО читателя, суммируем сумму книг, но только тех, которые у него на руках, то есть поле фактической даты сдачи не заполнено.

Определить книгу, которая была наиболее популярной весной 2000 года.

Нужно выявить книгу (книги), которую выдавали наибольшее количество раз весной 2000 года.
Сделаем три запроса. Сначала посчитаем, сколько раз выдавали каждую книгу в нужный период.
Затем найдем максимальное значение.
Найдем нужную книгу по максимальному значению выдач.

Вот такой вариант пришел в голову. Если кто-то делает одним запросом, молодец.
Первый запрос (Количество):

Текст

SELECT Книги.[Шифр книги], Count(Выдача.Дата_выдачи) AS [Count-Дата_выдачи], Книги.Автор, Книги.Название
FROM Книги INNER JOIN Выдача ON Книги.[Шифр книги] = Выдача.Щифр_книги
WHERE (((Выдача.Дата_выдачи) Between #3/1/2000# And #5/31/2000#))
GROUP BY Книги.[Шифр книги], Книги.Автор, Книги.Название;

Второй запрос (Максимум)

Текст

SELECT Max(Количество.[Count-Дата_выдачи]) AS [Max-Count-Дата_выдачи]
FROM Количество;

И, наконец, в третьем запросе объединяем два предыдущих:

SELECT Количество.[Шифр книги], Количество.Автор, Количество.Название, Количество.[Count-Дата_выдачи]
FROM Максимум, Количество
WHERE (((Количество.[Count-Дата_выдачи])=[Максимум]![Max-Count-Дата_выдачи]));

Выбрать книгу, для которой наибольшее количество экземпляров находится "на полках" (не выданы читателям).

Вот тут в моей базе имеются два варианта решения. Один образовался из-за того, что я сделал поле "Выдано" в таблице книг. В этом случае достаточно двух запросов:
В первом я ищу максимальную разницу между двумя полями таблицы «Книги» - «Количество» и «Выдано». Результат – сколько лежит на полке.

Текст

SELECT Max([Количество]-[Выдано]) AS [Не выдано]
FROM Книги;

Второй – обычный запрос на выборку, условие для отбора – нужно, чтобы разница между количеством экземпляров и количеством выданных книг совпадала с максимальным количеством из первого запроса.

SELECT Книги.Автор, Книги.Название, [Книги]![Количество]-[Книги]![Выдано] AS [Не выдано]
FROM Книги, [Не выдано 1]
WHERE ((([Книги]![Количество]-[Книги]![Выдано])=[Не выдано 1]![Не выдано]));

Но, вообще-то, это читерство)) Думаю, что ожидается не такой результат, поэтому будем решать так, как будто поля «Выдано» в таблице книг нет.

В первом запросе смотрим, какие и сколько книг выдано:

SELECT Книги.Автор, Книги.Название, Книги.Количество, Count(Книги.[Шифр книги]) AS [Count-Шифр книги], Книги.[Шифр книги]
FROM Книги INNER JOIN Выдача ON Книги.[Шифр книги] = Выдача.Щифр_книги
WHERE (((IsNull([Выдача]![Дата_сдачи_факт]))=Yes))
GROUP BY Книги.Автор, Книги.Название, Книги.Количество, Книги.[Шифр книги];

Во втором запросе будем смотреть, сколько экземпляров каждой книги лежит на полке.

Какие тут моменты. Во-первых, используем следующие параметры объединения:

Это для того, чтобы не выпадали книги, которых нет в запросе. А там нет книг, которые лежат на полках в полном составе (ну вот такие непопулярные).
Во-вторых, считаем количество на полках, как разницу между количеством книг из таблицы «Книги» и количеством выданных книг из запроса. Если же это количество пустое (у совсем невыданных книг), то разница тоже будет пустой. Поэтому приходится использовать условное выражение IIf и в случае, если значение пустое, то подставляется количество экземпляров всего, а если не пустое, то подставляется разница между количеством всего и количеством выданных книг.

SELECT Книги.[Шифр книги], Книги.Автор, Книги.Название, Книги.Количество, [Не выдано 2].[Count-Шифр книги], IIf(IsNull([Count-Шифр книги]),[Книги]![Количество],[Книги]![Количество]-[Count-Шифр книги]) AS [На полке]
FROM Книги LEFT JOIN [Не выдано 2] ON Книги.[Шифр книги] = [Не выдано 2].[Шифр книги];

Дальше находим максимум:

SELECT Max([Не выдано 2 -1].[На полке]) AS [Max-На полке]
FROM [Не выдано 2 -1];

И объединяем два последних запроса, чтобы вывести книгу, у которой количество экземпляров на полке равно максимальному из последнего запроса.

SELECT [Не выдано 2 -1].[Шифр книги], [Не выдано 2 -1].Автор, [Не выдано 2 -1].Название, [Не выдано 2 -1].[На полке]
FROM [Не выдано 2 -1], [Не выдано 2 максимум]
WHERE ((([Не выдано 2 -1].[На полке])=[Не выдано 2 максимум]![Max-На полке]));

Вот и все. Надеюсь, понятно расписал.