create table library(bookid varchar(10)primary key,bookname char(20),author char(20),datepurchased date ,publisher char(20),price int );

INSERT INTO library values('b101','cost accounting','jain narang','2013-02-11','kalyani',800);
INSERT INTO library values('b102','bussiness statistics','op aggarwal','2011-12-22','himalaya',750);
INSERT INTO library values('b103','rdbms','cjdate','2015-03-02','TMH',900);
INSERT INTO library values('b104','Mgmt accounting','RK sharma','2016-04-19','kalyani', 450);
INSERT INTO library values('b105','operating system','Galvin','2013-11-25','PHI', 750);
INSERT INTO library values('b106','adavance accounting','SC Gupta','2018-04-16','himalaya', 600);
    
select*from library;

1. Write sql query to display the list of authors from Himalaya publications.
SELECT Author FROM Library
WHERE Publisher = 'himalaya';

Output-
OP Aggarwal
SC Gupta

2. Write sql query to display the total cost of books purchased Publisher wise.   
SELECT Publisher ,Sum(Price) AS Totalcost 
FROM Library 
GROUP BY Publisher;

Output-
PHI|750
TMH|900
Himalaya|1350
Kalyani|1250

3. Write sql query to count the total number of books under Kalyani publications.
SELECT Publisher ,COUNT(*) AS Totalbook
FROM Library
WHERE Publisher = 'Kalyani'
GROUP BY Publisher;

Output-
Kalyani|2

4. Write sql query to rename the column Publisher as Publications.
ALTER TABLE Library RENAME COLUMN Publisher to Publications;

Output-

    
5. Write a sql query to display the books in the ascending order of DatePurchased.
SELECT Bookname, Datepurchased FROM library ORDER BY Datepurchased;

Output-
Bussiness Statistics|2011-12-22
Cost Accounting|2013-02-11
Operating System|2013-11-25
Rdbms|2015-03-02
Mgmt Accounting|2016-04-19
Adavance Accounting|2018-04-16
    
6. Write sql query to create an index on the fields BookName and Author.
CREATE INDEX idx_Book_Author ON Library(Bookname,Author);

Output-

7. Write sql query to display the books whose price is between 500 and 700.
SELECT Bookname,Price FROM Library 
WHERE Price between 500 and 700;

Output-
Adavance Accounting|600

8. Write sql query to increase the price of all the books by 200 for publishers other than Himalaya or 
Kalyani.
UPDATE Library 
SET Price = Price + 200
WHERE Publications not in ('Himalaya', 'Kalyani');
SELECT Price FROM Library;

Output-
800
750
1100
450
950
600

9.Write sql query to display the book details where author name contains the name Sharma.
SELECT * FROM Library
WHERE Author like '%Sharma';

Output-
B104|Mgmt Accounting|RK Sharma|2016-04-19|Kalyani|450

10. Create a view to display the fields BookId and BookName where the Publisher is Himalaya.
CREATE VIEW Himalaya AS
SELECT Bookid, Bookname FROM Library
WHERE Publications = 'Himalaya';