Файл: Лабораторные работы по курсу Базы данных Введение.pdf

Пример 2.1:
Рассмотрим базу данных, которая моделирует сдачу сессии в некотором учебном заведении. Пусть она состоит из трех отношений R1, R2, R3, представленных таблицами R1, R2, R3 соответственно.
Примечание: В дальнейшем эти отношения будут использоваться для всех примеров
в данной лабораторной работе.
R1 = (ФИО, Дисциплина, Оценка); R2 = (ФИО, Группа); R3 = (Группа,
Дисциплина)
R1
ФИО
Дисциплина
Оценка
Петров Ф.И.
Базы данных
5
Сидоров К.А.
Базы данных
4
Миронов А.В.
Базы данных
2
Петров Ф.И.
Моделирование
5
Сидоров К.А.
Моделирование
4
Миронов А.В.
Моделирование
Null
Трофимов П.А.
Сети ЭВМ
4
Иванова Е.А.
Сети ЭВМ
5
Уткина Н.В.
Сети ЭВМ
5
R2
ФИО
Группа
Петров Ф.И.
АИ21
Сидоров К.А.
АИ21
Миронов А.В.
АИ21
Трофимов П.А.
АИ22
Иванова Е.А.
АИ22
Уткина Н.В.
АИ22
R3
Группа
Дисциплина
АИ21
Базы данных
АИ21
Моделирование
АИ22
Сети ЭВМ
Тогда запрос всех студентов и дисциплин, по которым у них есть оценки будет выглядеть следующим образом:
SELECT ФИО, Дисциплина FROM R1;
В разделе WHERE задаются условия отбора строк результата или условия соединения кортежей исходных таблиц, подобно операции условного соединения в реляционной алгебре.
В выражении условий раздела WHERE могут быть использованы следующие предикаты:
Предикаты сравнения (=, <>, >, >=, <, <=), которые имеют традиционный смысл.
Предикат Between A and B – принимает значения между А и В.
Предикат истинен, когда сравниваемое значение попадает в заданный диапазон, включая границы диапазона. Одновременно в стандарте задан и противоположный предикат Not Between A and B, который истинен тогда,

когда сравниваемое значение не попадает в заданный интервал, включая его границы.
Предикат вхождения в множество IN (множество) истинен тогда, когда сравниваемое значение входит в множество заданных значений. При этом множество значений может быть задано простым перечислением или встроенным подзапросом. Одновременно существует противоположный предикат NOT IN (множество), который истинен тогда, когда сравниваемое значение не входит в заданное множество.
Предикаты сравнения с образцом LIKE и NOT LIKE. Предикат LIKE требует задания шаблона, с которым сравнивается заданное значение, предикат истинен, если сравниваемое значение соответствует шаблону, и ложен в противном случае. Предикат NOT LIKE имеет противоположный смысл. Шаблон может содержать % (* для Access) для обозначения любого числа любых символов; _ (? для Access) для обозначения любого одного символа.
Предикат сравнения с неопределенным значением IS NULL. Для выявления равенства значения некоторого атрибута неопределенному значению применяют специальные стандартные предикаты: <имя атрибута>
IS NULL и <имя атрибута> IS NOT NULL
Предикат существования EXIST и не существования NOT EXIST.
Применяется во вложенных запросах для определения непустого или пустого множества, являющегося результатом выборки.
В условиях поиска могут быть использованы все рассмотренные предикаты.
Пример 2.2:
Например, можно выбрать из отношения R3 только те дисциплины, которые были у группы АИ21:
SELECT Дисциплина FROM R3 WHERE Группа Like ‘АИ21’;
Для таблиц и полей можно задавать псевдонимы (alias). Для этого необходимо использовать предлог AS. Например, Select [Цена за единицу] *
[Количество] as [Стоимость покупки] from Продажа; - здесь определяется псевдоним для вычисляемого поля (операция умножение).
В разделе GROUP BY задается список полей группировки. GROUP BY
группирует записи данных и объединяет в одну запись все записи данных, которые содержат идентичные значения в указанном поле (или полях).
WHERE определяет, какие записи должны участвовать в группировании, т.е. фильтрует до группирования.
Обратите внимание, что использование Group By отличается от использования Distinct. Во втором случае будут отброшены кортежи, которые в текущем представлении совпадают по всем полям (из совпадающих записей остается только один кортеж). Операция группировки приводит исходное отношение к виду, когда ко всем полям, запрошенным на отображение и не указанным в выражении группировки, применяются агрегатные функции (если они не определены, то запрос не выполнится).

