Лабораторная работа №3: запросы

ЛАБОРАТОРНАЯ РАБОТА №3: ЗАПРОСЫ

 

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

 

В Microsoft Accessесть несколько типов запросов:

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

2. Запросы с параметрами -это запрос,при выполнении отображающий всобственном диалоговом окне приглашение ввести данные, например условие для возвращения записей или значение, которое требуется вставить в поле. Можно разработать запрос, выводящий приглашение на ввод нескольких единиц данных, например двух дат. Затем Microsoft Access может вернуть все записи, приходящиеся на интервал времени между этими датами. Запросы с параметрами также удобно использовать

в качестве основы для форм, отчетов и страниц доступа к данным.

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

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

 

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

4.2. На обновление записи -вносит общие изменения в группу записейодной или нескольких таблиц. Запрос на обновление записей позволяет изменять данные в существующих таблицах.

 

4.3. На добавление записей -добавляет группу записей из одной илинескольких таблиц в конец одной или нескольких таблиц.

4.4. На создание таблицы -создает новую таблицу на основе всех иличасти данных из одной или нескольких таблиц. Запрос на создание таблицы полезен при создании таблицы для экспорта в другие базы данных Microsoft Access или при создании архивной таблицы, содержащей старые записи.

4.5. На объединение –позволяет объединить данных из двух таблиц саналогичными структурами.

Запросы можно создавать с помощью Мастера запросов (Query Wizard) или Конструктора запросов (Query Design).

 

Однотабличные запросы-выборки

 

Создание запроса, выполняющего отбор значений по шаблону

 

1. Откройте запрос «Товары_И_Цены» в режиме конструктора(Design).

 

2. Введите в строку Условия отбора (Criteria) для столбца

«НаименованиеТовара» выражение: *ая.

3. Выполните запрос  и просмотрите результаты.

 

4. Закройте запрос, не сохраняя его.

 

Итоги по всей таблице

 

Задание:в запросе«Стоимость_Товара_В_Заказе»подсчитать суммувсех проданных товаров.

 

1. Откройте запрос «Стоимость_Товара_В_Заказе» и оставьте результаты открытыми в режиме таблицы (Datasheet).

2. На вкладке Главная (Home) в группе Записи (Records) щелкните Итоги (Totals). В нижней части таблицы появится новая строка со словом Итог впервом столбце.

3. Щелкните ячейку в поле «Стоимость» с именем Итог. Обратите внимание на стрелку, которая появилась в ячейке.

 

4. Щелкните стрелку для просмотра доступных статистических функций.

 

5. Выберите Сумма (Sum). В поле будет отображаться сумма значений в столбце.

 

6. Сохраните запрос.

 

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

 

1. С помощью Мастера запросов (Query Wizard) создайте простой запрос на основании таблицы «Клиенты» (поля «Фамилия», «Имя») и запроса

 

«Стоимость_Товара_В_Заказе» (поля «КодЗаказа», «НаименованиеТовара», «Количество», «Скидка», «Стоимость»).

2. Выберите Итоговый отчет, нажмите кнопку Итоги и выберите вычисляемые значение «Стоимость» - необходимо вычислить сумму

(Sum).

3. Сохраните запрос под именем «Заказы_Клиентов» и выполните его.

 

Перекрестные запросы

 

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

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

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

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

 

1. На вкладке Создание (Create) выберите Мастер запросов(Query Wizard),

 

тип запроса – перекрестный (Crosstab Query Wizard).

2. Новый запрос создайте на основании запроса «Заказы_Клиентов»: для заголовка строк (Row Heading) – поле «Фамилия», для названий столбцов (Column Heading) – поле «НаименованиеТовара»; на пересечении строк и столбцов необходимо показать сумму стоимости (функция –

 

сумма(Sum)).

 

3. Задайте имя запроса: «Клиенты_Суммы_Товары».

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

 

5. В режиме таблицы (Datasheet) используйте функцию Итоги на вкладке Главная (Home) для подсчета общей суммы продаж товаров,а такжесумме продаж по каждой отдельной позиции товара (т.е. установив значение Сумма (Sum) для каждого поля в последней записи запроса

 

Итог).

 

