Solutions on book_shop Database - Request to all the user to suggest for the corrections and optimization

create database bk_shop

create table author
(
authorid int primary key,
aname  varchar(20),
city varchar(20),
country varchar(20)

)

insert into author values(111,'sebesta','mangalore','India')
insert into author values(112,'Elmasri','Houston','Canada')
insert into author values(113,'Bharath K','Bangalore','India')
insert into author values(114,'Willy Z','California','USA')
insert into author values(115,'Salma','Dakha','Bangla')



create table publisher
(
pubid int primary key,
pname  varchar(20),
city varchar(20),
country varchar(20)
)


insert into publisher values(201,'McGRAW','mangalore','India')
insert into publisher values(202,'Pearson','Bangalore','India')
insert into publisher values(203,'GKP','Bangalore','India')
insert into publisher values(204,'MediTech','Delhi','India')
insert into publisher values(205,'Sun','Ahmadbad','India')



create table catagory
(
catid int primary key ,
descript varchar(30),

)


insert into catagory values(1,'All children Books')
insert into catagory values(2,'Cooking Books')
insert into catagory values(3,'Popular Novels')
insert into catagory values(4,'Samll Story Books')
insert into catagory values(5,'Medicla Books')



create table catalog
(
bookid int primary key,
title  varchar(20),
pubid int,
authorid int,
catid int,
yr int,
price int,
foreign key(pubid) references publisher(pubid) on delete cascade on update cascade,
foreign key(authorid) references author(authorid) on delete cascade on update cascade,
foreign key(catid) references catagory(catid) on delete cascade on update cascade,
)


select * from publisher

insert into catalog values(301,'Panchatantra',201,111,1,2000,300)
insert into catalog values(302,'Vegetables',202,111,2,2000,400)
insert into catalog values(303,'Yogasana',203,112,5,2002,600)
insert into catalog values(304,'Stories of Village',204,113,4,2005,100)
insert into catalog values(305,'Triangle',205,114,3,2008,1000)

select * from catalog

create table order_det
(
ordno int ,
bookid int,
qty int,
primary key (ordno,bookid),
foreign key(bookid) references catalog(bookid) on delete cascade on update cascade,
)


insert into order_det values(1,301,10)
insert into order_det values(1,302,6)
insert into order_det values(1,303,23)

insert into order_det values(2,301,15)
insert into order_det values(2,304,11)

insert into order_det values(3,304,15)

insert into order_det values(4,301,3)
insert into order_det values(4,305,8)

insert into order_det values(5,303,20)
insert into order_det values(5,304,6)
insert into order_det values(5,305,7)

select * from order_det

1. 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 A.authorid,A.aname,A.city from author A, catalog C
where A.authorid  =  C.authorid group by A.authorid, A.aname,A.city
having sum(C.price) > (select avg(price)   from catalog)
and count(*)>=2


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

select A.authorid ,A.aname  ,A.city ,C.bookid,sum(O.qty) as QTY_SUM into tb_auth  from author A, catalog C,order_det O
where A.authorid  =  C.authorid
and C.bookid = O.bookid  group by A.authorid, A.aname,A.city,C.bookid

select * from  tb_auth   where QTY_SUM in (select max(QTY_SUM) from tb_auth)


select A.authorid ,A.aname  ,A.city ,sum(O.qty) as QTY_SUM   from author A, catalog C,order_det O
where A.authorid  =  C.authorid
and C.bookid = O.bookid  group by A.authorid, A.aname,A.city,C.bookid
having sum(qty) >= all (select sum(qty)  from order_det group by bookid)

(select A.authorid ,A.aname  ,A.city ,C.bookid,sum(O.qty)  from author A, catalog C,order_det O
where A.authorid  =  C.authorid
and C.bookid = O.bookid)   group by A.authorid, A.aname,A.city,C.bookid
having sum(qty) = (select max(qty)  from temp1 )

create view temp as
select A.authorid ,A.aname  ,A.city ,C.bookid,sum(O.qty) as QTY_SUM  from author A, catalog C,order_det O
where A.authorid  =  C.authorid
and C.bookid = O.bookid  group by A.authorid, A.aname,A.city,C.bookid

select * from  temp   where QTY_SUM = (select max(QTY_SUM) from temp)


select * from  tb_auth   where QTY_SUM in (select max(QTY_SUM) from tb_auth)


3. Increase the price of the books published by a specific publisher by 10%

update  catalog set price = price * 1.1 where pubid in ( select pubid from publisher where pname ='Pearson')


select count(*) as no_of_orders from order_det
where bookid in (

select bookid  from order_det  group by bookid
having sum(qty) >= all (select sum(qty)  from order_det group by bookid)
    )
group by bookid

select * from order_det