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

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

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-Номер_наряда]));

• Выбрать случаи, когда ремонт автомобилей марки 'Мерседес-600' задерживался относительно планового срока.

SELECT Автомобили.Марка, Наряды.Номер_наряда, Наряды.Дата_наряда, Автомеханики.ФИО, Наряды.Плановая_дата, Наряды.Реальная_дата FROM Автомобили INNER JOIN (Автомеханики INNER JOIN Наряды ON Автомеханики.Номер_механика = Наряды.Автомеханик) ON Автомобили.Номер = Наряды.Автомобиль WHERE (((Автомобили.Марка)="Мерседес-600") AND ((Наряды.Реальная_дата)>[Наряды]![Плановая_дата]));

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

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

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 Владельцы.ФИО, Автомеханики.ФИО;

• Для каждой категории работ определить, механик какого разряда чаще всего назначается на эту категорию работ

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-Номер_наряда]));

7. Деканат

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

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

• ФИО преподавателя, вид контроля, дата;

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

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

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

Выборки:

• Для каждой группы определить продолжительность сессии.

SELECT Группы.Код_группы, DateDiff('d',[Группы]![Начало_сесии],[Группы]![Окончание_сессии]) AS [Продолжительность сессии] FROM Группы;

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

SELECT Преподаватели.ФИО, Sum(Группы.Количество_студентов) AS [Sum-Количество_студентов] FROM Преподаватели INNER JOIN (Группы INNER JOIN Контроль ON Группы.Код_группы = Контроль.Группа) ON Преподаватели.Код = Контроль.Преподаватель WHERE (((Контроль.Вид_контроля)="Экзамен")) GROUP BY Преподаватели.ФИО;

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

SELECT Max(Экзамен_макс1.[Sum-Количество_студентов]) AS [Max-Sum-Количество_студентов] FROM Экзамен_макс1;

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

SELECT Экзамен_макс1.ФИО, Экзамен_макс1.[Sum-Количество_студентов] FROM Экзамен_макс1, Экзамен_макс2 WHERE (((Экзамен_макс1.[Sum-Количество_студентов])=[Экзамен_макс2]![Max-Sum-Количество_студентов]));

• Определить, какой процент от общего объема дисциплин, изучаемых группой 'АП-17а', составляют дисциплины каждой категории.

SELECT Группы.Код_группы, Дисциплины.Категория, Sum(Дисциплины.[Объем часов]) AS [Sum-Объем часов], [Sum-Объем часов]*100/[Объем_часов] AS Процент FROM Дисциплины INNER JOIN (Группы INNER JOIN Контроль ON Группы.Код_группы = Контроль.Группа) ON Дисциплины.Код = Контроль.Дисциплина GROUP BY Группы.Код_группы, Дисциплины.Категория, Группы.Объем_часов HAVING (((Группы.Код_группы)="АП-17а"));

• Определить, не сдает ли какая-либо группа два экзамена в один день.

SELECT Группы.Код_группы, Контроль.Дата, Count(Контроль.Код_записи) AS [Count-Код_записи] FROM Дисциплины INNER JOIN (Группы INNER JOIN Контроль ON Группы.Код_группы = Контроль.Группа) ON Дисциплины.Код = Контроль.Дисциплина WHERE (((Контроль.Вид_контроля)="Экзамен")) GROUP BY Группы.Код_группы, Контроль.Дата HAVING (((Count(Контроль.Код_записи))>1));

8. Договорная деятельность организации

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

• Шифр работы, название, трудоемкость, дата завершения;

• ФИО сотрудника, должность, табельный номер;

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

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

Выборки:

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

SELECT Работы.Название FROM Работы INNER JOIN Поручения ON Работы.Шифр_работы = Поручения.Работа WHERE (((Работы.Дата_завершения) Between #1/1/2000# And #12/31/2000#) AND ((Поручения.Плановая_дата)>[Работы]![Дата_завершения])) GROUP BY Работы.Название;

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

SELECT Сотрудники.ФИО, Count(Работы.Шифр_работы) AS [Count-Шифр_работы] FROM Сотрудники INNER JOIN (Работы INNER JOIN Поручения ON Работы.Шифр_работы = Поручения.Работа) ON Сотрудники.Табельный_номер = Поручения.Сотрудник WHERE (((([Поручения]![Дата_выдачи] Between #3/1/1999# And #5/31/1999#) Or ([Поручения]![Реальная_дата] Between #3/1/1999# And #5/31/1999#) Or (([Поручения]![Дата_выдачи]<#3/1/1999#) And ([Поручения]![Реальная_дата]>#5/31/1999#)))=Yes)) GROUP BY Сотрудники.ФИО;

• Определить те работы, которые к дате завершения были выполнены не более, чем на 50%.

SELECT Работы.Название, Sum(DateDiff('d',[Поручения]![Дата_выдачи],[Работы]![Дата_завершения])) AS Дни, Работы.Трудоемкость FROM Работы INNER JOIN Поручения ON Работы.Шифр_работы = Поручения.Работа GROUP BY Работы.Название, Работы.Трудоемкость HAVING (((Sum(DateDiff('d',[Поручения]![Дата_выдачи],[Работы]![Дата_завершения])))<[Работы]![Трудоемкость]/2));

• Определить должностной состав сотрудников, выполняющих работу 'проект Гелиограф'.

SELECT Сотрудники.Должность, Count(Сотрудники.Табельный_номер) AS [Count-Табельный_номер] FROM Сотрудники INNER JOIN (Работы INNER JOIN Поручения ON Работы.Шифр_работы = Поручения.Работа) ON Сотрудники.Табельный_номер = Поручения.Сотрудник WHERE (((Работы.Название)="Проект Гелиограф")) GROUP BY Сотрудники.Должность;

9. Поликлиника

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

• Номер, фамилия, имя, отчество, дата рождения пациента, социальный статус, текущее состояние;

• ФИО, должность, квалификация и специализация лечащего врача;

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

Текущее состояние - лечится, вылечился, направлен в стационар, умер. Социальный статус пациента - учащийся, работающий, временно неработающий, инвалид, пенсионер Специализация врача - терапевт, невропатолог и т.п. Квалификация врача - 1-я, 2-я, 3-я категория.

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

Выборки:

• Определить те случаи, когда заболевание 'язва желудка' лечилось врачом

специализации 'невропатолог'.

SELECT Врачи.ФИО, Врачи.Специализация, Диагнозы.Диагноз, Лечение.Дата_начала, Пациенты.ФИО FROM Пациенты INNER JOIN (Диагнозы INNER JOIN (Врачи INNER JOIN Лечение ON Врачи.Номер_врача = Лечение.Врач) ON Диагнозы.Номер = Лечение.Диагноз1) ON Пациенты.Номер = Лечение.Пациент WHERE (((Врачи.Специализация)="невропатолог") AND ((Диагнозы.Диагноз)="язва желудка"));

• Вывести имена тех врачей, которые работают исключительно с пенсионерами.

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

SELECT Врачи.ФИО, Пациенты.Социальный_статус, Count(Лечение.Номер_записи) AS [Count-Номер_записи] FROM Пациенты INNER JOIN (Врачи INNER JOIN Лечение ON Врачи.Номер_врача = Лечение.Врач) ON Пациенты.Номер = Лечение.Пациент GROUP BY Врачи.ФИО, Пациенты.Социальный_статус HAVING (((Пациенты.Социальный_статус)<>'пенсионер'));

Во втором запросе выбираем случаи лечения пенсионеров, группировка та же, что и в предыдущем случае

SELECT Врачи.ФИО, Пациенты.Социальный_статус, Count(Лечение.Номер_записи) AS [Count-Номер_записи] FROM Пациенты INNER JOIN (Врачи INNER JOIN Лечение ON Врачи.Номер_врача = Лечение.Врач) ON Пациенты.Номер = Лечение.Пациент GROUP BY Врачи.ФИО, Пациенты.Социальный_статус HAVING (((Пациенты.Социальный_статус)='пенсионер'));

И в третьем запросе делаем левое объединение второго и первого предыдущих запросов. При этом выбираются все записи из второго (случаи лечения пенсионеров) и связанные записи из первого (те, кто лечил и пенсионеров, и остальных). Те, кто вообще не лечил пенсионеров, в выборку не попадут. Те, кто лечил только пенсионеров, попадут в выборку с нулевым (пустым) значением любого поля первого запроса (см. рисунок). Вот по этим пустым значениям мы их и выловим.

SELECT Пенс2.ФИО FROM Пенс2 LEFT JOIN Пенс1 ON Пенс2.ФИО = Пенс1.ФИО WHERE (((IsNull([Пенс1]![Count-Номер_записи]))=Yes)); Смотрим второй вариант. Пишем сразу на SQL SELECT DISTINCT Врачи.ФИО FROM Врачи WHERE (((Врачи.[номер_врача]) Not In (SELECT Лечение.Врач FROM Лечение INNER JOIN Пациенты ON Лечение.Пациент =Пациенты.Номер WHERE Пациенты.[Социальный_статус]<>"Пенсионер")));

По этому запросу мы выбираем врачей, которых нет в списке лечивших пенсионеров. Казалось бы, все просто. Но есть одно «но»… В этот список попадут и те врачи, кто вообще никого не лечил. То есть нужно дописывать условие, что они кого-либо лечили.

• Определить процент смертности от заболевания 'кариес'.

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

SELECT Диагнозы.Номер, Count(Лечение.Номер_записи) AS [Count-Номер_записи] FROM Диагнозы INNER JOIN Лечение ON Диагнозы.Номер = Лечение.Диагноз1 GROUP BY Диагнозы.Номер;

Во втором будем считать процент в вычисляемом поле

SELECT Диагнозы.Диагноз, Лечение.Текущее_состояние, Count(Лечение.Номер_записи) AS [Count-Номер_записи1], [кариес1]![Count-Номер_записи] AS ОбщееЧисло, [Count-Номер_записи1]*100/[ОбщееЧисло] AS Процент FROM (Диагнозы INNER JOIN кариес1 ON Диагнозы.Номер = кариес1.Номер) INNER JOIN Лечение ON Диагнозы.Номер = Лечение.Диагноз1 WHERE (((Лечение.Текущее_состояние)="умер") AND ((Диагнозы.Диагноз)="кариес")) GROUP BY Диагнозы.Диагноз, Лечение.Текущее_состояние, [кариес1]![Count-Номер_записи];