Запросы с подзапросам

Лабораторная работа 6. Разработка запросов на языке SQL. Часть 3.

Варианты заданий

Содержание отчета

Содержание отчета:

— текст запросов на SQL;

— наборы данных, возвращаемые запросами.

Варианты заданий приведены в ПРИЛОЖЕНИИ.

Сложные операции выборки иногда невозможно выполнить с помощью одного запроса, для решения таких задач в разделах where и having могут использоваться подзапросы. При этом подзапрос, в свою очередь, может содержать подзапросы.

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

select Номер, ФИО

from Группа, Студент, Успеваемость

where Группа = Группа.Id and

Студент.Ном_Зач = Успеваемость.Ном_Зач and

Оценка <> 3

go

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

select Номер, ФИО

from Группа, Студент

where Группа = Группа.Id and

Студент.Ном_Зач not in

(select Студент.Ном_Зач

from Студент, Успеваемость

where Студент.Ном_Зач = Успеваемость.Ном_Зач

and Оценка = 3)

go

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

Теоретически уровень вложенности подзапросов ничем не ограничен, но в конкретных реализациях SQL-серверов ограничения могут накладываться. При выполнении запроса с подзапросами вначале выполняется самый вложенный запрос, затем запрос предыдущего уровня и т.д. вплоть до основного запроса.

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

select Номер, ФИО

from Группа, Студент, Успеваемость

where Группа = Группа.Id and

Студент.Ном_Зач = Успеваемость.Ном_Зач

group by Номер, ФИО, Успеваемость.Ном_Зач

having avg(Оценка) > (select avg(Оценка) from Успеваемость)

order by Номер, ФИО

go

Подзапросы могут использоваться в операторах манипулирования данными: в insert для вставки данных в таблицы из других таблиц; в update и delete для формирования критериев выполнения операций и вычисления новых значений.

Создадим и заполним таблицу Дипл_с_отл данными о студентах, имеющих средний балл выше 4.75:

create table Дипл_с_отл (Группа char(7), ФИО varchar(70), Ном_зач int)

go

insert into Дипл_с_отл (Группа, ФИО, Ном_Зач)

select Номер, ФИО, Студент.Ном_Зач

from Группа, Студент, Успеваемость

where Группа = Группа.Id and

Студент.Ном_Зач = Успеваемость.Ном_Зач

group by Номер, ФИО, Студент.Ном_Зач

having avg(Оценка) >= 4.75

order by Номер, ФИО

go

Удалим из нее записи о студентах, имеющих тройки:

delete from Дипл_с_отл

where Ном_Зач in

(select Студент.Ном_Зач

from Студент, Успеваемость

where Студент.Ном_Зач = Успеваемость.Ном_Зач and Оценка = 3)

go

Добавим столбец Ср_Балл и заполним его:

alter table Дипл_с_отл add Ср_Балл real

go

update Дипл_с_отл

set Ср_Балл =

(select avg(Оценка) from Успеваемость

where Дипл_с_отл.Ном_Зач = Успеваемость.Ном_Зач)

go

Особенности использования подзапросов:

(i) список выбора в подзапросе должен содержать один элемент (за исключением подзапросов в insert into);

(ii) подзапросы могут использоваться в разделах select, from, where, having;

(iii) подзапросы не могут использоваться в разделах order by, group by;

(iv) если подзапрос используется с операцией сравнения, он должен возвращать единственное значение.

Дата добавления: 2015-05-09