Question

Consider the following database schema for a BOOKSTORE database:

• Books (bookid, title, author, year)
• Customers (customerid, name, email)
• Purchases (customerid, bookid, year)
• Reviews (customerid, bookid, rating)
• Pricing (bookid, format, price)

The Books relation stores information about books sold by the bookstore. Note that bookid is the primary key. An example tuple is as follows:

(105, 'JAVA PROGRAMMING', 'JOHN DOE', 2001)

The Customers relation stores information about the customers of the bookstore. Note that customerid is the primary key. An example tuple is as follows:

(210, 'JOHN SMITH', 'SMITH@YAHOO.COM')

The Purchases relation stores information about the customer purchases of books. Note that customerid and bookid form the primary key. An example tuple is as follows:

(210, 105, 2002), indicating that 'JOHN SMITH' with customerid 210 purchased the 'JAVA PROGRAMMING' book with bookid 105 in the year 2002.

The Reviews relation stores information about the customer reviews/ratings of the books. The ratings field refers to the number of "stars" given to the book. Note that customerid and bookid form the primary key. An example tuple is as follows:

(210, 105, 3), indicating that 'JOHN SMITH' with customerid 210 gave a 3-star rating to the 'JAVA PROGRAMMING' book with bookid 105.

The Pricing relation stores information about the price of the various books sold by the bookstore. Note that the same book can be available in multiple formats at possibly different prices. The price field refers to the number of dollars. For instance a $25 purchase will have a price field of 25. Note that the bookid and format fields form the primary key for the relation. An example tuple is as follows:

(105, 'AUDIO', 25), indicating that the 'JAVA PROGRAMMING' book with bookid 105 is available in the audio format for $25.

Given the above schema, write queries for the following:

1. Find books (show their titles) written by 'EDMUND MORGAN' since year 1990.
2. Find books (show their titles, authors and prices) that are on 'CIVIL WAR' (i.e., the title field contains 'CIVIL WAR'), available in 'AUDIO' format.
3. For each year, 'JOHN CHAMBERS' purchased at least one book, find the number of books purchased. That is, the output should be a set of tuples, each indicating a year and the number of books purchased by 'JOHN CHAMBERS' in that year.
4. Find customers (show their names and email addresses) who purchased more than one book in year 2003.
5. Find the ratings information (show titles, authors and average ratings) for books on 'CIVIL WAR' (i.e., title contains 'CIVIL WAR').

Answer 1

Table

Drop table Books;
Drop table Customers;
Drop table Purchases;
Drop table Reviews;
Drop table Pricing;
Create table Books(bookid number(4) primary key, title varchar(20), author varchar(20), year number(4));
desc Books;
insert into Books values(105, 'JAVA PROGRAMMING', 'JOHN DOE', 2001);
Select * from Books;
Create table Customers (customerid number(4) primary Key, name varchar(20), email varchar(25));
desc Customers;
insert into Customers values (210, 'JOHN SMITH', 'SMITH@YAHOO.COM');
Select * from Customers;
create table Purchases (customerid number(4) references Customers(customerid), bookid number(4) references Books(bookid), year number(4));
desc Purchases;
insert into Purchases values (210, 105, 2002);
create table Reviews (customerid number(4) references Customers(customerid), bookid number(4) references Books(bookid), rating number(1));
desc Reviews;
insert into Reviews values (210, 105, 3);
Select * from Reviews;
create table Pricing (bookid number(4) references Books(bookid), format varchar(25), price number(10,3));
desc Pricing;
insert into Pricing values(105, 'AUDIO', 25);
Select * from Pricing;

Query:

select title from Books where author='EDMUND MORGAN' and year=1990;

Select Books.title,Books.author,Pricing.price from Books inner join Pricing on Books.bookid=Pricing.bookid where format='AUDIO';

Select year,count(*)"Total Order"from Customers inner join Purchases on Purchases.customerid=Customers.customerid where name= 'JOHN CHAMBERS' group by Purchases.customerid,year;

Select name,email from Customers where customerid in(Select Purchases.customerid from Customers inner join Purchases on Purchases.customerid=Customers.customerid where year=2003 group by Purchases.customerid having count(*)>1);

Select Books.title,Books.author,avg(Reviews.rating) from Books inner join Reviews on Books.bookid=Reviews.bookid where title='CIVIL WAR' group by Books.title,Books.author;

if you still have any Problem regarding this question please comment and if you like my code please appreciate me by thumbs up thank you.........