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
Abdul Haseeb
