Задание 9. Создайте почтовые наклейки

· Выделите таблицу или запрос в качестве источника данных, например, запрос Адрес.

· Щелкните по кнопке , расположенной на вкладке Создание в группе Отчеты.

· В появившемся диалоговом окне Создание наклеек щелкните по кнопке Далее.

· В следующем окне вновь щелкните по кнопке Далее.

· В следующем окне прототип наклейки, напечатайте нужные слова и выберите соответствующие поля (см. рис.16). Щелкните по кнопке Далее.

Рис.16

· В следующем окне укажите поле(я) для сортировки. Щелкните по кнопке Далее.

· Введите имя отчета Наклейки и щелкните по кнопке Готов

Предъявите преподавателю отчеты Адрес, Номер телефона, Справка, Список учеников, Отчет0, Отчет1, Отчет2, Отчет3, Отчет4, Отчет5, Наклейки.

Задание 10.Завершите работу с программой.

4.5.Контрольные вопросы

1. Назначение отчета?

2. Какие разделы отчетов вы знаете и каково их назначение?

3. Кратко опишите все способы создания отчетов.

4. Какие элементы управления присутствуют в отчетах?

5. Как пронумеровать страницы отчета?

6. Как поместить в отчет дату его распечатки?

7. Как выполнить модификацию ранее созданного отчета?

8. Как просматривать и распечатывать отчеты?

9. В чем заключается создание отчета с помощью Мастера?

4.6. Требования к отчету

1. Название лабораторной работы.

2. Цель работы.

3. Укажите назначение объекта базы данных – отчет.

4. Опишите все способы создания отчета.

Работа 5. Создание запросов с помощью языка SQL

5.1. Цель практического занятия: научиться создавать запросы с использованием команды SELECT.

5.2. Оборудование и материалы: компьютеры, приложение Microsoft Access 2007.

5.3. Теоретические сведения

Язык SQL – Structured Query Language (структурированный язык запросов). Разработан фирмой IBM в начале 70-х гг. это современное средство для работы с базами данных, которое применяется в среде реляционных баз данных (создание, поиск, изменение, обновление, передача данных).

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

Язык SQL состоит из команд (инструкций). Команды передаются программе, которая управляет работой базы данных, для выполнения конкретных действий. Объектом действия команды SQL может быть как одна таблица, так и группа таблиц.

Команды SQL делятся на две группы:

· Язык определения данных (Data Definition Language – DDL):

Create Table – создает новую таблицу;

Create Index – создает новый индекс;

Alter Table – дополняет новое поле или индекс в существующую таблицу;

· Язык манипулирования данными (Data Manipulation Language - DML):

Select – запрос к базе данных; запрос возвращает данные в виде набора записей;

Select Into – запрос к базе данных; запрос возвращает данные в виде новой таблицы;

Update – запрос на объединение; запрос изменяет значения полей таблицы;

Insert Into – запрос на добавление в таблицу одной или нескольких записей;

Delete – запрос на удаление записей из одной или нескольких таблиц.

Существуют две формы языка SQL: интерактивная и встроенная.

Интерактивный SQL применяется непосредственно в базе данных для выполнения определенных действий над данными. Вводятся определенные команды, после выполнения, которых тут же выводятся выходные данные (результат).

Встроенный SQL - это включение команд языка SQL в программы, написанные на другом языке программирования, например Pascal, Си.

Далее дается описание интерактивного языка SQL.

Главной командой языка SQL является команда SELECT (ВЫБРАТЬ). Это ключевое слово сообщает базе данных, что эта команда - запрос. Все запросы начинаются этим словом с последующим пробелом.

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

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

Синтаксис (формат) команды SELECT:

SELECT[предикат] {*|таблица.*| [таблица.] поле_1

[AS псевдоним_1] [,[таблица.] поле_2 [AS псевдоним_2] [, …] ]}

FROM выражение [,…] [IN Внешняя_База_данных]

[WHERE .. условие_Отбора]

[GROUP BY.. список_полей_группировки]

[HAVING условие_Группировки]

[ORDER BY.. вполе_1 [ASC|DESC].[, вполе_2[ASC|DESC]]…]

Предикат – задает ограничения на возвращаемые записи: ALL- по умолчанию, т.е. без ограничений; DISTINCT – все записи без их дублирования;

Таблица – имя таблицы-источника, из которой берутся записи;

