Примечание: если имя поля содержит знак пробела, то его следует писать в [] квадратных скобках

ЛАБОРАТОРНАЯ РАБОТА №3

СОЗДАНИЕ ЗАПРОСОВ

Цель работы: научиться создавать различные типы запросов СУБД ACCESS и ANSI SQL.

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

В ACCESS запросы можно создавать двумя способами: QBE-запросы (Query By Example -запрос по образцу) и SQL-запросы (Structured Query Language-структурированный язык запросов), при создании которых применяются операторы и функции языка SQL.

Ход выполнения работы

1. Скопировать файл primer_lab3_sp.mdb в свой каталог и выполнить все задания по методическим указаниям.

2.Затем выполнить индивидуальное задание (№2 и №3 в файле Variants.doc)

Для защиты работы должны:

1. Уметь создавать 6 типов запросов в Access.

2. Объяснить назначение основных операторов SQL (Select, From, Where, Group By, Having, Order By, Into, As). Файл sql.rtf.

ЗАПРОСЫ НА ВЫБОРКУ

Запрос 1. Выбрать сведения о клиентахпоставщиках из Москвы, , проживающих в Бресте и заказавших автомобиль марки Citroenкоторые привозили мониторы в октябре 2004после 01.05.98. В результат запроса вывести поля: ФИОИмяП, Телефон, Город, Модель, Адрес, Дата, Товар заказа. Использовать таблицы Клиенты Товар, Поставка, Поставщики Модели.

Сначала построим QBE-запрос.

Для этого в окнеБазы Данных активизируйте вкладку ЗАПРОСЫ и выполните щелчок на кнопке СОЗДАТЬ. На экране появится диалоговое окно НОВЫЙ ЗАПРОС, в котором необходимо выбрать способ создания запроса (см. рис.1).

Выберите способКонструктор и нажмите кнопку ОК. В результате появится пустое окноКонструктора запросов и диалоговое окно Добавление таблицы. Маркируйте таблицуТовар Клиенты и выполните щелчок на кнопке ДОБАВИТЬ. Затем маркируйте таблицуПоставкаиПоставщик Модели и снова выполните щелчок на кнопке ДОБАВИТЬ. Закроите окно, щелкнув на кнопку ЗАКРЫТЬ. Имена таблиц появятся в окне проектирования запроса.

Окно конструктора запроса разделено на две части. В верхней части окна находятся таблицы со списками полей. Между полямиКодТд модели обеих таблицТовариПоставкаи полями КодП таблиц ПоставщикиПоставкапроведеныа линия, определяющая связиь между таблицами (см рис.2). Нижняя часть – бланк запроса

Разработка запроса выполняется в несколько этапов;

• Добавление полей в запрос.

• Установка критериев отбора записей.

• Сортировка записей.

Добавить нужные поля в бланк запроса можно или путем перетаскивания их имен из списка, находящегося в верхней части окна конструктора, в строку Поле,.Еще один способ -или двойнымой щелчок на имени поля. Из таблицыПоставщик Клиенты выберите одним из предложенных способов поля ФИОИмяП, АдресГород, Дата заказаТелефон, из таблицыПоставка Модели - поле МодельДата, из таблицы. Товар –поле НaзваниеТ. Теперь необходимо ввести критерии отбора. Формируемый в данном примере запрос должен отбирать данные о клиентах поставщиках из Москвы которые привозили мониторы в октябре 2004из Бреста, заказавших после 01.05.98 г. автомобиль марки Citroen. Поэтому для перечисленных ниже полей установим в строкеУсловие отбора такие критерии:

Адрес Город МоскваБрест

Модель НазваниеТ мониторCitroen

Дата заказа >=#01.10.04# And <#01.11.04#>01.05.98

