07-08 Лекции - Язык SQL

SET R5.Стипендия = 25% WHERE R5.ФИО IN (SELECT R1.ФИО FROM R1

WHERE R1.ФИО NOT

IN (SELECT A.ФИО FROM R1 A

WHERE A.Оценка <=3 OR A.Оценка IS NULL) GROUP BY R1.ФИО HAVING COUNT(*)>=2 )

Назначение обычной стипендии:

UPDATE R5

SET R5.Стипендия = 0%

WHERE R5.ФИО IN (SELECT R1.ФИО FROM R1

WHERE R1.Оценка>=4 AND R1.ФИО NOT IN (SELECT A.ФИО FROM R1 A WHERE A.Оценка <= 3 OR A.Оценка IS NULL))

Снятие стипендии:

UPDATE R5

SET R5.Стипендия = —100% WHERE R5.ФИО IN (SELECT R1.ФИО FROM R1

WHERE R1.Оценка <= 2 OR

R1.Оценка IS NULL)

Почему мы в первом запросе на обновление не использовали дополнительную проверку на отсутствие двоек, троек и несданных экзаменов, как мы сделали это при назначении следующих видов стипендии? Просто мы учли особенности нашей предметной области: у нас в соответствии с исходными данными только 3 экзамена. Но если мы можем предположить, что число экзаменов может быть произвольным и изменяться от семестра к семестру, то нам надо изменить наш запрос. Запрос — это некоторый алгоритм решения конкретной задачи, которую мы формулируем заранее на естественном языке. И оттого, что наша задача решается всего одним оператором языка SQL, она не становится примитивной. Мощность языка SQL и состоит в том, что он позволяет одним предложением сформулировать ответы на достаточно сложные запросы, для реализации которых на традиционных языках понадобилось бы писать большую программу. Итак, подумаем, как нам надо изменить текст нашего запроса на обновление для назначения повышенной стипендии при любом количестве сданных экзаменов. Прежде всего, каждая группа может иметь свое число экзаменов в сессию, это зависит от специальности и учебного плана, по которому учится данная группа. Поэтому для каждого студента нам надо знать, сколько экзаменов он должен был сдавать и сколько экзаменов он сдал на пять, и в том случае, когда эти два числа равны, мы можем назначить ему повышенную стипендию.

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

Это мы делать умеем, для этого надо сделать запрос SELECT над отношением R3, сгруппировав его по атрибуту Группа, и вывести для каждой группы количество дисциплин, по которым должны сдаваться экзамены. Если мы учтем, что в одной сессии по одной дисциплине не бывает более одного экзамена, то можно просто подсчитывать количество строк в каждой группе.

SELECT R3.Группа, число экзаменов = COUNT(*) FROM R3 GROUP BY R3.Группа

Однако нам нужен не этот запрос, нам нужен запрос, в котором мы определяем для каждого студента количество экзаменов. Этот запрос мы должны строить по схеме встроенного запроса:

SELECT COUNT(*) FROM R3

WHERE R2.Группа = R3.Группа GROUP BY R3.Группа

А почему мы здесь в части FROM не написали имя второго отношения R2? Мы имя этого отношения укажем для связи с вышестоящим запросом, когда будем формировать запрос полностью. Теперь попробуем сформулировать полностью запрос. Нам надо объединить отношения R1 и R2 по атрибуту ФИО, нам надо знать группу, в которой учится каждый студент, далее надо выбрать все строки с оценкой 5 и сгруппировать их по фамилии студента, сосчитав количество строк в каждой группе, а выбирать мы будем те группы, в которых число строк в группе равно числу строк во встроенном запросе, рассмотренном ранее, при условии равенства количества строк в группе результату подзапроса, который выводит только одно число.

SELECT R1.ФИО FROM R1,R2

WHERE R1. ФИО = R2.ФИО AND R1.Оценка = 5

GROUP BY R1.ФИО HAVING COUNT(*) = (SELECT COUNT(*) FROM R3 WHERE R2.Группа = R3.Группа

GROUP BY R3.Группа)

Ну а теперь нам осталась последняя простейшая операция: надо заменить старый вложенный запрос, определявший отличников, получивших три пятерки на сессии, на новый универсальный запрос:

UPDATE R5

SET R5.Стипендия = 50%

WHERE R5.ФИО IN (SELECT R1.ФИО FROM R1, R2

WHERE R1. ФИО = R2.ФИО AND R1.Оценка = 5 GROUP BY R1.ФИО

HAVING COUNT(*) = (SELECT COUNT(*) FROM R3

WHERE R2.Группа = R3.Группа GROUP BY R3.Группа))