поле_1, поле_2 – имена полей, из которых берутся записи;

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

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

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

«условие_Отбора» - выражение с условием, которому должны соответствовать записи, включенные в результат исполняемого запроса;

«список_полей_группировки» - имена полей (до 10), которые применяются для группировки записей;

«условие_Группировки» - выражение, которое определяет, какие сгруппированные записи надлежит отображать;

«в поле_1, в поле_2» - поля, по значениям которых сортируются записи в результате запроса;

ASC – по возрастанию, DESC – по убыванию;

FROM – обязательный параметр.

5.4. Порядок выполнения работы

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

2. Щелкнуть по кнопке Конструктор запросов, расположенной на вкладке Создание в группе Другие.

3. Окно Добавление таблицы закрыть.

4. Щелкнуть по кнопке Режим SQL на вкладке Конструктор в группе Результаты.

Задание 1. На основании таблицы Список базы данных Школа.

Таблица 1.

Список

№

Фамилия

Имя

Отчество

Дата

Пол (м)

Улица

Дом

Квартира

Класс

Группа

Хобби

Глаза

Суханов

Сергей

Алексеевич

16.02.85

Да

Чердынская

основная

тяжелая атлетика

зеленые

Пирогов

Юрий

Николаевич

5.12.87

Да

Куйбышева

основная

футбол

голубые

Лебедева

Светлана

Дмитриевна

16.06.89

Нет

Пушкина

специальная

вязание

карие

Голдобин

Сергей

Иванович

23.05.92

Да

Леонова

основная

лыжи

голубые

Ельшина

Наталья

Дмитриевна

24.05.86

Нет

Чердынская

специальная

чтение

серые

Суханова

Наталья

Петровна

20.12.90

Нет

Ленина

подготовительная

шитье

зеленые

Петрова

Светлана

Алексеевна

18.04.86

Нет

Пушкина

основная

лыжи

серые

Горина

Ольга

Сергеевна

20.12.88

Нет

Свиязева

подготовительная

аэробика

карие

Попов

Михаил

Иванович

7.07.91

Да

Леонова

подготовительная

голубые

Сергеев

Александр

Дмитриевич

30.11.93

Да

Куйбышева

основная

карате

зеленые

Павлова

Елена

Петровна

13.12.89

Нет

Пушкина

основная

аэробика

карие

Емельянова

Наталья

Вячеславовна

25.05.85

Нет

Попова

основная

шитье

зеленые

Евдокимов

Михаил

Андреевич

18.08.88

Да

Чердынская

основная

футбол

зеленые

Евсеева

Елена

Алексеевна

14.10.86

Нет

Ленина

основная

лыжи

серые

Суханова

Светлана

Ивановна

29.07.84

Нет

Куйбышева

основная

аэробика

карие

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

Фамилия

Имя

Класс

Подается команда:

SELECT Фамилия, Имя, Класс

FROM Список;

Результат выполнения приведен в таблице 2.

Таблица 2.

Фамилия	Имя	Класс
Суханов	Сергей
Пирогов	Юрий
Лебедева	Светлана
Голдобин	Сергей
Ельшина	Наталья
Суханова	Наталья
Петрова	Светлана
Горина	Ольга
Попов	Михаил
Сергеев	Александр
Павлова	Елена
Емельянова	Наталья
Евдокимов	Михаил
Евсеева	Елена
Суханова	Светлана

Поясним команду в приведенном примере:

SELECT – ключевое слово, которое «сообщает» СУБД о том, что команда является запросом;

Фамилия, Имя, Класс – список имен полей, по которым должна выбираться информация и формироваться новая таблица;

FROM Список; FROM – ключевое слово, должно быть в каждом запросе; Список – имя таблицы-источника данных для запроса;

Символ точка с запятой (;) – признак окончания команды и готовности к ее выполнению.

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

Для вывода всех столбцов таблицы БД список полей можно не перечислять, заменив его символом «звездочка» (*). Столбцы выводятся в соответствии со структурой таблицы-источника

SELECT *

FROM Список;

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

SELECT Фамилия, Хобби, Группа, Пол

FROM Список;

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

Подается команда:

SELECT Фамилия, Имя

FROM Список

WHERE Класс=9;

Результат выполнения приведен в таблице 3.

Таблица 3.

