Лабораторная работа №2
Лабораторная №2. Создание запросов на обновление и на выборку средствами СУБД Access и ANSI SQL
Используя методичку Модели Клиенты Продавцы выполнить:
Лабораторная №2 Создание запросов.
Запрос1
Выбрать сведения о клиентах, проживающих в Бресте и заказавших автомобиль марки Citroen после 01.05.98
В Access выбираем в Меню закладок : СОЗДАНИЕ — КОНСТРУКТОР ЗАПРОСОВ, далее в появившемся окне выбираем таблицы которые нужны для создания запроса, в данном запросе это таблицы Клиенты и Модели, после добавления таблиц мы видим отображающуюся связь по ключевому полю Код_модели и в графе Условие отбора прописываем те условия, которые заданы в условии Запрос1
В режиме конструктора:
В результате выполнения запроса получим:
| ФИО | Адрес | Дата_заказа | Модель |
|---|---|---|---|
| Быстров | Брест | 25.06.1998 | Citroen |
| Чудаков | Брест | 20.07.1998 | Citroen |
Этот же запрос выполним в режиме SQL
Для этого выбираем СОЗДАНИЕ — КОНСТРУКТОР ЗАПРОСОВ, далее переключаемся в режим SQL, в котором пишем запрос на SQL
На рис. в режиме SQL: набрав текст запроса в режиме SQL, активируем закладку РАБОТА С ЗАПРОСАМИ (т.е. нажимаем на нее), появляется меню для работы с запросами и нажимаем ВЫПОЛНИТЬ (см. красный вопросительный знак), если запрос написан без ошибок, то в результате выполнения запроса мы увидим таблицу такую же как и при выполнении этого запроса в режиме конструктора/
Обратите внимание на условие даты, в режиме SQL формат даты: месяц/дата/год
Результат выполнения запроса:
| ФИО | Адрес | Дата_заказа | Модель |
|---|---|---|---|
| Быстров | Брест | 25.06.1998 | Citroen |
| Чудаков | Брест | 20.07.1998 | Citroen |
Запрос №2.
Определить максимальную и среднюю сумму заказа, выполненную каждым продавцом. В результате вывести поля: №_продавца, вычисляемым полям присвоить собственные имена : Макс_сумма_заказа, Средняя_сумма_заказа. Использовать таблицы Клиенты и Продавцы
Это запрос с группировкой. После вызова окна конструктора запросов и выполнения всех действий необходимо в соответствующих полях задать статистические функции MAX (максимальное значение), AVG (среднее значение). Статистические функции задаются в строке ГРУППОВАЯ ОПЕРАЦИЯ, эта строка появляется только после нажатия соответствующего значка сигма (математический знак суммы) 
Обратите внимание:
Для того, чтобы переименовать название столбца нужно выбрать этот столбец из выпадающего меню и в графе ПОЛЕ: изменить название на новое, далее ставим :(двоеточие), и старое название столбца остается
Например:
Есть таблица с названием столбца Сумма_заказа — нужно поменять название на Макс_сумма_заказа.
В графе ПОЛЕ нужно выбрать поле, которое нужно переименовать и впереди этого названия Напечатать новое, поставить двоеточие
Этот же запрос в режиме SQL:
В результате выполнения ЗАПРОСА №2 получим результат:
| Макс_сумма_заказа | Средняя_сумма_заказа | №_продавца |
|---|---|---|
| 70000 | 58333,3333333333 | 201 |
| 112000 | 95500 | 202 |
| 60000 | 55666,6666666667 | 203 |
Для каждого продавца видна максимальная сумма заказа и средняя сумма заказа
ЗАПРОС №3
Определить максимальную и минимальную сумму заказа, выполненную продавцом Козловым. В результате запроса вывести поля: №_продавца, ФИО, Макс_заказ, Мин_заказ. Вычисляемым полям присвоить собственные имена: Макс_заказ, Мин_заказ. Использовать таблицы Клиенты и Продавцы.
В режиме конструктора запрос:
В режиме SQL:
SELECT Продавцы.ФИО,
Max(Клиенты.Сумма_заказа) AS Макс_зак,
Min(Клиенты.Сумма_заказа) AS Мин_зак,
Продавцы.[№_продавца]
FROM Продавцы, Клиенты
WHERE Продавцы.[№_продавца] = Клиенты.[№_продавца]
GROUP BY Продавцы.ФИО, Продавцы.[№_продавца]
HAVING (((Продавцы.ФИО)=»Козлов»));
В результате выполнения запроса получим таблицу:
| ФИО | Макс_зак | Мин_зак | №_продавца |
|---|---|---|---|
| Козлов | 112000 | 64000 | 202 |
ЗАПРОС №4.
Выбрать сведения о продавцах, размер продаж которых превышает средний размер продаж за весь период. В результате запроса вывести поля: ФИО_прод, ФИО_клиента, сумма_заказа. Использовать таблицы Клиенты и Продавцы.
Сначала нужно определить значение среднего размера продаж по всем продавцам, а затем сравнивать значения поля Сумма_заказа для каждой продажи с вычисленным средним значением. Здесь необходимо выполнить вложение запросов. Внутренний запрос сгенерирует значение среднего размера продаж, которое будет тестироваться на предмет истинности условия, то есть сравниваться со значением поля Сумма_заказа каждой записи. Запросы такого титпа не совсем удобно выполнять средствами Конструктора запросов, но в принципе возможно. В строку Условие отбора поля Сумма_заказа необходимо ввести текст вложенного запроса.
В режиме SQL запрос :
SELECT Продавцы.ФИО, Клиенты.Сумма_заказа, Клиенты.ФИО AS ФИО_клиента
FROM Продавцы, Клиенты
WHERE (((Клиенты.Сумма_заказа)>(select avg(Сумма_заказа) from Клиенты)
and Продавцы.[№_продавца] = Клиенты.[№_продавца]));
Результат выполнения:
| ФИО | Сумма_заказа | ФИО_клиента |
|---|---|---|
| Козлов | 100000 | Умный |
| Козлов | 112000 | Борисов |
| Козлов | 106000 | Сергеева |
ЗАПРОС №5.
Заполнить поле Розн_цена таблицы Модели, рассчитав его значения по следующей формуле:
Розн_цена=Зав_цена*(1+[Торг_наценка]/100)
Это запрос на обновление.
Часто возникает необходимость изменить значение какого-либо поля в группе записей таблицы, отобранных на основании определенного критерия или во всех записях таблицы . Для того, чтобы не выводить заново новые значения во все записи, используется ЗАПРОС НА ОБНОВЛЕНИЕ. В этом случае в проект запроса добавляется строка ОБНОВИТЬ, которая предназначается для указания новых значений полей таблицы. В качестве таких могут выступать и вычисляемые выражения. После нажатия кнопки ЗАПУСК (восклицательный знак) ACCESS укажет в специальном диалоговом окне, сколько записей изменится в таблице и потребует подтвердить изменения.
Розн_цена относится к таблице Модели, создадим запрос на обновление, используя таблицу МОДЕЛИ.
В результате обновления столбец Розн_цена будет рассчитан по указанной формуле, и данные обновятся.
В режиме SQL текст запроса будет :
UPDATE Модели SET Модели.Розн_цена = [Зав_цена$]*(1+[Торг_наценка%]/100);