Будь умным!

У вас вопросы?
У нас ответы:) SamZan.net

ТЕМА УПРАВЛЕНИЯ БАЗАМИ ДАННЫХ MICROSOFT CCESS 2000 Методические указания по выполнению лабораторных раб

Работа добавлена на сайт samzan.net:

Поможем написать учебную работу

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

Предоплата всего

от 25%

Подписываем

договор

Выберите тип работы:

Скидка 25% при заказе до 3.6.2024

ФЕДЕРАЛЬНОЕ АГЕНСТВО ПО ОБРАЗОВАНИЮ

Санкт-Петербургская государственный

инженерно-экономический университет

СИСТЕМА УПРАВЛЕНИЯ БАЗАМИ ДАННЫХ

MICROSOFT  ACCESS 2000

Методические указания

по выполнению лабораторных работ

Для студентов, магистров и аспирантов  всех специальностей

Санкт-Петербург

2001

Утверждено

Редакционно-издательским советом СПбГИЭУ

Составители:

канд. экон. наук, доц. И.Г. Гниденко

канд. техн. наук, проф. Е.Л. Рамин

канд. экон. наук, доц. О.Д.Мердина

Рецензенты

канд. экон. наук, доц. А.А.Макаров,

канд. экон. наук, доц. И.В.Егорова

Подготовлено

на кафедре вычислительных систем и программирования

Печатается в авторской редакции

© СПбГИЭУ, 2001

Лабораторная работа № 1. Создание таблиц

Цель работы: Ознакомление с основными понятиями СУБД Access на примере учебной базы данных компании «Борей». Освоение технологии конструирования реляционных таблиц.  

Краткие сведения

СУБД Access 2000 использует реляционную модель базы данных,  в которой данные представлены в виде взаимосвязанных таблиц (отношений по англ. - relations).

Важнейшим этапом проектирования базы данных является разработка информационно-логической (инфологической) модели предметной области, не ориентированной на СУБД, но отражающей предметную область в виде совокупности информационных объектов и их информационных связей.

СУБД Access позволяет работать с объектами базы данных, к которым относятся таблицы, запросы, формы, отчеты, страницы,  макросы и модули.

Таблицы служат для хранения данных в определенной структуре.

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

Формы предназначены для ввода, редактирования и просмотра табличных данных на экране в удобном виде.

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

Страницы доступа к данным – это Web- страницы, обеспечивающие функциональность стандартных форм и отчетов Access: ввод, редактирование и представление данных. Страницы доступа к данным можно открывать в программах просмотра Web-страниц (например, Internet Explorer) и использовать для ввода, просмотра и отбора информации в базе данных.

Макросы используются для автоматизации различных процедур обработки данных, являются программами, состоящими из макрокоманд высокого уровня. Макропрограммирование в Access не требует знания языка VisualBasic.  Имеющийся в Access набор из около 60 макрокоманд обеспечивает практически любые действия, необходимые для решения задач.

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

Все данные  БД Microsoft Access и средства их отображения хранятся в одном файле с расширением MDB.

Задание 1. Ознакомиться с учебной базой данных компании «Борей»

Технология

  1.  Загрузить Microsoft Access 2000.
  2.  В диалоговом окне, которое появится в процессе загрузки, установить флажок «Открыть базу данных».
  3.  Если в окне ранее открывавшихся баз данных нет базы «Борей», дважды щелкнуть по строке «Другие файлы»
  4.  В окне «Открытие файла базы данных» установить путь:

C:\Progam Files\Micosoft Office\Office\Samples\Борей

  1.  Щелкнуть по кнопке «Ok».
  2.  Просмотреть данные каждой таблицы, открыв их.
  3.  Просмотреть структуру каждой таблицы в режиме конструктора. Обратить внимание на типы и свойства полей.
  4.  Открыть таблицу «Клиенты». Выполнить следующие операции:
  •  определить количество записей в таблице;
  •  просмотреть 45-ую запись, введя ее номер в окно номеров записей
  •  рассортировать таблицу по должностям. Для этого установить указатель мыши на заголовок столбца и щелкните правой кнопкой мыши. Столбец будет выделен и появится контекстное меню. Выбрать в контекстном меню пункт «Сортировка по возрастанию».
  •  скрытьстолбец «Обращаться к», выделив его и выполнив команду ФОРМАТ/ Скрыть. Отобразить скрытый столбец;
  •  выделить первые два столбца и закрепите их, выполнив команду ФОРМАТ/Закрепить. Прокрутить таблицу по горизонтали. Отменить закрепление;
  •  применить фильтр для выделения строк с клиентами в г. Лондон. Для этого выделить в любой строке поле со значением «Лондон» и вызвать контекстное меню. Выбрать пункт «Фильтр по выделенному». Отменить фильтр, щелкнув в контекстном меню по пункту «Удалить фильтр»;
  •  изменить вид сетки таблицы, используя соответствующую кнопку инструментальной панели «Таблица», если такой кнопки нет, то ее необходимо ввести.  
  1.  Просмотреть запросы и их структуру
  •  открыть запрос на выборку товаров с ценой выше средней;
  •  переключить запрос в режим конструктора и просмотреть структуру запроса.
  1.  Просмотреть формы:
  •  «Сотрудники», вкладки «Служебные данные», «Личные данные»;
  •  «Товары»;
  •  «Типы»;
  1.  Просмотреть форму «Сотрудники» в режиме конструктора.
  2.  Просмотреть отчеты:
  •  «Каталог»;
  •  «Продажи по типам»;
  •  «Суммы продаж по годам»;
  •  «Счет»;
  •  «Продажи по сотрудникам и странам», введя дату начала: 01.01.1995 и дату окончания: 31.12.1995
  1.  Просмотреть отчет «Каталог» в режиме конструктора.

1. Таблицы

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

Таблица - это объект БД, который хранит данные определенной структуры. Таблица состоит из записей (строк), каждая из которых описывает одну сущность. Каждый столбец таблицы - это поле. Столбец содержит однотипную информацию.

Длина имени таблицы - не более 64 символов.

Длина имени поля - не более 64 символов.

Количество полей  в одной таблице - не более 255.

Количество записей - неограниченно.

Суммарный объем информации во всей БД - не более 1 гигабайта.

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

Таблица может содержать следующие типы полей (всего 8):

Текстовый  Короткий текст. Текст и числа, например, имена и адреса, номера телефонов и почтовые индексы. Текстовое поле может содержать до 255 символов.

Поле Memo  Длинный текст и числа, например, комментарии и пояснения. Memo-поле может содержать до 65 535 символов.

Числовой  Общий тип для числовых данных, допускающих проведение математических расчетов, за исключением расчетов для денежных значений. Свойство Размер поля позволяет указать различные типы числовых данных. Длина -  до 8 байт. Точность – до 15 знаков.

Дата/время  Значения даты и времени. Пользователь имеет возможность выбрать один из многочисленных стандартных форматов или создать специальный формат. Длина - 8 байт.

Денежный  Денежные значения. Числа представляются с двумя знаками после запятой. Не рекомендуется использовать для проведения денежных расчетов значения, принадлежащие к числовому типу данных, так как последние могут округляться при расчетах. Значения типа "Денежный" всегда выводятся с указанным числом десятичных знаков после запятой. Длина - 8 байт.

Счетчик   Автоматически вставляющиеся последовательные номера. Счетчик увеличивается на единицу для каждой следующей записи. Нумерация начинается с 1. Поле счетчика удобно для создания ключа. В таблице может быть только одно такое поле. Длина - 4 байта.

Логический Значения "Да"/"Нет", "Истина"/"Ложь", "Вкл"/"Выкл", т.е. одно из двух возможных значений. Длина - 1 байт.

Поле объекта OLE  Объекты, созданные в других программах, поддерживающих протокол OLE, например графики, рисунки и т.п. Объекты связываются или внедряются в базу данных Microsoft Access через элемент управления в форме или отчете.

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

Индексирование полей таблицы. Индексирование позволяет ускорить сортировку и поиск данных в таблице. Можно индексировать числовые, денежные, текстовые, логические поля, а также поля типа Счетчик и Дата. Не следует создавать слишком много индексов для одной таблицы, т.к. это замедлит ввод и редактирование ее данных.

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

Связи между таблицами. Таблицы могут быть связаны отношениями один-к-одному, один-ко-многим и многие-к-многим. Access позволяет использовать только отношения первых двух типов.

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

Отношение один-к-одному означает, что одной записи подчиненной таблицы соответствует только одна запись в главной таблице. Такие отношения встречаются очень редко, т.к. требую неоправданно много места в БД. Вместо них можно просто добавить поля подчиненной таблицы к полям главной.

Наиболее часто используются отношения один-ко-многим. В этом случае одной записи в главной таблице соответствует несколько записей в подчиненной таблице.

Для создания отношений необходимо указать поля в двух таблицах, которые содержат одни и те же данные. Обычно такое поле в одной из таблиц (главной) является ключевым. Имена связывающих полей могут отличаться, но типы и свойства должны совпадать. Возможна связь между полем типа Счетчик и полем типа Число с форматом Длинное целое.

Рекомендации для ввода данных в таблицы

Для ввода в поле текущей записи значения из того же поля предыдущей записи нажать клавиши <Ctrl> и <“>. (Двойной апостроф на русском регистре - на клавише “2”).

Задание 2. Создать базу данных Академия на основе инфологической модели, приведенной на рис.1. База данных должна содержать 4 взаимосвязанных таблицы: Студент, Группа, Специальность и Факультет.

Таблица Студент:

  •  Номер зачетной книжки – ключевое поле числового типа, длинное целое;
  •  Номер группы – числовое поле, целое;
  •  Фамилия – текстовое поле длиной 15 символов;
  •  Дата рождения – поле типа «дата»;
  •  Коммерческий – логическое поле (да/нет).

Таблица Группа:

  •  Номер группы –ключевое поле числового типа, целое;
  •  Номер специальности – числовое поле, длинное целое;
  •  Номер факультета – числовое поле, байтовое;
  •  Номер курса – числовое поле, байтовое.  

Таблица Факультет:

  •  Номер факультета – ключевое поле числового типа, байтовое;
  •  Наименование факультета – текстовое поле, 30 символов;
  •  Декан -  тестовое поле, 15 символов.

Рис1. Инфологическая модель базы данных Академия

Таблица Специальность:

  •  Номер специальности – ключевое поле числового типа, длинное целое;
  •  Наименование специальности – текстовое поле, 40 символов;
  •  Стоимость обучения – денежного типа.

Технология создания таблицы Студент

  1.  Создать новую базу данных, щелкнув по соответствующей кнопке инструментальной панели.  
  2.  На вкладке «Общие» дважды щелкнуть по значку «База данных». В окне «Файл новой базы данных» ввести имя базы данных «Академия.mdb».
  3.  В окне базы данных щелкнуть по кнопке «Создать». В окне базы данных выбрать режим создания  таблицы с помощью мастера.
  4.  На 1-м шаге работы мастера выбрать в качестве образца таблицу «Студенты» и, дважды щелкая по образцам полей, включить в создаваемую таблицу поля:
  •  код студента
  •  фамилия
  •  специализация

Из таблицы сотрудники:

  •  дату рождения
  •  код отдела
  1.  Переименовать поля в соответствии с заданием:

код студента -  номер зачетной книжки

специализация – коммерческий

код отдела – номер группы.

Щелкнуть по кнопке «Далее».

  1.  На шаге 2 в окне «Создание таблиц» дать имя таблице «Студент» и сохранить включенным переключатель «Microsoft Access автоматически определяет ключ». Щелкнуть по кнопке «Далее».  
  2.  На шаге 3 установить переключатель Изменить структуру таблицы  и нажать на кнопку  Готово.
  3.  Провести  корректировку типов данных в соответствии с заданием.
  4.  С помощью копки Вид инструментальной панели перейти в режим таблицы  и приступить к вводу данных.
  5.  Ввести данные для 3-х групп по 10 студентов в каждой с различным набором признаков.
  6.  После ввода данных  сохранить базу данных.

Освоение приемов работы с фильтрами в таблицах

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

