Select l_name from student

Union all select distinct prof from professor order by l_name

Получим

l_ name

Волушкова

Дудаков

Иванов

Иванов

Климок

Колдунов

Михно

Сидоров

Сорокин

Сорокин

Сорокин

Федоров

Инструкция union выполняет объединение таблиц как множеств строк. Точно так же используются инструкции intersect [all] и except [all], означающие пересечение и вычитание таблиц соответ­ственно (как множеств).

Пример 49Например, чтобы узнать, какие фамилии встречаются как среди студентов, т,ак и среди преподавателей, можно выполнить запрос

select l_name from student

intersect select prof from professor order by l_name

l_ name Сорокин

Задача79 (Библиотека)Постройте список всех фамилий (авторов и читателей).

Задача 80 (Библиотека)Проверьте (с помощью intersect), есть ли среди читателей такие, имена которых полностью совпадают, с именем какого-либо автора.

Представления

Представления — это запросы, которые хранятся в базе данных и ис­пользуются как таблицы. Когда представление используется в качестве таблицы, то вместо него подставляется результат выполнения запроса. Для того чтобы отличать таблицы, реально хранящиеся в базе данных, от представлений реально хранящиеся таблицы называют основными. Представление создается с помощью оператора create view:

create view <имя представления> as <SQL-3anpoc>

Пример 50Например, предстлвление, содержащее информацию о сда-че студентами экзамена, по анализу:

create view analysis as select stud_nomer, dat, res from ball where dis = 'Анализ'

Напомним, что таблицы является неупорядоченной совокупностью строк, то же должно относиться и к представлениям: порядок следо­вания строк в представлении не определен. Поэтому в операторе SQL, образующем представление, инструкции order by быть не должно. Дру­гое важное условие на SQL-запрос: все его столбцы должны иметь име­на. Если в представлении в качестве столбцов используются какие-то выражения, то они обязательно должны быть поименованы с помощью as.

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

create view avg_ball as select stud_nomer, avg(res) as avg_res from ball group by stud_nomer

Теперь представление avg_ball можно использовать как т,аблицу, со­держащую два столбца: stud_nomer и avg_res. Например, чтобы выве­сти ее содержимое:

select * from avg_ball

 

stud nomer	avg_ res
010001	85
010002	66
011003	63
011004	63
011005	16

При изменении основной таблицы ball изменения автоматически от­разятся и в представлении avg_ball.

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

Задача 81 (Библиотека) Создайте представление, которое содер­жит информацию о выданных в настоящий момент книгах (когда вы­дана, кому, срок возврати).

Задача 82 (Университет) Создайте представление, которое будет, хранить информацию о всех персоналиях, с указанием их имени и по­ложения, например,

 

1_ пате	f name	т пате	position	additional
Иванов	Иван	Иванович	студент	1 курс
Колдунов	Владислав	Алексеевич	профессор	доктор физ.-мат. наук

Другой пример использования представлений — двойная группиров­ка данных. С помощью одного оператора select можно выполнить толь­ко одну группировку, например, найти средний балл для каждого сту­дента. Если же требуется еще и среди этих средних баллов найти самый высокий, то для этого придется использовать представление, так как вложенные агрегатные функции язык SQL не допускает:

select max(avg_res) . .

from avg_ball

Нельзя написать непосредственно:

select max(avg(res)) from ball group by stud_nomer

Однако, SQL допускает в качестве исходных таблиц запроса использо­вать другие запросы.

Пример52 Запрос (5) можно переписать в виде:

select max(avg_res)

from (select avg(res) as avg_res

from ball

group by stud_nomer)

To есть, вместо имени представления в инструкции from просто пишется SQL запрос, реализующий это представление.

Задача 83 (Супермаркет)Напишите следующий запрос с использо­ванием представлений и с использованием запросов в качестве исход­ных таблиц. Найти фирмы, продукция которых илкет, самую высокую среднюю цену.

Задача 84 (Банк)Напишите следующий запрос с использованием представлений и с использованием, запросов в качестве исходных таб­лиц. Найти количество счетов тех клиентов, которые имеют наи­большую общую сумму на своих счетах.

Задача 85 (CD) Напишите следующий запрос с использованием пред­ставлений и с использованием запросов в качестве исходных таблиц. Найт,и среднюю цену компакт-дисков, имеющих наибольшее количе­ство треков.

Задача 86 (CD) Напишите следующий запрос с использованием пред­ставлений и с использованием запросов в качестве исходных таблиц. Определить, сколько раз в каких ролях записывались арт,ист.ы, кото­рые выступали в какой-либо роли наибольшее число раз (по сравнению с другими артистами).

4 Изменение данных.