После ввода каждого из критериев следует нажимать клавишу <ENTER>, вследствие чего ACCESS проверит его синтаксис и нормализует запись в соответствии с правилами записи критериев. Записи в генерируемом при выполнении запроса динамическом наборе данных могут быть отсортированы по алфавиту, по возрастанию или по убыванию. Отсортируем фамилии клиентовпоставщиков в алфавитном порядке. Для этого в строкеСортировкаполя ФИО ИмяП выполните щелчок и в появившемся списке выберите способ сортировки по возрастанию. Окно конструктора запроса представлено на рис.39.

Готовый запрос выполняется после щелчка по кнопке c изображением восклицательного знака на панели инструменте Конструктора запросов или командой ЗАПУСК меню ЗАПРОС. Access отобразит на экране результирующий набор записей, которые отобраны из таблицы Клиенты Поставщик в соответствии с заданными критериями (см. рис10рис 4).

Запросы, созданные в окне Конструктора запроса, ACCESS автоматически преобразует в SQL-запросы. Чтобы просмотреть инструкцию запроса на SQL в меню ВИД выберите команду РЕЖИМ SQL. На экране появится окно SQL.(рис.115)

SQL-запрос можно отредактировать в окне SQL. После закрытия окна изменения автоматически отображаются и в QBE-запросе.

В SQL-окно можно непосредственно вводить команды, составляющие SQL-запрос. Запрос можно выполнить, вызвав из меню ЗАПРОС команду ЗАПУСК или активизировав кнопку с восклицательным знаком на панели инструментов.

Запрос1, написанный на ANSI SQL, выглядит следующим образом:

SELECT ФИО, Модель, Адрес, [Дата заказа]

FROMКлиенты INNERJOINМодели

ONКлиенты.Код_модели= Модели.Код_модели

WHERE ((Модель="Citroen")AND (Адрес=-"Брест")AND ([Дата заказа]>5/01/98)

AND Клиенты.[Код модели]=Модели.[Код модели])

ORDER BY ФИОSELECTИмяП, Город, Телефон, Дата, НазваниеТ

FROM Товар INNER JOIN (Поставщик INNER JOIN Поставка ON Поставщик.КодП = Поставка.КодП) ON Товар.КодТ = Поставка.КодТ

