Группировка данных
Данные в одной таблице группируются по какому-то полю, значение которого не уникально, обычно являющегося вторичным ключом.
Пример 5.
Найти, сколько студентов в каждой группе
SELECT код_группы, COUNT(*)
FROM студенты
GROUP BY код_группы
Группировка ведется по полю код_группы - все данные с одинаковым значением этого поля группируются и подсчитывается число записей в каждой такой группе.
Отбор групп
Выбирать можно не только записи, но и группы. Для этого используется предложение HAVING. Использовать агрегатные функции в предложение WHERE нельзя.
Пример 6.
Вывести список групп, в которых количество пятерок у студентов больше 6 на группу.
SELECT название, COUNT(*) AS пятерок
FROM группы, студенты, сессия
WHERE группы.код=студенты.код_группы
AND студенты.код=сессия.код_студ
AND сессия.оценка=5
GROUP BY группы.название
HAVING COUNT(*) > 6
Рис. 4.3. Список групп, в которых количество пятерок больше шести
Здесь сначала создается виртуальная таблица с количеством пятерок для каждой группы. После этого из этой таблицы выбираются только те записи которые удовлетворяют условию в предложении HAVING.
Содержание работы
1. Создайте следующие запросы:
Посчитать количество пятерок у студентов группы Гр1
Посчитать, сколько экзаменов должны сдать студенты группы Гр1.
Для каждой кафедры вывести количество работающих на ней преподавателей.
Подсчитать количество часов учебной нагрузки на каждой кафедре
Найти студентов, которые не сдали экзамен по двум и более предметам.
Для каждого преподавателя показать количество студентов не сдавших сессию.
2. Выполните и объясните смысл следующих запросов:
а)
SELECT фамилия, SUM(часов) AS часов
FROM преподаватели INNER JOIN группы_дисциплины ON преподаватели.код = группы_дисциплины.код_преп
GROUP BY фамилия
HAVING SUM(часов) > 200
б)
SELECT название, COUNT(*)
FROM группы INNER JOIN
(студенты INNER JOIN сессия ON студенты.код = сессия.код_студ) ON группы.код = студенты.код_гр
WHERE оценка < > 2 AND NOT оценка IS NULL
GROUP BY название
Содержание отчета
Запишите текст созданных запросов.
Приведите ответы на контрольные вопросы.
Контрольные вопросы
Можно ли не указывать в предложении SELECT поле, по которому производится группировка?
Можно ли указывать условие отбора для групп в предложении WHERE?
Можно ли группировать данные по нескольким полям? Приведите пример.
Лабораторная работа № 5
Подзапросы
Цель работы: Получить представление о способах формирования подзапросов
Основные понятия
SQL позволяет вкладывать запросы друг в друга. Обычно внутренний запрос генерирует значения, которые тестируются на предмет истинности условия внешнего запроса. Подзапрос должен возвращать множество таких объектов, к которым можно применить условие внешнего запроса.
Подзапросы с подмножеством
Пример 1.
Получить ФИО и названия групп студентов - задолжников
SELECT фамилия, имя, отчество, название
FROM студенты INNER JOIN группы ON студенты.код_группы = группы.код
WHERE студенты.код IN ( SELECT код_студ FROM сессия
WHERE оценка = 2 )
Во внутреннем запросе (который и называется подзапросом), строится виртуальная таблица (подмножество), состоящая из одного столбца и включающая в себя коды всех задолжников. Во внешнем запросе поле код каждой записи таблицы Студенты анализируется на принадлежность к этому подмножеству. Если значение поля код из таблицы Студенты принадлежит подмножеству, возвращаемому внутренним запросом, то соответствующая запись из таблицы, полученной путем соединения таблиц Студенты и Группы вносится в результат.
Результат выполнения этого запроса:
Рис. 5.1. Фамилии и названия групп студентов-задолжников