Материал: Технологии_Баз_Данных_2020

1. Библиотека

Минимальный список характеристик:

• Автор книги, название, год издания, цена, количество экземпляров, краткая аннотация;

• номер читательского билета, ФИО, адрес и телефон читателя, дата выдачи книги читателю и дата сдачи книги читателем, отметка о выбытии.

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

Выборки:

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

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

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

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

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

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

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

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

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

Текст

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

FROM Книги;

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

SELECT Книги.Автор, Книги.Название, [Книги]![Количество]-[Книги]![Выдано] AS [Не выдано]

FROM Книги, [Не выдано 1]

WHERE ((([Книги]![Количество]-[Книги]![Выдано])=[Не выдано 1]![Не выдано]));

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

2. Университет

Минимальный список характеристик:

• Номер, ФИО, адрес и должность преподавателя, ученая степень;

• код, название, количество часов, тип контроля и раздел предмета (дисциплины);

• код, название, номер заведующего кафедрой;

• номер аудитории, где преподаватель читает свой предмет, дата, время, группа.

Один преподаватель может вести несколько дисциплин и одна дисциплина может вестись несколькими преподавателями.

Примечание: Циклы дисциплин: гуманитарный, общеинженерный, математический, компьютерный и т.д.

Выборки:

• Выбрать преподавателя, который был "без работы" весной 2001г.

