Министерство образования и науки Российской Федерации
Балтийский государственный технический университет “Военмех”

БАЗЫ ДАННЫХ

Практикум

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

2005


Составитель В.Н. Каминский, канд. техн. наук, доцент

УДК 681.3.07

Базы данных: Практикум. /Сост. В.Н. Каминский; Балт. гос. техн. у‑нт. – СПБ., 2005. – с.

Содержит описание комплекса лабораторных работ по созданию и использованию баз данных. Подробно рассматривается составление запросов к базам данных на языке SQL.

Предназначены для студентов факультетов Р и Ф, изучающих дисциплины “Базы данных” и “Организация баз данных”.

Р е ц е н з е н т  канд. техн. наук, доцент В.К. Кругликов

Утверждено
редакционно-издательским
советом университета

©В.Н. Каминский

© БГТУ, 2005


ОГЛАВЛЕНИЕ

Введение. 4

Особенности СУБД Access. 5

Часть I. Построение информационной системы.. 7

Лабораторная работа № 1. Создание простейшей базы данных. 7

Лабораторная работа № 2. Создание экранных форм для просмотра, ввода и редактирования. 9

Лабораторная работа № 3. Создание кнопочной формы.. 13

Лабораторная работа №  4. Импорт и экспорт данных. 17

Лабораторная работа № 5. Определение связей между таблицами и создание подчинённых форм. 19

Лабораторная работа № 6. Поддержка целостности базы данных, имеющей связи «многие ко многим». 22

Лабораторная работа № 7. Формы для просмотра и редактирования данных, имеющих связи «многие ко многим». 24

Лабораторная работа № 8. Формы для ввода связей «многие ко многим»  28

Лабораторная работа № 9 Главная (управляющая) форма информационной системы «Кафедра». 29

Часть II. Запросы к базам данных. 32

Лабораторная работа № 10. Создание запросов с помощью конструктора Access. 32

Лабораторная работа № 11. Создание и изменение объектов базы данных средствами  SQL. 34

Лабораторная работа № 12. Оператор SELECT. Выборка всех записей из одной таблицы.. 39

Лабораторная работа № 13. Оператор SELECT … WHERE. Выборка из таблицы записей, удовлетворяющих заданному условию.. 43

Лабораторная работа № 14. Предложения GROUP BY и HAVING.. 48

Лабораторная работа № 15. Многотабличные запросы.. 50

Лабораторная работа № 16. Предикат NULL.        Подзапросы. Предикаты EXISTS, ANY, ALL. 54

Лабораторная работа № 17. Объединение результатов нескольких запросов – UNION. Создание таблицы из существующих таблиц – SELECT … INTO   57

Лабораторная работа № 18. Операторы INSERT, UPDATE, DELETE. 58

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

 

Введение

Предлагаемые лабораторные работы предназначены для практической работы студентов, изучающих курс «Базы данных». Выборочно их можно использовать и в курсе «Информатика» при изучении тем «Базы данных» и пакета прикладных программ (ППП) Microsoft Office.

Для проведения описываемых лабораторных работ используется система управления базами данных (СУБД) Microsoft Access. Выбор СУБД Access объясняется тем, что она входит в состав ППП Microsoft Office, установленнного практически на всех персональных компьютерах.

Темы лабораторных работ разбиты на две части. Первая часть посвящена практическим приёмам построения информационных систем. Во второй части отрабатывается техника составления запросов к базе данных как с помощью средств, предоставляемых Access, так  и непосредственно на языке SQL.

Все лабораторные работы первой части делаются с созданной в первой работе базой данных «кафедра» и тесно связаны друг с другом. Объекты (таблицы, формы, поля и т.д.) созданные в одних лабораторных работах, используются в других, поэтому следует давать объектам названия, указанные в данном описании, иначе в дальнейшем будет трудно понять, о каком объекте идёт речь.

Особенности СУБД Access

Разработчики СУБД Access   используют терминологию, несколько отличающуюся от общепринятой. Своеобразно трактуются термины «база данных», «запрос», «макрос». Приведём часто используемые в литературе определения понятий «база данных», «СУБД» и «информационная система».

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

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

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

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

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

В состав созданной с помощью СУБД Access базы данных входят следующие компоненты:

-         таблицы,

-         запросы,

-         формы,

-         отчёты,

-         макросы,

-         модули.

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

Обычно под запросом понимается команда на выборку данных из базы (в SQL – команда SELECT). Часто под запросом понимают команды на вставку, редактирование и удаление данных. В Access под запросом (на выборку) понимается не только команда, но и результат выборки, т.е. запрос в Access – это обзор (VIEW).

Под макросом первоначально понималась маленькая программка, запускавшаяся нажатием заданного сочетания клавиш. В дальнейшем это понятие расширилось и сейчас макрос – это любая программа, запускающаяся нажатием заданного сочетания клавиш или щелчком мышкой по какому-либо объекту (кнопке, иконке и т.д.) на экране дисплея. В ранних версиях Word, Excel  и Access для создания макросов использовались разные средства. После включения Word, Excel  и Access в ППП Microsoft Office для написания макросов во всех трёх программах стал использоваться язык Visual Basic for Applications (VBA). В Access можно создавать макросы и старым способом и на VBA. Чтобы отличать друг от друга макросы двух видов, «старые» макросы в Access называют по-прежнему макросами а новые, написанные на VBA, называют процедурами.


Часть I. Построение информационной системы

Лабораторная работа № 1. Создание простейшей базы данных

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

Создание базы данных. В Access базу данных можно создать двумя способами:

-         при открытии выбрать в открывшемся окне переключатель «Новая база данных»,

-         выбрать в меню Access пункт «Файл». Далее последовательно выбрать: пункт меню Создать    вкладка  Общие   значок База данных.

Присвойте создаваемой базе данных имя «Кафедра.mdb» и поместите её в свой каталог.

Создание таблицы. После открытия или создания базы данных появляется окно базы данных с названием базы в верхнем левом углу. В расположенном слева списке объектов, выберите «Таблицы». Создайте в режиме конструктора таблицу «преподаватели».

Таблица должна состоять из девяти колонок (полей). Имена и характеристики полей приведены в табл. 1.

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

Ограничение на значение задаётся в «свойствах поля» в нижней части окна конструктора таблиц при выборе вкладки «Общие», а список допустимых значений ­­­– при выборе вкладки «Подстановка». При задании списка допустимых значений необходимо выбрать следующие значения свойств:

-         для свойства «Тип элемента управления» – поле со списком или список,

-         для свойства «Тип источника строк» – список значений.

-         для свойства «Ограничиться списком» – да.

Таблица 1

Имя поля

Характеристика

Тип данных

Размер поля

Ограниче-ние на значение

Список допустимых значений

Код_преп

Счётчик

Длинное целое

 

 

ФИО

Текстовый

40

 

 

Дата_рожд.

Дата/время

 

>#01.01.1910#

 

Зарплата

Числовой

Длинное целое

<100000

 

Должность

Текстовый

16

 

ассистент; ст.преподаватель; доцент; профессор

Степень

Текстовый

6

 

к.т.н.; к.э.н.; д.т.н.; д.э.н.

Звание

Текстовый

9

 

доцент; профессор

Биография

Поле МЕМО

 

 

 

Фотография

Поле объекта OLE

 

 

 

Значения перечисляются через точку с запятой в свойстве «Источник строк».

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

Присвойте таблице имя «преподаватели». Подтвердите создание ключевого поля.

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

Обратите внимание на то, что счётчик (поле Код_преп) автоматически увеличивается на единицу после ввода очередной записи. Значение счётчика

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

Тип «Поле объекта OLE» служит для вставки в запись документов Word, электронных таблиц Excel и других объектов, или для связи с этими объектами. Для вставки нужно щёлкнуть правой кнопкой мышки по полю и в

появившемся меню выбрать «Вставить объект». В появившемся окне выбрать переключатель «Создать из файла», щёлкнуть по кнопке «Обзор» и выбрать файл с рисунком из каталога, указанного преподавателем.

В поле МЕМО может находиться текст, имеющий длину до 64000 символов.

Лабораторная работа № 2. Создание экранных форм для просмотра, ввода и редактирования

Цель работы: научиться создавать удобные экранные формы для трёх основных видов работ в информационной системе: просмотра, ввода и редактирования, научиться пользоваться этими формами, изучить различные права доступа к данным.

Кроме перечисленных видов работ к основным относится ещё и удаление, рассматриваемое в лабораторной работе № 3.

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

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

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

Создание формы для просмотра. В окне базы данных выберите объект «Формы» и пункт меню «Создать». Откроется окно «Новая форма». Выберите в верхней части окна пункт меню «Конструктор» и в нижней части окна укажите таблицу «преподаватели». Нажмите «ОК». Должны появиться 3 окна: форма, список полей и панель элементов. Список полей и панель элементов можно открыть через пункт меню Access «Вид» в верхней части экрана.

Перетащите мышкой из списка полей в форму поля «ФИО», «Дата_рожд.», «Зарплата», «Биография» и «Фотография». Эти поля таблицы отобразятся в форме также в виде полей. Для поля «Фотография» будет создан элемент «Присоединённая рамка объекта».

При перетаскивании в форму поля «Должность» Access создаёт элемент «список», в котором будут показаны все допустимые значения должности. Так как при просмотре список не нужен, то для «Должности» нужно создать элемент «поле» вручную. Для этого щёлкните мышкой по элементу «поле» на панели элементов. Переместите указатель мышки в форму и раздвиньте поле до подходящих, на Ваш взгляд, размеров. Введите вместо «поле0» название поля. Теперь нужно привязать поле формы к полю «Должность» таблицы «преподаватели». Щёлкните правой кнопкой мышки по создаваемому полю в форме. В появившемся меню выберите пункт «Свойства». Появится окно свойств поля. Выберите вкладку «Данные» и в свойстве «Данные» выберите «Должность».

Аналогично создайте поля для «Степени» и «Звания».

Для ввода названия формы откройте раздел формы «Заголовок», выполнив пункты меню Access «Вид»  «Заголовок/примечание формы». Название  формы должно выглядеть примерно так:

П Р Е П О Д А В А Т Е Л И

(форма для просмотра)

В форме, предназначенной только для просмотра, необходимо запретить пользователю вносить какие-либо изменения в данные. Щёлкните правой кнопкой мышки по квадратику в левом верхнем углу формы и в раскрывшемся меню выберите пункт «Свойства». Должно открыться окно «Форма». Выберите вкладку «Данные». Установите для свойства «Тип набора записей» значение «Статический набор».

Отредактируйте по своему вкусу названия полей и формы.

Перейдите в режим формы и проверьте её работу. Форму можно просматривать в трёх режимах:

-         простая форма,

-         ленточная форма,

-         таблица.

Чаще всего при просмотре используется режим «Таблица».  Просмотрите форму во всех трёх режимах. Режимы просмотра устанавливаются Конструктором . В режиме конструктора откройте окно свойств формы и выберите вкладку «Макет». Подберите нужные значения свойств «Режим по умолчанию» и «Допустимый режим». Режим «Ленточная форма» не может использоваться при включении в форму подчинённых форм.

Создание формы для редактирования. Воспользуйтесь помощью мастера. В окне базы данных выберите объект «Формы»  и  затем – «Создание формы с помощью мастера». В появившемся окне «Создание формы» выберите таблицу «преподаватели» и все поля за исключением поля «код_преп». Следуйте далее указаниям мастера. Форму назовите «преп_ред». Если при просмотре Вам не понравится вид формы, то перейдите в режим конструктора и внесите изменения в свойства либо всей формы, либо отдельных элементов.

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

Создание формы для ввода новых записей. Форма для ввода отличается от формы для редактирования только значением свойства «Ввод данных».

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

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

Перейдите в режим формы и введите данные о двух-трёх преподавателях.

Лабораторная работа № 3. Создание кнопочной формы

Цели работы:

-          научится создавать форму, с помощью которой множество объектов (формы, отчёты, запросы) объединяются в единую, управляемую пользователем систему;

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

-         познакомиться с безопасными приёмами удаления записей из базы.

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

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

Рис. 1. Пример формы

Создание поля со списком для выбора записи. Создайте поле со списком преподавателей. Из этого списка пользователь сможет выбрать пре­подавателя, сведения о котором нужно про­смотреть. Выберите на панели элементов поле со списком и по­местите его в форму. Подтвер­дите, что поле со списком будет использовать значения из таб­лицы или запроса и щёлкните по кнопке «далее». Выберите таб­лицу «преподаватели» и снова щёлкните по кнопке «далее».

Для отображения в поле со списком нужно кроме поля «ФИО» выбрать ключевое поле «код_преп» и показывать в форме при про­смотре списка только «ФИО». Все значения ключевого поля по определению оригинальны. Люди с одинаковыми фами­лиями и инициалами иногда встречаются. Если указать для отображения в списке только поле «ФИО», то Access (и дру­гие СУБД) всегда будет выби­рать из таблицы первую по порядку из двух запи­сей, имеющих одинаковые значения поля «ФИО». Если в строке списка есть и «код_преп» и «ФИО», то из таблицы будет выбрана запись, содержащая значение поля «код_преп».

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

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

Выберите на панели элементов кнопку и поместите её в форму. В от­крывшемся окне «Создание кнопок» выберите категорию «Работа с формой» и действия «Открыть форму». Далее выберите форму «преп_просм», далее – переключатель «Открыть форму и показать все записи». далее – переключа­тель «Текст» и наберите в поле надпись на кнопке «Просмотр всех препода­вателей». Задайте имя кнопки «откр_таб_преп». Созданная кнопка будет от­крывать «преп_просм» в режиме формы.

Для того чтобы форма «преп_просм» открывалась в режиме таблицы, внесите изменения в процедуру, которую Access автоматически создал вме­сте с кнопкой. Для этого в режиме конструктора откройте окно свойств кнопки «откр_таб_преп» и в нём вкладку «События». Щёлкните мышкой по свойству «Нажатие мышки», а затем щёлкните по квадратной кнопке с мно­готочием, расположенной справа. Откроется окно Visual Basic. В процедуре «Откр_таб_преп_Click()» в строчку

DoCmd.OpenForm stDocName, , , stLinkCriteria

вставьте после первой запятой слово «acFormDS» (это параметр, за­дающий открытие формы  в режиме таблицы). Строка примет вид:

DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria

Эта строка содержит команду на открытие формы. Значения «слов» в строке следующие:

-         DoCmd – выполнить команду;

-         OpenForm – команда «открыть форму»;

-         StDocName – переменная, содержащая имя формы.

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

Закройте окно Visual Basic и проверьте работу кнопки.

Создание кнопок для просмотра и редактирования данных об опре­делённом  преподавателе. Создадим кнопку, с помощью которой будет открываться форма для просмотра данных об одном преподавателе. ФИО преподавателя  выбирается  из поля со списком.

Выберите на панели элементов кнопку и поместите её в форму. В открывшемся окне «Создание кнопок» выберите категорию «Работа с формой» и действия «Открыть форму». Далее выберите форму «преп_просм», далее – переключатель «Открыть форму для отобранных записей».

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

 Далее выберите переключатель «Текст» и наберите в поле надпись на кнопке «Просмотр одного преподавателя». Задайте имя кнопки «откр_один_преп».

Кнопка для редактирования данных о преподавателе создаётся аналогично. Отличие состоит только в названиях кнопки и открываемой формы. Назовите кнопку «Редактирование и удаление», так как в дальнейшем в форме для редактирования будет кнопка для удаления записи .

Кнопка для ввода записи создаётся аналогично двум предыдущим, только не нужно связывать поля и исправлять процедуру.  Выберите переключатель «Открыть форму и показать все записи», надпись на кнопке  «Ввод преподавателя». Задайте имя кнопки «ввод_преп».

Кнопка для удаления записи. Чтобы уменьшить возможности ошибиться при удалении записи, необходимо:

-          в момент нажатия кнопки удаления видеть удаляемую запись,

-         после нажатия кнопки для удаления записи пользователь должен подтвердить приказ об удалении.

Создайте кнопку для удаления записи в форме для редактирования. Выберите категорию «Обработка записей» и действие «Удалить запись». На кнопке напишите «Удаление». Попробуйте удалить одну запись.

Внимание! При вводе новых записей номер удалённой записи не используется. В последовательности номеров образуется неустранимый разрыв.

На рис. 1 в форме «кафедра» изображена необязательная кнопка «Удаление». Для её  работы используется следующая процедура:

   Private Sub КнопкаУд_Click()
     Dim N_record As String
     Dim dbs As Database
     a = MsgBox("Удалять?", vbYesNo)
     If a = vbNo Then
       Exit Sub
     Else
       N_record = Me![ПолеСоСписком0]
       Set dbs = CurrentDb
       dbs.Execute "DELETE FROM преподаватели WHERE код_преп=" & N_record
     End If
   End Sub

Процедурой КнопкаУд_Click() удаляется из таблицы преподаватели запись, код (поле «код_преп») которой равен значению кода преподавателя, выбранному из поля со списком в форме «кафедра» (Me![ПолеСоСписком0]). Следует отметить, что перед удалением записи пользователь должен видеть всё её содержимое. Поэтому лучше кнопку для удаления поместить только в форме для просмотра или в форме для редактирования.

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

Лабораторная работа №  4. Импорт и экспорт данных