Фамилия	Имя
Ельшина	Наталья
Петрова	Светлана
Евсеева	Елена

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

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

Подается команда:

SELECT Фамилия, Имя

FROM Список

WHERE Хобби=”футбол” Or Хобби=”лыжи”;

Результат выполнения приведен в таблице 4.

Таблица 4.

Фамилия	Имя
Пирогов	Юрий
Голдобин	Сергей
Петрова	Светлана
Евдокимов	Михаил
Евсеева	Елена

В SQL применяются операторы сравнения для задания типов сравнения между двумя значениями: = (равно), > (больше, чем), <(меньше), >=(больше или равно), <=(меньше или равно), <> (неравно).

Булевы операторы

Оператор AND сравнивает два выражения – A AND B – в качестве аргументов и в результате выдает истину только в случае истинности обоих.

Оператор OR сравнивает два выражения – A OR B – в качестве аргументов и оценивает результат как истину, если хотя бы один из них истинен.

Оператор NOT анализирует единственное булево выражение NOT А в качестве аргумента и изменяет его значение на противоположное.

Задание 4.Выбрать из таблицы Список школьников, которые не живут на улице Чердынская

Подается команда:

SELECT Фамилия, Имя, Отчество, Улица

FROM Список

WHERE Not Улица=”Чердынская”;

Результат выполнения приведен в таблице 5.

Таблица 5.

Фамилия	Имя	Отчество	Улица
Пирогов	Юрий	Николаевич	Куйбышева
Лебедева	Светлана	Дмитриевна	Пушкина
Голдобин	Сергей	Иванович	Леонова
Суханова	Наталья	Петровна	Ленина
Петрова	Светлана	Алексеевна	Пушкина
Горина	Ольга	Сергеевна	Свиязева
Попов	Михаил	Иванович	Леонова
Сергеев	Александр	Дмитриевич	Куйбышева
Павлова	Елена	Петровна	Пушкина
Емельянова	Наталья	Вячеславовна	Попова
Евсеева	Елена	Алексеевна	Ленина
Суханова	Светлана	Ивановна	Куйбышева

В запросе могут использоваться специальные операторы в условиях IN, BETWEEN, LIKE.

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

Синтаксис оператора IN:

<Проверяемое выражение> [NOT] IN (<подзапрос>)

|(<выражение для вычисления значения>,…)

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

Понятие подзапрос используется, когда один запрос вложен в другой запрос. Запрос, который вкладывается, - это подзапрос, а в который вкладывается – основной (внешний). Подзапрос записывается в предложении WHERE, выполняется первым по отношению к внешнему, и используется для определения истинности или ложности предиката.

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

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

Подается команда:

SELECT Фамилия, Имя

FROM Список

WHERE Глаза In (“голубые” , “зеленые”);

Результат выполнения приведен в таблице 6.

Таблица 6.

Фамилия	Имя
Суханов	Сергей
Пирогов	Юрий
Голдобин	Сергей
Суханова	Наталья
Попов	Михаил
Сергеев	Александр

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

Синтаксис оператора BETWEEN:

<Проверяемое выражение> [NOT] BETWEEN

<Начальное значение> AND <Конечное значение>

Задание 6.Выбрать из таблицы Список всех школьников, фамилии которых начинаются от буквы Е до буквы О.

Подается команда:

SELECT *

FROM Список

WHERE Фамилия BETWEEN “Е” And “О” ;

Результат выполнения приведен в таблице 7.

Таблица 7.

№

Фамилия

Имя

Отчество

Дата

Пол (м)

Улица

Дом

Квар- тира

Класс

Группа

Хобби

Глаза

Лебедева

Светлана

Дмитриевна

16.06.89

Нет

Пушкина

специальная

вязание

карие

Ельшина

Наталья

Дмитриевна

24.05.86

Нет

Чердынская

специальная

чтение

серые

Емельянова

Наталья

Вячеславовна

25.05.85

Нет

Попова

основная

шитье

зеленые

Евдокимов

Михаил

Андреевич

18.08.88

Да

Чердынская

основная

футбол

зеленые

Евсеева

Елена

Алексеевна

14.10.86

Нет

Ленина

основная

лыжи

серые

Оператор LIKE применяется только для выборки значений из полей типа CHAR (символьный) с поиском подстрок в указанном поле.

Синтаксис оператора LIKE:

<Выражение 1> [NOT] LIKE <Выражение 2>

Оператор LIKE сравнивает строку, представленную выражением 1 и называемую проверяемым значением, с образцом, который определен в выражении 2.

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

Подается команда:

SELECT Фамилия, Имя, Класс

FROM Список

WHERE Фамилия Like “П*”

ORDER BY Имя;

Результат выполнения приведен в таблице 8.

Таблица 8.

Фамилия	Имя	Класс
Павлова	Елена
Попов	Михаил
Петрова	Светлана
Пирогов	Юрий

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

Задание 8.Выбрать из таблицы Список школьников, с фамилиями от буквы Г до буквы Л с сортировкой по полю Фамилия.

Подается команда:

SELECT Фамилия, Имя, Класс

FROM Список

WHERE Фамилия Like “[Г-Л]*”

ORDER BY Фамилия;

Результат выполнения приведен в таблице 9.

Таблица 9.

Фамилия	Имя	Класс
Голдобин	Сергей
Горина	Ольга
Евдокимов	Михаил
Евсеева	Елена
Ельшина	Наталья
Емельянова	Наталья
Лебедева	Светлана

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

1. COUNT (*) – возвращает количество строк источника записей;

2. COUNT() – возвращает количество значений в указанном столбце

3. SUM () – суммирование всех выбранных значений данного поля;

4. AVG () – подсчет среднего значения для всех выбранных значений;

5. MAX () – вычисляет наибольшее из всех выбранных значений;

6. MIN () – вычисляет наименьшее из всех выбранных значений.

Все эти функции возвращают единственное значение. При этом функции COUNT, MIN, MAX применимы к любым типам данных, в то время как SUM и AVG используются только для числовых полей.

