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

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

• Номер, фамилия, имя, отчество, дата рождения пациента, социальный статус, текущее состояние;
• ФИО, должность, квалификация  и специализация лечащего врача;
• диагноз, поставленный данным врачом данному пациенту, необходимо ли амбулаторное лечение, срок потери трудоспособности, состоит ли на диспансерном учете, дата начала лечения.

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

1. Определить те случаи, когда заболевание 'язва желудка' лечилось врачом специализации 'невропатолог'.
2. Вывести имена тех врачей, которые работают исключительно с пенсионерами.
3. Определить процент смертности от заболевания 'кариес'.
4. Пациентов, которые болеют (болели) всеми болезнями.

Создаем базу, делаем таблицы. Диагнозы нужно вынести в отдельную таблицу, поскольку в одном из запросов нам нужно будет знать их общее число. Проще посчитать строки в таблице, чем перечислять все элементы списка значений. И еще – текущее состояние нужно перенести от пациента к конкретному случаю лечения. А иначе диагноз не привязать к смертности.
Вот так получаются таблицы Пациенты, Врачи, Диагнозы, Лечение. Таблица со случаями лечения связывает остальные три таблицы.

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

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

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

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 Пациенты.[Социальный_статус]<>"Пенсионер")));

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

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-Номер_записи];

Считаем число умерших от кариеса и с помощью первого запроса высчитываем процент.
4 В последнем запросе нужно найти несчастных пациентов, болевших всеми болезнями.
Первый вариант – три запроса
В первом запросе выбираем пациентов и считаем, сколькими болезнями они болели:

SELECT Пациенты.ФИО, Count(Лечение.Диагноз1) AS [Count-Диагноз]
FROM Пациенты INNER JOIN Лечение ON Пациенты.Номер = Лечение.Пациент
GROUP BY Пациенты.ФИО;

Во втором считаем число болезней:

SELECT Count(Диагнозы.Номер) AS [Count-Номер]
FROM Диагнозы;

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

SELECT Все1.ФИО
FROM Все1, Все2
WHERE (((Все1.[Count-Диагноз])=[Все2]![Count-Номер]));

И второй вариант – делаем то же самое, только на SQL и одним запросом

SELECT DISTINCT Пациенты.ФИО
FROM Пациенты INNER JOIN (Диагнозы INNER JOIN Лечение ON Диагнозы.Номер = Лечение.Диагноз1) ON Пациенты.Номер = Лечение.Пациент
WHERE ((((SELECT COUNT ( Лечение.Диагноз1 ) FROM  Лечение WHERE Лечение.Пациент = Пациенты.Номер ))=(SELECT COUNT ( Диагнозы.[Номер]) FROM  Диагнозы )));