Sem V MYSQL Practical 6

1. Rename the column Pub of the Book table to Publisher.

ALTER TABLE Book RENAME COLUMN Pub TO Publisher;

2. Delete the row from the Book_Issue table where the fine is zero.

DELETE FROM Book_Issue WHERE Fine = 0;

3. Rename the Book_Issue table to Issue.

RENAME TABLE Book_Issue TO Issue;

4. Display the information from the Book table for the ‘Thriller’ category.

SELECT * FROM Book WHERE Category = 'Thriller';

5. Insert a row into the Book table.

INSERT INTO Book (BookID, Title, Author, Publisher, Category, Price, Qty) 
VALUES ('C142', 'Chacha Chaudhary', 'Pran Kumar', 'Diamond', 'Comics', 150, 200);

6. Delete the column Fine from the Book_Issue table.

ALTER TABLE Issue DROP COLUMN Fine;

7. Display the records of Comics type of books.

SELECT * FROM Book WHERE Category = 'Comics';

8. Transaction Statements for Book Table (Cosmos):

i. Display original contents of the Book where the title of the book is ‘Cosmos’.
ii. Change the price of BookID S170 to 1050.
iii. Display the updated contents.
iv. Cancel the change with ROLLBACK.
v. Verify the original status.

-- Start the transaction
START TRANSACTION;

-- i. Display the original contents
SELECT * FROM Book WHERE Title = 'Cosmos';

-- ii. Update the price
UPDATE Book SET Price = 1050 WHERE BookID = 'S170';

-- iii. Display the updated contents
SELECT * FROM Book WHERE Title = 'Cosmos';

-- iv. Rollback the change
ROLLBACK;

-- v. Verify the original status
SELECT * FROM Book WHERE Title = 'Cosmos';

9. Transaction Statements for Book Table (Let Us C):

i. Display original contents of the Book where the title of the book is ‘Let Us C’.
ii. Delete the row with BookID R185.
iii. Display the table contents.
iv. Save the changes with COMMIT.
v. Verify that ROLLBACK does not work after COMMIT.

-- Start the transaction
START TRANSACTION;

-- i. Display the original contents
SELECT * FROM Book WHERE Title = 'Let Us C';

-- ii. Delete the row with BookID 'R185'
DELETE FROM Book WHERE BookID = 'R185';

-- iii. Display the updated contents
SELECT * FROM Book;

-- iv. Save the changes
COMMIT;

-- v. Verify that rollback does not revert the changes
-- Trying rollback will not affect the data now
ROLLBACK;

-- Verify contents
SELECT * FROM Book;

10. Delete the database LibraryXXX.

DROP DATABASE LibraryXXX;