Разница между COUNT (*) и COUNT(<имя поля>) состоит в том, что вторая (как и остальные агрегатные функции при подсчете не учитывает NULL – значения.

Задание 9.Подсчитать количество школьников, обучающихся в каждом классе по таблице Список.

Подается команда:

SELECT Класс, Count(Класс) AS [Count-Класс]

FROM Список

GROUP BY Класс;

Результат выполнения приведен в таблице 10.

Таблица 10.

Класс	Count-Класс

Синтаксис функции COUNT:

SELECT COUNT (имя_столбца)

FROM имя_таблицы;

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

Для подсчета количества строк по критерию отбора в команде SELECT используется предложение WHERE.

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

Подается команда:

SELECT Count(Группа) AS [Count-Группа]

FROM Список

GROUP BY Группа

HAVING Группа=”основная”;

Результат выполнения приведен в таблице 11.

Таблица 11.

Count-Группа

5. Показать результаты работы преподавателю.

6. Закрыть базу данных Школа. Закрыть приложение MS Access.

7.Выполнить самостоятельно следующие задания.

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

Задание 11. Получить информацию о фактах продажи товара в количестве, превышающем 100 штук (см. таблицу Продажи).

Результат выполнения приведен в таблице 12.

Таблица 12.

Код	Товар	Прод_возвр	Количество	Дата_продажи	Магазин
		Пр		01.04.2007
		Пр		30.07.2006

Задание 12. Получить информацию о фактах списания и возврата товара в количестве, превышающем 53 штук.

Результат выполнения приведен в таблице 13.

Таблица 13.

Код	Товар	Прод_возвр	Количество	Дата_продажи	Магазин
		Сп		20.12.2009
		Вт		25.06.2008

Задание 13.Найти наименования и розничные цены товаров, оптовая цена которых лежит в диапазоне от 80 до 120 рублей (использовать предикат BETWEEN).

Результат выполнения приведен в таблице 14.

Таблица 14.

Наименование	Цена_розн
Могилев А. Информатика	113,34р.
Дезодорант «Мужской»	102,10р.
Химия 10 кл.	95,32р.

Задание 14.Найти информацию о товарах, списанных в восьмом или пятнадцатом магазинах (использовать предикат IN).

Результат выполнения приведен в таблице 15.

Таблица 15.

Код	Товар	Прод_возвр	Количество	Дата_продажи	Магазин
		Сп		14.02.2008
		Сп		18.12.2009
		Сп		20.12.2009

Задание 15.Найти информацию о товарах, которые были оприходованы в 8 или 99 магазинах и оптовая стоимость которых меньше 100 рублей (см. стр. 50 понятие подзапрос)

SELECT *

FROM Продажи

WHERE Магазин IN(“8”, “99” ) and

Товар IN (SELECT Код_товара FROM Товары

WHERE Цена_опт<100);

Выполнение данного запроса начинается с подзапроса: в таблице Товары просматриваются все записи, для которых значение поля Цена_опт<100; для этих записей выбирается значение для поля Код_товара, выбранные значения подставляются в предикат основного запроса в предложение WHERE вместо самого подзапроса (предложение будет иметь вид: WHERE Магазин IN(“8”, “99” ) and Товар IN (4,6,17,18,13,23,14,16,15,9,3,2)). Далее выполняется основной запрос. Те строки, в которых это составное условие имеет значение истина, и выводятся в результирующую таблицу 16.

Таблица 16.

Код	Товар	Прод_возвр	Количество	Дата_продажи	Магазин
		Вт		25.06.2008
		Вт		25.06.2008
		Пр		11.04.2007
		Пр		01.04.2007

Имена столбцов, указанные в предложении SELECT, можно изменять. Для этого используется ключевое слово AS. Например, запрос

SELECT Товар, Количество AS Продано,

Дата_продажи AS Дата, Магазин AS [В магазине]

FROM Продажи

WHERE Прод_возвр=”Пр” AND Количество>1200;

переименует столбец Количество в Продано, столбец Дата_продажи – в Дата, Магазин – в В магазине. Квадратные скобки для столбца [В магазине] необходимы, так как имя содержит недопустимый пробел.

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

Задание 16. Вывести количество штук и общую стоимость товара с кодом 13 в виде

Наименование	Всего	Сумма
Порошок стиральный «Босс»

Результат выполнения приведен в таблице 17.

Таблица 17.

Наименование	Всего	Сумма
Порошок стиральный «Босс»		2 400,00р.

Задание 17.Найти все товары, названия которых заканчиваются на букву ‘г’. Для создания запроса использовать предикат LIKE.

Результат выполнения приведен в таблице 18.

Таблица 18.

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

Мыло банное 120г

Мыло хозяйственное 200г

Солженицин А. Архипелаг ГУЛАГ

Утюг

Задание 18.Найти все названия товаров, которые заканчиваются на букву ‘г’, но не на ‘0г’.

Результат выполнения приведен в таблице 19.

Таблица 19.

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

Солженицин А. Архипелаг ГУЛАГ

Утюг

Задание 19.Найти минимальную и максимальную розничные цены на книги (категория товара =1)

Результат выполнения приведен в таблице 20.

Таблица 20.

Минимальная цена	Максимальная цена
13,21р.	450,37р.

Задание 20.Определить количество товаров поставщика Костина Михаила Александровича.

Результат выполнения приведен в таблице 21.

Таблица 21.

Q_t

Задание 21.Определить количество товаров поставщика Круглова Силантия Ивановича, для которых установлен срок реализации.

Результат выполнения приведен в таблице 22.

Таблица 22.

Q_t

9. Показать результаты работы преподавателю.

10. Закрыть базу данных.

11. Закрыть приложение Microsoft Access.

5.5. Контрольные вопросы

3.1. Что такое SQL?

3.2. Дать определение понятия «запрос».

3.3. Что обозначает команда SELECT? Какая информация записывается после этой команды?

3.4. Назначение предложения WHERE команды SELECT?

3.5. Что обозначает символ “*” после команды SELECT?

3.6. Объяснить назначение и работу операторов IN, BETWEEN.

3.7. Что такое предикат?

3.8. Как отсортировать записи результирующей таблицы?

3.9. Какие групповые операции можно выполнять над данными?

3.10. Как изменить имя поля в результирующей таблице?

3.11. Объяснить назначение и работу оператора LIKE.

5.6. Требования к отчету

1. Название лабораторной работы.

2. Цель работы.

3. Привести тексты запросов с № 11 по № 21.

СПИСОК РЕКОМЕНДОВАННОЙ ЛИТЕРАТУРЫ

1. Хансен Г., Хансен Д. Базы данных: разработка и управление: Пер. с анл. – М:. ЗАО «Издательство БИНОМ», 1999. – 704 с.

2. Моисеенко С.И, Соболь Б.В. Разработка приложений в MS Access. Краткое руководство. – М.: Издательский дом «Вильямс», 2006. – 272 с.