Реляционная алгебра и SQL

Рассмотрим, как связаны операции реляционной алгебры и язык SQL, т.е. приведем примеры запросов SQL, аналогичных операциям реляционной алгебры. В качестве примера базы данных будем использовать «Музыкантов».

Музыканты (НомМуз, ИмяМуз, ДатаРожд, СтрРожд)
 
Сочинения (НомСоч, НазСоч, ДатаСоч, НомМуз)
Столбец НомМуз представляет собой ссылку на таблицу «Музыканты» и содержит номера музыкантов- композиторов.
 
Исполнители (НомИсп, Инструмент, Оценка, НомМуз)
Столбец НомМуз представляет собой ссылку на таблицу «Музыканты».  

Ансамбли (НомАнс, НазАнс, СтрАнс, НомМуз)
Столбец НомМуз представляет собой ссылку на таблицу «Музыканты» и содержит номера музыкантов-руководителей ансамблей.
 
УчастникиАнсамблей(НомАнс, НомИсп)
Эта таблица содержит ссылки на таблицы «Ансамбли» и «Исполнители».
 
Исполнения (НомМуз, НомАнс, НомСоч, ДатаИсп, СтрИсп, ГорИсп)
Таблица имеет составной первичный ключ, а также ссылки на таблицы «Сочинения», «Музыканты» (имеются в виду дирижеры) и «Ансамбли».

Операция проекции proj

выражается через SELECT с ключевым словом DISTINCT.
Получить все названия ансамблей:
proj НазАнс (Ансамбли)
 
SELECT DISTINCT НазАнс FROM Ансамбли

Операция выбора sel

выражается через SELECT с ключевым словом WHERE.
Получить данные об ансамблях из России:
sel СтрАнс='Россия' (Ансамбли)
 
SELECT * FROM Ансамбли WHERE СтрАнс='Россия'
 
Условия также могут быть и сложными.
 
Получить имена музыкантов, родившихся в 20-м веке
SELECT ИмяМуз FROM Музыканты WHERE ДатаРожд>'31.12.1900' AND ДатаРожд<'01.01.2001'

Операция соединения таблиц join

может быть выражена несколькими способами.
Получить имена композиторов:
proj ИмяМуз (Музыканты join Сочинения)

Можно использовать связь таблиц через условие WHERE:
 SELECT DISTINCT ИмяМуз FROM Музыканты М, Сочинения С WHERE С.НомМуз=М.НомМуз

Можно использовать более современный синтаксис JOIN ... ON
SELECT DISTINCT ИмяМуз FROM Музыканты М JOIN Сочинения С
ON С.НомМуз=М.НомМуз

Операция соединения таблиц join

Если требуется вывести данные из одной таблицы, а условие накладывать на другую таблицу, то удобно использовать подзапросы, связанные и несвязанные.
 
SELECT DISTINCT ИмяМуз FROM Музыканты WHERE НомМуз IN
(SELECT НомМуз FROM Сочинения)
или
SELECT DISTINCT ИмяМуз FROM Музыканты WHERE НомМуз = Any
(SELECT НомМуз FROM Сочинения)
или
SELECT DISTINCT ИмяМуз FROM Музыканты М WHERE EXISTS
(SELECT * FROM Сочинения С WHERE
С.НомМуз=М.НомМуз)

Операция соединения таблиц join

Приведем пример сложного запроса, использующего данные из всех 6 таблиц базы данных.
Получить названия ансамблей, которые играли Моцарта на саксофоне:
 
SELECT НазАнс FROM Ансамбли WHERE НомАнс IN
(
SELECT И1.НомАнс
FROM Исполнения И1, Исполнители И2, Музыканты М,
Сочинения С, УчастникиАнсамблей У
WHERE И1.НомСоч=С.НомСоч AND С.НомМуз=М.НомМуз AND
И1.НомАнс=У.НомАнс AND И2.НомИсп=У.НомИсп AND
М.ИмяМуз='Моцарт' AND
И2.Инструмент='Саксофон'
)

Операция объединения union

соответствует нескольким командам SELECT, связанным ключевым словом UNION.
Получить общий список фамилий композиторов и дирижеров:
proj ИмяМуз (Музыканты join Сочинения)
union
proj ИмяМуз (Музыканты join Исполнения)
 
SELECT DISTINCT ИмяМуз FROM Музыканты М, Сочинения С WHERE С.НомМуз=М.НомМуз
UNION
SELECT DISTINCT ИмяМуз FROM Музыканты М, Исполнения И WHERE И.НомМуз=М.НомМуз

