Create a relation

I created the following tables and inserted few values.

create table books
(
bookid integer primary key, 
booktitle varchar(20), 
year integer, 
publisherid integer foreign key references publisher(publisherid), 
price integer,
number integer
)

create table publisher
(
publisherid integer primary key,
publishername varchar(20)
)

create table author
(
authorid integer primary key, 
authorname varchar(20)
) 

create table bookauthor
(
bookid integer foreign key references books(bookid),
authorid integer references author(authorid),
earnings integer
 ) 

create table bookreference
(
bookid integer foreign key references books(bookid),
referencebook varchar(20),
times integer
) 

create table revie开发者_运维百科wer
(
reviewerid integer primary key,
reviewername varchar(20)
) 

create table bookreview
(
bookid integer foreign key references books(bookid), 
reviewerid integer foreign key references reviewer(reviewerid),
score integer
) 

Now, I want to solve following query in SQL Server 2000. Find all the books published in 2003 and reviewed by both ‘Sammer Tulpule’ and ‘Hemant Mahta’ . I am not getting any idea about query. How can I write it?

Thanks, Pooja

SELECT b.bookid as bookid
FROM (books b INNER JOIN bookreview br ON (br.bookid=b.bookid)) 
         INNER JOIN reviewer r ON (br.reviewerid = r.reviewerid)
WHERE year=2003 AND r.reviewername IN ('Sammer Tulpule', 'Hemant Mahta')
GROUP BY b.bookid
HAVING COUNT(r.reviewerid) >= 2

Obviously, this is assuming that you don't have duplicated entries in the bookreview table.

(SELECT b.* FROM books b, bookreview br, reviewer rv WHERE b.year = 2003 AND br.bookid = b.bookid and br.reviewerid = rv.reviewerid and rv.reviewername = 'Sammer Tulpule') 
INTERSECT
(SELECT b.* FROM books b, bookreview br, reviewer rv WHERE b.year = 2003 AND br.bookid = b.bookid and br.reviewerid = rv.reviewerid and rv.reviewername = 'Hemant Mahta')

I am typing this on the fly so there might be small errors, but you get the general idea...