Язык SQL часть Б

Вложенные запросы

Теперь вернемся к БД "Сессия" и рассмотрим на ее примере использование вложенных запросов.

С помощью SQL можно вкладывать запросы внутрь друг друга. Обычно внутренний запрос генерирует значение, которое проверяется в предикате внешнего запроса (в предложении WHERE или HAVING), определяющего, верно оно или нет. Совместно с подзапросом можно использовать предикат EXISTS, который возвращает истину, если вывод подзапроса не пуст.

В сочетании с другими возможностями оператора выбора, такими как группировка, подзапрос представляет собой мощное средство для достижения нужного-результата. В части FROM оператора SELECT допустимо применять синонимы к именам таблицы, если при формировании запроса нам требуется более чем один экземпляр некоторого отношения. Синонимы задаются с использованием ключевого слова AS, которое может быть вообще опущено. Поэтому часть FROM может выглядеть следующим образом:

FROM R1 AS A, R1 AS B

или

FROM R1 A, R1 B;

оба выражения эквивалентны и рассматриваются как применения оператора SELECT к двум экземплярам таблицы R1.

Например, покажем, как выглядят на SQL некоторые запросы к БД "Сессия":

• Список тех, кто сдал все положенные экзамены.

Рекомендуемые материалы

· SELECT ФИО

· FROM R1 as a

· WHERE Оценка > 2

· GROUP BY ФИО

· HAVING COUNT(*) = (SELECT COUNT(*)

· FROM R2,R3

· WHERE R2.Группа=R3.Группа AND ФИО=a.ФИО)

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

• Список тех, кто должен был сдавать экзамен по БД, но пока еще не сдавал.

· SELECT ФИО

· FROM R2 a, R3

· WHERE R2.Группа=R3.Группа AND Дисциплина = "БД" AND NOT EXISTS (SELECT ФИО

· FROM R1

· WHERE ФИО=a.ФИО AND Дисциплина = "БД")

Предикат EXISTS ( SubQuery) истинен, когда подзапрос SubQuery не пуст, то есть содержит хотя бы один кортеж, в противном случае предикат EXISTS ложен.

Предикат NOT EXISTS обратно — истинен только тогда, когда подзапрос SubQuery пуст.

Обратите внимание, каким образом NOT EXISTS с вложенным запросом позволяет обойтись без операции разности отношений. Например, формулировка запроса со словом "все" может быть выполнена как бы с двойным отрицанием. Рассмотрим пример базы, которая моделирует поставку отдельных деталей отдельными поставщиками, она представлена одним отношением SP "Поставщики—детали" со схемой

SP (Номер_поставщика, номер_детали) P (номер_детали, наименование)

Вот каким образом формулируется ответ на запрос: "Найти поставщиков, которые поставляют все детали".

SELECT DISTINCT НОМЕР_ПОСТАВЩИКА FROM SP SP1 WHERE NOT EXISTS

(SELECT номер_детали FROM P WHERE NOT EXISTS

(SELECT * FROM SP SP2

WHERE SP2.номер_поставщика=SP1.номер_поставщика AND

sp2.номер_детали = P.номер_детали));

Фактически мы переформулировали этот запрос так: "Найти поставщиков таких, что не существует детали, которую бы они не поставляли". Следует отметить, что этот запрос может быть реализован и через агрегатные функции с подзапросом:

SELECT DISTINCT Номер_поставщика

FROM SP

GROUP BY Номер_поставщика

HAVING Count(DISTINCT номер_детали) =

(SELECT Count( номер_детали)

FROM P)

В стандарте SQL92 операторы сравнения расширены до многократных сравнений с использованием ключевых слов ANY и ALL. Это расширение используется при сравнении значения определенного столбца со столбцом данных, возвращаемым вложенным запросом.

Ключевое слово ANY, поставленное в любом предикате сравнения, означает, что предикат будет истинен, если хотя бы для одного значения из подзапроса предикат сравнения истинен. Ключевое слово ALL требует, чтобы предикат сравнения был бы истинен при сравнении со всеми строками подзапроса.

