Упражнение 3
Откройте БД Продажи.
Указание: запросы сохраняйте под именами: Запрос_номерУпражнения (подчеркивание) НомерПунктаУпражнения, по которому строится запрос (например, Запрос2_1).
1)Создайте запрос для отображения товаров, проданных со скидкой. Укажите наименование товара, цену товара, размер скидки и цену со скидкой. В свойствах поля [ЦенаСоСкидкой] определите Формат поля – Денежный, Число десятичных знаков – 2.
2)Создайте запрос для отображения наименования товара, номера склада и количества данного товара, проданного с данного склада. В свойстве поля [Продажи].[Количество] определите Надпись – Всего продано.
3)Создайте запрос для отображения количества товаров, оставшегося после продаж. Укажите наименование товара, номер склада, количество товара на складе, количество проданного товара, количество оставшегося товара. Поле, где указано количество проданного товара подпишите Всего Продано; количество оставшегося товара – Осталось на складе.
4)Создайте запрос для отображения средних цен каждой группы товаров (группой считать товары одного наименования, например, мониторы, процессоры). Укажите наименование товара (сокращенное: 5-7 первых символов от наименования товара), среднюю цену. Первое поле назовите Товар, второе – Средняя цена. Для поля [Средняя цена] определите Денежный знак
– $, Число десятичных знаков – 2.
5)Один и тот же товар может храниться на нескольких складах. Создайте запрос для отображения списка таких товаров, указав общее количество каждого товара и число складов, на которых хранится данный товар. Определите Надписи для полей: [Товар], [Общее количество], [Количество складов] соответственно.
6)Создайте параметрический запрос для отображения товаров одного наименования (например, мониторов, процессоров…). В качестве параметра
30
используйте три первых символа наименования товара. Укажите наименование товара, цену и гарантийный срок.
7)Создайте запрос, вычисляющий налог с продаж. Укажите наименование товара, общую сумму продаж, налог с продаж. Налог с продажи равен 5%, если сумма продаж составляет <10000$, в противном случае налог с продаж равен 10%. Для полей [Сумма продаж], [Налог с продаж] установите денежный знак $, Число десятичных знаков – 2.
7. Модификация данных с помощью запросов
Чтобы обезопасить себя от случайностей, перед созданием запросов на модификацию данных, создайте резервные копии таблиц, данные которых будут модифицироваться.
7.1 Запрос на создание таблицы
БД на физическом уровне хранит только таблицы. Набор записей запросов физически не существует в БД. Ассеss создает его из данных таблиц только во время выполнения запроса. Иногда возникает необходимость сохранить извлекаемые с помощью запроса на выборку данные в новой таблице:
1.Создайте новый запрос на выборку тех записей, из которых должна состоять создаваемая с помощью запроса таблица.
2.Проверьте правильность отбора записей, перейдя в режим ТАБЛИЦА.
3.Преобразуйте запрос на выборку в запрос на создание новой таблицы. Для этого, вернувшись в режим КОНСТРУКТОРА, выберите Создание таблицы...(меню Тип запроса).
4.В появившемся окне введите имя новой таблицы.
5.Выполните запрос (кнопка 
на панели инструментов ).
7.2 Запрос на обновление
Используя этот тип запроса, можно изменить в базовой таблице группу блоков данных, отобранную на основе определенных критериев:
31
1.Создайте новый запрос на выборку и проверьте его корректность, перейдя в режим ТАБЛИЦА.
2.Преобразуйте запрос на выборку в запрос на обновление. Для этого, вернувшись в режим КОНСТРУКТОРА, выберите команду Обновление (меню Запрос).
3.В появившейся в бланке запроса строке «Обновление» в соответствующих столбцах задайте новые значения полей таблицы. В качестве таковых могут выступать и вычисляемые значения. В случае необходимости воспользуйтесь Построителем выражений (кнопка ПОСТРОИТЬ панели инструментов или соответствующая кнопка контекстного меню).
4.Выполните запрос.
7.3Запрос на добавление записей
Спомощью этого типа запроса блоки данных одной таблицы (все или отобранные запросом) можно присоединить в конец другой таблицы:
1.Создайте новый запрос на выборку тех блоков данных, которые будут добавлены в некоторую таблицу и проверьте его корректность, перейдя
врежим ТАБЛИЦА.
2.Преобразуйте запрос на выборку в запрос на добавление. Для этого, вернувшись в режим КОНСТРУКТОРА, выберите команду Добавление...(меню Запрос).
3.В появившемся окне введите имя таблицы, к которой нужно присоединить данные и нажмите ОК.
4.Выполните запрос.
7.4Запрос на удаление записей
Спомощью данного типа запроса можно удалить из базовой таблицы группу блоков данных, отобранных по определенным критериям. При этом следует тщательно проанализировать критерии отбора, поскольку эту операцию нельзя отменить.
1.Создайте новый запрос на выборку удаляемых блоков данных. Отбор блоков данных выполняется в соответствии с заданными в строке «Условие» критериями.
2.Проверьте корректность сформулированных условий, перейдя в режим ТАБЛИЦА.
3.Преобразуйте запрос на выборку в запрос на удаление записей. Для этого, вернувшись в режим КОНСТРУКТОРА, выберите команду Удалить
(меню Запрос).
4.В появившейся строке «Удалить» установите критерии отбора.
5.Выполните запрос.
Упражнение 4
Откройте БД Продажи.
Создайте копию таблицы [Товары]. Присвойте ей имя [КопияТовары]. Создайте копию таблицы [Хранение]. Присвойте ей имя [КопияХранение].
32
Указание: запросы сохраняйте под именами: Запрос_номерУпражнения (подчеркивание) НомерПунктаУпражнения, по которому строится запрос (например, Запрос2_1).
1)Создайте запрос на обновление в таблице [КопияТовары] цен с учетом сезонных скидок на 10%.
2)Создайте запрос на обновление в таблице [КопияХранение] количества товара, оставшегося на складе после продаж.
3)Создайте запрос на создание таблицы [Мониторы], отображающей все поля таблицы [КопияТовары].
4)Создайте запрос на создание таблицы [ТоварыМаркиAsus], отображающие все поля таблицы [КопияТовары].
5)Создайте запрос на добавление в таблицу [Мониторы] данных о Видеокартах из таблицы [КопияТовары]. Таблицу [Мониторы] переименуйте. Новой таблице присвойте имя [Мониторы_и_видеокарты].
6)Создайте запрос на добавление в таблицу [ТоварыМаркиAsus] данных о товарах фирмы AMD из таблицы [КопияТовары]. Новой таблице присвойте имя [ТоварыМарокAsus_и_AMD].
7)Создайте запрос на создание таблицы [ТоварыLizarin], отображающей поля [КодТовара], [Наименование], [Цена], [НомерСклада].
8)Создайте запрос на удаление из таблицы [КопияТовары] данных о Мониторах и Видеокартах.
9)Создайте запрос на удаление из таблицы [КопияТовары] данных о товарах фирм Asus и AMD.
Самостоятельная работа
1)На диске D создайте новую базу данных. В качестве имени БД используйте Вашу фамилию. Импортируйте в новую БД из БД Продажи таблицы [Товары], [Хранение], [Склады], [Фирмы], [Продажи].
2)Проверьте наличие связей в схеме данных.
3)Создайте запрос для отображения дат продаж процессоров Intel с указанием проданного количества, а также номеров и телефонов складов, с которых они были проданы.
4)Создайте запрос для отображения количества товаров, проданных фирмами Citilink и Lizarin c 1 по 10 декабря 2003г.
5)Создайте параметрический запрос для отображения товаров, проданных конкретной фирмой (параметром является название фирмы). Укажите наименование товара, дату продажи, количество проданного товара и номер склада, с которого был продан товар.
6)Создайте перекрестный запрос, отображающий количество проданного товара фирмами Lizarin и Citilink. В заголовках строк укажите наименования товаров, в заголовках столбцов – названия фирм.
7)Создайте запрос, отображающий количество складов каждой фирмы.
33
8)Фирма Lizarin проводит рекламную акцию и снижает цены на свои товары на 10%. Создайте запрос, отображающий товары фирмы Lizarin с новыми ценами. Укажите поля: Наименование товара, СтараяЦена, НоваяЦена.
9)Создайте копию таблицы [Товары]. Присвойте ей имя [КопияТовары]. 10)Создайте запрос на создание таблицы [ПоставкиПрекращены], отображаю-
щей список товаров, поставки которых прекращены. Используйте таблицу [КопияТовары].
11)Holding-центр проводит рекламную акцию и снижает цены на 17-дюймовые мониторы на 20%. Создайте запрос на обновление и обновите цены в таблице [КопияТовары] в соответствии с условиями акции.
12)Стало известно, что прекращены поставки видеокарт фирмы Asus. Укажите это в таблице [КопияТовары] (поставьте «галочки» в поле [Поставки] соответствующих строк). Создайте запрос на добавление и добавьте эти товары в таблицу [ПоставкиПрекращены].
13)Создайте запрос на удаление из таблицы [КопияТовары] товаров, поставки на которые прекращены.
8. Создание запросов с использованием языка SQL
На самом деле любой запрос в MS Access реализуется с помощью языка SQL. Хотя большинство запросов можно построить, пользуясь средствами, которые Access предоставляет в режиме конструктора, и в этом случае они будут храниться в виде инструкций SQL. Некоторые типы запросов можно построить, только используя язык SQL.
8.1 Синтаксис инструкции SELECT в MS Access
Ядром языка SQL является инструкция SELECT. Она используется для отбора строк и столбцов из таблиц базы данных и содержит пять основных предложений. В общем случае ее синтаксис можно представить в следующем виде:
SELECT <список–полей>1 FROM <список–таблиц>
[WHERE <спецификация–отбора–строк>]2 [GROUP BY <спецификация–группировки>] [HAVING <спецификация–отбора–групп>] [ORDER BY <спецификация–сортировки>]
ВMS Access реализованы важные средства расширения языка:
∙Инструкция TRANSFORM, позволяющая строить перекрестные запросы;
∙Предложение IN, с помощью которого задается связь с удаленной базой данных или указываются имена столбцов перекрестного запроса;
и др.
1 В угловые скобки заключается обязательный элемент синтаксиса.
2 В квадратные скобки заключаются один или несколько необязательных элементов, разделенных символом «вертикальная черта» (|).
34
Синтаксис
[[[]{имя–таблицы | имя–запроса–на–выборку | псевдоним}[]].][[]имя– поля[]]1
Имя–таблицы, имя–запроса–на–выборку и псевдоним, уточняющий имя поля обязательно должны присутствовать в предложении FROM запроса. Если таблица или запрос имеет псевдоним, необходимо использовать именно его, а не реальное имя таблицы или запроса.
Первую часть имени (включая точку) можно опустить, если поле имеется только в одной из таблиц предложения FROM.
Имена, содержащие пробелы, обязательно должны заключаться в квадратные скобки.
При определении списка полей использование символа «*» вместо имени поля указывает, что нужно отобразить все столбцы данной таблицы. Если в качестве списка полей использован символ «*», то отбираются все столбцы всех таблиц, указанных в предложении FROM.
Пример
Следующее выражение задает поле из таблицы [Товары], имя которого Наименование: Товары.Наименование
8.2 Предложение FROM
Задает таблицы или запросы, служащие источниками данных для создаваемого запроса.
Синтаксис
FROM {имя–таблицы [[AS] псевдоним ] |
имя–запроса–на–выборку [[AS] псевдоним ] | <таблица–объединения>} …
[IN <спецификация источника>] где <таблица–объединения>:
{имя–таблицы [[AS] псевдоним ] | имя–запроса–на–выборку [[AS] псевдоним ] |
(<таблица–объединения>)}
{INNER | LEFT | RIGHT} JOIN
{имя–таблицы [[AS] псевдоним ] | имя–запроса–на–выборку [[AS] псевдоним ] |
(<таблица–объединения>)}
ON <условие объединения>
Для каждой таблицы и запроса можно определить альтернативное имя. Оно используется как псевдоним вместо полного имени таблицы при задании имен столбцов в списке полей, условии объединения и предложении WHERE.
1 В фигурные скобки заключаются один или несколько элементов, разделенных символом «вертикальная черта». Следует выбрать один из перечисленных элементов.
35
Пример
Следующий запрос отображает все поля таблицы Товары: SELECT Товары.*
FROM Товары;1
Следующий запрос отображает товары, цена которых больше $100 и гарантийный срок которых 36 месяцев:
SELECT Товары.Наименование, Товары.Цена FROM Товары
WHERE Товары.Цена > 100 AND Товары.[Гарантийный срок] = 36;
Большие возможности SQL, во многом, основаны на его способности объединять информацию из нескольких таблиц или запросов.
Для задания типа объединения таблиц в логический набор записей, из которого будет выбираться необходимая информация, в предложении FROM используется операция JOIN.
Операция INNER JOIN используется для получения всех строк из обеих логических таблиц, удовлетворяющих условию объединения.
Операция LEFT JOIN возвращает все строки из первой логической таблицы, объединенные с теми строками из второй, для которых выполняется условие объединения.
Аналогично, операция RIGHT JOIN возвращает все строки из второй логической таблицы, объединенные с теми строками из первой таблицы, для которых выполняется условие объединения.
<условие объединения> – выражение, в котором поля первой таблицы сравниваются с полями второй таблицы. В бланке запроса QBE в условии объединения используется только оператор равно (=). Запрос на основе объединения таблиц по неравенству (<, >, <>, <= или >=) можно создать только в режиме SQL.
Пример 1
Следующий запрос отображает сведения о складах фирмы Citilink. SELECT Склады.НомерСклада, Склады.Адрес, Склады.Телефон FROM Фирмы INNER JOIN Склады
ON Фирмы.КодФирмы = Склады.КодФирмы WHERE Фирмы.Название = «Citilink»;
Пример 2
Запрос отображает список товаров, которые еще не продавались, с указанием наименования товаров и номеров складов, на которых они хранятся.
SELECT Товары.Наименование, Хранение.НомерСклада
FROM (Товары INNER JOIN Хранение ON Товары.КодТовара = Хранение.КодТовара) LEFT JOIN Продажи ON Хранение.ID = Продажи.ID
WHERE (( Продажи.Количество) Is Null);
1 Запрос должен заканчиваться символом «точка с запятой» (;)
36