6. Сохраните запрос.

 

Задание:необходимо получить информацию о работе каждого сотрудника

 

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

 

1. С помощью Мастера запросов(Query Wizard) создайте простой запрос на основании таблиц «Сотрудники» (поля «Фамилия», «Должность»), «Заказы» (поля «КодЗаказа», «ДатаЗаказа») и запроса

«Стоимость_Товара_В_Заказе» (поле «Стоимость»).

 

2. Сохраните запрос под именем «Работа_Сотрудников». Выполните запрос.

3. В режиме конструктора(Design) для поля «ДатаЗаказа» установите

 

Сортировку (Sort) - По возрастанию (Ascending).

4. Для поля «Фамилия» введите Условие отбора (Criteria) [Введите фамилию сотрудника:].

 

5. Выполните запрос (для разных сотрудников – Андерсон, Эмони, Каплан и т.п.). Убедитесь в корректности его выполнения.

6. В режиме таблицы (Datasheet) используйте функцию Итоги (Totals): для поля «КодЗаказа» выберите Количество значений (что будет подсчитывать количество заказов для выбранного сотрудника), для поля «Стоимость товара в заказе» - Сумма (Sum) (будет подсчитана общая сумма заказов в обработке выбранного сотрудника).

 

7. Для поля «ДатаЗаказа» введите условия отбора (Criteria):

 

Year ([ДатаЗаказа]) = Year (Now ()).

 

данная конструкция позволяет в качестве условия выбора установить выбор записей только за текущий год

8. Выполните запрос для разных сотрудников. Обратите внимание, что данные функции Итоги (Totals) должны отображаться для каждого сотрудника.

 

Запрос на добавление записи

 

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

 

Задание:с помощью запроса добавить запись о новом поставщике втаблицу «Поставщики».

 

1. Перейдите на вкладку Создание (Create) и нажмите на кнопку Мастер запросов (Query Wizard).

 

2. Выберите Простой запрос (Simple Query Wizard) – в качестве источника выберите таблицу «Поставщики» - добавьте в запрос все поля таблицы (кнопка >> добавляет сразу все поля) – имя запроса

 

«Данные_О_Поставщиках» - ОК.

 

3. В запросе внесите информацию о новом поставщике (произвольную), заполнив все поля запроса. После заполнения полей запроса данные автоматически должны передаться в таблицу «Поставщики».

 

4. Нажмите кнопку Обновить все.

 

5. Откройте таблицу «Поставщики» и убедитесь, что в ней появилась новая запись.

6. В запросе «Данные_О_Поставщиках» измените название организации для последней введенной записи и выполните запрос.

 

7. Откройте таблицу «Поставщики» и убедитесь, что название организации в последней записи также изменилось.

8. Сохраните изменения.

 

Запрос на обновление записи

 

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

 

Задание:тариф доставки всех транспортных компаний на все заказыувеличился в два раза. Необходимо ввести новый тариф (работая с таблицей «Доставка_Транспорт»).

 

1. На базе таблицы «Доставка_Транспорт» создайте конструктором новый запрос на обновление , включив в него поле «Тариф».

2. В ячейку Обновление (Update To) для поля «Тариф» введите следующее выражение: [Тариф]*2.

3. Выполните запрос, подтвердив обновление записей.

 

4. Сохраните запрос под именем «Удвоение_Тарифа».

5. Закройте запрос и посмотрите измененную таблицу.

6. Выполните запрос «Удвоение_Тарифа» еще раз, подтвердив все операции. Проверьте, что в таблице «Доставка_Транспорт» тарифы повысились еще раз.

 

Запрос на удаление записи

 

 

Задание:удалить из запроса все транспортные компании,тариф которыхпревышает 1000 руб.

 

1. Создайте новый запрос, внеся в него все поля таблицы

 

«Доставка_Транспорт».

 

2. В режиме конструктора в группе Тип запроса (Query Type) выберите кнопку Удаление (Delete).

3. В ячейку Условие отбора (Criteria) для поля «Тариф» введите выражение: >1000.

 

4. Выполните запрос; убедитесь, что в представленных данных нет тарифов больше 1 000 руб.

5. Сохраните запрос под именем «Условие_Тарифа».

 

 