Например, найдем студентов, которые сдали все экзамены на оценку не ниже чем "хорошо". Работаем с той же базой "Сессия", но добавим к ней еще одно отношение R4, которое характеризует сдачу лабораторных работ в течение семестра:

R1 = (ФИО, Дисциплина, Оценка);

R2 = (ФИО, Группа);

R3 = (Группы, Дисциплина )

R4 = (ФИО, Дисциплина, Номерлабраб, Оценка);

Select R1.Фио From R1 Where 4 > = All (Select R1.Оценка

From R1 as R11

Where R1.Фио = R11.Фио)

Рассмотрим еще один пример:

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

Select R1.ФИО

From R1

Where R1.Оценка >= ANY (Select R4.Оценка

From R4

Where R1.Дисциплина = R4. Дисциплина AND R1.ФИО = R4.ФИО)

Внешние объединения

Стандарт SQL2 расширил понятие условного объединения. В стандарте SQL1 при объединении отношений использовались только условия, задаваемые в части WHERE оператора SELECT, и в этом случае в результирующее отношение попадали только сцепленные по заданным условиям кортежи исходных отношений, для которых эти условия были определены и истинны. Однако в действительности часто необходимо объединять таблицы таким образом, чтобы в результат попали все строки из первой таблицы, а вместо тех строк второй таблицы, для которых не выполнено условие соединения, в результат попадали бы неопределенные значения. Или наоборот, включаются все строки из правой (второй) таблицы, а отсутствующие части строк из первой таблицы дополняются неопределенными значениями. Такие объединения были названы внешними в противоположность объединениям, определенным стандартом SQL1, которые стали называться внутренними.

В общем случае синтаксис части FROM в стандарте SQL2 выглядит следующим образом:

FROM <список исходных таблиц>

<   выражение естественного объединения >

<   выражение объединения >

<   выражение перекрестного объединения >

<   выражение запроса на объединение >

<список исходных таблиц>::= <имя_таблицы_1> [ имя синонима таблицы_1] [ …] [,<имя_таблицы_n>[ <имя синонима таблицы_n> ] ]

<выражение естественного объединения>:: =

<имя_таблицы_1> NATURAL { INNER | FULL [OUTER] LEFT [OUTER] | RIGHT [OUTER]} JOIN <имя_таблицы_2>

<выражение перекрестного объединения>:: = <имя_таблицы_1> CROSS JOIN <имя_таблицы_2>

<выражение запроса на объединение>::=

<имя_таблицы_1> UNION JOIN <имя_таблицы_2>

<выражение объединения>::= <имя_таблицы_1> { INNER

FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER]}

JOIN {ON условие | [USING (список столбцов)]} <имя_таблицы_2>

В этих определениях INNER — означает внутреннее объединение, LEFT — левое объединение, то есть в результат входят все строки таблицы 1, а части результирующих кортежей, для которых не было соответствующих значений в таблице 2, дополняются значениями NULL (неопределено). Ключевое слово RIGHT означает правое внешнее объединение, и в отличие от левого объединения в этом случае в результирующее отношение включаются все строки таблицы 2, а недостающие части из таблицы 1 дополняются неопределенными значениями, Ключевое слово FULL определяет полное внешнее объединение: и левое и правое. При полном внешнем объединении выполняются и правое и левое внешние объединения и в результирующее отношение включаются все строки из таблицы 1, дополненные неопределенными значениями, и все строки из таблицы 2, также дополненные неопределенными значениями.

Ключевое слово OUTER означает внешнее, но если заданы ключевые слова FULL, LEFT, RIGHT, то объединение всегда считается внешним.

Рассмотрим примеры выполнения внешних объединений. Снова вернемся к БД "Сессия". Создадим отношение, в котором будут стоять все оценки, полученные всеми студентами по всем экзаменам, которые они должны были сдавать. Если студент не сдавал данного экзамена, то вместо оценки у него будет стоять неопределенное значение. Для этого выполним последовательно естественное внутреннее объединение таблиц R2 и R3 по атрибуту Группа, а полученное отношение соединим левым внешним естественным объединением с таблицей R1, используя столбцы ФИО и Дисциплина. При этом в стандарте разрешено использовать скобочную структуру, так как результат объединения может быть одним из аргументов в части FROM оператора SELECT.

SELECT R1.ФИО, R1.Дисциплина, R1.Оценка

FROM (R2 NATURAL INNER JOIN R3 ) LEFT JOIN R1 USING ( ФИО, Дисциплина)

Результат:

Рассмотрим еще один пример, для этого возьмем БД "Библиотека". Она состоит из трех отношений, имена атрибутов здесь набраны латинскими буквами, что является необходимым в большинстве коммерческих СУБД.

BOOKS(ISBN, TITLE, AUTOR, COAUTOR, YEARIZD, PAGES)

READER(NUM_READER, NAME_READER, ADRESS,

       HOOM_PHONE, WORK_PHONE, BIRTH_DAY)

EXEMPLARE(INV, ISBN, YES_NO, NUM_READER, DATE_IN, DATE_OUT)

Здесь таблица BOOKS описывает все книги, присутствующие в библиотеке, она имеет следующие атрибуты:

• ISBN — уникальный шифр книги;
• TITLE — название книги;
• AUTOR — фамилия автора;
• COAUTOR — фамилия соавтора;
• YEARIZD — год издания;
• PAGES — число страниц.

Таблица READER хранит сведения обо всех читателях библиотеки, и она содержит следующие атрибуты:

• NUM_READER — уникальный номер читательского билета;
• NAME_READER — фамилию и инициалы читателя;
• ADRESS — адрес читателя;
• HOOM_PHONE — номер домашнего телефона;
• WORK_PHONE — номер рабочего телефона;
• BIRTH_DAY — дату рождения читателя.

Таблица EXEMPLARE содержит сведения о текущем состоянии всех экземпляров всех книг. Она включает в себя следующие столбцы:

• INV — уникальный инвентарный номер экземпляра книги;
• ISBN — шифр книги, который определяет, какая это книга, и ссылается на сведения из первой таблицы;
• YES_NO — признак наличия или отсутствия в библиотеке данного экземпляра в текущий момент;
• NUM_READER — номер читательского билета, если книга выдана читателю, и Null в противном случае;
• DATE_IN — если книга у читателя, то это дата, когда она выдана читателю;
• DATE_OUT — дата, когда читатель должен вернуть книгу в библиотеку.

Определим перечень книг у каждого читателя; если у читателя нет книг, то номер экземпляра книги равен NULL. Для выполнения этого поиска нам надо использовать левое внешнее объединение, то есть мы берем все строки из таблицы READER и соединяем со строками из таблицы EXEMPLARE, если во второй таблице нет строки с соответствующим номером читательского билета, то в строке результирующего отношения атрибут EXEMPLARE.INV будет иметь неопределенное значение NULL:

SELECT READER.NAME_READER, EXEMPLARE.INV

FROM READER RIGHT JOIN EXEMPLARE

ON READER.NUM_READER=EXEMPLARE.NUM_READER

Операция внешнего объединения, как мы уже упоминали, может использоваться для формирования источников в предложении FROM, поэтому допустимым будет, например, следующий текст запроса:

SELECT *

FROM ( BOOKS LEFT JOIN EXEMPLARE)

LEFT JOIN (READER NATURAL JOIN EXEMPLARE)

USING (ISBN)

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

Перекрестное объединение в трактовке стандарта SQL2 соответствует операции расширенного декартова произведения, то есть операции соединения двух таблиц, при которой каждая строка первой таблицы соединяется с каждой строкой второй таблицы.

Операция запроса на объединение эквивалентна операции теоретико-множественного объединения в алгебре. При этом требование эквивалентности схем исходных отношений сохраняется. Запрос на объединение выполняется по следующей схеме:

SELECT — запрос

UNION

SELECT — запрос

UNION

SELECT — запрос

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

Например, нужно вывести список читателей, которые держат на руках книгу "Идиот" или книгу "Преступление и наказание". Вот как будет выглядеть запрос:

SELECT READER.NAME_READER

FROM READER, EXEMPLARE,BOOKS

WHERE EXEMPLARE.NUM_READER= READER.NUM_READER AND

EXEMPLRE.ISBN = BOOKS.ISBN AND

BOOKS.TITLE = "Идиот"

UNION

SELECT READER.NAME_READER

FROM READER, EXEMPLARE,BOOKS

WHERE EXEMPLARE.NUM_READER= READER.NUM_READER AND

EXEMPLRE.ISBN = BOOKS.ISBN AND

BOOKS.TITLE = "Преступление и наказание"

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

Запрос на объединение может объединять любое число исходных запросов.

Так, к предыдущему запросу можно добавить еще читателей, которые держат на руках книгу "Замок":

UNION

SELECT READER.NAME_READER

FROM READER, EXEMPLARE,BOOKS

WHERE EXEMPLARE.NUM_READER= READER.NUM_READER AND

EXEMPLRE.ISBN = BOOKS.ISBN AND

BOOKS.TITLE = "Замок"

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

SELECT — запрос

   UNION   ALL

SELECT -  запрос

   UNION   ALL

SELECT -  запрос

Однако тот же результат можно получить простым изменением фразы WHERE первой части исходного запроса, соединив локальные условия логической операцией ИЛИ и исключив дубликаты кортежей.

SELECT DISTINCT READER.NAME_READER

FROM READER, EXEMPLARE,BOOKS

WHERE EXEMPLARE.NUM_READER= READER.NUM_READER AND

EXEMPLRE.ISBN = BOOKS.ISBN AND

BOOKS.TITLE = "Идиот" OR

BOOKS.TITLE = "Преступление и наказание" OR

BOOKS.TITLE = "Замок"

Ни один из исходных запросов в операции UNION не должен содержать предложения упорядочения результата ORDER BY, однако результат объединения может быть упорядочен, для этого предложение ORDER BY с указанием списка столбцов упорядочения записывается после текста последнего исходного SELECT-запроса.

Операторы манипулирования данными

В операции манипулирования данными входят три операции: операция удаления записей — ей соответствует оператор DELETE, операция добавления или ввода новых записей — ей соответствует оператор INSERT и операция изменения (обновления записей) — ей соответствует оператор UPDATE. Рассмотрим каждый из операторов подробнее.

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

Оператор ввода данных INSERT имеет следующий синтаксис:

INSERT INTO имя_таблицы [(<список столбцов>) ]

VALUES (<список значений>)

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

INSERT INTO BOOKS ( ISBN,TITLE,AUTOR,COAUTOR,YEARIZD,PAGES)

  VALUES ("5-88782-290-2",

         "Аппаратные средства IBM PC. Энциклопедия",

         "Гук М. ","",2000,816)

В этой книге только один автор, нет соавторов, но мы в списке столбцов задали столбец COAUTOR, поэтому мы должны были ввести соответствующее значение

в разделе VALUES. Мы ввели пустую строку, потому что мы знаем точно, что нет соавтора. Мы могли бы ввести неопределенное значение NULL.

Так как мы вводим полную строку, то мы можем не задавать список столбцов, ограничиться только заданием перечня значений, в этом случае оператор ввода будет выглядеть следующим образом:

INSERT INTO BOOKS VALUES ("5-88782-290-2",

   "Аппаратные средства IBM PC. Энциклопедия",

   "Гук М.","",2000,816)

Результаты работы обоих операторов одинаковые.

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

INSERT INTO BOOKS ( ISBN,TITLE,AUTOR,YEARIZD,PAGES)

  VALUES ("5-88782-290-2",

          "Аппаратные средства IBM PC. Энциклопедия",

          "Гук М.",2000,816)

Столбцу COAUTOR будет присвоено в этом случае значение NULL.

Какие столбцы должны быть заданы при вводе данных? Это определяется тем, как описаны эти столбцы при описании соответствующей таблицы, и будет рассмотрено более подробно при описании языка Data Definition Language (DDL). Здесь мы пока отметим, что если столбец или атрибут имеет признак обязательный (NOT NULL) при описании таблицы, то оператор INSERT должен обязательно содержать данные для ввода в каждую строку данного столбца. Поэтому если в таблице все столбцы обязательные, то каждая вводимая строка должна содержать полный перечень вводимых значений, а указание имен столбцов в этом случае необязательно. В противном случае, если имеется хотя бы один необязательный столбец и вы не вводите в него значений, задание списка имен столбцов — обязательно.

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

