Методические указания, страница 3

pol, spec, kat_obuch. kat_obucheniya, mo, gok

from poss, vuz, kat_obuch

where gp='91'

and vuz_k=vuz.cod

and kat_obuch_k=kat_obuch.cod

order by 3,5 [F5]

или

select fio, data_rogden, vuz.uch_zavedenie,

pol, spec, kat_obuch. kat_obucheniya, mo, gok

from poss join vuz on vuz_k=vuz.cod

join kat_obuch on kat_obuch_k=kat_obuch.cod

where gp='91'

order by 3,5

Отчет будет представлен в нижней области окна (см. рис. 17).

3) Выполнение запроса по четырем таблицам БД со сложным условием, например:

выдать фамилии, даты рождения, названия учебных заведений, пол, семейные положения, специальности, категории обучения, месяцы приема, годы приема, месяцы окончания и годы окончания по выпускникам России 1993 и 1995 г.г. по специальностям от 0701 до 0705; данные отсортировать по годам окончания, специальностям и учебным заведениям.

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

Рис. 17

имена (т.е. с именем таблицы), в предложении from должны быть записаны четыре имени таблиц, а в предложении where - три условия соединения таблиц (таблицы poss с таблицей vuz по полю код учебного заведения; таблицы poss с таблицей family по полю код семейного положения; таблицы poss с таблицей kat_obuch по полю код категории обучения).

Таким образом, в верхней области окна необходимо ввести:

select fio, data_rogden, vuz. uch_zavedenie, pol,

family.family_status, spec, kat_obuch. kat_obucheniya,

mp, gp, mo, gok

from poss, vuz, family, kat_obuch

where (gok='93' or gok='95')

and spec between '000701' and '000705'

and vuz_k=vuz.cod

and sem_polog_k=family.cod

and kat_obuch_k=kat_obuch.cod

order by 11, 6, 3 [F5]

или

select fio, data_rogden, vuz. uch_zavedenie, pol,

family.family_status, spec, kat_obuch. kat_obucheniya,

mp, gp, mo, gok

from poss join vuz on vuz_k=vuz.cod

join family on sem_polog_k=family.cod

join kat_obuch on kat_obuch_k=kat_obuch.cod

where (gok='93' or gok='95')

and spec between '000701' and '000705'

order by 11, 6, 3 [F5]

Отчет будет представлен в нижней области окна (см. рис. 18).

Рис. 18

Варианты заданий на выполнение лабораторной работы № 3 приведены в приложении II.

ЛАБОРАТОРНАЯ РАБОТА № 4

Цель:

1. Приобрести практические навыки выполнения запросов с подзапросами.

Справочный материал:

1. Конспект курса.

Упражнения:

Перед выполнением лабораторной работы необходимо войти в сеть, затем в систему MS SQL Server и SQL Query Analyser, а также установить связь пользователя с базой данных (аналогично описанному сценарию в лабораторной работе № 1).

Для использования подзапросов (оператор select) в операторах манипулирования данными (select, update, insert, delete) употребляют три формата предложения where, указанных в конспекте курса (in, any/all, exists). Наиболее часто используется первый формат с функцией in.

1) Выполнение запроса по четырем таблицам БД с использованием подзапроса, например:

выдать список студентов, обучаемых в городе Москве, принятых на учебу в 1989 г.; схема выдачи отчета должна включать следующие поля: ФИО, дата рождения, специальность, категория обучения, название учебного заведения, месяц окончания, год окончания; данные отсортировать по учебным заведениям и специальностям.

В этом запросе требуется, во-первых: выдать значения двух полей, не принадлежащих структуре таблицы poss, а именно: названия учебных заведений и названия категорий обучений; во-вторых: учебные заведения должны быть московскими, т.е. необходимо дополнительно сделать подзапрос по таблице vuz_gorod, указав в списке выборки код учебного заведения, а в условии выборки - город Москва.

Поэтому в списке выборки запроса для полей учебное заведение и категория обучения должны быть указаны составные имена (т.е. с именем таблицы), в предложении from должны быть записаны три имени таблиц, а в предложении where для поля код учебного заведения должны быть использованы функция IN и подзапрос, а также должны быть указаны два условия соединения таблиц (таблицы poss с таблицей vuz по полю код учебного заведения; таблицы poss с таблицей kat_obuch по полю код категории обучения).

Таким образом, в верхней области окна необходимо ввести:

select fio, data_rogden, spec, kat_obuch.kat_obucheniya,

vuz.uch_zavedenie, mo, gok

from poss join kat_obuch on kat_obuch_k=kat_obuch.cod

join vuz on vuz_k=vuz.cod

where vuz_k in

(select cod_vuza from vuz_gorod

where cod_goroda='001')

and kat_obuch_k between '03' and '04'

and gp='89'

order by 5, 3 [F5]

где '001' - код города Москвы.

