Учебное пособие Власовой, Самойловой MS Access

6.Ввести в 3-й столбец поле Коммерческий и замените название столбца на Количество коммерческих. Для этого ячейка с названием поля должна содержать:

Количество коммерческих: Коммерческий

(новое и старое названия столбца разделены символом «двоеточие»).

7.Щелкнув по кнопке инструментальной панели Групповые операции, добавить в бланк строку Групповая операция и выбрать из списка для 3-го столбца операцию Count.

8.Просмотреть выборку с подсчетом итогов, щелкнув по кнопке инструментальной панели Запуск, либо выполнив команду ЗАПРОС/Запуск.

9.Вернуться в режим конструктора запросов, щелкнув по кнопке Вид инструментальной панели.

10.Сохранить запрос, присвоив ему имя Задание 13.

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

Запрос должен содержать следующий текст:

SELECT [Номер группы], соunt(Коммерческий) AS [Количество коммерческих]

FROM Студент

WHERE Коммерческий=ТRUЕ

GROUP BY [Номер группы]

ORDER BY [Номер группы];

Выполнить запрос. Сравнить результаты работы запросов из заданий 13 и 14. Просмотреть, сравнить и проанализировать структуры этих запросов сначала в режиме SQL, затем в режиме конструктора.

Примечание. При создании сложного SQL-запроса можно с целью проверки синтаксиса запроса открывать его в окне конструктора QBEзапроса. Если команды запроса содержат орфографические или синтаксические ошибки, то Access не сможет преобразовать SQL-запрос в QBE-запрос и выведет окно сообщения с описанием ошибки.

82

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

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

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

Пример. SELECT *

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

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

Студент).

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

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

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

SELECT список_полей FROM таблица_1 , таблица_2

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

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

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

оператор – любой оператор сравнения: =, <, >, <=, >=, <>. Соединение может выполняться и по нескольким полям. В этом

случае условие объединения задается:

83

таблица_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 Группа.[Номер специальности], Студент.[Номер группы], Студент.Фамилия, Студент.Коммерческий

84

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SELECT

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

специальности],

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

85

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

86

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.

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

87

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

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

88

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

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

книжки старосты; Список студентов, создаваемый запросом должен содержать

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

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

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

Использование вычисляемых полей в SQL-запросах

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

Специальность [Стоимость обучения]*(1+НДС) AS [Стоимость с учетом НДС]

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

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

Например, необходимо упорядочить выборку по возрастанию значений стоимости с учетом НДС, в запрос следует добавить предложение

ORDER BY Специальность. [Стоимость обучения] *(1+НДС)

89

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

GROUP BY Группа.[Номер факультета]

в список полей включить групповую операцию:

SUМ(Специальностъ.[Стоимость обучения]*(1+НДС))

Пример. Определить итоговую стоимость обучения с учетом НДС по номерам специальностей.

SELECT Специальность.[Номер специальности], Sum(Специальность. [Стоимость обучения] * (1+НДС)) AS [Итоговая стоимость с учетом НДС] FROM Специальность INNER JOIN ( Группа INNER JOIN Студент ON Студент.[Номер группы] = Группа.[Номер группы]) ON

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

Задание 25. Составить SQL-запрос для получения справки по стоимости обучения коммерческих студентов с учетом НДС. Принять, что стоимость обучения с учетом НДС коммерческого студента равна стоимости обучения по выбранной им специальности, умноженной на (1+0,2), где 0,2 - величина НДС.

В справку выводить следующие сведения о студенте: Номер группы,

Наименование специальности, Номер зачетной книжки, Фамилия и Стоимость обучения с учетом НДС.

Сведения о студентах упорядочить по убыванию значений

Стоимость обучения с учетом НДС.

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

В справку выводить Наименование специальности и Итоговую стоимость обучения с учетом НДС.

90

Создание новой таблицы

Создание повой таблицы выполняется командой CREATE TABLE. В этой команде выполняется описания новой таблицы, ее полей и индексов.

Синтаксис команда:

CREATE TABLE таблица (поле_1 тип [(размер)] [NOT NULL]

[индекс_1]

[, поле_2 тип [(размер)][NOT NULL] [индекс_2] [,...]]

[, CONSTRAINT составной_Индекс [,...]])

где таблица - имя создаваемой таблицы; поле_1, поле_2 и т.д. – имена нолей, создаваемых в новой таблице.

Таблица должна содержать хотя бы одно поле; тип – тип данных поля в новой таблице;

размер – размер поля в символах (только для текстовых и двоичных полей);

индекс_1, ипдекс_2 – предложение CONSTRAINT,

предназначенное для создания простого индекса;

составной_Индекс – предложение CONSTRAINT,

предназначенное для создания составного индекса.

Примечание. Если для поля добавлено ограничение NOT NULL, то при добавлении новых записей это поле должно содержать допустимые данные.

Пример. Создается новая таблица с двумя текстовыми полями и числовым полем. Поле SSN делается ключевым полем.

CREATE TABLE НоваяТаблица (Имя TEXT, Фамилия TEXT, SSN INTEGER

CONSTRAINT МойИндекс PRIMARY KEY);

Задание 27. Составить SQL-запрос на создание новой таблицы с именем Дисциплина1. Таблица Дисциплина1 должна иметь следующий состав полей:

Код дисциплины – числовое, байтовое (Byte);

91