WHERE ((Город="Москва") AND (Дата>=#10/1/2004# ANDДата<#11/1/2004#) AND (НазваниеТ="монитор"))

ORDER BY ИмяП

Примечание: если имя поля содержит знак пробела, то его следует писать в [] квадратных скобках.

Запрос можно использовать для выполнения расчетов и подведения итогов, обобщив данные из исходных таблиц. Для этих целей в ACCESS предусмотрены статистические функции SQL:Sum, Avg,Min, Max, Count и некоторые другие.

Запрос 2. Определить , сколько было поставок каждого товара, максимальную, минимальную и среднюю цену поставки каждого товарасумму заказа, выполненную каждым продавцом. В результат запроса вывести поля: № продавцаНазваниеТ, и 43 вычисляемыхм полям, которым присвоить собственныесоответствующие имена: Число поставок, Макс цена сумма заказа, Мин цена, Средняя цена сумма заказа. Использовать таблицы Товар Клиенты и ПоставкаПродавцы.

Это так называемый запрос с группировкой. После вызова окна конструктора запросов и выполнения всех описанных выше действий необходимо в соответствующих полях задать статистические функцииSum Count, Max, Min и Avg. Статистические функции задают в строкеГрупповая операция окна Конструктора запросов, которая появляется после нажатия кнопки с греческой литерой сигма, расположенной на панели инструментов, или после вызова команды ГРУППОВЫЕ ОПЕРАЦИИ меню ВИД., НовыеСобственные имена полей { Число поставок, Макс цена, Мин цена, Средняя цена Макс сумма заказа и Средняя _сумма заказа} нужно ввести в строкуПоле: конструктора запросов (см. рис.126).

Результат запроса представляет собой следующую таблицу:

Этот же запрос на ANSI SQL:

SELECT Мах([Сумма заказа]) AS Макс_сумма_заказа,

Аvg([Сумма заказа]) AS Средняя_сумма_заказа,

[N продавца]

FROM Продавцы INNER JOIN Клиенты ON Продавцы.[N продавца] = Клиенты.[N продавца]

SELECT Товар.НазваниеТ, Count(Поставка.[№ Записи]) AS [Число поставок],

Max(Поставка.Цена) AS [Макс цена],

Min(Поставка.Цена) AS [Мин цена],

Avg(Поставка.Цена) AS [Средняя цена]

FROM Товар INNER JOIN Поставка ON Товар.КодТ = Поставка.КодТ

GROUP BY Товар.НазваниеТ;

GROUP BY Продавцы.[N продавца];

Запрос 3:Определить количество поставок из Санкт-Петербурга. В результат запроса вывести поля: Город, Число поставок. Использовать таблицы Поставка и Поставщик.

Определить максимальную и минимальную сумму заказа, выполненную продавцом Козловым. В результат запроса вывести поля: №_продавца, ФИО, Макс_зак, Мин_зак. Вычисляемым полям присвоить собственные имена: Макс зак-. Мин зак. Использовать таблицыКлиенты и Продавцы.

Действия по конструированию запроса аналогичны описанным выше, только в строкуУсловие отбора Конструктора запросов необходимо ввести критерий отбора по полю Город ФИО-КозловСПБ (рис 8).

Результат выполнения запроса представлен в таблице на рис. 15.9.

Этот же запрос на ANSI SQL:

SELECTПоставщик.Город, Count(Поставка.[№ Записи]) AS [Число поставок]

FROMПоставщикINNER JOINПоставкаONПоставщик.КодП=Поставка.КодП

GROUP BYПоставщик.Город

HAVINGПоставщик.Город="Спб";

ЗАПРОСЫ НА ОБНОВЛЕНИЕ

Часто возникает необходимость изменить значения какого-либо поля в группе записей таблицы, отобранных на основании определенного критерия или во всех записях таблицы (например, в связи с инфляцией повысить заводскую цену каждой модели автомобиля на определенный процент или рассчитать значение какого-либо поля по уже внесенным в таблицу данным). Для того, чтобы не вводить заново новые значения во все записи, используется так называемый ЗАПРОС НА ОБНОВЛЕНИЕ. Команда ОБНОВЛЕНИЕ вызывается из меню ЗАПРОС во время проектирования QBE-запроса. В этом случае в проект запроса добавляется строка ОБНОВИТЬ, которая предназначается для указания новых значений полей таблицы. В качестве таковых могут выступать и вычисляемые выражения. После нажатия кнопки ЗАПУСК (восклицательный знак) ACCESS укажет в специальном диалоговом окне, сколько записей изменится в таблице, и потребует подтвердить изменения. Рассмотрим на примере.

Запрос 4: Заполнить поле Стоимость таблицыПоставка, рассчитав его значения по следующей формуле:

Стоимость = Цена*Количество

Для реализации этого запроса загружаемКонструктор запросов, затем в меню ЗАПРОС вызываем команду ОБНОВЛЕНИЕ, добавляем таблицу Поставка, выбираем поле Стоимость и в появившуюся строку ОБНОВИТЬ Конструктора запросов вводим выражение:

[Цена]*[Количество]

Чтобы расчет выполнялся только для тех записей, для которых стоимость ещё не вычислена, в строке Условие отбора пишем выражение Is Null(рис. 10).Иначе пересчет выполнялся бы для всех записей в таблице и при большом их количестве это занимало бы лишнее время.

После запуска запроса на выполнение появится диалоговое окно (рис 11), в котором нужно нажать кнопку «Да».

После этого откройте таблицу Поставка и убедитесь, что поле Стоимость оказалось заполнено рассчитанными значениями

Текст этого запроса на ANSI SQL выглядит следующим образом:

UPDATE ПоставкаSET Поставка.Стоимость = [Цена]*[Количество]

WHERE (((Поставка.Стоимость) Is Null));