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

Лабораторная работа

«Формирование запросов на языке SQL»

Цель работы:Получить практические навыки формирования запросов на языке SQL в среде Microsoft Access.

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

1. Изучить применение операторов языка 5QL по приведенным в кратком теоретическом описании примерам.

2. Выполнить задания для самостоятельного выполнения в СУБД Access в режиме формирования запросов на SQL и сохранить соответствующие запросы базе graber.mdb.

3. Составить отчет о выполненной работе и внести в него результаты выполнения упражнений.

Краткое теоретическое описание

Введение

SQL (Structured Query Language - структурированный язык запросов) в настоящее время является универсальным языком для работы с базами данных.

Язык SQL появился в 1974 г. Первый стандарт относится к 1986 году. Язык действующего ныне стандарта называют часто SQL-92.

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

SQL встроен также в СУБД Access, но не все требования стандарта реализованы в этой версии SQL. Некоторые особенности данной версии являются расширениями по отношению к стандарт} SQL-92.

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

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

В данной лабораторной работе изучаются лишь некоторые простые возможности SQL.

База данных (graber.mdb), используемая в примерах и в упражнениях.

Таблица ПРОДАВЦЫ

HПP - номер продавца ИМЯПР -имя продавца

ГОРОД - адрес продавца КОМ - комиссионные продавца

Таблица ЗАКАЗЫ

НЗК - номер заказа НПК - номер покупателя

НПР - номер продавца

Таблица ПОКУПАТЕЛИ

НПК - номер покупателя ИМЯПК - имя покупателя РЕЙТ - рейтинг покупателя НПР - номер продавца

Начало работы:

1. Выполнить пуск СУБД Access

2. Открыть файл базы данных graber.mdb

3. Выбрать режим ЗАПРОСЫ

4. Выбрать команду Создать.

5. Защита окно Добавление таблицы.

6. Перейти в режим SQL.

Выборка данных из таблиц

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

Примеры

1. Наберите в окне режима SQL запрос, который должен выводить из таблицы ПРОДАВЦЫ имена продавцов и их комиссионные:

SELECT ИМЯПР, КОМ FROM ПРОДАВЦЫ;

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

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

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

ключевого слова SELECT указать символ * ("звездочка"):

SELECT * FROM ПРОДАВЦЫ;

Результатом будет вся таблица ПРОДАВЦЫ. При этом столбцы выводятся в порядке их хранения в базе.

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

SELECT ИМЯПР, НПР, ГОРОД, КОМ FROM ПРОДАВЦЫ; Результатом будет следующая таблица:

4. Пусть необходимо запросить, какие по номеру продавцы имеют в

настоящее время заказы в таблице ЗАКАЗЫ. Введем:

SELECT НПР FROM ЗАКАЗЫ;

Получим следующую таблицу:

Такой результат содержит повторяющиеся строки, которые в данном случае желательно исключить для получения только различных номеров. С этой целью после ключевого слова SELECTставится ключевое словоDISTINCT:

SELECT DISTINCT НПР FROM ЗАКАЗЫ;

В результате получим:

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

Альтернативой DISTINCTявляется ALL. Это ключевое слово имеет противоположное действие: повторяющиеся строки включаются в состав выходных данных. Если не задано ни DISTINCTни ALL,то предполагается ALL.

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

Предположим необходимо узнать имена всех продавцов в Орле. В этом случае можно ввести команду:

SELECT ИМЯПР, ГОРОД FROM ПРОДАВЦЫ WHERE ГОРОД=”ОРЕЛ”;

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

6. При использовании в предложении WHERE числового поля конкретные значения не заключаются в кавычки, как это сделано в следующем примере выбора всех покупателей с рейтингом равным 100.

SELECT * FROM ПОКУПАТЕЛИ WHERE РЕЙТ=100;

Результат:

Задание на самостоятельное выполнение

1.Запишите команду SELECT, которая выводит номер, сумму и дату заказов для всех строк таблицы ЗАКАЗЫ.

2.Запишите запрос, который выдает все строки таблицы ПОКУПАТЕЛИ, где продавец имеет-номер 1001.

3. Запишите запрос, который выдает строки таблицы ПРОДАВЦЫ порядке ГОРОД, ИМЯПР, НПР, КОМ.

4. Запишите команду SELECT, которая выдает рейтинг и следом за ним ИМЯПК каждого покупателя, проживающего в Саратове.

5. Запишите запрос, позволяющий получить значения столбца НПР для всех продавцов, номера которых находятся в настоящее время в таблице ЗАКАЗЫ, причем повторения требуется исключить.

WHERE

(СУММА < 1000 OR NOT (ДАТА =#03/10/99# AND НПК > 2003));

10. Каков будет результат выполнения следующего запроса? .

SELECT*FROM ЗАКАЗЫ

WHERE NOT((ДАТА=#03/10/99# OR НПР > 1006) AND СУММА >=1500);

11.Как упростить запись следующего запроса?

SELECT НПР, ИМЯПР, ГОРОД, КОМ

FROM ПРОДАВЦЫ

WHERE (КОМ >0.12 OR КОМ < 0.14);

Примеры.

10. ОператорINопределяет множество, которому данное значение может принадлежать или не принадлежать.

Пусть нужно найти всех продавцов, расположенных либо в Клину, либо в Орле. Тогда можно написать следующий запрос:

SELECT * FROM ПРОДАВЦЫ

WHERE ГОРОД='Клин' OR ГОРОД='Орел'

Однако существует более простой способ получить ту же самую информацию:

SELECT * FROM ПРОДАВЦЫ

WHERE ГОРОД IN ('Клин','Орел');

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

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

11. Найти всех покупателей, обслуживаемых продавцами 1001, 1007,1004.

SELECT * FROM ПОКУПАТЕЛИ WHERE НПР IN(1001,1007,1004);

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

Для записи оператора используется ключевое слово BETWEEN, за которым следуют начальное значение, ключевое слово AND и конечное значение.

Например, следующий запрос извлекает таблицы ПРОДАВЦЫ всех продавцов, комиссионные которых имеют величину в диапазоне 0.10 и 0.12:

SELECT * FROM ПРОДАВЦЫ

WHERE КОМ BETWEEN 0.10 AND 0.12;

13. Оператор BETWEEN является включающим, т.е. граничные значения делают предикат истинным. Если граничные значения надо исключить, то можно, например записать

SELECT * FROM ПРОДАВЦЫ

WHERE (КОМ BETWEEN 0.10 AND 0.12)

AND NOT КОМ IN (0.10, 0.12);

Тогда получим результат:

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

SELECT * FROM ПОКУПАТЕЛИ WHERE ИМЯПК

BETWEEN 'A' AND 'Л';

В результат не попала строка с именем покупателя 'Лосев' несмотря на то. что BETWEEN является включающим. Дело в том, что строка ‘Л’ короче, чем 'Лосев', и при сравнении она дополняется пробелами, которые предшествуют символам алфавита. Об этом следует помнить при использовании BETWEEN со строковыми диапазонами.

15. Оператор LIKE применим только к полям текстового типа, поскольку он используется для поиска подстрок, т.е. он осуществляет просмотр строки для выяснения: входит ли заданная подстрока в указанное поле.

С этой же целью используются шаблоны, или подстановочные знаки:

* заменяет любые символы в начале или конке строки,

? заменяет любой текстовый символ,

# заменяет любую цифру.

Пусть надо найти покупателей, фамилии которых начинаются на К. Тогдf запрос может иметь вид:

SELECT * FROM ПОКУПАТЕЛИ

WHERE ИМЯПК LIKE 'К*';

Результат:

16. Часто в таблице встречаются записи с незаданными значениями какого-либо из полей. В таких случаях SQL позволяет указать в поле NULL-значение.

Результат любого сравнения с NULL-значением неизвестен. Для локализации NULL-значений SQL располагает специальным оператором IS. который используется с ключевым словом NULL.

Например, для нахождения всех записей со значениями NULL• таблице ПОКУПАТЕЛИ в столбце ГОРОД следует ввести: ,

SELECT * FROM ПОКУПАТЕЛИ

WHERE ГОРОД IS NULL;

В данном случае выходных данных не будет, поскольку в данной базе нет NULL-значений.

Запрос:

SELECT * FROM ПОКУПАТЕЛИ

WHERE ГОРОД IS NOT NULL;

или:

SELECT * FROM ПОКУПАТЕЛИ

WHERE NOT ГОРОД IS NULL;

приведет к выводу всей таблицы ПОКУПАТЕЛИ.

Задание на самостоятельное выполнение

12. Запишите два запроса (один с оператором IN, другой с BETWEEN), которые выдают сведения о всех заявках, принятых 3 или 4 октября 1999 года.

13. Запишите запрос, который выбирает всех покупателей. обслуживаемых Петровым или Мишиным, номера которых, в таблице ПРОДАВЦЫ соответственно 1001 и 1004.

14. .Запишите запрос, который выбирал всех покупателей, имена которых начинаются на любую из буи от 'А' до ‘П’.

15. .Запишите запрос, который выбирал всех покупателей, имена которых начинаются на ’В’.

16. Запишите запрос, который выбирает все заявки, у которых в поле СУММА указано какое-либо значение отличное от 0.

Примеры.

17. Найти сумму всех заявок из таблицы ЗАКАЗЫ:

SELECT SUM (СУММА) FROM ЗАКАЗЫ;

Результат:

18. Похожей операцией является вычисление среднего значения:

SELECT AVG (СУММА) AS Средн_сумма FROM ЗАКАЗЫ;

Ключевое слово AS позволяет переименовать заголовок выводимого результата.

19. Функцию COUNT используют чаше всего для подсчета количества строк.

Например:

SELECT COUNT (*) AS Кол_заказов FROM ЗАКАЗЫ;

Предложение GROUP BY

20. Предложение GROUP BY позволяет определять подмножество значений отдельного поля в терминах другого поля и применять функции агрегирования к полученному подмножеству.

Например, нужно найти наибольший Заказ из тех, что получил каждый из продавцов.

SELECT НПР, MAX (СУММА) AS Сумма_макс FROM ЗАКАЗЫ GROUP BY НПР;

GROUP BY применяет агрегатные функции отдельно к каждой серии групп, которые определяются общим значением поля. В последнем запросе каждая группа состоит из всех тех строк, которые имеют одно и то же значение НПР, а функция МАХ применяется отдельно к каждой такой группе.

21. Можно применять GROUP BY с несколькими полями.

Пусть, например, в предыдущем запросе необходимо увидел наибольший заказ, сделанный каждому продавцу на каждую дату. Для этого нужно сгруппировать данные из таблицы ЗАКАЗЫ по дате внутри одного и того же поля НПР и применить функцию МАХ к каждой группе:

SELECT НПР, ДАТА, MAX(СУММА) FROM ЗАКАЗЫ GROUP BY НПР, ДАТА;

Пустые даты, т.е. даты, когда данный продавец не получал заказов, в результате не представлены.

При использовании предложения GROUP BY всe поля в списке полей инструкции SELECT должны быть либо включены в положение GROUP BY, либо использоваться в качестве аргументов статистической функции SQL.

Предложение HAVING

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

SELECT НПР, ДАТА, MAX (СУММА) FROM ЗАКАЗЫ WHERE MAX (СУММА)> 3000.00 GROUP BY НПР, ДАТА;

Здесь следует использовать предложение HAVING. Оно определяет критерий, согласно которому определенные группы исключаются из числа выходных данных так же, как предложение WHERE делает это для отдельных строк. Правильная команда выглядит так:

SELECT НПР, ДАТА, MAX(СУММА) FROM ЗАКАЗЫ GROUP BY НПР, ДАТА HAVING MAX(СУММА) > 3000.00;

23. ПредложениеHAVING должно относиться только к агрегатам и полям, выбранным по GROUP BY.

Следующая команда, например, некорректна, поскольку к полю ДАТА неприменена никакая агрегатная функция и этого поля нет в предложении GROUT BY:

SELECT НПР, MAX(СУММА) FROM ЗАКАЗЫ GROUP BY НПР HAVING ДАТА = #10/03/990#;

Соответствующий корректный запрос, показывающий максимальные суммы для (номеров) продавцов на 3 октября 1999 года, если этот максимум превышает сумму 800.00:

SELECT НПР, MAX(СУММА) FROM ЗАКАЗЫ

WHERE ДАТА = #10/03/99#

GROUP BY НПР HAVING MAX(СУММА)>800.00;

Таким образом, предложение WHERE используется для исключения записей из группировки, а предложение HAVING для применения фильтра к записям после группировки.

24. Корректным будет также записанный ниже запрос, показывающий максимальные суммы заказов, полученные продавцами с номерами 1002 и 1007: ,

SELECT НПР, MAX(СУММА) FROM ЗАКАЗЫ

GROUP BY НПР HAVING НПР IN (1002,1007);

Задание на самостоятельное выполнение

17. Запишите запрос, который подсчитывает все заявки за 3 октября 1999 года.

18. Запишите запрос, который подсчитывает количество продавцов.

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

20. Запишите запрос, который выбирает первого в алфавитном порядке покупателя, имя которого начинается с ’Т’.

21. Запишите запрос, который выбирает максимальный рейтинг для каждого города.

22. Что означает запрос, записанный в виде:

SELECT ГОРОД МАХ(РЕЙТ)

FROM ПОКУПАТЕЛИ

GROUP BYГОРОД

HAVING МАХ(РЕИТ) > 100;

Задание на лабораторную работу

1. Изучить применение операторов языка 5QL по приведенным примерам.

2. Выполнить задания для самостоятельного выполнения в СУБД Access в режиме формирования запросов на SQL и сохранить соответствующие запросы базе graber.mdb.

3. Составить отчет о выполненной работе и внести в него результаты выполнения упражнений.

Литература

Грабер М. Введение в SQL. M.: Лори, 1996, 3 75:

Лабораторная работа

«Формирование запросов на языке SQL»

Цель работы:Получить практические навыки формирования запросов на языке SQL в среде Microsoft Access.

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

1. Изучить применение операторов языка 5QL по приведенным в кратком теоретическом описании примерам.

2. Выполнить задания для самостоятельного выполнения в СУБД Access в режиме формирования запросов на SQL и сохранить соответствующие запросы базе graber.mdb.

3. Составить отчет о выполненной работе и внести в него результаты выполнения упражнений.

Краткое теоретическое описание

Введение

SQL (Structured Query Language - структурированный язык запросов) в настоящее время является универсальным языком для работы с базами данных.

Язык SQL появился в 1974 г. Первый стандарт относится к 1986 году. Язык действующего ныне стандарта называют часто SQL-92.

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

SQL встроен также в СУБД Access, но не все требования стандарта реализованы в этой версии SQL. Некоторые особенности данной версии являются расширениями по отношению к стандарт} SQL-92.

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

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

В данной лабораторной работе изучаются лишь некоторые простые возможности SQL.

База данных (graber.mdb), используемая в примерах и в упражнениях.

Таблица ПРОДАВЦЫ

HПP - номер продавца ИМЯПР -имя продавца

ГОРОД - адрес продавца КОМ - комиссионные продавца

Таблица ЗАКАЗЫ

НЗК - номер заказа НПК - номер покупателя

НПР - номер продавца

Таблица ПОКУПАТЕЛИ

НПК - номер покупателя ИМЯПК - имя покупателя РЕЙТ - рейтинг покупателя НПР - номер продавца

Начало работы:

1. Выполнить пуск СУБД Access

2. Открыть файл базы данных graber.mdb

3. Выбрать режим ЗАПРОСЫ

4. Выбрать команду Создать.

5. Защита окно Добавление таблицы.

6. Перейти в режим SQL.

Выборка данных из таблиц

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

Примеры

1. Наберите в окне режима SQL запрос, который должен выводить из таблицы ПРОДАВЦЫ имена продавцов и их комиссионные:

SELECT ИМЯПР, КОМ FROM ПРОДАВЦЫ;

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

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

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

ключевого слова SELECT указать символ * ("звездочка"):

SELECT * FROM ПРОДАВЦЫ;

Результатом будет вся таблица ПРОДАВЦЫ. При этом столбцы выводятся в порядке их хранения в базе.

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

SELECT ИМЯПР, НПР, ГОРОД, КОМ FROM ПРОДАВЦЫ; Результатом будет следующая таблица:

4. Пусть необходимо запросить, какие по номеру продавцы имеют в

настоящее время заказы в таблице ЗАКАЗЫ. Введем:

SELECT НПР FROM ЗАКАЗЫ;

Получим следующую таблицу:

Такой результат содержит повторяющиеся строки, которые в данном случае желательно исключить для получения только различных номеров. С этой целью после ключевого слова SELECTставится ключевое словоDISTINCT:

SELECT DISTINCT НПР FROM ЗАКАЗЫ;

В результате получим:

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

Альтернативой DISTINCTявляется ALL. Это ключевое слово имеет противоположное действие: повторяющиеся строки включаются в состав выходных данных. Если не задано ни DISTINCTни ALL,то предполагается ALL.

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

Предположим необходимо узнать имена всех продавцов в Орле. В этом случае можно ввести команду:

SELECT ИМЯПР, ГОРОД FROM ПРОДАВЦЫ WHERE ГОРОД=”ОРЕЛ”;

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

6. При использовании в предложении WHERE числового поля конкретные значения не заключаются в кавычки, как это сделано в следующем примере выбора всех покупателей с рейтингом равным 100.

SELECT * FROM ПОКУПАТЕЛИ WHERE РЕЙТ=100;

Результат:

Задание на самостоятельное выполнение

1.Запишите команду SELECT, которая выводит номер, сумму и дату заказов для всех строк таблицы ЗАКАЗЫ.

2.Запишите запрос, который выдает все строки таблицы ПОКУПАТЕЛИ, где продавец имеет-номер 1001.

3. Запишите запрос, который выдает строки таблицы ПРОДАВЦЫ порядке ГОРОД, ИМЯПР, НПР, КОМ.

4. Запишите команду SELECT, которая выдает рейтинг и следом за ним ИМЯПК каждого покупателя, проживающего в Саратове.

5. Запишите запрос, позволяющий получить значения столбца НПР для всех продавцов, номера которых находятся в настоящее время в таблице ЗАКАЗЫ, причем повторения требуется исключить.