select p.* from professor p, teach t where p.lname = t.lname and p.lname not in ( select t1.lname from teach t1, teach t2 where t1.lname = t2.lname and t1.cno > t2.cno

LNAME DEPT SALARY AGE

POMEL SCIENCE 500 65

Запрос 10

Требуется найти студентов, которые изучают только CS112 и CS114 (именно эти два предмета и никакие другие), но следующий запрос возвращает пустое результирующее множество:

select s.*

from student s, take t where s.sno = t.sno and t.cno = CS112 and t.cno = CS114

Ни в одной строке не может быть поля с двумя значениями одновременно (предполагаем, что рассматриваются простые скалярные типы данных, такие как используются в таблице STUDENT), поэтому этот запрос никак не может обеспечить правильный результат. В своей книге Розенштейн рассуждает о том, как написание запросов в лоб может привести к подобным ошибкам. DOUG - единственный студент, который изучает только CS112 и CS114, и только он должен быть возвращен для этой задачи.

MySQL и PostgreSQL

Чтобы найти студентов, которые изучают только CS112 и CS114, используйте выражение CASE и агрегатную функцию 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(*) = 2

6 and max(case when cno = CS112 then 1 else 0 end) +

7 max(case when cno = CS114 then 1 else 0 end) = 2

DB2, Oracle и SQL Server

Чтобы найти студентов, которые изучают только CS112 и CS114, используйте оконную функцию COUNT OVER в сочетании с выражением CASE:

1 select sno,sname,age

2 from (

3 select s.sno,

4 s.sname,

5 s.age,

6 count(*) over (partition by s.sno) as cnt,

7 sum(case when t.cno in ( CS112, CS114 )

8 then 1 else 0

9 end)

10 over (partition by s.sno) as both,

11 row number()

12 over (partition by s.sno order by s.sno) as rn 14

15 16

Обсуждение

В решении с агрегатной функцией используется тот же подход, что и в Запросе 1 и Запросе 2 . Внутреннее объединение таблицы STUDENT с таблицей TAKE обеспечивает исключение всех студентов, не изучающих ни одного предмета. По выражению COUNT в конструкции HAVING выбираются только студенты, изучающие именно два предмета. Посредством суммирования результатов выражений CASE определяется, сколько из заданных предметов изучает студент. Эта сумма равна 2 только для студентов, изучающих оба предмета, CS112 и CS114.

В решении с оконной функцией используется техника, аналогичная технике в Запросе 1 и Запросе 2 . Данная версия немного отличается: здесь значение выражения CASE возвращается в оконную функцию SUM OVER, и кроме того используется ранжирующая функция ROW NUMBER, чтобы избежать применения ключевого слова DISTINCT. Результаты этого решения без завершающих фильтров показаны ниже:

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

count(*) over (partition by s.sno) as cnt, sum(case when t.cno in ( CS112, CS114 ) then 1 else 0

end)

over (partition by s.sno) as both, row number()

over (partition by s.sno order by s.sno) as rn from student s, take t

Проанализировав результаты, можно увидеть, что окончательное результирующее множество составляют записи, в которых поля BOTH и CNT равны 2. RN может быть равным или 1, или 2, это не важно; этот столбец существует только для того, чтобы можно было отфильтровать дубликаты, не применяя DISTINCT.

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

В данном решении поиск студентов, изучающих не менее трех предметов, ведется с помощью подзапроса с множеством рефлексивных объединений. Следующий шаг - использовать рефлексивное объединение таблицы TAKE, чтобы найти студентов, изучающих оба предмета, CS112 и CS114. Заключительный шаг - выбрать только тех студентов, которые изучают оба предмета, CS112 и CS114, и не изучают три или более предметов. Решение показано ниже: