drop table member cascade constraints;
create table member (
  mid             varchar2 (7),
  password        varchar2 (8) not null,
  fname           varchar2 (15) not null,
  lname           varchar2 (15) not null,
  address         varchar2 (50),
  email           varchar2(30),
  cash_balance    number (10,2) not null,
  primary key (mid)
);
insert into member values 
('1000','0000','John','Smith','123 Main St','k1@abc.com',40000.00);
insert into member values 
('1001','1111','John','Jones','124 Main St','k2@abc.com',40000.00);
insert into member values 
('1002','2222','John','Blake','125 Main St','k3@abc.com',40000.00);
insert into member values 
('1003','3333','John','Philip','126 Main St','k4@abc.com',40000.00);
insert into member values 
('1004','4444','John','Brown','127 Main St','k5@abc.com',40000.00);

drop table security cascade constraints;
create table security (
  symbol              varchar2 (8),
  cname               varchar2 (30),
  current_price       number (7,3)  check (current_price >= 0.0) not null,
  ask_price           number (7,3)  check (ask_price >= 0.0),
  bid_price           number (7,3)  check (bid_price >= 0.0),
  primary key (symbol)
);

insert into security values ('CSCO','Cisco Corporation',80.00,79.00,79.50);
insert into security values ('ORCL','Oracle Corporation',60.00,59.00,59.50);
insert into security values ('SUNW','Sun Microsystems',120.00,120.00,120.50);
insert into security values ('RHAT','Red Hat Inc.',30.00,29.00,29.50);
insert into security values ('MSFT','Microsoft Corp.',180.00,179.00,179.50);
insert into security values ('BLS','Bellsouth',65.00,64.00,64.50);
insert into security values ('T','ATT',34.00,33.00,33.50);

drop table transaction cascade constraints;
create table transaction (
  mid              varchar2 (7),
  symbol           varchar2 (8),
  trans_date       date,
  trans_type       varchar2 (20) not null,
  quantity         number (7,2) not null,
  price_per_share  number (7,3)  check (price_per_share >= 0.0) not null,
  commission       number (5,2)  check (commission >= 0.0) not null,
  amount           number (8,2)  check (amount >= 0.0) not null,
  primary key (mid, symbol, trans_date),
  foreign key (mid) references member,
  foreign key (symbol) references security
);

insert into transaction values
('1000','ORCL','10-JAN-99','buy',100,50.00,29.95,5029.95);
insert into transaction values
('1000','RHAT','10-JAN-99','buy',100,30.00,29.95,3029.95);
insert into transaction values
('1000','SUNW','10-JAN-99','buy',100,40.00,29.95,4029.95);
insert into transaction values
('1000','ORCL','10-FEB-99','buy',100,60.00,29.95,6029.95);
insert into transaction values
('1000','ORCL','10-APR-99','sell',100,90.00,29.95,8970.05);
insert into transaction values
('1000','T','10-APR-99','buy',100,20.00,29.95,2029.95);
insert into transaction values
('1000','SUNW','10-MAY-99','sell',500,50.00,29.95,2470.05);
insert into transaction values
('1000','MSFT','10-MAY-99','buy',100,80.00,29.95,8029.95);
insert into transaction values
('1000','ORCL','10-JUN-99','buy',100,60.00,29.95,6029.95);
insert into transaction values
('1000','T','10-JUN-99','buy',100,40.00,29.95,4029.95);
insert into transaction values
('1000','ORCL','10-JUL-99','sell',100,90.00,29.95,8970.05);
insert into transaction values
('1000','RHAT','10-SEP-99','buy',100,50.00,29.95,5029.95);
insert into transaction values
('1000','ORCL','10-SEP-99','buy',100,80.00,29.95,8029.95);

insert into transaction values
('1001','MSFT','10-JUN-99','buy',200,30.00,29.95,6029.95);
insert into transaction values
('1001','SUNW','10-JUN-99','buy',100,45.00,29.95,4529.95);
insert into transaction values
('1001','MSFT','10-JUL-99','buy',100,20.00,29.95,2029.95);
insert into transaction values
('1001','SUNW','10-AUG-99','sell',100,50.00,29.95,4970.05);
insert into transaction values
('1001','MSFT','10-OCT-99','sell',150,50.00,29.95,7470.05);
insert into transaction values
('1001','ORCL','10-OCT-99','buy',50,100.00,29.95,5029.95);

drop view buy_transaction;
create view buy_transaction as
  select mid, symbol, sum(quantity) total
  from transaction 
  where trans_type = 'buy'
  group by mid, symbol;

drop view sell_transaction;
create view sell_transaction as
  select mid, symbol, sum(quantity) total
  from transaction 
  where trans_type = 'sell'
  group by mid, symbol;

drop view portfolio;
create view portfolio as
  (select b.mid, b.symbol, (b.total - s.total) quantity
   from buy_transaction b, sell_transaction s
   where b.mid = s.mid and 
         b.symbol = s.symbol and
         (b.total - s.total) > 0)
  union
  (select b.mid, b.symbol, b.total  quantity
   from buy_transaction b
   where not exists (
                select * 
                from sell_transaction s
                where b.mid = s.mid and 
                      b.symbol = s.symbol));

create or replace function avgPP(
  mid in member.mid%type,
  sym in security.symbol%type) 
       return transaction.price_per_share%type as 

  
  cursor c1 is 
    select trans_type, quantity, price_per_share
    from transaction
    where mid = avgPP.mid and
          symbol = sym
    order by trans_date;

  q transaction.quantity%type := 0.0;
  a transaction.price_per_share%type := 0.0;

begin
  for c1_rec in c1 loop
    if (c1_rec.trans_type = 'buy') then
      a := ((q * a) + (c1_rec.quantity * c1_rec.price_per_share))/
            (q + c1_rec.quantity);
      q := q + c1_rec.quantity;
    else
      q := q - c1_rec.quantity;
    end if;
  end loop;
  return (1.01 * a);  -- Commission of 1%
end;
/
show errors