Наиболее часто встречающиеся ошибки при выполнении group BY

Если вы сделаете попытку указать в предложении GROUP BY атрибут, которого нет в списке SELECT, то выдается сообщение об ошибке.

Пример 65

Задание.

Вывести названия всех дисциплин, по которым средняя оценка меньше 4.

Решение:

SELECT NameSubject, ROUND(AVG(Mark),1)

FROM Progress P,Subject S

WHERE P.IDSubject=S.IDSubject

GROUP BY P.IDSubject

HAVING AVG(Mark)<4

Результат реализации запроса:

Server: Msg 8120

Column 'S.NameSubject' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Другая часто встречающаяся ошибка связана с тем, что в предложении SELECT указываются атрибуты, которые не являются параметрами группировки или агрегатной функции.

Пример 66

SELECT NameSubject, P.IDSubject, ROUND(AVG(Mark),1)

FROM Progress P,Subject S

WHERE P.IDSubject=S.IDSubject

GROUP BY P.IDSubject

HAVING AVG(Mark)<4

Результат реализации запроса:

Server: Msg 8120

Column 'S.NameSubject' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Выходом из положения может быть группировка по атрибуту NameSubject.

Предложение ORDER BY

Предложение ORDER BY позволяет выполнить сортировку результатов запроса, оно всегда ставится в конец команды SELECT. По умолчанию сортировка идет по возрастанию. Для изменения порядка сортировки используются опции ASC – возрастание или DESC – убывание. При сортировке по нескольким столбцам столбцы перечисляются через запятую.

Пример 67

Задача.

Вывести список имен студентов по возрастанию.

Решение.

SELECT StName ФИО

FROM Student

ORDER BY StName;

Результат реализации запроса:

Пример 68

Задача.

Вывести список имен и оценок студентов, расположив по возрастанию имена студентов, а также расположив для каждого студента его оценки по возрастанию.

Решение.

SELECT DISTINCT StName ФИО, Mark Оценка

FROM Student, Progress

WHERE Student.NRecordBook= Progress.NRecordBook

ORDER BY StName, Mark;

Результат реализации запроса:

Задание 17

Вывести имена и номера зачеток всех студентов, у которых нет ни одной оценки 3.

Задание 18

Вывести имена всех преподавателей, которые ведут дисциплину Базы данных.

Задание 19

Вывести все дисциплины, по которым сдается экзамен. Название дисциплин расположить в порядке возрастания.

Задание 20

Вывести имена преподавателей и название дисциплин, которые они ведут, если по этим дисциплинам выставлена хоть одна оценка.

Задание 21

Вывести имена преподавателей которые преподают как в 5 так и в 6 семестрах.

Вопросы для самоконтроля к лабораторной работе № 4

1. Какие предложения в команде SELECT являются обязательными?

2. Какое из предложений используется для исключения групп строк из результата запроса?

3. C помощью какого предложения создаются группы?

4. В каком порядке в команде SELECT должны располагаться предложения ORDER BY, GROUP BY, WHERE?

5. В чем заключается различие между предложениями WHERE и HAVING?

6. Всегда ли запрос, построенный с использованием предложений HAVING может быть преобразован в эквивалентный запрос без использования этого предложения?

7. Какие предложения позволяют ограничить количество выводимых строк?

8. В чем отличие простого запроса от сложного?

9. С помощью каких ключевых слов в команде SELECT осуществляется проверка вхождения результата вычисления выражения в заданное множество?

10. Какая функция используется для подсчета кортежей, отвечающих заданному условию?

11. Какой вид будет иметь команда SELECT, реализующая операцию проекции на атрибуты AB в отношении со схемой R={ABCD}?

12. Какое из предложений команды SELECT накладывает ограничение на отбор групп?

13. Как следует сформировать запрос к таблице R1={AB} и R2={CD}, чтобы получить их декартово произведение?

14. Возможно ли используя команду SELECT реализовать операцию деления?

15. В каких случаях нельзя для осуществления операции выбора использовать предложение WHERE?

Лабораторная работа № 5

Цель занятия: Изучить классификацию подзапросов и правила их формирования.

Подзапросы

Подзапрос – это команда SELECT, вложенная в предложение другой команды SQL. Подзапросы могут использоваться в командах SELECT, UPDATE, INSERT, DELETE, CREATE TABLE. Например, каждая команда SELECT может включать в себя несколько других команд SELECT^[6]. При этом подзапрос (внутренний запрос) генерирует значение, которое проверяется в предикате внешнего запроса. Подзапросы всегда выполняются от внутренних к внешнему, если только не являются коррелированными. Подзапрос может возвращать одну и более строк или один и более столбцов.

1. Подзапрос помещается в круглые скобки и должен стоять в правой части оператора сравнения внешнего запроса.

2. Подзапрос может обращаться к таблицам отличным от тех, к которым обращается основной запрос.

3. Подзапрос может задаваться в сложных критериях поиска внешних запросов с использованием логических связок AND и OR.

4. Предложение ORDER BY ставится последним в основном запросе и не может содержаться в подзапросе.

5. В команде SELECT подзапрос может стоять в предложениях FROM, WHERE, HAVING.

6. Подзапрос может содержать группы и групповые функции.

7. Имена столбцов в предложении SELECT внутреннего запроса должны стоять в той же последовательности, что и имена столбцов в левой части оператора сравнения внешнего запроса. Типы столбцов должны попарно соответствовать.

