Write SQL commands for the following based on the given tables:
Table: BOOKS
| book_id | Book_name | author_name | Publishers | Price | Type | qty |
|---------|-----------------------|-----------------|-------------|-------|---------|-----|
| L01 | Let us C | Sanjay Mukharjee| EPB | 450 | Comp | 15 |
| L02 | Genuine | J. Mukhi | FIRST PUBL. | 755 | Fiction | 24 |
| L04 | Mastering C++ | Kantkar | EPB | 165 | Comp | 60 |
| L03 | VC++ advance | P. Purohit | TDH | 250 | Comp | 45 |
| L05 | Programming with Python| Sanjeev | FIRST PUBL. | 350 | Fiction | 30 |
Table: ISSUED
| Book_ID | Qty_Issued |
|---------|------------|
| L02 | 13 |
| L04 | 5 |
| L05 | 21 |
(i) Show books of FIRST PUBL. published by P. Purohit.
(ii) Display cost of all books published by EPB.
(iii) Depreciate price of all books of EPB publishers by 5%.
(iv) Display BOOK_NAME and price of books with more than 5 copies issued.
(v) Show total cost of books of each type.
Answer (1)
To solve the problems using SQL commands based on the given tables, we can use SELECT statements and update commands. Here's a detailed breakdown of the SQL commands for each part:
(i) Show books of FIRST PUBL. published by P. Purohit.
This query requires filtering the BOOKS table on Publishers and author_name.
SELECT *
FROM BOOKS WHERE Publishers = 'FIRST PUBL.' AND author_name = 'P. Purohit';
(ii) Display cost of all books published by EPB.
For this query, filter the BOOKS table by Publishers to find the total Price.
SELECT SUM(Price) AS Total_Cost FROM BOOKS WHERE Publishers = 'EPB';
(iii) Depreciate price of all books of EPB publishers by 5%.
To depreciate the price, you can use the UPDATE command with a calculation that reduces the price by 5%.
UPDATE BOOKS SET Price = Price * 0.95 WHERE Publishers = 'EPB';
(iv) Display BOOK_NAME and price of books with more than 5 copies issued.
This requires a join between BOOKS and ISSUED tables, filtering on Qty_Issued.
SELECT BOOKS.Book_name, BOOKS.Price FROM BOOKS JOIN ISSUED ON BOOKS.book_id = ISSUED.Book_ID WHERE ISSUED.Qty_Issued > 5;
(v) Show total cost of books of each type.
Here, group the BOOKS table by Type and compute the sum of Price.
SELECT Type, SUM(Price * qty) AS Total_Value FROM BOOKS GROUP BY Type;
This step-by-step SQL explanation helps in retrieving the required data from the tables to meet each of the specified conditions efficiently. Remember to test each SQL command in your own environment to ensure they work as expected.
