Работа с Access. Учебнопрактическое пособие Хабаровск 2004 Содержание Введение 4 Лабораторная работа Создание таблиц 5 Основные понятия субд access 5

• Название: Учебнопрактическое пособие Хабаровск 2004 Содержание Введение 4 Лабораторная работа Создание таблиц 5 Основные понятия субд access 5
• Дата: 16.01.2023
• Размер: 2.57 Mb.
• Имя файла: Работа с Access.doc
• Тип: Учебно-практическое пособие; #888653
• страница: 17 из 19

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

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-запрос, формирующий список старост групп.

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

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

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

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

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