Запросы с подзапросами. Функции определяемые пользователем. Курсоры: Лабораторная работа № 6 по курсу «Информационные технологии в менеджменте»

Тема. Запросы с подзапросами. Функции определяемые пользователем. Курсоры.

Многотабличные запросы на основе подзапросов. 1

Запросы с подзапросами. Ошибка! Закладка не определена.

Несвязанные подзапросы.. 1

Связанные подзапросы.. 2

Пользовательские функции. 3

Создание курсора для приложения. 4

Многотабличные запросы на основе подзапросов

Запросы на выборку

Несвязанные подзапросы

Оператор in

Запрос Выведите названия дисциплин по которым на данный момент имеются оценки у учеников 9 классе.

Рассуждения

1)  сначала сформируем список кодов учеников 9 класса

2)  определим список дисциплин, по которым эти ученики получили оценки

3)  и наконец создадим список названий тех дисциплин , которые отобраны во втором запросе

4)  соединим запросы оператором in в разделе where.

SELECT name

FROM  Desceplene

where idDisciplene in

(SELECT iD

FROM  Advencement

where id in

(SELECT id

FROM  piple

WHERE (Klass = 9)) )

Запрос. Какие дисциплины преподаются в 9 классе.

Запрос. По каким дисциплинам ученики всего класса не имеют оценок.

Можно ли на нашей базе получить ответ на эти запросы?????.

Использование оператора ALL

Запрос. Кто из учеников имеет минимальное количество оценок

Рассуждения.

1)  Сформируем множество идентификаторов учеников, у которых количество оценок меньше каждого значения этого множества (меньше всех значений).

2)  Затем проверим, чьи идентификаторы включены в результирующее множество.

·  Создайте запрос на формирование множества из количества оценок каждого ученика по таблице успеваемости. Создание запроса выполните в дизайнере запросов.

·  Создайте запрос на выборку с группировкой по id учеников. Для подключения раздела группировки по столбцу id сначала выделите его в бланке запроса, затем вызовите контекстное меню в области отображения таблиц и выполните команду Add Group By.(отменить группировку также).

·  Подсчитайте количество оценок каждого ученика. Для этого добавьте в бланк запроса столбец оценки. Примените к нему агрегатную функцию count следующим образом: щелкните в строке оценки, в ячейке столбца группировки; и в  списке агрегатных функций выберите функцию count. Нажмите на Enter. Просмотрите строку SQL кода. Выполните команду ОК.

·  В окне редактора запроса щелкните перед командой select и создайте дизайнером запрос на формирование списка id учеников, количество оценок которых меньше всех. Для этого включите в запрос группировку по id  подсчет количества оценок. Затем добавьте условие отбора из группы тех строк в которых количество оценок меньше всех остальных. Для этого в ячейке условия строки оценки введите оператор отбора: <=ALL. Нажмите Enter. Просмотрите SQL выражение. Выполните ОК.

·  Заключите подзапрос в круглые скобки.

·  Создайте в конструкторе запросов главный запрос для отбора фамилий из таблицы piple, id которых включено в множество предыдущего подзапроса. Для выбора данных из множества, сформированного подзапросом используйте оператор in.

SELECT id

FROM  piple

where id in

(SELECT id

FROM  Advencement

GROUP BY id

Having COUNT(id)<=All

(SELECT COUNT(id)

FROM  Advencement

GROUP BY id

))

Самостоятельное задание. Можно ли этот запрос решить на основе соединения.

Использование оператора ANY(SOME).

Запрос. Сформировать список учеников, у которых по

Связанные подзапросы

Использование оператора Exists.

Запрос. Кто из учеников получил хотя бы одну оценку за указанный период . Рассуждения.

Перефразируем запрос: имеется ли хотя бы одна строка в таблице успеваемости за указанный период, содержащая идентификатор этого ученика.

1)  Сначала сформируем подзапрос на формирование множества из id конкретного ученика за определенный период

2)  Сформируем запрос по ученику и проверим, какое множество сформировал наш подзапрос – пустое или нет.

Для этого используем оператор Exists.

·  Сначала создайте подзапрос на формирование множества из id конкретного ученика за определенный период при помощи дизайнера запросов. Используйте  оператор BETWEEN. Введите условие отбора в столбец условия строки data.

·  Создайте запрос с подзапросом уже в окне редактора запросов

·  Выполните запрос

·  Сохраните запрос.

Select name

from piple

where  Exists

(SELECT Advencement.id

FROM  Advencement

WHERE (Advencement.id = piple.id) AND

Data BETWEEN CONVERT(DATETIME, '2007-01-11 00:00:00', 102)

AND CONVERT(DATETIME, '2007-05-11 00:00:00', 102))

Самостоятельное задание.

Запрос. Кто из учеников не получал оценок за период.

Примечание. Этот запрос уже был решен при помощи соединения.