Лабораторная работа №3 "СОЗДАНИЕ ЗАПРОСОВ СРЕДСТВАМИ ЯЗЫКА SQL В MICROSOFT ACCESS "

Лабораторная работа №3

Создание запросов средствами языка SQL

В microsoft access

Цель работы: цель выполнения лабораторной работы заключается в изучении основ языка SQL, изучении компонент языка SQL, получении навыков по созданию простейших SQL-запросов с использованием операторов, созданию запросов в режиме SQL с использованием агрегатных функций.

ОСНОВНЫЕ ПОНЯТИЯ

В середине 1980-х годов Национальный институт стандартизации США начал разрабатывать первый стандарт языка SQL, который был опубликован в 1986 г. Дальнейшие разработки были отражены в следующих версиях стандарта SQL (1989г., 1992г., 1999г., 2003г.).

SQL содержит следующие компоненты [4]:

• Средства администрирования данных;

• Средства управления транзакциями;

• Операторы определения данных DDL (Data Definition Language);

• Операторы манипулирования данными DML (Data Manipulation Language).

Администрирование данных предполагает создание БД, предоставление прав доступа, назначение и изменение пароля и т.д.

Транзакция – это неделимая, с точки зрения воздействия на СУБД, последовательность операций манипулирования данными [4]. Транзакция либо выполняется целиком и переводит БД из одного целостного состояния в другое целостное состояние, либо, если по каким-либо причинам одно из действий транзакции невыполнимо или произошло нарушение работы системы, БД возвращается в исходное состояние, предшествовавшее началу транзакции (происходит откат транзакции).

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

Операторы манипулирования данными позволяют добавлять, удалять или обновлять записи.

Язык запросов позволяет организовать отбор данных по указанным пользователем критериям.

Манипулирование базами данных предполагает использование операторов UPDATE (Обновление), INSERT (Добавление) и DELETE (Удаление). Эти операторы используются для создания запросов различного типа.

Инструкция GROUP BY позволяет группировать данные. Эта команда группирует блоки данных, объединяя все записи, содержащие в полях идентичные значения, в один отдельный элемент выходного набора. При этом повторяющиеся значения будут присутствовать в выходном наборе только один раз.

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

Параметр ORDER BY позволяет отсортировать выходные данные в заданной последовательности. Если сортировка производится по нескольким полям, то они перечисляются через запятую. Способ сортировки определяется параметром, указанным после названия поля, по которому выполняется сортировка. Если способ сортировки не указан, то выполняется сортировка по возрастанию; если следом за названием поля записан параметр DESC, то выполняется сортировка по убыванию. Сортировка может выполняться для каждого из полей, приведенных в списке параметра ORDER BY.ORDER BY не является обязательным параметром. Если этот параметр не задан, то сортировка данных не происходит, и они выводятся в той последовательности, в которой они записаны в исходной таблице.

При формировании набора данных по запросам на выборку требуется производить определенные вы­числения или определенные операции по непосредственной об­работке отбираемых данных. В реляционных СУБД такие воз­можности предоставляются агрегатными функциями SQL.

В большин­стве диалектов языка SQL в состав инструкции SELECT допус­кается включение агрегатных функций SQL, которые осу­ществляют те или иные групповые вычислительные операции над отбираемыми записями Агрегатные функции позволяют получить ряд обобщающих статистических характеристик в рамках SQL – запроса, формы или отчета. В MS Access имеются следующие агрегатные функции:

• Avg – определение среднего значения;

• Min, Max – определение минимума, максимума;

• Count – подсчет количества значений;

• StDev, StDevP – определение стандартного отклонения генеральной совокупности или ее выборки, представленной в виде набора значений содержащихся в указанном поле в запросе:

StDevP - вычисляет генеральную совокупность;

StDev – осуществляет выборку генеральной совокупности.

• Var, VarP – определение предполагаемого значения дисперсии генеральной совокупности или ее выборки, представленной в виде набора значений, содержащихся в указанном поле запроса:

Var – осуществляет выборку генеральной совокупности;

VarP - вычисляет генеральную совокупность.

• First, Last – определение первого и последнего значений;

• Sum – вычисляет арифметическую сумму всех выбранных значений дан­ного поля.

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

Если необходимо подсчитать количество записей в столбце таблицы при помощи функции Count, то при наличии пустых значений в данном столбце, будут подсчитаны только строки содержащие записи, к которым применимо определенное в Count условие. Если же при использовании функции Count условие не определено (например, необходимо подсчитать общее количество записей таблицы), то она подсчитает общее количество строк в таблице.