Цель работы: научиться загружать в базу из внешних источников большие объёмы данных и сбрасывать из базы данные в формате других приложений.

В качестве приложения, с которым будет взаимодействовать Access, выберем электронные таблицы Excel. Откройте файл «gruppy.xls». Ознакомьтесь с листами «группы» и «факультеты».

Импорт в новую таблицу. Импортируйте из файла «gruppy.xls» в новую таблицу  данные с листа «группы». Последовательность действий: Меню → Файл → Внешние данные → Импорт. Далее выберите файл «gruppy.xls» и следуйте указаниям Access. Наименования полей оставьте такими же, как в таблице Excel, выберите автоматическое создание ключа, новую таблицу в базе данных назовите «Группы».

Импорт в существующую таблицу. Создайте в базе данных таблицу «факультет» следующей структуры:

Имя поля

Тип данных

Размер поля

Код_фак

Счётчик

 

Факультет

Текст

50

Телефон

Текст

10

Декан

Текст

30

Секретарь

Текст

30

Сделайте поле «Код_фак» ключевым. Введите первую запись следующего содержания:

-         Факультет – Юридический,

-         Телефон    - 167,

-         Декан        - Семёнов А.Б.

-         Секретарь – Никитина А.Г.

Импортируйте в таблицу «факультет»  данные с листа ««факультеты» из файла «gruppy.xls». Последовательность действий: Меню  Файл  Внешние данные   Импорт. Далее выберите файл «gruppy.xls» и следуйте указаниям Access. Наименования соответствующих полей в таблицах Excel и Access должны совпадать.

Установка связи с таблицей в формате Excel. Можно работать с таблицей Excel так же, как и с таблицами базы данных Access, если установить с ней связь.

Установите связь с таблицей Excel, расположенной на листе «Список» в файле (книге) «Sortirovka1.xls». Последовательность действий: Меню  Файл  Внешние данные  Связь с таблицами. Далее выберите файл «Sortirovka1.xls» и следуйте указаниям Access.

Экспорт данных из базы в таблицу Excel. Экспортируем таблицу «Группы». Откройте таблицу «Группы» в Access. В меню Access выберите Файл  Экспорт. В нижней части открывшегося окна выберите тип файла «Microsoft Excel» версии, установленной на Вашем компьютере, например, «Microsoft Excel 97-2000 (*.xls)». . Назовите файл «ГруппыAcc» и сохраните его в Вашем каталоге.

Кнопки для экспорта и импорта данных. В Access есть макрос «ПреобразоватьЭлектроннуюТаблицу», с помощью которого можно импортировать и экспортировать данные. Создайте форму для экспорта таблицы «Группы».

Создайте кнопку «экспорт в Excel». В окне «Создание кнопки» нажмите «Отмена». Вызовите свойства кнопки. Щёлкните по свойству «нажатие кнопки». Справа появятся две квадратные кнопочки. Щёлкните по кнопочке с многоточием и в появившемся меню выберите «Макросы» Дайте любое название макросу. Выберите макрокоманду «ПреобразоватьЭлектроннуюТаблицу» и присвойте ей необходимые значения аргументов (в нижней части окна). Файл с экспортированной таблицей поместите в свой каталог

Проверьте  работу созданной формы.

Лабораторная работа № 5. Определение связей между таблицами и создание подчинённых форм

Цель работы: научиться устанавливать связи между таблицами и строить подчинённые формы.

Между реальными объектами, данные о которых хранятся в базе, могут существовать логические связи. Например, многие группы студентов входят в состав одного факультета, и ни одна группа не может относиться сразу к двум факультетам. Говорят, что между факультетом и группой имеется связь «один ко многим».

Отображение связи «один ко многим». В режиме конструктора дополните структуру таблицы «Группы» полем «код_ф», затем перейдите в режим просмотра таблицы и распределите группы по факультетам, то есть введите в поле «код_ф» коды факультетов, хранящиеся в поле «код_фак» таблицы «факультет». 

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

-         для свойства «Тип элемента управления» - Поле со списком,

-         для свойства «Тип источника строк» - Таблица или запрос,

-         для свойства «Источник строк» - факультет,

-         для свойства «Присоединённый столбец» -  1,

-         для свойства «Число столбцов» -  2,

-         для свойства «Ширина столбцов» – 0,

-         для свойства «Ограничиться списком» – да.

Теперь перейдите в режим таблицы. В поле «код_ф» должны появиться названия факультетов.

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

  Закройте таблицу «Группы». Выполните пункты меню Сервис Схема данных. В любом месте открывшегося окна щёлкните правой кнопкой мышки. В появившемся меню выберите пункт «Добавить таблицу» и добавьте таблицы «Группы» и «факультет». Левой кнопкой мышки соедините поле «код_фак» и поле «код_ф» связываемых таблиц. Должно появиться окно «Изменение связей».

Установите флажок «Обеспечение целостности данных». Теперь, если не устанавливать два других флажка, Access при попытке удалить из таблицы «факультет» любую запись будет проверять, нет ли в таблице  «Группы» кода удаляемой записи. Если такой код есть, то появится сообщение об ошибке. Другими словами, пока в базе данных указано, что на факультете есть хотя бы одна группа, удалить этот факультет нельзя.

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

Флажок «Каскадное обновление связанных полей» служит для автоматического обновления в таблицы со стороны «многие»  значения связанного поля, обновляемого в таблице со стороны «1». В нашем случае связанное поле – счётчик и оно не может обновляться.

 После закрытия окна «Изменение связей» на  схема данных появится связь «один ко многим» между таблицами «факультет» и «Группы» (рис. 2).

Создание подчинённой формы. Создайте новую форму. Назовите её «факультеты». Отобразите в ней все поля, кроме «код_фак».

Рис. 2. Схема данных

Теперь в форме «фа­культеты» нужно создать подчинённую форму, в кото­рой будут показываться дан­ные о группах, входящих в состав показы­ваемого в главной форме фа­культета. Раздвиньте гра­ницы формы «факультеты так, чтобы в ней поместилась таблица с данными о груп­пах. На па­нели элементов найдите эле­мент «Подчи­нённая форма/отчёт» и уста­новите его в форме «факуль­теты».

Рис.3. Форма факультет с подчинённой формой

В окне мастера подчи­нённых форм установите пе­реключатель «Имеющиеся таблицы и запросы» и щёлкните по кнопке «Далее». В следующем окне мастера подчинённых форм выберите таблицу «группы» и все поля, кроме «Код_гр». Выберите переключатель «Выбор из списка» и оставьте предложенное название подчинённой формы. На рис. 3 показана форма «факультет» с подчиненной формой,. в которой выведены группы, входящие в состав просматриваемого в основной форме факультета. Перейдите в режим формы и просмотрите несколько за писей.

Лабораторная работа № 6. Поддержка целостности базы данных, имеющей связи «многие ко многим»

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

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

Пример объектов, имеющих связи «многие ко многим» - преподаватели и предметы (дисциплины). Один преподаватель может вести несколько дисциплин, и одну дисциплину могут вести в разных группах разные преподаватели. Наиболее рациональный способ хранения данных о связях «многие ко многим» - это создание вспомогательной таблицы, в каждой записи которой хранятся код преподавателя и код предмета. Между основными таблицами и вспомогательной устанавливаются связи «один ко многим». Вспомогательной таблицы позволяет избежать избыточности данных в базе, уменьшить количество ошибок при вводе и упростить запросы к базе данных.

Создание таблиц. Создайте таблицу «дисциплины»,  имеющую следующую структуру:

№ п.п.

Имя поля

Тип данных

Размер поля

1

код_дис

Счётчик

Длинное целое

2

предмет

Текстовый

40

3

лекции

Числовой

Целое

4

практика

Числовой

Целое

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

Создайте таблицу «преп_дис», имеющую следующую структуру:

№ п.п

Имя поля

Тип данных

Размер поля

1

код_преп_дис

счётчик

Длинное целое

2

код_преп

Числовой

Длинное целое

3

код_дис

Числовой

Длинное целое

Введите в таблицу «преп_дис» несколько записей, связывающих преподавателей и дисциплины. На рис. 4 показан пример, в котором преподаватель Андреев В.К. ведёт Pascal, информатику и Fortran, а Семёнова Е.М. ведёт информатику и Pascal.

Внесение связей в схему данных аналогично внесению связей между таблицами «Группы» и «факультет» в предыдущей лабораторной работе. Только с таблицей «преп_дис» связаны две таблицы (рис. 5).

Лабораторная работа № 7. Формы для просмотра и редактирования данных, имеющих связи «многие ко многим»

Цели работы ­ –  научиться создавать:

-         подчинённые формы для просмотра данных из основных таблиц с использованием вспомогательных,

