Question

Advance Challenge

To perform this activity, refer to the tables in the JustLee Books database.

Management is proposing to increase the price of each book. The amount of the increase will be based on each book’s category, according to the following scale: Computer books, 10%; Fitness books, 15%; Self-Help books, 25%; all other categories, 3%. Create a list that displays each book’s title, category, current retail price, and revised retail price. The prices should be displayed with two decimal places. The column headings for the output should be as follows: Title, Category, Current Price, and Revised Price. Sort the results by category. If there’s more than one book in a category, a secondary sort should be performed on the book’s title.

Create a document to show management the SELECT statement used to generate the results and the results of the statement.

Answer 1

Queries used in sequence......

create database mynew;

use mynew;

create table BOOK(ISBN int PRIMARY KEY, TITLE VARCHAR(50) NULL, PUBDATE DATE NULL, PUBID INT NULL, COST DOUBLE NULL, RETAIL DOUBLE NULL, CATEGORY VARCHAR(50) NULL);

select * from BOOK;

desc BOOK;

INSERT INTO BOOK VALUES(1059831198, "BODYBUILD IN 10 MINUTES A DAY", "21-01-01", 4, 18.75, 30.95, "FITNESS");

INSERT INTO BOOK VALUES(0401140733, "REVENGE OF MICKEY", "14-12-01", 1, 14.20, 22.00, "FAMILY LIFE");

INSERT INTO BOOK VALUES(1981341710, "BUILDING A CAR WITH TOOTHPICKS", "18-03-02", 2, 37.80, 59.95, "CHILDREN");

INSERT INTO BOOK VALUES(1843172113, "DATABASE IMPLEMENTATION", "04-06-09", 3, 31.40, 55.95, "COMPUTER");

INSERT INTO BOOK VALUES(1437212490, "COOKING WITH MUSHROOMS", "28-02-01", 4, 12.50, 19.95, "COOKING");

INSERT INTO BOOK VALUES (0132149871, "HOW TO GET FASTER PIZZA", "11-11-02", 4, 17.85, 29.95, "SELF HELP");

select * from BOOK;

ALTER TABLE BOOK ADD COLUMN REVISED DOUBLE;

select * from BOOK;

UPDATE BOOK SET REVISED=(COST+COST*0.1) WHERE CATEGORY="COMPUTER";

UPDATE BOOK SET REVISED=(COST+COST*0.15) WHERE CATEGORY="FITNESS";

UPDATE BOOK SET REVISED=(COST+COST*0.25) WHERE CATEGORY="SELF HELP";

UPDATE BOOK SET REVISED=(COST+COST*0.03) WHERE CATEGORY NOT IN ("COMPUTER", "FITNESS", "SELF HELP");

select * from BOOK;

SELECT TITLE AS "Title", CATEGORY AS "Category", ROUND(COST, 2) AS "Current Price", ROUND(COST, 2) AS "Revised Price" from BOOK;