Write a PL/SQL function that returns the average retail price of all of the book
ID: 3819734 • Letter: W
Question
Write a PL/SQL function that returns the average retail price of all of the books in the books table.
BOOKS table
CREATE TABLE Books
(ISBN VARCHAR2(10),
Title VARCHAR2(30),
PubDate DATE,
PubID NUMBER (2),
Cost NUMBER (5,2),
Retail NUMBER (5,2),
Discount NUMBER (4,2),
Category VARCHAR2(12),
CONSTRAINT books_isbn_pk PRIMARY KEY(isbn),
CONSTRAINT books_pubid_fk FOREIGN KEY (pubid)
REFERENCES publisher (pubid));
INSERT INTO BOOKS
VALUES ('1059831198','BODYBUILD IN 10 MINUTES A DAY','21-JAN-05',4,18.75,30.95, NULL, 'FITNESS');
INSERT INTO BOOKS
VALUES ('0401140733','REVENGE OF MICKEY','14-DEC-05',1,14.20,22.00, NULL, 'FAMILY LIFE');
INSERT INTO BOOKS
VALUES ('4981341710','BUILDING A CAR WITH TOOTHPICKS','18-MAR-06',2,37.80,59.95, 3.00, 'CHILDREN');
INSERT INTO BOOKS
VALUES ('8843172113','DATABASE IMPLEMENTATION','04-JUN-03',3,31.40,55.95, NULL, 'COMPUTER');
INSERT INTO BOOKS
VALUES ('3437212490','COOKING WITH MUSHROOMS','28-FEB-04',4,12.50,19.95, NULL, 'COOKING');
INSERT INTO BOOKS
VALUES ('3957136468','HOLY GRAIL OF ORACLE','31-DEC-05',3,47.25,75.95, 3.80, 'COMPUTER');
INSERT INTO BOOKS
VALUES ('1915762492','HANDCRANKED COMPUTERS','21-JAN-05',3,21.80,25.00, NULL, 'COMPUTER');
INSERT INTO BOOKS
VALUES ('9959789321','E-BUSINESS THE EASY WAY','01-MAR-06',2,37.90,54.50, NULL, 'COMPUTER');
INSERT INTO BOOKS
VALUES ('2491748320','PAINLESS CHILD-REARING','17-JUL-04',5,48.00,89.95, 4.50, 'FAMILY LIFE');
INSERT INTO BOOKS
VALUES ('0299282519','THE WOK WAY TO COOK','11-SEP-04',4,19.00,28.75, NULL, 'COOKING');
INSERT INTO BOOKS
VALUES ('8117949391','BIG BEAR AND LITTLE DOVE','08-NOV-05',5,5.32,8.95, NULL, 'CHILDREN');
INSERT INTO BOOKS
VALUES ('0132149871','HOW TO GET FASTER PIZZA','11-NOV-06',4,17.85,29.95, 1.50, 'SELF HELP');
INSERT INTO BOOKS
VALUES ('9247381001','HOW TO MANAGE THE MANAGER','09-MAY-03',1,15.40,31.95, NULL, 'BUSINESS');
INSERT INTO BOOKS
VALUES ('2147428890','SHORTEST POEMS','01-MAY-05',5,21.85,39.95, NULL, 'LITERATURE');
Explanation / Answer
Function:
Create Function Fun_GetAvgRetailPrice()
returns numeric(12, 2) AS
BEGIN
declare @AvgRetailBoolPrice as numeric(12, 2);
Select @AvgRetailBoolPrice = CAST(AVG(Retail) AS numeric(12, 2)) From BOOKS;
return @AvgRetailBoolPrice;
END
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.