Question

Create a table book_store with columns Book_id VARCHAR(255) NOT NULL,

Book_Name VARCHAR(255) NOT NULL,

Book_genre VARCHAR(255) NOT NULL,

Status VARCHAR(255) NOT NULL,

PRIMARY KEY (Book_id)

Create a table book with columns Book_id VARCHAR(255) NOT NULL,

Book_Name VARCHAR(255) NOT NULL,

Book_release integer,

Book_price integer ,

Publisher Varchar(10),

Book_genre VARCHAR(255) NOT NULL,

PRIMARY KEY (Book_id)

CREATE TABLE price_logs with columns id INT(11) NOT NULL AUTO_INCREMENT,

Book_id VARCHAR(255) NOT NULL,

Old_Book_price DOUBLE NOT NULL,

New_Book_price DOUBLE NOT NULL,

updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

updated_by varchar(30) NOT NULL,

PRIMARY KEY (id),

KEY Book (Book_id),

CONSTRAINT price_logs_ibfk_1 FOREIGN KEY (Book_id) REFERENCES Book (Book_id) ON DELETE CASCADE ON UPDATE CASCADE

4. Stored Procedure:

Return only top 3 when parameter TOP3 is passed, top 5 when TOP5 is passed else print entire book table.

DELIMITER //

CREATE PROCEDURE GetBooks( all_books varchar(10) )

BEGIN

<Write your code here >

END IF;

END // DELIMITER ;

call the procedure:

CALL GetBooks('ALL');

CALL GetBooks('TOP3');

CALL GetBooks('TOP5');

Answer 1

Create a table book_store with columns

create table book_store(

Book_id VARCHAR(255) NOT NULL,

Book_Name VARCHAR(255) NOT NULL,

Book_genre VARCHAR(255) NOT NULL,

Status VARCHAR(255) NOT NULL,

PRIMARY KEY (Book_id)
);

Create a table book with columns

create table book(
Book_id VARCHAR(255) NOT NULL,

Book_Name VARCHAR(255) NOT NULL,

Book_release integer,

Book_price integer ,

Publisher Varchar(10),

Book_genre VARCHAR(255) NOT NULL,

PRIMARY KEY (Book_id)
);

CREATE TABLE price_logs with columns

create table price_logs(
id INT(11) NOT NULL AUTO_INCREMENT,

Book_id VARCHAR(255) NOT NULL,

Old_Book_price DOUBLE NOT NULL,

New_Book_price DOUBLE NOT NULL,

updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

updated_by varchar(30) NOT NULL,

PRIMARY KEY (id),

KEY Book (Book_id),

CONSTRAINT price_logs_ibfk_1 FOREIGN KEY (Book_id) REFERENCES Book (Book_id) ON DELETE CASCADE ON UPDATE CASCADE
);

STORED PROCEDURE CODE:

This portion to be written in place of <Write your code here >

IF all_books = 3 THEN
SELECT * FROM book LIMIT 3;
ELSIF all_books = 5 THEN
SELECT * FROM book LIMIT 5;
ELSE
SELECT * FROM book;
END IF;

For any doubts feel free to ask.