Отчет будет представлен в нижней области окна (см. рис. 19).

2) Выполнение запроса по пяти таблицам БД с использованием подзапроса со сложным условием выборки данных, например:

выдать список аспирантов и стажеров, обучаемых в городах Москва, Санкт-Петербург и Воронеж и заканчивающих учебу в 1993 и 1995 г.г.; схема выдачи отчета должна включать поля: ФИО, дата рождения, название

Рис. 19

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

В этом запросе требуется, во-первых: выдать значения трех полей, не принадлежащих структуре таблицы poss, а именно: названия учебных заведений, названия семейных положений и названия категорий обучения; во-вторых: учебные заведения должны быть московскими, санкт-петербургскими и воронежскими, т.е. необходимо сделать подзапрос по таблице vuz_gorod, указав в списке выборки код учебного заведения, а в условии выборки - три значения городов - Москва, Санкт-Петербург и Воронеж.

Поэтому в списке выборки запроса для полей учебное заведение, семейное положение и категория обучения должны быть указаны составные имена (т.е. с именем таблицы), в предложении from должны быть записаны четыре имени таблиц, а в предложении where для поля код учебного заведения должны быть использованы функция IN и подзапрос, а также должны быть указаны три условия соединения таблиц (таблицы poss с таблицей vuz по полю код учебного заведения; таблицы poss с таблицей family по полю код семейного положения; таблицы poss с таблицей kat_obuch по полю код категории обучения).

Таким образом, в верхней области окна необходимо ввести:

select fio, data_rogden, vuz.uch_zavedenie, pol,

family.family_status, spec, kat_obuch.kat_obucheniya,

mp, gp, mo, gok

from poss join vuz on vuz_k=vuz.cod

join family on sem_polog_k=family.cod

join kat_obuch on kat_obuch_k=kat_obuch.cod

where vuz_k in

(select cod_vuza from vuz_gorod

where cod_goroda='001' or cod_goroda='002' or

cod_goroda='006')

and (kat_obuch_k between '07' and '08' or

kat_obuch_k between '10' and '11')

and gok in ('93', '95')

order by 3, 11 [F5]

Рис. 20

где '001' - код города Москвы;

'002' - код города Санкт-Петербург;

'006' - код города Воронеж;

'07' - код, соответствующий категории обучения "аспирант";

'08' - код, соответствующий категории обучения "аспирант-
заочник";

'10' и '11' - коды, соответствующие категориям
обучения "повышение квалификации" и
"стажер".

Отчет будет представлен в нижней области окна (см. рис. 20).

Варианты заданий на выполнение лабораторной работы № 4 приведены в приложении III.

ЛАБОРАТОРНАЯ РАБОТА № 5

Цель:

1. Научиться выполнять одномерные статистические запросы по одной и по нескольким таблицам БД.

2. Практически освоить создание и использование простых хранимых процедур для выполнения запросов.

Справочный материал:

1. Конспект курса.

Упражнения:

Перед выполнением лабораторной работы необходимо войти в сеть, затем в систему MS SQL Server и SQL Query Analyser, а также установить связь пользователя с базой данных (аналогично описанному сценарию в лабораторной работе № 1).

Реляционные СУБД специально приспособлены для выполнения списковых и персональных запросов (в результирующей форме отчета содержится совокупность документов (записей) в виде списка значений атрибутов (полей)). Проблемы возникают при выполнении статистических запросов.

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

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

И так далее, если количественные характеристики выдаются по n атрибутам, такой запрос называется n-мерным статистическим запросом. Кроме этого, в статистических запросах может использоваться операция соединения таблиц, поэтому статистические данные могут выдаваться по атрибутам таблиц, связанных с той таблицей, по которой выполняется запрос. Например, количество учащихся по каждому городу России (для этого запроса необходимо соединение таблиц poss и vuz_gorod по полю код учебного заведения).

Первый Вариант запроса к базе данных

Выдать статистику по приему учащихся 1990 и 1991 годов в России по всем специальностям. Отчет должен начинаться с общей цифры - всего по приему 90-91 г.г. Далее - таблица, включающая перечень шифров специальностей с цифрами, не равными нулю (цифра - это количество учащихся по указанной специальности; если количество учащихся по специальности равно нулю, то этот шифр специальности не включается в отчет).

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

Другими словами, необходимо выполнить два запроса:

1) выполнить запрос с условием выборки по году приема (значения '90' и '91'), используя в списке выборки агрегатную функцию count(*), предназначенную для подсчета общего количества записей по запросу;

2) выполнить запрос с условием выборки по году приема (значения '90' и '91'), используя предложение group by для группирования значений по полю "специальность" и агрегатную функцию count(*) в списке выборки, предназначенную для оценки количества значений специальностей в каждой группе.

Таким образом, в верхней области окна необходимо ввести:

1) select count(*) from poss

where gp='90' or gp='91' [F5]

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