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

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

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

Этот запрос выполняется аналогично запросу первого варианта, за исключением того, что в отчете необходимо выдать значения атрибута (названия учебных заведений), не принадлежащего структуре таблицы poss. Другими словами, в этом запросе необходимо в предложении where (условии выборки) добавить условие соединения двух таблиц (poss и vuz) по полю код учебного заведения, в предложении from имя таблицы vuz, а в списке выборки и в предложении group by указать составное имя для поля название учебного заведения (с именем таблицы).

Поэтому здесь необходимо выполнить два следующих запроса:

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

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

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

1) select count(*) from poss

where gok='96' [F5]

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

2) select vuz.uch_zavedenie, count(*)

from poss join vuz on vuz_k=vuz.cod

where gok='96'

group by vuz.uch_zavedenie [F5]

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

Рис. 23

Рис. 24

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

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

Этот запрос выполняется аналогично запросу второго варианта, за исключением того, что вместо названий учебных заведений необходимо в отчете выдать названия городов. Для этого нужно вместо соединения таблиц poss и vuz по полю код учебного заведения выполнить соединение таблиц poss и vuz_gorod по полю код учебного заведения, так как поле "название города" принадлежит структуре таблицы vuz_gorod.

Поэтому здесь необходимо выполнить два следующих запроса:

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

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

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

1) select count(*) from poss [F5]

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

Рис. 25

2) select vuz_gorod.nazvanie_goroda, count(*)

from poss join vuz_gorod on vuz_k=vuz_gorod.cod_vuza

group by vuz_gorod.nazvanie_goroda [F5]

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

Рис. 26

Возможности SQL Query Analyser в плане выполнения сложных запросов к БД весьма ограничены (ограничения связаны с трудоемкостью набора длинных предложений SELECT и с реляционной ограниченностью языка SQL). Одним из способов преодоления ограничений является использование хранимых процедур, описанных в конспекте курса.

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

Для выполнения одномерных статистических запросов в SQL Query Analyser требуется задавать два предложения SELECT и получать два результата. Чтобы объединить оба результата в один отчет, необходимо задать оба предложения SELECT в одной хранимой процедуре.

Пример создания процедуры (смотри первый вариант запроса):

create procedure zapr1 as

select count(*) from poss where gp='90' or gp='91'

select spec, count(*) from poss

where gp='90' or gp='91' group by spec [F5]

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

zapr1 [F5]

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

Рис. 27

Замечание. Если во время набора команды create procedure будут допущены синтаксические ошибки, то после нажатия [F5] прекомпилятор в нижней половине окна выдаст сообщение об ошибке.

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

Варианты заданий на выполнение лабораторной работы № 5 приведены в приложении IV. Задание необходимо выполнить двумя способами: 1) в диалоговом SQL; 2) с помощью хранимой процедуры.