Where not exists

(SELECT * FROM СТ

WHERE СТ.Nгр = ГР.Nгр and СТ.Кст <> ГР.Кстг

and (EXISTS (SELECT В1.Nдц FROM ВЫБ as В1

WHERE В1.Кст = СТ.Кст

MINUS

SELECT В2.Nдц FROM ВЫБ as В2

WHERE В2.Кст = ГР.Кстг)

or EXISTS (SELECT В3.Nдц FROM ВЫБ as В3

WHERE В3.Кст = ГР.Кстг

MINUS

SELECT В4.Nдц FROM ВЫБ as В4

WHERE В4.Кст = СТ.Кст)))

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

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

Задача 22. Выдать фамилии студентов, которым помогают, с указанием фамилии помогающего ему студента.

SELECT СТ1.Фио, СТ2.Фио

FROM СТ as CT1, CT as CT2

WHERE CT1.Ккур = СТ2.Кст

Наконец, рассмотрим особенности использования агрегативных функций в SQL-запросах.

Чтобы студент точнее понимал идеологию SQL-запроса, напомним, что команда FROM обеспечивает полную возможную схему отношения, а команда WHERE селекцию кортежей, которые попадут в результат. Только на последнем этапе срабатывает команда SELECT, которая в результирующем отношении оставляет искомые атрибуты. При использовании агрегативных функций потенциальное отношение, формируемое командами FROM и WHERE, применением команды GROUP BY приобретает новую схему, а селекция кортежей получаемого отношения по новой схеме может быть выполнена командой HAVING. Команда SELECT уже применяется к отношению с новой схемой. Общий вид запроса с использованием агрегативной функции следующий

SELECT <подсписок списка атрибутов>, <список функций>

FROM <список отношений>

WHERE <условие>

GROUP BY <список атрибутов>

HAVING <условие, вычисленное по значениям функций>

Таким образом, потенциальная схема отношения, формируемого командой GROUP BY, состоит из списка атрибутов, указанных в этой команде, и атрибутов, домены которых определяются всеми функциями, упомянутыми как в команде SELECT, так и в команде HAVING.

В качестве примера рассмотрим задачу 21.

Задача 21. По всем выпускающим кафедрам дать количество выпущенных студентов, значения и количество минимальных и максимальных оценок.

SELECT КФ.Nкф, КФ0.Кво, КФ0.О1, КФ1.Кво1, КФ0.О2, КФ2.Кво2

FROM КФ, (SELECT СТ.Nкф, count() as Кво, min(Оценка) as О1, max(Оценка) as О2

FROM СТ

GROUP BY СТ.Nкф) as КФ0,

(SELECT СТ1.Nкф, СТ1.Оценка, count() as Кво1,

FROM СТ as СТ1

GROUP BY СТ1.Nкф, СТ1.Оценка) as КФ1,

(SELECT СТ2.Nкф, СТ2.Оценка, count() as Кво2,

FROM СТ as СТ2

GROUP BY СТ2.Nкф, СТ2.Оценка) as КФ2

WHERE КФ.Nкф = КФ0.Nкф

and КФ0.Nкф = КФ1.Nкф and КФ0.О1 = КФ1.Оценка

and КФ0.Nкф = КФ2.Nкф and КФ0.О2 = КФ2. Оценка

В данном решении команда FROM моделирует декартово произведение четырех отношений, причем КФ0 несет информацию о количестве выпускаемых студентов, а, также, о минимальной и максимальной оценках, полученных ими, а КФ1 и КФ2, являясь фактически тождественными, необходимы для получения требуемого результата.

Применение команды HAVING продемонстрируем на решении задачи 23.

Задача 23. Выдать названия дисциплин, названия отвечающих за них кафедр и количество "двоечников", по которым более пяти студентов получили двойки.

SELECT ДЦ.Назв, КФ.Назв, ДЦ0.Кво

FROM ДЦ, ОТВ, КФ,

(SELECT ОЦН.Nдц, count() as Кво FROM ОЦН

WHERE ОЦН.Оценка = "2"

GROUP BY ОЦН.Nдц HAVING count()>5) as ДЦ0,

WHERE ДЦ.Nдц = ОТВ.Nдц and ОТВ.Nкф = КФ.Nкф

and ДЦ.Nдц = ДЦ0.Nдц