ВВЕДЕНИЕ

Язык структурных запросов SQL

ВВЕДЕНИЕ

Большинство современных СУБД построено на реляционной модели данных. Для получения информации из отношений (таблиц) базы данных в качестве языка манипулирования данными в теоретическом плане используются три абстрактных языка:

язык реляционной алгебры;

язык реляционного исчисления на кортежах;

язык реляционного исчисления на доменах.

В качестве практического языка работы с данными в середине 70-х годов фирмой IBM разработан язык структурных запросов SQL, ставший впоследствии стандартом de-facto при работе с базами данных. Наметившееся в настоящее время переход к крупным корпоративным СУБД типа Oracle, Informix, Sybase, DB2, Progress делает актуальным изучение языка SQL как в практическом плане, так и чисто теоретически, поскольку в основе элементов языка SQL лежат положения теории отношений, теории множеств и логики.

Методические указания по изучению языка SQL

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

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

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

Таблица поставщиков S:

Create table S (n_post char(5) not NULL,

name char(20),

reiting smallint,

town char(15))

Таблица деталей Р:

Create table P (n_det char(6) ,

name char(20),

cvet char(7),

ves smallint,

town char(15))

Таблица поставок SP:

Create table SP (n_post char(5) ,

n_det char(6),

date_post date,

kol smallint)

Содержание таблиц базы данных следующее:

Таблица поставщиков (S)

Hомеp_поставщика	Фамилия	Рейтинг	Гоpод
S1	Смит	20	Лондон
S2	Джонс	10	Париж
S3	Блейк	30	Париж
S4	Кларк	20	Лондон
S5	Адамс	30	Атенс

Таблица деталей (P)

Номер детали	Название	Цвет	Вес	Гоpод
P1	Гайка	Красный	12	Лондон
P2	Болт	Зеленый	17	Париж
P3	Винт	Голубой	17	Рим
P4	Винт	Красный	14	Лондон
P5	Кулачок	Голубой	12	Париж
P6	Блюм	Красный	19	Лондон

Таблица поставок (SP)

Номер поставщика	Номер детали	Дата поставки	Количество
S1	P1	02/01/95	300
S1	P2	04/05/95	200
S1	P3	05/12/95	400
S1	P4	06/15/95	200
S1	P5	07/22/95	100
S1	P6	08/13/95	100
S2	P1	03/03/95	300
S2	P2	06/12/95	400
S3	P2	04/04/95	200
S4	P2	03/23/95	200
S4	P4	06/17/95	300
S4	P5	08/22/95	400

В качестве инструментария для выполнения заданий лабораторных работ в рамках СУБД Informix можно использовать интерактивные программы Informix-SQL и DBAccess. В процессе работы при выполнении заданий необходимо уметь подготавливать запросы в текстовом редакторе, сохранять и выполнять их.

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

I. Простые запросы на языке SQL

Запрос на языке SQL формируется с использованием оператора Select. Оператор Select используется

для выборки данных из базы данных;
для получения новых строк в составе оператора Insert;
для обновления информации в составе оператора Update.

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

спецификатор Select;

спецификатор From;

спецификатор Where;

спецификатор Group by;

спецификатор Having;

спецификатор Order by;

спецификатор Into temp.

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

Спецификатор Where добавляется для выборки определенных строк или указания условия соединения. Спецификатор Order by добавляется для изменения порядка получаемых данных. Спецификатор Into temp добавляется для сохранения этих результатов в виде таблицы с целью выполнения последующих запросов. Два дополнительных спецификатора оператора Select - Group by (спецификатор группирования) и Having (спецификатор условия выборки группы) - позволяют выполнять более сложные выборки данных.

У п р а ж н е н и я

1. Выбор всех строк и столбцов таблицы.

Пример.

Выдать полную информацию о поставщиках.

Select * from S

Результат: таблица S в полном объеме.

Подготовьте запрос и проверьте полученный результат.

2. Измение порядка следования столбцов.

Пример.

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

Select фамилия, город, рейтинг, номер_поставщика from S

Результат: таблица S в требуемом порядке.

Подготовьте запрос и проверьте полученный результат.

3. Выбор заданных столбцов.

Пример.

Выдать номера всех поставляемых деталей.

Select номер_детали from SP

Результат: столбец номер_детали таблицы SP

Подготовьте запрос и проверьте полученный результат.

4. Выбор без повторения.

Пример.

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

Select distinct номер_детали from SP

Результат:	номер_детали
	P1
	P2
	P3
	P4
	P5
	P6

Подготовьте запрос и проверьте полученный результат.

5. Использование в запросах констант и выражений.

Пример.

Select номер_детали, "вес в граммах", вес*454 from P

Результат:	P1 вес в граммах=5448
	---------------------
	---------------------
	P6 вес в граммах=8226

Подготовьте запрос и проверьте полученный результат.

6. Выборка подстрок.

Пример.

Выдать сокращение фамилий до двух букв и рейтинг поставщика.

Select фамилия[1,2], рейтинг from S

Результат:	См	20
	Дж	10
	Бл	30
	Кл	20
	Ад	30

Подготовьте запрос и проверьте полученный результат.

7.Ограничение в выборке.

Пример.

Выдать номера всех поставщиков, находящихся в Париже с рейтингом > 0.

Select номер_поставщика from S

where город="Париж" and рейтинг>20

Результат:	номер_поставщика
	S3

Подготовьте запрос и проверьте полученный результат.

8. Выборка с упорядочиванием.

Пример.

Выдать номера поставщиков, находящихся в Париже в порядке убывания рейтинга.

Select номер_поставщика from S

where город="Париж" order by рейтинг desc

Результат:	номер_поставщика	рейтинг
	S3	30
	S2	10

Подготовьте запрос и проверьте полученный результат.

9. Упорядочивание по нескольким столбцам.

Пример.

Выдать список поставщиков, упорядоченных по городу, в пределах города - по рейтингу.

Select * from S order by 4, 3

Результат:	Hомеp_поставщика	Фамилия	Рейтинг	Гоpод
	S5	Адамс	30	Атенс
	S1	Смит	20	Лондон
	S4	Кларк	20	Лондон
	S2	Джонс	10	Париж
	S3	Блейк	30	Париж

Подготовьте запрос и проверьте полученный результат.

10. Фраза between.

Пример.

Выдать информацию о деталях, вес которых лежит в диапазоне от 16 до 19.

Select номер_детали, название, вес from P

where вес between 16 and 19

Результат:	номер_детали	название	вес
	P2	Болт	17
	P3	Винт	17
	P6	Блюм	19

Подготовьте запрос и проверьте полученный результат.

11. Фраза in ( not in ).

Пример.

Выдать детали, вес которых равен 12, 16 или 17.

Select номер_детали, название, вес from P

where вес in (12, 16, 17)

Результат:	номер_детали	Название	вес
	P1	Гайка	12
	P2	Болт	17
	P3	Винт	17
	P5	Кулачок	12

Подготовьте запрос и проверьте полученный результат.

12. Выбор по шаблону.

Для запросов с поиском по шаблону, основанных на поиске подстрок в полях типа CHARACTER, используются ключевые слова LIKE и MATCHES.

Включение в выражение ключевого слова NOT порождает условие c обратным смыслом. Ключевое слово LIKE соответствует стандарту ANSI, а MATCHES является расширением INFORMIX.

СИМВОЛ	ЗНАЧЕНИЕ
LIKE
%	заменяет последовательность символов
-	заменяет любой одиночный символ
\	отменяет специальное назначение следующего за ним символа
MATCHES
*	заменяет последовательность символов
?	заменяет любой одиночный символ
[]	служит для замены одиночного символа или символа из диапазона
\	отменяет специальное назначение следующего за ним символа

Примеры.

а) Выбрать список деталей, начинающихся с буквы "Б"

Select номер_детали, название, вес from P

where название like "Б%"

Результат:	номер_детали	название	вес
	P5	Болт	12
	P6	Блюм	19

б) Выдать список фамилий поставщиков, начинающихся с букв [В-Н]

Select фамилия from S

where фамилия matches "В-Н"

Результат:

фамилия

Джонс

Блейк

Кларк

Подготовьте запрос и проверьте полученный результат.

II. Использование функций

1. Агрегатные функции.

count - число значений в столбце

sum - сумма значений по столбцу

avg - среднее значение в столбце

Примеры.

а) Выдать общее количество поставщиков.

Select count (*) from S

Результат: 5

Подготовьте запрос и проверьте полученный результат.

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