Технология:

  1.  Для установки расширенного фильтра ввести команду ЗАПИСИ/Фильтр/Расширенный фильтр. Появится окно с бланком фильтра.
  2.  Указать поле, по которому должна происходить фильтрация. В окне бланка дважды щелкнуть по полю «Фамилия», расположенном в таблице «Студент». Поле «Фамилия» появится в 1-ой строке «Поле» нижней половины бланка (столбец 1).
  3.  Указать в строке бланка «Сортировка» порядок сортировки. Для этого щелкнуть левой клавишей по этой строке в 1-м столбце. Появится список вариантов сортировки. Выбрать «по возрастанию».
  4.  Ввести условие отбора. Для этого ввести в 3-ью строку 2 символа: В*   
  5.  Применить фильтр. Для этого можно воспользоваться 3-мя способами:
  •  выполнить команду ЗАПИСЬ/Применить фильтр.
  •  щелкнуть по кнопке инструментальной панели «Применение фильтра»
  •  щелкнуть правой клавишей по свободной зоне бланка и  в контекстном меню выбрать пункт «Применить фильтр».
  1.  Отмена фильтра. Для просмотра таблицы в полном виде нужно выполнить команду «Удалить фильтр» либо в меню ЗАПИСИ, либо в контекстном меню, либо с помощью соответствующей кнопки инструментальной панели.

Задание 5. Найти студентов, родившихся в заданном году с помощью расширенного фильтра.

  1.  Вызвав контекстное меню, очистить бланк фильтра
  2.  Ввести в 1-ый столбец бланка условие для поля дата рождения >= заданная дата, а во 2-ой столбец  для того же поля условие <= заданная дата,

Задание 6. Найти студентов, родившихся в заданном году и обучающихся на коммерческой основе в заданной группе.

Для решения задачи использовать 4 столбца бланка с названиями полей: дата рождения, дата рождения, номер группы, коммерческий.

Задание 7. Предварительно создав в режиме конструктора, ввести данные в  таблицы: Группа, Факультет, Специальность.

В таблице Группа  поле «Номер группы» должно быть того же типа и с того же размера, что и в таблице Студент. В эту таблицу следует ввести 5-6 строк, в том числе 3 строки с теми же номерами групп, что и в таблице Студент.

Аналогично, в таблице Факультет поле «Номер факультета» должно быть того же типа и того же размера, что и в таблице Группа.  В таблице должно быть 3-5 строк, в том числе строки с такими же номерами факультетов, что и в таблице Группа.

В таблице Специальность  поле «Номер специальности» должно быть того же типа и того же размера, что и в таблице Группа. В таблице должно быть 3-5 строк, в том числе строки с такими  же номерами специальностей, что и в таблице Группа.

Задание 8. Сжатие базы данных

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

1. Сжатие открытой базы данных

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

2.Сжатие базы данных, которая не открыта 

-  Закрыть активную базу данных.

-  Выполнить команду СЕРВИС/Служебные программы/Сжать базу данных.

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

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

- Нажать кнопку Сохранить.

Допускается сжатие файла базы данных в файл с тем же именем, что и имя файла исходной базы данных, или создание файла с новым именем. При указании того же имени, диска и папки и при успешном сжатии базы данных исходный файл автоматически заменяется на сжатый файл.

Общие сведения о MDE-файлах

Если база данных содержит программы Visual Basic, то ее сохранение как MDE-файла скомпилирует все модули, удалит все изменяемые исходные программы и выполнит сжатие базы данных. Программы Visual Basic будут по-прежнему выполняться, но их нельзя будет просмотреть или изменить, благодаря чему уменьшится размер базы данных. Кроме того, будет оптимизировано использование памяти, что повысит быстродействие.

Защита базы данных

Microsoft Access обеспечивает два традиционных способа защиты базы данных: установка пароля, требуемого при открытии базы данных, и защита на уровне пользователей, которая позволяет ограничить, к какой части базы данных пользователь будет иметь доступ или какую ее часть он сможет изменять. Кроме того, можно удалить изменяемую программу Visual Basic из базы данных, чтобы предотвратить изменения структуры форм, отчетов и модулей, сохранив базу данных как файл MDE.

Установка пароля

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

Для установки пароля следует открыть базу данных в монопольном режиме (команда ФАЙЛ/Открыть, в списке кнопки Открыть выбрать режим Монопольно) и  выполнить команду СЕРВИС/Защита/Задать пароль базы данных.

Задание 9. Шифрование и дешифрование базы данных

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

1. Запустить Microsoft Access без открытия базы данных.

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

2. Выполнить команду СЕРВИС/Защита/Шифровать/Дешифровать.

3 . Указать имя базы данных, которую требуется зашифровать или дешифровать, и нажать кнопку OK.

4. Указать имя, диск и папку для конечной базы данных и нажать кнопку OK.

Лабораторная работа № 2.  Конструирование   запросов

Цель работы: Ознакомление с технологией связывания таблиц. Конструирование и использование запросов к базе данных. Конструирование и использование запросов на изменение базы данных.

1. Связи между таблицами

Таблицы могут быть связаны отношениями один-к-одному, один-ко-многим и многие-к-многим. Access позволяет использовать только отношения первых двух типов.

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

Отношение один-к-одному означает, что одной записи подчиненной таблицы соответствует только одна запись в главной таблице. Такие отношения встречаются очень редко, т.к. требуют неоправданно много места в БД. Вместо них можно просто добавить поля подчиненной таблицы к полям главной.

Наиболее часто используются отношения один-ко-многим. В этом случае одной записи в главной таблице соответствует несколько записей в подчиненной таблице.

Для создания отношений необходимо указать поля в двух таблицах, которые содержат одни и те же данные. Обычно такое поле в одной из таблиц (главной) является ключевым. Имена связывающих полей могут отличаться, но типы и свойства должны совпадать. Возможна связь между полем типа Счетчик и полем типа Число с форматом Длинное целое.

Задание 1. Установить связи между данными таблиц Факультет, Специальность, Группа и Студент.

Технология.

  1.  Загрузить файл базы данных Академия, созданный в предыдущей лабораторной работе.
  2.  Щелкнуть по кнопке инструментальной панели Схема данных или выполнить команду СЕРВИС/Схема данных.
  3.  В окне Добавить таблицу последовательно выбирать указанные выше таблицы, щелкая затем по кнопке Добавить.
  4.  Добавив последнюю таблицу, щелкнуть по кнопке Закрыть.
  5.  В окне Схема данных установить связь между таблицей Факультет и таблицей Группа. Для этого:
  •  выделить щелчком поле Номер факультета в таблице Факультет и, удерживая нажатой левую клавишу мыши, переместить указатель на поле с тем же названием в таблице Группа;
  •  отпустить клавишу мыши, появится окно Связи, в котором необходимо включить флажок Обеспечение целостности данных и нажать кнопку Создать;
  •  в окне Схема данных появится связь между таблицами типа один ко многим.

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

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

  1.  Аналогично установить связь между таблицами Специальность и Группа по полю Номер специальности. Установить связь между таблицами Группа и Студент по полю Номер группы.
  2.  Закрыть окно Схема данных.

2. Запросы

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

Условие может определять:

- порядок сортировки выводимых данных ;

- фильтрацию данных;

- вычисляемые поля;

- вывод данных из нескольких связанных таблиц;

- и т.п.

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

Все запросы можно разделить на 2 группы:

- запросы-выборки;

- запросы действия.

Запросы-выборки извлекают данные из таблиц в соответствии с заданными условиями

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

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

- запрос с критерием поиска;

- запрос перекрестный;

- запрос с параметром;

- запрос с вычисляемым полем;

- запрос с итогами;

- запрос к связанным таблицам.

Запросы-действия предназначены для выполнения требуемых действий над данными таблиц. Они позволяют добавлять, изменять  или удалять данные. В Access существует 4 вида запросов-действия:

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

- запрос на замену (обновление);

- запрос на создание новой таблицы;

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

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

1-ая строка таблицы называется Поле. В ней содержатся имена полей. Поле может быть вычисляемым. Тогда в соответствующей ячейке содержится выражение для вычисления значения этого поля.

2-ая строка таблицы называется Сортировка. Она определяет способ сортировки по соответствующему полю (по возрастанию, по убыванию или без сортировки).

3-ья строка  таблицы управляет Выводом  на экран значения соответствующего поля

4-ая строка таблицы называется Условием отбора. Содержит критерии, по которым производится отбор записей в динамический набор данных.

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

При записи в запросе условия можно объединять критерии при помощи операций И (AND) или ИЛИ (OR).

Использование вычисляемых полей в запросах

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

1. В режиме конструктора запроса введите выражение в пустую ячейку строки "Поле". После нажатия клавиши Enter или перевода курсора в другую ячейку будет выведено имя поля "ВыражениеN", где N - целое число, увеличивающееся на единицу для каждого нового создаваемого вычисляемого поля в запросе. Это имя выводится перед выражением и отделяется от него двоеточием. В режиме таблицы данное имя становится заголовком столбца.

2. Пользователь имеет возможность переименовать вычисляемое поле.

Например, для создания поля "Сумма" следует ввести в ячейку в строке "Поле" выражение:   Сумма: [Цена]*[Количество]

При выполнении запроса в поле "Сумма" будет занесено новое значение, найденное путем перемножения содержимого полей "Цена" и "Количество".

Вычисляемые поля позволяют:

-рассчитывать числовые значения и даты;

-комбинировать значения в текстовых полях;

-создавать подчиненные запросы;

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

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

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

При делении денежного значения на любое число возвращается числовое значение со значением свойства <Размер поля> - "С плавающей точкой (8 байт)". Для того чтобы возвращалось значение типа "Денежный", следует указать этот тип в свойстве запроса <Формат поля>.

В Microsoft Access определена специальная функция, Count, обеспечивающая подсчет числа записей в запросе. Для этого следует ввести Count(*) в ячейку в строке полей.

Окно “Область ввода”, выводящееся нажатием клавиш Shift+F2, позволяет просматривать выражения целиком (без прокрутки).

Элементы выражений

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

Оператор =, <, &, And, Or, Like   Определяет операцию, выполняемую над одним или несколькими элементами.

Идентификатор Формы![Заказы]![Заказ] или Отчеты![Счет]. ВыводНаЭкран Задает ссылку на значение поля элемента управления или свойства.

Функция Date, Sum, DLookup Возвращает значение, найденное в результате расчета или другой операции. Access Basic предоставляет пользователю возможность создавать собственные функции.

Литерал 100, #1-янв-94#, "New York"  Представляет значение, например, число, строку или дату, которое используется в Microsoft Access, именно в том виде, как оно записано. Даты заключаются в символы номера (#), а строки в прямые кавычки (").

Константа True, False, Да, Нет, Null    Представляет значение, остающееся неизменным.

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

  •  наименование факультета
  •  номер группы
  •  наименование специальности
  •  номер зачетной книжки
  •  фамилия, имя, отчество

Технология

  1.  В окне База данных выбрать объект  Запросы.
  2.  В появившемся списке выбрать Создание запроса в режиме конструктора.
  3.  В появившемся окне Добавление таблицы добавить таблицы Студент, Группа, Специальность, Факультет, затем закрыть окно.
  4.  При необходимости отрегулировать размеры и расположение окон с таблицами на схеме данных.
  5.  Перетащить соответствующие названия полей из окон таблиц в бланк запроса, расположенный под схемой данных, соблюдая заданный их порядок.

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

  1.  Просмотреть полученную выборку, выполнив команду ВИД/Режим таблицы или щелкнув по кнопке инструментальной панели «Вид».
  2.  Вернуться в режим конструктора запросов в случае, если выборка содержит ошибки, и откорректировать запрос.
  3.  Закрыть запрос. Появится диалоговое окно, в котором следует подтвердить необходимость его сохранения. Присвоить запросу имя «Выборка 1»    

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

Для выполнения этого задания необходимо на бланке запроса добавить поле «Коммерческий» и ввести по нему соответствующее условие.

Задание 4.Составить запрос для подсчета количества коммерческих студентов в каждой группе.

Технология.

  1.  Создать в режиме конструктора новый запрос с использованием таблиц Факультет, Группа, Студент.
  2.  Ввести в 1-ый столбец бланка запроса поле Наименование факультета, во 2-ой столбец – поле Номер группы, в 3-ий столбец  –  поле Коммерческий.
  3.  Установить в качестве условия выборки для 3-го столбца – значение Да.
  4.  Установить для 1-го и 2-го столбцов бланка сортировку по возрастанию.
  5.  Отключить вывод на экран данных 3-го столбца.
  6.  Ввести в 4-ый столбец поле Коммерческий и замените название столбца на Количество коммерческих. Для этого новое и старое названия столбца следует разделить символом «двоеточие», т.е. ячейка с названием поля должна содержать:

Количество коммерческих: Коммерческий

  1.  Щелкнув по кнопке инструментальной панели Групповые операции, добавить в бланк строку Групповая операция и выбрать из списка в этой строке для 4-го столбца операцию Count.
  2.  Просмотреть выборку с подсчетом итогов,  щелкнув по кнопке инструментальной панели Запуск, либо выполнив команду ЗАПРОС/Запуск.
  3.  Вернуться в режим конструктора запросов, щелкнув по кнопке Вид инструментальной панели.
  4.  Сохранить запрос, присвоив ему имя Подсчет коммерческих по группам. 

Задание 5. Составить запрос-выборку для подсчета количества коммерческих студентов по каждому факультету.

Задание 6. Составить запрос, позволяющий увидеть выборку, отражающую количество студентов по каждому факультету и каждой группе. Заголовки столбцов должны соответствовать названиям факультетов, заголовки строк – номерам групп.  Такой вид выборки может быть реализован перекрестным запросом. Для применения подобного запроса желательно иметь в базе данных сведения по 5-6 группам, отнесенным к 3 факультетам.

Технология. 

  1.  Создать с помощью конструктора новый запрос с использованием таблиц Факультет, Группа, Студент.
  2.  Ввести в 1-ый столбец бланка запроса поле Наименование факультета, во 2-ой столбец – поле Номер группы, в 3-ий столбец – поле Коммерческий.
  3.  Выполнить команду ЗАПРОС/Перекрестный запрос или щелкнуть по кнопке инструментальной панели Тип запроса и выбрать из списка Перекрестный запрос.
  4.  Выбрать значения в строке бланка Перекрестная таблица, развернув список в ячейках:
  •  для 1-го столбца заголовок строк, для 2-го столбца – заголовок столбцов, для 3-го столбца – значение.
  1.  Выбрать функцию Count для групповой операции в 3-м столбце.
  2.  Просмотреть перекрестную выборку, щелкнув по кнопке Запуск.
  3.  В режиме таблицы уменьшить ширину столбцов таблицы-выборки. Для этого выделить столбцы с данными по группам и выполнить команду ФОРМАТ/Ширина столбца/По ширине данных.
  4.  Сохранить запрос под именем Количество студентов по факультетам и группам.

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

Задание 8. Составить запрос для вывода списков групп, причем номер группы должен запрашиваться в процессе выполнения запроса. Такой запрос называется запросом с параметром. Параметром является номер группы. Значение параметра вводится в диалоговом окне. Для создания запроса необходимо ввести в ячейку с условием текст условия отбора в квадратных скобках.

Технология.

  1.  Создать с помощью конструктора новый запрос с использованием одной таблицы Студент.
  2.  Ввести в 1-ую строку бланка запроса все поля таблицы.
  3.  Ввести в ячейку строки Условие отбора для поля Номер группы текст: [Введите номер группы].  
  4.  Запустить запрос, и в появившееся диалоговое окно ввести номер одной из групп. Просмотреть выборку. Сохранить запрос, присвоив ему имя Запрос с параметром.

Задание 9. Составить запрос для получения справки по стоимости обучения коммерческих студентов с учетом НДС. Принять, что стоимость обучения с учетом НДС коммерческого студента равна стоимости обучения по выбранной им специальности, умноженной на (1+0,2), где 0,2 – величина НДС.

Такой запрос называется запросом с вычисляемым полем

Технология.

  1.  Создать с помощью конструктора новый запрос с использованием таблиц Специальность, Группа, Студент.
  2.  Ввести в бланк запроса поля: Номер группы, Номер специальности, Фамилия, N зачетки, Коммерческий, Стоимость обучения.
  3.  Ввести в поле Коммерческий условие да и отключить вывод содержимого этого поля на экран.
  4.  Щелкнуть мышью в строке поле следующего свободного столбца бланка и щелкнуть по кнопке инструментальной панели Построить. Будет вызвано окно Построителя выражений.
  5.  Используя поле таблицы Специальность, ввести в окно построителя выражение:  НДС: [Стоимость обучения] * 0,2

где   НДС:  - заголовок столбца с вычисляемым полем,

[Стоимость обучения] – поле таблицы «Специальность,

0,2 – величина НДС.

Для записи выражения следует использовать инструменты построителя выражений. После  ввода выражения щелкнуть по кнопке Ok.

  1.  Щелкнуть мышью в ячейке поля следующего свободного столбца и перейти в окно Построителя выражений. Ввести в этом окне формулу:

Итого:[Стоимость обучения]+[НДС]

Примечание.

Для редактирования выражения в вычисляемом поле запроса можно использовать окно Область ввода, которое вызывается комбинацией клавиш Shift+F2, если ячейка с полем активизирована.

  1.  Включить флажки вывода на экран вычисляемых полей.
  2.  Запустить запрос
  3.  Отладив запрос, сохранить его с именем Запрос с вычисляемым полем.

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

Технология.

  1.  Создать с помощью конструктора новый запрос. Выбрать в качестве исходной таблицы одну таблицу – Специальность.
  2.  Указать тип запроса, выполнив команду ЗАПРОС/Обновление, или, щелкнув правой клавишей по свободному месту схемы данных, выбрать из контекстного меню пункт Тип запроса/Обновление.
  3.  Выбрать поле Стоимость обучения и ввести в него с помощью Построителя выражений в строку Обновление выражение: [Стоимость обучения]*1,5.
  4.  Выполнить запрос и сохранить его, присвоив имя Запрос на обновление. 

Задание 11. Составить запрос на обновление  базы данных. Необходимо создать запрос, при  выполнении которого произойдет уменьшение стоимости обучения по двум специальностям на 10%.

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

  •  Номер  группы;
  •  Код дисциплины;
  •  Фамилия студента;
  •  № зачетной книжки

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

Предварительно необходимо дополнить базу данных еще двумя таблицами Дисциплина и Вспомогательная.

Таблица Дисциплина должна иметь следующий состав полей:

  •  Код дисциплины – ключевое поле, длинное целое;
  •  Наименование дисциплины – текстовое поле, 30 символов;
  •  Лекции (часов) – числовое поле, целое;
  •  Практика (часов) – числовое поле, целое;

Ввести в таблицу Дисциплина 5-6 строк с различными кодами и наименованиями дисциплин.

В таблицу Вспомогательная включить только одно поле:

- Оценка - числовое, байтовое, необязательное.

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

Технология.

  1.  Создать с помощью конструктора новый запрос. Выбрать в качестве исходных  таблицы – Студент, Дисциплина и Вспомогательная.
  2.  Указать тип запроса, выполнив команду ЗАПРОС/Создание таблицы. Присвоить таблице имя Ведомость 1.
  3.  Ввести в бланк запроса поля:
  •  Номер  группы;
  •  Код дисциплины;
  •  Наименование дисциплины;
  •  Фамилия;
  •  № зачетки;
  •  Оценка
  1.  Ввести для поля Номер группы параметрическое условие отбора: [Введите номер группы: ]
  2.  Ввести для поля Код дисциплины параметрическое условие отбора: [Введите код дисциплины: ]
  3.  С использованием запроса создать 6 ведомостей: по двум дисциплинам для каждой из 3-х групп. Номера групп и коды дисциплин задавать в окне ввода параметров. Присвоить созданным таблицам имена Ведомость 1, Ведомость 2 и т.д.

Замечание

1. Поскольку при повторном использовании запроса на создание таблицы ранее созданная таблица удаляется необходимо перед созданием новой таблицы переименовывать ранее созданную таблицу. Для переименования необходимо:

  •  установить вкладку базы данных Таблицы;
  •  выделить исходную таблицу и вызвать правой кнопкой мыши контекстное меню;
  •  выполнить пункт Переименовать. 

2. При создании ведомостей для всех трех групп необходимо выбирать один и тот же набор из двух дисциплин (например, каждая из трех групп сдает Дисциплину 1 и Дисциплину 2).

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

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

Технология.

  1.  Создать в окне базы данных на вкладке Таблицы копию таблицы Ведомость 1 и заменить ее имя на Общая ведомость  (щелчок правой клавишей по имени таблицы и вызов соответствующей команды из контекстного меню).   
  2.  Создать с помощью конструктора новый запрос. Выбрать в качестве исходной таблицу Ведомость 2.
  3.  Указать тип запроса, выполнив команду ЗАПРОС/Добавление. В окне Добавление ввести имя таблицы Общая ведомость, к которой должны добавляться строки исходной таблицы.
  4.  Ввести в строку Поле бланка запроса все поля таблицы Ведомость 2.
  5.  Закрыть запрос, присвоив ему имя Запрос на добавление.
  6.  Выполнить запрос и просмотреть таблицу Общая ведомость, которая должна пополниться записями из таблицы Ведомость 2.
  7.  Временно переименовывая другие ведомости в Ведомость 2, добавить их содержимое в Общую ведомость.

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

Технология

  1.  Создать с помощью конструктора новый запрос. Выбрать в качестве исходной  таблицу Общая ведомость.
  2.  Указать тип запроса, выполнив команду ЗАПРОС/Удаление. 
  3.  Ввести в бланк запроса поля Номер группы  и Код дисциплины.
  4.  Ввести в строку Условие отбора соответственно для каждого из полей параметрические условия:

[Введите номер группы:] и [Введите код дисциплины: ]

  1.  Закрыть и сохранить запрос под именем Запрос на удаление.
  2.  Выполнить запрос и  просмотреть результаты его выполнения.
  3.  Воспользовавшись таблицами Ведомость 1 – Ведомость 6 и Запросом на добавление добавить в Общую ведомость удаленную часть.

Лабораторная работа № 3.  Конструирование форм

Цель работы: Освоение технологии конструирования и использования форм.

Общие сведения о формах Access.

Форма является удобным средством для просмотра БД, а также для ввода данных и их корректировки. Форма обычно отображает поля одной строки таблицы или запроса. В форме можно отображать данные нескольких таблиц или запросов. Применение форм позволяет упростить ввод данных в БД и уменьшить количество допускаемых ошибок ввода. Для этого форма снабжается:

- форматами ввода;

- условиями проверки вводимых данных;

- масками ввода для ввода стандартизованной информации;

- пояснительным текстом;

  •  группировкой данных, приближающей ее вид к бумажному бланку.

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

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

Область данных содержит записи. На экране одновременно их может располагаться от одной до нескольких.

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

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

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

При проектировании формы можно использовать текстовые строки, рисунки и линии, кнопки, списковые окна и т.п. Форма проектируется при помощи панели элементов.

В форме может быть разрешено или запрещено корректировать определенные данные или вообще ввод новых записей.

С формой можно работать в 3-х режимах:

- в режиме конструктора;

- в режиме формы;

- в режиме таблицы.

Основные виды форм:

- простая форма по одной таблице;

- простая форма по связанным таблицам;

- простая форма на основании запроса;

- форма со списком или с полем для списка;

- составная форма;

- форма-меню с кнопками управления.

Составная форма содержит главную форму и подчиненные ей формы. Подчиненные формы позволяют решать следующие задачи:

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

- обновлять записи в разных таблицах с помощью одной формы.

При необходимости, пользователь имеет возможность изменить макет подчиненной формы.

Допускается вставка в главную форму нескольких подчиненных форм.

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

Если для свойства формы “Допустимые режимы” задано значение "Все", то в режиме формы для главной формы допускается вывод подчиненной формы, как в режиме формы, так и в режиме таблицы.

Для перехода в режиме формы из подчиненной формы в главную следует щелкнуть мышью  на любом поле в главной форме. Для перехода из главной формы в подчиненную следует щелкнуть мышью на любом месте подчиненной формы (или выбрать имя подчиненной формы в раскрывающемся списке "Переход к полю" на панели инструментов).

Примечания

1.Для отказа от данных только что введенных в запись необходимо 2 раза нажать клавишу Esc.

2.В режиме конструктора форму можно преобразовать в отчет, если выполнить команду ФАЙЛ/Сохранить как отчет.

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