Запросы в режиме SQL

 

Для извлечения данных из базы данных используется язык SQL (Structured Query Language — язык структурированных запросов). SQL —

 

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

 

Простой запрос на выборку

 

Задание:из таблицы«Транспортная_Компания»выбрать всетранспортные компании, кроме «Кенгуру».

 

1. Создайте запрос на выборку на основании таблицы

 

«Транспортная_Компания» (поле «Название компании»).

 

2. Выполните запрос.

 

3. Перейдите в режим SQL («Вид(View)» - SQL). Измените код таким образом:

 

SELECT [Транспортная_Компания].[НазваниеКомпании] FROM [Транспортная_Компания]

 

WHERE [Транспортная_Компания].[НазваниеКомпании]<>'Кенгуру';

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

 

5. Вернитесь в режим SQL. Программно измените запрос таким образом, чтобы он выводил не только название компании, но и адрес (имения полей «Улица», «Город», «Штат»). Выполните запрос.

 

6. В режиме SQL измените запрос таким образом, чтобы выводились только транспортные компании, расположенные в городе Денвер. Выполните запрос.

7. Сохраните запрос под именем «SQL _Выборка».

 

8. Сделайте сортировку выбранных записей по названию компании в алфавитном порядке:

ORDER BY НазваниеКомпании;

9. Выполните запрос. Сохраните изменения.

 

Задание:удалить таблицу«Доставка_Транспорт»

 

1. В конструкторе запросов выберите режим SQL.

 

2. Введите следующий код:

DROP TABLE [Доставка_Транспорт];

 

3. Выполните запрос, убедитесь, что выбранная таблица удалена.

 

4. Сохраните запрос под именем «Удаление_Таблицы».

 

Запрос на объединение

 

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

 

Задание:создать запрос,возвращающий список всех контактов компании(клиенты, сотрудники) с номерами телефонов, отсортированный в алфавитном порядке.

 

1. В конструкторе запросов выберите тип запроса – объединение  (автоматически работа будет происходить в режиме SQL).

2. Введите следующий код:

 

SELECT Фамилия, Имя, НомерТелефона FROM Клиенты UNION SELECT Фамилия, Имя, Телефон FROM Сотрудники;

 

3. Для объединения двух полей «Фамилия» и «Имя» в одно – «ФамилияИмя» измените код следующим образом:

 

SELECT Фамилия&" "& Имя AS ФамилияИмя, НомерТелефона FROM Клиенты

UNION

 

SELECT Фамилия&" "&Имя AS ФамилияИмя, Телефон FROM Сотрудники;

4. Сохраните запрос под именем «Список_Телефонов».

 

 

Рис. 2.1. Результат выполнения запроса «Список_Телефонов».

 

ЛАБОРАТОРНАЯ РАБОТА №3: ЗАПРОСЫ

 

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

 

В Microsoft Accessесть несколько типов запросов:

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

2. Запросы с параметрами -это запрос,при выполнении отображающий всобственном диалоговом окне приглашение ввести данные, например условие для возвращения записей или значение, которое требуется вставить в поле. Можно разработать запрос, выводящий приглашение на ввод нескольких единиц данных, например двух дат. Затем Microsoft Access может вернуть все записи, приходящиеся на интервал времени между этими датами. Запросы с параметрами также удобно использовать

в качестве основы для форм, отчетов и страниц доступа к данным.

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

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

 

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

4.2. На обновление записи -вносит общие изменения в группу записейодной или нескольких таблиц. Запрос на обновление записей позволяет изменять данные в существующих таблицах.

 

4.3. На добавление записей -добавляет группу записей из одной илинескольких таблиц в конец одной или нескольких таблиц.

4.4. На создание таблицы -создает новую таблицу на основе всех иличасти данных из одной или нескольких таблиц. Запрос на создание таблицы полезен при создании таблицы для экспорта в другие базы данных Microsoft Access или при создании архивной таблицы, содержащей старые записи.

4.5. На объединение –позволяет объединить данных из двух таблиц саналогичными структурами.

Запросы можно создавать с помощью Мастера запросов (Query Wizard) или Конструктора запросов (Query Design).