Create a Relation

• connect.pooja28

  SSC Rookie

  Points: 29

  More actions

  March 16, 2011 at 11:20 pm

  #234313

  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 reviewer(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

• a.rajmane

  Old Hand

  Points: 377

  More actions

  March 17, 2011 at 12:40 am

  #1299800

  Just join the tables as per the relations and apply filter for reviewers.

  www.sqlsuperfast.com