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

Основным оператором языка 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

67

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

Задача 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 ballon nomer = stud_nomer where res = all( select max(res) from ballb 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: найдите клиентов, общая сумма на. счетах (с учетом общих с другими клиентами счетов) которых является наибольшей.