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

11.1 Теоретические положения

Групповые функции SQL. Групповые функции необходимы для определения статистических данных на основе наборов числовых значений:

• Avg — вычисляет среднее арифметическое набора чисел, содержащихся в указанном поле запроса;

• Count — вычисляет число выделенных записей в запросе;

• Min, Max — соответственно возвращают минимальное и максимальное значения из набора в указанном поле запроса;

• StDev, StDevPs — возвращают среднеквадратическое отклонение соответственно генеральной совокупности и выборки для указанного поля в запросе;

• Sum — возвращает сумму значений в заданном поле запроса;

• Var, VarPs — возвращают дисперсию распределения соответственно генеральной совокупности и выборки для указанного поля в запросе.

Для определения полей группирования указывается ключевое слово HAVING для заданного условия по группе при вычислении групповых значений.

Подчиненный запрос. В инструкцию SELECT может быть вло­жена другая инструкция SELECT, SELECT...INTO, INSERT... INTO, DELETE или UPDATE.

Различают основной и подчиненные запросы, которые являются вложенными в основной запрос.

Подчиненный запрос можно использовать вместо выражения в списке полей инструкции SELECT или в предложениях WHERE и HAVING. Существуют три типа подчиненных запросов:

• сравнение (ANY|ALL|SOME) (инструкция);

• выражение [NOT] IN (инструкция); . [NOT] EXISTS (инструкция).

Запрос первого типа служит для сравнения выражения с результатом подчиненного запроса.

Ключевые слова: ANY — каждый; ALL — все; SOME — некоторые.

11.2 Порядок выполнения работы

4. В окне базы данных нажмите кнопку Запросы на панели Объекты, а затем нажмите кнопку Создать на панели инструментов окна базы данных.

5. В диалоговом окне Новый запрос выберите Конструктор и нажмите кнопку OK.

6. Не добавляя таблицы или запросы, нажмите кнопку Закрыть в диалоговом окне Добавление таблицы.

7. В меню Запрос выберите команду Запрос SQL и подкоманду Управление.

Введите инструкцию SQL для управляющего запроса. Каждый управляющий запрос может содержать только одну управляющую инструкцию. Запуск запроса осуществляется нажатием клавиши ! красного цвета на основной панели.

Задание 1. Использование групповых функций SQL

Определите какие исходные таблицы данных и с какими полями нужны для работы, создайте их, пользуясь запросами SQL (описание запросов дано в л.р. № 10)

SELECT Фамилия, Avg(Результат) AS Средний_балл FROM Результаты

GROUP BY [Фамилия];

Задание 2. Использование групповых функций SQL

SELECT [Код дисциплины], Avg(Результат) AS Средний_балл FROM Результаты GROUP BY [Код дисциплины];

Задание 3. Использование групповых функций SQL

SELECT Фамилия, Avg(Результат) AS Средний_балл FROM Результаты

GROUP BY [Фамилия] HAVING Avg(Результат)>4.5;

Задание 4. Использование групповых функций SQL

SELECT [Код дисциплины], Avg(Результат) AS Средний_балл FROM Результаты GROUP BY [Код дисциплины] HAVING Avg(Результат)<5;

Задание 5. Использование подчиненных запросов

SELECT * FROM Оценка WHERE [Результат] > ANY (SELECT [Результат] FROM Оценка WHERE [№ зачетной книжки] = "12345");

Задание 6. Использование подчиненных запросов

SELECT * FROM Студент WHERE [№ зачетной книжки] IN (SELECT [№ зачетной книжки] FROM Оценка WHERE [Результат] >=4);

Задание 6. Использование подчиненных запросов

SELECT * FROM Дисциплина WHERE [Код дисциплины] NOT IN (SELECT [Код дисциплины] FROM оценка);

Задание 6. Использование подчиненных запросов

SELECT * FROM Студент WHERE EXISTS (SELECT * FROM Оценка WHERE Студент.[№ зачетной книжки]= Оценка.[№ зачетной книжки]);