DEV Community

Abdul Haseeb
Abdul Haseeb

Posted on

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)
);
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

3. Display the contents of the Book table

SELECT * FROM Book;
Enter fullscreen mode Exit fullscreen mode

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

ALTER TABLE Book MODIFY Price DECIMAL(10, 2);
Enter fullscreen mode Exit fullscreen mode

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)
);
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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

ALTER TABLE Book ADD Qty INT DEFAULT 100;
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

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

SELECT Title, Qty FROM Book;
Enter fullscreen mode Exit fullscreen mode

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');
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

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

SELECT * FROM Book WHERE Title LIKE '%ri%';
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

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

SELECT Author, Price FROM Book WHERE Price > 2000;
Enter fullscreen mode Exit fullscreen mode

16. Display unique Category from the Book table

SELECT DISTINCT Catg FROM Book;
Enter fullscreen mode Exit fullscreen mode

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

SELECT Title, Price FROM Book WHERE Catg = 'Reference';
Enter fullscreen mode Exit fullscreen mode

18. Display contents of the Book_Issue table

SELECT * FROM Book_Issue;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Top comments (0)