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

Recommended Answers

All 8 Replies

Why you need to create the following tasbles ?

1. bookauthor
2. bookreview
3. bookreference

why not have reference in the book table itself.

actually, i have 4 tables which i normalised into above tables?

but i think you are making the design complex quiet unnecessarily.

@debasisdas, Actually..........the basic tables are as follows...
BookAuthor(book, authors, earnings)
BookReference(book, referenceBook, times)
BookReview(Book, reviewer, score)
BookPublish(book, year, publisher, price, num)
These tables I normalized into that tables which i stated in the query. What should be the table normalization according to you?

I suggest to go with this approach.





Hi, please read following info. about tables and then reply with your normalized tables.

In this database, each book may have one or more authors and each author may make different amount of money from that book. One book may make reference to other books. One book may be reviewed by different reviewers and get difference scores. An author could also be a reviewer and a publisher.

Oh dear you should have passed all these info on day one.

Should have posted 14 days back itself.

I will look into the same once i am free.

@debasisdas, YAH......plz look it. i m very new to MS SQL. i studied it, but fail to solve queries

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.