-         экранные формы, с помощью которых при вводе пользователем данных, имеющих связи «многие ко многим», автоматически заполняется вспомогательная таблица (см. лаб. работу №6) со связями «один ко многим».

Рис. 4. Связывание данных с помощью вспомогательной таблицы

Дополнение формы «преп_просм» подчинённой формой с данными о предме­тах, которые ведёт преподаватель. Откройте форму «преп_просм» в ре­жиме конструктора. На па­нели элементов найдите эле­мент «Подчинённая форма/отчёт» и установите его в открытой форме.

Далее описываются два способа создания подчинённой формы со связями «многие ко многим»:

-         с помощью мастера подчинённых форм,

-         с использованием запроса к базе данных.

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

Создание подчинённой формы с помощью мастера. В окне мастера подчинённых форм установите переключатель «Имеющиеся таблицы и запросы» и щёлкните по кнопке «Далее».

В следующем окне мастера подчинённых форм выберите

-         таблицу «дисциплины» и в ней все поля,

-          таблицу «преп_дис» и в ней  поля «код_преп» и «код_дис».

Перейдите в следующее окно и выберите переключатель «Выбор из списка» и оставьте предложенное название подчинённой формы.

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

Рис. 5. Схема данных с использованием вспомогательной таблицы

Создание подчинённой формы с использованием запроса к базе данных. Сначала нужно создать запрос на выборку из базы данных. (Подробно методы создания запросов рассматриваются во второй части данного пособия.) Откройте окно базы данных. В левой части окна выберите пункт меню «Запросы». В верхней части окна выберите вкладку «Создать». Создайте запрос в режиме конструктора.

В окне «Добавление таблицы» выберите таблицы «преподаватели», «дисциплины» и «преп_дис». Закройте окно «Добавление таблицы». окне «запрос на выборку» Перетащите мышкой поля из списков полей таблиц расположенных в верхней части окна «запрос на выборку», в нижнюю часть так , как это показано на рис. 6.

Выполните запрос с помощью пунктов меню Запрос  Запуск. Результат запроса – новая (временная) таблица, состоящая из записей, содержащих все допустимые комбинации значений полей из трёх исходных таблиц. Закройте окно запроса. Назовите запрос «преп_дисц».

Откройте форму «преп_просм» в режиме конструктора. Установите элемент «Подчинённая форма/отчёт» в  форму «преп_просм». Отмените использование мастера подчинённых форм (или закройте окно с сообщением о том, что мастер не установлен).

Рис. 6. Запрос, сформированный средствами Access

 Подведите указатель мышки к квадратику в верхнем левом углу подчинённой (внедрённой) формы и щёлкните правой кнопкой. В появившемся окне выберите пункт «Свойства». Появится окно «Форма/отчёт». Выберите в нём вкладку «Данные». Установите для свойства объект-источник  значение «Запрос.преп_дисц». Перейдите в режим просмотра формы. Примерный вид формы показан на рис. 7.

Дополнение формы «преп_ред» подчинённой формой с данными о предметах, которые ведёт преподаватель. Любым из двух способов, описанных выше в данной лаб. работе, вставьте в форму «преп_ред» подчинённую форму.

Рис. 7. Форма для просмотра данных о преподавателях

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

Самостоятельно создайте формы для ввода, просмотра и редактирования данных о дисциплинах, аналогичные формам для работы с данными о преподавателях. Эти формы понадобятся для построения информационной системы «Кафедра». Назовите формы «дис_ввод», дис_просм» и «дис_ред».

Лабораторная работа № 8. Формы для ввода связей «многие ко многим»

Цель работы: создать форму для автоматического заполнения вспомогательной таблицы, хранящей только коды записей двух таблиц со связей «многие ко многим».

 Предполагается, что при эксплуатации создаваемой формы (рис. 8) пользователь, распределяющий нагрузку преподавателей, будет указывать преподавателя и ведомую им дисциплину, а СУБД Access, исходя из этих данных, будет создавать в таблице «преп_дис» запись с кодами выбранных преподавателя и дисциплины.

Рис.8. Форма для ввода учебной загрузки преподавателей

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

Создайте форму в режиме конструктора. Выберите таблицу «преп_дис» в качестве источника записей. Укажите, что форма предназначена для ввода.

Создайте поле со списком для выбора преподавателя. Для этого выберите на панели элементов поле со списком и поместите его в форму. Подтвердите, что поле со списком будет использовать значения из таблицы или запроса и щёлкните по кнопке «далее». Выберите таблицу «преподаватели» и снова щёлкните по кнопке «далее».

Включите в поле со списком поля «код_преп» и «ФИО».   Подтвердите, что нужно скрыть ключевой столбец. Далее нужно указать, что выбранное из таблицы «преподаватели» значение поля «код_преп» нужно сохранить в поле «код_преп» создаваемой записи в таблице «преп_дис».

Создайте поле со списком для выбора дисциплины. Все действия такие же, как и при создании предыдущего поля со списком. Только нужно выбрать таблицу «дисциплины» и поле «код_дис».

Кнопки создаются так же, как и в лаб. работе № 3.

Лабораторная работа № 9 Главная (управляющая) форма информационной системы «Кафедра»

Цель работы: объединить в единую систему с помощью управляющей формы все объекты созданные в лабораторных работах №№ 1, 2, 3, 6, 7, 8. Управляющая форма позволит пользоваться информационной системой, не зная ни структуры базы данных, ни названий форм.

 

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

Главная форма. В лабораторной работе № 3 была разработана кнопочная форма (рис. 1). Необходимо дополнить её средствами для работы с дисциплинами и распределения загрузки преподавателей (рис.9).

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

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

Рис. 9. Главная форма информационной системы «Кафедра»

В таблице «дисциплины» два потенциальных ключа: «код_дис» и «предмет». Поле «код_дис» назначено (первичным) ключом. Поэтому необходимо сделать столбец «предмет» индексированным с запретом поторяющихся значений.

Откройте таблицу «дисциплины» в режиме конструктора.  Установите курсор на поле «предмет» и выберите для свойство «индексированное поле» значение «Да (Совпадения не допускаются)».

В таблице «преп_дис» нельзя допустить повторения пары значений «код_преп» и «код_дис». Нужно создать составной ключ из этих полей. Откройте таблицу «преп_дис» в режиме просмотра и убедитесь в том что ни одному преподавателю не назначена дважды одна дисциплина.  Перейдите в режим конструктора и выделите поля «код_преп» и «код_дис». Щёлкните по значку с изображением ключа на панели инструментов.

Теперь при попытке повторно ввести данные в таблицы «дисциплины» и «преп_дис» Access будет сообщать об ошибке.

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

Проверьте правильность работы всех элементов главной формы.

Введите через форму для ввода данные о трёх-четырёх преподавателях.

Введите через форму для ввода данные о пяти-шести дисциплинах.

Через форму  «загрузка» распределите учебную загрузку между преподавателями. Убедитесь в том, что попытка дважды назначить одному преподавателю один и тот же предмет вызывает сообщение об ошибке.

Проверьте работу форм для просмотра и редактирования.

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

Исправьте обнаруженные ошибки, добавьте недостающие формы и элементы.


Часть II. Запросы к базам данных

В большинстве запросов, рассматриваемых во II части, будут использоваться таблицы «Заказы», «Заказано» и «Товары», взятые из учебной базы данных «Борей». Импортируйте эти таблицы в свою базу данных. Для этого выберите последовательно пункты меню Access Файл  Внешние данные  Импорт.

Лабораторная работа № 10. Создание запросов с помощью конструктора Access

Цель работы: научиться создавать с помощью конструктора одно- и многотабличные запросы на выборку записей с заданным набором полей и удовлетворяющие заданным условиям.

Во всех современных СУБД запросы пишутся на языке SQL (Structured Query Language – язык структурированных запросов). В ACCESS также есть возможность писать запросы на SQL, но разработчики ACCESS ориентируют пользователя на максимальное использование средств автоматизации для создания и ведения баз данных. К  этим средствам относится конструктор запросов, с помощью которого можно быстро создать многие запросы.

Запрос на просмотр всех данных одной таблицы. (Этот запрос неявно генерируется  СУБД Access  при открытии таблицы в режиме «таблица».) Для создания запроса откройте окно базы данных. Выберите объект «Запросы». В верхней части окна выберите вкладку «Создать». Создайте запрос в режиме конструктора.

В окне «Добавление таблицы» выберите таблицу «Заказы». Закройте окно «Добавление таблицы». В окне «запрос на выборку» Перетащите символ «*» (звёздочка) из списка полей таблицы «Заказы» в крайнее левое поле в нижней части окна. После этого запрос готов. Выполните его, выбрав в меню пункты Запрос Запуск.

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

Во всех современных СУБД запросы пишутся на языке SQL. Просмотрите созданный запрос в режиме SQL (пункты меню Вид Режим SQL). Запрос имеет вид

