Задание № 16. Работа с базами данных

Цель: Знакомство с использованием MS Query для работы с внешними базами данных.

Темы: Формирование критериев выборки. Импорт данных в MS Excel. Утилита MS Query. SQL-запрос.

1. Работа с данными Excel как с "базой данных".

1.1. Создайте таблицу, приведенную в левой части рис.16.1. Большую часть этой таблицы можно заимствовать из задания № 8. Обратите внимание на то, как в соответствии с заданием определяются и записываются в ячейках листа формулы для вычисления надбавки, налога и выплат.

1.2. Сформулируйте критерии для выборки данных и запишите их справа от исходной таблицы в несмежных диапазонах, как представлено на рис.16.1 (справа).

Критерии для выборки следующих данных:

· лица женского пола, (критерий №1);

· лица мужского пола с суммой выплат больше 500 руб. (критерий №2);

· лица мужского пола с суммой выплат меньше 400 руб. (критерий №3);

· мужчины - референты и водители с выплатой больше 250 и меньше 500, а также женщины - менеджеры с выплатой больше 500 и меньше 700 (критерий №4).

1.3. Пользуясь командой Данные – Сортировка и фильтр – Дополнительно - Расширенный фильтр выполните выборку данных в соответствии с указанными критериями и поместите результаты выборки в диапазоне ячеек справа от критериев (рис.16.1).

Рис.16.1

2. Импорт текстовых файлов.

2.1. Путем копирования данных создайте на отдельном листе рабочей книги часть представленной выше таблицы, содержащей только исходные данные (диапазон А1:Н12).

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

2.3. Запустите текстовый процессор Word и загрузите в него сохраненный файл. Просмотрите его структуру.

2.4. Закройте текстовый файл с данными.

2.5. Загрузите в табличный процессор Excel созданный в предыдущем пункте текстовый файл. Обратите внимание на все диалоговые окна "Мастера текстов", которые открываются в процессе загрузки файла и на возможности работы со структурой импортируемого текстового документа, особенно на определение разделителей и задание форматов данных.

2.6. Просмотрите загруженную таблицу, сравните её с исходной таблицей. Обратите внимание на количество листов в созданной книге.

3. Использование MS Query для создания запросов к внешним базам данных.

3.1. Проверьте наличие в вашей папке файла, содержащего базу данных (название файла уточните у преподавателя (DBAccess.mdb)).

3.2. Перейдите на новый лист Excel. Запустите программу взаимодействия с базами данных MS Query, используя команду Данные – Получить внешние данные – Из других источников – Из Microsoft Query.

3.2.1. Выберите в качестве источника данных файлы, создаваемые MS Access Database и откажитесь от использования мастера запросов (рис.16.2).

Рис.16.2

3.2.2. В открывшемся диалоговом окне "Выбор базы данных" (рис.16.3) найдите и выберите файл DBAccess.mdb, содержащий базу данных.

Рис.16.3

3.2.3. В окне "Добавление таблицы" выберите один файл с именем "Таблица1" для дальнейшего использования.

3.2.4. Научитесь добавлять, удалять и перемещать столбцы (поля базы данных) в области данных. Для размещения столбцов можно использовать непосредственно таблицу, область данных или команды меню. Разместите в области данных столбцы-поля в следующем порядке: "№", "Фамилия", "Пол", "Должность", "Оклад", "Телефон", "Год рожд".

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

3.2.6. Используя команду Вид - Условия, измените внешний вид запроса так, чтобы в нем присутствовали три области: область таблиц (исходные данные), область критериев (формирование условий запроса) и область результатов (рис.16.4.).

3.2.7. Уберите (скройте) столбец, соответствующий полю "№" (Формат – Скрыть столбец).

3.2.8. Извлеките все записи из таблицы базы данных, выполнив команду Файл – Вернуть данные в Microsoft Office Excel, разместив их, начиная с ячейки А1 текущего листа Excel.

3.2.9. Отсортируйте извлеченные данные в соответствии со следующими правилами:

· по фамилии в алфавитном порядке;

· в порядке убывания по году рождения;

3.2.10. Находясь в области полученных данных, вернитесь в MS Query, используя команду Данные – Подключения – Обновить все – Свойства подключения – вкладка Определение – кнопка Изменить запрос.

3.2.11. Научитесь выполнять переходы к записям (строкам) с произвольным номером (Записи - Перейти). Просмотрите первую, последнюю и 8-ю записи. Внесите исправления в номера телефонов первой, последней и 2-й записей, предварительно указав возможность правки записей (Записи – Разрешить правку).

Рис.16.4

3.2.12. Верните данные в рабочий лист Excel.

3.2.13. Измените свойства диапазона данных, указав включение номеров строк (рис.16.5) (Данные – Подключения – Свойства и Данные – Подключения – Обновить все - Обновить).

Рис.16.5

4. Формирование критериев для выбора данных из внешней базы данных.

4.1. Формирование простых критериев.

4.1.1. На новом листе Excel выполните запрос, используя область критериев (Вид - Условия), в которой укажите в качестве поля для определения критерия - "Должность", а в качестве значения поля - менеджер.

4.1.2. Выполните выборку данных и просмотрите результат в области результатов (Записи – Выполнить запрос).

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

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

4.1.5. Выполните запрос и проверьте полученный результат.

4.1.6. Сохраните созданный запрос под именем "Запрос1".

4.2. Критерии, содержащие несколько логических условий (связанные критерии).

4.2.1. Сформируйте критерий для выборки данных о лицах, фамилии которых начинаются на "И" или "С".

4.2.2. Сформируйте критерий для выборки данных о менеджерах - мужчинах и секретарях.

4.2.3. Сохраните созданный запрос под именем "Запрос2".

4.3.Специальные критерии.

4.3.1. Внесите изменения непосредственно в записи базы данных, удалив содержимое поля "Оклад" для директора и референта.

4.3.2. Извлеките данные о лицах, для которых отсутствует значение в поле "Оклад".

4.3.3. Очистите область критериев (удалите все критерии).

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

5. SQL-Запрос.

5.1. Сформируйте критерий и выполните запрос, содержащий сведения о фамилии, должности и телефоне для лиц, достигших возраста 50 лет.

5.2. Вызовите на экран окно для просмотра SQL-инструкции для созданного запроса, как показано на рис.16.7 (Вид – Запрос SQL). Просмотрите текст SQL-инструкции, выясните назначение каждой из ее частей и установите соответствие между ними и объектами "Запроса по образцу", создаваемому ранее.