8. В критерии поиска могут использоваться логические операторы, операторы ANY (SOME), ALL.

Подзапрос на уровне предложения WHERE

Пример 69

Задача.

Вывести имена студентов и их оценки, если оценка меньше средней по университету.

Решение.

SELECT StName, Mark

FROM Student S,Progress P

WHERE S.NRecordBook=P. NRecordBook

AND Mark<(SELECT AVG(Mark)

FROM Progress)

Результат реализации запроса:

Подзапрос вычисляет среднюю оценку и подставляет высчитанное значение в предложение WHERE внешнего запроса.

Коррелированные подзапросы на уровне предложения WHERE

Коррелированные подзапросы – это вложенные подзапросы. Они выполняются для каждой строки главного запроса.

Последовательность выполнения коррелированного подзапроса:

· внешний запрос выбирает строку;

· выполняется внутренний запрос, используя значение строки внешнего запроса;

· результат выполнения внутреннего запроса возвращается во внешний запрос, где проверяется его соответствие выбранной строке;

· выбирается следующая строка внешнего запроса.

При задании вложенных запросов допускаются применение операторов АNY, EXISTS, ALL и логических операторов.

Пример 70

Задача.

Вывести имена студентов, чьи оценки выше, чем средняя оценка в их группе.

Решение:

SELECT StName,Mark

FROM Student s,Progress p

WHERE S.NRecordBook=P.NRecordBook

AND Mark>(SELECT AVG(Mark)

FROM Progress P1,Student S1

WHERE S1.IDGroup=S.IDGroup

AND S1.NRecordBook=P1.NRecordBook)

Результат реализации запроса:

 

 

Рисунок 3

Из примера становится очевидным, что коррелированные запросы следует применять только в случае крайней необходимости, так как производительность их мала. Например, при реализации запроса средняя оценка по группе вычисляется столько раз, сколько раз информация о группе встречается в первом запросе. Далее (см. Пример 75) приведен пример использования коррелированного подзапроса в команде UPDATE.

Задание 22

Вывести имя студента, название предмета и оценку студентов для тех студентов, у которых оценка по той или иной дисциплине выше средней оценки по этой самой дисциплине.

Задание 23

Вывести имя студента, название предмета и оценку студентов для тех студентов, у которых оценка по той или иной дисциплине выше средней оценки по группе, в которой они обучаются.

Задание 24

Вывести имена студентов, у которых средняя оценка равна средней оценке по группе, в которой учится студент.

Подзапрос на уровне предложения HAVING

Пример 71

Задача.

Вывести имена студентов, у которых средняя оценка, выше средней по университету.

Решение.

Было бы ошибочно использовать следующий синтаксис команды. Выше уже говорилось о том, что отбор групп по условию возможен только в предложении HAVING.

SELECT StName, AVG(Mark)

FROM Student s,Progress p

WHERE s.NRecordBook =p. NRecordBook

AND AVG(Mark) >(SELECT AVG(Mark)

FROM Progress)

GROUP BY StName

Результат реализации запроса:

Server: Msg 147

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Корректный синтаксис команды будет следующим:

SELECT StName, AVG(Mark)

FROM Student s,Progress p

WHERE s.NRecordBook =p. NRecordBook

GROUP BY StName

HAVING AVG(Mark) >(SELECT AVG(Mark)

FROM Progress)

Результат реализации запроса:

Наибольшее затруднение, как ни странно, вызывают функции по работе с датами, поэтому ниже мы приводим наиболее употребимые функции (см. Приложение 4. Функции обработки дат. и Приложение 5. Допустимые значения параметра частьДаты).

Пример 72

Задача.

Вывести год, в котором было принято на работу наибольшее число сотрудников.

Решение:

select year(DateHire)

from Teacher

group by year (DateHire)

having count(year (DateHire))=(select max(d.aCount)

from (select count (year (DateHire)) aCount

from Teacher

group by(year (DateHire))) d)

Результат реализации запроса:

 

Подзапрос на уровне предложения FROM

В ряде случаев нам надо сравнить результаты агрегирования строк. Выше был приведен пример коррелированного запроса, осуществляющего вывод имен студентов, которые имели оценки выше, чем средняя оценка по той группе, в которой они учатся (см. Пример 70). Применение подзапроса на уровне FROM позволяет значительно упростить реализацию этого запроса (см. Пример 73).

Пример 73

Задача.

Вывести имена студентов, чьи оценки выше, чем средняя оценка в их группе.

Решение:

SELECT S.StName,S.NRecordBook

FROM Progress P

INNER JOIN Student S

on P.NRecordBook=S.NRecordBook

INNER JOIN

(SELECT IDGroup,AVG(Mark) BMark

FROM Progress P

INNER JOIN Student S

on P.NRecordBook=S.NRecordBook

GROUP BY IDGroup) b

ON S.IDGroup=b.IDGroup

WHERE mark>BMark

Результат реализации запроса:

Пример 74

Задача.

Вывести имена студентов, чьи средние оценки равны средней оценке в их группе.

Решение:

SELECT a.StName,a.NRecordBook,Amark,Bmark

FROM (SELECT p.NRecordBook,StName,IDGroup,AVG(Mark) Amark

FROM Progress P

INNER JOIN Student s

ON P.NRecordBook=S.NRecordBook