Пример 2.3:
Для того чтобы почувствовать разницу между использованием ключевого слова DISTINCT и группировкой с помощью GROUP BY попробуйте поочередно выполнить следующие запросы к отношению R2:
SELECT DISTINCT ФИО, Группа FROM R2;
SELECT Группа FROM R2;
SELECT DISTINCT Группа FROM R2;
SELECT max(Поле1), Поле2 FROM R2 Group by Поле2;
SELECT Поле2 FROM R2 Group by Поле2;
В разделе HAVING задаются предикаты-условия, накладываемые на каждую группу. HAVING используется для фильтрации записей, полученных в результате группировки. WHERE определяет, какие записи должны участвовать в группировании, т.е. фильтрует до группирования.
HAVING определяет, какие из получившихся в результате группировки записей будут включены в результирующую выборку, т.е. фильтрует записи после группирования.
Пример 2.4:
Для того чтобы наложить фильтр на поле в запрос, данные в котором уже были сгруппированы, необходимо использовать HAVING:
SELECT R2. Группа FROM R2
GROUP BY R2. Группа
HAVING R2. Группа =’АИ21’;
В части ORDER BY задается список полей упорядочения результата, то есть список полей, который определяет порядок сортировки в результирующем отношении. Например, если первым полем списка будет указан Шифр группы, а вторым Фамилия, то в результирующем отношении записи сначала будут расположены в порядке возрастания шифра группы, а затем в рамках одной группы записи будут отсортированы по фамилии в алфавитном порядке.
Применение агрегатных функций
В SQL добавлены дополнительные функции, которые позволяют вычислять обобщенные групповые значения. Для применения агрегатных функций предполагается предварительная операция группировки. При группировке все множество кортежей отношения разбивается на группы, в которых объединяются кортежи, имеющие одинаковые значения атрибутов, которые заданы в списке группировки.
Пример 2.5:
Для того, чтобы посчитать, сколько студентов обучается в группе АИ22 необходимо выполнить запрос:
SELECT COUNT(*), Группа FROM R2
WHERE Группа Like ‘АИ21’;
Некоторые агрегатные функции описаны в таблице 1.

Таблица 1 - Агрегатные функции
Функция Результат
COUNT
Количество строк или непустых значений полей, которые выбрал запрос
SUM
Сумма всех выбранных значений данного поля
AVG
Среднеарифметическое значение всех выбранных значений данного поля
MIN
Наименьшее из всех выбранных значений данного поля
MAX
Наибольшее из всех выбранных значений данного поля
Агрегатные функции применяются подобно именам полей в операторе
SELECT, но они используют имя поля как аргумент. С функциями SUM И
AVG могут использоваться только числовые поля. С функциями COUNT,
MAX, MIN могут использоваться как числовые, так и символьные поля. При использовании с символьными полями MAX и MIN будут транслировать их в эквивалент ASCII кода и обрабатывать в алфавитном порядке.
Применение объединения (JOIN)
Стандарт SQL2 расширил понятие условного объединения. В стандарте
SQL1 при объединении отношений использовались только условия, задаваемые в части WHERE оператора SELECT, и в этом случае в результирующее отношение попадали только сцепленные по заданным условиям кортежи исходных отношений, для которых эти условия были определены и истинны. Однако в действительности часто необходимо объединять таблицы таким образом, чтобы в результат попали все строки из первой таблицы, а вместо тех строк второй таблицы, для которых не выполнено условие соединения, в результат попадали бы неопределенные значения. Или наоборот, включаются все строки из правой (второй) таблицы, а отсутствующие части строк из первой таблицы дополняются неопределенными значениями. Такие объединения были названы внешними в противоположность объединениям, определенным стандартом SQL1, которые стали называться внутренними.
Внутреннее объединение (INNER JOIN) возвращает записи из двух таблиц, если значение первичного ключа первой таблицы соответствует значению внешнего ключа второй таблицы, связанной с первой.
Формат описания:
<выражение естественного объединения> -
<имя таблицы1> NATURAL { INNER | FULL [OUTER]
LEFT [OUTER] | RIGHT [OUTER]} JOIN <имя таблицы2>|
<выражение объединения> -
<имя таблицы1> { INNER | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER]}
JOIN { ON условие | [USING
(список столбцов)]} <имя таблицы2>
<выражение перекрестного объединения> -
<имя таблицы1> CROSS JOIN <имя таблицы2>

