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

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

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

Пример. SELECT *

FROM Группа,Студент;

Так как в запросе не определены связи между таблицами, то результатом запроса будет декартово произведение таблиц (каждая запись таблицы Группа будет соединена со всеми записями таблицы Студент).

Связи между таблицами в SQL могут быть установлены с помощью предложений WHERE и INNER JOIN.

1. Связь между таблицами с помощью предложения WHERE.

Если в качестве критерия поиска в предложении WHERE задаются условия, основанные на равенствах, то выполняется внутреннее соединение 2-х таблиц. Такое соединение называется эквисоединением. Синтаксис запроса на соединение будет иметь вид:

SELECT список_полей

FROM таблица_1 , таблица_2

WHERE таблица_!.поле 1 оператор таблица 2. поле 2

где таблица_1, таблица_2. – имена таблиц, записи которых подлежат объединению;

поле_1, поле_2 – имена объединяемых полей. Если эти поля не являются числовыми, то должны иметь одинаковый тип данных и содержать данные одного рода, однако, поля могут иметь разные имена.

оператор – любой оператор сравнения: =, <, >, <=, >=, <>.

Соединение может выполняться и по нескольким полям. В этом случае условие объединения задается:

таблица_1.поле_1 оператор таблица_2. поле_1 AND таблица_1. поле_2 оператор таблица_2.поле_2

Пример:

SELECT*

FROM Группа,Студент

WHERE Группа.[Номер группы]=Студент. [Номер группы];

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

Синтаксис запроса на множественное соединение:

SELECT список полей

FROM таблица_1, таблица_2 , таблица 3

WHERE таблица_1.поле_1 оператор таблица_2.поле_2 AND таблица_2. поле_1 оператор таблица_3. поле_1

Задание 15. Составить SQL-запрос для подсчета количества коммерческих студентов по каждой специальности.

Запрос должен содержать поля: Наименование специальности и Количество коммерческих студентов.

Множество записей полученных по запросу должно представлять собой соединение таблиц Студент, Группа и Специальность.

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

Технология

1. Создать новый SQL-запрос с использованием таблиц Студент и Группа, включив в запрос поля: Номер специальности, Номер группы. Фамилия, Коммерческий.

Текст запроса:

SELECT Группа.[Номер специальности], Студент.[Номер группы], Студент.Фамилия, Студент.Коммерческий

FROM Студент, Группа

WHERE Студент.[Номер группы]=Группа.[Номер группы];

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

3. Исправить запрос в соответствии со следующим текстом:

SELECT Специальность. [Наименование специальности], Группа. [Номер специальности], Студент. [Номер группы], Студент.Фамилия, Студент. Коммерческий

FROM Студент, Группа, Специальность

WHERE Специальность.[Номер специальности]=Группа.[Номер специальности] AND Студент.[Номер группы]=Группа.[Номер группы];

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

5. Откорректировать запрос, выполнив следующие действия:

• изменить список полей, оставив в списке поля Наименование специальности и Количество коммерческих студентов. Количество коммерческих студентов должно являться псевдонимом функции Соunt(Коммерческий);

• добавить в предложение WHERE условие для отбора в объединение только коммерческих студентов;

• добавить предложение GROUP BY для группировки данных по полю Наименование специальности.

6. Выполнить запрос и проверить полученную выборку. Затем сохранить запрос.

Вероятно, текст запроса будет иметь следующий вид:

SELECT Специальность.[Наименование специальности], СОUNT(ФИО) AS [Количество коммерческих студентов]

FROM Студент, Группа, Специальность

WHERE Специальность.[Номер специальности]= Группа. [Номер специальности] AND Студент.[Номер группы] =Группа.[Номер группы] AND Студент.Коммерческий=Yes

GROUP BY Специальность.[Наименование специальности];

Задание 16. Составить SQL-запрос для подсчета количества бюджетных (некоммерческих) студентов по каждому факультету

Запрос должен содержать поля: Наименование факультета и Количество бюджетных студентов