Технология.

  1.  Выбрать в окне базы данных в списке объектов Формы и выбрать режим Создание формы с помощью мастера.
  2.  В окне Создание форм в списке Таблицы и запросы выбрать таблицу Студент, в списке  доступных полей выбрать все поля таблицы и нажать кнопку Далее.
  3.  Выбрать внешний вид формы В один столбец и нажать кнопку Далее.
  4.  Выбрать стиль оформления Диффузный и нажать кнопку Далее.
  5.  Присвоить имя форме: Студент – простая и нажать кнопку Готово.
  6.  Просмотреть информацию таблицы с помощью созданной формы.
  7.  Выполнить редактирование формы – ввести заголовок формы «Список студентов». Для этого:   
  •  установить режим Конструктор;
  •  с помощью вертикальной линейки создать достаточную область для ввода заголовка формы;
  •  выбрать на панели элементов Надпись и отметить границы надписи в области заголовка, ввести текст в рамку надписи;
  •  выделить рамку надписи и отрегулировать ее размер и местоположение;
  •  выделить надпись и установить необходимый размер и начертание шрифта.
  1.  Просмотреть форму, щелкнув по кнопке инструментальной панели Вид и установив режим Форма.
  2.  Закрыть форму, сохранив изменения.

Задание 2. Создать простую форму по связанным таблицам. Создать форму для просмотра перечня групп с расшифровкой специальностей и названий факультетов. Для создания формы – использовать 3 таблицы: Группа, Специальность и Факультет.

Технология.

  1.  В окне базы установить объект Формы и выбрать режим Создание формы с помощью мастера.
  2.  В окне Создание форм выбрать из таблицы Группа поле Номер группы, из таблицы Факультет - поле Наименование факультета, из таблицы Специальность – поля Номер специальности и Наименование специальности. Нажать кнопку Далее.
  3.  На следующем  шаге  выбрать тип представления данных:  Группа. Нажать кнопку Далее.
  4.  Выбрать внешний вид формы – ленточный и стиль – камень. Нажать кнопку Далее.
  5.  Присвоить имя форме – Группа, простая по 3 связанным таблицам. Нажать кнопку Готово. Просмотреть данные, выводимые в форму.

Задание 3. Создать простую форму на основании запроса. Необходимо создать форму по запросу с вычисляемым полем (запрос для получения справки по стоимости обучения коммерческих студентов с учетом НДС - задание 9 лабораторной работы №2), включив в форму все поля запроса. Порядок действий аналогичен порядку, рассмотренному в предыдущем задании. В пункте 5 следует выбрать иной внешний вид формы – выровненный. Присвоить форме имя Простая форма по запросу.

Задание 4. Создать форму со списком. Необходимо создать форму, позволяющую просматривать и редактировать данные каждого студента. Форма должна быть снабжена списком групп,

Рис.2.  Форма со списком

в котором для каждой группы указывается ее номер специальности и наименование факультета (см.рис.2). Для создания формы следует использовать таблицу Студент и специально подготовленный запрос Группа-факультет.

Образец формы приведен на рисунке.

Технология.

  1.  Подготовить запрос на выборку по таблицам Группа, Факультет, включив в него поля:
  •  № группы;
  •  № специальности;
  •  Наименование факультета.

Присвоить запросу имя Группа-факультет.

  1.  Создать с помощью Мастера форм простую форму в один столбец по таблице Студент, включив в нее поля:
  •  Фамилия;
  •  № зачетки;
  •  Дата рождения;
  •  Коммерческий.
  1.  В окне Создание форм присвоить форме имя Форма с полем для списком. Включить флажок Изменение макета формы для непосредственного перехода в режим конструктора. Для ознакомления с приемами конструирования форм  включить флажок Вывести справку по работе с формой.
  2.  В режиме конструктора переместить вниз горизонтальную линию, разделяющую области данных и примечания.
  3.  Выбрать на панели элементов конструирования формы элемент Поле со списком. Указать мышью местоположение окна для списка в области данных формы. Высота окна должна быть равна высоте кнопки, раскрывающей список. В окне Создание полей со списком нажать кнопку Отмена.
  4.  Разъединить контуры названия поля и самого поля, перетаскивая мышью левый верхний маркер одного из контуров. Заменить название поля в контуре надписи на Группа.
  5.  Щелкнуть правой клавишей мыши внутри контура поля со списком и выбрать в контекстном меню пункт Свойства. Появится окно Поле со списком для настройки свойств поля. Выполнить настройку. Для этого:
  •  установить вкладку Данные в окне  Поле со списком;
  •  в строке данные раскрыть список полей и выбрать поле Номер группы;
  •  в строке тип источника строк выберите таблица/запрос;
  •  в строке источник строк выбрать запрос Группа-факультет;
  •  на вкладке макет указать число столбцов - 3, заглавия столбцов – да, ширина списка – 6 см, ширина столбцов – 1; 1,2; 3.
  1.  Просмотреть готовую форму, щелкнув по кнопке Вид инструментальной панели. Сохранить форму под именем Форма со списком.

Задание 5. Создать составную форму. Составная форма должна содержать главную форму и одну подчиненную форму. Необходимо создать форму для вывода информации о группе см.рис.3).

Рис.3. Составная форма

Главная форма должна содержать общие сведения о группе:

  •  номер группы;
  •  номер специальности;
  •  наименование специальности;
  •  наименование факультета.

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

  •  фамилия;
  •  номер зачетной книжки;
  •  дата рождения;
  •  коммерческий.

Технология.

  1.  Создать простой запрос на выборку Группа-специальность-факультет, включив в него необходимые поля.
  2.  Создать с помощью Мастера форм табличную форму с именем Подчиненная со списком студентов из таблицы Студент.
  3.  Создать с помощью Мастера форм главную форму на основе запроса Группа-специальность-факультет, включив  в нее все поля. Главную форму создать в один столбец обычным стилем, используя тип представления данных Группа. Включить флажок Изменение макета формы.
  4.  В режиме конструктора:
  •  увеличить область для размещения данных;
  •  ввести элемент Надпись с текстом Список студентов;
  •  ввести под надписью элемент Подчиненная форма/отчет;
  •  вызвать щелчком правой клавиши мыши окно настройки свойств элемента;
  •  на вкладке данные выбрать объект-источник – форма  с именем Подчиненная;
  •  щелкнув по строке Подчиненные поля, вызвать кнопку настройки и далее повторным щелчком по кнопке настройки вызвать окно Связь с полями подчиненной формы;
  •  в окне Связь с полями подчиненной формы определить связь межу главным и подчиненным полями через поле Номер группы;
  •  на вкладке макет установить размер окна подчиненной формы: ширина – 7, высота – 8 и закрыть окно настройки свойств;
  •  удалить лишнюю надпись встроенный объект, щелкнув внутри контура надписи и затем нажав клавишу Delete.
  1.  Перейти в режим формы и, используя 2 горизонтальные линейки прокрутки, просмотреть перечень и списки групп.
  2.  Переходя из режима форма в режим конструктор и обратно, отрегулировать размеры и местоположение полей главной и подчиненной формы, изменить размеры и типы шрифтов и линий.

Задание 6. Создать составную форму для просмотра состава групп следующего вида (см.рис.4).

Особенности формы:

  •  отключены линейки прокрутки;
  •  для перехода от группы к группе введены пользовательские кнопки Стрелка вниз и Стрелка вверх;
  •  в форме выводится количество студентов в группе.

Для создания такой формы необходимо подготовить:

  1.  Запрос с итогом по количеству студентов в группах на основании таблиц Факультет, Специальность, Группа, Студент, включив  в него поля Наименование специальности,  Наименование факультета, Номер группы, Номер курса и итоговое поле Количество студентов в группе.
  2.  Подчиненную форму Студент подчиненная в табличном виде, содержащую список студентов, и подготовленную на основании таблицы Студент 

Рис.4. Составная форма Состав группы

Технология.

  1.  Создать сначала главную форму. Для этого на вкладке Формы щелкните по кнопке Создать
  2.  Выбрать в качестве источника данных запрос с итогами Факульт-Спец-Группа-Кол-во_студ
  3.  В окне конструктора форм, находясь в области данных, разместить поля и их названия, последовательно щелкая по кнопке аб, расположенной на панели элементов.
  4.  Установить связь между полями формы и запросом. Для этого, щелкнув внутри контура поля правой клавишей, вызвать контекстное меню и выбрать в нем пункт Свойства. Появится окно с названием Поле. Установить вкладку Данные и в строке Данные выбрать из раскрывающегося списка имя соответствующего поля.
  5.  Включив сетку командой ВИД/Сетка, выровнять местоположение и размеры полей и подписей к ним в соответствии с образцом.
  6.  Переключившись в режим формы, проверить вид формы и при необходимости откорректировать ее в режиме конструктора. Увеличить ширину и высоту области данных.
  7.  Ввести в главную форму заранее подготовленную починенную форму Студент_подчиненная. Для этого щелкнуть на панели элементов по кнопке Подчиненная форма/Отчет и очертить мышкой при нажатой левой клавише ее прямоугольный контур. После отпускания клавиши мыши появится окно с заголовком Создание подчиненных форм и отчетов. Включить в нем флажок Формы и выбрать в раскрывающемся списке имя формы Студент_подчиненная. Щелкнуть по кнопке Готово.
  8.  Установить связь главной и подчиненной форм по полю Номер группы. Для этого вызовите окно свойств подчиненной формы и на вкладке Данные щелкнуть в строке Подчиненные поля, а затем по кнопке вызова построителя. Появится окно Подчиненная форма/отчет. Установить в этом окне значение Номер группы для Главного и Подчиненного полей.
  9.  Отключить в главной форме линейки (полосы) прокрутки, поле с номером записи и область выделения.  Для этого открыть окно свойств формы, щелкнув по кнопке Свойства на инструментальной панели. Установить вкладку Макет и изменить на ней значения соответствующих свойств.
  10.  Дать новое название создаваемой формы Состав групп. Для этого на вкладке Макет в окне свойств формы ввести значение Состав групп в свойство Подпись.
  11.  Создать в форме кнопку со стрелкой для перехода к следующей группе студентов. Для этого включить мастера панели элементов, щелкнув по соответствующей кнопке этой панели. Выбрать на панели элемент Кнопка и очертить контур будущей кнопки при нажатой левой клавише  мыши. После отпускания клавиши мыши появится окно Создание кнопок.
  12.  В окне Создание кнопок выбрать категорию Переходы по записям, а в нем - действие Следующая запись. Щелкнуть по кнопке Далее. В следующем окне включить флажок Показать все рисунки и выбрать рисунок Стрелка вниз (черная). Щелкнуть по кнопке Далее. Не изменяя имя, предложенное Access, щелкнуть по кнопке Готово. Перейти в режим формы и проверить правильность работы кнопки. При ее нажатии должен происходить вывод сведений о следующей группе.
  13.  Аналогичным образом создать кнопку с рисунком Стрелка вверх (черная) для перехода к предыдущей записи.

Примечание.

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

Задание 7. Создать форму с вкладками для просмотра списка студентов двух групп. На каждой вкладке должен располагаться список студентов только одной группы. На ярлычках вкладок должны отображаться номера групп.  

Технология.

  1.  С помощью мастера создать два простых запроса, например Запрос_Студент 651 и Запрос_Студент 652, для выборки из таблицы Студент списка студентов двух групп (651 и 652). Включить в запросы все поля таблицы. В режиме конструктора ввести в запросы условия отбора записей соответственно для групп 651 и 652.
  2.  С помощью мастера создать две табличные формы, например Форма_Студент 651 и Форма_Студент 652 на основании запросов Запрос_Студент 651 и Запрос_Студент 652 с использованием обычного стиля.
  3.  Создать с помощью конструктора новую форму (главную), не используя в качестве источника таблицу или запрос. В окне конструктора щелкнуть по элементу Набор вкладок и очертить мышкой прямоугольный контур в области данных.
  4.  Заменить названия ярлычков вкладок. Для замены имени ярлычка необходимо  щелкнуть по ярлычку правой клавишей мыши и выбрать в контекстном меню пункт Свойства, а затем ввести в окне свойств элемента новое имя на вкладке Другие.
  5.  Выделить первую вкладку и разместить на ней подчиненную форму Форма_Студент 651. Удалить связанную с подчиненной формой подпись формы.
  6.  Выделить вторую вкладку и разместить на ней подчиненную форму Форма_Студент 652. Удалить связанную с подчиненной формой подпись формы.
  7.  Просмотрить форму в режиме формы. Удалить полосу прокрутки, поле с номером записи и область выделения в главной форме. Отрегулировать размеры починенных форм. Внешний вид формы, которая должна быть получена представлена на рис.5.
  8.  Сохранить форму с именем Состав групп - форма с вкладками.

