Извлечение информации из одной таблицы.

Основным оператором языка DML является оператор select — извлечение данных из таблиц. Результатом выполнения оператора select всегда является таблица. В простейшем виде оператор имеет такой формат:

select *

from <имя таблицы>

Например,

select * (1)

from student

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

Пример 7 При рассмотрении примеров мы будем считать что в таблице student содержатся следующие сведения:

1_ пате	f_ name	m_ пате	потеr	gr_ nomer
Иванов	Иван	Иванович	010001	11
Иванов	Александр	Петрович	010002	11
Федоров	Николай	Андреевич	011005	12
Сидоров	Павел	Алексеевич	011003	12
Сорокин	Михаил	Анатольевич	011004	12
Сорокин	Николай	Сергеевич	011101	13

В результате выполнения оператора (1) эта таблица и получится.

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

select <список столбцов>

from <имя таблицы>

Пример 8 Например, если мы хотим извлечь только номера и фамилии студентов, то следует написать:

select nomer, l_nate

from student

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

потеr	1_ пате
011004	Сорокин
010001	Иванов
011005	Федоров
011001	Сорокин
010002	Иванов
011003	Сидоров

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

Пример 9 Например, оператор

select nomer, l_name as last_name

from student

извлекает из таблицы student me же данные, но столбец фамилий будет иметь имя last_name:

nomer	last_name name
011004	Сорокин
010001	Иванов
011005	Федоров
011001	Сорокин
010002	Иванов
011003	Сидоров

Кроме имен столбцов в списке после select можно писать произвольные выражения.

Пример 10 Например,

select 'студент ' + f_name + l_name as full_name from student

выдаст таблицу

full_ name

студент Иван Иванов

студент Александр Иванов

студент Павел Сидоров

студент Михаил Сорокин

студент Николай Федоров

студент Николай Сорокин

Столбцы выражений не имеют имен, поэтому обычно имена им требуется присваивать при помощи as.

Задача 8 (Университет) Напишите SQL-запрос, который извлекает полное имя преподавателя, с указанием должности и степени (см. задачу 3), например,

доцент кандидат физ.мат. наук Дудаков Сергей Михайлович

профессор доктор физ.мат. наук Климок Виктор Иванович

Как мы уже говорили, порядок строк в таблицах не определён. Следовательно, приведенные выше операторы select выдадут нам информацию в случайном порядке, что и продемонстрировано в предыдущем примере (порядок в каждом случае будет определяться внутренними особенностями реализации). Чтобы в точности указать порядок следования извлеченных строк используется инструкция order by:

Select...

From...

order by <способ сортировки>

Способ сортировки представляет собой список полей (или выражений), разделенных запятыми после каждого из которых может стоять одно из слов: ASC - сортировка по данному полю осуществляется по возрастанию, DESC - по убыванию. Если после имени поля не стоит ни одно из этих слов, то подразумевается сортировка по возрастанию. Сортировка строк осуществляется сначала по первому полю из списка, если две строки имеют равные значения первого поля, то - по второму и т.д.

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

select nomer,

1 _ пате from student

order by nomer

тогда получим,

nomer	l_ name
010001	Иванов
010002	Иванов
011003	Сидоров
011004	Сорокин
011005	Федоров
011101	Сорокин

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

select l _ name, f _ name, m _ name

from student

order by l _ name desc, f _ name desc, m _ name desc

Задача 9 (Банк) Выведите список номеров счетов, суммы на них и даты открытия

1. в порядке возрастания номеров счетов;

2. в порядке убывания сумм наших, а в случае одинаковых сумм - в порядке возрастания номеров.

Задача 10 (CD) Выведите список компакт-дисков (см. задачу 5) в порядке возрастания фирмы производителя, а в случае одинаковой фирмы в порядке возрастания названий.

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

Пример 12 Например, в результате выполнения оператора

select l name

from student

появятся несколько строк с фамилиями 'Иванов' и 'Сорокин'

l_ пате

Иванов

Сидоров

Федоров

Сорокин

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

Для исключения повторов в этом и любом другом случае используется слово distinct, которое ставится после select:

select distinct...

from...

[order by...]

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

Пример 13 В нашем случае, чтобы, просто вывести список различных фамилий, упорядочив его по возрастанию, следует, написать:

select distinct l _ name

from student

order by l _ name

и получим

l_ name

Иванов

Сидоров

Cорокин

Федоров

Задача 11 (Университет) Выведите список групп, без повторений, упорядочив его по возрастанию номера группы.

Задача 12 (Супермаркет) Выведите список фирм, товары которых имеются в продаже, без повторений.

Задача 13 (CD) Выведите список названий фирм-производителей компакт-дисков без повторений.

Задача 14 (Библиотека) Выведите список авторов, книги которых имеются в библиотеке, без повторений.

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

select...

from...

where <условие>

[order by... ]

Условие может принимать одно из трех значений: TRUE (истина), FALSE (ложь) и UNKNOWN (неизвестно). В результате выполнения такого оператора выбираются в точности те строки, для которых условие истинно, строки для которых условие ложно или неизвестно отбрасываются.

Условие в простейшем случае представляет собой сравнения полей с константами и другими полями. Простейшие условия можно соединять логическими операциями and, or и not по правилам трехзначной логики:

and	T	U	F	or	T	U	F	not
T	T	U	F	T	T	T	T	T	F
U	U	U	F	U	T	U	U	U	U
F	F	F	F	F	T	U	F	F	T

Для некоторых условий (like, in, between) можно ставить not не перед всем условием, а перед одним из указанным слов, что более соответствует естественному языку. Например, вместо

not (a like b)

можно писать

a not like b

Точно так же вместо

not (a in (...))

можно написать

a not in (...)

В качестве сравнений используются равенство (=), неравенство (<>), отношения порядка (<, >, <=, >=). Если одно из сравниваемых значений есть NULL, то результат сравнения — UNKNOWN (это же относится и к любым другим условиям). В противном случае результат всегда является истинным или ложным.

Пример 14 Например, если мы хотим извлечь информация о всех студентах, фамилии которых начинаются на букву 'С’ нужно написать:

select *

from student

where l_name >= 'С and l_name < 'T'

order by nomer

Получится

I name	f name	т пате	по тег	gr nomer
Сидоров	Павел	Алексеевич	011003	12
Сорокин	Михаил	Анатольевич	011004	12
Сорокин	Николай	Сергеевич	011101	13

Пример 15 Чтобы получить список студентов 12 группы, следует выполнить:

select *

from student

where gr _ nomer = 12

Федоров	Николай	Андреевич,	011005	12
Сидоров	Павел	Алексеевич	011003	12
Сорокин	Михаил	Анатольевич	011004	12

Пример 16 Если требует список студентов с номерами от, '011000' до '011099', то соответствующий оператор:

select *

from student

where nomer >= '011000' and nomer <= '011099'

order by nomer

Задача 15 (Супермаркет) Выберите список товаров, цена которых превосходит 100.

Задача 16 (Супермаркет) Выберите список товаров, производимых фирмой «Рога и копыта».

Задача 17 (Банк) Выведите список счетов, которые были открыты ранее 2003 года и на которых при этом содержится не менее 1000000.

Задача 18 (Банк) Выведите список счетов, которые были открыты, в 2002 году.

Задача 19 (Университет) Выберите преподавателей, которые занимают должности доцентов или профессоров.

Задача 20 (CD) Выберите без повторений исполнителей, который исполняли партию Фигаро, упорядочите список по возрастанию.

Последнее условие принадлежности значения поля некоторому отрезку значений (в нашем случае это отрезок от '011000' до '011099') можно написать с помощью сравнения between... and:

|<выражение> between <выражение1> and <выраженже2>

Пример 17

select *

from student

where nomer between '011000' and '011099' order

by nomer

Данный оператор делает то же самое, что и в примере 16.

Задача 21 (Банк) С помощью between выберите номера счетов, на которых находится сумма от 10000 до 100000.

Для проверки на равенство одному из нескольких значений используется in:

|<выражение> in (<список выражений>)

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

Пример 18 Например, если нас интересуют студенты только 11 и 13 групп, то их можно выбрать так:

select *

from student

where gr_nomer in (11, 13)

order by nomer

Задача 22 (CD) Выберите без повторений всех исполнителей, которые пели хотя бы одну из партий: Фигаро, Риголетто, Альфио, Скар-пиа, Дои Жуан.

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

Для строк предусмотрена проверка соответствия шаблону - like:

|<строка> like <шаблон>

Шаблон тоже является строкой, в которой символы подчеркивания ('_'), процента ('%') и квадратные скобки ('[', ']') интерпретируются особым образом. Строка считается соответствующей шаблону, если ее можно получить из шаблона с помощью следующих действий, выполненных одновременно:

• каждый символ ' _' заменить каким-либо (в точности одним символом),

• каждый символ '%' заменить какой-либо (возможно, пустой) строкой,

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

Пример 19 Например, чтобы записать условие, что фамилия начинается с буквы 'С, можно использовать такой синтаксис:

l_name like 'С%,'

Если требуется извлечь информацию о студентах, фамилии которых начинаются на буквы 'С’, 'Т' или 'И', можно выполнить следующий оператор:

select *

from student

where l_name like >[СТИ]%’

Чтобы получить информацию о студентах, у которых третья цифра номера — единица:

select *
from student
where nomer like ’ –1%

Задача 24 (Супермаркет) Выведите список всех товаров, название которых начинается на «Со».

Задача 25 (Супермаркет) Выведите список всех товаров, название которых содержит в себе слово «молочный», а цена находится в диапазоне от, 20 до 50.

Задача 26 (Библиотека) Выберите всех читателей, которых зовут «Николай», и электронный адрес которых заканчивается на «@tversu.ru».

Задача 27 (Банк) Выберите счета, в номере которых присутствует хотя бы одна из цифр: 3, 5, или 9.

Задача 28 (CD) Выберите список всех треков, в названии которых присутствуют слова «amore» и «core» именно в таком порядке и длительность которых превышает 5 минут.

3.2 Группировка данных и агрегатные функции.

Теперь рассмотрим возможности SQL по обработке табличных данных. Один из основных способов анализа данных связан с использованием инструкции group by в операторе select:

select...

from...

[where... ]

group by <список группировке>

[order by...]

Список группировки является списком полей, разделенных запятыми. Семантика оператора group by такая: все строки объединяются в несколько групп таким образом, что каждую группу образуют строки, у которых значения всех столбцов их списка группировки совпадают. Затем из каждой группы формируется одна строка.

Например, если в операторе select присутствует инструкция вида

select l_name...

group by l_name...

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

Из описанной семантики group by следует, в списке выбираемых полей после select нельзя использовать поля, не входящие в список группировки. В самом деле, если написать

select l_name, f_name

from student

group by l_name

то непонятно, чему должно быть равно значение второго столбца. В таблице есть два Ивановых, и значение f _name может быть равно и 'Иван', и 'Александр'.

Для того, чтобы из каждой группы выбрать одно значение для какого либо поля или сгенерировать новое значение, используются агрегатные функции: max — для выбора максимального значения, min — минимального, avg — среднего арифметического в группе, sum - для нахождения суммы в группе. Последние две функции могут использоваться только для столбцов числового типа. Еще одна агрегатная функция - count (*) - предназначена для подсчета количества строк в группе.

Рассмотрим несколько примеров.

Пример 20

select gr_nomer, count(*) as gr_count

from student

group by gr_nomer

gr nomer	fr count
11	2
12	3
13	1

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

Пример 21

select gr_nomer, min(nomer) as min_nomer,

max(nomer) as max_nomer

from student

group by gr _ nomer

gr nomer	min nomer	max nomer
11	010001	010002
12	011003	011005
13	011101	011101

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

Заметим, что столбцы, формируемые с помощью агрегатных функций, не имеют имени, поэтому обычно им требуется явно присваивать имя при помощи as.

Пример 22

select dis, avg(res) as res

from ball

group by dis

Будем считать, что таблица ball заполнена следующим образом:

stud nomer	dis	dat	form	res
010001	Алгебра	04.01.2004	Зачет	100
010001	Анализ	08.01.2004	Экзамен	80
010001	Информатика	Ц.01.2004	Экзамен	75
010002	Алгебра	04-01.2004	Зачет	50
010002	Анализ	08.01.20 04	Экзамен	10
010002	Инфюрматика	Ц.01.2004	Экзамен	80
011003	Алгебра	09.01.2004	Экзамен	30
011003	Анализ	04.01.2004	Экзамен	10
011003	Инфюрматика	i4.Ol.2OO4	Зачет	90
01Ю04	Алгебра	09.01.2004	Экзамен	60
01Ю04	Анализ	04-01.2004	Экзамен	60
01Ю04	Информатика	Ц.01.2004	Зачет.	10
011005	Алгебра	09.01.2004	Экзамен	80
011005	Анализ	04.01.2004	Экзамен	10
011005	Информатика	14-01.2004	Зачет	80

Тогда приведенный оператор выдаст

dis	res
Алгебра	64
Анализ	10
Информатика	19

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

select dis, sum(res) / count(*) as res

from ball

group by dis

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

Задача 29 (Супермаркет) Найти какое количество различных товаров каждого производителя имеется в продаже.

Задача 30 (Супермаркет) Найти, сколько различных товаров каждого вида имеется в продаже.

Задача 31 (Супермаркет) Найти среднюю цену для каждого вида товаров.

Задача 32 (Супермаркет) Найти общее количество единиц товара каждого вида из имеющихся в магазине.

Задача 33 (Супермаркет) Найти наименьшую и наибольшую цены для каждого вида товара.

Инструкции group by и where можно использовать вместе. В этом случае сначала осуществляется отбор строк при помощи where, а затем - группировка.

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

select dis, avg(res) as res

from ball

where form='Экзамен’

group by dis

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

dis	res
Алгебра	56,6666
Анализ	70
Информатика	77,5

Если бы по каким-то дисциплинам экзамены вообще не проводились, то они вообще не попали бы в результирующий набор. На этом примере видно, что в инструкции where можно использовать имена тех столбцов, по которым группировка не проводиться (в нашем случае — form). Это как раз и связано с тем, что фильтрация строк в where осуществляется до группировки.

Задача 34 (Супермаркет) Найти среднюю цепу товаров фирмы «Рога и копыта» из имеющихся в продаже.

Задача 35 (Супермаркет) Найти количество каждого товара, имеющего цену от 1000 до 10000.

Агрегатные функции можно использовать, даже если инструкции group by в операторе select нет. В этом случае считается, что все строки образуют одну группу.

Пример 24 Например, оператор

select max(nomer) as max _ nomer

from student

вернет таблицу

тах_ потеr

011101

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

Пример 25 Точно так же оператор

select avg(res) as res

from ball where form = 'Экзамен'

подсчитает среднее количество баллов набираемых студентами в ходе сдачи экзаменов:

res

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

Задача 36 (Супермаркет) Найти среднюю цену товара в магазине.

Задача 37 (Библиотека) Найти общее количество книг в библиотеке.

Задача 38 (Супермаркет) Найти наименьшую и наибольшую цены товара в магазине.

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

select...

from...

[where...]

group by...

having <условие>

[order by... ]

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

Пример 26 Например, чтобы вывести средний балл по дисциплинам,, для которых максимальный балл, полученный когда-либо, равен 100, следует выполнить

select dis, avg(res)

from ball

group by dis

having max(res) = 100

Результат:

dis
Алгебра	64

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

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

select gr_nomer, count(*) as cnt

from student

group by gr_nomer

order by count(*) DESC

gr nomer	cnt
12	3
11	2
13	1

Задача 39 (Супермаркет) Найти среднюю цену каждого из видов товаров, общее количество единиц которого в магазине превосходит 1000. Результат отсортировать по количеству единиц, т,овара по убыванию.