Рассмотрим механизм определения среднего значения при помощи функции Avg. Среднее значение множества чисел равно сумме чисел, входящих в множество, деленной на число элементов множества. Но если некоторые элементы пусты, деление на количество всех элементов множества приведет к неправильному результату. Функция Avg вычисляет среднее значение всех известных значений мно­жества элементов, то есть эта функция подсчитывает сумму известных значений и делит ее на количество этих известных значений не учитывая NULL-значения.

Создание запросов на языке SQL

Для того чтобы создать запрос на языке SQL, необходимо в режиме конструктора запросов выбрать пункт Режим SQL на вкладке Вид (рис. 1). В раскрывшемся окне необходимо ввести условие выборки. Например, запрос, подсчитывающий средний балл студентов, включенных в базу данных (БД) «Студенты» выглядит так, как показано на рис. 2.

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

В общем виде запрос выглядит так:

SELECT ALL -, DISTINCT-, DISTINCTROW- предикаты (условия), которые указывают записи, выбранные для запросов

Список._полей

FROM [Имена таблиц]

WHERE Критерии поиска

IN Имя базы данных

GROUP BY Список полей

HAVING Критерии поиска

ORDER BY Список полей

Параметр WHERE задает условия, которым должны удовлетворять извлекаемые записи.

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

Рис. 1. Переход в режим SQL

Рис. 2. Запрос в режиме SQL

Таким образом, SELECT – ключевое слово, которое сообщает СУБД, что эта команда – запрос на выборку. Все запросы на выборку начинаются этим словом с последующим пробелом. За этим словом может следовать способ выборки – с удалением дубликатов (DISTINCT, DISTINCTROW) или без удаления (ALL, подразумевается по умолчанию).

Данный запрос можно записать в следующем виде:

SELECT DISTINCT Список полей

FROM [Имя таблицы];

Для запуска запроса необходимо нажать кнопку Выполнить на панели инструментов как показано на рис. 3.

Рис. 3. Выполнение запроса

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

Для задания в запросе условия отбора используется ключевое слово WHERE (где), принимающее значение истина или ложь для значений полей и строк таблиц, к которым обращается оператор SELECT. Параметр WHERE задает условия, которым должны удовлетворять извлекаемые записи. Таким образом, параметр WHERE – это ключевое слово, за которым следует предикат – условие, налагаемое на запись в таблице, которому она должна удовлетворять, чтобы попасть в выборку.

В условии выборки могут использоваться следующие предикаты:

- предикаты сравнения =, ,, =,

- предикат BETWEEN – принимает значение между А и В:

/NOT BETWEEN не принимает значение между а1 и а2;

имя BETWEEN А AND В или имя NOT BETWEEN А AND В;

- предикат IN – входит в множество/ не входит:

IN (Подзапрос) (список значений);

NOT IN (Подзапрос) (список значений);

- предикат LIKE – включает (подобен):

LIKE;

NOT LIKE – не подобен.

ПОРЯДОК ВЫПОЛНЕНИЯ РАБОТЫ

1. Ознакомится с теоретическим материалом.

2. Для выполнения работы используйте ранее созданную базу данных Access. Например, в рамках дисциплины «Базы данных» и дисциплины «Проектирование информационных систем (Лабораторная работа №1)».

3. Если выбранная база данных не была заполнена, необходимо внести в нее данные.

4. Создать запрос на выборку в режиме SQL всех значений нескольких столбцов таблицы.

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

6. Создать запрос на выборку нескольких полей из таблицы при помощи параметра WHERE.

7. Создать запрос на выборку данных из таблицы при помощи параметра WHERE и предиката BETWEEN А AND В.

8. Создать запрос на выборку данных из таблицы при помощи параметра WHERE и предиката LIKE.

9. Сгруппировать данные таблицы при помощи инструкции GROUP BY.

10. Подсчитать количество строк таблицы.

11. Создать запрос, позволяющий получить информацию о первой записи обрабатываемой таблицы.

12. Создать запрос, позволяющий получить информацию о последней записи обрабатываемой таблицы.

13. Создать запрос, позволяющий получить информацию о последней записи обрабатываемой таблицы.

14. Составить отчет по работе

Пример выполнения работы

Рассмотрим создание запросов при помощи SQL – инструкций для базы данных Микропроцессорные устройства систем управления.

Схема рассматриваемой базы данных представлена на рис. 4.

Рис.4. Схема базы данных

1. Создать запрос на выборку в режиме SQL всех значений 2 полей таблицы.

Создадим запрос на выборку полей БИС и Назначение БИС из таблицы Микросхемы - отберем все значения полей. Запрос в режиме SQL представлен на рис. 5.

Рис. 5. Запрос в режиме SQL

Результат запроса представлен на рис. 6.

Рис. 6. Результат задания 1

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

Создадим запрос на выборку поля БИС из таблицы Микросхемы, поля Назначение выводов из таблицы Схема БИС. Запрос в режиме SQL представлен на рис. 7. Результат выполнения запроса представлен на рис. 8.

