Практическое занятие № 4

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

Пусть имеется БД "dbase" в формате SQL Server. Данная БД состоит из 6 таблиц со следующей структурой:

Группировка данных по одной таблице (для повторения прошлой темы)

Запрос 1. Определите количество парней и девушек по всему учебному заведению.

Запрос 2. Определите количество парней и девушек по всему учебному заведению, которые учатся на бюджетной основе.

Запрос 3. Определите сколько имен имеется в таблице и сколько студентов имеют эти имена

Группировка данных по нескольким таблицам

Запрос 4. Для каждой группы подсчитать количество студентов в ней.

Запрос 5. Вывести список групп, у которых количество студентов больше 20 человек.

Запрос 6. Выведите специальность, группу, ФИО студентов (отбор по трем таблицам). Отсортируйте результат по группе и фамилии.

Запрос 7. Выведите фамилию, имя, название предмета и оценку всех студентов (отбор по трем таблицам). Отсортируйте результат по фамилии и предмету.

Запрос 8. Измените предыдущий запрос так, чтобы отобразился список всех студентов, даже тех, у которых нет оценок.

Запрос 9. Выведите ФИО и средний балл каждого студента. Отсортировать по фамилии.

Запрос 10. Измените предыдущий запрос. Выведите ФИО и средний балл каждого студента, включая тех, у кого нет оценок.

Запрос 11. Определите, сколько студентов по техникуму сдали физику на 5 (отбор по трем таблицам).

Запрос 12. Определите средний балл мальчиков и девочек по каждой группе (средний балл считается по оценкам из таблицы ocenki) (отбор по 3 таблицам).

Запрос 13. Определите средний балл мальчиков и девочек по каждой группе, которые учатся на контрактной основе (средний балл считается по оценкам из таблицы ocenki) (отбор по 3 таблицам).

Запрос 14. Определите средний балл студентов, которые учатся на бюджетной основе и по контракту. Округлите результат до двух знаков. (средний балл считается по оценкам из таблицы ocenki) (отбор по 2 таблицам).

Запрос 15. Для каждого отделения определить количество студентов (отбор по 4 таблицам).

Запрос 16. Для каждого отделения определить количество студентов обучающихся на бюджетной и контрактной форме обучения (отбор по 4 таблицам)

Запрос 17. Для каждой группы выведите средний балл по каждому предмету (отбор по 4 таблицам). Отсортировать по группе и предмету.

Запрос 18. Для каждого предмета определите средний балл в каждой группе (отбор по 3 таблицам). Отсортировать по предмету и группе.

Запрос 19. Определите средний балл студентов по каждому предмету (отбор по 2 таблицам).

Запрос 20. Для каждого предмета определите сколько студентов учатся на 4 и выше (отбор по 3 таблицам).

Указания: в правой части окна запроса введите команду

select pol,

count(*) as kol_stud

from students

group by pol;

Для выполнения запроса щелкните на команде Выполнить. Если запрос выполнен верно, то в нижней части окна вы увидите результат его работы.

Запрос 2.

Указания: в правой части окна запроса перейдите на закладку "Редактор" и введите команду

select pol,

count(*) as kol_stud

from students

where finance like 'Госзаказ'

group by pol;

Запрос 3.

Указания: введите команду

select imya,

count(*) as kol_stud

from students

group by imya;

Запрос 4.

Указания: введите команду

select nazv_grup,

count(id_stud) as kol_stud

from grupy inner join students on grupy.id_grup=students.id_grup

group by nazv_grup;

Запрос 5.

Указания: введите команду

select nazv_grup,

count(id_stud) as kol_stud

from grupy inner join students on grupy.id_grup=students.id_grup

group by nazv_grup

having count(id_stud)>20;

Запрос 6.

Указания: введите команду

select nazv_spec, nazv_grup, fam, imya, otch

from spec inner join grupy on spec.id_spec = grupy.id_spec

inner join students on grupy.id_grup = students.id_grup

order by nazv_grup, fam;

