Практическое занятие № 6
Тема: Работа с представлениями в разных СУБД
Цель работы: получить практические навыки по созданию и использованию представлений
Ход работы
Пусть имеется БД "dbase" в формате SQL Server. Данная БД состоит из 6 таблиц со следующей структурой:
Таблицы связаны так:
| Главная таблица | Подчиненная таблица | Поле для связи | Тип связи |
| otdelen | spec | id_otd | 1 : ∞ |
| spec | grupy | id_spec | 1 : ∞ |
| grupy | students | id_grup | 1 : ∞ |
| students | ocenki | id_stud | 1 : ∞ |
| predmety | ocenki | id_predmet | 1 : ∞ |
Выполните подключение БД к серверу (если она отсутствует).
Указания: разархивируйте архив.
Запустите утилиту "Microsoft SQL Server Management Studio", пройдите регистрацию введя информацию: Параметры подлинности = "Проверка подлинности SQL Server", Имя пользователя=sa, пароль=111 (у Вас может быть свой пароль).
В дереве объектов вызовите контекстное меню папки "Базы данных" и выберите команду "Присоединить". В правой части появится окно, в котором с помощью кнопки "Добавить" укажите файл из архива и щелкните ОК.
В результате у вас на сервере будет зарегистрирована новая БД.
Для создания запроса выберите в левой части нужную БД и щелкните на кнопке
"Создать запрос" 
В правой части окна
введите текст запроса.
Для выполнения запроса щелкните на кнопке "Выполнить" 
.
В нижней части окна отобразится набор отобранных данных.
Создайте представление № 1 "stud_stbal" для расчета среднего балла каждого студента.
Указания: для создания представления введите команду:
create view stud_srbal
as
select id_stud,
avg(ocenka) as srbal
from ocenki
group by id_stud;
После выполнения команды в левой части окна программы в папке Представления появится созданный вами элемент "stud_srbal". В его контекстном меню выберите команду "Выбрать первые 1000 строк" для просмотра результата работы представления.
Используя представление "stud_srbal", для каждой группы определите количество отличников, хорошистов и троечников (отбирать по среднему баллу, а не по наличию оценок).
Указания: напишем запрос, в котором список средних баллов каждого студента возьмем из созданного ранее представления. При этом введем новое поле nazv, в котором каждого студента пометим текстовой меткой в зависимости от его среднего балла. Результат отбора сгруппируем по группе и по созданной текстовой метке.
Внутри запроса выполним ряд действий:
- свяжем таблицу grupy с представлением stud_srbal через таблицу students;
- для отображения вида рассчитываемой информации (отличники, хорошисты или троечники) будем использовать функцию case;
- по правилам создания запросов с группировкой данных укажем в разделе group by название группы и функцию case.
select nazv_grup,
case when srbal=5 then 'отличников'
when srbal>=4 then 'хорошистов'
else 'троечников' end as nazv,
count(students.id_stud) as kol_stud
from grupy inner join students on grupy.id_grup=students.id_grup
inner join stud_srbal on students.id_stud=stud_srbal.id_stud
group by nazv_grup, case when srbal=5 then 'отличников'
when srbal>=4 then 'хорошистов'
else 'троечников' end;
Определите эти же показатели для каждой специальности.
Указания: запрос похож на предыдущий, но добавляется еще и таблица spec с группировкой по специальности:
Внутри запроса выполним ряд действий:
- свяжем таблицу spec с представлением stud_srbal через таблицы grupy и students;
- для отображения вида рассчитываемой информации (отличники, хорошисты или троечники) будем использовать функцию case;
- по правилам создания запросов с группировкой данных укажем в разделе group by название группы и функцию case.
select nazv_spec,
case when srbal=5 then 'отличников'
when srbal>=4 then 'хорошистов'
else 'троечников' end as nazv,
count(students.id_stud) as kol_stud
from spec inner join grupy on spec.id_spec=grupy.id_spec
inner join students on grupy.id_grup=students.id_grup
inner join stud_srbal on students.id_stud=stud_srbal.id_stud
group by nazv_spec, case when srbal=5 then 'отличников'
when srbal>=4 then 'хорошистов'
else 'троечников' end;
Используя представление № 1 "stud_srbal", создайте запрос на расчет стипендии для каждого студента. Выведите номер студента и сумму стипендии.
Указания: введите запрос вида:
select id_stud,
case when srbal=5 then 1475
when srbal>=4 then 1300
else 0 end as stip
from stud_srbal;
Созданный в предыдущем задании запрос оформить в виде представления № 2 "stip_list".
Указания: нужно над запросом добавить команды создания представления. Также для использования этого представления в других запросах, добавим в его запросе отбор поля id_stud для связывания с другими таблицами:
create view stip_list
as
select id_stud,
case when srbal=5 then 1475
when srbal>=4 then 1300
else 0 end as stip
from stud_srbal;
После выполнения команды в левой части окна программы в папке Представления появится созданный вами элемент "stip_list". Если представление не появилось, выделите узел "Представления" и нажмите F5 для обновления.
Используя представление № 2 "stip_list", выведите ФИО и сумму стипендии. Данные отсортируйте по ФИО.
Указания: для решения свяжем представление с таблицей students.
Введите запрос вида:
select fam,
imya,
otch,
stip
from students inner join stip_list on students.id_stud=stip_list.id_stud
order by fam, imya, otch
Используя представление № 2 "stip_list", определить суммарную стипендию для девушек и парней.
Указания: для решения свяжем представление с таблицей students.
Введите запрос вида:
select pol,
sum(stip) as sumstip
from students inner join stip_list on students.id_stud=stip_list.id_stud
group by pol;
Используя представление № 2 "stip_list", определить суммарную стипендию для каждой группы.
Указания: запрос аналогичен предыдущему. Добавим в него выбор из таблицы grupy и группировку по группам. Для решения свяжем представление с таблицей grupy через таблицу students.
select nazv_grup,
sum(stip) as sumstip
from grupy inner join students on grupy.id_grup=students.id_grup
inner join stip_list on students.id_stud=stip_list.id_stud
group by nazv_grup;