Select count ( distinct номер_поставщика ) from SP

Результат: 4

Подготовьте запрос и проверьте полученный результат.

в) Выдать количество поставок для детали P2.

Select count (*) from SP

where номер_детали='P2'

Результат: 4

Подготовьте запрос и проверьте полученный результат.

г) Выдать общее количество поставляемых деталей 'P2'.

Select sum (количество) from SP

where номер_детали='P2'

Результат: 1000

Подготовьте запрос и проверьте полученный результат.

д) Выдать средний, минимальный и максимальный объем поставок для поставщика S1 с соответствующим заголовком.

Select avg(количество) average,

min(количество) minimum,

max(количество) maximum

from SP where номер_поставщика='S1'

Результат:	average	minimum	maximum
	216.6	100	400

Подготовьте запрос и проверьте полученный результат.

2. Временные функции.

day - день месяца

current - текущая дата

month - значение месяца

Пример.

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

Select номер_детали, количество

Day(дата_поставки) day,

Month(дата_поставки) month,

Weekday(дата_поставки) weekday,

Day(дата_поставки) - -current

from SP

Результат:

Номер_детали	Количество	Day	month	weekday	(Expression)
P1	300	1	2	3	. . .
P2	200	5	4	3	. . .
. . .	. . .	. . .	. . .	. . .	. . .

Подготовьте запрос и проверьте полученный результат.

3. Другие функции.

length - размер в байтах символьной строки ( поля таблицы )

hex - 16-й код выражения

round - округленное значение выражения

trunc - обрезание значения выражения

III. Запросы, использующие соединения

1. Простое эквисоединение.

Пример.

Выдать все комбинации информации о поставщиках и деталях, расположенных в одном городе.

Select S.*,P.* from S, P

where S.город=P.город

Результат:

н_пост	фам-я	рейтинг	s.город	н_дет	назв-е	цвет	вес	p.город
S1	Смит	20	Лондон	P1	Гайка	красный	12	Лондон
S1	Смит	20	Лондон	P4	Винт	красный	14	Лондон
S1	Смит	20	Лондон	P6	Блюм	красный	19	Лондон
S2	Джонс	10	Париж	P2	Болт	зеленый	17	Париж
. . .	. . .	. . .	. . .	. . .	. . .	. . .	. . .	. . .

Подготовьте запрос и проверьте полученный результат.

2. Эквисоединение с дополнительным условием.

Пример.

Выдать все комбинации информации о поставщиках и деталях, расположенных в одном городе, опустив поставщиков с рейтингом = 20.

Select S.номер_поставщика, p.номер_детали, рейтинг

from S, P

where S.город=P.город and S.рейтинг<>20

Результат:	Hомеp_поставщика	Номер_детали	Рейтинг
	S2	P2	10
	S2	P5	10
	S3	P2	30
	S3	P5	30

Подготовьте запрос и проверьте полученный результат.

3. Соединение таблицы с ней самой.

Пример.

Выдать все пары поставщиков из одного города.

Select one.номер_поставщика, two.номер_поставщика

from S one, S two

where one.город = two.город

and

one.номер_поставщика < two.номер_поставщика

Результат:	Hомеp_поставщика	Номер_поставщика
	S1	S4
	S2	S3

Подготовьте запрос и проверьте полученный результат.

4. Соединение трех таблиц.

Пример.

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

Select distinct S.город, P.город

from S, SP, P

where S.номер_поставщика = SP.номер_поставщика

and

SP.номер_детали = SP.номер_детали

Результат:	s.город	p.город
	Лондон	Лондон
	Лондон	Париж
	Лондон	Рим
	Париж	Лондон
	Париж	Париж

Подготовьте запрос и проверьте полученный результат.

IV. Группирование

1. Оператор group by группирует таблицу, представленную фразой from в группы т.о., чтобы в каждой группе все строки имели одно и тоже значение поля, указанного во фразе group by. Далее, к каждой группе перекомпанованной таблицы ( а не к каждой строке исходной таблицы) применяется фраза select, в результате чего, каждое выражение во фразе select принимает единственное значение для группы.

Пример.

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

Select номер_детали, sum( количество)

from SP

where номер_поставщика

group by номер_детали

Результат:	Hомеp_поставщика	(Sum)
	P1	300
	P2	800
	P4	300
	P5	400