.

Задание 17. Составить SQL-запрос для подсчета итоговой стоимости платы за обучение коммерческими студентами по каждой специальности.

Запрос должен содержать поля: Наименование специальности и Итого по специальности.

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

Связь между таблицами с помощью предложения INNER JOIN.

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

SELECT список полей

FROM таблица_1 INNER JOIN таблица_2

ON таблица_1 .поле_1 оператор таблица_2.поле_2

Например.

SELECT*

FROM Группа INNER JOIN Студент

ON Группа.[Номер группы]=Студент.[Номер группы];

Если задаются условия, основанные на равенствах, то выполняется внутреннее соединение 2-х таблиц.

Чтобы установить связь по нескольким полям, следует связать несколько предложений ON в инструкции JOIN:

SELECT список_полей

FROM таблица_1 INNER JOIN таблица_2

ON таблица_1.поле_1 оператор таблица_2.поле_1 {AND|OR}

ON таблица_1.поле_2 оператор таблица_2.поле_2;

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

SELECT список_полей

FROM таблица_1 INNER JOIN таблица_2 INNER JOIN таблица_3

ON таблица_2.поле_1 оператор таблица_3.поле_1

ON таблица 1.поле1 оператор таблица_2.поле _1 {AND|OR}

ON таблица 1.поле _2 оператор таблица_2.поле_2;

Задание 18. С помощью SQL-запроса сформировать список групп с указанием наименования факультетов, к которым они относятся.

Запрос должен содержать поля: Номер группы и Наименование факультета.

В запросе использовать предложение INNER JOIN.

Задание 19. С помощью SQL-запроса сформировать список коммерческих студентов на оплату за обучение, упорядоченный по наименованию специальности. Список должен содержать поля Наименование специальности, Фамилия и Стоимость обучения. В запросе использовать предложение INNER JOIN.

Задание 20. Составить SQL-запрос для подсчета итоговой стоимости платы за обучение коммерческими студентами по каждой специальности в разрезе факультетов.

Запрос должен содержать поля: Наименование факультета, Наименование специальности и Итого по специальности.

В запросе использовать предложение INNER JOIN.

Внешнее соединение таблиц

Для выполнения внешнего соединения таблиц в любом предложении FROM используют предложения LEFT JOIN или RIGHT JOIN.

Синтаксис запроса на внешнее соединение:

SELECT список полей

FROM таблица_1 {LEFT|RIGHT} JOIN таблица_2

ON таблица_1.поле_1 оператор таблица_2.поле_2

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

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

Примечание. Операции LEFT JOIN или RIGHT JOIN могут быть вложены в операцию INNER JOIN, но операция INNER JOIN не может быть вложена в операцию LEFT JOIN или RIGHT JOIN.

Задание 21. Составить SQL-запрос для определения по каждой специальности номеров групп.

Запрос должен содержать поля Наименование специальности и Номер группы.

В запросе использовать предложение LEFT JOIN.

Задание 22. Создать копию запроса по заданию 21. Присвоить ему имя Задание 22. Изменить запрос так, чтобы результатом являлось правое внешнее объединение таблиц Группа и Факультет.

Задание 23. Составить SQL-запрос, определяющий на основании таблиц Группа и Студент количество студентов в группах. С помощью запроса определить, по каким группам отсутствуют сведения о студентах.

Задание 24. Составить SQL-запрос, формирующий список старост групп.

Перед составлением запроса внести следующие изменения в базу данных:

• изменить структуру таблицы Группа, добавив в таблицу новое поле Староста - числовое, длинное целое;

• в режиме таблицы ввести в поле Староста номера зачетных книжек тех студентов из таблицы Студент, кто является старостой. Для этого можно открыть таблицу Группа, затем последовательно щелкать по знаку «+», расположенному в левом столбце каждой записи. На экране будет появляться список студентов, обучающихся в текущей группе. Просматривая этот список, можно выбрать номер зачетной книжки старосты;

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

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

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