Question

Query Description Salece hardware store has decided to sell some products on discount as described below: (a) all types of saws (i.e., products whose description contains the word saw) are discounted by 7% (b) all types of hammers (i.e., products whose description contains the word hammer) are discounted by 13%. (c) other products are not discounted, i.e., all other products are selling at their listed price. Write a single SQL statement to show product code, product description, price (as currently listed in the database), and selling price. Selling price is discounted price for saws and hammers, but for other products it is the same as currently listed in the database. Sample (partial)query output is shown below for your guidance. HINT: You need to use the following three columns from the PRODUCT table: Racede (for product code), rudescript (for product description), and paprice (for list price). The fourth column shown in the sample output (Selling Price) is a calculated column. Product Code Description List Price Discounted Price 89-WRE-Q Hicut chain saw, 16 in. 256.99 239 WR3/TT3 119.95 119.95 Steel matting, 4'*8'x1/6", .5" mesh Power painter, 15 psi., 3-nozzle 11QER/31 109.99 109.99 2232/QTY 109.92 102.23 2232/QWE B\&D jigsaw, 12-in. blade B\&D jigsaw, 8-in. blade Hrd. cloth, 1/2-in., 3x50 99.87 92.88 1558-QW1 43.99 43.99

Answer 1

create table PRODUCT(PRODUCTCODE VARCHAR(20),DESCRIPTION VARCHAR(50),LISTPRICE numeric(10,2),DISCOUNTEDPRICE numeric(10,2));
INSERT INTO PRODUCT VALUES("89-WRE-Q","Hicut chain saw,16 in.",256.99,239);
INSERT INTO PRODUCT VALUES("WR3/TT3","Steel matting,4'x8'x1/6',.5''mesh",119.95,119.95);
INSERT INTO PRODUCT VALUES("11QER/31","Power painter,15 psi.,3-nozzle",109.99,109.99);
INSERT INTO PRODUCT VALUES("2232/QTY","B\&D jigsaw,12-in. blade",109.92,102.23);
INSERT INTO PRODUCT VALUES("2232/QWE","B\&D jigsaw,8-in. blade",99.87,92.88);
INSERT INTO PRODUCT VALUES("1558-QW1","Hrd. cloth,1/2-in.,3x50",43.99,43.99);

alter table PRODUCT ADD COLUMN SELLINGPRICE NUMERIC(10,2);

UPDATE PRODUCT SET SELLINGPRICE=DISCOUNTEDPRICE*0.13 WHERE DESCRIPTION LIKE '%hammer%';
UPDATE PRODUCT SET SELLINGPRICE=DISCOUNTEDPRICE*0.07 WHERE DESCRIPTION LIKE '%saw%';
UPDATE PRODUCT SET SELLINGPRICE=LISTPRICE WHERE DESCRIPTION NOT LIKE '%hammer%' OR DESCRIPTION NOT LIKE '%saw%';

select PRODUCTCODE,DESCRIPTION,LISTPRICE,SELLINGPRICE FROM PRODUCT;

MySQL Enter a title here Main.sql X + Success Tweet Share o 0 1 create table PRODUCT (PRODUCTCODE VARCHAR(20), DESCRIPTION VARCHAR(56), LISTPRICE numeric(10,2), DISCOUNTEDPRICE numeric(10,2)); 2 INSERT INTO PRODUCT VALUES("89-WRE-Q", "Hicut chain saw, 16 in. ",256.99,239); 3 INSERT INTO PRODUCT VALUES("WR3/TT3", "Steel matting, 4'x'x1/6,.5''mesh", 119.95,119.95); 4 INSERT INTO PRODUCT VALUES("110ER/31", "Power painter, 15 psi. ,3-nozzle", 109.99, 109.99); 5 INSERT INTO PRODUCT VALUES("2232/QTY", "B\&D jigsaw, 12-in. blade",109.92,102.23); 6 INSERT INTO PRODUCT VALUES ("2232/QWE", "B\&D jigsaw, 8-in. blade", 99.87,92.88); 7 INSERT INTO PRODUCT VALUES("1558-W1", "Hrd. cloth, 1/2-in.,3x50", 43.99,43.99); 9 9 10 alter table PRODUCT ADD COLUMN SELLINGPRICE NUMERIC(10,2); 11 12 13 14 UPDATE PRODUCT SET SELLINGPRICE-DISCOUNTEDPRICE*0.13 WHERE DESCRIPTION LIKE Xhammer%'; 15 UPDATE PRODUCT SET SELLINGPRICE-DISCOUNTEDPRICE*0.07 WHERE DESCRIPTION LIKE '%saw; 16 UPDATE PRODUCT SET SELLINGPRICE LISTPRICE WHERE DESCRIPTION NOT LIKE 'Xhammer%' OR DESCRIPTION NOT LIKE '%saw'; 17 18 19 select PRODUCTCODE, DESCRIPTION,LISTPRICE, SELLINGPRICE FROM PRODUCT; Run (Ctrl-Enter) G * .. P Output Input Comments (1 Text PRODUCTCODE DESCRIPTION LISTPRICE SELLINGPRICE 89-WRE-Q Hicut chain saw, 16 in. 256.99 256.99 WR3/TT3 Steel matting, 4x8'x1/6', .5''mesh 119.95 119.95 11QER/31 Power painter, 15 psi., 3-nozzle 109.99 109.99 2232/QTY B&D jigsaw, 12-in. blade 109.92 109.92 2232/QWE B&D jigsaw,8-in. blade 99.87 99.87 1558-OW1 Hrd. cloth, 1/2-in.,3x50 43.99 43.99