Запрос 8

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

Возможно, удобнее будет перефразировать задачу так: Найти студентов, которые старше двух или более студентов . Здесь подойдет техника, аналогичная применяемой в Запросе 5 . Окончательное результирующее множество показано ниже (только студенты JING и MAGGIE не старше двух или более студентов):

MySQL и PostgreSQL

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

1 select s1..

2 from student s1

3 where 2 <= ( select count(*)

4 from student s2

5 where s2.age < s1.age )

DB2, Oracle и SQL Server

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

1 select sno,sname,age

2 from (

3 select sno,sname,age,

4 dense rank()over(order by age) as dr

5 from student

6 ) x

7 where dr >= 3

Обсуждение

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

Запросы с условием точно

Может показаться, что решать вопросы истинности чего бы то ни было просто. Во многих случаях так оно и есть. Но иногда ответ на вопрос об абсолютной истинности, особенно если это связано с объединением данных из связанных таблиц, может вызывать затруднения. Проблема произрастает из исключающей природы условия точно . Возможно, удобнее будет рассматривать его как условие только . К примеру, скажем, чем отличаются люди, которые носят туфли, и которые носят только туфли? Недостаточно просто удовлетворить условию; условие должно быть выполнено с гарантией того, что ни одно из других условий не выполняется.

Запрос 9

Найти преподавателей, которые читают именно один предмет.

Эту задачу можно сформулировать иначе: Найти преподавателей, которые читают только один предмет . Какой конкретно предмет - не важно, важно то, что только один предмет. Окончательное результирующее множество должно выглядеть следующим образом:

LNAME DEPT SALARY AGE

POMEL SCIENCE 500 65

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

Эта задача является вариантом Запроса 6 , разница в том, что теперь мы работаем только с таблицей STUDENT. Решение Запроса 6 можно легко адаптировать для поиска студентов, которые старше, по крайней мере, двух других студентов , что и показано ниже:

select distinct s1.* from student s1, student s2, student s3 where s1.age > s2.age

MySQL и PostgreSQL

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

1 select p.lname,p.dept,p.salary,p.age

2 from professor p, teach t

3 where p.lname = t.lname

4 group by p.lname,p.dept,p.salary,p.age

5 having count(*) = 1

DB2, Oracle и SQL Server

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

1 select lname, dept, salary, age

2 from (

3 select p.lname,p.dept,p.salary,p.age,

4 count(*) over (partition by p.lname) as cnt

5 from professor p, teach t

6 where p.lname = t.lname

7 ) x

8 where cnt = 1

Обсуждение

Внутренним объединением таблицы PROFESSOR с таблицей TEACH мы гарантируем, что из рассмотрения исключаются все преподаватели, не читающие ни одного курса. В решении с агрегацией функция COUNT в конструкции HAVING возвращает преподавателей, читающих только один предмет. Во втором решении используется оконная функция COUNT OVER. Но обратите внимание, что в операторе PARTITION функции COUNT OVER этого решения и в операторе GROUP BY решения с агрегацией указаны разные столбцы таблицы PROFESSOR. В данном примере операторы GROUP BY и PARTITION BY могут отличаться, поскольку все фамилии в таблице TEACHER разные, т. е. исключение полей P.DEPT, P.SALARY и P.AGE из сегмента не влияет на операцию подсчета. В предыдущих решениях я намеренно использовал в операторе PARTITION решения с оконной функцией те же столбцы, что и в операторе GROUP BY решения с агрегацией, чтобы показать, что PARTITION является скользящей, более гибкой разновидностью GROUP BY.

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

В этом решении используется та же техника, что и в Запросе 3 : ответ дается в два этапа. Первый шаг - выбрать преподавателей, которые читают два или более предмета. Второй шаг - найти преподавателей, которые читают какой-то предмет, но которых нет среди выбранных в шаге 1. Полное обсуждение приведено в Запросе 3 . Решение показано ниже: