 |
Students (N_zach, St_name,N_group)
|
|
|
|
Groups (N_group, Kod_spec)
Spec (Kod_spec, Name_spec)
Из нового для нас – это N_zach – номер зачетной книжки, Kod_spec – код специальности и Name_spec – название специльности. Например, код специальности - 351400, название - «Прикладная информатика в экономике».
Теперь попробуем сформировать корректные ответы на некоторые вопросы.
1. По какому числу специальностей ведется подготовка в моделируемом учебном заведении, или иначе говоря, сколько различных специальностей есть в нашей базе данных «Сессия».
Сначала подумаем, где хранится эта информация. Код специальности есть в отношении группы Groups и в отношении специальности Spec. Откуда нам его взять? А может быть все равно откуда? Оказывается, нет, не все равно. Если мы хотим узнать, какое количество различных специальностей, в принципе, поддерживает наше учебное заведение, то надо работать с отношением специальности, потому что в настоящий момент у нас может быть еще не набрано ни одной группы по новый специальностям, и тогда кодов этих специальностей нет в отношении Groups, но зато они есть в отношении Spec. Если же мы хотим узнать по какму числу специальностей уже набраны группы, то надо работать с отношением Groups. Мы будем считать, что у нас первый вариант вопроса, т.е. просто по какому числу специальностей может в нашем Вузе вестись подготовка. Ну а теперь сосчитаем количество различных специальностей в отношении Spec.
Select count(distinct Kod_spec)
From Spec
Или
Select count (distinct Name_spec)
From Spec
Потому что имена специальностей тоже должны быть разными, однако имя специальности – это целое предложение, а код – это просто число, поэтому, скорее всего первый запрос выполнится быстрее. Но в нашем случае в отношении Spec нам надо просто сосчитать количество строк. Потому что все они различны.
|
|
|
Поэтому правильным будет и запрос
Select Count(*)
From Spec
2. Вопрос: сколько групп учится по каждой специальности?
Где будем искать ответ? В отношении группы Groups. Мысгруппируем строки в этом отношении по равеству значения код специальности Kod_spec и для каждого кода специальности сосчитаем количество строчек в группе, а это и есть количество учебных групп.
Select Kod_spec, Count(*)
From Groups
Group By Kod_spec
3. Сколько студентов учится в каждой группе?
Этот вопрос по структуре аналогичен предыдущему, только информация о студентах находится в другом отношении, в отношении Students.
Select N_group,count(*)
From Students
Group By N_group
В дальнейшем в качестве примера будем работать не БД «Сессия», а с БД «Банк», состоящей из одной таблицы F, в которой хранится отношение 
, содержащее информацию о счетах в филиалах некоторого банка:
,
поскольку на этой таблице можно ярче проиллюстрировать работу с агрегатными функциями и группировкой.
Например, предположим, что мы хотим найти суммарный остаток на счетах в филиалах. Можно сделать раздельный запрос для каждого из них, выбрав SUM(Остаток) из таблицы для каждого филиала. GROUP BY, однако, позволит поместить их все в одну команду:
SELECT Филиал, SUM(Остаток)
FROM F
GROUP BY Филиал;
GROUP BY применяет агрегатные функции независимо для каждой группы, определяемой с помощью значения поля Филиал. Группа состоит из всех строк с одинаковым значением поля Филиал, и функция SUM применяется отдельно для каждой такой группы, то есть суммарный остаток на счетах подсчитывается отдельно для каждого филиала. Значение поля, к которому применяется GROUP BY, имеет, по определению, только одно значение на группу вывода, как и результат работы агрегатной функции. Поэтому мы можем совместить в одном запросе агрегат и поле. Вы можете также использовать GROUP BY с несколькими полями.
|
|
|
Предположим, что мы хотели бы увидеть только те суммарные значения остатков на счетах, которые превышают $5000. Мы не можем использовать агрегатную функцию в предложении WHERE, потому что предикаты оцениваются в терминах одиночной строки, а агрегатные функции — в терминах групп строк. Чтобы увидеть суммарные остатки свыше $5000, необходимо использовать предложение HAVING. Предложение HAVING определяет критерии, используемые чтобы удалять определенные группы из вывода, точно также как предложение WHERE делает это для индивидуальных строк.
Правильной командой будет следующая:
SELECT Филиал, SUM(Остаток)
FROM F
GROUP BY Филиал
HAVING SUM(Остаток) > 5000;
Аргументы в предложении HAVING следуют тем же самым правилам, что и в предложении SELECT, где используется GROUP BY. Они должны иметь одно значение на группу вывода.
Следующая команда будет запрещена:
SELECT Филиал, SUM(Остаток)
FROM F
GROUP BY Филиал
HAVING ДатаОткрытия = 27/12/1997;
Поле ДатаОткрытия не может быть использовано в предложении HAVING, потому что оно может иметь больше чем одно значение на группу вывода. Чтобы избегать такой ситуации, предложение HAVING должно ссылаться только на агрегаты и поля выбранные GROUP BY. Имеется правильный способ сделать вышеупомянутый запрос:
SELECT Филиал, SUM(Остаток)
FROM F
WHERE ДатаОткрытия = 27/12/1997
GROUP BY Филиал;
Как и говорилось ранее, HAVING может использовать только аргументы, которые имеют одно значение на группу вывода. Практически, ссылки на агрегатные функции — наиболее общие, но и поля, выбранные с помощью GROUP BY также допустимы. Например, мы хотим увидеть суммарные остатки на счетах филиалов в Санкт‑Петербурге, Пскове и Урюпинске:
SELECT Филиал, SUM(Остаток)
FROM F
GROUP BY Филиал
HAVING Филиал IN ("Санкт‑Петербург", "Псков", "Урюпинск");
Поэтому в арифметических выражениях предикатов, входящих в условие выборки раздела HAVING, прямо можно использовать только спецификации столбцов, указанных в качестве столбцов группирования в разделе GROUP BY. Остальные столбцы можно специфицировать только внутри спецификаций агрегатных функций COUNT, SUM, AVG, MIN и MAX, вычисляющих в данном случае некоторое агрегатное значение для всей группы строк. Аналогично обстоит дело с подзапросами, входящими в предикаты условия выборки раздела HAVING: если в подзапросе используется характеристика текущей группы, то она может задаваться только путем ссылки на столбцы группирования.
|
|
|
Результатом выполнения раздела HAVING является сгруппированная таблица, содержащая только те группы строк, для которых результат вычисления условия поиска есть true. В частности, если раздел HAVING присутствует в табличном выражении, не содержащем GROUP BY, то результатом его выполнения будет либо пустая таблица, либо результат выполнения предыдущих разделов табличного выражения, рассматриваемый как одна группа без столбцов группирования.
Теперь вернемся к БД «Сессия» и рассмотрим на ее примере использование вложенных запросов.
С помощью SQL можно вкладывать запросы внутрь друг друга. Обычно, внутренний запрос генерирует значение, которое проверяется в предикате внешнего запроса (в предложении WHERE или HAVING), определяющего, верно оно или нет. Совместно с подзапросом можно использовать предикат EXISTS, который возвращает истину, если вывод подзапроса не пуст.
Синтаксическая диаграмма вложенного запроса изображена на рис.11.
В сочетании с другими возможностями оператора выбора, такими как группировка, подзапрос представляет собой мощное средство для достижения нужного результата. В части 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 (Номер_поставщика, номер_детали)
Вот каким образом формулируется ответ на запрос: «Найти поставщиков, которые посталяют все детали».
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(номер_детали) = (SELECT Count(DISTNCT номер_детали)
FROM sp)
Стандарт SQL92
В стандарт SQL92 операторы сравнения расширены до многократных сравнений с использованием ключевых слов ANY и ALL. Это расширение используется при сравнении значения определенного столбца со столбцом данных возвращаемым вложенным запросом. Синтаксические диаграммы операций многократного сравнения ANY и ALL приведены на рис.12.
Операция ANY и операция ALL применяются только соовместно с одной из допустимых опреаций сравнения (=, <>, >=, <=, >, <).
|
|
|