Запрос 7.

Указания: введите команду

select fam,

imya,

nazv_predmet,

ocenka

from students inner join ocenki on students.id_stud=ocenki.id_stud

inner join predmety on predmety.id_predmet=ocenki.id_predmet

order by fam, nazv_predmet;

Запрос 8.

Указания: для отображения всех студентов нужно связать таблицы операцией left outer join.

Введите команду

select fam,

imya,

nazv_predmet,

ocenka

from students left outer join ocenki on students.id_stud=ocenki.id_stud

left outer join predmety on predmety.id_predmet=ocenki.id_predmet

order by fam, nazv_predmet;

Запрос 9.

Указания: введите команду

select fam, imya, otch, avg(ocenka) as srbal

from grupy inner join students on grupy.id_grup = students.id_grup

inner join ocenki on students.id_stud=ocenki.id_stud

group by fam, imya, otch

order by fam;

Запрос 10.

Указания: заменим тип связывания таблиц на left outer join:

select fam, imya, otch, avg(ocenka) as srbal

from grupy left outer join students on grupy.id_grup = students.id_grup

left outer join ocenki on students.id_stud=ocenki.id_stud

group by fam, imya, otch

order by fam;

Запрос 11.

Указания: введите команду

select count(students.id_stud) as kol_stud

from students inner join ocenki on students.id_stud=ocenki.id_stud

inner join predmety on predmety.id_predmet=ocenki.id_predmet

where nazv_predmet = 'Физика' and ocenka=5;

Запрос 12.

Указания: введите команду

select nazv_grup,

pol,

avg(ocenka) as srbal

from grupy inner join students on grupy.id_grup=students.id_grup

inner join ocenki on students.id_stud=ocenki.id_stud

group by nazv_grup, pol;

Запрос 13.

Указания: к предыдущему запросу добавляем условие where

select nazv_grup,

pol,

avg(ocenka) as srbal

from grupy inner join students on grupy.id_grup=students.id_grup

inner join ocenki on students.id_stud=ocenki.id_stud

where finance = 'Контракт'

group by nazv_grup, pol;

Запрос 14.

Указания: введите команду

select finance,

round(avg(ocenka),2) as sred_bal

from students inner join ocenki on students.id_stud=ocenki.id_stud

group by finance;

Запрос 15.

Указания: введите команду

select nazv_otd,

count(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

group by nazv_otd;

Запрос 16.

Указания: введите команду

select nazv_otd,

finance,

count(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

group by nazv_otd, finance;

Запрос 17.

Указания: введите команду

select nazv_grup,

nazv_predmet,

round(avg(ocenka),2) as sredbal

from grupy inner join students on grupy.id_grup=students.id_grup

inner join ocenki on students.id_stud=ocenki.id_stud

inner join predmety on predmety.id_predmet=ocenki.id_predmet

group by nazv_grup, nazv_predmet

order by nazv_grup, nazv_predmet;

Запрос 18. Изменим порядок полей в запросе:

Указания: введите команду

select nazv_predmet,

nazv_grup,

round(avg(ocenka),2) as sredbal

from grupy inner join students on grupy.id_grup=students.id_grup

inner join ocenki on students.id_stud=ocenki.id_stud

inner join predmety on predmety.id_predmet=ocenki.id_predmet

group by nazv_predmet, nazv_grup

order by nazv_predmet, nazv_grup;

Запрос 19.

Указания: введите команду

select nazv_predmet,

round(avg(ocenka),2) as sredbal

from predmety inner join ocenki on predmety.id_predmet = ocenki.id_predmet

group by nazv_predmet;

Запрос 20.

Указания: введите команду

select nazv_predmet,

count(students.id_stud) as kol_stud

from students inner join ocenki on students.id_stud=ocenki.id_stud

inner join predmety on predmety.id_predmet = ocenki.id_predmet

where ocenka>=4

group by nazv_predmet;

Главная таблица	Подчиненная таблица	Поле для связи	Тип связи
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 : ∞