Подготовьте запрос и проверьте полученный результат.

2. Фраза having.

Фраза having играет ту же роль для групп, что и фраза where для строк и используется для того, чтобы исключать группы, точно так же, как where используется для исключения строк. Выражение во фразе having должно принимать единственное значение для группы.

Пример.

Выдать номера деталей, поставляемых более чем одним поставщиком.

Select номер_детали

from SP

group by номер_детали

having count(*) > 1

Результат:	Номер_детали
	P1
	P2
	P4
	P5

Подготовьте запрос и проверьте полученный результат.

V. Использование значений rowid-столбца

Для нахождения в таблице дубликата значения можно использовать в самосоединении скрытый rowid-столбцец. Условие x.rowid != y.rowid, заданное в следующем примере эквивалентно следующему утверждению: строка x не является той же самой, что и строка y.

Пример.

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

Select номер_детали, название, вес

from P.x, P.y

where x.вес = y.вес

and

x.rowid != y.rowid

Результат:	номер_детали	название	вес
	P2	Болт	17
	P3	Винт	17
	P1	Болт	12
	P5	Кулачок	12

Подготовьте запрос и проверьте полученный результат.

VI. Построение внешнего соединения.

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

Существует четыре основных типа внешних соединений:

простое внешнее соединение;

простое внешнее соединение с третьей таблицей;

внешнее соединение простого соединения с третьей таблицей;

внешнее соединение внешнего соединения с третьей таблицей.

1. Простое соединение

Пример.

Select S.номер_поставщика, S.фамилия, SP.количество

from S, SP

where S.номер_поставщика=SP.номер_поставщика

Результат:	Hомеp_поставщика	Фамилия	Количество
	S1	Смит	300
	S1	Смит	200
	S1	Смит	400
	S1	Смит	200
	S1	Смит	100
	S1	Смит	100
	S2	Джонс	300
	S2	Джонс	400
	S3	Блейк	200
	S4	Кларк	200
	S4	Кларк	300
	S4	Кларк	400

Подготовьте запрос и проверьте полученный результат.

2. Простое внешнее соединение двух таблиц.

Пример.

Select S.номер_поставщика, S.фамилия, SP.количество

from S, outer SP

where S.номер_поставщика=SP.номер_поставщика

Добавление ключевого слова outer перед именем таблицы SP превращает ее в подчиненную таблицу. Результатом этого внешнего соединения будет получение сведений обо всех поставщиках, независимо делали ли они поставки.

Результат:	Hомеp_поставщика	Фамилия	Количество
	S1	Смит	300
	S1	Смит	200
	S1	Смит	400
	S1	Смит	200
	S1	Смит	100
	S1	Смит	100
	S2	Джонс	300
	S2	Джонс	400
	S3	Блейк	200
	S4	Кларк	200
	S4	Кларк	300
	S4	Кларк	400
	S5	Адамс

Подготовьте запрос и проверьте полученный результат.

3. Внешнее соединение простого соединения с третьей таблицей.

Пример

select S.номер_поставщика, S.фамилия, SP.номер_детали,

P.название, P.цвет, P.вес

from S, outer(SP, P)

where S.номер_поставщика = P.номер_поставщика

and

SP.номер_детали =SP.номер_детали

and

цвет in ("Красный", "Зеленый")

Оператор select выполняет сначала простое соединение таблиц SP и P, а затем этот оператор выполняет внешнее соединение как комбинирование этой информации с данными из главной таблицы S.

Результат:

Hомеp_пост	Фамилия	Номер_дет	Название	Цвет	Вес
S1	Смит	P1	Гайка	Красный	12
S1	Смит	P2	Болт	Зеленый	17
S1	Смит	P4	Винт	Красный	14
S1	Смит	P6	Блюм	Красный	19
S2	Джонс	P1	Гайка	Красный	12
S2	Джонс	P2	Болт	Зеленый	17
S3	Блейк	P2	Болт	Зеленый	17
S4	Кларк	P2	Болт	Зеленый	17
S4	Кларк	P4	Винт	Красный	14
S5	Адамс

Подготовьте запрос и проверьте полученный результат.

4. Внешнее соединение внешнего соединения с третьей таблицей.

Пример

select S.номер_поставщика, S.фамилия, SP.номер_детали,

