/* таблица предметов */ create table courses ( cno varchar(5),

title varchar(10),

credits integer

/* таблица преподавателей */ create table professor ( Iname varchar(10),

dept varchar(10),

salary integer,

age integer

/* таблица студентов и изучаемых ими предметов */ create table take ( sno integer, cno varchar(5)

/* таблица преподавателей и читаемых ими предметов */ create table teach ( lname varchar(10), cno varchar(5)

insert into student values (1,AARON,20) insert into student values (2,CHUCK,21) insert into student values (3,DOUG,20) insert into student values (4,MAGGIE,19) insert into student values (5,STEVE,22) insert into student values (6,JING,18) insert into student values (7,BRIAN,21) insert into student values (8,KAY,20) insert into student values (9,GILLIAN,20) insert into student values (10,CHAD,21)

insert into courses values (CS112,PHYSICS,4) insert into courses values (CS113,CALCULUS,4) insert into courses values (CS114,HISTORY,4)

insert into professor values (CHOI,SCIENCE,400,45)

insert into professor values (GUNN,HISTORY,300,60)

insert into professor values (MAYER,MATH,400,55)

insert into professor values (POMEL,SCIENCE,500,65)

insert into professor values (FEUER,MATH,400,40)

insert into take values (1,CS112)

insert into take values (1,CS113)

insert into take values (1,CS114)

insert into take values (2,CS112)

insert into take values (3,CS112)

insert into take values (3,CS114)

insert into take values (4,CS112)

insert into take values (4,CS113)

insert into take values (5,CS113)

insert into take values (6,CS113)

insert into take values (6,CS114)

insert into teach values (CHOI,CS112)

insert into teach values (CHOI,CS113)

insert into teach values (CHOI,CS114)

insert into teach values (POMEL,CS113)

insert into teach values (MAYER,CS112)

insert into teach values (MAYER,CS114)

Запросы с отрицанием

В своей книге Розенштейн подходит к обучению SQL через рассмотрение различных типов фундаментальных задач, которые часто приходится решать, в том или ином виде. Отрицание - один из таких типов. Часто требуется найти строки, для которых не выполняется некоторое условие. С простыми условиями все понятно, но, как показывают следующие запросы, для решения некоторых задач отрицания требуется применить творческий подход и здравый смысл.

Запрос 1

Требуется найти студентов, которые не изучают курс CS112. Следующий запрос возвращает неверные результаты:

select *

from student where sno in ( select sno from take where cno != CS112 )

Поскольку студент может изучать несколько предметов, этот запрос может (он так и делает) возвратить студентов, изучающих CS112. Запрос неверен, потому что не отвечает на вопрос Кто не изучает CS112? . Он дает ответ на вопрос Кто изучает не CS112? . Требуется получить результирующее множество, включающее студентов, которые не изучают ни один предмет, а также тех, среди изучаемых предметов которых нет CS112. В итоге должно быть получено следующее результирующее множество:

SNO SNAME AGE

5 STEVE 22

6 JING 18

7 BRIAN 21

8 KAY 20

9 GILLIAN 20 10 CHAD 21

Oracle

Для Oracle 9i Database и более поздних версий может использоваться приведенное выше решение для DB2. В качестве альтернативы можно применять собственный синтаксис Oracle для внешнего объединения; для пользователей Oracle 8i Database и более ранних версий - это единственно возможное решение:

/* решение с применением group by */

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 max(case when t.cno = CS112

6 then 1 else 0 end) = 0

/* решение с применением оконной функции */

1 select distinct sno,sname,age

2 from (

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

4 max(case when t.cno = CS112

5 then 1 else 0 end)

7 over(partition by s.sno,s.sname,s.age) as takes CS112

9 from student s, take t

10 where s.sno = t.sno (+)

MySQL и PostgreSQL

С помощью выражения CASE и агрегатной функции MAX установите флаг, если CS112 присутствует в записи студента:

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

2 from student s left join take t

3 on (s.sno = t.sno)

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

5 having max(case when t.cno = CS112

6 then 1 else 0 end) = 0

DB2 и SQL Server

С помощью выражения CASE и агрегатной функции MAX OVER установите флаг, если CS112 присутствует в записи студента: