Оригинальное решение

В оригинальном решении Розенштейн уходит от агрегатов, дьявольски разумно используя декартово произведение. Приведенный ниже запрос основывается на оригинале:

select *

from student where sno not in ( select s.sno

from student s, courses c where (s.sno,c.cno) not in (select sno,cno from take) )

Розенштейн изменил формулировку задачи: Кого из студентов нет среди тех, для которых существует предмет, которого они не изучают? Если взглянуть на задачу таким образом, получается, что мы имеем дело с отрицанием. Вспомним, как Розенштейн предлагает обрабатывать отрицание:

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

Самый внутренний подзапрос возвращает все действительные сочетания SNO/CNO. Средний подзапрос, который использует декартово произведение таблиц STUDENT и COURSES, возвращает всех студентов и все предметы (т. е. все возможные пары студент-предмет) и отсеивает действительные сочетания SNO/CNO (оставляя только фиктивные сочетания SNO/CNO). Самый внешний запрос возвращает только те строки таблицы STUDENT, значений SNO которых нет среди возвращенных средним подзапросом. Следующие запросы, возможно, внесут ясность в решение. Для удобства здесь участвуют только записи студентов AARON и CHUCK (только AARON изучает все предметы):

select *

from student where sno in ( 1,2 )

SNO SNAME AGE

1 AARON 20

2 CHUCK 21

select *

from take where sno in ( 1,2 )

SNO CNO

1 CS112

1 CS113

1 CS114

2 CS112

select s.sno, c.cno

from student s, courses c where s.sno in ( 1,2 ) order by 1

SNO CNO

1 CS112

1 CS113

1 CS114

2 CS112 2 CS113 2 CS114

Данные запросы возвращают записи студентов AARON и CHUCK из таблицы STUDENT, предметы, изучаемые студентами AARON и CHUCK, и декартово произведение этих студентов и всех предметов соответственно. Декартово произведение для AARON соответствует результирующему множеству, возвращенному для него из таблицы TAKE, тогда как в декартовом произведении для CHUCK появляются две фиктивные строки, которым нет соответствия в таблице TAKE. Следующий запрос представляет средний подзапрос. В нем для выбора действительных сочетаний SNO/CNO используется оператор NOT IN:

select s.sno, c.cno

from student s, courses c where s.sno in ( 1,2 )

and (s.sno,c.cno) not in (select sno,cno from take)

SNO CNO

2 CS113

2 CS114

Обратите внимание, что средний подзапрос не возвращает записи студента AARON (потому что AARON изучает все предметы). Результирующее множество среднего подзапроса содержит строки, поскольку они образованы в результате декартова произведения, а не потому что студент CHUCK на самом деле изучает эти предметы. Самый внешний запрос возвращает из таблицы STUDENT строки, содержащие такие значения в поле SNO, которых нет среди значений SNO, возвращенных средним подзапросом:

select *

from student where sno in ( 1,2 ) and sno not in

(select s.sno from student s, courses c where s.sno in ( 1,2 ) and (s.sno,c.cno) not in (select sno,cno from take))

SNO SNAME AGE 1 AARON 20

Запрос 13

Требуется найти студентов, которые старше любого другого студента.

Эту задачу можно сформулировать иначе: Найти самых старших студентов . Должно быть получено следующее результирующее множество:

SNO SNAME AGE 5 STEVE 22 MySQL и PostgreSQL

Чтобы найти самых старших студентов, используйте в подзапросе агрегатную функцию MAX:

1 select *

2 from student

3 where age = (select max(age) from student) DB2, Oracle и SQL Server

Чтобы найти самых старших студентов, используйте во вложенном запросе оконную функцию MAX OVER:

1 select sno,sname,age

2 from (

3 select s.*,

4 max(s.age)over() as oldest

5 from student s

6 ) x

7 where age = oldest

Обсуждение

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

select s.*,

max(s.age) over() as oldest from student s