Лабораторная работа 6 по курсу "Компьютерные информационные технологии" запросы в access
 Скачать 0.92 Mb.
|
|
ЛАБОРАТОРНАЯ РАБОТА №6 по курсу “Компьютерные информационные технологии” ЗАПРОСЫ В ACCESS 1.Виды и способы создания запросовЗапросы предназначены для выборки информации из базы данных или для внесения изменений в базу данных. В Access имеются следующие основные виды запросов: по способу описания: QBE-запросы (Query By Example – выборка по образцу) – вид запроса устанавливается в специальном окне (окне конструктора запросов); SQL-запросы (Structured Query Language – структурированный язык запросов) – запрос описывается с помощью команд языка SQL; При построении любого QBE-запроса для него автоматически строится описание на языке SQL, и наоборот (за исключением некоторых SQL-запросов, которые не могут быть построены как QBE-запросы). Переход от описания в виде SQL-запроса к QBE-запросу (и наоборот) выполняется с помощью команд меню Вид. по назначению: запросы на выборку – для извлечения информации из базы данных; запросы на изменение – для внесения изменений в базу данных (включая добавление, удаление, изменение записей, создание новых таблиц); по содержанию: обычные (подробные) – содержащие информацию из отдельных записей, извлеченных из одной или нескольких таблиц; с групповыми операциями (итоговые) – запросы, в которых выполняется суммирование по отдельным полям, вычисление средних, подсчет количества записей и т.д.; перекрестные – для разбиения данных на несколько групп и подсчета итогов (количества, суммы, среднего и т.д.) по этим группам; по виду описания условий обработки данных: фиксированные – запросы, в которых условия обработки данных (т.е. их выборки или изменения) полностью заданы; параметрические – запросы, в которых условия обработки данных указываются пользователем при выполнении запроса. Имеются два способа создания запросов: с помощью Мастера – в процессе построения запроса пользователю предлагаются подсказки и возможности выбора; с помощью Конструктора – запрос строится пользователем самостоятельно. Источником данных для запроса может быть как таблица, так и другой запрос (созданный ранее). Обычно удобно создавать запрос с помощью Мастера, а затем вносить в него необходимые изменения с помощью Конструктора. 2.Создание запросов с помощью МастераПример 2.1 (запрос для получения информации из одной таблицы). Пусть требуется создать запрос для вывода списка всех рабочих. В списке должна содержаться вся информация о рабочих, кроме шифра объекта, на котором он работает. Перейти на вкладку Запросы. Нажать кнопку Создать. Выбрать команду Простой запрос. Нажать OK. В поле Таблицы и запросы выбрать таблицу Рабочие. Из списка Доступные поля перенести в список Выбранные поля обозначения всех полей, кроме поля Шифр объекта. Нажать Далее. Выбрать вид отчета – Подробный. Нажать Далее. Задать имя запроса Список рабочих. Установить переключатель Открыть запрос для просмотра данных. Нажать Готово. На экран выводится запрос с данными из таблицы Рабочие. Закрыть его. Чтобы снова выполнить запрос, требуется выделить его и нажать Открыть, или просто дважды щелкнуть по отметке запроса. Пример 2.2 (запрос для получения информации из нескольких таблиц). Пусть требуется создать запрос для вывода списка рабочих. Для каждого рабочего указываются его табельный номер, фамилия, разряд, шифр объекта, а также вид объекта, на котором он работает. Нажать кнопку Создать. Выбрать команду Простой запрос. Нажать OK. В поле Таблицы и запросы выбрать таблицу Рабочие. Из списка Доступные поля перенести в список Выбранные поля обозначения необходимых полей. Кнопку Далее не нажимать! В поле Таблицы и запросы выбрать таблицу Объекты. Из списка Доступные поля перенести в список Выбранные поля обозначение поля Вид объекта. Нажать Далее. Дальнейшие действия выполняются точно так же, как показано выше (п.2.1). Присвоить созданному запросу имя Список рабочих_2. 3.Изменение запросов с помощью КонструктораПример 3.1. Пусть требуется создать запрос для вывода списка объектов. Требуется также предусмотреть следующее: а) запрос должен быть упорядочен по названию заказчика, а для одного заказчика – по дате окончания строительства (первыми должны быть указаны объекты, строительство которых должно быть закончено раньше); б) вид объекта должен указываться после шифра объекта. Создать запрос на основе таблицы Объекты аналогично показанному выше. Присвоить ему имя Список объектов. Открыть запрос Список объектов в режиме Конструктора. Для этого выбрать запрос и нажать кнопку Конструктор. В появившемся окне шаблона запроса с помощью мыши переместить поле Вид объекта в желаемое место (после поля Шифр объекта). В строке Сортировка для полей Заказчик и Дата окончания установить значение По возрастанию. Сохранить измененный запрос. Для просмотра запроса нажать кнопку с восклицательным знаком, или выбрать команду Вид – Режим таблицы, или закрыть запрос и нажать кнопку Открыть. Пример 3.2 (создание вычисляемого поля). Внести изменения в созданный запрос Список объектов: добавить в него поле с именем Налог, в котором должна указываться величина, равная 10% от стоимости. Открыть запрос в режиме Конструктора. Чтобы создать вычисляемое поле Налог, поместить курсор в свободное поле в конце запроса. Ввести в этом поле следующее выражение: [Стоимость контракта]*0,1. После ввода этого выражения перед ним автоматически указывается отметка Выражение1. Примечание. Здесь Стоимость контракта – имя поля, используемого в выражении. Оно должно быть заключено в квадратные скобки. Никаких знаков в конце выражения не ставится. Примечание. Для записи выражения можно использовать построитель (мастер) выражений. Для этого следует нажать правую кнопку мыши и выбрать команду Построить. В столбце с введенным выражением нажать правую кнопку мыши и выбрать команду Свойства, или выбрать из меню команду Вид – Свойства. Указать следующие свойства созданного поля: Формат поля – Денежный или С разделителями разрядов, Подпись – Налог. Закрыть окно свойств поля. Примечание. Вместо указания свойства Подпись можно заменить отметку Выражение1 на название поля Налог. Сохранить внесенные изменения. Просмотреть полученный запрос. 4.Запросы с условиями выборкиПрежде чем выполнять следующее задание, необходимо добавить в таблицу Рабочие данные о рабочих, приведенные в таблице П1. Пример 4.1 (простое условие выборки). Создать запрос для получения списка рабочих-штукатуров, имеющих разряд не ниже пятого. В запросе должны указываться следующие данные: табельный номер, фамилия, имя и отчество, разряд, допуск к работам на высоте, а также шифр объекта и название заказчика, у которого работает данный рабочий. Запрос должен быть упорядочен по фамилиям. Сделать копию запроса Список рабочих. Для этого отметить этот запрос и выбрать команду Правка – Копировать. Затем выбрать команду Правка – Вставить. Указать имя запроса – Отобранные. Открыть запрос Отобранные в режиме конструктора. Удалить лишние поля Дата рождения и Дата приема на работу. Чтобы удалить поле, необходимо поместить курсор в это поле и выбрать команду Правка – Удалить столбцы. Из таблицы Рабочие (над описанием запроса) выбрать поле Шифр объекта и поместить его в свободный столбец в конце запроса (после столбца Допуск на высоту). Чтобы добавить в запрос название заказчика, выбрать команду Вид – Добавить таблицу, или нажать правую кнопку мыши в области над описанием запроса и выбрать команду Добавить таблицу. Выбрать таблицу Объекты. Из этой таблицы выбрать поле Заказчик и поместить в свободный столбец в конце запроса. В строке Условие отбора для поля Профессия указать штукатур. Снять флажок Вывод на экран для поля Профессия (так как в запросе будут только данные о штукатурах, выводить название профессии не требуется). В строке Условие отбора для поля Разряд указать >=5. В строке Сортировка для поля Фамилия установить значение По возрастанию. Сохранить внесенные изменения. Просмотреть запрос. Пример 4.2 (сложное условие выборки). Изменить запрос Отобранные, созданный в примере 4.1, таким образом, чтобы получить список штукатуров и каменщиков, имеющих разряд не ниже пятого. Открыть запрос Отобранные в режиме Конструктора. С помощью мыши переместить поле Профессия, чтобы оно размещалось перед полем Разряд (если оно там не находится). В строке Или для поля Профессия указать каменщик. В этой же строке для поля Разряд указать >=5. Примечание. Хотя условие >=5 уже было указано для поля Разряд в строке Условие отбора, его необходимо также указать в строке Или. Если не сделать этого, то в запросе будет получен список штукатуров, имеющих разряд не ниже пятого, и всех каменщиков. Для поля Профессия установить флажок Вывод на экран (так как в запросе будут указаны рабочие двух профессий – каменщики и штукатуры, профессию также следует выводить на экран). Сохранить внесенные изменения. Просмотреть запрос. 5.Запросы с групповыми операциямиГрупповые операции – это операции суммирования по отдельным полям, вычисление средних, подсчет количества записей и т.д. Пример 5.1 (создание запроса с помощью Мастера). Создать запрос для подсчета суммарной стоимости контрактов и количества контрактов каждого заказчика. Нажать кнопку Создать. Выбрать команду Простой запрос. Нажать OK. В поле Таблицы и запросы выбрать таблицу Объекты. Из списка Доступные поля перенести в список Выбранные поля обозначения полей Заказчик и Стоимость контракта. Нажать Далее. Выбрать вид отчета – Итоговый. Нажать кнопку Итоги. В появившемся окне Итоги для поля Стоимость контракта установить флажок Sum. Установить также флажок Подсчет записей. Нажать OK. В окне Создание простых запросов нажать Далее. Задать имя запроса Стоимость контрактов по заказчикам. Установить переключатель Открыть запрос для просмотра данных. Нажать Готово. Внести изменения в созданный запрос, чтобы сделать его заголовки более понятными. Для этого открыть запрос в режиме Конструктора. Для поля, в котором указывается сумма стоимостей контрактов, вызвать окно свойств (команда Вид – Свойства) и указать в строке Подпись заголовок Стоимость контрактов. Аналогично для поля с количеством объектов указать заголовок Количество. Другой способ указания заголовков – ввести их в строке Поле вместо отметок Sum – Стоимость контракта и Count-Объекты. Сохранить внесенные изменения и просмотреть запрос. Пример 5.2. Создать запрос для подсчета суммарной стоимости контрактов по годам окончания строительства. Нажать кнопку Создать. Выбрать команду Простой запрос. Нажать OK. В поле Таблицы и запросы выбрать таблицу Объекты. Из списка Доступные поля перенести в список Выбранные поля обозначения полей Стоимость контракта и Дата окончания. Нажать Далее. Выбрать вид отчета – Итоговый. Нажать кнопку Итоги. В появившемся окне Итоги для поля Стоимость контракта установить флажок Sum. Нажать OK. В окне Создание простых запросов нажать Далее. Для переключателя Выберите интервал группировки дат выбрать значение По годам. Нажать Далее. Задать имя запроса Стоимость контрактов по годам. Установить переключатель Открыть запрос для просмотра данных. Нажать Готово. Открыть запрос в режиме Конструктора. Установить для поля суммы контрактов заголовок Стоимость контрактов. Сохранить внесенные изменения и просмотреть запрос. Пример 5.3 (создание запроса с помощью конструктора). Создать запрос для подсчета количества рабочих каждой профессии. Запрос должен быть упорядочен по количеству рабочих (первыми должны выводиться профессии с максимальным количеством рабочих). Примечание. Если в запросе требуется только групповая операция подсчета (без каких-либо других операций), то такие запросы удобно создавать с помощью Конструктора. Нажать кнопку Создать. Выбрать команду Конструктор. Нажать OK. Выбрать таблицу Рабочие. Нажать кнопку Добавить. Закрыть окно Добавление таблицы. В строке Поле выбрать отметки полей Профессия и Табельный номер (именно в таком порядке). Выбрать команду Вид – Групповые операции. В описании запроса появляется строка Групповая операция. В строке Групповая операция для поля Профессия выбрать отметку Группировка, для поля Табельный номер – отметку Count. Это означает, что должно быть подсчитано количество табельных номеров по каждой профессии. В поле Табельный номер вызвать окно свойств (см. примеры 3.2, 5.1) и установить подпись Количество. В строке Сортировка для поля Табельный номер выбрать значение По убыванию. Сохранить запрос под названием Количество рабочих по профессиям. Просмотреть запрос. При необходимости внести в него изменения. Пример 5.4 (создание запроса с источником-запросом). Используя Конструктор, создать запрос для подсчета суммы налогов. Так как данных о налогах нет ни в одной таблице, в качестве источника будет использоваться запрос, в котором такие данные есть (запрос Список объектов). Нажать кнопку Создать. Выбрать команду Конструктор. Нажать OK. В окне Добавление таблицы перейти на вкладку Запросы (или Таблицы и запросы). Выбрать запрос Список объектов. Нажать кнопку Добавить. Закрыть окно Добавление таблицы. В строке Поле выбрать отметку поля Налог. Выбрать команду Вид – Групповые операции. В строке Групповая операция для поля Налог выбрать отметку Sum. Для поля Налог установить свойства: Подпись – Сумма налогов, Формат поля – Денежный или С разделителями разрядов. Сохранить запрос под названием Сумма налогов. Просмотреть запрос. 6.Перекрестные запросыПример 6.1. Создать запрос для подсчета количества рабочих каждой профессии, работающих на каждом из объектов. Результат запроса будет представлять собой таблицу, где в строках будут указаны объекты, а в столбцах – профессии. Нажать кнопку Создать. Выбрать команду Перекрестный запрос. Нажать OK. В появившемся окне Создание перекрестных таблиц для переключателя Показать выбрать значение Таблицы (или Все). Выбрать таблицу Рабочие. Нажать Далее. Из списка Доступные поля перенести в список Выбранные поля отметку поля Шифр объекта (т.е. выбрать поле, которому в создаваемой таблице будут соответствовать строки). Нажать Далее. Выбрать поле Профессия (т.е. поле, которому в создаваемой таблице будут соответствовать столбцы). Нажать Далее. В очередном окне Создание перекрестных таблиц в списке Поле выбрать Табельный номер, в списке Функции – Число (так как требуется подсчитать количество рабочих). Установить также флажок Вычислить итоговое значение для каждой строки (чтобы подсчитать количество рабочих на каждом объекте). Нажать Далее. Задать имя запроса Распределение профессий по объектам. Установить переключатель Просмотреть результаты запроса. Нажать Готово. Результаты запроса должны иметь примерно такой вид, как показано на рис.1.  Рисунок 1 – Результаты перекрестного запроса Пример 6.2. Создать запрос для подсчета количества рабочих каждой профессии, работающих на объектах каждого вида (жилые дома, офисы, цехи и т.д.). Все данные, необходимые для построения перекрестного запроса, должны содержаться в одной таблице или запросе (другими словами, в перекрестном запросе невозможно использовать данные из нескольких таблиц или запросов). Поэтому сначала необходимо создать обычный запрос, содержащий поля Табельный номер и Профессия (из таблицы Рабочие) и Вид объекта (из таблицы Объекты). Присвоить ему имя Распределение рабочих по видам объектов. Создать перекрестный запрос, как показано в примере 6.1. На шаге 2 потребуется выбрать для переключателя Показать значение Запросы (или Все) и выбрать запрос Распределение рабочих по видам объектов. В качестве строк создаваемого запроса следует выбрать виды объектов, в качестве столбцов – названия профессий (или наоборот). Присвоить созданному отчету имя Распределение профессий по видам объектов. 7.Запросы на изменение базы данныхПример 7.1 (запрос на обновление). Создать запрос для выполнения следующей операции: для всех каменщиков и штукатуров, имеющих разряд не ниже пятого, установить шифр объекта П80. Условия отбора (профессии – каменщики и штукатуры, разряд – не ниже пятого) уже были заданы в запросе Отобранные (см. раздел 4). Сделать копию запроса Отобранные, присвоив новому запросу имя Направление на объект. Открыть запрос Направление на объект в режиме Конструктора. Удалить из запроса все лишние поля: оставить только поля Профессия, Разряд и Шифр объекта. Выбрать команду Запрос – Обновление. В описании запроса появляется строка Обновление. В строке Обновление для поля Шифр объекта указать значение П80. Сохранить запрос Направление на объект. Закрыть его. Выполнить запрос (кнопкой Открыть или двойным щелчком мыши). При этом на экран будут выводиться предупреждения о том, что выполнение запроса приведен к изменению данных в таблице. На эти предупреждения отвечать Да. Открыть запрос Отобранные (или таблицу Рабочие) и убедиться, что для всех каменщиков и штукатуров с разрядом не ниже пятого установлен шифр объекта П80. Пример 7.2 (запрос на удаление). Создать запрос для удаления данных обо всех рабочих, имеющих разряд не выше второго и не имеющих допуска к работам на высоте. Используя Конструктор, создать обычный запрос, содержащий поля Фамилия, Разряд и Допуск на высоту (из таблицы Рабочие). В строке Условие отбора для поля Разряд указать условие <=2, для поля Допуск на высоту – условие нет. Сохранить запрос под именем Удаление. Выполнить запрос Удаление (кнопкой Открыть или двойным щелчком мыши). Эта операция требуется только в качестве меры предосторожности (запрос пока выполняет не удаление, а только просмотр данных). Убедиться, что в результате выполнения запроса на экран выводится информация о рабочих, соответствующих заданным условиям (разряд не выше второго, нет допуска на высоту). Открыть запрос Удаление в режиме Конструктора. Выбрать команду Запрос – Удаление. Можно (но не обязательно) также удалить из запроса поле Фамилия. Примечание. Если требуется снова преобразовать запрос для удаления в обычный запрос (для выборки данных), то необходимо выполнить команду Запрос – Выборка. Сохранить запрос Удаление. Выполнить его. При этом на экран выводятся предупреждения. Чтобы выполнить удаление, ответить на эти предупреждения Да. Открыть таблицу Рабочие и убедиться, что удаление выполнено. 8.Параметрические запросыПример 8.1. Создать запрос для получения списка рабочих заданной профессии, имеющих разряд не ниже заданного. В списке, получаемом в результате выполнения запроса, должны быть указаны табельные номера, фамилии и разряды рабочих. Профессия и разряд, должны вводиться пользователем при выполнении запроса. Используя Конструктор, создать обычный запрос, содержащий поля Табельный номер, Фамилия и Профессия (из таблицы Рабочие). Для поля Профессия в строке Условие отбора ввести: [Укажите профессию]. Для поля Разряд в строке Условие отбора ввести >=[Укажите разряд]. Здесь Укажите профессию и Укажите разряд – подсказки, выводимые на экран при выполнении запроса. Они указываются в квадратных скобках. Для поля Профессия снять флажок Вывод на экран (так как в результате запроса должен быть получен список рабочих одной профессии, и выводить ее на экран для каждого рабочего не требуется). Описание запроса должно иметь примерно такой вид, как показано на рис.2.  Рисунок 2 – Создание перекрестного запросаСохранить запрос под именем Список рабочих по профессии и разряду. Выполнить запрос и убедиться в том, что он выполняется правильно. 9.SQL-запросыSQL – стандартный язык управления базами данных, используемый в различных СУБД. При построении любого QBE-запроса для него автоматически строится описание на языке SQL, и наоборот (за исключением некоторых SQL-запросов, которые не могут быть построены как QBE-запросы). Все запросы, рассмотренные выше, представляли собой QBE-запросы. Чтобы перейти от описания в виде QBE-запроса к SQL-запросу, необходимо открыть запрос в режиме Конструктора и выбрать команду Вид – Режим SQL. Для возврата к описанию в виде QBE-запроса используется команда Вид – Конструктор. Обычно удобно сначала создать запрос в режиме QBE, а затем перейти в режим SQL и внести необходимые изменения. При изменении запроса в режиме SQL автоматически изменяется его описание в режиме QBE (и наоборот). При переходе в новую строку в любой команде SQL необходимо нажимать комбинацию клавиш CTRL-ENTER (а не просто клавишу ENTER). В конце команды языка SQL всегда ставится точка с запятой (;). В данной работе предлагается изучить примеры SQL-запросов, приведенные в подразделах 9.1 – 9.6, и самостоятельно создать SQL-запросы, указанные в разделе 9.7. 9.1.Выборка данных с помощью SQL-запросов. Команда SELECTОсновная команда языка SQL – команда SELECT. Общий вид этой команды следующий: SELECT поля FROM таблицы или запросы WHERE условия ORDER BY поля для сортировки; Здесь поля – имена полей, которые должны быть выведены на экран при выполнении запроса; таблицы или запросы – источники данных для запроса; условия – условия отбора данных; поля для сортировки – поля, по которым выполняется упорядочение данных в запросе. Пример 9.1. Запрос для вывода списка всех рабочих. В списке должна содержаться вся информация о рабочих, кроме шифра объекта, на котором он работает (эта же задача решалась в примере 2.1). SELECT [Табельный номер], Фамилия, [Имя и отчество], [Дата рождения], Профессия, Разряд, [Дата приема на работу], [Допуск на высоту] FROM Рабочие; Из этого примера видно, что в случае, если в имени поля имеются пробелы (например, в имени Табельный номер), оно должно заключаться в квадратные скобки. Пример 9.2. Получить список всех рабочих, имеющих разряд не ниже пятого и допуск к работе на высоте. Вывести фамилию, профессию, разряд. SELECT Фамилия, Профессия, Разряд FROM Рабочие WHERE (Разряд>=5) AND ([Допуск на высоту]=true); Пример 9.3. Получить список всех каменщиков и штукатуров, имеющих разряд не ниже пятого. Вывести табельный номер, фамилию, профессию, разряд, допуск к работе на высоте. Список должен быть отсортирован по профессии и фамилии (т.е. список сортируется по профессии, а при одинаковой профессии – по фамилии). SELECT [Табельный номер], Фамилия, Профессия, Разряд, [Допуск на высоту] FROM Рабочие WHERE ((Профессия="каменщик") OR (Профессия="штукатур")) AND (Разряд>=5) ORDER BY Профессия, Фамилия; Следует обратить внимание, что построить такой запрос в режиме QBE было бы достаточно сложно, так как список рабочих должен быть отсортирован по профессии и фамилии, а в запросе сначала указывается фамилия, затем - профессия. Если построить этот запрос в режиме QBE так, как описывалось в примерах выше (ввести в отчет поля Табельный номер, Фамилия, Профессия и Допуск на высоту, для полей Фамилия и Профессия в строке Сортировка выбрать значение По возрастанию), то упорядочение в запросе будет выполняться неправильно: записи будут упорядочиваться по фамилии, и только при одинаковой фамилии –по профессии. Использование SQL-запроса позволяет легко избежать этой проблемы. Пример 9.4. Получить те же данные, что и в примере 9.4. Однако сортировка должна выполняться по профессии, разряду и фамилии. Это значит, что список рабочих сортируется по профессии; рабочие с одинаковой профессией сортируются по разряду (причем первыми указываются рабочие, имеющие более высокий разряд), а при одинаковой профессии и разряде – по фамилии. SELECT [Табельный номер], Фамилия, Профессия, Разряд, [Допуск на высоту] FROM Рабочие WHERE ((Профессия="каменщик") OR (Профессия="штукатур")) AND (Разряд>=5) ORDER BY Профессия, Разряд DESC, Фамилия; Здесь DESC означает, что сортировка по разряду выполняется по убыванию (сначала указываются рабочие с более высоким разрядом). 9.2.SQL-запросы для выборки данных из нескольких таблицДля выборки данных из нескольких таблиц используется команда объединения таблиц, включаемая в команду SELECT: Таблица1 INNER JOIN Таблица2 ON Таблица1.Поле1=Таблица2.Поле2 Таблица1 и Таблица2 должны быть связаны (обычно используется связь 1:М). Таблица1 должна находиться со стороны 1, а Таблица2 – со стороны М. Поле1 и Поле2 – поля, по которым связываются таблицы (обычно они имеют одинаковые имена). Пример 9.5. Создать запрос для получения списка штукатуров и каменщиков, имеющих разряд не ниже пятого. В запросе должны указываться следующие данные: табельный номер, фамилия, имя и отчество, разряд, допуск к работам на высоте, а также шифр объекта и название заказчика, у которого работает данный рабочий. Запрос должен быть упорядочен по фамилиям рабочих. SELECT Рабочие.[Табельный номер], Рабочие.Фамилия, Рабочие.[Имя и отчество], Рабочие.Профессия, Рабочие.Разряд, Рабочие.[Допуск на высоту], Рабочие.[Шифр объекта], Объекты.Заказчик FROM Объекты INNER JOIN Рабочие ON Объекты.[Шифр объекта] = Рабочие.[Шифр объекта] WHERE ((Рабочие.Профессия="штукатур") OR (Рабочие.Профессия="каменщик")) AND (Рабочие.Разряд>=5) ORDER BY Рабочие.Фамилия; Здесь часть команды FROM Объекты INNER JOIN Рабочие ON Объекты.[Шифр объекта] = Рабочие.[Шифр объекта] означает, что данные должны выбираться из таблиц Объекты и Рабочие, между которыми имеется связь 1:М (причем таблица Объекты находится со стороны 1, а Рабочие – со стороны М, т.е. одному объекту может соответствовать несколько рабочих). Таблицы связаны по полю Шифр объекта, которое имеется в обеих таблицах. Так как в данном запросе используются поля из двух таблиц, перед каждым именем поля указывается имя таблицы, отделяемое точкой (например, Объекты.Заказчик - поле Заказчик из таблицы Объекты). 9.3.SQL-запросы с вычисляемыми полямиПример 9.6. Создать запрос для получения списка объектов. Для каждого объекта должно указываться все данные об объекте, имеющиеся в таблице Объекты, а также величина налога, составляющая 10% от стоимости контракта. Список объектов должен быть упорядочен по названию заказчика, а для каждого заказчика – по дате окончания строительства. Аналогичная задача решалась в примере 3.2. SELECT [Шифр объекта], Заказчик, [Вид объекта], [Стоимость контракта], [Дата заключения], [Дата окончания], [Стоимость контракта]*0.1 AS [Налог] FROM Объекты ORDER BY Заказчик, [Дата окончания]; Как видно из примера, вычисляемое поле описывается следующим образом: формула для вычисления (в данном случае [Стоимость контракта]*0.1), затем слово AS, затем – имя вычисляемого поля (в данном случае – Налог). Так как в этом примере все данные берутся из одной таблицы (Объекты), указывать имя таблицы перед именами полей необязательно. 9.4.SQL-запросы c групповыми операциямиОбщий вид команды SELECT, применяемой для таких запросов, следующий: SELECT поля и статистические функции FROM таблицы или запросы GROUP BY поля для группировки HAVING условия ORDER BY условия сортировки; Здесь поля и статистические функции – данные, которые должны быть получены в результате выполнения запроса; поля для группировки – поля, используемые для групповых операций (например, если требуется вычислить сумму стоимостей или количество контрактов для каждого заказчика, то в разделе GROUP BY будет указано поле Заказчик); условия – условия выборки данных. Раздел HAVING в запросах с групповыми операциями аналогичен разделу WHERE в обычных запросах. Он всегда указывается после раздела GROUP BY. Пример 9.7. Создать запрос для подсчета суммарной стоимости контрактов и количества контрактов каждого заказчика (эта же задача решалась в примере 5.1). SELECT Заказчик, Sum([Стоимость контракта]) AS [Стоимость контрактов], Count([Шифр объекта]) AS [Количество] FROM Объекты GROUP BY Заказчик; Здесь выражение GROUP BY Заказчик означает, что записи в таблице Объекты (указанной в разделе FROM) будут сгруппированы по значению поля Заказчик. Для записей с одинаковым значением этого поля будет подсчитана сумма значений поля Стоимость контракта; для этого указано выражение Sum([Стоимость контракта]). Результат будет выводиться на экран под именем Стоимость контрактов (указывается после слова AS). Будет также подсчитано количество значений шифра объекта (т.е. количество записей) с одинаковым значением поля Заказчик; для этого используется функция Count. Примечание. Вместо выражения Count([Шифр объекта]) можно указать Count(*). Такая операция представляет собой подсчет количества записей. Пример 9.8. Создать запрос для получения тех же данных, что и в примере 9.7, но требуется только информация о заказчиках, для которых стоимость контрактов составляет не менее 100 млн ден.ед. SELECT Заказчик, Sum([Стоимость контракта]) AS [Стоимость контрактов], Count([Шифр объекта]) AS [Количество] FROM Объекты GROUP BY Заказчик HAVING Sum([Стоимость контракта])>=100000000; Пример 9.9. Создать запрос для подсчета количества рабочих каждой профессии. Запрос должен быть упорядочен по количеству рабочих (первыми должны выводиться профессии с максимальным количеством рабочих), а при одинаковом количестве рабочих – по профессии (в алфавитном порядке). Аналогичная задача решалась в примере 5.3. SELECT Профессия, Count([Табельный номер]) AS [Количество] FROM Рабочие GROUP BY Профессия ORDER BY Count([Табельный номер]) DESC, Профессия; Пример 9.10. Создать запрос для подсчета суммы налогов (аналогичная задача решалась в примере 5.4). Так как данных о налогах нет ни в одной таблице, в качестве источника будет использоваться запрос, в котором такие данные есть. Этот запрос был создан в примере 9.7. Пусть он сохранен под именем Список объектов. SELECT Sum([Налог]) AS [Сумма налогов] FROM [Список объектов]; Здесь Налог – имя вычисляемого поля, имеющегося в запросе Список объектов. 9.5.SQL-запросы на изменение базы данныхЗапросы на обновлениеОбщий вид таких запросов следующий: UPDATE таблица SET поле=значение WHERE условия Пример 9.11. Создать запрос для выполнения следующей операции: для всех каменщиков и штукатуров, имеющих разряд не ниже пятого, установить шифр объекта П80 (аналогичная задача решалась в примере 7.1). UPDATE Рабочие SET [Шифр объекта] = "П100" WHERE ((Профессия="штукатур") OR (Профессия="каменщик")) AND (Разряд>=5); Пример 9.12. Создать запрос для выполнения следующей операции: снять всех рабочих с объектов заказчика “Автозавод”. UPDATE Объекты INNER JOIN Рабочие ON Объекты.[Шифр объекта]= Рабочие.[Шифр объекта] SET Рабочие.[Шифр объекта] = Null WHERE (Объекты.Заказчик="Автозавод"); Здесь требуется изменять значения поля Шифр объекта в таблице Рабочие, но при этом используется информация из таблицы Объекты, где указаны названия заказчиков. Поэтому в команде используется раздел INNER JOIN (связь между таблицами), и перед именами полей указываются имена таблиц. Значение Null, присваиваемое полю Шифр объекта, обозначает, что это поле становится пустым. Важно понимать, что значение Null – это не то же самое, что пустая строка (“”). Запросы на удалениеОбщий вид таких запросов следующий: DELETE FROM таблица WHERE условие Пример 9.13. Создать запрос для удаления данных обо всех рабочих, имеющих разряд не выше второго и не имеющих допуска к работам на высоте (аналогичная задача решалась в примере 7.1). DELETE FROM Рабочие WHERE (Разряд<=2) AND ([Допуск на высоту]=False); 9.6.Параметрические SQL-запросыКак и в QBE-запросах, вместо конкретных величин в параметрических SQL-запросах указываются тексты сообщений (подсказок), которые должны выводиться на экран при выполнении запроса. Эти подсказки заключаются в квадратные скобки. Пример 9.14. Создать запрос для получения списка рабочих заданной профессии, имеющих разряд не ниже заданного. В списке, получаемом в результате выполнения запроса, должны быть указаны табельные номера, фамилии и разряды рабочих. Профессия и разряд, должны вводиться пользователем при выполнении запроса (эта же задача решалась в примере 8.1). SELECT [Табельный номер], Фамилия, Разряд FROM Рабочие WHERE (Профессия=[Укажите профессию]) AND (Разряд>=[Укажите разряд]); При выполнении этого запроса на экран будут выводиться подсказки Укажите профессию и Укажите разряд. 9.7.Создание SQL-запросов. Задания для самостоятельного выполненияДля создания SQL-запроса “с нуля” (т.е. без предварительного создания QBE-запроса) необходимо выполнить следующее. Перейти на вкладку Запросы. Нажать кнопку Создать. Выбрать команду Конструктор. Нажать OK. На экран выводится пустой бланк для создания QBE-запроса, а также окно Добавление таблицы. Закрыть окно Добавление таблицы. Выбрать команду Вид – Режим SQL. В появившемся пустом окне ввести текст SQL-запроса. Сохранить запрос. Для выполнения запроса нажать кнопку Запуск (с восклицательным знаком), или выбрать команду Вид – Режим таблицы, или закрыть запрос и выполнить его нажатием кнопки Открыть. Предлагается самостоятельно создать следующие SQL-запросы (не создавая предварительно QBE-запросы): а) список всех объектов, представляющих собой жилые дома или офисы. Для каждого объекта должны указываться все данные, имеющиеся в таблице Объекты. Список объектов должен быть упорядочен по названию заказчика, а для каждого заказчика – по шифру объекта; б) список всех рабочих, занятых на объектах, где заказчиком является автозавод. Для каждого рабочего указывается табельный номер, фамилия, профессия, шифр объекта. Список должен быть упорядочен по фамилиям рабочих (если есть однофамильцы – то по табельному номеру); в) то же, что в задании б), но для произвольного заказчика. Название заказчика должно вводиться пользователем при выполнении запроса; г) список всех объектов. Для каждого объекта должны указываться все данные, имеющиеся в таблице Объекты, а также количество дней, оставшихся до окончания строительства. Для вычисления количества дней до конца строительства использовать формулу: [дата окончания]-date() (здесь date() – стандартная функция, возвращающая текущую дату); д) то же, что в задании г), но только для объектов, у которых до окончания строительства остается не более 30 дней; е) список всех объектов. Для каждого объекта указывается его шифр, заказчик, вид объекта и количество занятых рабочих. Список должен быть отсортирован по шифрам объектов (по алфавиту); ж) повышение стоимости контракта на 20% для всех объектов – офисов. Указание: в разделе SET использовать выражение примерно следующего вида: [Стоимость контракта] = [Стоимость контракта]*1,2; з) удаление данных об указанном рабочем (фамилия рабочего должна вводиться пользователем). ПРИЛОЖЕНИЕДАННЫЕ ДЛЯ ДОБАВЛЕНИЯ В БАЗУ ДАННЫХТаблица П1 Данные для добавления в таблицу Рабочие
|