Для Oracle 9i и последующих версий может использоваться решение для DB2. В качестве альтернативы в этих версиях применим собственный синтаксис Oracle для внешнего объединения. Но для пользователей Si и более ранних версий это будет единственно возможное решение:

1 select sno,sname,age

2 from (

3 select s.sno,s.sname,s.age,

4 count(t.cno)

5 over (partition by s.sno) as cnt,

6 count(distinct c.title) over() as total,

Количество предметов, изучаемых студентом, в таблице TAKE должно быть равно общему количеству предметов в таблице COURSES. В таблице COURSES три предмета. Только студент AARON изучает всех их, и он должен быть единственным студентом в результате. Окончательное результирующее множество будет таким:

SNO SNAME AGE 1 AARON 20

MySQL и PostgreSQL

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

1 select s.sno,s.sname,s.age

2 from student s, take t

3 where s.sno = t.sno

4 group by s.sno,s.sname,s.age

5 having count(t.cno) = (select count(*) from courses) DB2 и SQL Server

Используйте оконную функцию COUNT OVER и внешнее объединение вместо подзапроса:

7 row number() over

8 (partition by s.sno order by c.cno) as rn

9 from courses c, take t, student s

10 where c.cno = t.cno (+)

11 and t.sno = s.sno (+)

12 )

13 where cnt = total

14 and rn = 1

Обсуждение

В решении с агрегатной функцией для получения общего числа доступных предметов используется подзапрос. Внешний запрос выбирает только тех студентов, которые изучают такое же количество предметов, как возвратил подзапрос. В решении с оконной функцией применен другой подход: вместо подзапроса используется внешнее объединение с таблицей COURSES. Здесь оконные функции также возвращают количество предметов, изучаемых студентом (под псевдонимом CNT), вместе с общим числом предметов, представленных в таблице COURSES (под псевдонимом TOTAL). Запрос ниже показывает промежуточные результаты, возвращаемые этими оконными функциями:

select s.sno,s.sname,s.age, count(distinct t.cno) over (partition by s.sno) as cnt, count(distinct c.title) over() as total, row number()

over(partition by s.sno order by c.cno) as rn from courses c

left join take t on (c.cno = t.cno) left join student s on (t.sno = s.sno) order by 1

Студент, изучающий все предметы, - это тот, значение CNT которого равно значению TOTAL. Для отсеивания дубликатов из окончательного результирующего множества вместо DISTINCT используется функция ROW NUMBER. Строго говоря, во внешних объединениях таблиц TAKE и STUDENT нет необходимости, поскольку нет таких предме-

тов, которые не изучались бы, по крайней мере, одним студентом. Если бы такой предмет (который не изучает ни один студент) существовал, для него CNT не равнялось бы TOTAL, и в столбцах SNO, SNAME и AGE было бы возвращено значение NULL. В примере ниже создается новый предмет, не изучаемый ни одним студентом. Следующий запрос демонстрирует промежуточный результат, который был бы получен в таком случае (для ясности включен столбец C.TITLE):

insert into courses values (CS115,BIOLOGy,4) select s.sno,s.sname,s.age,c.title,

count(distinct t.cno)

over (partition by s.sno) as cnt,

count(distinct c.title) over() as total,

row number()

over(partition by s.sno order by c.cno) as rn from courses c

left join take t on (c.cno = t.cno) left join student s on (t.sno = s.sno) order by 1

Из этого результата видно, что после применения окончательных фильтров не будет возвращено ни одной строки. Необходимо помнить, что оконные функции обрабатываются после оператора WHERE, поэтому важно при подсчете общего количества доступных предметов в таблице COURSES использовать ключевое слово DISTINCT (в противном случае будет получено общее для результирующего множества, что будет являться общим числом предметов, изучаемых всеми студентами, т. е. select count(cno) from take).

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