SELECT Преподаватели.ФИО FROM Преподаватели WHERE (((Преподаватели.Номер) Not In (SELECT Преподаватели.Номер FROM Преподаватели LEFT JOIN Расписание ON Преподаватели.Номер = Расписание.Преподаватель WHERE (((Расписание.Дата) Between #3/1/2001# And #5/31/2001#)) GROUP BY Преподаватели.Номер)));

Тут выбираются преподаватели, которых нет (Not In) в списке преподов, работавших весной 2001 года (то есть у них нет записей в таблице «Расписание» за указанный период).

• Определить возможные "накладки" аудиторий в расписании.

SELECT Расписание.Дата, Расписание.Время, Расписание.Аудитория, Count(Расписание.[Код записи]) AS [Count-Код записи] FROM Расписание GROUP BY Расписание.Дата, Расписание.Время, Расписание.Аудитория HAVING (((Count(Расписание.[Код записи]))>1));

SELECT Расписание.Дата, Расписание.Время, Расписание.Аудитория, Расписание.Группа, Расписание.Преподаватель, Расписание.Предмет FROM Расписание, Накладки WHERE (((Расписание.Дата)=[Накладки]![Дата]) AND ((Расписание.Время)=[Накладки]![Время]) AND ((Расписание.Аудитория)=[Накладки]![Аудитория]));

• Вывести расписание занятий группы 'АП-17а' на март 2001г.

SELECT Расписание.Дата, Расписание.Время, Расписание.Аудитория, Расписание.Предмет, Расписание.Преподаватель FROM Группы INNER JOIN Расписание ON Группы.[Код группы] = Расписание.Группа WHERE (((Расписание.Дата) Between #3/1/2001# And #3/31/2001#) AND ((Группы.Название)="АП17а"));

• Определить для каждой группы долю дисциплин каждого цикла в процентах.

SELECT [Предметы группы].Группа, Предметы.[Цикл предмета], Count([Предметы группы].[Предмет])*100/[Count-Предмет] AS [Процент предметов] FROM [Всего предметов] INNER JOIN (Группы INNER JOIN (Предметы INNER JOIN [Предметы группы] ON Предметы.[Код предмета] = [Предметы группы].Предмет) ON Группы.[Код группы] = [Предметы группы].Группа) ON [Всего предметов].Группа = Группы.[Код группы] GROUP BY [Предметы группы].Группа, Предметы.[Цикл предмета], [Всего предметов].[Count-Предмет];

3. Оптовая база

Минимальный список характеристик:

• Код товара, название товара, количество на складе, единица измерения, стоимость единицы товара, примечания - описание товара;

• Номер, адрес, телефон и ФИО поставщика товара, срок поставки и количество товаров в поставке, номер счета.

Один и тот же товар может доставляться несколькими поставщиками и один и тот же поставщик может доставлять несколько видов товаров.

Выборки:

• Выбрать поставщиков, которые поставляют все товары.

SELECT Поставщики.ФИО, Товары.Название, Sum(Поставки.Количество_товара) AS [Sum-Количество_товара] FROM Поставщики INNER JOIN (Товары INNER JOIN Поставки ON Товары.[Код товара] = Поставки.Товар) ON Поставщики.Номер = Поставки.Поставщик GROUP BY Поставщики.ФИО, Товары.Название;

SELECT Все_товары1.ФИО, Count(Все_товары1.Название) AS [Count-Название] FROM Все_товары1 GROUP BY Все_товары1.ФИО HAVING (((Count(Все_товары1.Название))=(SELECT Count(Название) FROM Товары)));

• Определить поставщика, который поставляет товар 'коврик для мыши' по самой низкой в среднем цене.

SELECT Avg(Поставки.Цена) AS [Avg-Цена], Поставщики.ФИО FROM Поставщики INNER JOIN (Товары INNER JOIN Поставки ON Товары.[Код товара] = Поставки.Товар) ON Поставщики.Номер = Поставки.Поставщик WHERE (((Товары.Название)="коврик для мыши")) GROUP BY Поставщики.ФИО;

SELECT Min(Коврик1.[Avg-Цена]) AS [Min-Avg-Цена] FROM Коврик1;

SELECT Коврик1.ФИО FROM Коврик1, Коврик2 WHERE (((Коврик1.[Avg-Цена])=[Коврик2]![Min-Avg-Цена]));

• Вывести названия товаров, цены на которые никогда не повышались.

SELECT Товары.Название FROM Товары INNER JOIN Поставки ON Товары.[Код товара] = Поставки.Товар GROUP BY Товары.Название HAVING (((Max([Поставки].[Цена])-Min([Поставки].[Цена]))<=0));

• Определить, на сколько единиц возросли поставки товара 'инструмент' в 2001г. по сравнению с предыдущим годом.

SELECT Sum(Поставки.Количество_товара) AS [Sum-Количество_товара], DatePart('yyyy',[Поставки]![Срок_поставки]) AS Год FROM Товары INNER JOIN Поставки ON Товары.[Код товара] = Поставки.Товар WHERE (((Товары.Название)="Инструмент")) GROUP BY DatePart('yyyy',[Поставки]![Срок_поставки]);

Из этого запроса делаем два других – по 2000 и 2001 году

SELECT Инструмент1.[Sum-Количество_товара], Инструмент1.Год FROM Инструмент1 WHERE (((Инструмент1.Год)=2000));

И аналогичный по 2001 году (не буду тут приводить). И из двух последних делаем еще один, минусуя из количества 2001 года количество товара в 2000 году

SELECT [Инструмент2001]![Sum-Количество_товара]-[Инструмент2000]![Sum-Количество_товара] AS Разность FROM Инструмент2000, Инструмент2001;

4. Производство

Минимальный список характеристик:

• Код изделия, название изделия, является ли типовым, примечание - для каких целей предназначено, годовой объем выпуска;

• код, название, адрес и телефон предприятий, выпускающих изделия;

• название, тип, единица измерения материала, цена за единицу, отметка об использовании материала в данном изделии;

• количество материала в спецификации изделия, дата установления спецификации, дата отмены;

• год выпуска и объем выпуска данного изделия предприятием.

Одно изделие может содержать много типов материалов и один и тот же материал может входить в состав разных изделий.

Выборки:

• Определить изделие, в которое входит больше всего материалов типа 'цветной металл'.

SELECT Изделия.Название, Sum(Спецификация.Количество) AS [Sum-Количество], Материалы.Тип FROM Спец INNER JOIN (Материалы INNER JOIN (Изделия INNER JOIN Спецификация ON Изделия.Код_изделия = Спецификация.Изделие) ON Материалы.Код_материала = Спецификация.Материал) ON Спец.Код = Спецификация.Спецификация WHERE (((IIf((IsNull([Спец]![Дата_отмены]) Or [Спец]![Дата_отмены]>Date()) And [Спец]![Дата_установки]<Date(),1,0))=1)) GROUP BY Изделия.Название, Материалы.Тип HAVING (((Материалы.Тип)="Цветной металл"));

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

SELECT Max(ЦветМет1.[Sum-Количество]) AS [Max-Sum-Количество] FROM ЦветМет1;

Третий запрос делаем из первых двух. Выбираем название и сумму и сравниваем сумму с максимальным количеством.

SELECT ЦветМет1.Название, ЦветМет1.[Sum-Количество] FROM ЦветМет1, ЦветМет2 WHERE (((ЦветМет1.[Sum-Количество])=[ЦветМет2]![Max-Sum-Количество]));

• Вывести список изделий, которые не производились в 2000 г.

SELECT Изделия.Название FROM Изделия WHERE (((Изделия.Код_изделия) Not In (SELECT Изделия.Код_изделия FROM Изделия INNER JOIN Выпуск ON Изделия.Код_изделия = Выпуск.Изделие GROUP BY Изделия.Код_изделия, Выпуск.Год_выпуска HAVING ((Выпуск.Год_выпуска)=2000))));

• Вывести список изделий, для которых затраты на материалы в 2000 г. снизились по сравнению с предыдущим годом.

Сначала посчитаем затраты за все годы с учетом действовавших на тот момент спецификаций. Я посчитал затраты, как произведение выпуска, количества материала в единице изделия и цены материала. Цена у нас постоянная, поэтому затраты зависят от спецификации и объема выпуска. Если объем выпуска не важен, то его можно убрать.

Условие проверки спецификация, как в предыдущем запросе.

SELECT Изделия.Код_изделия, Выпуск.Год_выпуска, Sum([Выпуск]![Объем_выпуска]*[Спецификация]![Количество]*[Материалы]![Цена]) AS Затраты FROM Спец INNER JOIN (Материалы INNER JOIN ((Изделия INNER JOIN Выпуск ON Изделия.Код_изделия = Выпуск.Изделие) INNER JOIN Спецификация ON Изделия.Код_изделия = Спецификация.Изделие) ON Материалы.Код_материала = Спецификация.Материал) ON Спец.Код = Спецификация.Спецификация WHERE (((IIf([Выпуск]![Год_выпуска] Between DatePart('yyyy',[Спец]![Дата_установки]) And IIf(IsNull([Спец]![Дата_отмены]),DatePart('yyyy',Date()),DatePart('yyyy',[Спец]![Дата_отмены])),1,0))=1)) GROUP BY Изделия.Код_изделия, Выпуск.Год_выпуска;

Затем отбираем выпуск 1999 года

SELECT Затраты2000.Код_изделия, Затраты2000.Год_выпуска, Затраты2000.Затраты FROM Затраты2000 WHERE (((Затраты2000.Год_выпуска)=1999));

И точно так же делаем 2000 год. Последний запрос делаем из двух предыдущих (1999 и 2000 год) и таблицы изделий. Выбираем непустые записи, отнимаем из 1999 года 2000-й и, если сумма положительная, выводим.

SELECT Изделия.Название, Затраты_1999.Затраты, Затраты_2000.Затраты, [Затраты_1999.Затраты]-[Затраты_2000.Затраты] AS [Снижение затрат] FROM (Затраты_1999 INNER JOIN Изделия ON Затраты_1999.Код_изделия = Изделия.Код_изделия) INNER JOIN Затраты_2000 ON Изделия.Код_изделия = Затраты_2000.Код_изделия WHERE (((Затраты_1999.Затраты) Is Not Null) AND ((Затраты_2000.Затраты) Is Not Null) AND (([Затраты_1999.Затраты]-[Затраты_2000.Затраты])>0));

• Вывести среднемесячный расход материала 'лапша' в 2000 г.

SELECT Выпуск.Год_выпуска, Материалы.Название, Sum(Выпуск.Объем_выпуска) AS [Sum-Объем_выпуска], Avg([Спецификация]![Количество]*[Выпуск]![Объем_выпуска]) AS Расход FROM Спец INNER JOIN (Материалы INNER JOIN ((Изделия INNER JOIN Выпуск ON Изделия.Код_изделия = Выпуск.Изделие) INNER JOIN Спецификация ON Изделия.Код_изделия = Спецификация.Изделие) ON Материалы.Код_материала = Спецификация.Материал) ON Спец.Код = Спецификация.Спецификация WHERE (((IIf([Выпуск]![Год_выпуска] Between DatePart('yyyy',[Спец]![Дата_установки]) And IIf(IsNull([Спец]![Дата_отмены]),DatePart('yyyy',Date()),DatePart('yyyy',[Спец]![Дата_отмены])),1,0))=1)) GROUP BY Выпуск.Год_выпуска, Материалы.Название HAVING (((Выпуск.Год_выпуска)=2000) AND ((Материалы.Название)="Лапша"));

5. Сеть магазинов

Минимальный список характеристик:

• Номер, ФИО, адрес, телефон владельца магазина, размер вклада в магазин, номер регистрации, дата регистрации;

• номер, название, адрес и телефон магазина, уставной капитал, профиль;

• номер, ФИО, адрес, телефон поставщика, а также стоимость поставки данного поставщика в данный магазин.

Один и тот же магазин может иметь несколько владельцев и один и тот же владелец может иметь в собственности много магазинов.

Примечание: профиль - продуктовый, галантерейный, канцелярский и т.п.

Выборки:

• Определить самого молодого предпринимателя, владеющего собственностью в районе 'Киевский'.

SELECT Магазины.Район, Владельцы.ФИО, Владельцы.Дата_рождения, Count(Магазины.Номер_магазина) AS [Count-Номер_магазина] FROM Магазины INNER JOIN (Владельцы INNER JOIN Владение ON Владельцы.Номер_владельца = Владение.Владелец) ON Магазины.Номер_магазина = Владение.Магазин GROUP BY Магазины.Район, Владельцы.ФИО, Владельцы.Дата_рождения HAVING (((Магазины.Район)="Киевский"));

Затем выбираем запись с максимальной датой

SELECT Max(Молодой1.[Min-Дата_рождения]) AS [Max-Min-Дата_рождения] FROM Молодой1;

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

SELECT Молодой1.ФИО FROM Молодой1, Молодой2 WHERE (((Молодой1.[Min-Дата_рождения])=[Молодой2]![Max-Min-Дата_рождения]));

• Определить случаи, когда регистрировалось владение лицами, не достигшими 18 лет.

SELECT Владение.Номер_регистрации, Владение.Дата_регистрации, Владельцы.ФИО, DateDiff('yyyy',[Владельцы]![Дата_рождения],[Владение]![Дата_регистрации]) AS Возраст, Магазины.Название FROM Магазины INNER JOIN (Владельцы INNER JOIN Владение ON Владельцы.Номер_владельца = Владение.Владелец) ON Магазины.Номер_магазина = Владение.Магазин WHERE (((DateDiff('yyyy',[Владельцы]![Дата_рождения],[Владение]![Дата_регистрации]))<18));

• Определить случаи, когда больше 50% уставного капитала магазина внесено предпринимателем, проживающим в другом районе.

SELECT Владельцы.ФИО, Владельцы.Район, Магазины.Название, Магазины.Район, [Владение]![Размер вклада]*100/[Магазины]![Уставной_капитал] AS Процент FROM Магазины INNER JOIN (Владельцы INNER JOIN Владение ON Владельцы.Номер_владельца = Владение.Владелец) ON Магазины.Номер_магазина = Владение.Магазин WHERE (((Магазины.Район)<>[Владельцы]![Район]) AND (([Владение]![Размер вклада]*100/[Магазины]![Уставной_капитал])>50));

• Вывести список профилей магазинов, которыми владеет предприниматель 'Кузнецов' в порядке убывания вложенного в них капитала

SELECT Магазины.Профиль, Владельцы.ФИО, Sum(Владение.[Размер вклада]) AS [Sum-Размер вклада] FROM Магазины INNER JOIN (Владельцы INNER JOIN Владение ON Владельцы.Номер_владельца = Владение.Владелец) ON Магазины.Номер_магазина = Владение.Магазин GROUP BY Магазины.Профиль, Владельцы.ФИО HAVING (((Владельцы.ФИО)="Кузнецов")) ORDER BY Sum(Владение.[Размер вклада]) DESC;

6. Авторемонтные мастерские

Минимальный список характеристик:

• Номер водительских прав, ФИО, адрес и телефон владельца автомобиля;

• номер, ФИО, адрес, телефон и квалификация (разряд) механика;

• номер, марка, мощность, год выпуска и цвет автомобиля;

• номер, название, адрес и телефон ремонтной мастерской;

• стоимость наряда на ремонт, дата выдачи наряда, категория работ, плановая и реальная дата окончания ремонта.

Один и тот же автомобиль может обслуживаться разными автомеханиками и один и тот же автомеханик может обслуживать несколько автомобилей.

Выборки:

• Выбрать фамилию того механика, который чаще всех работает с довоенными автомобилями.