Рис.7. Запрос на выборку в режиме SQL

Рис. 8. Результат выполнения задания

3. Создать запрос на выборку нескольких полей из таблицы при помощи параметра WHERE.

Создадим запрос на выборку БИС, являющейся системным контроллером, и ее условного обозначения из таблицы Микросхемы Запрос в режиме SQL представлен на рис. 9. Результат выполнения запроса представлен на рис. 10.

Рис. 9. Запрос на выборку в режиме SQL

Рис. 10. Результат выполнения запроса

4. Создать запрос на выборку данных из таблицы при помощи параметра WHERE и предиката BETWEEN А AND В.

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

Запрос в режиме SQL представлен на рис.11. Результат выполнения запроса представлен на рис. 12.

Рис. 11. Запрос в режиме SQL

Рис. 12. Результат выполнения запроса

5. Создать запрос на выборку данных из таблицы при помощи параметра WHERE и предиката LIKE.

Создадим запрос на выборку БИС микросхемы, которая является центральным процессором. Выборка производится из таблицы Микросхемы.

Запрос в режиме SQL представлен на рис. 13. Результат выполнения запроса представлен на рис.14.

Рис. 13. Запрос в режиме SQL

Рис. 14. Результат выполнения запроса

6. Сгруппировать данные таблицы при помощи инструкции GROUP BY.

Создадим запрос на выборку поля Назначение из таблицы Микросхемы, сгруппировав по полю Назначение.

Запрос в режиме SQL представлен на рис.15. Результат выполнения запроса представлен на рис. 16.

Рис. 15. Запрос в режиме SQL

Рис.16. Результат выполнения запроса

7. Подсчитать количество строк в таблице.

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

Запрос в режиме SQL представлен на рис. 17.

Рис. 17. Запрос в режиме SQL

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

Рис. 18. Результат выполнения задания

8. Создать запрос, позволяющий получить информацию о первой записи обрабатываемой таблицы.

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

Запрос в режиме SQL представлен на рис. 19.

Рис. 19. Запрос в режиме SQL

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

Рис. 20. Результат выполнения задания

9. Создать запрос, позволяющий получить информацию о последней записи обрабатываемой таблицы.

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

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

Рис. 22. Результат выполнения задания

10. Создать запрос, позволяющий получить информацию о последней записи обрабатываемой таблицы.

Создадим запрос в режиме SQL, позволяющий определить серию микропроцессорного комплекта, запись о котором является последней в таблице «Микропроцессорный комплект».

Запрос в режиме SQL представлен на рис.23.

Рис. 23. Запрос в режиме SQL

Результат выполнения запроса представлен на рисунке 24.

Рис. 24. Результат выполнения задания

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

Рассчитаем сумму данных поля «В наличие» из таблицы «Микропроцессорный комплект».

Запрос в режиме SQL представлен на рис. 25.

Рис. 25. Запрос в режиме SQL

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

Рис. 26. Результат выполнения задания

СОДЕРЖАНИЕ И ОФОРМЛЕНИЕ ОТЧЕТА

ПО ЛАБОРАТОРНОЙ РАБОТЕ

Отчет оформляется в текстовом редакторе MS Word в формате А4 и должен содержать:

1. Титульный лист.

2. Цель работы.

3. Запросы в режиме SQL и результаты выполнения запросов в режиме таблиц.

ВОПРОСЫ для самоконтроля

1. Какие компоненты содержит SQL – инструкции?

2. Показать, как осуществляется переход в режим SQL – инструкции.

3. Что такое предикаты? Какие предикаты используются при создании запросов в режиме SQL?

4. Что предполагает администрирование данных?

5. Транзакция. Дать определение.

6. Что предполагает Манипулирование базами данных?

7. Какие операторы используются при манипулировании базами данных?

8. Каково назначение инструкции GROUP BY?

9. Назначение параметра HAVING при использовании инструкции GROUP BY.

10. Каково назначение инструкции ORDER BY?

11. Перечислите агрегатные функции, позволяющие получить статистические характеристики в рамках SQL – запроса.

12. Какие операции позволяют осуществить агрегатные функции с данными таблиц?

13. При помощи какой агрегатной функции можно подсчитать среднее значение величины?

14. Какая функция позволяет определить количество записей в таблице?

15. При помощи какой функции можно подсчитать минимальное и максимальное число из множества значений в заданном поле запроса?

16. Как влияет наличие пустых значений на статистическую обработку данных при помощи функции Count?

17. Как влияет наличие пустых значений на статистическую обработку данных при помощи функции Avg?

Содержание отчета:

1. Титульный лист.

2. Скриншоты всех этапов создания в соответствии с примером.

9