Рис.5. Форма с вкладками

Лабораторная работа № 4. Конструирование отчетов

Цель работы: Конструирование и использование отчетов.

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

Основные виды отчетов:

- одноколонный (простой) отчет;

- многоколонный отчет;

- табличный отчет;

- отчет с группировкой данных и подведением итогов;

- отчет по связанным таблицам;

- связанный отчет, т.е. отчет, содержащий другой (подчиненный отчет);

- отчет слиянием с Word (составной документ);

- перекрестный отчет.

Основные разделы отчета

- заголовок отчета (начало отчета);

- верхний колонтитул (печатается в начале каждой страницы);

- область заголовка группы (отображается перед первой записью каждой группы);

- область данных (основная часть отчета);

- область примечания группы (отображается после области данных последней записи каждой группы);

- нижний колонтитул (печатается в конце каждой страницы);

- область примечаний (печатается в конце отчета).

В режиме конструктора доступны кнопки панели инструментов и пункты меню Вид:

- Сортировка и группировка;

- Список полей;

- Свойства.

Окно сортировки и группировки позволяет определить условия сортировки и группировки данных в отчете. В ячейках верхней половины окна указываются поля, по которым выполняется сортировка, и порядок сортировки. Ячейки нижней половины окна позволяют задать условия группировки. Допускается выполнение сортировки записей без их группировки; однако, условия группировки можно задать только для сортируемых полей или выражений.

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

Столбец "Порядок сортировки". В ячейках выбирают порядок сортировки "По возрастанию" или "По убыванию" для поля или выражения в этой строке.

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

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

Для построения многоколонного отчета выполнить в режиме «Конструктор»:

- команду ФАЙЛ/Настройка печати;

- нажать кнопку Дополнительно;

- в поле ввода  По горизонтали указать количество элементов (колонок)

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

  •  Наименование факультета;
  •  № группы;
  •  № специальности;
  •  ФИО;
  •  № зачетки.

Технология

  1.  Модифицировать запрос Выборка 1, открыв его в режиме конструктора и заменив поле Наименование специальности на поле Номер специальности, а поле Номер факультета на поле Наименование факультета.
  2.  На вкладке Отчеты в окне базы данных щелкнуть по кнопке Создать.
  3.  В окне Новый отчет выбрать Мастер отчетов и в качестве источника данных – запрос Выборка 1. Щелкнуть по кнопке Ok.
  4.  В окне «Создание отчета» выполнить следующие шаги:
  •  выбрать все поля запроса, но в заданной по условию последовательности;
  •  выбрать тип представления данных по факультету;
  •  добавить уровень группировки по № специальности;
  •  выбрать порядок сортировки по полю ФИО;
  •  выбрать макет ступенчатый;
  •  выбрать тип заголовка спокойный;
  •  присвоить имя отчету Отчет табличный;
  •  нажать кнопку Готово. Просмотреть отчет. Уменьшить масштаб отображения.
  1.  Перейти в режим конструктора. Выполнить редактирование заголовка отчета. Ввести названия столбцов в 2 строки, отцентрировав их и увеличив соответственно высоту области верхнего колонтитула.
  2.  Переместить поля в области данных в соответствии с расположением заголовков столбцов в области верхнего колонтитула.
  3.  Изменить заголовок отчета, введя текст: Список студентов по факультетам и специальностям.
  4.  Ввести надпись в нижний колонтитул: «Ведомость составил:».
  5.  Закрыть и сохранить отчет.

Задание 2. Создать отчет с группировкой данных и подведением итогов, используя запрос на выборку, подготовленный в задании №9 лабораторной работы №2 с именем Запрос с вычисляемым полем. В отчете необходимо подсчитать общую суммарную плату за обучение коммерческих студентов и в том числе по каждой специальности и по каждой группе. Отчет должен содержать поля:

  •  № специальности;
  •  № группы;
  •  Фамилия;
  •  № зачетки;
  •  Стоимость обучения с учетом НДС (Итого).

Технология

  1.  Создать с помощью мастера новый отчет на основании указанного выше запроса. Включить в запрос необходимые поля в заданном порядке.
  2.  В окне создания отчета выполнить следующие шаги:
  •  выбрать тип представления данных - по таблице студент;
  •  добавить уровни группировки по специальности и группе;
  •  настроить режим сортировки в алфавитном порядке фамилий и, щелкнув по кнопке Итоги, включить флажки Sum и показать  данные и итоги;
  •  выбрать макет отчета Структура 1;
  •  выбрать сжатый стиль заголовка;.
  1.  Нажать кнопку Готово и просмотреть отчет.
  2.  Закрыть отчет и сохранить его под именем Отчет с группировкой.
  3.  Выполнить редактирование отчета, используя режим конструктора. Отредактировать заголовок отчета, ширину столбцов. Изменить шрифты отдельных элементов отчета. Заменить слово sum в итоговых строках на слово Всего:. Изменить ширину полей с итогами.

Задание 3. Создать перекрестный отчет, используя таблицу Общая ведомость, созданную в лабораторной работе №3. Отчет должен иметь следующие столбцы:

  •  № группы
  •  ФИО студента;
  •  № зачетки;
  •  Наименование дисциплины 1;
  •  Наименование дисциплины 2;
  •  Средний балл.

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

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

Технология.

  1.  С помощью мастера создать перекрестный запрос по таблице Общая  ведомость, в которой должны иметься оценки для всех студентов 3-х  групп по 2-м дисциплинам. Для этого:
  •  выбрать поля  Номер группы, Фамилия, № зачетки в качестве заголовков строк;
  •  выбрать поле Наименование дисциплины в качестве заголовка столбцов;
  •  снять флажок Да вычисления итоговых значений для каждой строки;
  •  выбрать для вычислений поле Оценка и функцию вычисления First (Первый); 
  •  сохранить запрос, присвоив ему имя Запрос для отчета по среднему баллу.
  1.  С помощью мастера создать отчет по запросу Запрос для отчета по среднему баллу
  2.  Выбрать все поля запроса.
  3.  Добавить уровень группировки по номеру группы.
  4.  Выбрать порядок сортировки по фамилиям студентов.
  5.  Выбрать ступенчатый вид отчета и деловой заголовок.
  6.  Присвоить отчету имя Отчет по среднему баллу. Просмотреть отчет.
  7.  Перейти в режим конструктора и отрегулировать ширину столбцов и их местоположение.
  8.  Выровнять оценки по центру столбца. Для этого выделить поля и нажать соответствующую кнопку инструментальной панели Формат отчета.
  9.  С помощью инструментальной панели элементов ввести в правую часть области верхнего колонтитула надпись Средний балл.
  10.  С помощью инструментальной панели элементов ввести в область данных поле для  вычисления среднего балла. Щелкнуть правой кнопкой мыши внутри созданного поля и вызвать окно Свойства поля.
  11.  Установить вкладку Данные. Для вызова построителя выражений на вкладке Данные в строке данные щелкнуть по кнопке «…».
  12.  Используя инструменты построителя, ввести в поле формулу расчета среднего балла:

=([наимен. дисц.1]  + [наимен. дисц.2] ) / 2

Примечание.

Наименования дисциплин вставляются в формулы двойным щелчком левой клавиши мыши по соответствующей строке в подокне  построителя выражений Отчет.

  1.  Просмотреть и отредактировать отчет. Удалить дублирующее название вычисляемого поля в области данных. Отрегулировать формат, местоположение и выравнивание вычисленного значения. Установить для поля Средний балл вкладку Макет и выбрать формат поля фиксированный с 2-мя дробными знаками.
  2.  Выполнить условное форматирование поля Средний балл. Выделить красным цветом значения среднего балла, большие 4,5 и синим цветом – меньшие 3,5. Для этого в режиме конструктора выделить поле Средний балл, выполнить команду Формат/Условное форматирование. В окне Условное форматирование задать: Условие 1 – Значение поля, операция сравнения – больше, значение для сравнения – 4,5. В строке задания формата установить цвет текста – красный, нажать кнопку Добавить>>. Повторить указанные действия для Условия 2, установив синий цвет для всех значений поля, меньших 3,5.
  3.  Для подсчета среднего балла по группе добавить в окне конструктора раздел Примечание группы. Для этого вызвать окно настройки Сортировка и группировка, щелкнув по соответствующей кнопке инструментальной панели, или выполнить команду ВИД/Сортировка и группировка. В окне  Сортировка и группировка включить отображение области примечаний группы.
  4.  В области примечаний группы в столбце, соответствующем среднему баллу студента, создать вычисляемое поле. Вызвать окно настройки свойств поля. Установить вкладку Данные. В строке данные вызвать Построитель выражений. Ввести формулу вычислений:

