 |
Разработка запросов в режиме конструктора
|
|
|
|
ПРАКТИЧЕСКАЯ РАБОТА 3. РАЗРАБОТКА ЗАПРОСОВ В БАЗЕ ДАННЫХ
Лабораторная работа посвящена разработке запросов в базе данных «Абитуриенты».
Примечание: Во время выполнения предыдущей работы должны быть созданы таблицы базы данных в папке «Базы данных». Таблицы база данных должны быть полностью заполнена данными, приведенными в описании к предыдущей работе.
Запросы являются основным способом извлечение требуемой информации из базы данных. Причём в процессе выполнения запроса в качестве источников данных могут быть задействованы одна или несколько таблиц, а также ранее созданные запросы. Существуют различные виды запросов. В данной работе мы познакомимся с основным видом запросов – запросом на выборку.
Для разработки запросов существуют два способа:
· Создание запроса в режиме конструктора
· Создание запроса с помощью мастера
Начнём с первого, как наиболее универсального способа создания запросов.
Обеспечение условий целостности баз данных
1. Откройте Вашу базу данных. Найдите пиктограмму «схема данных» и откройте окно схемы данных.
2. Проверьте связи между таблицами базы данных. Схема данных должна иметь следующий вид (рисунок 1.) Если связь отсутствует, то ее можно установить при помощи мышки проводя связь от ключевого поля одной таблицы к такому же полю другой таблицы.
Рисунок 1 – Схема данных базы данных
3. Для каждой связи настройте её свойства. Щелкните по линии связи и нажмите правую клавишу. После этого выбираем изменить связь. Установите флажки в позиции:
-обеспечение целостности данных;
-каскадное обновление связанных полей;
-каскадное удаление связанных полей.
|
|
|
Рисунок 2- Определение свойства (мощности) связи
4. Откройте таблицу Факультет в режиме таблица. Измените аббревиатуру факультета ээ на фээ. Закройте таблицу Факультет и откройте таблицу Специальность и таблицу Группы. Как изменилась аббревиатура факультета в этих таблицах? Как Вы можете объяснить произошедшие изменения?
Верните аббревиатуру факультета таблицы Факультет в исходное состояние.
Разработка запросов в режиме конструктора
Откройте Вашу базу данных. Она к данному моменту должна состоять из четырёх связанных между собой таблиц. Если вы вберете в окне базы данных в качестве объекта «таблица», то вы увидите в рабочей части окна перечень созданных Вами ранее четырёх таблиц: факультет, специальность, группа, контингент.
Выберете в окне базы данных в качестве объекта «запросы», и далее «Создание запроса в режиме конструктора». На экране появится окно конструктора запросов следующего вида.
Окно конструктора запросов состоит из двух областей:
верхней, куда добавляются таблицы и ранее созданные запросы, данные из которых потребуются для формирования нового запроса;
нижней, где заполняется специальный шаблон, определяющий условие отбора и сортировки записей в выборке.
Кроме того, выводится вспомогательная форма «Добавление таблицы», которая позволяет добавить в верхнюю область окна конструктора запросов необходимые таблицы.
3. Прежде чем приступать к разработке запроса необходимо уяснить следующие положения:
База данных, представленная в виде четырёх таблиц предназначена для работы приёмной комиссии высшего учебного заведения.
Таблица факультет содержит перечень всех факультетов института. В качестве полей таблицы выступают: код факультета, название и план набора на факультет. План набора определяет максимальное число студентов, которое может быть зачислено на факультет.
|
|
|
Таблица специальность содержит перечень специальностей на каждом факультете, а также план набора студентов на каждую специальность (максимальное число студентов, которые могут быть зачислены на специальность).
Таблица группа содержит список всех групп абитуриентов, поскольку при поступлении все абитуриенты распределяются по группам. Для каждой группы устанавливаются дни проведения вступительных экзаменов.
Таблица контингент содержит все сведения об абитуриенте. Кроме того, в эту таблицу занесены все результаты экзаменов.
Правила приёма в ВУЗ сводятся к следующему:
· В ВУЗ могут быть зачислены только абитуриенты успешно прошедшие вступительные испытания. Это значит, что по всем трём экзаменам получены положительные оценки (3, 4, 5). Медалисты, при условии сдачи экзамена по математике на оценку 5 освобождаются от дальнейших экзаменов и зачисляются в ВУЗ без конкурса. Если медалист сдал экзамен по математике на одну из оценок 2, 3 или 4, то он подчиняется тем же правилам, что и не медалисты.
· Конкурс проводится в случае, если число мест в институте меньше, чем число абитуриентов, успешно прошедших вступительные испытания.
· Конкурс проводится по сумме оценок по двум предметам – математике и физике. В случае, если сумма баллов у нескольких претендентов одинакова, а число мест меньше, чем число таких претендентов, то во внимание принимается средний балл по аттестату и в качестве третьего критерия требование общежития для проживания.
В качестве примера рассмотрим запрос, позволяющих вывести список абитуриентов, окончивших учебное заведение ранее 2010 года. Список должен быть упорядочен по алфавиту фамилий.
Бланк запроса приведен ниже. SQL текст запроса будет иметь вид:
SELECT контингент.Фамилия, контингент.Имя, контингент.Имя, контингент.ГодОкончанияШколы
FROM контингент
WHERE (((контингент.ГодОкончанияШколы)<2010))
ORDER BY контингент.Фамилия;
Практические задания
Простые запросы
Задача 01.
Вывести список абитуриентов юношей (фамилию, имя, отчество, пол, общежитие), для которых требуется общежитие. Список должен быть отсортирован сначала по фамилии, затем по имени. Запрос сохранить под именем Задача 01. Команду SQL запроса записать в отчёте.
|
|
|
Для определения вида сформированного SQL запроса в меню Запросы выбираем Запрос SQL и далее К серверу.
Задача 02.
Вывести список абитуриентов (фамилию, имя, отчество, оценки по: математике, физике, русскому), не получивших двоек при сдаче трёх экзаменов. Список должен быть отсортирован сначала по фамилии, затем по имени. Запрос сохранить под именем Задача 02. Команду SQL запроса записать в отчёте.
Задача 03.
Вывести список абитуриентов (фамилию, имя, отчество, оценки по: математике, физике, русскому, сумму по трём экзаменам), не имеющих медали. Список должен быть отсортирован по сумме балов трёх экзаменов. Запрос сохранить под именем Задача 03. Команду SQL запроса записать в отчёте.
Задача 04.
Вывести список абитуриентов (аббревиатуру специальности, фамилию, имя, отчество, оценки по: математике, физике, русскому, суммарной оценке по трем экзаменам, наличие медали), успешно выдержавших вступительные испытания. Список должен быть отсортирован по полям: математика, физика, русский. Запрос сохранить под именем Задача 04. Команду SQL запроса записать в отчёте. Команду SQL запроса записать в отчёте.
Задача 05.
Вывести список, состоящий из названия факультетов, специальностей, фамилий и имен абитуриентов. Список должен быть отсортирован по названию факультета, названию специальности и фамилии абитуриента. Запрос сохранить под именем Задача 05. Команду SQL запроса записать в отчёте.
Задача 06.
Вывести список специальностей и список медалистов, поступавших на эти специальности, но не выдержавших вступительные испытания. (Специальность, фамилия, имя, отчество, оценки по: математике, физике, русскому, наличие медали). Список должен быть отсортирован по фамилии. Запрос сохранить под именем Задача 06.Команду SQL запроса записать в отчёте.
Обобщающие запросы
С помощью обобщающих запросов можно обобщать числовые данные, содержащиеся в таблицах. Обобщающий запрос может вычислять сумму, среднее, количество, минимальное и максимальное значение и другие параметры.
|
|
|
Задача 07.
Сколько всего абитуриентов приняло участие во вступительных испытаниях по каждому факультету? Список факультетов отсортировать по количеству абитуриентов, подавших заявление на факультет. Запрос сохранить под именем Задача 7. Команду SQL запроса записать в отчёте.
Примечание: В данном запросе необходимо выполнить группировку данных. Для этого необходимо установить на планшете режим групповых операций (значок ∑). При решении задачи вначале определите, какие таблицы Вам понадобятся при формировании запроса и по каким полям выполняется группировка.
Задача 08.
Сколько всего абитуриентов приняло участие во вступительных испытаниях по каждой специальности? Список специальностей отсортировать по количеству абитуриентов, подавших заявление на специальность. Запрос сохранить под именем Задача 8. Команду SQL запроса записать в отчёте.
Задача 09.
Определить конкурс (число заявлений на одно место) по каждой специальности до проведения вступительных испытаний. Запрос должен содержать название специальности, план набора, число поданных заявлений, число заявлений на одно место. Список специальностей отсортировать по величине конкурса. Запрос сохранить под именем Задача 9. Команду SQL запроса записать в отчёте.
Примечание: Для формирования запроса в качестве источников исходных данных можно воспользоваться таблицей Специальности и запросом Задача 08. Для поля-выражения, в котором рассчитывается конкурс установить фиксированный формат.
Задача 10.
Определить конкурс (число заявлений на одно место) по каждому факультету до проведения вступительных испытаний. Запрос должен содержать название факультета, план набора, число поданных заявлений, число заявлений на одно место. Список факультетов отсортировать по величине конкурса. Запрос сохранить под именемЗадача 10. Команду SQL запроса записать в отчёте. Для поля-выражения, в котором рассчитывается конкурс установить краткий формат вывода.
Задача 11.
Вывести список абитуриентов (фамилию и инициалы в виде «Петров Н.И.»), средний бал по трем экзаменам и средний бал по аттестату при условии отсутствия медали. Запрос сохранить под именем Задача 11. Команду SQLзапроса записать в отчёте.
Примечание: Для формирования и объединения инициалов абитуриента сформируйте выражение
[Контигент]![Фамилия]+’ ‘+Left([Контигент]![Имя];1)+’.’+
Left([Контигент]![Отчество];1)+’.’
Функция Left(поле, n) вырезает n первых символов поля.
Задача 12.
Определить конкурс (число заявлений на одно место) по каждой специальности после проведения вступительных испытаний. Список специальностей отсортировать по величине конкурса. Запрос сохранить под именем Задача 12. Команду SQL запроса записать в отчёте.
|
|
|
Задача 13.
Определить конкурс (число заявлений на одно место) по каждому факультету после проведения вступительных испытаний. Список факультетов отсортировать по величине конкурса. Запрос сохранить под именем Задача 13.Команду SQL запроса записать в отчёте.
Задача 14.
Вывести список специальностей и число двоек, полученных на экзаменах абитуриентами каждой специальности. Список специальностей отсортировать по числу двоек. Запрос сохранить под именем Задача 14. Команду SQLзапроса записать в отчёте.
Задача 15.
При помощи запроса определить число претендентов на одно место в общежитии до проведения испытаний и после проведения испытаний, если число мест в общежитии для юношей 60, а для девушек 40. Команду SQLзапроса записать в отчёте.
Задача 16.
Вывести список средних балов по математике, физике, русскому языку для всех специальностей. Медалистов исключить из рассмотрения. Задача 16.
Параметрические запросы
1. Разработать запрос, при помощи которого можно вывести список абитуриентов со средним балом аттестата, находящемся в диапазоне от z1 до z2, где z1 и z2 любые числа в диапазоне от 2 до 5.
Примечание:
Для решения задачи использовать в условии отбора оператор Between в выражении:
Between [Введите минимальное значение] AND [Введите максимальное значение]
2. Разработать запрос, который выводит список абитуриентов, сдающих экзамены в любой указанный день. При выполнении запроса дата экзамена должна вводится через окно параметрического запроса.
3. Разработать запрос (или набор запросов), позволяющих вывести список абитуриентов, прошедших по конкурсу и зачисленных в институт для любой специальности. Аббревиатура специальности должна вводится в окне параметров при выполнении запроса.
Примечание:
Для того чтобы сформировать список зачисленных на специальность необходимо знать план набора на специальность, список абитуриентов успешно выдержавших вступительные испытания. Если таких окажется больше, чем план набора, то преимуществом пользуются те, у кого выше средний бал по аттестату. При наличии нескольких претендентов с равным средним балом аттестата преимуществом пользуются абитуриенты не нуждающиеся в общежитии.
|
|
|