P.название, P.цвет, P.вес

from S, outer(SP, outer P)

where S.номер_поставщика = SP.номер_поставщика

and

SP.номер_детали = P.номер_детали

and

цвет in ("Красный", "Зеленый")

Оператор select сначала выполняет внешнее соединение таблиц SP и P, затем - внешнее соединение как комбинирование этой информации с данными из главной таблицы S.

Результат:

Hомеp_пост	Фамилия	Номер_дет	Название	Цвет	Вес
S1	Смит	P1	Гайка	Красный	12
S1	Смит	P2	Болт	Зеленый	17
S1	Смит	P3
S1	Смит	P4	Винт	Красный	14
S1	Смит	P5
S1	Смит	P6	Блюм	Красный	19
S2	Джонс	P1	Гайка	Красный	12
S2	Джонс	P2	Болт	Зеленый	17
S3	Блейк	P2	Болт	Зеленый	17
S4	Кларк	P2	Болт	Зеленый	17
S4	Кларк	P4	Винт	Красный	14
S4	Кларк	P5
S5	Адамс

Подготовьте запрос и проверьте полученный результат.

5. Внешнее соединение двух таблиц с третьей.

Пример

Select S.номер_поставщика, S.фамилия, s.город,

SP.количество, P.название, P.город

from S, outer SP, outer P

where S.номер_поставщика = SP.номер_поставщика

and

S.город = S.город

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

Результат:

Hомеp_пост	Фамилия	Город_пост	Кол-во	Название	Город_дет
S1	Смит	Лондон	300	Гайка	Лондон
S1	Смит	Лондон	300	Винт	Лондон
S1	Смит	Лондон	300	Блюм	Лондон
. . .	. . .	. . .	. . .	. . .	. . .
S2	Джонс	Париж	300	Болт	Париж
S2	Джонс	Париж	300	Кулачок	Париж
S2	Джонс	Париж	400	Болт	Париж
. . .	. . .	. . .	. . .	. . .	. . .
S4	Кларк	Лондон	400	Гайка	Лондон
S4	Кларк	Лондон	400	Винт	Лондон
S4	Кларк	Лондон	400	Блюм	Лондон
S5	Адамс

Всего выборка содержит 34 строки.

Подготовьте запрос и проверьте полученный результат.

VII. Подзапросы

Оператор select, вложенный в спецификатор where другого оператора select (или одного из операторов insert, delete, update), называется подзапросом. В состав каждого подзапроса должны входить спецификаторы select и from. Кроме того, каждый подзапрос должен быть заключен в круглые скобки, чтобы указать серверу баз данных на то, что эту операцию следует выполнить первой.

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

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

Подзапрос включается в спецификатор where оператора select с помощью следующих ключевых слов:

ALL

ANY

EXISTS

Некоррелированные подзапросы.

1. Фраза ALL.

Ключевое слово ALL, указываемое перед подзапросом используется для определения того, выполняется ли условие сравнения для каждого возвращаемого подзапросом значения. Если подзапрос не возвращает ни одного значения, то условие поиска считается выполненным.

Пример

Получить перечень поставщиков, рейтинг которых выше рейтинга любого лондонского поставщика.

Select x.номер_поставщика, x.фамилия, x.рейтинг

from S x

where x.рейтинг > all

(select y.рейтинг

from S y

where y.город='Лондон')

Сначала выполняется независимый внутренний подзапрос, его результатом является выборка (20, 20), затем - внешний запрос, приводящий к результату, записанному ниже.

Результат:	Hомеp_поставщика	Фамилия	Рейтинг
	S3	Блейк	30
	S5	Адамс	30

Подготовьте запрос и проверьте полученный результат.

2. Фраза ANY.

Ключевое слово ANY, указываемое перед запросом, используется для определения того, выполняется ли сравнение по крайней мере для одного значения, возвращаемого подзапросом. Если подзапрос не возвращает ни одного значения, то условие поиска считается не выполненным.

Пример

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

Select x.номер_поставщика, x.фамилия, x.рейтинг

from S x

where x.рейтинг > any

(select y.рейтинг

from S y

where y.город='Париж')

Сначала выполняется независимый внутренний подзапрос, его результатом является выборка (10, 30), затем - внешний запрос, приводящий к результату, записанному ниже.

