ЛАБОРАТОРНАЯ РАБОТА № 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.