Минимальный список характеристик:
- Номер водительских прав, ФИО, адрес и телефон владельца автомобиля;
- номер, ФИО, адрес, телефон и квалификация (разряд) механика;
- номер, марка, мощность, год выпуска и цвет автомобиля;
- номер, название, адрес и телефон ремонтной мастерской;
- стоимость наряда на ремонт, дата выдачи наряда, категория работ, плановая и реальная дата окончания ремонта.
Один и тот же автомобиль может обслуживаться разными автомеханиками и один и тот же автомеханик может обслуживать несколько автомобилей.
Выборки:
- Выбрать фамилию того механика, который чаще всех работает с довоенными автомобилями.
- Выбрать случаи, когда ремонт автомобилей марки 'Мерседес-600' задерживался относительно планового срока.
- Определить тех владельцев автомобилей, которых всегда обслуживает один и тот же механик. Вывести фамилии механика и его постоянного клиента.
- Для каждой категории работ определить, механик какого разряда чаще всего назначается на эту категорию работ
Создаем пять таблиц – владельцы, автомобили, автомеханики, мастерские, наряды. Мастерские для запросов не нужны, но в задании указаны, поэтому такую таблицу делать нужно. Примем, что автомеханик может работать в одной мастерской (чтобы не делать еще одну таблицу). Автомехаников и автомобили свяжем через таблицу Наряды. Получается такая схема:
1. Выбираем механика, который чаще, чем другие механики, обслуживает довоенные автомобили. Чаще, я так понимаю, это значит, что больше всех остальных во всей базе данных. Ну, иной период в задании не указан, значит, считаем всего.
В первом запросе с группировкой выбираем количество нарядов, по которым каждый механик ремонтировал довоенные автомобили.
SELECT Автомеханики. ФИО, Count(Наряды. Номер_наряда) AS [Count-Номер_наряда]
FROM Автомобили INNER JOIN (Автомеханики INNER JOIN Наряды ON Автомеханики. Номер_механика = Наряды. Автомеханик) ON Автомобили. Номер = Наряды. Автомобиль
WHERE (((Автомобили.[Год выпуска])<1941))
GROUP BY Автомеханики. ФИО;
Во втором запросе ищем максимальное число нарядов.
SELECT Max(Довоенный1.[Count-Номер_наряда]) AS [Max-Count-Номер_наряда]
FROM Довоенный1;
Третий запрос делаем на основе двух предыдущих. Выбираем того/тех механика/ов, который/ые ремонтировал/и довоенные автомобили максимальное количество раз.
SELECT Довоенный1.ФИО, Довоенный1.[Count-Номер_наряда]
FROM Довоенный1, Довоенный2
WHERE (((Довоенный1.[Count-Номер_наряда])=[Довоенный2]![Max-Count-Номер_наряда]));
2. Выявляем случаи просроченного ремонта шестисотых «Мерседесов».
Обойдемся одним запросом:
SELECT Автомобили. Марка, Наряды. Номер_наряда, Наряды. Дата_наряда, Автомеханики. ФИО, Наряды. Плановая_дата, Наряды. Реальная_дата
FROM Автомобили INNER JOIN (Автомеханики INNER JOIN Наряды ON Автомеханики. Номер_механика = Наряды. Автомеханик) ON Автомобили. Номер = Наряды. Автомобиль
WHERE (((Автомобили. Марка)="Мерседес-600") AND ((Наряды. Реальная_дата)>[Наряды]![Плановая_дата]));
3. Ищем механиков с постоянными клиентами. Точнее, клиентов с постоянными механиками.
В первом запросе выбираем владельцев, номер их прав и автомехаников. Все группируем.
SELECT Владельцы. ФИО, Автомеханики. ФИО, Владельцы. Номер_прав
FROM Владельцы INNER JOIN (Автомобили INNER JOIN (Автомеханики INNER JOIN Наряды ON Автомеханики. Номер_механика = Наряды. Автомеханик) ON Автомобили. Номер = Наряды. Автомобиль) ON Владельцы. Номер_прав = Автомобили. Владелец
GROUP BY Владельцы. ФИО, Автомеханики. ФИО, Владельцы. Номер_прав;
Второй запрос делаем на основе первого и считаем количество механиков на каждого клиента. Добавляем в выборку только те записи, в которых число механиков на 1 клиента равно 1.
SELECT Постоянный_механик1.Владельцы. ФИО, Count(Постоянный_механик1.Автомеханики. ФИО) AS [Count-Автомеханики_ФИО], Постоянный_механик1.Номер_прав
FROM Постоянный_механик1
GROUP BY Постоянный_механик1.Владельцы. ФИО, Постоянный_механик1.Номер_прав
HAVING (((Count(Постоянный_механик1.Автомеханики. ФИО))=1));
Третий запрос делаем из четырех таблиц и предыдущего запроса. Такая длинная цепочка получилась из-за того, что между владельцами и механиками две таблицы (автомобили и наряды).
SELECT Владельцы. ФИО, Автомеханики. ФИО
FROM (Владельцы INNER JOIN Постоянный2 ON Владельцы. Номер_прав = Постоянный2.Номер_прав) INNER JOIN (Автомобили INNER JOIN (Автомеханики INNER JOIN Наряды ON Автомеханики. Номер_механика = Наряды. Автомеханик) ON Автомобили. Номер = Наряды. Автомобиль) ON Владельцы. Номер_прав = Автомобили. Владелец
GROUP BY Владельцы. ФИО, Автомеханики. ФИО;
4. Для каждой категории работ нужно определить разряд исполнителей, которые чаще всего занимаются этой работой.
В первом запросе выбираем категории работ, разряды и число нарядов, выполненных каждым разрядом (точнее, обладателями каждого разряда).
SELECT Наряды. Категория_работ, Автомеханики. Разряд, Count(Наряды. Номер_наряда) AS [Count-Номер_наряда]
FROM Автомеханики INNER JOIN Наряды ON Автомеханики. Номер_механика = Наряды. Автомеханик
GROUP BY Наряды. Категория_работ, Автомеханики. Разряд;
Второй запрос: выбираем записи с максимальным количеством нарядов по каждой категории.
SELECT Категория1.Категория_работ, Max(Категория1.[Count-Номер_наряда]) AS [Max-Count-Номер_наряда]
FROM Категория1
GROUP BY Категория1.Категория_работ;
Последний запрос делаем из первых двух, связав их по полю Категория. Выбираем те записи, число нарядов в которых равно максимальному числу выполненных работ (среди всех разрядов).
SELECT Категория1.Категория_работ, Категория1.Разряд, Категория1.[Count-Номер_наряда]
FROM Категория1 INNER JOIN Категория2 ON Категория1.Категория_работ = Категория2.Категория_работ
WHERE (((Категория1.[Count-Номер_наряда])=[Категория2]![Max-Count-Номер_наряда]));