Задача 40 (Супермаркет) Найти максимальную и минимальную

цену каждого из видов товаров, средняя цена которых лежит в диапазоне от 100 до 200. Результат отсортировать по количеству единиц товара по возрастанию.

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

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

Соединение таблиц

До сих пор мы использовали оператор select для извлечения данных из одной таблицы.

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

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

Самый простой способ внутреннего соединения декартово произ
ведение исходных таблиц. Декартово произведение записывается двумя
способами: соединяя имена таблиц словами cross join или запятой:

[from <имя таблицы> cross join <имя таблицы>

или

|from <имя таблицы>, <имя таблицы>

Пример 28 Например,

select *

from student cross join ball

или

select *

from student, ball

Оба оператора выдадут один и тот же результат.

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

select...

from <имя таблицы>, <имя таблицы>

where <условие соединения>

Пример 29

select *

from student, ball

where nomer = stud_nomer

Второй способ — использовать соединение при помощи inner join... on:

select...

from <имя таблицы> inner join <имя таблицы>

on <условие соединения>

Пример 30 Для нашего примера:

select *
from student inner join ball (2)

on nomer = stud_nomer

Оба этих оператора дают один и тот оке результат, показанный на рис. 1.

Как видим, таблица получилась достаточно громоздкой. С одной стороны она содержит много столбцов, с другой — эти столбцы повторяются. В самом деле, столбцы nomer и stud_nomer по условию соединения содержат одно и то же. Поэтому если при соединении получается достаточно много столбцов, разумнее в операторе select явно перечислить лишь те, которые действительно нужны.

l _name	f_ name	m_name	nomer	gr_nomer	stud_nomer	dis	dat	form	res
Иванов	Иван	Иванович				Алгебра	04.01.2004	Зачет
Иванов	Иван	Иванович				Анализ	08.01.2004	Экзамен
Иванов	Иван	Иванович				Информатика	14.01.2004	Экзамен
Иванов	Александр	Петрович				Алгебра	04.01.2004	Зачет
Иванов	Александр	Петрович				Анализ	08.01.2004	Экзамен
Иванов	Александр	Петрович				Информатика	14.01.2004	Экзамен	SO
Федоров	Николай	Андреевич				Алгебра	09.01.2004	Экзамен
Федоров	Николай	Андреевич				Анализ	04.01.2004	Экзамен
Федоров	Николай	Андреевич				Информатика	14.01.2004	Зачет
Сидоров	Павел	Алексеевич				Алгебра	09.01.2004	Экзамен
Сидоров	Павел	Алексеевич				Анализ	04.01.2004	Экзамен
Сидоров	Павел	Алексеевич				Информатика	14.01.2004	Зачет
Сорокин	Михаил	Анатольевич				Алгебра	09.01.2004	Экзамен
Сорокин	Михаил	Анатольевич				Анализ	04.01.2004	Экзамен
Сорокин	Михаил	Анатольевич				Информатика	14.01.2004	Зачет

Рис.1 Результат внутреннего объединения

Пример 31 Если, например, нас интересует для каждого студента лишь балл по каждой дисциплине, то для выборки можно использовать такой запрос:

select student.*, dis, res

from student inner join ball

on nomer = stud_nomer

Результат его выполнения:

l_ name	f_name	m-пате	потer	gr_ nomer	dis	res
Иванов	Иван	Иванович	010001	11	Алгебра	100
Иванов	Иван	Иванович	010001	11	Анализ	80
Иванов	Иван	Иванович	010001	11	Информатика	75
Иванов	А лекеандр	Петрович	010002	11	Алгебра	50
Иванов	Александр	Петрович	010002	11	Анализ	70
Иванов	Александр	Петрович	010002	11	Информатика	80
Федоров	Николай	Андреевич	011005	12	Алгебра	80
Федоров	Николай	Андреевич	011005	12	Анализ	70
Федоров	Николай	Андреевич	011005	12	Информатика	80
Сидоров	Павел	Алексеевич	011003	12	Алгебра	30
Сидоров	Павел	Алексеевич	011003	12	Анализ	70
Сидоров	Павел	Алексеевич	011003	12	Информатика	90
Сорокин	Михаил	Анатольевич	0U004	12	Алгебра	60
Сорокин	Михаил	Анатольевич	0П0О4	12	Анализ	60
Сорокин	Михаил	Анатольевич	011004	12	Информатика	70

Запись

|<имя таблицы>. *

означает выбор всех столбцов из указанной таблицы, в нашем случае — из таблицы student.

Задача 43 (Библиотека) Постройте оператор, соединяющий таблицу книг и таблицу авторов, и выводягций название книги и имена авторов.

Задача 44 (CD) Постройте оператор, соединяющий таблицу компакт-дисков и таблицу треков, и выводящий название альбома, номер и название трека.

Таблицу можно соединить саму с собой.

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

select s1.nomer as first, s2. nomer as second

from student s1 inner join student s2

on s1.gr_nomer = s2.gr_nomer

Здесь к каждой строке таблицы student последовательно будут присоединяться строки той же таблицы, у которых значение поля gr_nomer такое же. Результат:

first	second
010002	010001
010001	010001
010002	010002
010001	010002
011004	011005
011003	011005
011005	011005
011004	011003
011003	011003
011005	011003
011004	011004
011003	011004
011101	011101
011005	011004

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

|<имя таблицы> <новое имя>

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

|<имя таблицы>. <имя поля>

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

Пример 33 Если бы в таблице ball мы бы назвали первый столбец потеr вместо stud_nomer, то оператор (2) потребовалось бы переписать следующим образом:

select *

from student inner join ball

on student.nomer = ball.nomer

Задача 45 (CD) Соедините таблицу исполнителей саму с собой так, чтобы получить список пар, которые имеют записи в одном и том же альбоме.

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

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

select s1. nomer as first, s2. nomer as second

from student s1 inner join student s2

on s1. gr_nomer = s2. gr_nomer

where s1.nomer < s2.nomer

Получим:

first	second
010001	010002
011003	011004
011003	011005
011004	011005

Задача 46 (CD) Исправьте таким же образом запрос из задачи 45.

Задача 47 (Библиотека) Постройте запрос, выводящий для каждой книги название, первого (по алфавиту) автора и общее количество авторов книги.

Задача 48 (Библиотека) Постройте запрос, выводящий книги, имеющие в точности одного автора.

Задача 49 (CD) Построить запрос, выводящий для каждого компакт-диска, название и общее количество треков.

Задача 50 (Университет) Построить запрос, выводящий для каждого студента количество его оценок меньших 50.

Чтобы соединить больше чем две таблицы, их нужно соединять последовательно:

<имя таблицы> inner join <имя таблицы>

on <условие>

inner join <имя таблицы>

on <условие>...

Пример 35 Предположим, что таблица professor заполнена следующим образом:

dis name	gr	prof
Анализ	11	Климок
Алгебра	11	Колдунов
Информатика	11	Дудаков
Анализ	12	Михно
Алгебра	12	Колдунов
Информатика	12	Волушкова
Практикум на ЭВМ	11	Дудаков
Практикум на ЭВМ	12	Сорокин

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

select nomer, prof

from student inner join ball (3)

on nomer = stud_nomer

inner join professor

on dis = dis_name and gr_nomer gr

nomer	prof
010001	Колдунов
010001	Климок
010001	Дудаков
010002	Колдунов
010002	Климок
010002	Дудаков
011005	Колдунов
011005	Михпо
011005	Волугикова
011003	Колдунов
011003	Михно
011003	Волушкова
011004	Колдунов
011004	Михно
011004	Волугикова

Того же можно добиться, записав:

select nomer, prof

from student, ball, professor (4)

where nomer = stud_nomer and

dis = dis_name and gr_nomer = gr

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

select nomer, prof

from student inner join

ball inner join professor on dis = dis_name

on nomer = stud_nomer and gr_nomer = gr

В этом случае сначала соединяются таблицы, ball и professor, а затем к ним присоединяется таблица student. В запросе (3) происходит, наоборот: сначала соединяются таблицы student и ball, а затем к результату присоединяется professor. Для запроса (4) последовательность соединения выбирает исполнитель запроса (планировщик).

Задача 51 (Банк) Построить запрос, соединяющий таблицы клиентов и счетов.

Задача 52 (Банк) Построить запрос, определяющий количество владельцев каждого счета.

Задача 53 (Банк) Построить запрос, определяющий для каждого клиента коли'чество счетов, владельцем или совладельцем которых он является.

Задача 54 (Библиотека) Построить запрос, определяющий для каждой книги, сколько раз ее брали.

Задача 55 (Библиотека) Построить запрос, определяющий для каждого читателя, сколько книг он брал за все время.

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

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

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

Внешнее соединение предназначено для того, что сохранить все строки одной из таблиц или обеих сразу. Синтаксис конструкций: левое внешнее соединение —

<имя таблицы> left outer join <имя таблицы>

on <условие>

правое внешнее соединение —

<имя таблицы> right outer join <имя таблицы>

on <условие>

полное внешнее соединение —

<имя таблицы> full outer join <имя таблицы>

on <условие>

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

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

Пример 36 Например, можно найти для преподавателей средний балл, который получают у них студенты:

select prof, avg(res)

from professor left outer join

(ball inner join student

on nomer = stud_nomer)

on dis_name=dis and gr_nomer = gr

group by prof

В этом примере с помощью левого внешнего соединения соединяются две таблицы: первая — professor, а вторая получена внутренним соединением, таблиц ball и student. Результат:

prof
Волушкова	80
Дудаков	11
Климок	15
Колдунов	64
Сорокин	NULL
Михпо	66

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

Задача 57 (Банк) Для каждого счета определить, сколько операций с ним было проделано после его открытия 01.01.2004-01.02.2004-

Задача 58 (Супермаркет) Для каждого наименования товара подсчитать, скольким покупателям он был продан (если товар не продавался, то напротив него должен стоять ноль) в период 01.01.2004-01.02.2004-

Для проверки, имеет ли некоторое выражение значение NULL, используется предикат

|is null

Пример 37 Например, чтобы найти все предметы, оценки за которые еще не внесены в базу данных:

select dis_name, gr, prof

from professor left outer join

(ball inner join student

on nomer = stud_nomer)

on dis_name=dis and gr_nomer = gr

where res is null

В результате выполнения этого оператора в столбце res окажется значения NULL в тех строках, для которых отсутствуют строки в таблице ball.

dis name	gr	prof
Практикум на ЭВМ	11	Дудаков
Практикум на ЭВМ	12	Сорокин

Задача 59 (Университет) Найти преподавателей, которые за период 01.01.2000 01.01.2005 не написали ни одной статьи.

Задача 60 (Библиотека) Найти ключевые слова, которые не встречаются в книгах, выпущенных ранее 2000 года.

Задача 61 (Библиотека) Найти авторов, которые не написали ни одной книги, в названии которых есть хотя, бы одно из слов «математика», «математический».

Левое внешнее соединение не ассоциативно ни с самим собой, ни с другими видами соединений.

Пример 38 Рассмотрим, например, следующие три таблицы:

Таблица а	Таблица b	Таблица с
a1	b1	b2	c1	с2
1	1	11	1	12

При выполнении соединения

a left outer join b on a1 =b1

left outer join с on a1 = c1 and b2 = c2

получим таблицу вида

a1	b1	b2	c1	c2
1	1	11	NULL	NULL

Если же порядок соединения поменять:

a left outer join

Ъ left outer join с on Ъ2 = c2 on al = cl and al = Ы

то получается другой результат:

al	Ы	Ъ2	cl	c2
1	NULL	NULL	NULL	NULL

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

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

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

Самое мощное средство языка SQL — вложенные запросы (или подзапросы). Самой общей формой использования подзапросов является использование его в качестве условия в инструкциях where или having:

(exists(<SQL-3anpoc>)

Это условие будет истинным, если таблица, сгенерированная в результате выполнения SQL-запроса в скобках, содержит хотя бы одну строку, в противном случае условие ложно. Частные случаи использования подзапросов — сравнения:

<выражение> <знак сравнения> any (<SQL-3anpoc>)

<выражение> <знак сравнения> all

(<SQL-3anpoc>)

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

Пример 39 Найдем студента, который получил самый высокий балл:

select l_name, gr_nomer, nomer, dis, res from student inner join ball

on nomer = stud_nomer where res = all(select max(res) from ball)

l_ name	gr nomer	nomer	dis	res
Иванов	11	010001	Алгебра	100

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

Пример 40 Изменим запрос, чтобы найти студентов, получивших самый высокий балл, по каждому из предметов:

select l_name, gr_nomer, nomer, dis, res from student inner join ball on nomer = stud_nomer where res = all(select max(res) from ball b where ball.dis = b.dis)

I name	gr nomer	nomer	dis	res
Сидоров	12	011003	Информатика	90
Иванов	11	010001	Анализ	80
Иванов	11	010001	Алгебра	100

Здесь при выполнении подзапроса используется поле dis из таблицы ball основного запроса. Такие подзапросы называют, связанными. В связи с использованием таблицы ball и во внешнем запросе, и в подзапросе возникает, необходимость переименования таблицы вложенного запроса, чтобы отличать ее от таблицы основного запроса, и необходимость явного указания таблиц для поля dis.

Пример 41 Чтобы вывести все оценки по каждому предмету, кроме самых низких:

select l_name, gr_nomer, nomer, dis, res from student inner join ball

on nomer = stud_nomer where res > any(select min(res) from ball b where ball.dis = b.dis)

I name	gr nomer	nomer	dis	res
Иванов	11	010002	Алгебра	50
Сорокин	12	011004	Алгебра	60
Федоров	12	011005	Алгебра	80
Иванов	11	010001	Алгебра	100
Иванов	11	010002	Анализ	70
Сидоров	12	011003	Анализ	70
Федоров	12	011005	Анализ	70
Иванов	11	010001	Анализ	80
Иванов	11	010001	Информатика	75
Иванов	11	010002	Информатика	80
Федоров	12	011005	Информатика	80
Сидоров	12	011003	Информатика	90

Пример 42 Каждый из предыдущих запросов можно записать при помоги^ exists:

on nomer = stud_nomer where not exists (select * from ball b where b.res > ball.res)

select * from student inner join ball

on nomer = stud_nomer where not exists(select * from ball b where ball.dis = b.dis and b.res > ball.res)

select * from student inner join ball

on nomer = stud_nomer where exists (select * from ball b where ball.dis = b.dis and b.res < ball.res)

Задача 62 (Супермаркет) Запишите следующий запрос при помощи подзапросов-сравнений и при помощи exists: найти товары, который имеет самую высокую цену (среди всех тюваров).

Задача 63 (Супермаркет) Запишите следующий запрос при помощи подзапросов-сравнений и при помощи exists: найти тювары, который имеет самую высокую цену (среди товаров того же вида).

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

Задача 65 (Библиотека) Запишите следующий запрос при помощи подзапросов-сравнений и при помощи exists: найдите книги, имеющие самый большой авторский коллектив.

Задача 66 (Библиотека) Запишите следующий запрос при помощи подзапросов-сравнений и при помощи exists: найдите авторов, которые написали (одни или в соавторстве) наибольшее количество книг.

Задача 67 (Библиотека) Запигиит,е следующий запрос при помощи подзапросов-сравнений и при помощи exists: найдите автюров, которые написали самое большое количество книг без соавторов.

Задача 68 (Банк) Запишите следующий запрос при помощи подзапросов-сравнений и при помощи exists: найдите клиентов, общая сумма на. счетах (с учетом общих с другими клиентами счетов) которых является наибольшей.

Задача 69 (Банк) Запишите следующей запрос при помощи подзапросов-сравнений и при помощи exists: найдите клиентов, общая сумма на счетах (без учета общих с другими клиентами счетов) которых является наиб