Skip to Main Content
  • Questions
  • PL/SQL Database Programming Question

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Austin.

Asked: October 18, 2016 - 3:03 pm UTC

Last updated: October 19, 2016 - 12:53 am UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

I am struggling to figure out which LOOP statement to use. Here's the question:

Each day, starting on Monday, the price will drop 5% from the previous day’s price. Monday’s sale price will be 5% less than what is stored in the database in the BB_PRODUCT table. Then Tuesday’s sale price will be 5% less than what it was for Monday, etc. Create a PL/SQL block that will retrieve the original price from the database, display the original value and then display 5 additional lines that show the day of the week and the price for the product for that day. An initialized variable should provide the Product ID. You must use at least one looping structure in your solution.

For example, for IDPRODUCT = 1, the output may look like:

The original price is $99.99
The sale price on day #1 is $94.99
The sale price on day #2 is $90.24
The sale price on day #3 is $85.72
The sale price on day #4 is $81.44
The sale price on day #5 is $77.37

and Chris said...

I am struggling to figure out which LOOP statement to use.

Don't loop! Write SQL! :)

Calculate the running product of 0.95. Do this using by exploiting the fact that product(1 ... n) = exponent ( sum ( ln ( 1 ) .. ln ( n ) ) ).

You can then pass this as an analytic function. Couple with a row generator and you have your answer:

with rws as (
  select rownum x from dual connect by level <= 5
), orig as (
  select 99.99 v from dual
)
  select 'The original price is $' || v from orig
  union all 
  select 'The sale price on day #' || x || ' is $' ||
         round(99.99 * exp(sum(ln(0.95)) over (order by x)), 2)
  from rws;

'THEORIGINALPRICEIS$'||V            
The original price is $99.99        
The sale price on day #1 is $94.99  
The sale price on day #2 is $90.24  
The sale price on day #3 is $85.73  
The sale price on day #4 is $81.44  
The sale price on day #5 is $77.37

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Format

Austin Swoape, October 18, 2016 - 4:17 pm UTC

Per my assignment instructions I have to have a LOOP statement. I am learning from the text book:
Oracle 11g PL/SQL Programming 2e by Joan Casteel and we are in CH.4. In this chapter we are learning the Cursor and Cursor LOOPs also. My answer must be relative to that.

Thank you
Connor McDonald
October 19, 2016 - 12:53 am UTC

begin
for i in 1 .. 5 loop
   x := x * 0.95;
end loop;
end;


should get you started

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.