[VTU DBMS PGM 4] Author Database (involving the tables 'AUTHOR', 'PUBLISHER', 'CATALOG', 'CATEGORY', 'ORDER_DETAILS')

Question:

The following tables are maintained by a book dealer. 

• AUTHOR (authorid: int, name: string, city: string, country: string)
• PUBLISHER (publisherid: int, name: string, city: string, country: string) 
• CATALOG (bookid: int, title: string, authorid: int, publisherid: int, categoryid: int, year: int, price: int) 
• CATEGORY (categoryid: int, description: string) 
• ORDER_DETAILS (orderno: int, bookid: int, quantity: int) 

Queries:
1. Create the above tables by properly specifying the primary keys and the foreign keys.

CREATE TABLE AUTHOR (
author_id int, 
name varchar(20), 
city varchar(20), 
country varchar(20), 
constraint apk PRIMARY KEY(author_id));

CREATE TABLE PUBLISHER (
publisher_id int,
name varchar(20),
city varchar(20), 
country varchar(20), 
constraint ppk PRIMARY KEY(publisher_id));

CREATE TABLE CATEGORY (
category_id int, 
description varchar(20), 
constraint cpk PRIMARY KEY(category_id));

CREATE TABLE CATALOG (
book_id int, 
title varchar(20), 
author_id int, 
publisher_id int, 
category_id int, 
year int, 
price int, 
constraint cpk PRIMARY KEY(book_id), 
constraint cfk1 FOREIGN KEY(author_id) references AUTHOR(author_id), 
constraint cfk2 FOREIGN KEY(publisher_id) references PUBLISHER(publisher_id), 
constraint cfk3 FOREIGN KEY(category_id) references CATEGORY(category_id)); 

CREATE TABLE ORDER_DETAILS (
order_no int, 
book_id int,
quantity int, 
constraint opk PRIMARY KEY(order_no, book_id),
constraint ofk FOREIGN KEY(book_id) references CATALOG(book_id));

2. Enter at least five tuples for each relation.

INSERT INTO AUTHOR
VALUES('&author_id','&name','&city','&country');

INSERT INTO PUBLISHER VALUES('&publisher_id','&name','&city','&country');

INSERT INTO CATEGORY
VALUES('&category_id','&description');

INSERT INTO CATALOG
VALUES('&book_id','&title','&author_id','&publisher_id','&category_id','&year','&price');

INSERT INTO CATALOG VALUES('&book_id','&title','&author_id', '&publisher_id','&category_id','&year','&price');


3. Give the details of the authors who have 2 or more books in the catalog and the price of the books is greater than the average price of the books in the catalog and the year of publication is after 2000. 

SELECT *
FROM AUTHOR a 
WHERE a. author_id IN 
(SELECT c. author_id 
FROM CATALOG 
WHERE c. year > 2000 AND 
c.price > (SELECT AVG(price) FROM CATALOG) 
GROUP BY c.author_id 
having count(c.authorid)>=2);

4. Find the author of the book which has maximum sales. 

create view new as (
select sum(o.quantity) as "SUM", o.book_id 
from order_details o 
group by o.book_id);

select a.aname 
from author a,catalog c,new n 
where n.book_id=c.book_id and 
a.author_id=c.author_id and 

n.sum in(select max(sum) from new);

5. Demonstrate how you increase the price of books published by a specific publisher by 10%. 

UPDATE CATALOG 
SET price = price*1.1 
WHERE Publisher_id IN 
( SELECT Publisher_id 
 FROM PUBLISHER 

WHERE name='shiva');

6. Generate suitable reports. 

ttitle 'STOCK DETAILS' 
btitle 'BOOK DEALER DATABASE' 
column book_id heading 'BOOK_ID' 
column title heading 'BOOK_NAME'
column name heading 'AUTHOR_NAME' 
column publisher_id heading 'PUBLISHER_NAME' 
column price heading 'PRICE'
break on name skip 2 
compute count of book_id on name 
set linesize 100 
set pagesize 40 

select c.book_id, c.title, a.name, p.name, c.price 
from author a, publisher p, catalog c 
where a.author_id=c.author_id and 
p.publisher_id=c.publisher_id 
order by p.name; 

btitle off 
ttitle off 
clear breaks 

clear columns

Author : Unknown

He is a simple passionate tech freak who himself is an engineering student at Canara Engineering college. He likes App Development, Web designing, Blogging, Youtubing, Debugging and also is a CodeGeek!

Sharing is sexy!

Related Articles