Использование агрегатных функций в подзапросах.

Как видно из рассмотренного ранее лекционного материала, одним из видов функций, которые автоматически выдают в результате единственное значение для любого количества строк, являются агрегатные функции. Любой запрос, использующий единственную агрегатную функцию без предложения GROUP BY, дает в результате единственное значение для использования его в основном предикате (условии).

Например, если необходимо узнать про все заказы, стоимость которых превышает среднюю стоимость заказов за 4 октября 1990 г., то используя функцию AVG запрос будет выглядеть:

 

SELECT * FROM Orders

WHERE kol >

(SELECT AVG(kol)

FROM Orders

WHERE data=’04.10.90’);

Сгруппированные, то есть примененные с предложением GROUP BY, агрегатные функции могут дать в результате множество значений. Поэтому так их нельзя применять в подзапросах. Такие команды отвергаются в принципе, не смотря на то, что применение GROUP BY в некоторых случаях дает единственную группу в качестве выходных данных подзапроса. Для исключения ненужных групп следует применить единственную агрегатную функцию с предложением WHERE.

 

!!!!! То, что сейчас вы прочитали, может привести вас к выводу, что результатом подзапросов может быть только единственное значение, но в sql можно сформировать несколько строк, используя оператор IN.

 

Применение подзапросов, которые формируют множественные строки с помощью IN.

 

Можно формулировать подзапросы, в результате выполнения которых получается любое количество строк, применяя специальный оператор IN. Операторы BETWEEN, LIKE, IS NULL в подзапросах применять нельзя. IN определяет множество значений, которые тестируются на совпадение с другими значениями для определения истинности предиката. Когда IN применяется в подзапросе, SQL просто строит это множество из выходных данных подзапроса. Например, найти все заявки для продавцов из Киева:

 

SELECT * FROM Orders

WHERE person_n IN

(SELECT person_n

FROM PRODAV

WHERE city=’Киев’;

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

Примечание!

1. Общая черта всех рассмотренных подзапросов состоит в том, что они выбирают единственный столбец. Это существенно, так как выходные данные вложенного SELECT – предложения сравниваются с единственным значением. Из этого следует, что вариант SELECT * нельзя использовать в подзапросе. Исключением из этого правила являются подзапросы с оператором EXISTS, который рассмотри позднее.

2. В предложении подзапроса SELECT можно использовать выражения, основанные на столбцах, а не сами столбцы. Это можно сделать, применяя операторы отношения или IN. Например, следующий запрос использует оператор отношения =:

 

SELECT * FROM Pokypat

WHERE ynik_n =

(SELECT person_n + 1000

FROM Prodav

WHERE comm > 0.2);

Данный запрос находит всех покупателей, для которых работают продавцы, персональный номер которых начинается с 1001 и комиссионные которых больше 0,20.

 

Связанные подзапросы.

При применении подзапросов, во внутреннем запросе (вложенном) можно ссылаться на таблицу, имя которой указано в предложении FROM внешнего запроса, тем самым формируя связанный подзапрос. В этом случае подзапрос выполняется повторно, по одному разу для каждой строки таблицы из основного запроса.

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

 

SELECT person_n, name

FROM Prodav main

WHERE 1 <

(SELECT COUNT(*)

FROM Pokypat

WHERE person_n=main.person_n);

 

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

 

Представления (VIEW).

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

 

Представление определяется с помощью команды CREATE VIEW, состоящей из ключевых слов CREATE VIEW (т.е. создать представление), имени создаваемого представления и ключевого слова AS, после которого следует запрос:

 

CREATE VIEW имя представления AS SELECT * FROM имя таблицы WHERE условие;

 

Например, чтобы сразу получить данные о тех, кто проживает в городе Москва, можно сначала создать представление, а потом вызывать его:

 

CREATE VIEW Moskovcity AS SELECT * FROM Prodav

WHERE city=’Москва’;

 

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

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

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

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

 

CREATE VIEW Dann

AS SELECT person_n, name, city

FROM Prodav;

 

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

При обновлении представление можно модифицировать с помощью команд обновления, но модификации воздействуют не на само представление, а только на лежащую в его основе таблицу. Т.е. если необходимо перезаписать место жительства продавца с номером 100 в представлении Dann, то обновление нужно произвести с таблицей Prodav:

 

UPDATE Prodav

SET city=’Анапа’

WHERE person_n=100;

 

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

§ Некоторые столбцы являются выходными столбцами и, следовательно не поименованы;

§ Два и более столбцов в соединении имеют одинаковые имена в соответствующих таблицах.

 

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

 

Групповые представления.

Групповые представления – это представления, которые содержат предложение GROUP BY или базируются на других групповых представлениях.

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

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

 

CREATE VIEW ProdavPodchet

AS SELECT date, count(person_n), count(ynik_n), count(n), avg(kol), sum(kol) FROM Orders GROUP BY date;

 

Теперь можно получить всю необходимую информацию с помощью единственного запроса:

SELECT * FROM ProdavPodchet;

 

Удаление представлений.

Синтаксис исключения представления из базы данных сходен с синтаксисом для исключения базовых таблиц:

 

DROP VIEW <имя представления>;

 

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

Выполнение лабораторной работы:

 

1. В начале работы загрузите программный продукт: Пуск - Программы - Quest Software - TOAD - TOAD.

2. В диалоговом окне “Вход в систему” выберите Database – ZED, User - 4294, пароль Password: 4294 и выберите Connect as - Normal.

3. Откройте окно для работы SQL - окно ввода командной строки.

4. Откройте окно Schema Browser - окно просмотра объектов для просмотра структуры используемых в запросах таблиц.

5. В окне SQL наберите команду, выполняющую команду Select. Например:

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

Текст запроса:

Select familiy, persons_1.name, otch, formy_ob_1.name as forma

From persons_1, obuchenie_1, gruppy_1, formy_ob_1

where persons_1.id_pers = obuchenie_1.id_pers and obuchenie_1.id_gr = gruppy_1.id_gr and gruppy_1.id_f = formy_ob_1.id_f and formy_ob_1.name =: p_forma;

 

 

рисунок 12.1. Вывод команды

 

 

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

 

 

рисунок 13.3. Вывод выходных данных

 

 

6. Аналогичным образом выполнить заданные как рабочее задание запросы.

7. Сдать работу преподавателю.

 

 

Контрольные вопросы:

1. В каких случаях используется предложение where, а в каких - having?

2. Каким символом шаблона можно заменить список столбцов в команде Select?

3. Для чего предназначены булевы выражения? Как они работают?

4. На сколько различаются запросы с реляционными и булевыми операторами?