Результат:	Hомеp_поставщика	Фамилия	Рейтинг
	S1	Смит	20
	S3	Блейк	30
	S4	Кларк	20
	S5	Адамс	30

Подготовьте запрос и проверьте полученный результат.

3. Фраза IN.

3.1. Простой подзапрос.

Пример

Выдать фамилии поставщиков, поставляющих деталь P2.

Select фамилия

from S

where номер_поставщика in

(Select номер_поставщика

from SP

where номер_детали ='P2')

Сначала выполняется внутренний подзапрос, его результатом является выборка (S1, S2, S3, S4), затем - внешний запрос, который после подстановки результатов внутреннего подзапроса имеет вид:

Select фамилия

from S

where номер_поставщика in ('S1', 'S2', 'S3', 'S4')

Результат:	Фамилия
	Смит
	Джонс
	Блейк
	Кларк

Подготовьте запрос и проверьте полученный результат.

3.2. Подзапрос с несколькими уровнями вложенности.

Пример

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

Select фамилия

from S

where номер_поставщика in

(Select номер_поставщика

from SP

where номер_детали in

(select номер_детали

from P

where цвет='красный'))

Сначала осуществляется самый внутренний подзапрос, дающий выборку (P1, P4, P6). После подстановки его результатов выполняется второй по вложенности подзапрос, дающий выборку (S1, S2, S4). Подстановка результатов второго выполненного подзапроса во внешний запрос приводит к окончательному результату.

Результат:	Фамилия
	Смит
	Джонс
	Кларк

Подготовьте запрос и проверьте полученный результат.

3.3. Использование одной и той же таблицы в подзапросе внешнем запросе.

Пример

Выдать номера поставщиков, поставляющих, по крайней мере, одну деталь, поставляемую поставщиком S2.

Select distinct номер_поставщика

from SP spx

where spx.номер_детали in

(Select spy.номер_детали

from SP spy

where spy.номер_поставщика='S2')

Сначала выполняется внутренний подзапрос, дающий выборку (P1, P2). Подстановка его результатов во внешний запрос приводит к окончательному результату.

Результат:	номер_поставщика
	S1
	S2
	S3
	S4

Подготовьте запрос и проверьте полученный результат.

3.4. Подзапрос с оператором сравнения отличным от IN.

Пример

Выдать номера поставщиков, находящихся в том же городе, что и поставщик S1.

Select номер_поставщика

from S

where город =

(Select город

from S

where номер_поставщика ='S1')

Сначала выполняется внутренний подзапрос, дающий единственное значение "Лондон". Подстановка его результатов во внешний запрос приводит к окончательному результату.

Результат:	номер_поставщика
	S1
	S4

Подготовьте запрос и проверьте полученный результат.

Коррелированный подзапросы

3.5. Простой коррелированный подзапрос.

Пример

Выдать фамилии поставщиков, поставляющих деталь P2.

Seleсt фамилия

from S

where 'P2' in

(Select номер_детали

from SP

where номер_поставщика= S.номер_поставщика)

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