Операция пересечения intersection

может быть выражена несколькими способами.
Получить имена музыкантов, которые играют и на саксофоне, и на кларнете:
 
proj ИмяМуз (Музыканты join sel
Инструмент='Саксофон'(Исполнители))
intersection
proj ИмяМуз (Музыканты join sel
Инструмент='Кларнет'(Исполнители))

Операция пересечения intersection

SELECT DISTINCT ИмяМуз FROM Музыканты М1,
Исполнители И1, Исполнители И2
WHERE М1.НомМуз=И1.НомМуз AND
И1.Инструмент='Саксофон' AND
И2.Инструмент='Кларнет' AND
И2.НомМуз=И1.НомМуз
 или
 SELECT DISTINCT ИмяМуз
FROM Музыканты М1, Исполнители И1
WHERE М1.НомМуз=И1.НомМуз AND
И1.Инструмент='Саксофон' AND
М1.НомМуз IN
(SELECT НомМуз FROM Исполнители И2
WHERE И2.Инструмент='Кларнет')
 

Операция пересечения intersection

или
 SELECT DISTINCT ИмяМуз
FROM Музыканты М1, Исполнители И1
WHERE М1.НомМуз=И1.НомМуз AND
И1.Инструмент='Саксофон' AND
М1.НомМуз =ANY
(SELECT НомМуз FROM Исполнители И2
WHERE И2.Инструмент='Кларнет')
 или
 SELECT DISTINCT ИмяМуз
FROM Музыканты М1, Исполнители И1
WHERE М1.НомМуз=И1.НомМуз AND
И1.Инструмент='Саксофон' AND
EXISTS
(SELECT * FROM Исполнители И2
WHERE И2.Инструмент='Кларнет'
AND И2.НомМуз=И1.НомМуз)  

Операция вычитания difference

также может быть выражена несколькими способами.
Получить имена музыкантов, которые играют на саксофоне, но не играют на кларнете:
 
proj ИмяМуз (Музыканты join sel
Инструмент='Саксофон'(Исполнители))
difference
proj ИмяМуз (Музыканты join sel
Инструмент='Кларнет'(Исполнители))

Операция вычитания difference

SELECT DISTINCT ИмяМуз
FROM Музыканты М1, Исполнители И1
WHERE М1.НомМуз=И1.НомМуз AND
И1.Инструмент='Саксофон' AND
М1.НомМуз NOT IN
(SELECT НомМуз FROM Исполнители И2
WHERE И2.Инструмент='Кларнет')
 или
 SELECT DISTINCT ИмяМуз
FROM Музыканты М1, Исполнители И1
WHERE М1.НомМуз=И1.НомМуз AND
И1.Инструмент='Саксофон' AND
М1.НомМуз !=ALL
(SELECT НомМуз FROM Исполнители И2
WHERE И2.Инструмент='Кларнет')
 

Операция вычитания difference

или
 
SELECT DISTINCT ИмяМуз
FROM Музыканты М1, Исполнители И1
WHERE М1.НомМуз=И1.НомМуз AND
И1.Инструмент='Саксофон' AND
NOT EXISTS
(SELECT * FROM Исполнители И2
WHERE И2.Инструмент='Кларнет'
AND И2.НомМуз=И1.НомМуз)
 

Операция умножения product

получается, если мы выполняем выборку из 2 таблиц, но не указываем условия связи.
Получить всевозможные пары имен музыкантов:
Музыканты2 aliases Музыканты
 
proj Музыканты.ИмяМуз, Музыканты2.ИмяМуз
(Музыканты product Музыканты2)
 
SELECT М1.ИмяМуз, М2.ИмяМуз
FROM Музыканты М1, Музыканты М2

операция деления division

Очень интересно выглядит операция деления division. Она представляет собой двойное отрицание существования.
 
Получить названия ансамблей, которые играли все произведения Моцарта (т.е., нет ни одного произведения Моцарта, которого они бы не играли):
proj НазАнс
(proj НомАнс, НомСоч (Исполнения)
division
proj НомСоч (sel ИмяМуз='Моцарт' (Музыканты)
join Сочинения)
join Ансамбли)

операция деления division

SELECT НазАнс FROM Ансамбли А
WHERE NOT EXISTS
(
SELECT * FROM Сочинения С, Музыканты М
WHERE С.НомМуз=М.НомМуз AND ИмяМуз='Моцарт'
AND NOT EXISTS
(
SELECT * FROM Исполнения И
WHERE И.НомСоч=С.НомСоч AND
И.НомАнс=А.НомАнс
)
)