INSERT INTO EXEMPLAR (INV,ISBN,YES_NO,NUM_READER,DATE_IN, DATE_OUT)

VALUES (1872, "5-88782-290-2",NO,

344,GetDate(),DateAdd(d,GetDate(),14))

И это означает, что мы выдали экземпляр книги с инвентарным номером 1872 читателю с номером читательского билете 344, отметив, что этот экземпляр не присутствует с этого момента в библиотеке, и определили дату выдачи книги как текущую дату (функция GetDate()), а дату возврата задали двумя неделями позднее, использовав при этом функцию DateAdd (), которая позволяет к одной дате добавить заданное количество интервалов даты и тем самым получить новое значение типа "дата". Мы добавили 14 дней к текущей дате.

Оператор ввода данных позволяет ввести сразу множество строк, если их можно выбрать из некоторой другой таблицы. Допустим, что у нас есть таблица со студентами и в ней указаны основные данные о студентах: их фамилии, адреса, домашние телефоны и даты рождения. Тогда мы можем сделать всех студентов читателями нашей библиотеки одним оператором:

INSERT INTO READER (NAME_READER, ADRESS, HOOM_PHONE, BIRTH_DAY)

SELECT (NAME_STUDENT, ADRESS, HOOM_PHONE, BIRTH_DAY)

FROM STUDENT

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

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

Синтаксис оператора DELETE следующий:

DELETE FROM имя_таблицы [WHERE условия_отбора]

Если условия отбора не задаются, то из таблицы удаляются все строки, однако это не означает, что удаляется вся таблица. Исходная таблица остается, но она остается пустой, незаполненной.

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

DELETE FROM R1

Условия отбора в части WHERE имеют тот же вид, что и условия фильтрации в операторе SELECT. Эти условия определяют, какие строки из исходного отношения будут удалены. Например, если мы исключим студента Миронова А. В., то мы должны написать следующую команду:

DELETE FROM R2

WHERE ФИО = 'Миронов А.В.'

В части WHERE может находиться встроенный запрос. Например, если нам надо исключить неуспевающих студентов, то по закону о высшем образовании неуспевающим считается студент, имеющий две и более задолженности по последней сессии. Тогда нам в условиях отбора надо найти студентов, имеющих либо две или более двоек, либо два и более несданных экзамена из числа тех, которые студент сдавал. Для поиска таких горе-студентов нам надо выбрать из отношения R1 все строки с оценкой 2 или с неопределенным значением, потом надо сгруппировать полученный результат по атрибуту ФИО и, подсчитав количество строк в каждой группе, которое соответствует количеству несданных экзаменов каждым студентом, отобрать те группы, у которых количество строк не менее двух. Теперь попробуем просто записать эту сложную конструкцию на SQL и убедимся, что этот сложный запрос записывается достаточно компактно.