выбирается первая строка из S (номер_поставщика='S1");

выполняется подзапрос:

(Select номер_детали

from sp

where номер_поставщика='S1')

результатом подзапроса является выборка (P1, P2, P3, P4, P5, P6);

завершается обработка запроса для первой строки из S, при выполнении которого проверяется условие

'P2' in ('P1', 'P2', 'P3', 'P4', 'P5', 'P6')

поскольку проверяемое условие - истина, результатом обработки запроса для первой строки из S является фамилия "Смит";

аналогично повторяется обработка для остальных строк таблицы S.

Результат:	Фамилия
	Смит
	Джонс
	Блейк
	Кларк

Подготовьте запрос и проверьте полученный результат.

3.6. Коррелированный подзапрос с использованием в коррелированном и внешнем запросе одной и той же таблицы.

Пример

Выдать номера деталей, поставляемых более чем одним поставщиком.

Select distinct spx.номер_детали

from SP spx

where spx.номер_детали in

(Select spy.номер_детали

from SP spy

where spy.номер_поставщика<>spx.номер_поставщика)

Результат:	Номер_детали
	P1
	P2
	P4
	P5

Подготовьте запрос и проверьте полученный результат.

4. Фраза EXISTS.

4.1. Квантор существования EXISTS.

В языке SQL предикат с квантором существования представляется выражением вида:

EXISTS (select * from...)

Данное выражение истинно тогда и только тогда, когда результат вычисления подзапроса, представленного с помощью select * from является непустым множеством, т.е. когда существует какая-либо запись в таблице, указанной во фразе from подзапроса, который удовлетворяет условию where этого подзапроса.

Пример

Выдать фамилии поставщиков, поставляющих деталь P2.

Select фамилия

from S

where exists

(Select *

from SP

where номер_поставщика = S.номер_поставщика

and номер_детали = 'P2')

Последовательность обработки запроса:

выбирается первая строка из S (номер_поставщика='S1');
поскольку условие

номер_поставщика = 'S1' и номер_детали = 'P2' - истина, результат обработки запроса для первой строки - фамилия Смит.

Результат:	Фамилия
	Смит
	Джонс
	Блейк
	Кларк

Подготовьте запрос и проверьте полученный результат.

4.2. Запрос, реализующий квантор общности.

Квантор общности FORALL в SQL не поддерживается, однако он может быть выражен через квантор существования при помощи тождества

FORALL x(p)=NOT(EXISTS x(NOT(p))).

Пример

Выдать фамилии поставщиков, которые поставляют все детали.

Эквивалентная формулировка задачи может звучать так:

Выдать фамилии поставщиков таких, что для всех деталей существует запись в таблице SP, указывающая, что данный поставщик поставляет эту деталь.

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

Select фамилия

from S

where not exists

(Select * from P

where not exists

(Select * from SP

where номер_поставщика=S.номер_поставщика

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

Результат:	Фамилия
	Смит

Подготовьте запрос и проверьте полученный результат.

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

Пример

Выдать номера поставщиков со значением поля рейтинг меньшим, чем максимальный рейтинг в таблице S.

Select номер_поставщика from S

where рейтинг <

(Select max(рейтинг) from S)

Результат:	номер_поставщика
	S1
	S2
	S4

Подготовьте запрос и проверьте полученный результат.

Пример

Выдать номер_поставщика, рейтинг и город всех поставщиков, у которых рейтинг больше либо равен среднему для их конкретного города (использование функций в коррелированном подзапросе).

Select номер_поставщика, рейтинг, город

from S sx

where рейтинг >=

(Select avg(рейтинг)

from S sy

where sy.город=sx.город)

Результат:	номер_поставщика	Рейтинг	Город
	S1	20	Лондон
	S3	30	Париж
	S4	20	Лондон
	S5	30	Атенс

Подготовьте запрос и проверьте полученный результат.

VIII. ОБЬЕДЕНЕНИЕ

Объединяемые оператором UNION таблицы должны быть совместны по объединению:

иметь одинаковое число столбцов;
соответствующие столбцы должны иметь одинаковые типы.

Любое число предложений select может быть соединено оператором union. Избыточные дубликаты исключаются из результата объединения.

Пример

Выдать номера деталей, которые имеют вес более 16 фунтов, либо поставляются поставщиком S2.

Select номер_детали

from P

where вес>16

union

Select номер_детали

from SP

where номер_поставщика='S2'

Результат:	Номер_детали
	P1
	P2
	P3
	P6

Подготовьте запрос и проверьте полученный результат.

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

Общая форма оператора удаления:

delete from таблица [where предикат]

1. Удаление единственной записи.

Пример

Удалить сведения о поставщике S1.

delete from S

where номер_поставщика='S1'

Результат: таблица S с отсутствующей строкой о поставщике S1.

Подготовьте запрос и проверьте полученный результат.

2. Удаление множества записей.

Пример

Удалить сведения обо всех поставщиках из Лондона.

delete from S

where город='Лондон'

Результат: таблица S с отсутствующими строками о поставщиках из Лондона.

Подготовьте запрос и проверьте полученный результат.

3. Удаление с подзапросом.

Пример

Удалить все поставки для поставщиков из Лондона.

delete from SP

where 'Лондон'=

(Select город from S

where S.номер_поставщика=SP.номер_поставщика)

Результат: таблица SP с отсутствующими строками о поставках для поставщиков из Лондона.

Подготовьте запрос и проверьте полученный результат.

4. Удаление всех строк таблицы.

Пример

delete from S

Подготовьте запрос и проверьте полученный результат.

X. Оператора манипулирования данными. Вставка данных

Общая форма оператора вставки.

Insert into таблица [(поле [,поле]...)]

values ( константа [,константа]...) или подзапрос

1. Вставка единственной записи.

Пример

Вставить новую поставку с номером поставщика S2, номером детали P4 и количеством 1000 на дату "30 ноября 1995 г.".

Insert into SP values ('S2', 'P4', '11/30/95', 1000)

Результат: таблица SP с добавленной строкой о поставке поставщиком S2 детали P4.

Подготовьте запрос и проверьте полученный результат.

2. Вставка множества записей.

Пример

Восстановить таблицу S.

Insert into S values ('S1', 'Смит', 20, 'Лондон');

Insert into S values ('S2', 'Джонс', 10, 'Париж');

Insert into S values ('S3', 'Блейк', 30, 'Париж');

Insert into S values ('S4', 'Кларк', 20, 'Лондон');

Insert into S values ('S5', 'Адамс', 30, 'Атенс')

Результат: восстановленная таблица S.

Подготовьте запрос и проверьте полученный результат.

3. Перечисление имен столбцов.

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

Пример

Вставить строку о новом поставщике, занеся лишь номер поставщика, фамилию и город.

Insert into S(номер_поставщика, фамилия, город)

values ('S6', 'Боб', 'Нью-Йорк')

Результат: добавленная строка в таблице S.

Подготовьте запрос и проверьте полученный результат.

4. Вставка множества записей как результата подзапроса.

Пример

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

Create table temp

(номер_детали char(6),

объем поставки smallint);

Insert into temp (номер_детали,обьем_поставки)

Select номер_детали, sum(количество)

from SP
group by номер_детали

Результат: Сформированная таблица temp, данные в которую занесены как результат указанного оператора.

Подготовьте запрос и проверьте полученный результат.

5. Построение внешнего соединения с использованием оператора Insert.

Пример

Для каждого поставщика получить его номер, фамилию, рейтинг и город вместе с номерами всех поставляемых им деталей. Если поставщик не поставляет никаких деталей, поставить в поле номер_детали значение NN.

Create table outside_t

(номер_поставщика char(5),

фамилия char(20),

рейтинг smallint,

город char(15),

номер_детали char(6));

Insert into outside_t

Select S.*, SP.номер_детали

from S, SP

where S.номер_поставщика=SP.номер_поставщика;

Insert into outside_t

Select S.*, 'NN'

from S

where not exists

(Select *

from SP

where SP.номер_поставщика =

S.номер_поставщика)

Результат: Сформированная таблица outside_t с данными, представляющими собой результат внешнего соединения двух таблиц.

Подготовьте запрос и проверьте полученный результат.

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

Общая форма оператора обновления

Update таблица

set поле=выражение [,поле=выражение]...[where предикат]

1. Обновление единственной записи.

Пример

Изменить цвет детали P2 на желтый, увеличить ее вес на 5 и установить значение города "неопределен".

Update P set цвет='желтый',

вес=вес+5,

город=NULL

where номер_детали='P2'

Результат: Таблица S c внесенными изменениями.

Подготовьте запрос и проверьте полученный результат.

2. Обновление множества записей.

Пример

Удвоить рейтинг всех поставщиков в Лондоне.

Update S set рейтинг=2*рейтинг

where город='Лондон'

Результат: Таблица S с увеличенным рейтингом для поставщиков из Лондона.

Подготовьте запрос и проверьте полученный результат.

3. Обновление с подзапросом.

Пример

Установить объем поставок, равный нулю для поставщиков из Лондона.

update SP set количество=0

where 'Лондон'=

(Select город

from S

where S.номер_поставщика=SP.номер_поставщика)

Результат: Таблица SP с внесенными изменениями.

Подготовьте запрос и проверьте полученный результат.

Список литературы

1.The Informix Guide to SQL. Tutorial. - 1994. - Part No. 000-7028.

2. The Informix Guide to SQL. Reference. - 1994. - Part No. 000-7029.

3. The Informix Guide to SQL. Syntax. - 1994. - Part No. 000-7597.

4. Распределенные СУБД. Informix. Ч.1. Основы работы с базами данных. - Методические указания для выполнения лабораторных работ / Стасышин В.М. - Новосибирск: НГТУ, 1995.