SELECT Заказы.*
FROM Заказы;

         Запрос читается так: выбрать (SELECT) все поля (Заказы.*) из таблицы (FROM) Заказы.

Запрос на просмотр всех записей с заданным набором полей.  Измените предыдущий запрос. Замените символ «*» полем «КодКлиента». Добавьте в запрос поля  «ДатаИсполнения», «СтоимостьДоставки» и «НазваниеПолучателя». Просмотрите созданный запрос в режиме таблицы и в режиме SQL.

Выборка из таблицы записей, удовлетворяющих заданным условиям. Нужно выбрать из таблицы «Заказы» все заказы, у которых стоимостью доставки не меньше 35 р. и меньше 40 р. Для этого внесите изменения в предыдущий запрос. В условиях отбора поля «СтоимостьДоставки» запишите «>=35 AND <40». Здесь и далее кавычки «» в условие не входят.

Выполните запрос. Должно быть отобрано 22 заказа (записи).

Просмотрите созданный запрос в режиме SQL. Обратите внимание на условие после ключевого слова WHERE.

Добавьте в запрос ещё одно условие. В условия отбора поля «НазваниеПолучателя» запишите «Like ‘R*’», означающее «выбрать названия получателей, начинающиеся на R».

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

В таблицах «Заказы» и «Заказано» хранятся данные о заказах. Таблицы связаны с помощью поля «КодЗаказа».  В одном заказе заказывается несколько товаров. В таблице «Заказы» хранятся общие сведения о заказе, а в таблице «Заказано» ­– сведения о заказанных товарах из этого заказа. Тип связи между таблицами «Заказы» и «Заказано» – один ко многим. Такая же связь установлена между таблицами «Товары» и «Заказано»  через поле «КодТовара».

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

-         выбрать все три таблицы «Заказы», «Заказано» и «Товары»,

-         из таблицы «Заказы» выбрать поле «КодКлиента» , установить для него условие отбора «ANTON» и запретить его вывод на экран,

-         из таблицы «Товары» выбрать поле «Марка» и сортировку по возрастанию,

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

В режиме SQL запрос, сгенерированный Access, выглядит следующим образом:

SELECT DISTINCT Товары.Марка
FROM Товары INNER JOIN (Заказы INNER JOIN Заказано ON Заказы.КодЗаказа = Заказано.КодЗаказа) ON Товары.КодТовара = Заказано.КодТовара
WHERE (((Заказы.КодКлиента)="ANTON"))
ORDER BY Товары.Марка;

Лабораторная работа № 11. Создание и изменение объектов базы данных средствами  SQL

Цель работы: научиться создавать и изменять средствами SQL таблицы и индексы, задавать ограничения целостности.

Под созданием и изменением здесь подразумевается только создание и изменение структуры и параметров таблиц, а не хранящихся в них данных. Часть языка SQL, служащая для решения этих задач, называется языком описания данных (Date Definition Language – DDL).

Создание таблицы. Для создания таблицы в SQL служит команда CREATE TABLE.Синтаксис простейшего варианта команды CREATE TABLE:

    CREATE TABLE <Имя таблицы >
  ( <
имя поля > <тип данных>[(<размер>)],
         <
имя поля > <тип данных>[(<размер>] ... );

В стандартном языке SQL применяются следующие типы данных:

-         INTEGER –  до 10 цифр и знак;

-         SMALL – до 5 цифр и знак;

-         DECIMAL(p,q) – 0<p<16 всего позиций, q – цифр после запятой;

-         FLOAT – вещественное, определяется СУБД (REAL в ACCESS);

-         DOUBLE PRECISION – вещественное, определяется СУБД (FLOAT в ACCESS!!!), точность и диапазон больше, чем у FLOAT;

-         CHAR(n) – строка из n (n<256) символов.

Практически во всех СУБД, поддерживающих SQL, применяются дополнительно следующие типы данных:

-         VARCHAR(n) – строка из n  символов (nmax >4096 определяется СУБД );

-         DATE – формат определяется специальной командой (по умолчанию mm/dd/yy);

-         TIME – формат определяется специальной командой (по умолчанию hh.mm.ss);

-         DATETIME – комбинация даты и времени;

-         MONEY – денежный.

Подробнее о типах данных, поддерживаемых СУБД ACCESS, смотрите в справке ACCESS в ответе на вопрос «Типы данных SQL».

Пример команды на создание таблицы «Страна» (название, площадь, численность населения в млн чел.):

CREATE TABLE Страна
(название
CHAR(60),
площадь
REAL,
население
REAL);

Самостоятельно с помощью команды CREATE TABLE создайте таблицу «Изделие» со следующими атрибутами:

-         название

-         цена

-         вес

-         дата изготовления

-         фирма-производитель

Подберите соответствующие типы данных.

Создание запросов на SQL в Access начинается вызовом конструктора запросов. Для этого в окне базы данных нужно выбрать объект «Запросы», пункт меню «Создать» и в окне «Новый запрос» пункт «Конструктор». Далее, не выбирая таблицу, закройте окно «Добавление таблицы» и перейдите в режим SQL. Переход в режим SQL : меню Access Вид  Режим SQL.

Набрав в окне SQL запрос на создание таблицы «Изделие», выполните его.

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

 

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

ALTER TABLE <имя таблицы> {ADD {COLUMN <имя поля> <тип поля>[(<размер>)] [NOT NULL]     [CONSTRAINT <имя индекса>] |
    ALTER COLUMN <имя поля> <тип поля>[(<размер>)]|
    CONSTRAINT <описание составного индекса>} |
    DROP {COLUMN <имя поля>  | CONSTRAINT <имя индекса>} };

Команда, с помощью которой к таблице «Страна» добавляется поле «столица» выглядит так:

ALTER TABLE Страна
ADD COLUMN столица VARCHAR(40) NOT NULL UNIQUE;

На поле «столица» наложены 2 ограничения: не допускается пустое поле (NOT NULL) и название столицы должно быть уникальным (UNIQUE).

Для добавления к таблице «Страна» поля, являющегося первичным ключом, служит команда

ALTER TABLE Страна
ADD COLUMN Id_strana INTEGER NOT NULL PRIMARY KEY;

В поле Id_strana должен храниться номер записи. Приведённая выше команда не создаёт автоматического счётчика.

Самостоятельно с помощью команд ALTER TABLE добавьте к таблице «Изделие» следующие поля:

-         Id_izdelie – номер записи и первичный ключ;

-         сорт – сорт (1-й, 2-й, …), не допускается пустых полей.

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

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

-         Id_otd – номер записи (первичный ключ);

-         Id_st   - внешний ключ, связывающий с таблицей «Страна»;

-         курорт – название курорта;

-         гостиница;

-         продолжит – продолжительность отдыха в днях.

Совокупность  значений полей «Id_st», «курорт» и «гостиница» должна быть уникальной, то есть, потенциальным ключом. Таким образом, в таблице «курорт» будет 2 ключа. Описанная таблица создаётся следующей командой:

CREATE TABLE отдых
(
Id_otd INTEGER NOT NULL PRIMARY KEY,
 
Id_st INTEGER REFERENCES Страна(Id_strana),
  курорт
CHAR(80),
  гостиница
CHAR(60),
  стоимость
REAL,
  продолжительность
SMALLINT,
 
UNIQUE (Id_st,курорт,гостиница));

Самостоятельно с помощью команд CREATE TABLE создайте таблицу «поставка», связанную с таблицей «Изделие».

Таблица «Поставка» должна иметь следующие поля:

-         номер записи (первичный ключ),

-         внешний ключ, связывающий с таблицей «Изделие»;

-         адрес клиента,

-         дату поставки,

-         количество товара,

-         стоимость доставки.

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

После создания таблицы «Поставка» введите в неё несколько записей. Для того, чтобы убедиться  в правильной работе потенциального ключа, попытайтесь ввести две записи с одинаковыми значениями внешнего ключа, адреса клиента и даты поставки.

Добавьте таблицы «Изделие» и «Поставка» к схеме данных (Меню ACCESS   Схема данных). ACCESS должен автоматически обнаружить связь «один ко многим», заданную при создании таблиц командой CREATE TABLE.

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

CREATE [UNIQUE] INDEX <имя индекса>

ON <имя таблицы> <(список полей)>

Для построения индекса по полю «название» в таблице «Страна» служит следующая команда:

CREATE UNIQUE INDEX названиеIn

ON страна(название)

Самостоятельно постройте уникальный индекс для поля «Название» таблицы «Изделие». Проверьте его действие, введя две записи с одним наименованием изделия.

Лабораторная работа № 12. Оператор SELECT. Выборка всех записей из одной таблицы

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

Запрос на выборку всей таблицы. В лабораторной работе № 10 такой запрос уже был сформирован средствами Access. Можно упростить вид запроса, если вместо запроса

SELECT Заказы.* FROM Заказы;

написать запрос  

SELECT * FROM Заказы

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

Создание запросов на SQL в Access начинается вызовом конструктора запросов. Для этого в окне базы данных нужно выбрать объект «Запросы», пункт меню «Создать» и в окне «Новый запрос» пункт «Конструктор». Далее выберите таблицу «Заказы» и перейдите в режим SQL. Переход в режим SQL: меню Access Вид  Режим SQL.

Зокончите формирование запроса и выполните его.

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

SELECT КодЗаказа AS Заказ, НазваниеПолучателя AS Получатель, АдресПолучателя AS Адрес, ДатаИсполнения AS Дата
FROM Заказы
ORDER BY НазваниеПолучателя ASC;

В примере КодЗаказа, НазваниеПолучателя, АдресПолучателя и ДатаИсполнения – имена полей в таблице «Заказы». При выводе результатов запроса на экран дисплея имена полей будут заменены соответствующими псевдонимами, указанными после слова AS.

Предложение

ORDER BY НазваниеПолучателя ASC

служит для сортировки отобранных записей по возрастанию (т.е. в алфавитном порядке) значения поля НазваниеПолучателя. Если нужно сортировать по убыванию, то вместо  ASC нужно использовать DESC (сокращение от descending).

Сформируйте и выполните этот запрос.

Вывод записей без дублирования. Сформируйте и выполните следующий запрос

SELECT НазваниеПолучателя AS Получатель
FROM Заказы
ORDER BY НазваниеПолучателя DESC.

Названия получателей многократно повторяются, так как выбраны все записи таблицы. Чтобы не было дублирования записей, добавьте в запрос после слова SELECT слово DISTINCT. Иногда в СУБД режим DISTINCT установлен по умолчанию. Для вывода всех записей в этом случае после слова SELECT вставляется слово ALL.

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

Умножение. Сформируйте запрос на вывод из таблицы «Заказано» кода товара, цены, количества и общей стоимости заказанного товара. Запрос выглядит так:

SELECT КодТовара,Цена,Количество,Цена*Количество AS Стоимость
FROM Заказано;

Самостоятельно дополните запрос стоимостью со скидкой.

Использование функций. Функция STR() предназначена для преобразования  в текстовый тип. Для вывода на экран дисплея стоимости товара в тысячах рублей с указанием единицы измерения служит следующий запрос:

SELECT КодТовара,str(Цена*Количество/1000)+' тыс. руб' AS Стоимость FROM Заказано;

Для того чтобы в колонке «Стоимость» печатались число и текст, нужно преобразовать число в текстовый тип  и объединить с текстом 'тыс. руб.'. Для преобразования служат функция str(<выражение числового типа>)  и операция слияния «+» (конкатенация).

Сформируйте запрос, в котором из таблицы «Заказы» выбираются 5 полей и результат выводится в две колонки. В первую колонку выводится  поле «КодЗаказа», а в колонке с псевдонимом «Адрес клиента» объединены следующие поля: ИндексПолучателя, СтранаПолучателя, ГородПолучателя, НазваниеПолучателя.

Не забудьте поставить между объединяемыми полями адреса запятую с пробелом. Результат запроса (показаны две первые строки) должен иметь вид:

Код заказа

Адрес клиента

10248

90110, Финляндия, Оулу, Wartian Herkku

10249

44087, Германия, Мюнстер, Toms Spezialitaten

 

Функция выделения части даты DATEPART(). Познакомьтесь с описанием этой функции в справке Access (Содержание, раздел «Справочник по языку Visual Basic», пункт «Functions», буква D).

Определите с помощью запроса к таблице «Заказы», за какие годы были поставки товаров.

Агрегатные функции. (В Access они называются статистическими). Подсчитаем общее количество записей в таблице «Заказы» и количество записей содержащих данные в поле «ОбластьПолучателя», то есть, количество записей с непустым полем «ОбластьПолучателя». Для этого выполним следующий запрос:

SELECT count(*),count(ОбластьПолучателя)
FROM Заказы;

В запросе используется агрегатная функция COUNT(). Используя агрегатные функции MAX(), MIN() и AVG(), составьте запрос для подсчёта максимальной минимальной и средней цены товара в таблице «Товары».

Используя агрегатную функцию SUM(), составьте запрос для подсчёта общей стоимости доставки всех заказанных товаров в таблице «Заказы».

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

Лабораторная работа № 13. Оператор SELECT … WHERE. Выборка из таблицы записей, удовлетворяющих заданному условию

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

Синтаксис оператора выборки по условию следующий:

SELECT <список полей и выражений>

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

WHERE <условие> .

Условие – это выражение, принимающее значение «истина» или «ложь». Такое выражение называется предикатом. В предикате могут использоваться:

-         поля,

-          константы,

-         арифметические действия  +, - , *, / , возведение в степень **,

-          операторы отношения =, <, >, <= ,>=, <>,

-         логические операции  NOT, OR, AND.

В SQL имеются специальные предикаты BETWEEN, IN, LIKE, IS

NULL, ANY или SOME, ALL, EXISTS.

Использование в условии логических операций. Пусть из таблицы «Заказано» нужно выбратьтовары с ценой не меньше ста рублей и не больше двухсот, вывести все поля таблицы, кроме поля «КодЗаказа» и сформировать расчётное поле, в котором показать стоимость товара с учётом скидки. Запрос имеет вид:

SELECT КодТовара, Цена, Количество,
 Скидка, Цена* Количество*(1- Скидка)
AS [Стоимость со скидкой]
FROM Заказано
WHERE Цена>100 And Цена<200;

Обратите внимание на то, что скидка хранится в базе данных не в процентах, а в сотых долях от стоимости товара. Скидка 7% в базе хранится как 0.07. Выполните этот запрос.

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

Отсортируйте выбранные записи в порядке убывания цен.

Использование агрегатных функций для отобранных записей. Сформируйте и выполните следующие запросы к таблице «Заказано»:

подсчитать суммарную стоимость всех заказов, с ценой меньше100 руб.;

подсчитать количество записей, в которых код заказа принимает значения 10252 или 10255 или больше 11000;

найти минимальную, максимальную и среднюю цены для товаров с количеством, равным 10.

Использование в условии выборки функций для работы с датами. При работе с базами данных часто приходится производить операции с датами. Познакомьтесь с описаниями функций для работы с датами в справке Access (Содержание, раздел «Справочник по языку Visual Basic», пункт «Functions», буквы С, D).

Функция CDATE(). Найдём в таблице «Заказы» все заказы, исполненные между 01.07.1996 и 01.01.1997. Для этого нужно преобразовать даты с помощью функции CDATE() из текстового типа в тип «date». Запрос имеет вид:

SELECT *
FROM Заказы
WHERE ДатаИсполнения>=CDATE('01.07.1996') AND ДатаИсполнения<CDATE('01.01.1997');

Сформируйте и выполните этот запрос.

Даты можно вычитать друг из друга. Разность получается в днях.

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

Функция DatePart() служит для выделения из даты её части, например, года. Познакомьтесь с описанием этой функции в справке Access (Содержание, раздел «Справочник по языку Visual Basic» пункт «Functions», буква D).

С помощью функции DatePart() найдите все заказы, размещённые

a)     в первом квартале 1997 г;

b)    по понедельникам в январе за все годы.

 

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

<выражение> IN (<список значений>)

Найдём в таблице «Заказано» все товары со скидками, равными 5%, 10%, 25% и 30%. Запрос имеет вид:

SELECT Цена, Скидка, КодЗаказа
FROM Заказано
WHERE Скидка In (0.05,0.1,0.25,0.3)
ORDER BY Скидка,Цена;

В запросе записи упорядочены по скидкам и цене в возрастающем порядке.

Самостоятельно найдите в таблице «Заказы» все заказы, размещённые:

a)     в мае , августе и декабре в1996 и 1998 годах;

b)    пятого августа в 1996, 1997 и 1998 годах.

 

Использование в условии выборки диапазона значений Диапазон значений задаётся с помощью предиката BETWEEN, имеющего следующий синтаксис:

<выражение> BETWEEN <значение 1> AND <значение 2>

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

Запрос на выборку из таблицы «Заказано» товаров с ценами между 500 и 1000 руб. можно составить так:

SELECT *
FROM Заказано
WHERE Цена BETWEEN 500 AND 1000
ORDER BY Скидка,Цена;

Внесите в этот запрос дополнительное условие: или со скидкой между 5% и 10%.

Можно выбирать символьные величины, например, названия стран, которые начинаются с букв из заданной последовательности. Выберем из таблицы «Заказы» все заказы из стран, названия которых начинаются на А, Б, В, …, К:

SELECT *
FROM Заказы
WHERE СтранаПолучателя Between 'А' And 'Л'
ORDER BY СтранаПолучателя

Обратите внимание на то, что диапазон поиска на одну букву больше, чем в условии задачи. Access ищет все названия стран, начинающиеся на А,Б, …,К и имеющие любую длину, а на букву ‘Л’ ищет названия страны длиной в одну букву, то есть страну ‘Л’. Можно в качестве конца диапазона указать ‘Кя’, тогда также будут найдены все страны на букву ‘К’.

Самостоятельно выберите из таблицы «Заказы:

a)     только поле СтранаПолучателя, при условии, что названия стран начинаются на А, Б, В или Р, С, Т и выборке не должно быть повторений названий стран.

b)     алфавитном порядке города (поле ГородПолучателя) от Лилля до Парижа.

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

Оператора LIKE сравнивает строковое поле со строковым выражением. Пусть, из таблицы «Заказы» нужно выбрать все заказы при условии, что название города, в котором находится получатель, начинается на «Л». Запрос выглядит так:

SELECT *
FROM Заказы
WHERE ГородПолучателя Like 'Л*';

Символ «*» означает «любая последовательность из нуля или более символов». Кроме «*» используются и другие символы групповой замены (willcards).

Познакомьтесь с описанием оператора LIKE в справке Access (Содержание, раздел «Справочник по языку Visual Basic», пункт «Operators», LIKE Operator). В табл. 2 приводятся примеры использования в операторе LIKE символов групповой замены.

Таблица 2

Тип совпадения

Образец

Совпадение
(True)

Несовпадение
(False)

Несколько знаков 

a*a

aa, aBa, aBBBa

aBC

*ab*

abc, AABB, Xab

aZb, bac

ab*

abcdefg, abc

cab, aab

Специальный знак

a[*]a

a*a

aaa

Одиночный знак

a?a

aaa, a3a, aBa

aBBBa

Одиночная цифра

a#a

a0a, a1a, a2a

aaa, a10a

Диапазон знаков

[a-z]

f, p, j

2, &

Вне диапазона

[!a-z]

9, &, %

b, a

Не цифра

[!0-9]

A, a, &, ~

0, 1, 9

Комбинированное выражение

a[!b-m]#

An9, az0, a99

abc, aj0

Сформируйте, используя оператор LIKE, и выполните следующие запросы к таблице «Заказы»:

a)      выбрать все заказы с названием города получателя, начинающимся на А, Л или П;

b)     изменить предыдущий запрос, выбирая только поле ГородПолучателя и не допуская повторений;

c)      выбрать все заказы с названием города получателя, начинающимся на Л, а со второй буквой – «и» или «о»;

d)     выбрать названия городов, состоящие из пяти букв и начинающиеся на букву П;

e)      выбрать заказы, в которых адрес получателя сдержит запятую;

f)       выбрать заказы, в которых адрес получателя начинается с цифры;

g)      выбрать заказы, в которых адрес получателя начинается не с цифры;

h)     выбрать заказы, в которых адрес получателя начинается не с букв с C (лат.) по L и не с цифры;

i)        выбрать заказы, в которых адрес получателя начинается с цифры и имеет длину, не более 20 символов.

Для определения длины строки используется функция LEN(«строковое выражение»).

Сохраните запросы и покажите их преподавателю.

Лабораторная работа № 14. Предложения GROUP BY и HAVING

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

Предложение GROUP BY. Синтаксис:

GROUP BY <список полей>

Предложение GROUP BY применяется для разбиения таблицы на группы строк и применения к каждой группе агрегатных функций. Рассмотрим пример. Пусть необходимо в таблице «Заказано» подсчитать для каждого наименования (кода) товара количество заказов и максимального количества товара в одном заказе. Для уменьшения количества выводимых результатов ограничимся кодами товара от 1 до 5. Задача решается с помощью следующего запроса :

SELECT Заказано.КодТовара, Count(*) AS [К-во заказов], Max([Количество]) AS [Макс_к-во_товара]
FROM Заказано
WHERE КодТовара<6
GROUP BY КодТовара ;

Результаты запроса:

КодТовара

К-во заказов

Макс_к-во_товара

1

38

80

2

 45

100

3

14

60

4

23

50

5

10

70

 

Самостоятельно составьте и выполните следующие запросы к таблице «Заказы»:

a)     подсчитать для каждой страны количество заказов, минимальную, среднюю и максимальную стоимость доставки;

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

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

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

 

Предложение HAVING служит для задания условий, содержащих агрегатные функции.

Пример. Выбрать в таблице «Заказы» города для которых сделано более десяти заказов. Вывести название города и количество заказов. Запрос выглядит так:

SELECT ГородПолучателя, Count(КодЗаказа) AS [К-во заказов]
FROM Заказы
GROUP BY ГородПолучателя
HAVING Count(КодЗаказа)>10;

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

a)     выбрать из таблицы «Заказано» коды товаров, у которых максимальная скидка больше 20%;

b)    выбрать из таблицы «Заказано» для каждого товара код товара, среднюю, минимальную и максимальную цены при условии, что средняя цена меньше  2000

Сохраните запросы и покажите их преподавателю

Лабораторная работа № 15. Многотабличные запросы

Цель работы: освоить методы выборки из базы данных информации, размещённой в нескольких соединяемых таблицах

Для дальнейших упражнений понадобятся следующие таблицы из базы данных «Борей»: «Заказы», «Заказано», «Сотрудники», «Клиенты» и «Товары». Импортируйте в Вашу базу данных недостающие таблицы.

Создайте с помощью оператора CREATE TABLE  таблицы «писатель» и книга».и заполните их так , как показано на рис 10. В колонке «Автор» таблицы «книга» указаны коды писателей из таблицы «писатель». Несколько клеток в последних строках обеих таблиц специально оставлены пустыми. В строке 7 таблицы «книги» указан код 12, отсутствующий в таблице «писатель».

Рис. 10. Заполнение таблиц книги и писатели

Таблицы, состоящие из двух столбцов, в одном из которых хранится наименование объекта, а в другом – номер или код, называется справочником. Код из справочника используется в других таблицах вместо имени объекта. Замена наименования кодом уменьшает вероятность ошибки при вводе данных и позволяет при изменении наименования, например фамилии, внести изменение только в одно поле справочника.

Многотабличный запрос с описанием связей между таблицами в предложении WHERE. Запрос на выборку наименований книг и их авторов  выглядит так:

SELECT a.Наим,b.ФИО
FROM книга a,писатель b
WHERE a.Автор=b.КодП

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

В многотабличном запросе можно использовать любые условия для отбора данных. Если не использовать никаких условий, то будет выведено декартово произведение из всех строк, таблиц, используемых в запросе, то есть 4*9=36 строк. Удалите из последнего запроса предложение WHERE и выполните получившийся запрос.

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

SELECT a.Наим,b.ФИО
FROM книга a INNER JOIN писатель b ON a.Автор=b.КодП

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

книгa.Автор=писатель.КодП.

Левые и правые внешние соединения. Чтобы кроме данных из записей, удовлетворяющих условию запроса, включить в выборку названия книг из записей, не удовлетворяющих условию запроса, применяется «левое внешнее соединение» (LEFT JOIN):

SELECT a.Наим,b.ФИО
FROM книга a LEFT JOIN писатель b ON a.Автор=b.КодП.

Для книг «Обломов», «Пётр I» и «На дне» поле ФИО в результатах этого запроса останется пустым

Чтобы кроме данных из записей, удовлетворяющих условию запроса, включить в выборку  ФИО писателей из записей, не удовлетворяющих условию запроса, применяется «правое внешнее соединение» (RIGHT JOIN):

SELECT a.Наим,b.ФИО
FROM книга a RIGHT JOIN писатель b ON a.Автор=b.КодП

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

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

-         выбрать все имеющиеся в базе названия произведений Пушкина и Толстого Л.Н. и вместе с ФИО авторов;

-         используя  таблицы «заказы» и «клиенты», выбрать названия клиентов, их представителей (поле «ОбращатьсяК») и даты размещения их заказов при условии, что клиенты из Лондона;

-         используя  таблицы «заказы» «заказано», «товары» и «клиенты», выбрать названия клиентов, их представителей (поле «ОбращатьсяК») и коды и марки выбранных ими товаров при условии, что названия клиентов начинаются на “F” и заказы оформлял сотрудник Кротов.

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

Пример. Допустим, в таблице «Сотрудники» имеются поля «ФИО», «должность» и «отдел». Требуется найти всех программистов и их начальников. Известно, что программисты работают почти во всех отделах. Запрос выглядит так:

SELECT а.ФИО AS программист,b.ФИО AS начальник_отдела
FROM Сотрудники а,Сотрудники b
WHERE a.должность=’программист’ AND b.должность=’нач_отдела’
        
