Given the Data Definition Language (DDL) statements and content of BOOKS table as follows. CREATE TABLE...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Given the Data Definition Language (DDL) statements and content of BOOKS table as follows. CREATE TABLE Books (ISBN VARCHAR2 (10) PRIMARY KEY, Title VARCHAR2 (30), PubDate DATE, PubID NUMBER (2), Cost NUMBER (5,2), Retail NUMBER (5,2), Category VARCHAR2 (12)); ISBN TITLE BODYBUILD IN 10 MINUTES 1059831198 A DAY 401140733 REVENGE OF MICKEY BUILDING A CAR WITH 4981341710 TOOTHPICKS DATABASE 8843172113 IMPLEMENTATION COOKING WITH 3437212490 MUSHROOMS 3957136468 HOLY GRAIL OF ORACLE 1915762492 HANDCRANKED COMPUTERS 9959789321 E-BUSINESS THE EASY WAY 2491748320 PAINLESS CHILD-REARING 299282519 THE WOK WAY TO COOK 8117949391 BIG BEAR AND LITTLE DOVE 132149871 HOW TO GET FASTER PIZZA HOW TO MANAGE THE 9247381001 MANAGER 2147428890 SHORTEST POEMS PUBDATE PUBID 4 01/21/2001 12/14/2001 03/18/2002 6/4/2099 02/28/2000 12/31/2001 01/21/2001 3/1/2002 07/17/2000 9/11/2000 11/8/2001 11/11/2002 5/9/2099 5/1/2001 1 2 3 4 3 3 2 5 4 5 4 1 5 COST RETAIL CATEGORY 18.75 14.2 37.8 31.4 12.5 47.25 21.8 37.9 48 19 5.32 17.85 15.4 21.85 30.95 FITNESS FAMILY 22 LIFE 59.95 CHILDREN 55.95 COMPUTER 19.95 COOKING 75.95 COMPUTER 25 COMPUTER 54.5 COMPUTER FAMILY 89.95 LIFE 28.75 COOKING 8.95 CHILDREN 29.95 SELF HELP 31.95 BUSINESS 39.95 LITERATURE a) Complete the following stored function called NEW RETAIL to return the new retail price by accepting two parameters (category and retail). The percentage of price increase is according to the category. Use the following table to help you. IS Category FITNESS FAMILY LIFE CHILDREN COOKING COMPUTER OTHERS The NEW RETAIL function: CREATE OR REPLACE FUNCTION new_retail BEGIN Percentage 8% 8% 3% 7% 10% 5% add your code here. v_newretail books.retail&TYPE; add your code here. RETURN v newretail; END new_retail; / b) Use the function in a SELECT statement against the BOOKS table for all books. Display the ISBN, book title, retail price and the new retail price. The example output is given in the following figure. ISBN 1059831198 0401140733 4981341710 8843172113 3437212490 3957136468 1915762492 TITLE BODYBUILD IN 10 MINUTES A DAY REVENGE OF MICKEY BUILDING A CAR WITH TOOTHPICKS DATABASE IMPLEMENTATION COOKING WITH MUSHROOMS HOLY GRAIL OF ORACLE HANDCRANKED COMPUTERS 30.95 22 RETAIL 5995 55.95 1995 75.95 25 33.43 231 New Retail 61.75 61.55 21.35 83.55 27.5 Given the Data Definition Language (DDL) statements and content of BOOKS table as follows. CREATE TABLE Books (ISBN VARCHAR2 (10) PRIMARY KEY, Title VARCHAR2 (30), PubDate DATE, PubID NUMBER (2), Cost NUMBER (5,2), Retail NUMBER (5,2), Category VARCHAR2 (12)); ISBN TITLE BODYBUILD IN 10 MINUTES 1059831198 A DAY 401140733 REVENGE OF MICKEY BUILDING A CAR WITH 4981341710 TOOTHPICKS DATABASE 8843172113 IMPLEMENTATION COOKING WITH 3437212490 MUSHROOMS 3957136468 HOLY GRAIL OF ORACLE 1915762492 HANDCRANKED COMPUTERS 9959789321 E-BUSINESS THE EASY WAY 2491748320 PAINLESS CHILD-REARING 299282519 THE WOK WAY TO COOK 8117949391 BIG BEAR AND LITTLE DOVE 132149871 HOW TO GET FASTER PIZZA HOW TO MANAGE THE 9247381001 MANAGER 2147428890 SHORTEST POEMS PUBDATE PUBID 4 01/21/2001 12/14/2001 03/18/2002 6/4/2099 02/28/2000 12/31/2001 01/21/2001 3/1/2002 07/17/2000 9/11/2000 11/8/2001 11/11/2002 5/9/2099 5/1/2001 1 2 3 4 3 3 2 5 4 5 4 1 5 COST RETAIL CATEGORY 18.75 14.2 37.8 31.4 12.5 47.25 21.8 37.9 48 19 5.32 17.85 15.4 21.85 30.95 FITNESS FAMILY 22 LIFE 59.95 CHILDREN 55.95 COMPUTER 19.95 COOKING 75.95 COMPUTER 25 COMPUTER 54.5 COMPUTER FAMILY 89.95 LIFE 28.75 COOKING 8.95 CHILDREN 29.95 SELF HELP 31.95 BUSINESS 39.95 LITERATURE a) Complete the following stored function called NEW RETAIL to return the new retail price by accepting two parameters (category and retail). The percentage of price increase is according to the category. Use the following table to help you. IS Category FITNESS FAMILY LIFE CHILDREN COOKING COMPUTER OTHERS The NEW RETAIL function: CREATE OR REPLACE FUNCTION new_retail BEGIN Percentage 8% 8% 3% 7% 10% 5% add your code here. v_newretail books.retail&TYPE; add your code here. RETURN v newretail; END new_retail; / b) Use the function in a SELECT statement against the BOOKS table for all books. Display the ISBN, book title, retail price and the new retail price. The example output is given in the following figure. ISBN 1059831198 0401140733 4981341710 8843172113 3437212490 3957136468 1915762492 TITLE BODYBUILD IN 10 MINUTES A DAY REVENGE OF MICKEY BUILDING A CAR WITH TOOTHPICKS DATABASE IMPLEMENTATION COOKING WITH MUSHROOMS HOLY GRAIL OF ORACLE HANDCRANKED COMPUTERS 30.95 22 RETAIL 5995 55.95 1995 75.95 25 33.43 231 New Retail 61.75 61.55 21.35 83.55 27.5
Expert Answer:
Related Book For
Posted Date:
Students also viewed these databases questions
-
A 5 year USA Treasure bond yields 4% and a 10 year treasury bond yields 5%. You own a 7.5 year bond issued by Apple Inc. it yields 6.25%. What is the credit risk premium on your bond? A 5 year USA...
-
On November 14, Thorogood Enterprises announced that the public and acrimonious battle with its current CEO had been resolved. Under the terms of the deal, the CEO would step down from his position...
-
1. On average, and all else being equal, women who are mothers are paid less than women who are not mothers (this difference in pay is sometimes called "the mothers discount"). Explain why women who...
-
Refer to the situation described in P 192. Assume Pastner measures the fair value of all options on January 1, 2021, to be $4.50 per option using a single weighted-average expected life of the...
-
The trial balance of Nina's Novelty, Inc. at September 30, 2015, and the data needed for the month-end adjustments follow: a. Insurance coverage still remaining at September 30, $800. b. Supplies...
-
List three specific uses of cumulative frequencies in business.
-
What is SE?
-
The defroster of an automobile functions by discharging warm air on the inner surface of the windshield. To prevent condensation of water vapor on the surface, the temperature of the air and the...
-
1) Provide the price to a semiannual bond that has a coupon rate of 12.5%, face value of $1,000, YTM of 12%, and matures in 16 years. Provide the value as a positive number. 2) Given the information...
-
Senior Home Living (SHL) is a Canadian-based corporation located in British Columbia. SHL provides senior living residences across Canada. The company was incorporated in 1975, and has been...
-
On July 10, 2015, you purchase a $10,000 T-note that matures on December 31, 2024 (settlement occurs two days after purchase, so you receive actual ownership of the bond on July 12, 2015). The coupon...
-
Lexon Inc. is a large manufacturer of affordable DVD players. Management recently became aware of rising expenses resulting from returns of malfunctioning products. As a starting point for further...
-
Suppose you have been elected to Congress. One day, one of your colleagues makes the following statement: The Fed chair is the most powerful economic policy maker in the United States. We should not...
-
GDP in 2009 was roughly $15,000 billion. In 2009, GDP fell by approximately 3 percentage points in 2009. a. How many billion dollars is 3 percentage points of $15,000 billion? b. If the propensity to...
-
Why is the effect of a lockdown different from that of an oil shock? There is an important difference between a lockdown and an oil shock. Although a lockdown is typically temporary, not all firms...
-
Go to the website for the Federal Reserve Board of Governors (www.federalreserve.gov) and download the most recent monetary policy press release of the Federal Open Market Committee (FOMC), the body...
-
A paramagnetic material has 1028 atoms/m3. Its magnetic susceptibility at temperature 350 K is 2.8 104. Its susceptibility at 300 K is (a) 3.267 104 (b) 3.672 104 (c) 2.672 104 (d) 3.726 104
-
Is it ethical to provide safety training in English to immigrant workers who speak little English, in order to reduce costs?
-
Create a table named REP_CONTRACTS containing the columns listed in the following chart. A composite PRIMARY KEY constraint including the Rep_ID, Store_ID, and Quarter columns should be assigned. In...
-
How do you ensure that no user can change the data displayed by a view?
-
Which of the following is correct? a. The ORDER BY clause cant be used in the subquery of a CREATE VIEW command. b. The ORDER BY clause cant be used in an inline view. c. The DISTINCT keyword cant be...
-
This is a two-dimensional parallel channel with a corrugated plate (sine curve), which the distance between the upper and lower walls is h, Prove that is a solution to the subsonic linearization...
-
The experiment of airfoil NACA006 was carried out in subsonic wind tunnel, and the slope of lift curve d at a = 0 was measured as Try to plot experimental curve and compare with the results from...
-
Under the linearized condition, please write down the formulation of the definite solution problem of the velocity potential function of supersonic flow? The linearized expression of the wall...
Study smarter with the SolutionInn App