Файл: Учебнопрактическое пособие Хабаровск 2004 Содержание Введение 4 Лабораторная работа Создание таблиц 5 Основные понятия субд access 5.doc
ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 04.05.2024
Просмотров: 224
Скачиваний: 0
ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.
СОДЕРЖАНИЕ
Лабораторная работа № 1. Создание таблиц
Освоение приемов работы с фильтрами в таблицах
Лабораторная работа № 2. Конструирование запросов
Использование вычисляемых полей в запросах
Лабораторная работа № 3. Конструирование форм
Лабораторная работа № 4. Конструирование отчетов
Формирование перекрестного отчёта
Лабораторная работа № 5. Создание макросов
Создание пользовательского ниспадающего меню
Лабораторная работа № 6. Конструирование SQL запросов
соответствии с критерием группировки, множество записей объединяется в группы. Предложение HAVING определяет условия, которые накладываются на значения полученные в результате выполнения групповой операции (значение столбца статистическая функция).
Задание 13. Составить QBE-запрос для подсчета количества коммерческих студентов в каждой группе.
Технология
Количество коммерческих: Коммерческий
(новое и старое названия столбца разделены символом «двоеточие»).
Задание 14. Создать SQL-запрос с именем Задание 14, выполняющий подсчет количества коммерческих студентов в каждой группе, используя данные из таблицы Студент.
Запрос должен содержать следующий текст:
SELECT [Номер группы], соunt(Коммерческий) AS [Количество коммерческих]
FROM Студент
WHERE Коммерческий=ТRUЕ
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 оператор таблица 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 Студент.[Номер группы]=Группа.[Номер группы];
2. Выполнить запрос и проверить полученную выборку на правильность объединения. В случае верного объединения перейти к следующему пункту, иначе откорректировать запрос.
3. Исправить запрос в соответствии со следующим текстом:
SELECT Специальность. [Наименование специальности], Группа. [Номер специальности], Студент. [Номер группы], Студент.Фамилия, Студент. Коммерческий
FROM Студент, Группа, Специальность
WHERE Специальность.[Номер специальности]=Группа.[Номер специальности] AND Студент.[Номер группы]=Группа.[Номер группы];
6. Выполнить запрос и проверить полученную выборку. Затем сохранить запрос.
Вероятно, текст запроса будет иметь следующий вид:
SELECT Специальность.[Наименование специальности], СОUNT(ФИО) AS [Количество коммерческих студентов]
FROM Студент, Группа, Специальность
WHERE Специальность.[Номер специальности]= Группа. [Номер специальности] AND Студент.[Номер группы] =Группа.[Номер группы] AND Студент.Коммерческий=Yes
GROUP BY Специальность.[Наименование специальности];
Задание 16. Составить SQL-запрос для подсчета количества бюджетных (некоммерческих) студентов по каждому факультету
Запрос должен содержать поля: Наименование факультетаи Количество бюджетных студентов
.
Задание 17. Составить SQL-запрос для подсчета итоговой стоимости платы за обучение коммерческими студентами по каждой специальности.
Запрос должен содержать поля: Наименование специальности и Итого по специальности.
Итого по специальностирассчитывать исходя из стоимости обучения по специальности и количества коммерческих студентов, обучающихся по специальности.
Связь между таблицами с помощью предложения INNER JOIN.
В этом случае синтаксис запроса на соединение будет иметь вид:
SELECT список полей
FROM таблица_1 INNER JOIN таблица_2
ON таблица_1 .поле_1 оператор таблица_2.поле_2
Например.
SELECT*
FROM Группа INNER JOIN Студент
ON Группа.[Номер группы]=Студент.[Номер группы];
Если задаются условия, основанные на равенствах, то выполняется внутреннее соединение2-х таблиц.
Чтобы установить связь по нескольким полям, следует связать несколько предложений ON в инструкции JOIN:
SELECT список_полей
Задание 13. Составить QBE-запрос для подсчета количества коммерческих студентов в каждой группе.
Технология
-
Создать новый QBE-запрос с использованием таблицы Студент. -
Ввести в 1-ый столбец бланка запроса поле Номер группы, в 2-й столбец – поле Коммерческий. -
Установить в качестве условия выборки для 2-го столбца значение Да. -
Установить для 1-го столбца бланка сортировку по возрастанию. -
Отключить вывод на экран данных 2-го столбца. -
Ввести в 3-й столбец поле Коммерческийи замените название столбца на Количество коммерческих. Для этого ячейка с названием поля должна содержать:
Количество коммерческих: Коммерческий
(новое и старое названия столбца разделены символом «двоеточие»).
-
Щелкнув по кнопке инструментальной панели Групповые операции, добавить в бланк строку Групповая операция и выбрать из списка для 3-го столбца операцию Count. -
Просмотреть выборку с подсчетом итогов, щелкнув по кнопке инструментальной панели Запуск, либо выполнив команду ЗАПРОС/Запуск. -
Вернуться в режим конструктора запросов, щелкнув по кнопке Вид инструментальной панели. -
Сохранить запрос, присвоив ему имя Задание 13.
Задание 14. Создать SQL-запрос с именем Задание 14, выполняющий подсчет количества коммерческих студентов в каждой группе, используя данные из таблицы Студент.
Запрос должен содержать следующий текст:
SELECT [Номер группы], соunt(Коммерческий) AS [Количество коммерческих]
FROM Студент
WHERE Коммерческий=ТRUЕ
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 оператор таблица 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 Студент.[Номер группы]=Группа.[Номер группы];
2. Выполнить запрос и проверить полученную выборку на правильность объединения. В случае верного объединения перейти к следующему пункту, иначе откорректировать запрос.
3. Исправить запрос в соответствии со следующим текстом:
SELECT Специальность. [Наименование специальности], Группа. [Номер специальности], Студент. [Номер группы], Студент.Фамилия, Студент. Коммерческий
FROM Студент, Группа, Специальность
WHERE Специальность.[Номер специальности]=Группа.[Номер специальности] AND Студент.[Номер группы]=Группа.[Номер группы];
-
Выполнить запрос и проверить полученную выборку на правильность объединения. В случае верного объединения перейти к следующему пункту, иначе откорректировать запрос. -
Откорректировать запрос, выполнив следующие действия:
-
изменить список полей, оставив в списке поля Наименование специальностии Количество коммерческих студентов. Количество коммерческих студентовдолжно являться псевдонимом функции Соunt(Коммерческий); -
добавить в предложение WHERE условие для отбора в объединение только коммерческих студентов; -
добавить предложение GROUP BY для группировки данных по полю Наименование специальности.
6. Выполнить запрос и проверить полученную выборку. Затем сохранить запрос.
Вероятно, текст запроса будет иметь следующий вид:
SELECT Специальность.[Наименование специальности], СОUNT(ФИО) AS [Количество коммерческих студентов]
FROM Студент, Группа, Специальность
WHERE Специальность.[Номер специальности]= Группа. [Номер специальности] AND Студент.[Номер группы] =Группа.[Номер группы] AND Студент.Коммерческий=Yes
GROUP BY Специальность.[Наименование специальности];
Задание 16. Составить SQL-запрос для подсчета количества бюджетных (некоммерческих) студентов по каждому факультету
Запрос должен содержать поля: Наименование факультетаи Количество бюджетных студентов
.
Задание 17. Составить SQL-запрос для подсчета итоговой стоимости платы за обучение коммерческими студентами по каждой специальности.
Запрос должен содержать поля: Наименование специальности и Итого по специальности.
Итого по специальностирассчитывать исходя из стоимости обучения по специальности и количества коммерческих студентов, обучающихся по специальности.
Связь между таблицами с помощью предложения INNER JOIN.
В этом случае синтаксис запроса на соединение будет иметь вид:
SELECT список полей
FROM таблица_1 INNER JOIN таблица_2
ON таблица_1 .поле_1 оператор таблица_2.поле_2
Например.
SELECT*
FROM Группа INNER JOIN Студент
ON Группа.[Номер группы]=Студент.[Номер группы];
Если задаются условия, основанные на равенствах, то выполняется внутреннее соединение2-х таблиц.
Чтобы установить связь по нескольким полям, следует связать несколько предложений ON в инструкции JOIN:
SELECT список_полей