=Sum (([наимен. дисц.1] + [наимен. дисц.2] ) / 2 / Count([ФИО])

Примечание.

Для ввода названий функции групповых операций Sum и Count дважды щелкнуть по строке Функции в левом подокне Построителя выражений, а затем двойным щелчком раскрыть список встроенных функций. В среднем подокне выбрать категорию Статистические функции и затем в правом подокне нужную функцию.

Для вставки в качестве аргументов функции нужных полей выбрать в левом подокне "Отчет…", а в среднем  подокне - "Список полей" и двойным щелчком вставлять в формульное выражение имена полей из правого подокна.

  1.  Удалить контур с надписью вычисляемого поля. Отрегулировать формат, местоположение и выравнивание вычисленного значения аналогично пункту 14.
  2.  Создать слева от значения среднего балла группы надпись "Средний балл группы NNN", где NNN - номер группы. Для этого в область примечаний группы вставить поле и ввести в него формулу:

="Средний балл группы  "  & [Номер группы]

  1.   Подсчитать в отчете средний балл по всем группам. Для этого вставить вычисляемые поля аналогично пунктам 17, 18 и 19  в область примечаний отчета.
  2.  Присвоить отчету имя Расчет среднего балла групп. Ввести в заголовок отчета дату формирования отчета. Для этого вставьте под строкой с названием отчета поле и ввести в него с помощью построителя выражений функцию =DATE(). Используя Маску ввода, установить пользовательский  формат даты в виде: dd\.mm\.yyyy.
  3.  Для того чтобы сделать отчет более компактным, перенести номер группы из области заголовка группы в область данных. Для этого отключить область заголовка группы, вызвав окно Сортировка и группировка. Вставить поле Номер группы в соответствующий столбец области данных. Настроить свойства поля. Для того, чтобы номер поля не повторялся в каждой строке отчета установить на вкладке Макет свойство Не выводить повторы  в состояние Да. Примерный вид отчета представлен на рис.6.

Рис.6. Перекрестный отчет

Создание связанного  отчета

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

- вывести сводные данные или конкретные записи, связанные с содержимым главного отчета;

-объединить несколько независимых отчетов в главном отчете.

Для создания подчиненного отчета следует:

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

2. Открыть главный отчет в режиме конструктора.

3. Перейти в окно базы данных, например, нажатием клавиши F11.

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

5 . Если в подчиненном отчете должны выводиться данные, связанные с главным отчетом, установить связь элемента управления подчиненного отчета с главным отчетом.

Для связывания подчиненного отчета с главным отчетом открыть бланк свойств подчиненного отчета и задать значения свойств Подчиненные поля и Основные поля. По возможности, значения этих свойств задаются автоматически средствами Microsoft Access.

Например, подчиненный отчет - это отчет, содержащий сведения о студентах, сгруппированные по номерам групп. Главный отчет - это отчет, содержащий сведения о группах (номер, факультет, специальность). Связь осуществляется через поле “Номер группы”. Главный отчет создается мастером на базе запроса “Факультет-группа” как простой отчет. Подчиненный отчет также создается мастером на базе таблицы “Студент” как отчет с группировкой.

Примечание

Допускается вставка в отчет подчиненной формы. Главный отчет может содержать несколько подчиненных отчетов или подчиненных форм.

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

Уровень 1          Уровень 2

Подчиненный отчет 1             Подчиненный отчет 2

Подчиненный отчет 1  Подчиненная форма 1

Подчиненная форма 1  Подчиненная форма 2

Задание 4. Создать связанный  отчет, состоящий из главного и подчиненного отчетов. В качестве главного отчета создать отчет на базе запроса по связанным таблицам «Факультет-группа». В качестве  подчиненного отчета использовать табличный отчет по таблице «Группа».

Технология.

  1.  Создать с помощью мастера ступенчатый подчиненный отчет по таблице Студент, сгруппированный по номерам групп и упорядоченный фамилиям студентов,  включив  в него поля:
  •  Номер группы;
  •  Фамилия;
  •  № зачетки.

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

3. Создать с помощью мастера ступенчатый главный отчет по запросу Группа-Факультет, упорядоченный по факультетам и номерам  групп,  включив  в него поля:

  •  Наименование факультета;
  •  Номер группы;
  •  Номер специальности.

4. Сохранить отчет, присвоив ему имя «Главный». Отредактировать в режиме конструктора. Уменьшить ширину столбцов и длину линий обрамления заголовка.

5. Включить режим конструктора для  главного отчета. Не закрывая окна конструктора отчета Главный, перетащить мышью из окна базы данных (вкладка: отчеты) значок отчета Подчиненный в область данных главного отчета.

  1.  Щелкнуть правой кнопкой мыши по полю Подчиненный и вызвать окно свойств поля. На вкладке данные обеспечьте связь между подчиненным и основным отчетом по полю Номер группы.
  2.  Просмотреть получившийся связанный отчет. Вернуться в режим конструктора и окончательно отредактировать отчет.

Лабораторная работа № 5. Создание макросов

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

Автоматизация управления базой данных

Макросы

Макросы удобно использовать для упрощения работы с БД. С помощью макросов можно выполнять определенные действия, например открытие какой либо формы, таблицы или отчета, выход из Aсcess и др.  

Макросу присваивается заданное пользователем имя. Если макрос имеет стандартное имя Autoexec, то такой макрос выполняется автоматически сразу после загрузки Aсcess. Макросы состоят из макрокоманд. Макрокоманды записываются в строки первого столбца таблицы. Второй столбец служит для записи примечаний.

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

При создании формы в нее можно поместить кнопку запуска макроса. Это можно сделать буксировкой имени макроса из окна БД непосредственно в окно конструктора формы.

Задание 1. Создать форму-меню с кнопками для выполнения ранее составленных запросов, вывода форм и отчетов, а также выхода из Aсcess. Форма-меню должна появляться автоматически сразу после загрузки базы данных. В форме должна быть также кнопка для закрытия базы данных и выхода из Aсcess.

Технология.

  1.  Создать макрос для автозагрузки формы-меню. Для этого:

- в окне базы данных в списке объектов выбрать Макросы. Щелкнуть по кнопке Создать.

- в окне макроса в столбце Макрокоманды развернуть список и выбрать макрокоманду Открыть форму.   

- в строку Имя формы ввести текст: МЕНЮ.

- закрыть и сохранить макрос, присвоив ему имя Autoexec.

  1.  Создать макрос для  выхода из Access. Для этого:
  •  создать макрос из одной команды Выход, присвоив ему имя Выход из Aсcess;
  •  выбрать в строке параметры значение Сохранить все.
  1.  Создать макрос для  выполнения запроса Подсчет коммерческих по группам. Для этого:
  •  создать макрос из одной команды Открыть запрос;
  •  в строку Имя запроса выбрать название запроса из раскрывающегося списка;
  •  закрыть и сохранить макрос, присвоив ему имя Запрос по коммерческим в группах.
  1.  Аналогичным образом создать макросы из одной команды для открытия одной из ранее созданных форм и одного отчета.
  2.  Создать с помощью конструктора новую форму-меню. Появится форма, содержащая только одну область данных. Добавить область заголовка формы. Для этого щелкнуть правой клавишей мыши в области данных формы и выбрать соответствующий пункт в контекстном меню. Сократить до нуля область примечаний.
  3.  Ввести в область заголовка формы надпись База данных СПбГИЭУ. Подобрать оптимально местоположение надписи, ее размер и шрифт.
  4.  Расположить окна формы МЕНЮ и вкладки Макросы базы данных сверху вниз. Перетащить мышью все созданные значки макросов (кроме макроса Autoexec) из вкладки окна базы данных в окно формы МЕНЮ. Подобрать надлежащий размер и местоположение значков, превратившихся  в кнопки меню.
  5.  Проверить работу меню. Сохранить форму и присвоить ей имя МЕНЮ.
  6.  Отключить вертикальные и горизонтальные полосы прокрутки, область выделения и поле номера записи. Для этого в режиме конструктора форм открыть окно свойств формы, щелкнув по кнопке инструментальной панели с соответствующим названием. Установить в окне вкладку Макет. Установить значение для свойства Полосы прокрутки - Отсутствуют. Установить для свойств Область выделения и Кнопки перехода значение - Нет.
  7.  Закрыть базу данных и вновь ее загрузить. Форма МЕНЮ должна автоматически появиться на экране.

Создание пользовательского ниспадающего меню

Задание 2. Создать ниспадающее меню для управления базой данных, содержащее категории "Формы" и "Отчеты" с пунктами (командами) для открытия ранее составленных форм и отчетов.

Технология

  1.  Для создания новой строки меню открыть окно Настройка. Для этого выполнить команду ВИД/Панели инструментов/Настройка или,  щелкнув правой клавишей по любой панели инструментов, выбрать в контекстном меню пункт Настройка.
  2.  В окне Настройка на вкладке Панели инструментов щелкнуть по кнопке Создать.
  3.  В окне Создание панели инструментов ввести имя панели инструментов: Управление базой данных. Перетащить созданную панель инструментов в область меню Access.  
  4.  В окне Настройка нажать кнопку Свойства и определить тип созданной панели - Строка меню. Закрыть окно установки свойств.
  5.  Добавить в меню категорию Формы. Для этого в окне Настройка открыть вкладку Команды и в списке категорий щелкнуть по категории Новое меню. Перетащить команду Новое меню из списка команд в правом подокне на строку меню Управление базой данных. Не закрывая окна Настройка, щелкнуть правой клавишей в строке меню по категории Новое меню и в контекстном меню заменить имя категории на Формы.
  6.  Добавить в меню категорию Отчеты аналогично пункту 5.
  7.  Аналогично добавить в меню Формы новое подменю, назвав его Простые.
  8.  В окне Настройка на вкладке Команды выделить категорию Все формы. Перетащить строку с названием одной из созданных ранее форм  - Формы с полем для списка, Главной или Подчиненной в область команд (пунктов) категории Формы на панели Управление базой данных. Включив контекстное меню новой команды, установить стиль отображения - Только текст.
  9.  Аналогично добавить в область команды категории Формы/Простые пункты с названием форм – Студент-простая, Группа и Простая форма по запросу.
  10.  Аналогично добавить в меню Управление базой данных в  категорию Отчеты пункты с названиями  отчетов. Закрыть окно Настройка. Проверить работу меню.
  11.  Выполнить команду СЕРВИС/Параметры запуска и установить следующие параметры запуска при открытии базы данных:
  •  Ввести в качестве заголовка приложения название Академия;
  •  Выбрать в качестве строки меню строку Управление базой данных.
  •  Отменить вывод на экран окна базы данных, полного набора меню Access, стандартных панелей инструментов.
  1.  Закрыть окно Параметры запуска. Закрыть базу данных, затем повторно открыть и убедиться в правильной работе команд меню.
  2.  Восстановить для базы данных Академия отображение окна базы данных, полного набора меню Access, стандартных панелей инструментов. Для этого перезагрузить базу данных и при повторном открытии держать нажатой клавишу SHIFT. Выполнить команду СЕРВИС/Параметры запуска и восстановить исходное состояние флажков.

Лабораторная работа № 6. Конструирование SQL запросов

Цель работы: Конструирование и использование SQL запросов к базе данных

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

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

Запросы делятся на:

  •  QBE-запросы (Query By Example – запрос по образцу), которые строятся с помощью конструктора в окне конструктора запросов;
  •  SQL-запросы, написанные на языке SQL. SQL (Structured Query Language) – структурированный язык запросов.

В Access 2000 используется версия SQL – Jet SQL (Microsoft Jet Database Engine SQL).

Все запросы можно разделить на 2 группы:

- запросы-выборки;

- запросы-действия.

Запросы-выборки извлекают данные из таблиц в соответствии с заданными условиями

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

Задание 1. Просмотреть запрос Выборка 2, созданный в лабораторной работе №2, в командах SQL.

Технология

  1.  В окне База данных выбрать объект Запросы, выбрать из списка запрос Выборка 2 и щелкнуть по кнопке Конструктор.
  2.  В окне конструктора запросов выполнить команду ВИД/Режим SQL.

Появится окно SQL, содержащее эквивалентную QBE-запросу инструкцию SQL.

Задание 2. Изменить структуру запроса Выборка 2, удалив из запроса поле Номер зачетной книжки.

Технология

  1.  Открыть запрос Выборка 2 в режиме SQL.
  2.  В окне запроса из списка полей следующих за инструкцией SELECT удалить поле -
    Студент.[Номер зачетной книжки].
  3.  Нажать на панели инструментов кнопку Запуск для выполнения запроса. Проанализировать результат.
  4.  Выбрать команду ВИД/Конструктор и просмотреть структуру QBE-запроса.

SQL-запросы на выборку

SQL-запросы на выборку создаются инструкцией SELECT. При выполнении этой операции ядро базы данных Microsoft Jet находит указанную таблицу или таблицы, извлекает заданные столбцы, выделяет строки, соответствующие условию отбора, и сортирует или группирует результирующие строки в указанном порядке. Инструкция SELECT не изменяет данные в базе данных.

Команда имеет синтаксис:

SELECT [предикат] список_полей

FROM имена_таблиц

[WHERE критерий_поиска]

[GROUP BY критерий_группировки ]

[HAVING групповой_критерий]

[ORDER BY критерий_столбца]

Аргументы инструкции SELECT имеют следующий смысл:

  •  список_полей служит для задания имен полей, значение которых следует включить в выборку. Список может быть задан одним из следующих способов:
  1.  символом *,  что означает выбрать все поля;
  2.  таблица.* , что означает все поля из таблицы с именем таблица;
  3.  [таблица.]поле_1 [AS псевдоним_2] [, [таблица.]поле_2 [AS псевдоним_2] [, ...]] 

В этом случае :

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

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

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

  •  предложение FROM  имена_таблиц определяет имена одной или нескольких таблиц, которые содержат отбираемые данные.

Примечание. Если имена полей, таблиц, псевдонимы содержат пробелы, или знаки разделители, то они должны заключаться в квадратные скобки (например, Студент.[Номер зачетной книжки]).

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

Технология

  1.  Выбрать  в окне База данных объект Запросы и щелкнуть по кнопке Создать.
  2.  В окне Новый запрос выбрать режим  конструктор и нажмите кнопку Ok.
  3.  Закрыть окно Добавление таблицы.
  4.  В окне конструктора запросов выполнить команду ВИД/Режим SQL.
  5.  В окне запроса в режиме SQL набрать текст запроса:

SELECT *

FROM Студент;

Примечание. Если при наборе SQL-запроса требуется перейти на новую строку, следует использовать клавишную команду Ctrl+Enter . Инструкции SQL должны заканчиваться ;.

  1.  Нажать на панели инструментов кнопку Запуск для выполнения запроса.
  2.  Вернуться в режим SQL в случае, если выборка содержит ошибки, и откорректировать запрос.
  3.  Сохранить запрос с именем  Задание 3.

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

  •  аргумент «предикат» используются для ограничения числа возвращаемых записей. В инструкции SELECT может быть использован один из следующих предикатов отбора: ALL, DISTINCT, DISTINCTROW или TOP.

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

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

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

TOP позволяет ограничить количество выводимых записей. Например, следующим запросом из выборки будут отобраны ФИО и дата рождения только из трех первых записей:

SELECT TOP 3 Фамилия, [Дата рождения]

FROM Студент;

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

  •  предложение WHERE критерий_поиска определяет, какие записи из таблиц, перечисленных в предложении FROM, следует включить в результат выполнения инструкции SELECT.

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

Операторы определяют операцию, выполняемую над одним или несколькими элементами. Используются операторы: >, <,  =, <> (не равно), &, And, Or, Like, In, Between. Предложение WHERE может содержать до 40 выражений, связанных логическими операторами, такими как And и Or.

Оператор Like используется для сравнения строкового выражения с образцом в выражении SQL. Синтаксис оператора: выражение Like "образец"

Образец может содержать следующие подстановочные знаки:

  •  * - произвольное количество символов (например, оператор Студент.Фамилия Like "C*" в запросе возвратит все фамилии, начинающиеся на С);
  •  ? – один символ;
  •  # - одна цифра.

Оператор In проверяет, совпадает ли значение выражения с одним из элементов указанного списка. Синтаксис оператора:  выражение [Not] In (значение_1, значение_2,  . . .)

Например,  оператор Студент.ФИО In ('Иванов','Петров','Смирнов') определяет совпадает ли значение поля «ФИО» со значением одного из элементов списка.

Оператор Between определяет принадлежность значения выражения указанному диапазону.  

Синтаксис оператора:  выражение [Not] Between значение_1 And значение_2

Например,  оператор Студент.[Дата рождения] Between #01/01/80# And #31/12/80# определяет принадлежность значения поля «Дата рождения» интервалу дат 1980 года.

Если один из аргументов оператора Between...And, выражение, значение_1 или значение_2, имеет значение Null, то оператор Between...And возвращает значение Null.

Поскольку подстановочные знаки, такие как символ звездочки * считаются текстовыми символами, их нельзя использовать в операторе Between...And.

Идентификаторы в выражении задают ссылку на значение поля и заключаются в квадратные скобки, если содержат пробелы или знака препинания.

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

Литерал в выражении представляет значение, например, число, строку или дату, которое используется именно в том виде, как оно записано. Даты заключаются в символы номера (#), а строки в прямые кавычки (") или одинарные кавычки (например,100, #1-янв-94#, "New York").

Константа (True, False, Да, Нет, Null) представляет значение, остающееся неизменным.

Задание 6. С помощью SQL-запроса Задание 6 сформировать список студентов заданной группы. Список должен содержать полные сведения о студенте.

Запрос должен содержать следующий текст:

SELECT *

FROM Студент

WHERE [Номер группы]=n;,

где n – номер группы.

Примечание. Если номер группы требуется задавать во время выполнения запроса, то в Access критерий поиска можно задать условием [Номер группы] =[Введите номер группы]. Такой запрос  является запросом с параметром.

Задание 7. С помощью SQL-запроса Задание 7 сформировать список студентов, фамилии которых начинаются на «К». Список должен содержать полные сведения о студенте.

Задание 8. С помощью SQL-запроса  Задание 8 сформировать список студентов, фамилии которых начинаются на «А», «Б» и «В». Список должен содержать номер группы, фамилию и номер зачетной книжки студента.

Задание 9. С помощью SQL-запроса Задание 9 сформировать список студентов родившихся в заданном году. Список должен содержать номер группы, фамилию и дату рождения студента. 

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

  •  предложение ORDER BY определяет порядок сортировки выводимых записей. Критерий столбца задает поле, по значениям которого упорядочиваются записи. С помощью ключевых слов ASC или DESC можно явно указать порядок сортировки:

ASC – по возрастанию значений (действует по умолчанию);

DESC – по убыванию

Задание 11. С помощью SQL-запроса Задание 11 сформировать список студентов родившихся в заданном году. Список должен содержать номер группы, фамилию и дату рождения студента и отсортирован по номеру группы. 

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

  •  предложение GROUP BY определяет критерий объединения записей в группу для выполнения групповых операций.  Критерий_группировки определяет поле, на основе которого выполняется группировка. А аргумент список_полей задается в виде:

статистическая_функция(поле) AS псевдоним

Статистические функции, например Sum, Count (количество записей, возвращаемых запросом), Avg (арифметическое среднее набора чисел), Min, Max (минимальное и максимальное значения из набора значений, содержащихся в указанном поле запроса), используются для вычисления итоговых значений.

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

Задание 13. Составить QBE-запрос для подсчета количества коммерческих студентов в каждой группе.

Технология.

  1.  Создать новый QBE-запрос с использованием таблицы Студент.
  2.  Ввести в 1-ый столбец бланка запроса поле Номер группы, в 2-ой столбец – поле Коммерческий.
  3.  Установить в качестве условия выборки для 2-го столбца – значение Да.
  4.  Установить для 1-го столбца бланка сортировку по возрастанию.
  5.  Отключить вывод на экран данных 2-го столбца.
  6.  Ввести в 3-ый столбец поле Коммерческий и замените название столбца на Количество коммерческих. Для этого ячейка с названием поля должна содержать:

Количество коммерческих:Коммерческий

  (новое  и старое названия столбца разделены символом «двоеточие»).

  1.  Щелкнув по кнопке инструментальной панели Групповые операции, добавить в бланк строку Групповая операция и выбрать из списка для 3-го столбца операцию Count.
  2.  Просмотреть выборку с подсчетом итогов,  щелкнув по кнопке инструментальной панели Запуск, либо выполнив команду ЗАПРОС/Запуск.
  3.  Вернуться в режим конструктора запросов, щелкнув по кнопке Вид инструментальной панели.
  4.  Сохранить запрос, присвоив ему имя Задание 13.

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

Запрос должен содержать следующий текст:

SELECT [Номер группы], count(Коммерческий) AS [Количество коммерческих]

FROM Студент

WHERE Коммерческий=TRUE

GROUP BY [Номер группы]

ORDER BY [Номер группы];

Выполнить запрос. Сравнить результаты работы запросов из заданий 13 и 14. Просмотреть, сравнить и проанализировать структуры этих запросов сначала в режиме SQL,  затем в режиме конструктора.

Примечание. При создании сложного SQL-запроса можно с целью проверки синтаксиса запроса открывать его в окне конструктора QBE-запроса. Если команды запроса содержат орфографические или синтаксические ошибки, то Access не сможет преобразовать SQL-запрос в QBE-запрос и выведет окно сообщения с описанием ошибки.

Соединение таблиц

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

В результате выполнения такого запроса осуществляется соединение таблиц. При выполнении соединения имена таблиц перечисляются в предложении запроса FROM. 

Пример. SELECT*

              FROM Группа,Студент;

Так как в запросе не определены связи между таблицами, то результатом запроса будет декартово произведение таблиц (каждая запись таблицы Группа будет соединена со всеми записями таблицы Студент).

Связи между таблицами в SQL могут быть установлены с помощью предложений WHERE и INNER JOIN.

1. Связь между таблицами с помощью предложения WHERE.

Если в качестве критерия поиска в предложении WHERE задаются условия, основанные на равенствах, то выполняется внутреннее соединение 2-х таблиц. Такое соединение называется эквисоединением.

Синтаксис запроса на соединение будет иметь вид:

SELECT список_полей

FROM таблица_1 , таблица_2 

WHERE таблица_1.поле_1 оператор таблица_2.поле_2

где:

таблица_1, таблица_2 - имена таблиц, записи которых подлежат объединению;

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

оператор  -любой оператор сравнения: "=," "<," ">," "<=," ">=," или "<>".

Соединение может выполняться и по нескольким полям. В этом случае условие объединения задается:

 таблица_1.поле_1 оператор таблица_2.поле_1 AND таблица_1.поле_2 оператор таблица_2.поле_2) 

Пример. SELECT*

              FROM Группа,Студент

              WHERE Группа.[Номер группы]=Студент.[Номер группы];

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

Синтаксис запроса на множественное соединение:

SELECT список_полей

FROM таблица_1 , таблица_2 , таблица 3

WHERE таблица_1.поле_1 оператор таблица_2.поле_2 AND таблица_2.поле_1 оператор таблица_3.поле_1

Задание 15. Составить SQL-запрос для подсчета количества коммерческих студентов по каждой специальности.

Запрос должен содержать поля: Наименование специальности и количество коммерческих студентов.

Множество записей полученных по запросу должно представлять собой соединение таблиц Студент, Группа и Специальность.

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

Технология.

  1.  Создать новый SQL-запрос с использованием таблиц Студент и Группа, включив в запрос поля: Номер специальности, Номер группы, Фамилия, Коммерческий.

Текст запроса:

SELECT Группа.[Номер специальности], Студент.[Номер группы], Студент.Фамилия,

Студент.Коммерческий

FROM Студент, Группа

WHERE Студент.[Номер группы]=Группа.[Номер группы];

  1.  Выполнить запрос и проверить полученную выборку на правильность объединения. В случае верного объединения перейти к следующему пункту, иначе откорректировать запрос.
  2.  Исправить запрос в соответствии со следующим текстом:

SELECT Специальность.[Наименование специальности], Группа.[Номер специальности], Студент.[Номер группы], Студент.Фамилия, Студент.Коммерческий

FROM Студент, Группа, Специальность

WHERE Специальность.[Номер специальности]=Группа.[Номер специальности] AND Студент.[Номер группы]=Группа.[Номер группы];

  1.  Выполнить запрос и проверить полученную выборку на правильность объединения. В случае верного объединения перейти к следующему пункту, иначе откорректировать запрос.
  2.  Откорректировать запрос, выполнив следующие действия:
  •  изменить список полей, оставив в списке поля Наименование специальности и Количество коммерческих студентов. Количество коммерческих студентов должно являться псевдонимом функции Count(Коммерческий);
  •  добавить в предложение WHERE условие для отбора в объединение только коммерческих студентов;
  •  добавить предложение GROUP BY для группировки данных по полю Наименование специальности.
  1.  Выполнить запрос и проверить полученную выборку. Затем сохранить запрос.

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

SELECT Специальность.[Наименование специальности], COUNT(ФИО) AS [Количество коммерческих студентов]

FROM Студент, Группа, Специальность

WHERE Специальность.[Номер специальности]=Группа.[Номер специальности] AND Студент.[Номер группы]=Группа.[Номер группы] AND Студент.Коммерческий=Yes

GROUP BY Специальность.[Наименование специальности];

Задание 16. Составить SQL-запрос для подсчета количества бюджетных (некоммерческих) студентов по каждому факультету

Запрос должен содержать поля: Наименование факультета и Количество бюджетных студентов.

Задание 17. Составить SQL-запрос для подсчета итоговой стоимости платы за обучение коммерческими студентами по каждой специальности.

Запрос должен содержать поля: Наименование специальности и Итого по специальности.

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

2. Связь между таблицами с помощью предложения INNER JOIN.

В этом случае синтаксис запроса на соединение будет иметь вид:

SELECT список_полей

FROM таблица_1 INNER JOIN таблица_2 

ON таблица_1.поле_1 оператор таблица_2.поле_2

Пример. SELECT*

              FROM Группа INNER JOIN Студент

              ON Группа.[Номер группы]=Студент.[Номер группы];

Если задаются условия, основанные на равенствах, то выполняется внутреннее соединение 2-х таблиц.

Чтобы установить связь по нескольким полям, следует связать несколько предложений ON в инструкции JOIN:

SELECT список_полей

FROM таблица_1 INNER JOIN таблица_2 

ON таблица_1.поле_1 оператор таблица_2.поле_1 {AND| OR}

ON таблица_1.поле_2 оператор таблица_2.поле_2;

Предложения JOIN могут быть вложенными, в таком случае будет выполняться множественное соединение. Синтаксис запроса:

SELECT список_полей

FROM таблица_1 INNER JOIN  (таблица_2 INNER JOIN таблица_3

ON таблица_2.поле_1 оператор таблица_3.поле)_

ON таблица_1.поле_1 оператор таблица_2.поле_1 {AND| OR}

ON таблица_1.поле_2 оператор таблица_2.поле_2;

Задание 18. С помощью SQL-запроса сформировать список групп с указанием наименования факультетов, к которым они относятся.

Запрос должен содержать поля: Номер группы  и  Наименование факультета.

В запросе использовать предложение INNER JOIN.

Задание 19. С помощью SQL-запроса сформировать список коммерческих студентов на оплату за обучение, упорядоченный по наименованию специальности. Список должен содержать поля Наименование специальности,  Фамилия  и  Стоимость обучения. В запросе использовать предложение INNER JOIN.

Задание 20. Составить SQL-запрос для подсчета итоговой стоимости платы за обучение коммерческими студентами по каждой специальности в разрезе факультетов.

Запрос должен содержать поля: Наименование факультета,  Наименование специальности и  Итого по специальности.

В запросе использовать предложение INNER JOIN.

Внешнее соединение таблиц

Для выполнения внешнего соединения таблиц в любом предложении FROM используют предложения LEFT JOIN или RIGHT JOIN.

Синтаксис запроса на внешнее соединение:

SELECT список_полей

FROM таблица_1  {LEFT | RIGHT } JOIN таблица_2 

ON таблица_1.поле_1 оператор таблица_2.поле_2

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

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

Примечание. Операции LEFT JOIN или RIGHT JOIN могут быть вложены в операцию INNER JOIN, но операция INNER JOIN не может быть вложена в операцию LEFT JOIN или RIGHT JOIN.

Задание 21. Составить SQL-запрос для определения по каждой специальности номеров групп.

Запрос должен содержать поля  Наименование специальности  и  Номер группы.

В запросе использовать предложение LEFT JOIN.

Задание 22. Создать копию запроса по заданию 21. Присвоить ему имя Задание 22. Изменить запрос так, чтобы результатом являлось правое внешнее объединение таблиц Группа и Факультет.

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

Задание 24. Составить SQL-запрос, формирующий список старост групп.

Перед составлением запроса внести следующие изменения в базу данных:

  •  изменить структуру таблицы Группа, добавив в таблицу новое поле  Староста – числовое, длинное целое;
  •  в режиме таблицы ввести в поле Староста номера зачетных книжек тех студентов из таблицы Студент, кто является старостой.  Для этого можно открыть таблицу Группа, затем последовательно щелкать по знаку «+», расположенному в левом столбце каждой записи. На экране будет появляться список студентов, обучающихся в текущей группе. Просматривая этот список, можно выбрать номер зачетной книжки старосты;

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

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

Сведения о старостах упорядочить в разрезе факультетов, по номерам групп.

Использование вычисляемых полей в SQL-запросах

Список полей в команде SELECT кроме полей может содержать выражения. Например, для вычисления стоимости обучения с учетом НДС в список полей следует включить выражение:

Специальность.[Стоимость обучения]*(1+НДС) AS [Стоимость с учетом НДС]

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

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

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

ORDER BY Специальность.[Стоимость обучения]*(1+НДС)

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

GROUP BY Группа.[Номер факультета], в список полей включить групповую операцию SUM(Специальность.[Стоимость обучения]*(1+НДС))

Пример. Определить итоговые стоимости обучения с учетом НДС по номерам специальностей.

SELECT Специальность.[Номер специальности], sum(Специальность.[Стоимость обучения] * (1+НДС)) AS [Итоговая стоимость с учетом НДС]

FROM Специальность INNER JOIN ( Группа INNER JOIN Студент

ON Студент.[Номер группы] = Группа.[Номер группы])

ON Группа.[Номер специальности] = Специальность.[Номер специальности]

WHERE Студент.Коммерческий=Yes

GROUP BY Специальность.[Номер специальности];

Задание 25. Составить SQL-запрос для получения справки по стоимости обучения коммерческих студентов с учетом НДС. Принять, что стоимость обучения с учетом НДС коммерческого студента равна стоимости обучения по выбранной им специальности, умноженной на (1+0,2), где 0,2 – величина НДС.

В справку выводить следующие сведения о студенте: Номер группы, Наименование специальности, Номер зачетной книжки,  Фамилия  и  Стоимость обучения с учетом НДС.

Сведения о студентах упорядочить по убыванию значений  Стоимость обучения с учетом НДС.

Задание 26. Составить SQL-запрос для получения справки, содержащей итоговые данные о стоимости обучения с учетом НДС по специальностям.

В справку выводить Наименование специальности и  Итоговую стоимость обучения с учетом НДС.

Создание новой таблицы

Создание новой таблицы выполняется командой CREATE TABLE. В этой команде выполняется описания новой таблицы, ее полей и индексов.

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

CREATE TABLE таблица (поле_1 тип [(размер)] [NOT NULL] [индекс_1]

[, поле_2 тип [(размер)][NOT NULL] [индекс_2] [, ...]]

[, CONSTRAINT составнойИндекс [, ...]])

где:

  •  таблица - имя создаваемой таблицы;
  •  поле_1, поле_2 и т.д. - имена полей, создаваемых в новой таблице.  Таблица должна содержать хотя бы одно поле;
  •  тип - тип данных поля в новой таблице;
  •  размер - размер поля в символах (только для текстовых и двоичных полей);
  •  индекс_1, индекс_2 - предложение CONSTRAINT, предназначенное для создания простого индекса;
  •  составнойИндекс - предложение CONSTRAINT, предназначенное для создания составного индекса.

Примечание: Если для поля добавлено ограничение NOT NULL, то при добавлении новых записей это поле должно содержать допустимые данные.

Пример. Создается новая таблица с двумя текстовыми полями и числовым полем. Поле SSN делается ключевым полем.

CREATE TABLE НоваяТаблица (Имя TEXT, Фамилия TEXT, SSN INTEGER

CONSTRAINT МойИндекс PRIMARY KEY);

Задание 27. Составить SQL-запрос на создание новой таблицы с именем Дисциплина1. Таблица Дисциплина1 должна иметь следующий состав полей:

  •  Код дисциплины –числовое, байтовое (Byte);
  •  Наименование дисциплины – текстовое поле, 30 символов;
  •  Лекции (часов) – числовое поле, байтовое;
  •  Практика (часов) – числовое поле, байтовое.

После выполнения запроса проверить существование таблицы Дисциплина1.

Задание 28. Составить SQL-запрос на создание новой таблицы с именем Вспомогательная1. 

В таблицу Вспомогательная1 включите только одно поле:

- Оценка - числовое, байтовое.

Задание 29. Составить SQL-запрос на создание новой таблицы с именем Ведомость_1. 

Таблица должна иметь следующий состав полей:

  •  Номер группы – числового типа, целое
  •  Код дисциплины - числовое, байтовое
  •  Наименование дисциплины - текстовое поле, 30 символов
  •  Фамилия - текстовое поле, 15 символов
  •  Номер зачетной книжки – числовое, длинное целое
  •  Оценка - числовое, байтовое.

QSL-запросы на изменение

  1.  Добавление записей в таблицу выполняется командой INSERT INTO.

Синтаксис запроса на добавление одной записи:

INSERT INTO таблица [(поле_1[, поле_2[, ...]])]

VALUES (значение_1[, значение_2[, ...])

Примечание. Если создать в режиме SQL запрос на добавление с помощью инструкции INSERT INTO...VALUES, сохранить и закрыть его, а затем открыть снова, то предложение VALUES будет преобразовано в предложение SELECT. Это не повлияет на результат выполнения запроса.

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

INSERT INTO таблица_приемник 

SELECT список_полей

FROM таблица_источник

Задание 30. Составить SQL-запрос на добавление записи в таблицу Дисциплина.

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

Код дисциплины

Наименование дисциплины

Лекции

Практика

1

Информатика

100

20

2

Математика

200

50

 

Задание 31. Составить SQL-запрос на добавление записи в таблицу Вспомогательная.

Полю Оценка задать значение NULL (нет значения).

Задание 32. Составить SQL-запрос на добавление записей в таблицу Ведомость_1. 

В таблицу Ведомость_1 необходимо поместить данные, полученные в результате объединения таблиц Студент, Дисциплина и Вспомогательная. Причем в объединение следует включить сведения только об одной дисциплине, код которой равен 1. Запрос на объединение должен иметь вид:

SELECT Студент.[Номер группы], Дисциплина1.[Код дисциплины], Дисциплина1.[Наименование дисциплины], ФИО, [Номер зачетной книжки], Оценка

FROM Студент, Дисциплина1, Вспомогательная1

WHERE Дисциплина1.[Код дисциплины]=1;

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

В режиме таблицы заполните столбец Оценка таблицы Ведомость_1  оценками.

Создание новой таблицы на основе данных других таблиц

Выполняется командой SELECT...INTO. Синтаксис команды:

SELECT поле_1[, поле_2[, ...]] INTO новая_таблица 

FROM таблица_источник

Задание 33. Отладить следующий запрос на создание новой таблицы с именем Ведомость_2.

SELECT Студент.[Номер группы], Дисциплина1.[Код дисциплины],

Дисциплина1.[Наименование дисциплины], Фамилия, Студент.[Номер зачетной книжки], Оценка

INTO Ведомость_2

FROM Студент, Дисциплина1, Вспомогательная1

WHERE Дисциплина1.[Код дисциплины]=2;

Задание 34. Составить SQL-запрос на создание новой таблицы Староста. В таблицу поместить сведения из таблицы Студент о тех студентах, кто является старостами. Сведения о старостах содержаться в таблице Группа.

Таблица должна иметь следующий состав полей:

  •  Номер группы
  •  Фамилия
  •  Номер зачетной книжки
  •  Дата рождения.

Изменение значений полей

Выполняется командой Update. С помощью этой команды можно изменять некоторые или все значения в одной или нескольких записях. Команда содержит предложение Update, позволяющее указать имя таблицы, для которой выполняется операция, и предложение Set, определяющее изменение, которое необходимо выполнить для определенного поля.

Задание 35. 

Составить SQL-запрос на увеличение стоимости обучения на 20 % для двух номеров специальности.

Текст запроса:

UPDATE Специальность

SET [стоимость обучения]=[стоимость обучения]*1.2

WHERE [номер специальности]=n1 or [номер специальности]=n2,

где n1 и n2 – номера специальностей

Задание 36. Составить SQL-запрос на перевод всех коммерческих студентов, обучающихся в заданной группе, в новую группу.

Задание 37. Составить SQL-запрос  на перевод коммерческого студента, имеющего заданный номер зачетной книжки, на обучение на бюджетной основе.

Исключение записей из таблицы

Записи из таблицы можно исключить с помощью команды обновления Delete. По этой команде удаляются только целые записи, а не отдельные значения полей. Синтаксис команды:

 DELETE FROM <имя_таблицы>.

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

Задание 38. Составить SQL-запрос для удаления из таблицы Группа той группы, в которой не учатся студенты.

Текст запроса:

DELETE 

FROM Группа

WHERE [Группа].[Номер группы]=n,

где n – номер нужной группы.

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

Библиографический список

  1.  ИНФОРМАТИКА: Учебник / Под ред. Н.В.Макаровой – М.:Финансы и статистика, 1997. – 786с.
  2.  И.А.ХАРИТОНОВА, В.Д.МИХЕЕВА. Microsoft Access 2000. – СПб.: БХВ - Санкт-Петербург, 2000. –1088с.
  3.  М.ГРАБЕР. Введение в SQL. – М.:Изд-во «ЛОРИ», 1996. –377с.


СОДЕРЖАНИЕ

[0.0.1] Методические указания

[0.0.1.1] Утверждено

[0.1] Лабораторная работа № 1. Создание таблиц

[0.2] Лабораторная работа № 2.  Конструирование   запросов

[0.3] Лабораторная работа № 3.  Конструирование форм

[0.4] Лабораторная работа № 4. Конструирование отчетов

[0.5] Лабораторная работа № 5. Создание макросов

[0.6] Лабораторная работа № 6. Конструирование SQL запросов

[0.7] Библиографический список


N группы

Наименование cgспспец

Стоимость

N специальности

Специальность

Наименование

Декан

 фак-та

Факультет

Коммерческий

Дата рождения

N группы 

Фамилия

N зачетн. книжки

Студент

Курс

N cпециальности

N факуль-тета 

Группа

EMBED Word.Picture.8  




1. Ближнее зарубежье Г
2. Cучасні політичні партії в Україні
3.  Какие форматы листов установлены для чертежей 2
4. Державний кредит це економічні відносини які виникають між підприємством і державою внаслідок купівлі п
5. ТЕМА- Ознайомлення з МthCd.
6. Рынок ссудных капиталов
7. Реферат на тему- Небезпечні і шкідливі фактори їх класифікація Небезпека це негативна властивість ма
8. Fshioned ~ long used CM ~ dispprovl PM Timetested ~ long used CM ~ pprovl CM Primry nd secondry menings Exest in one word
9. Статья- Никольский храм в Зарайске
10. а кроме баранины Вторник Обед- вареная или жареная курица без кожи Ужин- 2 вареных яйца сала
11. Как правильно принять квартиру в новостройке
12. Мотивация персонала
13.  Понятие о хозяйственном учете
14. 24 ноября 2013 г Количество участников 76 из них юниоры63 девушек 13
15. психотерапевтам.html
16. Мислителі різних епох
17. ВАРИАНТ 4 1 Какой из видов нарушения сознания не существует- 1 Сомнолентность 2 Ступор 3 Афония
18. X Mle License Number- 64300057 Ntionlity- RUS
19. Пенсии за выслугу лет Условия назначения
20. БАРАНОВИЧСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ

Материалы собраны группой SamZan и находятся в свободном доступе