Методические указания (542539), страница 4

Просмтор этого файла доступен только зарегистрированным пользователям. Но у нас супер быстрая регистрация: достаточно только электронной почты!

Текст из файла (страница 4)

Рис. 21

Рис. 22

2) select spec, count(*) from poss

where gp='90' or gp='91'

group by spec [F5]

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

Замечание. Имя поля, по которому выдается статистика, задается и в списке выборки, и в структуре group by.

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

Выдать статистику по выпускникам 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) с помощью хранимой процедуры.

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

Цель:

1. Практическое освоение операторов grant/revoke, union и create view для получения доступа к защищенным данным.

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

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

Упражнения:

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

1. Создать входные имена SQL-сервера VAF и APB из БД master (имя sa).

Для этого в верхней области окна необходимо ввести:

sp_addlogin VAF, teacher, stud1 [F5]

sp_addlogin APB, freedom, stud1 [F5]

1. Создать пользователей БД MPEI1 из БД MPEI1 (имя STUD1).

Для этого в верхней области окна необходимо ввести:

sp_adduser VAF [F5]

sp_adduser APB [F5]

3) Предоставить командное полномочие на выполнение команды create table пользователю VAF. Для этого в верхней области окна необходимо ввести:

grant create table to VAF [F5]

4) Предоставить объектное полномочие на выполнение команд select, update, insert в таблице, созданной пользователем STUD1, пользователю АРВ. Для этого в верхней области окна необходимо ввести:

grant select, update, insert, delete

on stud1.poss to APB [F5]

5) Выполнить запрос по двум информационным таблицам (например: poss и ukr): выдать данные по учащимся России и Украины, заканчивающим учебу в 95, 97 и 98 годах.

Для того, чтобы выполнить этот запрос, например пользователю АРВ, который имеет объектное полномочие на работу с таблицей poss, но не имеет полномочий на таблицу ukr, необходимо:

а) владельцу БД stud1 (STUD1) создать и загрузить таблицу с именем ukr (по аналогии с таблицей poss);

б) предоставить объектное полномочие на выполнение команды select в таблице ukr пользователю АРВ, т.е. ввести команду:

grant select on stud1.ukr to APB [F5]

в) выйти из SQL Query Analyser (disconnect) и войти в SQL Query Analyser с именем APB;

г) так как таблицы poss и ukr имеют одинаковую структуру, то для этого запроса можно использовать оператор union, т.е. ввести команду:

select * from stud1.poss where gok in ('95', '97', '98')

union

select * from stud1.ukr where gok in ('95', '97', '98') [F5]

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

Рис. 28

д) отменить предоставленное пользователю АРВ полномочие на таблицу ukr, т.е. выйти из SQL Query Analyser (disconnect), войти в SQL Query Analyser с именем STUD1 и ввести команду:

revoke select on ukr from APB [F5]

6) Создать представление с именем pr1 на основе таблицы ukr.

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

create view pr1

(nomer1, fio1, data_rogden1, pol1, sem_polog_k1,

spec1, kat_obuch_k1, vuz_k1, mp1, gp1, mo1, gok1)

as select * from ukr [F5]

Замечание. Предварительно пользователю АРВ владельцем БД STUD1 должны быть предоставлены полномочия на использование команды create view и команды select в таблице ukr:

grant create view to APB [F5]

grant select on ukr APB [F5]

7) Предоставить вспомогательную таблицу vuz_gorod и четыре таблицы словаря БД в общее пользование. Для этого необходимо ввести следующие команды grant для каждой таблицы (команды вводятся владельцем БД STUD1):

grant all on vuz_gorod to public [F5]

grant all on vuz to public [F5]

grant all on family to public [F5]

grant all on kat_obuch to public [F5]

grant all on gorod to public [F5]

Задание:

1. Из БД master (входное серверное имя sa, пароль compute) создать 2 входных серверных имени, имена произвольны;

2. Используя эти имена, в своей БД (выйти из БД master и войти в свою) создать двух пользователей вашей БД;

3. Предоставить первому пользователю командное полномочие (произвольное), а второму – объектное (тоже произвольное);

4. Создать представление на основе сложного оператора SELECT (типа оператора SELECT из лабораторной работы №3 или №4).

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

Цель:

1. Знакомство с использованием контрольных ограничений и правил для поддержки целостности данных в БД.

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

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

Упражнения:

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

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

- на уровне поля;

- на уровне таблицы.

(см. конспект курса).

1) Создание контрольных ограничений на уровне поля.

Рассмотрим структуру таблицы poss:

а) поле номер (nomer) - контрольным ограничением для него является промежуток чисел от 1 до 700000;

б) поле фамилия, имя, отчество (fio) - контрольным ограничением для него является запрет использования символов "." (точка) и "-" (минус);

Характеристики

Список файлов книги