DELETE FROM R2 WHERE R2.ФИО IN (SELECT R1.ФИО FROM R1

WHERE Оценка = 2 OR Оценка IS NULL

GROUP BY R1.ФИО HAVING COUNT(*) >= 2

Однако при выполнении операции DELETE, включающей сложный подзапрос, в подзапросе нельзя упоминать таблицу, из которой удаляются строки, поэтому СУБД отвергнет такой красивый подзапрос, который попытается удалить всех не только сдававших, но и несдававших студентов, которые имеют более двух задолженностей.

DELETE FROM R2 WHERE R2.ФИО IN (SELECT R1.ФИО

FROM (R2 NATURAL INNER JOIN R3 )

LEFT JOIN R1 USING ( ФИО, Дисциплина)

WHERE Оценка = 2 OR Оценка IS NULL

GROUP BY R1.ФИО

HAVING COUNT(*) >= 2

Все операции манипулирования данными связаны с понятием целостности базы данных, которое будет рассматриваться далее в лекции 9. В настоящий момент мне бы хотелось отметить только то, что операции манипулирования данными не всегда выполнимы, даже если синтаксически они написаны правильно. Действительно, если мы бы захотели удалить какую-нибудь группу из отношения R3, то СУБД не позволила бы нам это сделать, так как в отношениях R1 и R2 есть строки, связанные с удаляемой строкой в отношении R3. Почему так делается, мы узнаем позднее, а пока просто примем к сведению, что не все операторы манипулирования выполнимы.

Операция обновления данных UPDATE требуется тогда, когда происходят изменения во внешнем мире и их надо адекватно отразить в базе данных, так как надо всегда помнить, что база данных отражает некоторую предметную область. Например, в нашем учебном заведении произошло счастливое событие, которое связано с тем, что госпожа Степанова К. Е. пересдала экзамен по дисциплине "Базы данных" с двойки сразу на четверку. В этом случае нам надо срочно выполнить соответствующую корректировку таблицы R1. Операция обновления имеет следующий формат:

UPDATE имя_таблицы

SET имя_столбца = новое_значение [WHERE условие_отбора]

Часть WHERE является необязательной, так же как и в операторе DELETE. Она играет здесь ту же роль, что и в операторе DELETE, — позволяет отобрать строки, к которым будет применена операция модификации. Если условие отбора не задается, то операция модификации будет применена ко всем строкам таблицы.

Для решения ранее поставленной задачи нам необходимо выполнить следующую операцию

UPDATE R1

SET R1.Оценка  = 4

WHERE R1.ФИО = "Степанова  К.Е." AND R1.Дисциплина  = "Базы данных"

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

R4 = <Группа, Курс>

В этом случае перевод на следующий курс можно выполнить следующей операцией обновления:

UPDATE R4

SET R4.Kypc  = R4.Kypc  + 1

И результат будет выглядеть следующим образом:

Операция модификации, так же как и операция удаления, может использовать сложные подзапросы. Расширим нашу базу еще одним отношением, которое будет содержать перечень студентов, получающих стипендию с указанием надбавки, которую они получают за отличную учебу. Исходно там могут находиться все студенты с указанием неопределенного размера стипендии. По мере анализа отношения R1мы можем постепенно заменять неопределенные значения на конкретные размеры стипендии. Отношение R5 имеет вид:

Будем считать наличие трех пятерок по сессии признаком повышенной стипендии, + 50% к основной, наличие двух пятерок из сданных экзаменов и отсутствие двоек и троек на сданных экзаменах — признаком повышения стипендии на 25%, наличие хотя бы одной двойки среди сданных экзаменов — признаком снятия или отсутствия стипендии вообще, то есть –100% надбавки. При отсутствии троек на сданных экзаменах назначим обычную стипендию с надбавкой 0%. Однако все эти изменения мы должны будем сделать отдельными операциями обновления.

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

UPDATE R5

SET R5.Стипендия = 50% WHERE R5.ФИО IN

(SELECT R1.ФИО

FROM R1

WHERE R1.Оценка = 5

GROUP BY R1.ФИО

HAVING COUNT(*) =3 )

Назначение стипендии с надбавкой 25%:

UPDATE R5

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.ФИО

Бесплатная лекция: "4.5 Аналитическое конструирование регулятора" также доступна.

    FROM R1,R2

    WHERE R1. ФИО = R2.ФИО AND

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

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

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

Вот какой сложный запрос мы построили. Это ведь практически один оператор, а какую сложную задачу он решает. Действительно, мощность языка SQL иногда удивляет даже профессионалов, кажется невозможно построить один запрос для решения конкретной задачи, но когда начинаешь поэтапно его конструировать — все получается. Самое сложное — это сделать переход от словесной формулировки задачи к представлению ее в терминах нашего SQL, но этот процесс сродни процессу алгоритмизации при решении задач традиционного программирования, а он всегда был самым трудным, творческим и неформализуемым процессом. Недаром на заре развития программирования известный американский специалист по программированию Дональд Е. Кнут озаглавил свой многотомный капитальный труд по теории и практике программирования "Искусство программирования для ЭВМ" ("The art of computer programming").