(часть 2, углубленное изучение)
Цель задания: Освоить приемы составления запросов с использованием
различных операторов языка SQL.
Последовательность действий:
1. Запустите Microsoft Access и создайте новую базу данных с именем «Ту-
рАгентство».
База данных должна содержать данные о туристических поездках, клиентах и
заказах. Таблицы базы данных следует создавать средствами языка SQL, без
использования визуальных инструментов. Для того, чтобы создать таблицу, не-
обходимо сначала создать запрос SQL на создание таблицы. Обычно при созда-
нии базы данных нет необходимости создавать все таблицы посредством SQL,
но иногда операторы SQL могут быть использованы в приложениях для созда-
ния или изменения структуры таблиц автоматически.
Структура создаваемой базы данных приведена на рисунке.
2. Создайте запрос с именем «СоздатьТблТранспорт». В режиме SQL вве-
дите текст запроса на создание таблицы:
CREATE TABLE Транспорт (
id COUNTER PRIMARY KEY,
название TEXT(30)
);
Этот запрос создает таблицу «Транспорт», содержащую два поля:
id – уникальный числовой идентификатор (первичный ключ) записи, значе-
ния которого назначаются автоматически (в режиме конструктора таблиц этот
тип данных называется «Счетчик»);
название – текстовое поле, которое может содержать не более 30 символов.
Таблица будет содержать названия видов транспорта (самолет, автобус и т.п.),
применяемых при организации туров.
3. Создайте запрос с именем «СоздатьТблСтрана», содержащий текст за-
проса на языке SQL:
CREATE TABLE Страна (
id COUNTER PRIMARY KEY,
название TEXT(30)
);
Этот запрос создает таблицу «Страна». Таблица будет содержать список
стран, в которые могут организовываться туры.
4. Создайте запрос с именем «СоздатьТблТур». В режиме SQL введите
текст запроса:
CREATE TABLE Тур (
id COUNTER PRIMARY KEY,
название TEXT(30),
отъезд DATE,
возвращение DATE,
страна INTEGER REFERENCES Страна (id),
транспорт INTEGER REFERENCES Транспорт (id),
цена CURRENCY NOT NULL
);
Запрос создает таблицу «Тур». Таблица будет содержать сведения о кон-
кретных туристических поездках.
Поля «отъезд» и «возвращение» будут содержать даты начала и окончания
тура соответственно. Для поля «страна» с помощью ключевого слова
«REFERENCES» («ссылается») определяется связь с полем «id» таблицы
«Страна». Аналогично, поле «транспорт» связывается с полем «id» таблицы
«Транспорт». Поле цена (имеющее денежный тип данных) предназначена для
хранения информации о стоимости тура и не может быть пустым.
5. Создайте запрос с именем «СоздатьТблКлиент», содержащий текст за-
проса:
CREATE TABLE Клиент (
id COUNTER PRIMARY KEY,
фио TEXT(50)
);
Таблица «Клиент» будет содержать имена клиентов, которые обращались в
данное туристическое агентство. Текстовое поле «фио» может содержать до 50
символов.
6. Создайте запрос с именем «СоздатьТблЗаказ»:
CREATE TABLE Заказ (
тур INTEGER REFERENCES Тур (id),
клиентINTEGER REFERENCES Клиент (id),
количество INTEGER NOT NULL,
PRIMARY KEY (тур, клиент)
);
Таблица «Заказ» будет содержать сведения о заказах, оформленных клиен-
тами данного турагентства на организуемые им туристические поездки. Поле
«тур» связывается с полем «id» таблицы «Тур», поле «клиент» - с полем «id»
таблицы «Клиент». Поле «количество» показывает, сколько билетов на вы-
бранный тур заказал данный клиент. Пара полей «тур» и «клиент» образует
первичный ключ таблицы. Это означает, что один клиент может заказать не-
сколько билетов на один и тот же тур, но все они должны входить в один заказ
(невозможно создать другой заказ на имя того же клиента на тот же самый тур).
7. Выполните созданные запросы в следующей последовательности: «Соз-
датьТблТранспорт», «СоздатьТблСтрана», «СоздатьТблТур», «СоздатьТ-
блКлиент», «СоздатьТблЗаказ». Последовательность создания таблиц не мо-
жет быть произвольной, поскольку поля таблицы «Тур» ссылаются на поля
таблиц «Транспорт» и «Страна», а поля таблицы «Заказ» - на поля таблиц
«Тур» и «Клиент».
8. Убедитесь, что все необходимые таблицы были созданы. Исследуйте ка-
ждую из таблиц с помощью конструктора и выясните, как применение ключе-
вых слов языка SQL (например, «NOT NULL» или «TEXT(30)») повлияло на
свойства полей таблиц.
9. Откройте окно «Схема данных» и убедитесь, что между таблицами были
установлены все связи, заданные в запросах SQL ключевым словом
«REFERENCES».
10. Создайте запрос с именем «ДобавитьСтрану». В режиме SQL введите
текст запроса на добавление данных в таблицу:
INSERT INTO Страна
(название) VALUES ([Введите название страны])
;
Этот запрос добавляет в таблицу «Страна» новую запись. Запрос содержит
параметр, поэтому при его запуске будет выдано окно для ввода дополнитель-
ной информации.
11. Используя созданный запрос, добавьте названия следующих стран:
- Россия
- Турция
- Индия
- Египет
- Околоземная орбита
12. Создайте запрос с именем «ДобавитьТранспорт». В режиме SQL вве-
дите текст запроса:
INSERT INTO Транспорт
(название) VALUES ([Введите вид транспорта])
;
Запрос будет добавлять новые записи в таблицу «Транспорт», запрашивая
у пользователя название вида транспорта.
13. Используя созданный запрос, добавьте следующие названия видов
транспорта:
- Самолет
- Поезд
- Автобус
- Теплоход
- Собачья упряжка
- Слон
- Велорикша
- Верблюд
- Космический корабль
14. Откройте таблицу «Тур» в режиме конструктора и определите свойства
подстановки столбцов для полей «страна» и «транспорт», используя данные
таблиц «Страна» и «Транспорт» соответственно.
15. Откройте таблицу «Тур» в режиме таблицы и добавьте не менее десяти
записей о турах в различные страны и на различных видах транспорта, задавая
различные значения для времени отъезда и возвращения, а также цены тура.
Каждому туру должно быть присвоено название (по возможности романтиче-
ское).
16. Введите в таблицу «Клиент» информацию не менее чем о десяти клиен-
тах.
17. Откройте таблицу «Заказ» в режиме конструктора и определите свойст-
ва подстановки столбцов для полей «тур» и «клиент», используя название тура
из таблицы «Тур» и поле «фио» из таблицы «Клиент» соответственно.
18. Откройте таблицу «Заказ» в режиме таблицы и добавьте не менее пят-
надцати записей о заказах. Для каждого заказа должно быть указано количество
билетов.
19. Самостоятельно создайте запрос «Суммы по заказам». Запрос должен
выводить список всех заказов (название тура, фио клиента) и для каждого из
заказов его стоимость (произведение цены тура на количество заказанных би-
летов).
20. Самостоятельно создайте запрос «Суммы по турам». Запрос должен
выводить список всех туров и для каждого тура – стоимость всех проданных на
него билетов (вычисляется как произведение цены тура на сумму поля «количе-
ство» всех заказов, связанных с данным туром).
21. Создайте запрос «Суммы заказов по клиентам». Введите в режиме
SQL следующий код:
SELECT Клиент.id, Клиент.фио, SUM(Стоимость) AS Сумма
FROM Клиент LEFT JOIN (
SELECT Тур.цена*Заказ.количество AS Стоимость,
Заказ.клиент
FROM Тур INNER JOIN Заказ ON Тур.id = Заказ.тур
) AS ТурЗаказ ON Клиент.id=ТурЗаказ.клиент
GROUP BY Клиент.id, Клиент.фио;
Запрос формирует список клиентов и для каждого клиента указывает сум-
марную стоимость всех его заказов
22. Самостоятельно создайте запрос «Самый уважаемый клиент», выво-
дящий имя клиента, который оформил заказов на самую большую сумму.
23. Самостоятельно создайте запрос «Элитные туры», выводящий список
туров, цена которых выше средней цены по всем имеющимся турам.