<выражение запроса на объединение> -
<имя таблицы1> UNION JOIN <имя таблицы2>
В этих определениях INNER – означает внутреннее объединение, LEFT
– левое объединение, то есть в результат входят все строки первой таблицы, а части результирующих кортежей, для которых не было соответствующих значений во второй таблице, дополняются значениями NULL (не определено). Ключевое слово RIGHT означает правое внешнее соединение, и в отличие от левого соединения в этом случае в результирующее отношение включаются все строки второй таблицы, а недостающие части из первой таблицы дополняются неопределенными значениями. Ключевое слово FULL определяет полное внешнее объединение: левое и правое. При полном внешнем объединении выполняются и правое и левое внешние объединения и в результирующее отношение включаются все строки из первой таблицы, дополненные неопределенными значениями, и все строки из второй таблицы, также дополненные неопределенными значениями.
Ключевое слово OUTER означает внешнее объединение, но если заданы ключевые слова FULL, LEFT, RIGHT, то объединение всегда считается внешним.
Пример 2.6:
Два следующих запроса выведут один и тот же результат:
SELECT R3.*,R2.* FROM R2,R3
WHERE R2.Группа=R3.Группа;
SELECT R3.*,R2.* FROM R2 INNER JOIN R3
ON R2.Группа=R3.Группа;
Перекрестные запросы и подзапросы
Перекрестный запрос – способ группировки данных по двум измерениям, позволяющий отображать итоги в компактном результирующем наборе. В перекрестном запросе группировка выполняется по одному полю, а итоговая функция применяется к другому полю. Структура перекрестного запроса следующая: в конструкции TRANSFORM указывается поле и групповая функция, применяемая к нему, данное поле выводится на пересечении строк и столбцов; в конструкции GROUP BY указывается поле, по которому проводится группировка и которое выводится в качестве заголовков строк; в конструкции PIVOT указывается поле, значения которого выводятся в качестве заголовков столбцов.
Пример 2.7.
Вычислить средние оценки по каждой дисциплине в каждой группе.
TRANSFORM Avg(R1.Оценка) AS [СредняяОценка]
SELECT R3.Дисциплина FROM (R2 INNER JOIN R1 ON R2.ФИО =
R1.ФИО) INNER JOIN R3 ON R2.Группа = R3.Группа
GROUP BY R3.Дисциплина PIVOT R2.Группа;

Основы работы с MS Access
Создание SQL-запросов в среде MS Access
Для создания запроса необходимо перейти на вкладку «запросы» в вашей БД и выполнить «Создание запроса в режиме конструктора».
В появившемся диалоге добавления таблиц в конструкторе запросов нажать «Закрыть», а затем на панели «Конструктор запросов» выбрать «Вид» запроса – SQL (рис. 2.1). Переключаться между видом конструирования запроса можно, используя контекстное меню.
Рисунок 2.1 – Переключение в режим SQL
После выполнения этих действий вам будет предложено окно для ввода текста запроса. По окончанию ввода можете выполнить запрос для просмотра результатов, используя кнопку на панели «Конструктор запросов»
(рис. 2.2).
Рисунок 2.2 – Запуск запроса
Запросу можно передать параметр. Для этого надо в тексте запроса написать [param] (вместо param может быть любое слово записанное на кириллице или латинице, кроме служебных слов SQL и имен полей используемых отношений) – при выполнении запроса будет запрошен ввод параметра. Несколько параметров с одинаковым именем считаются одним.
Использование базы данных авто
Описание БД
Для выполнения лабораторных работ необходимо использовать базу данных автомобилей (файл allauto.mdb). Данная БД состоит из двух таблиц:
AUTO – содержит сведения о автомобилях;
MENU – описывает свойства автомобилей.

Описание полей
Ниже приведена таблица с описанием необходимых для выполнения лабораторной работы полей таблицы AUTO (название, тип, длинна и описание):
Field | Field Name | Type
| Width |
Dec
1 INV
Numeric
5
ИНВЕНТАРНЫЙ НОМЕР АВТ.
2 C_TYPE Character 2
КОД ТИПА
3 C_PLANT
Character 2
КОД ЗАВОДА-ИЗГОТОВИТЕЛЯ
4 C_MARK
Character 2
КОД МАРКИ (МОДЕЛИ)
5 C_BODY
Character 2
КОД ТИПА КУЗОВА
6 C_GROUP
Character 2
КОД ШТАТНОЙ ГРУППЫ
7 N_CHASS
Character 8
НОМЕР ШАССИ
8 N_ENG Character 8
НОМЕР ДВИГАТЕЛЯ
9 N_BODY
Character 8
НОМЕР КУЗОВА
10
YEAR
Numeric 4
ГОД ВЫПУСКА
11
N_PASS
Character 10
НОМЕР ТЕХ.ПАСПОРТА
12
D_PASS
Date 8
ДАТА ТЕХ.ПАСПОРТА
13
SIGN
Character 7
ОСНОВ.НОМЕР ГОС.РЕГИСТРАЦИИ
14
SIGN1
Character 7 1 ДОП.НОМЕР ГОС.РЕГИСТРАЦИИ
15
SIGN2
Character 7 2 ДОП.НОМЕР ГОС.РЕГИСТРАЦИИ
16
D_SIGN
Date 8
ДАТА ВЫДАЧИ НОМ.ГОС.РЕГИСТР.
17
D_EXPL
Date 8
ДАТА ВВОДА В ЭКСПЛУАТАЦИЮ
18
C_CLASS
Character 2
КОД КЛАССА
19
C_COLOR
Character 2
КОД ЦВЕТА
20
C_DUTY
Character 2
КОД СЛУЖБЫ ЗКСПЛУАТАЩИИ
21
C_OWNER Character 4
КОД ВЛАДЕЛЬЦА
22
COST
Numeric 8
СТОИМОСТЬ
26
C_SOUR
Character 2
КОД ИСТОЧНИКА ПОЛУЧЕНИЯ
27
N_SOUR
Character 15
НОМ.фондового извещения
28
D_SOUR
Date 8
ДАТА фондового извещения
29
C_STOR
Character 2
КОД СКЛАДА
30
N_STOR
Character 15
НОМЕР ДОК.СКЛАДА
31
D_STOR
Date 8
ДАТА ПОЛУЧЕНИЯ СО СКЛАДА
32
N_ALOC
Character 15 НОМЕР ДОК. НА РАСПРЕДЕЛ. В ПОДР.
33
D_ALOC
Date 8
ДАТА ДОК. НА РАСПРЕДЕЛ. В ПОДР.
34
D_TRANS
Date 8
ДАТА ПЕРЕДАЧИ В ПОДРАЗДЕЛЕНИЕ
35
D_DEL
Date 8
ДАТА СПИСАНИЯ (ЕСЛИ СПИСАН)

Задание на лабораторную работу №2
Часть I Простые запросы с параметром
1. Написать запрос для выбора автомобилей определенного цвета.
Цвет задается в виде параметра в условии WHERE (например,
‘белый’).
Часть II Использование агрегирования и подзапросов
1. Определить количество автомобилей, у которых номер фондового извещения начинается на "10" и не заканчивается на "39"
2. По каждой штатной группе а/м определить, сколько а/м каждой марки было выпущено в заданном году. Вывести названия групп и названия марок на экран.
3. Определить, какие а/м данного класса переданы в подразделения после указанной даты. Указать также номер автомобиля и дату документа передачи каждого а/м.
Часть III Использование объединений
1. Произвести выборку автомобилей из двух полей «номер авто»,
«класс авто» (подставлять название из отношения MENU). Если поле «класс» в таблице MENU не существует, то выводить строку
«Класс средства неизвестен» с помощью функции iif.
Часть IV Использование перекрестных запросов и подзапросов
1. Определить, сколько а/м каждой марки имеют год выпуска меньший, чем округленный до целого средний год выпуска а/м заданной пользователем марки.
2. Определить какое количество а/м каждой марки в каком году было произведено (перекрестный запрос: марки а/м на год производства).
Прием работы
Прием происходит при наличии оформленного отчета и работающей БД, созданной в среде MS Access.
Вопросы
1. Что такое SQL, назначение языка SQL?
2. Назначение команды SELECT?
3. Что такое внешнее и внутреннее объединение, чем отличаются?
4. Что такое левое, правое и полное объединение?
5. Что такое перекрестный запрос?
6. Как применить агрегатную функцию?
7. Для чего в стандарт SQL2 были введены объединения?
8. Чем отличается использование WHERE от HAVING?
9. Чем отличается использование DISTINCT от группировки?

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