AND a.отдел=b.отдел

Результаты будут выглядеть примерно так:

Программист

Начальник_отдела

Андреев А.Б.

Петров К.Ю.

Борисова Г.П.

Петров К.Ю.

Смирнов П.С.

Новикова А.Г.

 

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

 

Лабораторная работа № 16. Предикат NULL.
       Подзапросы. Предикаты EXISTS, ANY, ALL

Цель работы: научиться применять в запросах предикаты NULL, EXISTS, ANY и ALL.

Предикат IS NULL. Для выяснения смысла значения NULL рассмотрим пример. Пусть в городе N ведётся база данных, в которой хранятся данные обо всех жителях, включая детей. Очевидно, что в графу «профессия» записи о ребёнке поместить нечего, так как у ребёнка ещё нет профессии. Графа профессия может оказаться пустой и в том случае, когда в момент занесения данных профессия жителя не была известна. Предполагается, что графа будет заполнена позже. Для неизвестного значения в SQL применяется специальное обозначение NULL. Значение NULL имеют по умолчанию все поля, в которые ничего не заносилось.

NULL применяется в полях всех типов и само не имеет типа. Значение NULL можно использовать только в специальном предикате IS NULL, имеющем следующий синтаксис:

<выражение> IS [NOT]NULL

Предикат IS NULL принимает значение «истина» только, если выражение равно NULL.

Для работы с NULL-значениями полей создайте в базе данных таблицу NullPusto, состоящую из двух текстовых полей длиной по 30 символов. Назовите поля «ФИО» и «адр». Введите в таблицу данные из табл. 3.

Создайте и выполните следующие запросы к таблице NullPusto:

a)     выбрать все записи с NULL;

b)    выбрать все записи с “”;

c)     выбрать все записи, в которых есть адреса;

d)    выбрать все записи, в которых нет адресов;

e)     подсчитать количество записей, содержащих NULL;

f)      подсчитать количество записей, содержащих NULL и “”.

Сохраните запросы и покажите их преподавателю.

Таблица 3

Поле

Значение в поле «адр»

ФИО

адр

А

К

“К”

Б

 

“” (две двойные кавычки)

В

 

NULL

Г

М

“М”

Д

 

NULL

Е

 

“” (две двойные кавычки)

Ж

 

NULL

 

Подзапросы. С помощью SQL можно вкладывать один запросы внутрь другого. Внутренний запрос называют подзапросом. Обычно, внутренний запрос генерирует значение, которое проверяется в предикате внешнего запроса,  определяющего верно  оно  или  нет. Например, в следующем запросе выбираются из таблицы «Товары» те товары, цена которых меньше средней цены всех товаров таблицы:

SELECT  *
FROM Товары
WHERE  Цена<(SELECT  AVG(Цена) FROM Товары);

Самостоятельно с помощью подзапроса выберите из таблицы «Заказано» заказы на товары с маркой «Pavlova». Марки товаров хранятся в таблице «Товары».

Предикат EXISTS имеет синтаксис

EXISTS подзапрос

и принимает значение ИСТИНА (TRUE), если  подзапрос содержит хотя бы одну строку.

В следующем запросе выбираются фамилии всех сотрудников, оформлявших заказы для клиента ANTON, при условии, что хотя бы один заказ для клиента ANTON был размещён в мае любого года.

SELECT DISTINCT  b.Фамилия
FROM Заказы a, Сотрудники b
WHERE EXISTS (SELECT * FROM Заказы WHERE КодКлиента='ANTON' AND DatePart('m',ДатаРазмещения)=5)
    
AND a.КодСотрудника = b.КодСотрудника AND a.КодКлиента='ANTON';

Самостоятельно, используя таблицы «Сотрудники», «Клиенты» и «Заказы», создайте и выполните запрос на выборку всех клиентов из Рио-Де-Жанейро, если был сделан хотя бы один заказ из Рио-Де-Жанейро, оформленый сотрудником Кротовым.

Предикаты количественного сравнения ANY, SOME и ALL имеют синтаксис

оператор сравнения {ANY | SOME | ALL} подзапрос.

ANY и SOME – синонимы.

Пример использования предиката  ANY:

SELECT КодЗаказа
FROM Заказы
WHERE СтоимостьДоставки < ANY(SELECT СтоимостьДоставки FROM Заказы WHERE ГородПолучателя    ='Ванкувер');

Для исследования особенностей предиката ANY проделайте следующее упражнение:

a)     выберите из таблицы «Товары» цены товаров от поставщика с кодом 2; запишите эти цены;

b)    используя ANY, выберите все товары, цены которых больше цен поставщика 2; сравните выбранные цены с записанными;

c)     повторите предыдущий пункт, иcпользуя вместо ANY  предикат ALL; сравните результаты.

Сохраните все выполненные запросы и покажите их преподавателю

Лабораторная работа № 17. Объединение результатов нескольких запросов – UNION. Создание таблицы из существующих таблиц – SELECT … INTO

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

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

SELECT ALL Адрес,Город,'Заказы ' AS Источник
FROM Клиенты
UNION
SELECT ALL АдресПолучателя AS Адрес,ГородПолучателя AS Город,'Клиенты ' AS Источник
FROM Заказы;

Выполните этот запрос.

Самостоятельно выберите из таблиц «Клиенты» и «Сотрудники» следующие данные:

-         фамилию и имя;

-         должность;

-         город.

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

Создание таблицы из существующих таблиц с помощью  SELECT … INTO. Во многих СУБД конструкция SELECT … INTO <имя таблицы> используется для создания новой таблицы и вывода в неё результатов запроса. Например, таблица «Страны» с названиями всех стран, в которые направляются заказы, создаётся в результате выполнения следующего запроса:

SELECT DISTINCT СтранаПолучателя
INTO Страны
FROM Заказы;

Самостоятельно с помощью  SELECT … INTO создайте таблицу «Клиенты2», содержащую данные из таблицы «Клиенты» обо всех клиентах, живущих в Лондоне.

Сохраните выполненные запросы и покажите их преподавателю

Лабораторная работа № 18. Операторы INSERT, UPDATE, DELETE

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

Вставка в таблицу одной или нескольких строк с помощью оператора INSERT. Синтаксис оператора INSERT:

INSERT INTO <имя таблицы>
[(<
имя столбца>)]
{
VALUES (<значение> .,..)}
|<
выражение запроса>
|{
DEFAULT VALUES};

Пример. Добавим в созданную в лаб. работе №15 таблицу «книга» книгу М. Горького «Детство». Так как в таблице «писатель» Горькому не присвоен код, то в добавляемой строке будут заполняться только столбцы «КодКн» и «Наим». Описанная строка добавляется с помощью оператора

INSERT INTO  книга
(КодКн,Наим)
VALUES (10,'Детство');

Столбец «КодКн» не является счётчиком, поэтому он указан в списках столбцов и добавляемых значений. Счётчик в операторе INSERT указывать не надо.

Самостоятельно добавьте в таблицу «писатель» Толстого А.Н. и в таблицу «книга» - роман «Сёстры».

Изменение (редактирование) данных в таблице с помощью оператора UPDATE. Синтаксис оператора UPDATE:

UPDATE <имя таблицы>
SET {<имя столбца>={<выражение для вычисления значения>
|
NULL |DEFAULT}}
[
WHERE <предикат>]
Пример. Укажем в таблице «писатель» код Горького:

UPDATE писатель
SET КодП=10
WHERE ФИО='Горький';

Самостоятельно с помощью оператора UPDATE занесите в таблицу «книга» все недостающие значения полей.

Удаление строк таблицы с помощью оператора DELETE. Синтаксис оператора DELETE:

DELETE FROM <имя таблицы>
[
WHERE <предикат>]

Пример. Удалим из таблицы «книга» книгу «На дне»

DELETE FROM книга WHERE КодКн=7

Самостоятельно с помощью оператора DELETE удалите из таблицы «писатель» Тургенева.

Сохраните выполненные запросы и покажите их преподавателю

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

Карпова Т.С. Базы данных: модели, разработка, реализация. / Т.С. Карпова. Спб.: Питер, 2002. 304 с.

Бекаревич Ю.Б. Самоучитель Microsoft Access 2002. / Ю.Б. Бекаревич, Н.В. Пушкина. Спб.: БХВ-Питербург, 2003. 720 с.

Грабер М. SQL: справочное руководство / Мартин Грабер. М.: Лори, 2001, 353 с.

Дейт К.Дж. Введение в системы баз данных. 7-е изд. / Дейт К.Дж. М.: Издательский дом «Вильямс», 2001. 1072 с.

 Крёнке, Девид. Теория и практика построения баз данных: учебное пособие для вузов /     Д. Крёнке, 8-е изд., пер. Вахитов, 2003