REM Create table storing changes in product prices by currency 

create table product_price_history ( 
  product_id          integer not null, 
  price               integer not null, 
  currency_code       varchar2(3 char) not null, 
  effective_from_date date not null, 
  effective_to_date   date, 
  constraint product_price_history_pk  
    primary key (product_id, currency_code, effective_from_date) 
) partition by range (effective_from_date) ( 
    partition p0 values less than (date'2015-01-02'), 
    partition p1 values less than (date'2015-01-03'), 
    partition p2 values less than (date'2015-01-04') 
)

REM Insert the test data 

insert into product_price_history 
  with prices as ( 
    select 1, 100, 'USD', date'2015-01-01', date'2015-01-02'  
    from   dual union all 
    select 1, 60, 'GBP', date'2015-01-01', date'2015-01-02'  
    from   dual union all 
    select 1, 110, 'EUR', date'2015-01-01', date'2015-01-02'  
    from   dual union all 
    select 1, 101, 'USD', date'2015-01-02', date'2015-01-03'  
    from   dual union all 
    select 1, 62, 'GBP', date'2015-01-02', date'2015-01-03'  
    from   dual union all 
    select 1, 109, 'EUR', date'2015-01-02', date'2015-01-03'  
    from   dual union all 
    select 1, 105, 'USD', date'2015-01-03', null 
    from   dual union all 
    select 1, 61, 'GBP', date'2015-01-03', null 
    from   dual union all 
    select 1, 107, 'EUR', date'2015-01-03', null 
    from   dual union all 
    select 2, 30, 'USD', date'2015-01-01', date'2015-01-03' 
    from   dual union all 
    select 2, 33, 'USD', date'2015-01-03', null 
    from   dual union all 
    select 3, 100, 'GBP', date'2015-01-03', null 
    from   dual 
  ) 
    select *  
    from   prices

REM View initial data set 

select * from product_price_history 
order  by product_id, effective_from_date, currency_code

REM Delete all the rows where product_id = 3 

delete from product_price_history  
where  product_id = 3

select * from product_price_history 
order  by product_id, effective_from_date, currency_code

REM Delete a single row 

REM Save the value of the currency column for the deleted row into a variable 

REM and display it 

declare 
  currency product_price_history.currency_code%type; 
begin 
  delete product_price_history  
  where  product_id = 2 
  and    effective_to_date is null 
  returning currency_code into currency; 
   
  dbms_output.put_line(currency); 
end; 

select * from product_price_history 
order  by product_id, effective_from_date, currency_code

REM Delete the results of a query 

delete (select * from product_price_history) 
where  currency_code = 'EUR'

select * from product_price_history 
order  by product_id, effective_from_date, currency_code

REM Delete based on a subquery 

REM Delete the most recent price for each product and currency 

delete product_price_history pp 
where  (product_id, currency_code, effective_from_date) 
   in (select product_id, currency_code, max(effective_from_date) 
       from   product_price_history 
       group by product_id, currency_code)

select * from product_price_history 
order  by product_id, effective_from_date, currency_code

REM Delete all the rows in a given partition 

REM partition P1 = effective from >= 2 JAN 2015 and < 3 JAN 2015 

delete product_price_history partition (p1)

select * from product_price_history

REM Delete all rows in a table 

delete product_price_history

select * from product_price_history