Пример решения

2.1. Создание простых запросов.

Инструкция SELECT является основной в языке SQL. Она выполняет реляционные операции выбора и объединения для создания логических таблиц или запросов. Элементы списка выбора задают вычислимые выражения или столбцы, выбираемые из исходных таблиц, которые затем включаются в выходную логическую таблицу.

Синтаксис команды SELECT:

SELECT [ALL | DISTINCT | DISTINCTROW | TOP число [PERCENT] ]

список выбора

FROM [{имя-таблицы [[AS] псевдоним ] |

имя-запроса-выборки [[AS] псевдоним ] |

<таблица-объединение> },...

[WHERE условие отбора]

[GROUP BY имя-столбца,...]

[HAVING условие отбора]

[UNION [ALL] инструкция выбора]

[ORDER BY {имя столбца [ ASC | DESC ] },...]

IN <"имя-базы-данных-источника"> <[строка-подключения-источника-данных]>

[WITH OWNERACCESS OPTION];

где [1]список выбора есть:

{ * | {выражение [AS имя-столбца-выходной-таблицы] |

имя-таблицы.* |

имя-запроса.* |

псевдоним.* },...}

и где [1]таблица-объединение есть:

({ имя-таблицы [[AS] псевдоним ] |

имя-запроса-выборки [[AS] псевдоним ] } |

<таблица-объединение>}

{ INNER | LEFT | RIGHT | JOIN

{ имя-таблицы [[AS] псевдоним ] |

имя-запроса-выборки [[AS] псевдоним ] |

<таблица-объединения>}

ON <условие-объединения>)

Таблицы, которые должны быть объединены, указываются в предложении FROM, а строки, которые необходимо выбирать ─ предложении WHERE. Предложение GROUP BY используется для определения, как должны быть сформированы группы для итогового запроса, предложение HAVING

Для отбора тех групп, которые должны быть включены в результат запроса.

Для каждой таблицы или запроса SQL позволяет определить альтернативное имя. Это имя может быть использовано как псевдоним вместо полного имени таблицы при задании имен столбцов в списке выбора, в предложении WHERE, или в подчиненных запросах.

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

Можно также определить строки, включаемые ACCESS в выходной набор записей, с помощью предикатов ALL, DISTINCT, DISTINCTROW, TOP и TOP число PERCENT.

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

и те, которые дублируются.

DISTINCT требует возврата только уникальных строк (дублирующие отбрасываются).

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

При выводе только части записей используется предикат TOP n и TOP n PERCENT. Первый выводит только n первых записей, а второй − только первые n %. n должно быть целым числом, а во втором случае меньше 100.

Предложение ORDER BY позволяет отсортировать выведенные строки по задаваемым столбцам. В этом предложении можно указать один или несколько столбцов, по значениям которых будут сортироваться выводимые записи. Записи сортируются вначале по первому из указанных столбцов, а при одинаковых записях первого столбца - по последующим. Для каждого столбца можно указать порядок сортировки: по возрастанию (ASC) или убыванию (DESC) значений в столбцах.

Создадим простой запрос на SQL по таблице "Преподаватели", выводящий информацию о преподавателях, отсортированных по номерным знакам. Для этого откроем базу данных STUD и щелкнем по корешку “Запросы”. Выберем кнопку "Создать" и в появившемся диалоговом окне выберем режим конструктора и кнопку "OK". Закроем окно “Добавление таблиц”.

После появления окна с пустым бланком QBE и диалоговым окном выбора таблиц для запроса щелкнем по кнопке "SQL" панели инструментов. Появится окно редактирования, в котором мы будем вводить создаваемые запросы. После создания запроса его можно просмотреть в форме бланка QBE, хотя некоторые элементы SQL в бланке отображены не будут.

В окне редактирования запроса введем инструкцию SQL:

SELECT Преподаватели.Номер_П, Преподаватели.Фамилия,

Преподаватели.Имя,

Преподаватели.Отчество,Преподаватели.Кафедра,

Преподаватели.Должность,Преподаватель.Дом_адрес

FROM Преподаватели

ORDER BY Преподаватели.Номер_П;

Запустим полученный запрос на выполнение кнопкой "Запуск" на панели инструментов.

Сохраним полученный запрос под именем "Сведения о преподавателях".

2.2. Создание многотабличного запроса.

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

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

SELECT Преподаватели.Номер_П, Преопдаватели.Фамилия,

Преподаватели.Имя, Преподаватели.Отчество,

Преподаватели.Кафедра, Результаты.Предмет

FROM Преподаватели

INNER JOIN Результаты

ON Преподаватели.Номер_П = Результаты.Номер_П

ORDER BY Преподаватели.Номер_П;

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

2.3. Запросы с параметрами.

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

Синтаксис предложения:

PARAMETERS {[ имя_параметра ] тип_данных }...;

Элемент <имя_параметра> выводится как его описание при запросе. Поэтому желательно создавать имена, однозначно определяющие данные, которые нам нужны. Так, вместо малоинформативных имен типа "Введите дату" или "Текущий номер" желательно дать расширенное описание требуемых значений.

Вторым элементом предложения является <тип_данных>, в котором будет хранится введенное значение. Соответствие типов данных SQL и отображение их в ACCESS приведено в Приложении 2. В SQL−инструкции указывается SQL−тип данных (для совместимости кода с другими приложениями, поддерживающими этот язык).

2.4. Создание многотабличного запроса с параметрически заданным условием отбора.

Условие отбора определяет простой или составной предикат, который может принимать значения "True", "False", или "Null" для данной строки или группы строк.

Условие отбора используется в предложении WHERE или в инструкции SELECT внутри предложения HAVING, в подчиненных запросах или в инструкциях DELETE, UPDATE для выбора группы строк, над которыми будут произведены действия или отбор. Если условие отбора для строки имеет значение "True", то такая строка включается в результирующий набор.

Синтаксис условия отбора:

[NOT] {предикат | (условие отбора)}

[ { AND | OR | XOR | EQV | IMP }

[NOT] {предикат | (условие отбора)}]...

Приоритет логических операторов следующий: NOT, AND,OR, XOR, EQV (эквивалентность) и IMP (импликация). Для изменения приоритетов выполнения условий применяются круглые скобки. Таблицы истинности логических операторов представлены в таблицах 1..5 приложения 1.

Создадим запрос, выводящий информацию о студентах сдавших сессию вовремя. Для этого в окне редактора инструкций SQL введем команду:

SELECT Студенты.Фамилия, Студенты.Имя, Студенты.Отчество,

Результаты.Номер_С AS Сдали задолженность

Результаты.Задолженность, Результаты.Дата_сдачи,

FROM Студенты

INNER JOIN Результаты

ON Студенты.Номер_С = Студенты.Номер_С

WHERE ((Результаты.Дата_сдачи<=[Введите дату сдачи задолженности])

OR (Результаты.Задолженность=0));

Сохраним полученный запрос под именем "Студенты сдавшие сессию".

2.5. Групповые запросы.

В инструкции SELECT могут быть заданы столбцы, используемые для формирования групп из выбранных строк. Строки в каждой группе будут содержать одно и тоже значение заданного столбца (столбцов). В SQL для формирования групповых запросов предназначено предложение GROUP BY.

Имя столбца в предложении GROUP BY может быть именем произвольного столбца или любой таблицы, описанной в предложении FROM.

Если GROUP BY стоит после предложения WHERE, создаются группы из

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

При включении предложения GROUP BY в инструкцию SELECT, список выбора должен состоять из итоговых функций SQL: AVG, SUM,MAX,MIN, COUNT, STDEV, VAR и VARP, или же из имен столбцов, указанных в предложении GROUP BY.

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

SELECT Студенты.Номер_С,

AVG(Результаты.Оценка_Т) AS Теория,

AVG(Результаты.Оценка_П) AS Практика,

AVG(Результаты.Оценка_Л) AS Лабы

FROM Студенты

INNER JOIN Результаты

ON Студенты.Номер_С = Результаты.Номер_С

WHERE ([Результаты,Дата_сдачи<=[Введите дату])

OR ([Результаты.Задолженность=0))

GROUP BY Студенты.Номер_С;

Сохраним запрос под именем "Средний бал сдавших сессию".

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

Разница между предложениями HAVING и WHERE в том, что условие выбора в WHERE применяется к отдельным записям перед, тем как они будут объединены в группы, а условие в HAVING используется в отношении уже сформированных групп строк.

2.6. Создание запроса на объединение таблиц.

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

Обновление набора записей возможно только при наличии (явно или по умолчанию) ключевого слова DISTINCTROW.

Существуют два основных типа объединения таблиц: внутреннее (INNER) и внешнее (OUTER).

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

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

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

Например,

SELECT......

FROM TAB_1, TAB_2

......

задает областью поиска все строки TAB_1, присоединенные к каждой строке TAB_2, независимо от того, соответствуют они друг другу или нет.

Можно создавать вложенные объединения, но только объединяя

последующую таблицу с результатами объединения двух предыдущих. ACCESS не ограничивает глубину вложенности объединений таблиц.

Для указания типа объединения таблиц применяется предложение JOIN. Синтаксис предложения приведен ниже:

({ имя_таблицы [AS] псевдоним] |

имя_запроса_выборки [[AS] псевдоним] |

<таблица_объединения>

{ INNER | LEFT | RIGHT } JOIN

{имя_таблицы [[AS] псевдоним] |

имя_запроса_выборки [[AS] псевдоним] |

{таблица_объединение}

ON <условие_объединения>);

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

Операция INNER JOIN применяется для включения из обеих таблиц всех строк, удовлетворяющий условию объединения.

LEFT JOIN используется для вывода всех строк первой таблицы, и только тех строк второй, для которых выполняются условия объединения. Если таких значений не обнаруживается − в качестве значений соответствующих полей возвращается NULL.

RIGHT JOIN возвращает все строки второй таблицы, объединенные со строками из первой, для которых выполняются условия объединения. Если таких значений не обнаруживается − в качестве значений соответствующих полей возвращается NULL.

Если в условии объединения используется только предикат сравнения на равенство, то результат называется объединением по равенству. Бланк QBE позволяет представлять только такие объединения. Если же необходимо

определить объединения таблиц по условию неравенства ("<", ">", "< >", "<=", ">="), то запрос необходимо создавать через SQL.

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

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

Если имя таблицы или запроса совпадает с зарезервированным словом SQL, то такое имя заключается в квадратные скобки.

Для определения списка всех студентов в группах с долгами и без, создадим запрос "Долги в группах", используя внешнее объединение таблиц "Студенты" и "Результаты":

SELECT Студенты.Номер_С, Студенты.Фамилия, Студенты.Группа,

Результаты.Предмет, Результаты.Задолженность

FROM Студенты

LEFT JOIN Результаты

ON Студенты.Номер_С = Результаты.Номер_С;

2.7. Запросы, использующие таблицы удаленной БД.

Для определения источника данных базовых таблиц запроса используется предложение IN. Источником может служить другая база данных MS ACCESS, файлы dBASE, FoxPro, Paradox, база Btrieve, или любая другая база, поддерживающая ODBS. Это предложение является расширением в ACCESS, и в стандартном SQL отсутствует.

Синтаксис:

IN<"имя_источника_базы_данных">

<[строка_подключения_источника]>

<строка_подключения_источника> вводится вместе с квадратными скобками. Для базы MS ACCESS вводится только параметр "имя_источника_базы_дан-ных". Форматы вводимых данных представлены в