Практическое занятие № 5
Тема: Использование подзапросов для выборки данных
Цель работы: получить практические навыки по выборке данных из нескольких таблиц с использованием подзапросов
Ход работы
Пусть имеется БД "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 (у Вас может быть свой пароль).
В дереве объектов вызовите контекстное меню папки "Базы данных" и выберите команду "Присоединить". В правой части появится окно, в котором с помощью кнопки "Добавить" укажите файл из архива и щелкните ОК.
В результате у вас на сервере будет зарегистрирована новая БД.
Для создания запроса выберите в левой части нужную БД и щелкните на кнопке
"Создать запрос" 
В правой части окна
введите текст запроса.
Для выполнения запроса щелкните на кнопке "Выполнить" 
.
В нижней части окна отобразится набор отобранных данных.
Подзапросы в разделе From
Запрос 1. Определите средний балл для каждого студента. Отсортировать по среднему по убыванию и по фамилии по возрастанию.
Запрос 2. Определите размер стипендии для каждого студента по формуле (1475 руб. - средний балл = 5; 1300 руб. - средний балл >4; 0 в остальных случаях). Отсортировать по стипендии по убыванию и по фамилии по возрастанию.
Запрос 3. Определите суммарную стипендию для каждой группы учебного заведения.
Запрос 4. Определите среднюю стипендию для каждой специальности.
Запрос 5. Определите суммарную стипендию отдельно для парней и девушек.
Запрос 6. Отобрать группы, сумма стипендии в которых выше 10000.
Подзапросы в разделе Where
Запрос 7. Выведите самых старших студентов (с наименьшей датой рождения).
Запрос 8. Выведите студентов, которые родились в те же дни, что и студенты группы 38.
Запрос 9. Выведите студентов, которые родились в любые дни, кроме тех, в которые родились студенты группы 38.
Запрос 10. Выведите студентов, которые не имеют троек (хорошисты и отличники).
Запрос 11. Определите, сколько хорошистов и отличников в каждой группе.
Запрос 12. Вывести группы, в которых количество хорошистов и отличников превышает 10 человек.
Запрос 13. Для каждого отделения определите количество девушек троечниц.
Подзапросы, на существование
Запрос 14. Вывести список групп, в которых нет троечников.
Запрос 15. Вывести список групп, в которых есть троечники.
Запрос 16. Вывести список групп, в которых учатся студенты с именем Ярослав.
Отбор по расчетным показателям
Запрос 17. Отобрать список студентов, у которых средний балл выше среднего балла по учебному заведению
Запрос 18. Отобрать список студентов, у которых средний балл выше, чем средний балл в их группе
Выполнение работы
Запрос 1.
Указания:
Вначале напишем подзапрос, который по таблице ocenki определяет для каждого id_stud его средний балл.
select id_stud,
avg(ocenka) as srbal
from ocenki
group by id_stud;
Если этот запрос рассмотреть как таблицу и связать его с таблицей students по полю id_stud, то можно решить поставленную задачу:
select fam,
imya,
srbal
from students inner join (select id_stud,
avg(ocenka) as srbal
from ocenki
group by id_stud) as a
on students.id_stud=a.id_stud
order by srbal desc, fam;
Запрос 2.
Указания:
Вначале напишем подзапрос, который по таблице ocenki определяет для каждого id_stud его средний балл.
select id_stud,
avg(ocenka) as srbal
from ocenki
group by id_stud;
Если этот запрос рассмотреть как таблицу и связать его с таблицей students по полю id_stud, то можно решить поставленную задачу:
select fam,
imya,
case when srbal=5 then 1475
when srbal>=4 then 1300
else 0 end as stip
from students inner join (select id_stud,
avg(ocenka) as srbal
from ocenki
group by id_stud) as a
on students.id_stud=a.id_stud
order by stip desc, fam;
Запрос 3.
Указания:
В предыдущий запрос внесем изменения: для отбора названия группы добавим таблицу grupy с указанием ее связи с таблицей students, по полю stip рассчитываем сумму, выполним группировку по полю nazv_grup
select nazv_grup,
sum(case when srbal=5 then 1475
when srbal>=4 then 1300
else 0 end) as sumstip
from grupy inner join students on grupy.id_grup=students.id_grup
inner join (select id_stud,
avg(ocenka) as srbal
from ocenki
group by id_stud) as a
on students.id_stud=a.id_stud
group by nazv_grup;
Запрос 4.
Указания:
Изменим предыдущий запрос: для отбора специальности добавим поле nazv_spec и таблицу spec с указанием связи с таблицей grupy, заменим функцию sum на avg, выполним группировку по полю nazv_spec
select nazv_spec,
avg(case when srbal=5 then 1475
when srbal>=4 then 1300
else 0 end) as sredstip
from spec inner join grupy on spec.id_spec=grupy.id_spec
inner join students on grupy.id_grup=students.id_grup
inner join (select id_stud,
avg(ocenka) as srbal
from ocenki
group by id_stud) as a
on students.id_stud=a.id_stud
group by nazv_spec;
Запрос 5.
Указания:
Запрос проще, чем предыдущие. Отберем pol из таблицы students и суммарную стипендию (как в запросе № 2). Свяжем таблицу students и подзапрос расчета среднего балла по полю id_stud и выполним группировку по полю pol.
select pol,
avg(case when srbal=5 then 1475
when srbal>=4 then 1300
else 0 end) as sredstip
from students inner join (select id_stud,
avg(ocenka) as srbal
from ocenki
group by id_stud) as a
on students.id_stud=a.id_stud
group by pol;
Запрос 6.
Указания:
Для выполнения расчета полностью походит текст запроса № 2. Только для отбора нужно добавить в раздел Having условие 'формула для суммы стипендии'>10000
select nazv_grup,
sum(case when srbal=5 then 1475
when srbal>=4 then 1300
else 0 end) as sumstip
from grupy inner join students on grupy.id_grup=students.id_grup
inner join (select id_stud,
avg(ocenka) as srbal
from ocenki
group by id_stud) as a
on students.id_stud=a.id_stud
group by nazv_grup
having sum(case when srbal=5 then 1475
when srbal>=4 then 1300
else 0 end) >10000;
Запрос 7.
Указания:
Сначала напишем подзапрос нахождения минимальной даты рождения по таблице students
select min(datar)
from students;
Теперь отберем студентов, у которых дата рождения равна найденному минимальному значению.
select *
from students
where datar = (select min(datar)
from students);
Запрос 8.
Указания:
Сначала напишем подзапрос для отбора дат рождения всех студентов из группы 38. Название группы находится в поле nazv_grup из таблицы grupy. Свяжем эту таблицу с таблицей students.
select datar
from grupy inner join students on grupy.id_grup=students.id_grup
where nazv_grup like '38%';
Теперь отберем студентов у которых дата рождения равна одной из дат, отобранных выше.
select *
from students
where datar in (select datar
from grupy inner join students on grupy.id_grup=students.id_grup
where nazv_grup like '38%');
Запрос 9.
Указания:
Для решения будем использовать предыдущий запрос, только перед функцией in добавим отрицание not.
select *
from students
where datar not in (select datar
from grupy inner join students on grupy.id_grup=students.id_grup
where nazv_grup like '38%');
Запрос 10.
Указания:
Для решения напишем подзапрос, который отбирает оценки конкретного студента (например, id_stud=1).
В данном решении подзапрос будет использоваться в условии Where.
select ocenka
from ocenki
where id_stud=1;
Теперь выведем студентов, не имеющих среди своих оценок 3. Для получения оценок очередного студента будем передавать id_stud из главного запроса в подзапрос
select *
from students
where 3 not in (select ocenka
from ocenki
where ocenki.id_stud=students.id_stud);
Запрос 11.
Указания:
Для решения внесем изменения в предыдущий запрос.
Для вывода названия группы будем использовать поле nazv_grup из таблицы grupy, которую свяжем с таблицей students. Количество студентов будем считать с помощью функции count(students.id_stud) и группировать данные по полю nazv_grup.
Нам нужны только те записи, у которых среди отобранных оценок нет 3.
select nazv_grup,
count(students.id_stud) as kolstud
from grupy inner join students on grupy.id_grup=students.id_grup
where 3 not in (select ocenka
from ocenki
where id_stud=students.id_stud)
group by nazv_grup;
Запрос 12.
Указания:
Запрос создается на основе предыдущего запроса, но с добавлением условия в разделе Having, в котором значение функции count(students.id_stud) > 10
select nazv_grup,
count(students.id_stud) as kolstud
from grupy inner join students on grupy.id_grup=students.id_grup
where 3 not in (select ocenka
from ocenki
where id_stud=students.id_stud)
group by nazv_grup
having count(students.id_stud)>10;
Запрос 13.
Указания:
В предыдущий запрос добавим поле nazv_otd из таблицы otdelen и укажем цепочку связей таблиц otdelen, spec, grupy, students. Добавим условие по полю pol (женский), и сгруппируем данные по полю nazv_otd.
select nazv_otd,
count(students.id_stud) as kol_stud
from otdelen inner join spec on otdelen.id_otd=spec.id_otd
inner join grupy on spec.id_spec=grupy.id_spec
inner join students on grupy.id_grup=students.id_grup
where 3 in (select ocenka
from ocenki
where id_stud=students.id_stud)
and pol='ж'
group by nazv_otd;
Запрос 14.
Указания:
Создадим подзапрос, который из таблицы students для конкретной группы (например, id_grup=1) отбирает список троечников
select *
from students
where id_grup=1
and 3 in (select ocenka
from ocenki
where id_stud=students.id_stud);
Используя подзапрос выберем названия групп, которые не имеют троечников. Для отображения названия группы будем использовать поле nazv_grup из таблицы grupy. Значение id_grup каждой группы из главного запроса последовательно подставим в условие подчиненного запроса.
Отбираем группы, для которых не существует студентов с тройками (функция not exists).
select nazv_grup
from grupy
where not exists
(select *
from students
where id_grup=grupy.id_grup
and 3 in (select ocenka
from ocenki
where id_stud=students.id_stud));
Запрос 15.
Указания:
Перепишем предыдущий запрос, но отберем группы, у которых СУЩЕСТВУЮТ троечники (функция exists).
select nazv_grup
from grupy
where exists
(select *
from students
where id_grup=grupy.id_grup
and 3 in (select ocenka
from ocenki
where id_stud=students.id_stud));
Запрос 16.
Указания:
Вначале напишем подзапрос, в котором для отдельной группы (например, id_grup=1) отберем из таблицы students данные студентов с именем Ярослав.
select *
from students
where imya='Ярослав' and id_grup=1;
Для отображения названия группы будем использовать поле nazv_grup из таблицы grupy. Значение id_grup каждой группы из главного запроса последовательно подставим в условие подчиненного запроса.
Отбираем группы, в которых имеются Ярославы (функция exists).
select nazv_grup
from grupy
where exists
(select *
from students
where imya='Ярослав' and id_grup=grupy.id_grup);
Запрос 17.
Указания:
Создадим подзапрос расчета среднего балла по учебному заведению:
select avg(ocenka)
from ocenki
Создадим подзапрос нахождения среднего балла для каждого id_stud в таблице ocenki.
select id_stud,
avg(ocenka) as srbal
from ocenki
group by id_stud
Для решения задачи свяжем таблицу students и второй подзапрос по полю id_stud. При этом в условии отбора where будем использовать значение среднего балла по учебному заведению из первого подзапроса.
select fam,
imya,
srbal
from students inner join (select id_stud,
avg(ocenka) as srbal
from ocenki
group by id_stud) as a
on students.id_stud=a.id_stud
where srbal>(select avg(ocenka)
from ocenki)
order by fam, imya
Запрос 18.
Указания:
Создадим подзапрос расчета среднего балла по конкретной группе (например, id_grup=1):
select avg(ocenka)
from students inner join ocenki
on students.id_stud=ocenki.id_stud
where students.id_grup=1
Используя подзапрос выведем список студентов со средним баллом выше среднего в их группе.
Подзапрос будем использовать в условии отбора where. При этом номер группы из таблицы students главного запроса будет сравниваться с полем id_grup из таблицы students подзапроса. Для устранения неопределенности присвоим этим таблицам имена s1 и s2.
select fam,
imya,
srbal
from students as s1 inner join (select id_stud,
avg(ocenka) as srbal
from ocenki
group by id_stud) as a
on s1.id_stud=a.id_stud
where srbal>(select avg(ocenka)
from students as s2 inner join ocenki
on s2.id_stud=ocenki.id_stud
where s2.id_grup=s1.id_grup);
Скачайте исходную БД в формате MySQL. Выполните все рассмотренные выше запросы в этой СУБД.
Указания: распакуйте архив. В архиве находится файл со скриптом БД.
Запустите утилиту EMS SQL Manager for MySQL. В правой части окна программы выберите команду "Выполнить SQL скрипт". В новом окне выберите команду "Выполнить скрипт из файла". В диалоговом окне укажите распакованный файл скрипта. Если появится окно регистрации, то укажите Пользовать=root, Пароль=111. Если такого окна не появилось, то нажимайте в окне ОК. Появится сообщение, что скрипт выполнен успешно и появится окно для регистрации БД. Перенесите ее в правую часть окна и нажмите ОК. В результате БД будет установлена, зарегистрирована и готова к работе.
В левой части окна можете открыть БД, просмотреть ее таблицы.
Для выполнения запросов в окне выполните команду "Инструменты - Показать редактор SQL". В результате в правой части окна откроется поле для ввода тестов запросов.
Скачайте исходную БД в формате SQLite. Выполните все рассмотренные выше запросы в этой СУБД.
Указания: распакуйте. В архиве находится файл БД.
Запустите утилиту SQLIte Expert Pro. На панели инструментов щелкните на кнопке "Open Database" (вторая слева) и в диалоге открытия файла укажите распакованный файл БД. После подключения должен отобразится значок БД.
В правой части окна перейдите на закладку "SQL". В верхней части вводите текст запроса и нажимайте на кнопку "Execute SQL". Если в запросе нет ошибок, то в нижней части будет отображаться результат отбора.
Замечание. В SQLite для тестовых полей не работает операция "=", только функция like. Следовательно, в запросе № 17 условие по полю imya запишите так:
imya like 'Ярослав%'