Sem V MYSQL Practical 2

1. Create the database LibraryXXX, open it, and create the Book table

-- Create the database
CREATE DATABASE LibraryXXX;

-- Use the database
USE LibraryXXX;

-- Create the Book table
CREATE TABLE Book (
    Bookid CHAR(6) PRIMARY KEY,
    Title VARCHAR(15) NOT NULL,
    Author VARCHAR(15),
    Pub VARCHAR(11),
    Category ENUM('Thriller', 'Comics', 'Science', 'Poetry', 'Reference'),
    Price DECIMAL(7, 2)
);

2. Insert the rows into the Book table

INSERT INTO Book (Bookid, Title, Author, Pub, Category, Price) VALUES
('T150', 'Gone Girl', 'Gillian Flynn', 'Tata Mc.', 'Thriller', 1450.00),
('S170', 'Cosmos', 'Carl Sagan', 'Pearson', 'Science', 1600.00),
('P175', 'The Sonnets', 'Shakespeare', 'Techmedia', 'Poetry', 2400.00),
('R188', 'Mastering SQL', 'Loni', 'Tata Mc.', 'Reference', NULL),
('S200', 'Silent Spring', 'Rachel Carson', 'Techmedia', 'Science', 1550.00),
('R185', 'Let Us C', 'Kanetkar', 'Techmedia', 'Reference', 800.00),
('R189', 'Complete Java', 'Herbert', 'Pearson', 'Reference', 2100.00),
('P110', 'Leaves of Grass', 'Walt Whitman', 'Pearson', 'Poetry', 2500.00);

3. Display the contents of the Book table

SELECT * FROM Book;

4. Change the size of the Price column from (7,2) to (10,2)

ALTER TABLE Book MODIFY Price DECIMAL(10, 2);

5. Create the Book_Issue table

CREATE TABLE Book_Issue (
    Issue_id SMALLINT AUTO_INCREMENT PRIMARY KEY,
    RNO SMALLINT NOT NULL,
    Name VARCHAR(10),
    Book_id CHAR(6),
    DOI DATE NOT NULL,
    DOR DATE NOT NULL,
    Fine DOUBLE(8, 2) DEFAULT 0,
    FOREIGN KEY (Book_id) REFERENCES Book(Bookid)
);

6. Insert rows into the Book_Issue table

INSERT INTO Book_Issue (RNO, Name, Book_id, DOI, DOR, Fine) VALUES
(21, 'Akshat', 'T150', '2023-08-20', '2023-08-27', 0.00),
(32, 'Krishna', 'R185', '2023-06-02', '2023-06-10', 20.00),
(105, 'Payal', 'R188', '2024-01-05', '2024-01-12', 0.00),
(21, 'Akshat', 'R189', '2022-01-03', '2022-01-10', 0.00),
(364, 'Vinay', 'T150', '2023-06-15', '2023-06-30', 50.00),
(78, 'Diksha', 'S170', '2022-12-23', '2023-12-31', 0.00),
(146, 'Abhay', 'S200', '2022-11-19', '2022-11-30', 30.00),
(32, 'Krishna', 'P175', '2023-11-05', '2023-11-20', 15.00),
(32, 'Krishna', 'R189', '2024-02-18', '2024-02-25', 0.00),
(146, 'Abhay', 'T150', '2024-02-25', '2024-02-28', 0.00);

7. Add a column Quantity (Qty integer default 100) in the Book table

ALTER TABLE Book ADD Qty INT DEFAULT 100;

8. Update values in the Qty column

UPDATE Book SET Qty = 25 WHERE Bookid = 'T150';
UPDATE Book SET Qty = 100 WHERE Bookid = 'S170';
UPDATE Book SET Qty = 50 WHERE Bookid = 'P175';
UPDATE Book SET Qty = 40 WHERE Bookid = 'R188';
UPDATE Book SET Qty = 62 WHERE Bookid = 'S200';
UPDATE Book SET Qty = 11 WHERE Bookid = 'R185';
UPDATE Book SET Qty = 85 WHERE Bookid = 'R189';
UPDATE Book SET Qty = NULL WHERE Bookid = 'P110';

9. Display book name and quantity of the book from the Book table

SELECT Title, Qty FROM Book;

10. Change the column name of Category to Catg in the Book table

ALTER TABLE Book CHANGE Category Catg ENUM('Thriller', 'Comics', 'Science', 'Poetry', 'Reference');

11. Display the book name and author name whose price of the book is ranging from 1000 to 2000

SELECT Title, Author FROM Book WHERE Price BETWEEN 1000 AND 2000;

12. Display Author name, Catg, and Price for books whose Author name ends with letter ‘S’

SELECT Author, Catg, Price FROM Book WHERE Author LIKE '%S';

13. Display book details whose title contains letters ‘ri’

SELECT * FROM Book WHERE Title LIKE '%ri%';

14. Display Bookid, Title, and Catg where the title starts with letter ‘C’ and ends with letter ‘A’

SELECT Bookid, Title, Catg FROM Book WHERE Title LIKE 'C%A';

15. Display Author name and Price of books whose price is more than 2000

SELECT Author, Price FROM Book WHERE Price > 2000;

16. Display unique Category from the Book table

SELECT DISTINCT Catg FROM Book;

17. Display Title of book and Price for books whose Catg is Reference

SELECT Title, Price FROM Book WHERE Catg = 'Reference';

18. Display contents of the Book_Issue table

SELECT * FROM Book_Issue;

19. Display the RNO, Name, Book_id, and DOI for those who have issued books in the year 2023

SELECT RNO, Name, Book_id, DOI FROM Book_Issue WHERE YEAR(DOI) = 2023;

20. Display RNO, Name, Book_id, and DOI for those who have issued books in August month

SELECT RNO, Name, Book_id, DOI FROM Book_Issue WHERE MONTH(DOI) = 8;